Fact Transactions

Schema: bitcoin.core Table: fact_transactions Type: View

​

Description

This table contains the canonical record of all confirmed Bitcoin transactions, providing comprehensive transaction-level data including block context, transaction identifiers, input/output details, fees, and raw transaction data. Each row represents a single transaction with complete UTXO information, fee calculations, and transaction metadata. The model aggregates data from upstream silver-layer models and includes computed fields such as transaction fees, input/output value totals, and coinbase status. All values are standardized to Bitcoin conventions (BTC and Satoshis), making this the primary source for Bitcoin transaction analytics.

​

Key Use Cases

• Transaction flow analysis and UTXO tracing across the Bitcoin network
• Fee market analysis and blockspace utilization studies
• Address and entity behavior pattern recognition
• Forensic analysis and transaction graph reconstruction
• Network activity monitoring and transaction volume analysis
• Coinbase transaction analysis and mining reward tracking
• Cross-transaction correlation and clustering analysis

​

Important Relationships

• Primary source for all transaction-level analytics in the Bitcoin ecosystem
• Referenced by core.fact_inputs and core.fact_outputs for detailed UTXO analysis
• Supports core.fact_clustered_transfers for entity-level transfer tracking
• Used by core.dim_entity_clusters for address clustering and entity identification
• Provides transaction data for stats.ez_core_metrics_hourly aggregated metrics
• Enables integration with core.dim_labels for categorized transaction analysis

​

Commonly-used Fields

• block_timestamp: Essential for time-series analysis and trend detection
• tx_hash: Critical for transaction identification and verification
• fee: Key metric for fee market analysis and transaction prioritization
• input_value and output_value: Core fields for value flow analysis
• inputs and outputs: Critical for UTXO tracing and transaction reconstruction
• is_coinbase: Important for distinguishing mining rewards from regular transactions
• size and virtual_size: Essential for blockspace analysis and fee calculations

​

Columns

Column Name	Data Type	Description
BLOCK_TIMESTAMP	TIMESTAMP_NTZ	The timestamp (TIMESTAMP_NTZ, UTC) of the block that contains this transaction. This value is inherited from the block header and represents when the block was mined and the transaction was confirmed on-chain.

Example: 2024-06-01 12:34:56.000 | | BLOCK_NUMBER | NUMBER | The block height (INTEGER) of the block containing this transaction. In Bitcoin, block height is the number of blocks preceding a particular block in the blockchain, starting from the genesis block (height 0). Example: 845000 | | BLOCK_HASH | TEXT | The 64-character hexadecimal hash (STRING) of the block containing this transaction. This value uniquely identifies the block in the Bitcoin blockchain. Example: 0000000000000000000a16b7e2e3b2c1d4e5f6a7b8c9d0e1f2a3b4c5d6e7f8g9 | | TX_ID | TEXT | The transaction ID (STRING), a 64-character hexadecimal hash that uniquely identifies this transaction. Note: For SegWit transactions, the txid is calculated without including witness data, which can differ from the transaction hash (see tx_hash). Example: 4d3c2b1a0f9e8d7c6b5a4e3d2c1b0a9876543210fedcba9876543210fedcba98 See: https://bitcoin.stackexchange.com/questions/77699/whats-the-difference-between-txid-and-hash-getrawtransaction-bitcoind | | INDEX | NUMBER | The index (INTEGER) of the transaction within the block, starting from 0 for the first transaction in the block. Example: 2 | | TX_HASH | TEXT | The transaction hash (STRING), a 64-character hexadecimal value that uniquely identifies a Bitcoin transaction. This hash is computed by double SHA-256 hashing of the transaction data and serves as the primary identifier for transactions across the Bitcoin network. For SegWit transactions, the transaction hash (wtxid) includes witness data, while the txid does not, which may result in different hash values for the same transaction. This field is essential for transaction verification, blockchain explorers, and cross-referencing transactions across different systems. Example: 9f8e7d6c5b4a3b2c1d0e1f2a3b4c5d6e7f8g9000000000000000a16b7e2e3b2c | | HEX | TEXT | The serialized, hex-encoded (STRING) raw transaction data as it appears on-chain. This field contains the full transaction in hexadecimal format. Example: 0100000001abcdef… (truncated) | | FEE | FLOAT | The transaction fee (FLOAT, BTC) paid by this transaction, calculated as the difference between the sum of input values and the sum of output values. In Bitcoin’s UTXO model, transaction fees are the primary incentive for miners to include transactions in blocks. Higher fees increase the likelihood of faster confirmation as miners prioritize transactions with better fee-to-size ratios. Fees are essential for network security and help prevent spam transactions. The fee market is dynamic and responds to network congestion and blockspace demand. Example: 0.00004567 | | IS_COINBASE | BOOLEAN | Boolean (TRUE/FALSE) indicating if this transaction is a coinbase transaction. Coinbase transactions are the first transaction in each block and are used to reward miners with newly minted Bitcoin and transaction fees. Example: TRUE | | COINBASE | TEXT | Hex-encoded (STRING) coinbase input data. Present only for coinbase transactions (the first transaction in a block). This field contains arbitrary data used by miners and may include extra nonce or mining pool information. Example: 03a2c50804b886c65b2f426974636f696e2f | | INPUTS | ARRAY | An array of input objects (ARRAY<STRUCT>) for this transaction. Each object represents a transaction input (vin), including fields such as previous output reference, scriptSig, and sequence number. Example: [ {"txid": "...", "vout": 0, "scriptSig": { ... }, "sequence": 4294967295 }, ... ] | | INPUT_COUNT | NUMBER | The number of inputs (INTEGER) in the transaction (length of the inputs/vin array). Example: 3 | | INPUT_VALUE | NUMBER | The total value (FLOAT, BTC) of all inputs in this transaction. Calculated as the sum of the value of each input, expressed in BTC. Example: 0.12345678 | | INPUT_VALUE_SATS | NUMBER | The total value (INTEGER, Satoshis) of all inputs or outputs in this transaction, depending on context. Satoshis are the smallest unit of Bitcoin, with 1 BTC = 100,000,000 Satoshis. Using satoshis provides the highest precision for Bitcoin value calculations and avoids floating-point arithmetic issues that can occur with decimal BTC values. This field is essential for precise financial calculations, especially when dealing with small amounts or when aggregating large numbers of transactions where precision is critical. Example: 12345678 | | OUTPUTS | ARRAY | An array of output objects (ARRAY<STRUCT>) for this transaction. Each object represents a transaction output (vout), including fields such as value, scriptPubKey, and output index. Example: [ {"value": 0.5, "n": 0, "scriptPubKey": { ... } }, ... ] | | OUTPUT_COUNT | NUMBER | The number of outputs (INTEGER) in the transaction (length of the outputs/vout array). Example: 2 | | OUTPUT_VALUE | NUMBER | The total value (FLOAT, BTC) of all outputs in this transaction. Calculated as the sum of the value of each output, expressed in BTC. Example: 0.12341111 | | OUTPUT_VALUE_SATS | NUMBER | The total value (INTEGER, Satoshis) of all inputs or outputs in this transaction, depending on context. Satoshis are the smallest unit of Bitcoin, with 1 BTC = 100,000,000 Satoshis. Using satoshis provides the highest precision for Bitcoin value calculations and avoids floating-point arithmetic issues that can occur with decimal BTC values. This field is essential for precise financial calculations, especially when dealing with small amounts or when aggregating large numbers of transactions where precision is critical. Example: 12345678 | | SIZE | NUMBER | The size (INTEGER, bytes) of the transaction as it appears on-chain. Example: 225 | | VIRTUAL_SIZE | TEXT | The virtual size (INTEGER, vbytes) of the transaction. For SegWit transactions, this is the weight divided by 4, which may differ from the raw size in bytes. Example: 141 | | WEIGHT | TEXT | The transaction’s weight (INTEGER, weight units). Calculated as (strippedsize * 3) + totalsize, or equivalently, virtual_size * 4. Used for fee calculation and block size limits in Bitcoin. Example: 564 | | LOCK_TIME | TEXT | The lock time (INTEGER) for this transaction. Specifies the earliest time or block height when the transaction may be added to the blockchain. If 0, the transaction is valid immediately. Example: 0 | | VERSION | NUMBER | The transaction version (INTEGER). Indicates the version of the transaction format used. Most Bitcoin transactions use version 1 or 2. Example: 2 | | FACT_TRANSACTIONS_ID | TEXT | A unique identifier (STRING or NUMBER) for the record, used as a primary key for this table. Not a blockchain-native field. Example: 123456789 | | INSERTED_TIMESTAMP | TIMESTAMP_LTZ | The timestamp (TIMESTAMP_NTZ, UTC) when this record was initially created and inserted into the table. Example: 2024-06-01 12:34:56.000 | | MODIFIED_TIMESTAMP | TIMESTAMP_LTZ | The timestamp (TIMESTAMP_NTZ, UTC) when this record was last modified by an internal process. Example: 2024-06-01 12:35:00.000 |