​
Concept

Balances tell the amount of assets held by each address at a specific block height. We track balances across all major token standards:

  • Native tokens (e.g., ETH, MATIC)

  • ERC20 tokens (e.g., USDC, WETH)

  • ERC721 tokens (NFTs)

  • ERC1155 tokens (Multi-tokens)

​
Balance Tracking Methodology

For Fungible Tokens (Native & ERC20):

  1. We detect balance-changing events (transfers, mints, burns)

  2. At each relevant block height, we read the actual on-chain balance

  3. This ensures accuracy by capturing any non-standard balance modifications

For Non-Fungible Tokens (ERC721 & ERC1155):

  1. We track transfers through standard events:

    • ERC721: Transfer() events

    • ERC1155: TransferSingle() and TransferBatch() events

  2. Build credit-debit tables to calculate cumulative ownership

  3. Account for minting, burning, and transfers between addresses

​
Precision Handling

To maintain numerical precision, especially for tokens with large supplies or many decimal places:

  1. Raw balances are stored as VARCHAR to preserve exact values

  2. Normalized balances are calculated using high-precision arithmetic

  3. Custom UDFs (User-Defined Functions) handle mathematical operations

  4. Both raw and human-readable balances are provided

This approach ensures:

  • No loss of precision from floating-point arithmetic

  • Accurate balance tracking over time

  • Reliable historical data for analysis

​
Sample Query

How to generate daily balances from block-level balances table.

Generating Daily Balances from Block-level Balances

-- Parameters to alter:

-- [1] wallet_addresses: list of wallet addresses, comma-separated

-- e.g: 0x915867061ea708869e34819216ae726041f48739, 0x0d37eb1528e7313a3954f25d4d4aead0dc7ff037

-- [2] token_address: token contract of interest, comma-separated  

-- e.g: 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48, 0xdac17f958d2ee523a2206206994597c13d831ec7

-- [3] time_granularity: time granularity of address: e.g. hour / day / month

-- [4] blockchain: blockchain of interest: e.g. ethereum 

-- [5] balances_table: name of balances table: e.g. erc20_balances 



WITH

    wallets AS ( 

        SELECT DISTINCT value::varchar AS wallet_address

        FROM

            (SELECT split(replace('{{wallet_addresses}}', ' ', ''), ',') AS addresses),

            LATERAL FLATTEN(input => addresses)

    ),

    tokens AS ( 

        SELECT DISTINCT value::varchar AS token_address

        FROM

            (SELECT split(replace('{{token_address}}', ' ', ''), ',') AS addresses),

            LATERAL FLATTEN(input => addresses)

    ),

    balances AS ( 

        SELECT

            date_trunc('{{time_granularity}}', b.block_timestamp) AS date,

            b.block_number,

            b.address,

            b.raw_balance,

            b.usd_balance,

            b.balance,

            b.token_address,

            b.token_name,

            b.token_symbol

        FROM {{blockchain}}.assets.{{balances_table}} b

        INNER JOIN wallets ON b.address = wallets.wallet_address

        INNER JOIN tokens ON b.token_address = tokens.token_address

        QUALIFY

            row_number() OVER (

                PARTITION BY b.address, b.token_address, date ORDER BY b.block_number DESC

            ) = 1

    ),

    distinct_address_tokens AS ( -- Generate a list of address x token

        SELECT DISTINCT address, token_address FROM balances

    ),

    distinct_dates AS ( -- Generate a list of timestamps

        SELECT date_trunc('{{time_granularity}}', timestamp) AS date, count(1) AS count_all

        FROM {{blockchain}}.raw.blocks

        WHERE timestamp >= (SELECT min(date) FROM balances)

        GROUP BY date

    ),

    date_address_token_cte AS (  -- Generate timestamp x address x tokens 

        SELECT t1.date, t2.address, t2.token_address

        FROM distinct_dates t1, distinct_address_tokens t2

    ),

    final AS (

        SELECT

            t1.date,

            t1.address,

            lag(t2.token_address) ignore nulls over (partition by t1.address, t1.token_address order by t1.date) AS token_address,

            lag(t2.token_name) ignore nulls over (partition by t1.address, t1.token_address order by t1.date) AS token_name,

            lag(t2.token_symbol) ignore nulls over (partition by t1.address, t1.token_address order by t1.date) AS token_symbol,

            lag(t2.usd_balance) ignore nulls over (partition by t1.address, t1.token_address order by t1.date) AS usd_balance,

            lag(t2.raw_balance) ignore nulls over (partition by t1.address, t1.token_address order by t1.date) AS raw_balance,

            lag(t2.balance) ignore nulls over (partition by t1.address, t1.token_address order by t1.date) AS balance

        FROM date_address_token_cte t1

        LEFT JOIN balances t2

        ON t1.date = t2.date

        AND t1.address = t2.address

        AND t1.token_address = t2.token_address

        WHERE 1 = 1

    )

SELECT 

    date,

    token_address,

    token_name,

    token_symbol,

    usd_balance,

    raw_balance,

    balance

FROM final

WHERE raw_balance > 0

ORDER BY date DESC