## `balances_<chain>.daily_updates`

`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](https://docs.dune.com/data-catalog/curated/balances/evm-daily-updates#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`](https://docs.dune.com/data-catalog/curated/utilities/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:**

```sql
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:**

```sql
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`](https://docs.dune.com/data-catalog/curated/cex-flows/overview), a curated table of known centralized-exchange wallets. The same pattern works for any cohort table or uploaded address list.

```sql
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
```