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

​
Description

This table contains detailed information about all transaction inputs in the Bitcoin blockchain, representing the spent UTXOs that fund each transaction. Each row represents a single input with complete metadata including the source transaction, output index, script information, and value data. The model includes both regular transaction inputs and coinbase inputs (mining rewards), with comprehensive script analysis and address information. This table is essential for understanding Bitcoin’s UTXO model and tracing the flow of funds across the network.

​
Key Use Cases

  • UTXO tracing and transaction flow analysis across the Bitcoin network
  • Address behavior analysis and spending pattern recognition
  • Coinbase transaction analysis and mining reward tracking
  • Script type analysis and Bitcoin protocol feature usage
  • Input age analysis and coin consolidation studies
  • Forensic analysis and transaction graph reconstruction
  • Address clustering and entity identification through spending patterns

​
Important Relationships

  • Primary source for input-level analytics in the Bitcoin ecosystem
  • Referenced by core.fact_transactions to provide input details for transaction analysis
  • Links to core.fact_outputs through spent transaction and output index relationships
  • 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 input analysis

​
Commonly-used Fields

  • block_timestamp: Essential for time-series analysis and trend detection
  • tx_id: Critical for linking inputs to their containing transactions
  • spent_tx_id and spent_output_index: Core fields for UTXO tracing and flow analysis
  • value and value_sats: Key metrics for value flow 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
  • is_coinbase: Critical for distinguishing mining rewards from regular transaction inputs

​
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. | | 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 | | SCRIPT_SIG_ASM | TEXT | The script’s public key in the form of a string. | | SCRIPT_SIG_HEX | TEXT | The hex-encoded witness data. | | SEQUENCE | NUMBER | The sequence number of the script. | | SPENT_BLOCK_NUMBER | NUMBER | The block number from the spent transaction input, corresponds with the block in which spent_tx_id occurred. | | SPENT_TX_ID | TEXT | The tx_id of the consumed output. | | SPENT_OUTPUT_INDEX | NUMBER | The index of the output in the transaction for the spent tx output | | 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 | | TX_IN_WITNESS | ARRAY | An array of hex-encoded witness data. | | INPUT_ID | TEXT | A surrogate key for inputs which is a hash of the transaction id and the index of the input in the transaction using dbt’s generate surrogate key macro. | | FACT_INPUTS_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 |