Accounts Snapshot

Table Metadata

Property	Configuration
Natural Key(s)	account_id, valid_from
Partition Field(s)	valid_to (MONTH partition)
Clustered Field(s)	account_id
Documentation	dbt docs

Column Details

Name	Description	Data Type	Domain Values	Required?	Notes
account_id	The unique identifier of the account.	STRING		Yes
balance	The number of units of XLM held by the account.	FLOAT		Yes	The accounts table only reports monetary balances for XLM. Any other asset class is reported in the trust_lines table.
buying_liabilities	The sum of all buy offers owned by this account for XLM only.	FLOAT		Yes	The accounts table only reports monetary balances for XLM. Any other asset class is reported in the trust_lines table. For buy offers, the account must hold the amount of asset to complete the transaction.
selling_liabilities	The sum of all sell offers owned by this account for XLM only.	FLOAT		Yes	The accounts table only reports monetary balances for XLM. Any other asset class is reported in the trust_lines table.
sequence_number	The account's current sequence number. The sequence number controls operations applied to an account. Operations must submit a unique sequence number that is incremented by 1.	INTEGER		Yes	Natural Key. Required Field.
num_subentries	The total number of ledger entries connected to this account. Ledger entries include: trustlines, offers, signers, and data entries.	INTEGER		Yes	Each entry on a ledger takes up space, which is expensive to store. Minimum balance is calculated by (2 + num_subentries - num_sponsoring + num_sponsored) * 0.5XLM.
inflation_destination	Deprecated: The account address to receive an inflation payment when disbursed.	STRING		Yes	Inflation was discontinued in 2019 by validator vote.
flags	Denotes enabling/disabling of certain asset issuer privileges.	INTEGER	0 - None, Default 1 - Auth Required (all trustlines by default are untrusted and require manual trust established) 2 - Auth Revocable (allows trustlines to be revoked if account no longer trusts asset) 4 - Auth Immutable (all auth flags are read only when set) 8 - Auth Clawback Enabled (asset can be clawed back from the user)	Yes	Flags can have values: 0=None, 1=Auth Required, 2=Auth Revocable, 4=Auth Immutable, 8=Auth Clawback Enabled.
home_domain	URL of home domain linked to wallet.	STRING		Yes
master_weight	The weight of the master key, which is the private key for this account.	INTEGER	Integers from 1 to 255	Yes	If master key = 0, account is locked.
threshold_low	The sum of the weight of all signatures required for low threshold operations.	INTEGER		Yes	Low: Allow Trust, Set Trust Line Flags, Bump Sequence, Claim Claimable Balance.
threshold_medium	The sum of the weight of all signatures required for medium threshold operations.	INTEGER		Yes	Medium: Everything Else.
threshold_high	The sum of the weight of all signatures required for high threshold operations.	INTEGER		Yes	High: Account Merge, Set Options.
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	0 - Ledger Entry Created 1 - Ledger Entry Updated 2 - Ledger Entry Deleted 3 - Ledger Entry State (value of the entry)	Yes	Values: 0=Created, 1=Updated (not valid for claimable balances), 2=Deleted.
deleted	Indicates whether the ledger entry has been deleted.	BOOLEAN		Yes	Once deleted, cannot be recovered.
sponsor	The account address of the sponsor paying the reserves for this ledger entry.	STRING		No	Sponsors can be accounts, signers, claimable balances, trust lines.
num_sponsored	The number of reserves sponsored for this account.	INTEGER		No	Defaults to 0.
num_sponsoring	The number of reserves sponsored by this account.	INTEGER		No	Defaults to 0.
sequence_ledger	The unsigned 32-bit ledger number of the sequence number's age.	INTEGER		No	Reflects the last time an account touched its sequence number.
sequence_time		TIMESTAMP		No
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	Represents the interval of ledgers processed.
closed_at	Timestamp in UTC when this ledger closed and committed.	TIMESTAMP		Yes	Ledgers close ~every 5 seconds.
ledger_sequence	The sequence number of this ledger.	INTEGER		Yes	Cluster Field. Unique per ledger.
batch_insert_ts	The timestamp in UTC when a batch of records was inserted into the database.	TIMESTAMP		Yes	Useful for data engineering, not for ad hoc analysis.
airflow_start_ts	The timestamp when the airflow job starts.	STRING		Yes	Used to detect backfill jobs.
valid_from	The timestamp when this snapshot row was first inserted and became effective.	TIMESTAMP		Yes	Helps track 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

1. Point-in-time query (as-of balance)

SELECT account_id, balance
FROM `crypto-stellar.snapshots.accounts_snapshot`
WHERE account_id = 'ACC123'
  DATE('2025-01-15') BETWEEN DATE(valid_from) AND COALESCE(DATE(valid_to), '9999-12-31')
LIMIT 100

2. Active snapshot (current records only)

SELECT account_id, balance, home_domain
FROM `crypto-stellar.snapshots.accounts_snapshot`
WHERE valid_to IS NULL
LIMIT 100

3. Changes in February 2025 (partition pruning)

SELECT account_id, COUNT(*) AS num_changes
FROM `crypto-stellar.snapshots.accounts_snapshot`
WHERE DATE_TRUNC(valid_to, MONTH) = '2025-02-01'
GROUP BY account_id
LIMIT 100