Skip to main content

Liquidity Pools Snapshot

Table Metadata​

PropertyConfiguration
Natural Key(s)liquidity_pool_id, valid_from
Partition Field(s)valid_to (MONTH partition)
Clustered Field(s)liquidity_pool_id
Documentationdbt docs

Column Details​

NameDescriptionData TypeDomain ValuesRequired?Notes
liquidity_pool_idUnique identifier for a liquidity pool.STRINGYesNatural Key. Cluster Field. Cannot be duplicated for same asset pair.
typeMechanism that calculates pricing and division of shares for the pool.STRINGconstant_productYesInitially only constant product pools are supported. See Cap-38.
feeBasis points charged as trade fee.INTEGERDefault = 30YesFees are distributed immediately to accounts as transactions complete.
trustline_countTotal number of accounts with trustlines authorized to the pool.INTEGERYesRevoking authorization on an asset will withdraw accounts from all pools with that asset.
pool_share_countTotal number of pool shares representing participation in the liquidity pool.FLOATYesShares are not transferable. Can only be adjusted via deposit/withdraw.
asset_a_typeAsset type for one side of the pool (sold asset).STRING
  • credit_alphanum4
  • credit_alphanum12
  • native
YesXLM has type = 'native'. No asset code/issuer.
asset_a_codeAsset code (4 or 12 chars) for sold asset in trade.STRINGNoAsset codes alone are not unique. Combination with issuer + type is unique.
asset_a_issuerAccount address of the original issuer of the sold asset.STRINGNo
asset_a_idFarm Hash encoding of asset code + issuer + type for asset A.INTEGERNo
asset_a_amountRaw number of tokens locked in the pool for asset A.FLOATYes
asset_b_typeAsset type for the other side of the pool.STRING
  • credit_alphanum4
  • credit_alphanum12
  • native
YesXLM has type = 'native'.
asset_b_codeAsset code (4 or 12 chars) for other asset in trade.STRINGNoAsset codes alone are not unique. Combination with issuer + type is unique.
asset_b_issuerAccount address of the original issuer of the other asset.STRINGNo
asset_b_idFarm Hash encoding of asset code + issuer + type for asset B.INTEGERNo
asset_b_amountRaw number of tokens locked in the pool for asset B.FLOATYes
last_modified_ledgerLedger sequence number when the pool entry was last modified.INTEGERYesNatural Key. Cluster Field. Deletion does not count as modification.
ledger_entry_changeCode describing the 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)
YesUpdates not valid for claimable balances.
deletedIndicates whether the liquidity pool entry was deleted.BOOLEANtrue, falseYesDeleted entries cannot be recovered; history is maintained.
batch_idString 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 capabilitiesstringYes
batch_run_dateStart date for the batch interval.DATETIMEYesProxy for closed_at.
closed_atTimestamp in UTC when ledger closed and committed.TIMESTAMPYesLedgers close ~every 5 seconds.
ledger_sequenceLedger sequence number.INTEGERYesRepresents order of ledger within Stellar blockchain.
batch_insert_tsTimestamp in UTC when batch was inserted into DB.TIMESTAMPYesUsed for engineering/debugging, not analysis.
airflow_start_tsTimestamp when airflow job started.STRINGYesHelps detect backfill loads.
valid_fromTimestamp when this snapshot row became effective.TIMESTAMPYesHelps track changes over time.
valid_toTimestamp when this row is no longer valid.TIMESTAMPNoNull = still active.

Example Business Questions​

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