Table Metadata
| Property | Configuration |
|---|---|
| Natural Key(s) | contract_id, ledger_key_hash, valid_from |
| Partition Field(s) | valid_to (MONTH partition) |
| Clustered Field(s) | contract_id, ledger_key_hash |
| Documentation | dbt docs |
Column Details
| Name | Description | Data Type | Domain Values | Required? | Notes |
|---|---|---|---|---|---|
| contract_id | Soroban contract id. | STRING | Yes | ||
| contract_key_type | Contract key type which is an ScVal. | STRING | ScValTypeScvContractInstance, ScValTypeScvLedgerKeyContractInstance, ScValTypeScvLedgerKeyNonce | No | |
| contract_durability | Contract can either be temporary or persistent. | STRING | No | ||
| asset_code | The 4 or 12 character code representation of the asset on the network. | STRING | No | Asset codes have no guarantees of uniqueness. The combination of asset code, issuer and type represents a distinct asset. | |
| asset_issuer | The account address of the original asset issuer that created the asset. | STRING | No | ||
| asset_type | The identifier for type of asset code, can be alphanumeric (4 or 12 characters) or the native asset to the network, XLM. | STRING | native, alphanum4, alphanum12 | No | XLM is the native asset. XLM has no asset code or issuer and is displayed with asset type = ‘native’. |
| balance_holder | The address/account that holds the balance of the asset in contract data. | STRING | No | ||
| balance | The number of units of XLM held by the account. | STRING | No | The accounts table only reports balances for XLM. Any other asset class is reported in trust_lines. | |
| last_modified_ledger | The ledger sequence number when the ledger entry was last modified. | INTEGER | Yes | Natural Key. Cluster Field. Deletion does not count as modification. | |
| ledger_entry_change | Code describing the ledger entry change type applied. | INTEGER |
| Yes | Not every entry can be updated. Updates not valid for claimable balances. |
| ledger_sequence | INTEGER | Yes | |||
| ledger_key_hash | STRING | Yes | |||
| key | The encoded key used to identify a specific piece of contract data. Has two components: type and value. | JSON | No | ||
| key_decoded | The human-readable/decoded version of the key. | JSON | No | ||
| val | The encoded value associated with the key in the contract data. Has two components: type and value. | JSON | No | ||
| val_decoded | The human-readable/decoded version of the value. | JSON | No | ||
| contract_data_xdr | The XDR (External Data Representation) encoding of the contract data. | STRING | No | XDR ensures interoperability across systems. | |
| closed_at | Timestamp in UTC when this ledger closed and committed. | TIMESTAMP | Yes | Ledgers close ~every 5 seconds. | |
| deleted | Indicates whether the ledger entry has been deleted. | BOOLEAN | Yes | Once deleted, cannot be recovered. History is maintained. | |
| batch_id | String representation of the run id for a given DAG in Airflow. Takes the form of scheduled__<batch_end_date>-<dag_alias>. Batch ids are unique to the batch and help with monitoring and rerun capabilities | string | Yes | ||
| batch_run_date | The start date for the batch interval. | DATETIME | Yes | Proxy for closed_at for a ledger. | |
| batch_insert_ts | The timestamp in UTC when a batch of records was inserted. | TIMESTAMP | Yes | For data engineering purposes, not ad hoc analysis. | |
| airflow_start_ts | The timestamp when the airflow job starts. | STRING | No | Used to detect backfill jobs. | |
| valid_from | The timestamp when this snapshot row was first inserted and became effective. | TIMESTAMP | Yes | Tracks changes over time. | |
| valid_to | The timestamp when this row is no longer valid. | TIMESTAMP | No | If null, the setting is currently active. |
Example Business Questions
- Point-in-time query (balance/value as of a given date)
SELECT contract_id, ledger_key_hash, val_decoded
FROM `crypto-stellar.snapshots.contract_data_snapshot`
WHERE contract_id = 'C123'
AND ledger_key_hash = 'abc123'
DATE('2025-02-10') BETWEEN DATE(valid_from) AND COALESCE(DATE(valid_to), '9999-12-31')
LIMIT 100
- All active contracts (latest state only)
SELECT contract_id, ledger_key_hash, val_decoded, balance, contract_durability
FROM `crypto-stellar.snapshots.contract_data_snapshot`
WHERE valid_to IS NULL
LIMIT 100
- Recent updates
SELECT contract_id, ledger_key_hash, closed_at, ledger_entry_change
FROM `crypto-stellar.snapshots.contract_data_snapshot`
WHERE closed_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY)
LIMIT 100