> ## Documentation Index
> Fetch the complete documentation index at: https://docs.moralis.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Historical Balances

> Reconstruct any wallet's balance of any token at any past block, and the full holder set of a token as of a block, from a retained per-(wallet, token) balance event log in your own database.

### 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?"

The latest-state balance recipes ([Token Balances by Wallet](/data-feeds/recipes/wallet/token-balances-by-wallet), [Token Holders](/data-feeds/recipes/token/token-holders)) keep only the *current* balance. This recipe keeps **every** observation, so an as-of-block balance is a point lookup and a token's **holder set at block N** is a single query.

### What you get

Token transfers carry the **absolute** post-transfer balance of both sides (`fromPostBalance` / `toPostBalance`). Each transfer becomes two per-wallet observations, and **all** of them are retained (not just the latest). The balance as of block N is the observation with the highest `(block_number, log_index)` at or below N.

| Column                      | Description                                                         |
| --------------------------- | ------------------------------------------------------------------- |
| `wallet_address`            | The holder, leading sort key                                        |
| `token_address`             | The token contract                                                  |
| `balance`                   | Absolute post-transfer balance, raw `uint256` as text               |
| `block_number`, `log_index` | Recency tuple, the latest at or below N wins                        |
| `leg`                       | `from` / `to`, which side of the transfer produced this observation |
| `tx_hash`, `event_ts`       | The transaction and block time                                      |
| `vendor_event_id`           | Stable per-observation identity                                     |

### Source

The transform reads one per-block array, `tokenTransfers`, and unpivots each transfer into `(from_address, from_post_balance)` and `(to_address, to_post_balance)`. Because the source supplies **absolute** post-balances, there's no running-sum reconstruction: the balance at any block is just the latest retained observation at or below it.

### Destination

| Destination                  | Table                                                  | As-of-block access                                                                                   |
| ---------------------------- | ------------------------------------------------------ | ---------------------------------------------------------------------------------------------------- |
| **ClickHouse** (first-class) | `fact_balance_observations`                            | `argMax(balance, (block_number, log_index))` over `FINAL` `WHERE block_number <= N`                  |
| **Postgres**                 | `wallet_token_balance_history` (append-only event log) | `DISTINCT ON (wallet, token) … WHERE block_number <= N ORDER BY … block_number DESC, log_index DESC` |
| **MySQL**                    | `wallet_token_balance_history` (append-only event log) | correlated subquery per `(wallet, token)` served by `(wallet, token, block_number, log_index)`       |

ClickHouse uses the collapsing log-table pattern (see the [recipes overview](/data-feeds/recipes/overview#destinations)) so chain reorganizations self-correct. The fact table's sort key is wallet-first, so a wallet's balance timeline is a contiguous range read; a bloom skip index on `token_address` serves the inverse "holders of token T" access path.

### Full schema

<Accordion title="ClickHouse, fact_balance_observations">
  ```sql theme={null}
  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);
  ```

  Read with `FINAL` (then `argMax`) or a sign-aware aggregate, never a bare `WHERE sign = 1`. A single-node setup can use `CollapsingMergeTree(sign)` without the replication path.
</Accordion>

<Accordion title="Postgres, wallet_token_balance_history">
  ```sql theme={null}
  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);
  ```

  MySQL is the same shape with `DECIMAL(76,0)`. `position` is the block-level cursor used during backfill.
</Accordion>

### Run it

Everything runs in **your** infrastructure: the sink writes into a database you own and you query it with plain SQL, no API and no per-call limits.

**Prerequisites**

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

<Card title="Sign up for a Data Feeds key" icon="key" href="https://admin.moralis.com">
  Log in to the Moralis admin panel to create your account and generate a `cm_live_…` key.
</Card>

**Start the sink**

Generate a **starter pack** for `historicalBalanceAtBlock` in the [Moralis Admin Panel](https://admin.moralis.com), pick your chain and destination database, then run it with Docker. The [quickstart](/data-feeds/migration/quickstart) walks through it end to end. Set the backfill window (`MORALIS_HISTORICAL_FROM_BLOCK`) as noted below.

<Note>
  **Backfill full history for correct point-in-time answers.** An as-of-block balance (or holder set) is only correct if the observation log goes back far enough, a wallet may have last moved the token long before block N. For token-scoped questions ("holders of token T at block N"), backfill from around the **token's deployment block**; for whole-wallet history, backfill from an early block. A recent-only window gives wrong historical answers. See [History & backfill](/data-feeds/concepts/history-and-backfill).
</Note>

Then run the example reads below to confirm rows are landing.

### Example reads

Balance of one `(wallet, token)` as of block N (ClickHouse):

```sql theme={null}
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};
```

A token's **holder set as of block N**, the historical-holders query, biggest first:

```sql theme={null}
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;
```

Postgres (as-of-block balance for a wallet, index-driven):

```sql theme={null}
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

The sink is the way to get **this retained balance log** (`fact_balance_observations` / `wallet_token_balance_history`), recommended for point-in-time queries you own. The *same underlying data* is also available as the **raw decoded feed**, if you'd rather retain balance history yourself:

* **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.

Those give you the **source array** (`tokenTransfers`, with absolute post-balances) to build the history yourself, not this retained log. All of them read the one data lake into **your** infrastructure. See [What are Data Feeds?](/data-feeds/concepts/what-are-data-feeds).

### Modes

Shipped defaults: **ClickHouse `hybrid`** (backfill → realtime), **Postgres / MySQL `historical`** (one-shot backfill). For live/reorg-safe ingestion, use ClickHouse, see the [overview](/data-feeds/recipes/overview#modes).

<Note>
  The append-only log on Postgres / MySQL isn't reorg-aware on its own. Run realtime/hybrid on **ClickHouse**, where the collapsing log table corrects reorgs per-block via `sign`; the Postgres / MySQL configs target `historical` backfill.
</Note>

### Multichain

The recipe is chain-parametrized via the `chain` setting, point it at any supported EVM chain or **Solana**. The Solana feed's `tokenTransfers` carry the same absolute `fromPostBalance` / `toPostBalance` fields, so the recipe produces an identically-shaped balance history; the `vendor_event_id` already folds in `(from, to, token, amount)` to stay row-unique under Solana's repeated `logIndex`.

### 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](/data-feeds/recipes/token/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.

On-chain primitives, wallet, token, raw post-event balance, block, log index, tx hash, are fully covered.

## Migrating from the REST API

This recipe replaces the legacy **historical token holders** endpoints on EVM and Solana.

<Warning>
  Both legacy endpoints below are **deprecated and sunset on July 31, 2026**. Migrate before that date.
</Warning>

Neither move is 1:1: the legacy endpoints returned a **pre-bucketed time series**, while this recipe lands the retained balance log that produces it. You resolve each bucket boundary to a block, count the as-of-block holder set, and diff adjacent buckets, and a **full backfill from the token's deployment block is required**, or early buckets undercount (see the backfill note in [Run it](#run-it) and [History & backfill](/data-feeds/concepts/history-and-backfill)).

### EVM: historical token holders

```
GET /erc20/:tokenAddress/holders/historical
```

The holder-count time series becomes an aggregation over this recipe's balance log: the [holder set as of block N](#example-reads) query, run per bucket boundary.

<Note>
  **This endpoint needs more than one recipe.** Counts and deltas come from this recipe alone, but `newHoldersByAcquisition` (swap / transfer / airdrop) classifies each new holder's first inbound event, which you build from [Token Transfers](/data-feeds/recipes/token/token-transfers) + [Swaps by Token](/data-feeds/recipes/markets/swaps-by-token).
</Note>

| Legacy field                                          | Data Feeds                                        | Fidelity        |
| ----------------------------------------------------- | ------------------------------------------------- | --------------- |
| `timestamp`                                           | your chosen bucket boundary, resolved to a block  | your definition |
| `totalHolders`                                        | count of the as-of-block holder set               | exact           |
| `netHolderChange`, `holderPercentChange`              | deltas between adjacent buckets                   | calculated      |
| `holdersIn` / `holdersOut` (whales … shrimps)         | size-tier flows on thresholds you set             | your definition |
| `newHoldersByAcquisition` (swap / transfer / airdrop) | classify new holders' first inbound transfer/swap | your definition |

Holder count as of a bucket's end block (ClickHouse, repeat per bucket, or drive from a calendar):

```sql theme={null}
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
);
```

Gotchas:

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

The same recipe, pointed at **Solana** in the Admin Panel wizard, replaces it, Solana's `tokenTransfers` carry the same absolute post-balances, so the balance log is identical in shape (see [Multichain](#multichain)). The field mapping matches the EVM table above, with the mint as `token_address`; `newHoldersByAcquisition` likewise needs [Token Transfers](/data-feeds/recipes/token/token-transfers) + [Swaps by Token](/data-feeds/recipes/markets/swaps-by-token) layered in.

Holder set as of a past block, the building block for each bucket (ClickHouse):

```sql theme={null}
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 != '';
```

Gotchas:

* **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

<Columns cols={2}>
  <Card title="Token Holders" href="/data-feeds/recipes/token/token-holders" icon="users">
    The latest-state holder set this time-travels.
  </Card>

  <Card title="Token Balances by Wallet" href="/data-feeds/recipes/wallet/token-balances-by-wallet" icon="wallet">
    Current balances per wallet, the latest-state sibling.
  </Card>
</Columns>