Evicted Keys Snapshot

Table Metadata

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

Column Details

Name	Description	Data Type	Domain Values	Required?	Notes
ledger_key_hash	Hash of the ledgerKey, which is a subset of the ledgerEntry. The subset of ledgerEntry fields depends on ledgerEntryType.	STRING		Yes
closed_at	Timestamp in UTC when this ledger closed and committed.	TIMESTAMP		Yes	Ledgers are expected to close ~every 5 seconds.
is_evicted	Status flag indicating whether an entry is evicted or not.	BOOLEAN	false, true	Yes	false = key has been restored.
ledger_sequence	The sequence number of this ledger, representing its order in the Stellar blockchain.	INTEGER		Yes	Cluster Field. Unique per ledger.
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. Evicted keys as of today

SELECT ledger_key_hash, is_evicted
FROM `crypto-stellar.snapshots.evicted_keys_snapshot`
WHERE valid_to IS NULL
  AND is_evicted = TRUE
LIMIT 100

2. Point-in-time eviction status

SELECT ledger_key_hash, is_evicted
FROM `crypto-stellar.snapshots.evicted_keys_snapshot`
WHERE ledger_key_hash = 'abc123'
  AND AND DATE('2025-03-10') BETWEEN DATE(valid_from) AND COALESCE(DATE(valid_to), '9999-12-31')
LIMIT 100

3. Monthly Key Restorations

WITH with_lag AS (
  SELECT
    ledger_key_hash,
    valid_from,
    is_evicted,
    LAG(is_evicted) OVER (
      PARTITION BY ledger_key_hash
      ORDER BY valid_from
    ) AS prev_status
  FROM `crypto-stellar.snapshots.evicted_keys_snapshot`
)

SELECT
  DATE_TRUNC(valid_from, MONTH) AS month,
  COUNTIF(prev_status = TRUE AND is_evicted = FALSE) AS restorations_count
FROM with_lag
GROUP BY month
ORDER BY month
LIMIT 100