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

​
What

​
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_NUMBERNUMBERThe 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_HASHTEXTThe 64-character hexadecimal hash (STRING) of the block containing this transaction. This value uniquely identifies the block in the Bitcoin blockchain. Example: 0000000000000000000a16b7e2e3b2c1d4e5f6a7b8c9d0e1f2a3b4c5d6e7f8g9
TX_IDTEXTThe 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
INDEXNUMBERThe output index, which corresponds with the vout output number in a transaction input.
IS_COINBASEBOOLEANBoolean (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
COINBASETEXTHex-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_ASMTEXTThe script’s public key in the form of a string.
SCRIPT_SIG_HEXTEXTThe hex-encoded witness data.
SEQUENCENUMBERThe sequence number of the script.
SPENT_BLOCK_NUMBERNUMBERThe block number from the spent transaction input, corresponds with the block in which spenttxid occurred.
SPENT_TX_IDTEXTThe tx_id of the consumed output.
SPENT_OUTPUT_INDEXNUMBERThe index of the output in the transaction for the spent tx output
PUBKEY_SCRIPT_ASMTEXTThe script public key as a string.
PUBKEY_SCRIPT_HEXTEXTThe hex of the script’s public key in string format
PUBKEY_SCRIPT_ADDRESSTEXTA Bitcoin address associated with the transaction, output, label, etc.
PUBKEY_SCRIPT_TYPETEXTThe type of script, e.g. pubkeyhash, multisig.
PUBKEY_SCRIPT_DESCTEXTThe descriptor for the output script of the pubkey.
VALUENUMBERThe value, expressed in Bitcoin.
VALUE_SATSNUMBERThe 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 w…
TX_IN_WITNESSARRAYAn array of hex-encoded witness data.
INPUT_IDTEXTA 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_IDTEXTA unique identifier (STRING or NUMBER) for the record, used as a primary key for this table. Not a blockchain-native field. Example: 123456789
INSERTED_TIMESTAMPTIMESTAMP_LTZThe timestamp (TIMESTAMP_NTZ, UTC) when this record was initially created and inserted into the table. Example: 2024-06-01 12:34:56.000
MODIFIED_TIMESTAMPTIMESTAMP_LTZThe timestamp (TIMESTAMP_NTZ, UTC) when this record was last modified by an internal process. Example: 2024-06-01 12:35:00.000