EVM Daily Balance Updates

Sparse validity-interval table for historical fungible token balances on EVM networks.

balances_<chain>.daily_updates is the default table for historical balance analysis. It is sparse: one row is written per (address, token_address) balance change, not one row per day per pair. Each row carries a validity interval [valid_from, valid_to) covering the days during which that balance was held. Adjacent intervals share the boundary day on valid_from / valid_to (no gap, no overlap). Refreshed hourly.

The interval layout lets you reconstruct the balance for any (address, token_address) on any past day by selecting the row where valid_from <= day < valid_to. To get per-day rows across a window, expand the interval against a calendar table (see Example Queries).

valid_to = '9999-12-31' marks the active (ongoing) interval. USD value is joined from prices.day at valid_from. Zero-balance periods are kept as their own intervals, so filter balance > 0 if you want to exclude empty holdings.

Table Schema

Column	Type	Description
blockchain	varchar	Chain identifier
address_prefix	varchar	First byte of address, lower-hex (partition key)
address	varbinary	Account address
token_address	varbinary	Token contract address. Native tokens use the standardized address from dune.blockchains (zero address on most EVMs, 0x0000…1010 on Polygon).
token_standard	varchar	native or erc20
token_symbol	varchar	Token symbol from tokens_<chain>.erc20 (nullable)
balance_raw	uint256	Balance in raw units while the interval is valid
balance	double	Balance in display units (balance_raw / 10^decimals, falls back to 18 decimals when unknown)
balance_usd	double	balance * prices.day.price at valid_from. NULL when no price is available for that token; coalesce(balance_usd, 0) if you need to sum across mixed-coverage tokens.
valid_from	date	First day of the interval (inclusive)
valid_to	date	First day after the interval (exclusive). 9999-12-31 for the active interval.
balance_updated_at	timestamp	Block time of the underlying balance update

Usage Notes

The table is partitioned by address_prefix, the first byte of address rendered as a 2-character lower-hex string ('00'..'ff'). For best performance, include all three of:

1. A cohort filter on address and/or token_address.
2. A partition filter address_prefix IN (...) matching the addresses in your cohort. For a hardcoded address the prefix is the first hex byte (e.g. 0xd8dA… → 'd8'). For a dynamic cohort, materialize the distinct prefixes in a subquery so the planner can prune partitions: address_prefix IN (SELECT DISTINCT lower(to_hex(varbinary_substring(address, 1, 1))) FROM <cohort>).
3. Day bounds on the interval: valid_from <= <end_date> AND valid_to > <start_date>. These let the scanner skip files that fall outside the window. The active row (valid_to = '9999-12-31') is naturally included by valid_to > <start_date>.

To expand validity intervals into per-day rows, the recommended pattern is to join the utils.days calendar table. For very large outputs (hundreds of millions of rows), CROSS JOIN UNNEST(sequence(...)) clips the day sequence at generation time and is typically faster.

Example Queries

Daily USDC balance for a wallet, last 30 days:

WITH days AS (
    SELECT CAST(timestamp AS DATE) AS day
    FROM utils.days
    WHERE timestamp BETWEEN
        CAST(CURRENT_DATE - INTERVAL '30' day AS TIMESTAMP)
        AND CAST(CURRENT_DATE AS TIMESTAMP)
)
SELECT d.day, i.balance, i.balance_usd
FROM balances_ethereum.daily_updates i
JOIN days d
    ON d.day >= i.valid_from
   AND d.day <  i.valid_to
WHERE i.address        = 0xd8da6bf26964af9d7eed9e03e53415d37aa96045
  AND i.address_prefix = 'd8'
  AND i.token_address  = 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48
  AND i.valid_from <= CURRENT_DATE
  AND i.valid_to   >  CURRENT_DATE - INTERVAL '30' day
ORDER BY d.day

Daily portfolio value for a wallet over a fixed window:

WITH days AS (
    SELECT CAST(timestamp AS DATE) AS day
    FROM utils.days
    WHERE timestamp BETWEEN TIMESTAMP '2025-01-01 00:00:00' AND TIMESTAMP '2025-12-31 00:00:00'
)
SELECT d.day, sum(i.balance_usd) AS portfolio_usd
FROM balances_ethereum.daily_updates i
JOIN days d
    ON d.day >= i.valid_from
   AND d.day <  i.valid_to
WHERE i.address       = 0xd8da6bf26964af9d7eed9e03e53415d37aa96045
  AND i.address_prefix = 'd8'
  AND i.valid_from <= DATE '2025-12-31'
  AND i.valid_to   >  DATE '2025-01-01'
GROUP BY d.day
ORDER BY d.day

Total USDC held by centralized exchanges over time (CEX stablecoin float):

The cohort is cex.addresses, a curated table of known centralized-exchange wallets. The same pattern works for any cohort table or uploaded address list.

WITH cex_wallets AS (
    SELECT address FROM cex.addresses WHERE blockchain = 'ethereum'
),
days AS (
    SELECT CAST(timestamp AS DATE) AS day
    FROM utils.days
    WHERE timestamp BETWEEN TIMESTAMP '2025-01-01 00:00:00' AND TIMESTAMP '2025-12-31 00:00:00'
)
SELECT d.day, sum(i.balance) AS usdc_at_cexes
FROM balances_ethereum.daily_updates i
JOIN cex_wallets c ON i.address = c.address
JOIN days d
    ON d.day >= i.valid_from
   AND d.day <  i.valid_to
WHERE i.token_address = 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48
  AND i.address_prefix IN (
        SELECT DISTINCT lower(to_hex(varbinary_substring(address, 1, 1)))
        FROM cex_wallets
      )
  AND i.valid_from <= DATE '2025-12-31'
  AND i.valid_to   >  DATE '2025-01-01'
GROUP BY d.day
ORDER BY d.day