Token Balances by Wallet

​

Question it answers

“Give me every token wallet 0x… holds, with its current non-zero balance.”

​

What you get

​

Source

​

Destination

​

Full schema

CREATE TABLE recipe_token_balances_by_wallet.fact_balances_by_wallet
(
    vendor_event_id   String,
    ingested_at       DateTime64(3),
    chain_id          UInt32,
    block_hash        String,
    block_number      UInt64,
    log_index         UInt32,
    event_ts          DateTime64(3),
    token_address     String,
    wallet_address    String,
    balance           String,                   -- absolute post-transfer balance, raw uint256
    leg               LowCardinality(String),   -- from | to
    sign              Int8
)
ENGINE = ReplicatedCollapsingMergeTree(
    '/clickhouse/tables/{database}/fact_balances_by_wallet', '{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);

-- 1. Observations (sink target): one row per wallet, per transfer leg.
CREATE TABLE public.token_balance_observations (
  position         BIGINT  NOT NULL,
  log_index        BIGINT  NOT NULL,
  block_number     BIGINT  NOT NULL,
  block_timestamp  BIGINT  NOT NULL,         -- unix seconds
  token_address    TEXT    NOT NULL,
  wallet_address   TEXT    NOT NULL,
  balance          NUMERIC(76, 0) NOT NULL,
  leg              TEXT    NOT NULL,
  vendor_event_id  TEXT    NOT NULL
);

-- Recency index leads with the DISTINCT ON keys so the REFRESH avoids a sort.
CREATE INDEX tbo_token_wallet_recency_idx
  ON public.token_balance_observations
  (token_address, wallet_address, block_number DESC, log_index DESC);

-- 2. Current-balance materialized view: latest observation per (token, wallet).
CREATE MATERIALIZED VIEW public.token_balances AS
SELECT DISTINCT ON (token_address, wallet_address)
  token_address,
  wallet_address,
  balance,
  block_number,
  log_index,
  block_timestamp
FROM public.token_balance_observations
ORDER BY token_address, wallet_address, block_number DESC, log_index DESC;

CREATE UNIQUE INDEX token_balances_pk
  ON public.token_balances (token_address, wallet_address);

-- This recipe's primary access path: active balances held by a wallet.
CREATE INDEX token_balances_by_wallet_active_idx
  ON public.token_balances (wallet_address, token_address)
  WHERE balance > 0;

-- Sibling access path (all non-zero holders of a token).
CREATE INDEX token_balances_by_token_active_idx
  ON public.token_balances (token_address, wallet_address)
  WHERE balance > 0;

-- Cleanup index for zeroed positions.
CREATE INDEX token_balances_zero_cleanup_idx
  ON public.token_balances (wallet_address, token_address)
  WHERE balance = 0;

​

Run it

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

​

Example reads

SELECT token_address,
       argMax(balance, (block_number, log_index)) AS current_balance
FROM recipe_token_balances_by_wallet.fact_balances_by_wallet FINAL
WHERE chain_id = 1 AND wallet_address = lower('0x...')
GROUP BY token_address
HAVING current_balance != '0' AND current_balance != ''
ORDER BY token_address;

SELECT token_address, balance FROM public.token_balances
WHERE wallet_address = lower('0x...') AND balance > 0
ORDER BY balance DESC;

SELECT token_address, balance FROM token_balances
WHERE wallet_address = LOWER('0x...') AND balance > 0
ORDER BY balance 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

• Raw balances only. balance is the raw uint256 post-transfer amount; divide by 10^token_decimals to read human units. Fold in a decimals lookup from a Token Metadata sync if you need it pre-scaled.
• No USD value. This recipe lands quantities, not dollar value. Join to a Token Prices sync at read time for portfolio valuation.
• Latest-wins semantics. A (wallet, token) row reflects the most recent transfer-derived post-balance. Direct mints/burns or rebases that emit a transfer are captured; balance changes with no transfer event are not.

​

Migrating from the REST API

​

GET /wallets/:address/tokens

CREATE VIEW wallet_token_balances_price AS
WITH latest_price AS (
  SELECT DISTINCT ON (token_address)
         token_address, usd_price
  FROM   token_price_updates
  ORDER  BY token_address, block_number DESC      -- newest price per token
)
SELECT
  b.wallet_address,
  b.token_address,
  m.symbol,
  m.name,
  m.decimals,
  b.balance                                          AS balance_raw,
  b.balance / power(10, m.decimals)                  AS balance_formatted,
  p.usd_price,
  (b.balance / power(10, m.decimals)) * p.usd_price  AS usd_value,
  m.total_supply,
  b.balance / NULLIF(m.total_supply, 0)              AS percentage_relative_to_total_supply
FROM token_balances b
LEFT JOIN token_metadata m ON m.token_address = b.token_address
LEFT JOIN latest_price   p ON p.token_address = b.token_address
WHERE b.balance > 0;

• USD values come from real DEX trades: very close to the old endpoint, but compare with a tolerance, not bit-for-bit. A thinly-traded token may not have a price yet.
• No fixed spam filter: you get every token the wallet holds, then apply your own spam/dust rules.
• No wallet-size limit: the old endpoint caps very large wallets; your own dataset has none.
• Balances are current-state on Postgres/MySQL; use ClickHouse for real-time/hybrid.

​

GET /:address/erc20

• Everything onchain is exact: no pricing engine in the loop, nothing to reconcile with a tolerance.
• ERC-20 only, matching the REST endpoint. For native (ETH) balance, add Native Balances.
• No wallet-size limit: the old endpoint errors on very large wallets (“too many ERC20 token balances”); your dataset doesn’t, and you get real keyset pagination on top.

​

GET /wallets/:address/net-worth

CREATE VIEW wallet_net_worth AS
WITH tokens AS (
  SELECT wallet_address,
         sum(usd_value) AS token_balance_usd        -- exclude what shouldn't count (see gotchas)
  FROM   wallet_token_balances_price
  WHERE  usd_value IS NOT NULL
  GROUP  BY wallet_address
),
native AS (
  SELECT n.wallet_address,
         n.balance / 1e18                       AS native_balance_formatted,
         n.balance / 1e18 * p.usd_price         AS native_balance_usd
  FROM   native_balances n
  LEFT JOIN LATERAL (
    SELECT usd_price FROM token_price_updates
    WHERE token_address = lower('0xC02aaa39b223FE8D0A0e5C4F27eAD9083C756Cc2')  -- WETH = native mark
    ORDER BY block_number DESC LIMIT 1
  ) p ON true
)
SELECT
  coalesce(t.wallet_address, n.wallet_address)            AS wallet_address,
  n.native_balance_formatted,
  n.native_balance_usd,
  coalesce(t.token_balance_usd, 0)                        AS token_balance_usd,
  coalesce(t.token_balance_usd, 0) + coalesce(n.native_balance_usd, 0) AS networth_usd
FROM tokens t
FULL JOIN native n ON n.wallet_address = t.wallet_address;

• Curating the token set is the whole game. One spam or bogus-priced token can dominate the total. The old exclude_spam / exclude_unverified_contracts / min_pair_side_liquidity_usd filters become your own WHERE in the tokens CTE; here they’re essential, not polish.
• Stale prices: a token that hasn’t traded recently has no fresh mark. Decide whether to value it at zero, carry forward the last mark, or exclude it. Each choice moves the total.
• Cross-chain net worth: each chain is its own feed. Compute per chain and sum networth_usd into total_networth_usd.
• Compare with a tolerance: USD comes from real DEX trades, very close to the old figure, not identical.
• A missed holding silently changes the total, so full-history backfill (see the note in Run it) matters even more here.

​

Related