Table Metadata
| Property | Configuration |
|---|---|
| Natural Key(s) | account_id, asset_type, asset_issuer, asset_code, liquidity_pool_id, valid_from |
| Partition Field(s) | valid_to (MONTH partition) |
| Clustered Field(s) | account_id, asset_id |
| Documentation | dbt docs |
Column Details
| Name | Description | Data Type | Domain Values | Required? | Notes |
|---|---|---|---|---|---|
| ledger_key | Unique ledger key when the trust line state last changed. | STRING | Yes | Natural Key. | |
| account_id | Unique identifier of the account. | STRING | Yes | ||
| asset_type | Type of asset code held by the trustline: 4-char, 12-char, or a liquidity pool share. | STRING |
| Yes | Trustlines never report native. XLM is held directly on the account and is reported in the accounts table, not here. pool_share identifies a trustline holding shares in a liquidity pool. |
| asset_issuer | Account address of the original asset issuer. | STRING | No | ||
| asset_code | 4- or 12-character code of the asset. | STRING | No | Asset uniqueness = code + issuer + type. | |
| asset_id | Encoded asset identifier. | INTEGER | No | Derived from asset code + issuer + type. | |
| liquidity_pool_id | Unique identifier for a liquidity pool. | STRING | Yes | Natural Key. Cluster Field. | |
| balance | Number of units of asset held by this account. | FLOAT | Yes | ||
| trust_line_limit | Maximum amount of this asset that the account accepts. | INTEGER | Yes | Set when opening trust line. | |
| buying_liabilities | Sum of buy offers owned by this account (XLM only). | FLOAT | Yes | Other assets reported in trust_lines. | |
| selling_liabilities | Sum of sell offers owned by this account (XLM only). | FLOAT | Yes | Other assets reported in trust_lines. | |
| flags | Issuer privilege flags applied to the asset. | INTEGER |
| Yes | Flags originate from issuer account. |
| last_modified_ledger | Ledger sequence number when entry was last modified. | INTEGER | Yes | Natural Key. Cluster Field. Not a proxy for deletion. | |
| ledger_entry_change | Code for ledger entry change type. | INTEGER |
| Yes | Some ledger entries cannot be updated. |
| deleted | Indicates if the ledger entry was deleted. | BOOLEAN | true, false | Yes | Deleted entries remain in history. |
| sponsor | Account paying reserves for this ledger entry. | STRING | No | Can sponsor accounts, signers, claimable balances, trust lines. | |
| 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 | Start date of batch interval. | DATETIME | Yes | Proxy for closed_at. | |
| closed_at | Ledger close timestamp in UTC. | TIMESTAMP | Yes | Ledgers close ~every 5s. | |
| ledger_sequence | Sequence number of this ledger. | INTEGER | Yes | Cluster Field. Unique, increments per ledger. | |
| batch_insert_ts | UTC timestamp when batch of records was inserted. | TIMESTAMP | Yes | For engineering/backfill analysis only. | |
| airflow_start_ts | Airflow task start timestamp. | STRING | Yes | ||
| valid_from | Timestamp when row became effective. | TIMESTAMP | Yes | Tracks historical changes. | |
| valid_to | Timestamp when row is no longer valid. | TIMESTAMP | No | Null = currently active. |
Example Business Questions
- What was the balance of each account’s trustlines on a given date?
SELECT
account_id,
asset_code,
asset_issuer,
balance
FROM `crypto-stellar.snapshots.trustlines_snapshot`
WHERE DATE(valid_from) <= DATE('2025-01-15')
AND (DATE(valid_to) > DATE('2025-01-15') OR valid_to IS NULL)
AND deleted = FALSE
AND liquidity_pool_id = ''
LIMIT 100;