Table Metadata
| Property | Configuration |
|---|---|
| Natural Key(s) | liquidity_pool_id, valid_from |
| Partition Field(s) | valid_to (MONTH partition) |
| Clustered Field(s) | liquidity_pool_id |
| Documentation | dbt docs |
Column Details
| Name | Description | Data Type | Domain Values | Required? | Notes |
|---|---|---|---|---|---|
| liquidity_pool_id | Unique identifier for a liquidity pool. | STRING | Yes | Natural Key. Cluster Field. Cannot be duplicated for same asset pair. | |
| type | Mechanism that calculates pricing and division of shares for the pool. | STRING | constant_product | Yes | Initially only constant product pools are supported. See CAP-38. |
| fee | Basis points charged as trade fee. | INTEGER | Default = 30 | Yes | Fees are distributed immediately to accounts as transactions complete. |
| trustline_count | Total number of accounts with trustlines authorized to the pool. | INTEGER | Yes | Revoking authorization on an asset will withdraw accounts from all pools with that asset. | |
| pool_share_count | Total number of pool shares representing participation in the liquidity pool. | FLOAT | Yes | Shares are not transferable. Can only be adjusted via deposit/withdraw. | |
| asset_a_type | Asset type for one side of the pool (sold asset). | STRING |
| Yes | XLM has type = ‘native’. No asset code/issuer. |
| asset_a_code | Asset code (4 or 12 chars) for sold asset in trade. | STRING | No | Asset codes alone are not unique. Combination with issuer + type is unique. | |
| asset_a_issuer | Account address of the original issuer of the sold asset. | STRING | No | ||
| asset_a_id | Farm Hash encoding of asset code + issuer + type for asset A. | INTEGER | No | ||
| asset_a_amount | Raw number of tokens locked in the pool for asset A. | FLOAT | Yes | ||
| asset_b_type | Asset type for the other side of the pool. | STRING |
| Yes | XLM has type = ‘native’. |
| asset_b_code | Asset code (4 or 12 chars) for other asset in trade. | STRING | No | Asset codes alone are not unique. Combination with issuer + type is unique. | |
| asset_b_issuer | Account address of the original issuer of the other asset. | STRING | No | ||
| asset_b_id | Farm Hash encoding of asset code + issuer + type for asset B. | INTEGER | No | ||
| asset_b_amount | Raw number of tokens locked in the pool for asset B. | FLOAT | Yes | ||
| last_modified_ledger | Ledger sequence number when the pool 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. | INTEGER |
| Yes | Updates not valid for claimable balances. |
| deleted | Indicates whether the liquidity pool entry was deleted. | BOOLEAN | true, false | Yes | Deleted entries 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 | Start date for the batch interval. | DATETIME | Yes | Proxy for closed_at. | |
| closed_at | Timestamp in UTC when ledger closed and committed. | TIMESTAMP | Yes | Ledgers close ~every 5 seconds. | |
| ledger_sequence | Ledger sequence number. | INTEGER | Yes | Represents order of ledger within Stellar blockchain. | |
| batch_insert_ts | Timestamp in UTC when batch was inserted into DB. | TIMESTAMP | Yes | Used for engineering/debugging, not analysis. | |
| airflow_start_ts | Timestamp when airflow job started. | STRING | Yes | Helps detect backfill loads. | |
| valid_from | Timestamp when this snapshot row became effective. | TIMESTAMP | Yes | Helps track changes over time. | |
| valid_to | Timestamp when this row is no longer valid. | TIMESTAMP | No | Null = still active. |
Example Business Questions
- What was the total liquidity (asset_a_amount + asset_b_amount) across all pools on a given date?
SELECT
DATE('2025-01-15') AS as_of_date,
SUM(asset_a_amount + asset_b_amount) AS total_liquidity
FROM `crypto-stellar.snapshots.liquidity_pools_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;