Skip to main content
Schema: bitcoin.core Table: fact_outputs Type: View

​
Description

This table contains detailed information about all transaction outputs in the Bitcoin blockchain, representing the newly created UTXOs that result from each transaction. Each row represents a single output with complete metadata including destination address, script information, value data, and spending conditions. The model includes comprehensive script analysis and address information for all output types, making it essential for understanding Bitcoin’s UTXO model and tracking the creation of spendable funds across the network.

​
Key Use Cases

  • UTXO creation analysis and new fund tracking across the Bitcoin network
  • Address receiving pattern analysis and fund accumulation studies
  • Script type analysis and Bitcoin protocol feature adoption tracking
  • Output value distribution analysis and economic studies
  • Address clustering and entity identification through receiving patterns
  • Change output analysis and transaction structure studies
  • Fund flow analysis and destination address tracking

​
Important Relationships

  • Primary source for output-level analytics in the Bitcoin ecosystem
  • Referenced by core.fact_transactions to provide output details for transaction analysis
  • Links to core.fact_inputs when outputs are spent in future transactions
  • Supports core.fact_clustered_transfers for entity-level transfer tracking
  • Used by core.dim_entity_clusters for address clustering and entity identification
  • Enables integration with core.dim_labels for categorized output analysis

​
Commonly-used Fields

  • block_timestamp: Essential for time-series analysis and trend detection
  • tx_id: Critical for linking outputs to their containing transactions
  • index: Important for identifying specific outputs within transactions
  • value and value_sats: Key metrics for value analysis and economic studies
  • pubkey_script_address: Essential for address-based analytics and entity tracking
  • pubkey_script_type: Important for protocol feature analysis and script usage patterns
  • output_id: Critical for unique identification and relationship mapping

​
Columns

Column NameData TypeDescription
BLOCK_TIMESTAMPTIMESTAMP_NTZThe 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 output index, which corresponds with the vout output number in a transaction input. | | PUBKEY_SCRIPT_ASM | TEXT | The script public key as a string. | | PUBKEY_SCRIPT_HEX | TEXT | The hex of the script’s public key in string format | | PUBKEY_SCRIPT_ADDRESS | TEXT | A Bitcoin address associated with the transaction, output, label, etc. | | PUBKEY_SCRIPT_TYPE | TEXT | The type of script, e.g. pubkeyhash, multisig. | | PUBKEY_SCRIPT_DESC | TEXT | The descriptor for the output script of the pubkey. | | VALUE | NUMBER | The value, expressed in Bitcoin. | | 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 | | OUTPUT_ID | TEXT | A surrogate key for outputs which is a hash of the transaction id and the index of the output in the transaction using dbt’s generate surrogate key macro. | | FACT_OUTPUTS_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 |