Trustlines Snapshot

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: 4-char, 12-char, or native (XLM).	STRING	credit_alphanum4 credit_alphanum12 native	Yes	XLM has no issuer or asset code, shown as native.
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	0 - None, Default 1 - Authorized 2 - Authorized to Maintain Liabilities 4 - Clawback Enabled	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	0 - Ledger Entry Created 1 - Ledger Entry Updated 2 - Ledger Entry Deleted 3 - Ledger Entry State (value of the entry)	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

1. 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;