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 account’s subentry count, a weighted total rather than a row count. Subentries are the account’s trustlines, offers, signers, and data entries. Each offer, signer, data entry, or ordinary trustline adds 1. A pool share trustline adds 2 and therefore requires two base reserves (see CAP-38). | 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) * base_reserve, currently 0.5 XLM. | |
| 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 |
| 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 0 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 |
| 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
- 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
- Active snapshot (current records only)
SELECT account_id, balance, home_domain
FROM `crypto-stellar.snapshots.accounts_snapshot`
WHERE valid_to IS NULL
LIMIT 100
- 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