Historical Balances

​

Question it answers

“What was wallet 0x…‘s balance of token 0x… at block N, for any past block? And who held the token back then?”

​

What you get

​

Source

​

Destination

​

Full schema

CREATE TABLE recipe_historical_balance_at_block.fact_balance_observations
(
    vendor_event_id   String,
    ingested_at       DateTime64(3),
    chain_id          UInt32,
    block_hash        String,
    block_number      UInt64,
    log_index         UInt32,
    event_ts          DateTime64(3),
    tx_hash           String,
    token_address     String,
    wallet_address    String,
    balance           String,                   -- absolute post-transfer balance, raw uint256
    leg               LowCardinality(String),   -- 'from' | 'to'
    sign              Int8,
    INDEX bf_token token_address TYPE bloom_filter(0.01) GRANULARITY 4
)
ENGINE = ReplicatedCollapsingMergeTree(
    '/clickhouse/tables/{database}/fact_balance_observations', '{replica}', sign)
PARTITION BY (chain_id, toYYYYMM(event_ts))
ORDER BY (chain_id, wallet_address, token_address, block_number, log_index, vendor_event_id, leg);

CREATE TABLE public.wallet_token_balance_history (
  position         BIGINT          NOT NULL,
  log_index        BIGINT          NOT NULL,
  block_number     BIGINT          NOT NULL,
  block_timestamp  BIGINT          NOT NULL,   -- unix seconds
  tx_hash          TEXT            NOT NULL,
  token_address    TEXT            NOT NULL,
  wallet_address   TEXT            NOT NULL,
  balance          NUMERIC(76, 0)  NOT NULL,   -- absolute post-transfer balance
  leg              TEXT            NOT NULL,    -- 'from' | 'to'
  vendor_event_id  TEXT            NOT NULL,
  UNIQUE (vendor_event_id, leg)
);

-- As-of-block recency index: leads with (wallet, token) so a DISTINCT ON with a
-- block_number cap avoids a sort and range-scans on block_number.
CREATE INDEX wtbh_wallet_token_recency_idx
  ON public.wallet_token_balance_history
  (wallet_address, token_address, block_number DESC, log_index DESC);

​

Run it

• Docker and Docker Compose.
• A Data Feeds key, a cm_live_… key with continuum:read scope (this is not your legacy Web3 API key).

​

Example reads

SELECT argMax(balance, (block_number, log_index)) AS balance_at_n
FROM recipe_historical_balance_at_block.fact_balance_observations FINAL
WHERE chain_id = 1
  AND wallet_address = lower('0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045')
  AND token_address  = lower('0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48')
  AND block_number  <= {N:UInt64};

SELECT wallet_address,
       argMax(balance, (block_number, log_index)) AS balance_at_n
FROM recipe_historical_balance_at_block.fact_balance_observations FINAL
WHERE chain_id = 1
  AND token_address = lower('0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48')
  AND block_number <= {N:UInt64}
GROUP BY wallet_address
HAVING balance_at_n != '0' AND balance_at_n != ''
ORDER BY toFloat64OrZero(balance_at_n) DESC
LIMIT 100;

SELECT DISTINCT ON (token_address)
       token_address, balance
FROM public.wallet_token_balance_history
WHERE wallet_address = lower('0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045')
  AND block_number <= $1
ORDER BY token_address, block_number DESC, log_index DESC;

​

Other ways to consume this data

• Your existing message clients: stream it with Kafka / AMQP / SQS.
• REST / Arrow Flight: pull the decoded block stream directly.
• Your warehouse: query it via Iceberg from Snowflake, BigQuery, Spark, or DuckDB.

​

Modes

​

Multichain

​

Fidelity gaps

• balance_formatted: needs the token’s decimals (contract metadata, not in tokenTransfers). Only the raw balance is emitted; divide by 10^decimals off-stream, sourcing decimals from a Token Metadata sync.
• No USD value: this recipe lands quantities. Join a price feed at read time if you want historical USD.
• Native balance at block: not covered in v1; the same pattern over nativeTransfers post-balances would unlock it.

​

Migrating from the REST API

​

EVM: historical token holders

GET /erc20/:tokenAddress/holders/historical

SELECT countIf(balance_at_n != '0' AND balance_at_n != '') AS total_holders
FROM (
    SELECT wallet_address,
           argMax(balance, (block_number, log_index)) AS balance_at_n
    FROM recipe_historical_balance_at_block.fact_balance_observations FINAL
    WHERE chain_id = 1
      AND token_address = lower('0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48')
      AND block_number <= {bucket_end_block:UInt64}
    GROUP BY wallet_address
);

• Materialize, don’t recompute. Large tokens have millions of holders, compute total_holders per bucket on a schedule and store the small series; the deltas are then trivial.
• No fixed timeFrame. Every observation is retained, so the same log serves hourly, daily, or custom windows.
• Tiers and acquisition are conventions you set: a feature, not a black box, but budget time to define them.
• Backfill from the token’s deployment block, not chain genesis, the recipe is token-scoped for this question.

​

Solana: historical holders

GET /token/:network/holders/:address/historical

SELECT wallet_address,
       argMax(balance, (block_number, log_index)) AS balance_at_n
FROM recipe_historical_balance_at_block.fact_balance_observations FINAL
WHERE token_address = lower('<MINT>') AND block_number <= {N:UInt64}
GROUP BY wallet_address
HAVING balance_at_n != '0' AND balance_at_n != '';

• SPL account model. Count owner wallets, summing a wallet’s token accounts for its per-owner balance.
• Backfill from the mint’s deployment: a short window gives wrong historical counts.
• Slot boundaries. Resolve each interval boundary to a block/slot before running the as-of query.

​

Related