Table Details
| Property | Value |
|---|---|
| Table Name | ethereum.assets.balances |
| Table Status | Production-Ready |
| Unique Key | block_timestamp, unique_id |
Table Columns
| Column Name | Data Type | Description |
|---|---|---|
| token_type | VARCHAR(16777216) | Token standard for the asset. One of: erc20 (fungible), erc721 (non-fungible), erc1155 (semi-fungible), or the native currency symbol (e.g. eth, matic, bnb). |
| address | VARCHAR(42) | Wallet or contract address of the account. On EVM chains, a 42-character hex string (0x-prefixed). On other chains, the native address format applies. |
| token_address | VARCHAR(42) | Contract address of the token. For EVM chains, native currency (e.g. ETH, MATIC) is represented as the zero address (0x0000000000000000000000000000000000000000). For Solana, this is the mint address. |
| token_name | VARCHAR(16777216) | Full name of the token (e.g. “USD Coin”, “Wrapped Ether”). |
| token_symbol | VARCHAR(16777216) | Ticker symbol of the token (e.g. “USDC”, “WETH”). |
| token_id | VARCHAR(16777216) | Identifier for non-fungible tokens (ERC-721) or semi-fungible tokens (ERC-1155). Each unique token_id within a collection represents a distinct asset. |
| raw_balance | FLOAT | Token balance in the token’s smallest unit (not decimal-adjusted). Use balance for the human-readable normalized value. |
| raw_balance_str | VARCHAR(16777216) | Token balance in the smallest unit, stored as a string for full precision. Avoids floating-point truncation for tokens with very large integer balances. |
| balance | FLOAT | Sum of end-of-day token balances for this address_type. |
| balance_str | VARCHAR(16777216) | Normalized token balance stored as a string for full precision. Equivalent to balance but avoids floating-point truncation. |
| usd_balance | FLOAT | USD value of the token balance, computed using the exchange rate at the time of the balance snapshot. |
| usd_exchange_rate | FLOAT | USD price per unit of the token at the time of the event, used to compute usd_amount. Sourced from Allium’s hourly price feed. |
| block_timestamp | TIMESTAMP_NTZ(9) | Timestamp (UTC) of the block that contains this record. |
| block_number | NUMBER(38,0) | Sequential number of the block that contains this record. Starts at 0 (genesis block) and increments by 1 for each new block. |
| block_hash | VARCHAR(66) | Cryptographic hash of the block header that contains this record. Uniquely identifies a block. |
| unique_id | VARCHAR(16777216) | Allium’s deterministic unique identifier for this row. Generated from the fields that uniquely identify the record (e.g. transaction hash + log index). Stable across full refreshes. |
| _created_at | TIMESTAMP_NTZ(9) | Timestamp of when the entry was created in the database. |
| _updated_at | TIMESTAMP_NTZ(9) | Timestamp of when the entry was last updated in the database. |
Generating Daily Balances
How to generate daily balances from block-level balances table.Generating Daily Balances from Block-level Balances
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