Skip to main content
Schema: ethereum.balances Table: ez_balances_erc20_daily Type: Base Table

​
What

This table provides daily ERC20 token balance snapshots for verified token contracts by making direct balanceOf contract calls at the end of each day. It captures the current token balance for each address-token pair that has had transfer activity, providing a comprehensive view of token holdings across all verified ERC20 tokens with decimal adjustments and USD valuations where available. Historical ERC20 balances data available, starting from 2025-06-10.

​
Key Use Cases

  • Daily portfolio tracking and balance monitoring for ERC20 tokens
  • Historical balance analysis and trend identification
  • Token holder distribution analysis at daily granularity
  • Wallet balance snapshots for reporting and analytics
  • Cross-token balance comparisons and concentration analysis
  • Token supply distribution monitoring over time
  • Daily balance-based yield and return calculations

​
Important Relationships

  • Join with fact_blocks: Use block_number for block metadata and timestamps
  • Join with dim_labels: Use address for entity identification and categorization
  • Join with dim_contracts: Use contract_address for token contract details
  • Join with ez_prices_hourly: USD valuations already included but can be refreshed
  • Join with ez_balances_native_daily: Compare with native token daily balances
  • Join with ez_token_transfers: Compare daily balances with transfer activity

​
Commonly-used Fields

  • address: The account address holding the token balance
  • contract_address: The ERC20 token contract address
  • symbol: Token symbol (USDC, WETH, etc.)
  • balance: Token balance at end of day, decimal adjusted to standard units
  • balance_usd: USD value of the token balance at end of day
  • balance_raw: Raw balance in smallest token unit (wei equivalent)
  • balance_precise: Precise decimal-adjusted balance as string
  • decimals: Number of decimal places for the token
  • block_date: The date for which this balance snapshot was taken

​
Sample queries

Daily Token Holdings by Address 
SELECT
    block_date,
    address,
    symbol,
    balance,
    balance_usd,
    contract_address
FROM ethereum.balances.ez_balances_erc20_daily
WHERE address = LOWER('0x1234567890123456789012345678901234567890')
    AND block_date >= CURRENT_DATE - 30
    AND balance > 0
ORDER BY block_date DESC, balance_usd DESC;
Token Holder Count Trends 
SELECT
    block_date,
    symbol,
    contract_address,
    COUNT(DISTINCT address) AS holder_count,
    SUM(balance) AS total_supply_tracked,
    AVG(balance) AS avg_balance,
    MEDIAN(balance) AS median_balance
FROM ethereum.balances.ez_balances_erc20_daily
WHERE block_date >= CURRENT_DATE - 90
    AND balance > 0
    AND symbol IS NOT NULL
GROUP BY 1, 2, 3
ORDER BY 1 DESC, holder_count DESC;
Portfolio Value Evolution 
-- Track portfolio value changes over time for specific addresses
SELECT
    block_date,
    address,
    COUNT(DISTINCT contract_address) AS token_count,
    SUM(balance_usd) AS total_portfolio_usd,
    STRING_AGG(
        CASE WHEN balance_usd > 100
        THEN symbol || ': $' || ROUND(balance_usd, 2)
        END, ', '
    ) AS major_holdings
FROM ethereum.balances.ez_balances_erc20_daily
WHERE address IN (
    SELECT DISTINCT address
    FROM ethereum.balances.ez_balances_erc20_daily
    WHERE balance_usd > 10000
    LIMIT 100
)
    AND block_date >= CURRENT_DATE - 30
    AND balance > 0
GROUP BY 1, 2
HAVING total_portfolio_usd > 1000
ORDER BY 1 DESC, total_portfolio_usd DESC;
Token Distribution Analysis 
-- Analyze token concentration and distribution patterns
SELECT
    symbol,
    contract_address,
    block_date,
    COUNT(DISTINCT address) AS total_holders,
    COUNT(DISTINCT CASE WHEN balance >= 1000 THEN address END) AS holders_1k_plus,
    COUNT(DISTINCT CASE WHEN balance >= 10000 THEN address END) AS holders_10k_plus,
    MAX(balance) AS max_balance,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY balance) AS p95_balance,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY balance) AS median_balance
FROM ethereum.balances.ez_balances_erc20_daily
WHERE block_date = CURRENT_DATE - 1
    AND balance > 0
    AND symbol IS NOT NULL
GROUP BY 1, 2, 3
HAVING total_holders >= 100
ORDER BY total_holders DESC
LIMIT 50;
Daily Balance Changes 
-- Compare daily balances to identify significant changes
WITH daily_changes AS (
    SELECT
        address,
        contract_address,
        symbol,
        block_date,
        balance,
        balance_usd,
        LAG(balance) OVER (
            PARTITION BY address, contract_address
            ORDER BY block_date
        ) AS prev_balance,
        LAG(balance_usd) OVER (
            PARTITION BY address, contract_address
            ORDER BY block_date
        ) AS prev_balance_usd
    FROM ethereum.balances.ez_balances_erc20_daily
    WHERE block_date >= CURRENT_DATE - 7
        AND balance > 0
)
SELECT
    block_date,
    address,
    symbol,
    balance,
    prev_balance,
    balance - prev_balance AS balance_change,
    balance_usd - prev_balance_usd AS balance_change_usd,
    CASE
        WHEN prev_balance > 0
        THEN ((balance - prev_balance) / prev_balance) * 100
        ELSE NULL
    END AS pct_change
FROM daily_changes
WHERE ABS(balance_change_usd) > 1000
    AND prev_balance IS NOT NULL
ORDER BY ABS(balance_change_usd) DESC
LIMIT 100;

​
Columns

Column NameData TypeDescription
BLOCK_NUMBERNUMBERSequential counter representing the position of a block in the blockchain since genesis (block 0).
BLOCK_DATEDATEThe date for which this balance snapshot represents the end-of-day token balance.
ADDRESSTEXTThe account address whose token balance is recorded in this daily snapshot.
CONTRACT_ADDRESSTEXTThe ERC20 token contract address for which the balance is recorded.
DECIMALSNUMBERNumber of decimal places for the token, used for proper decimal adjustment in balance calculations.
SYMBOLTEXTThe token symbol for the ERC20 token.
BALANCE_HEXTEXTHexadecimal representation of the token balance as returned by the balanceOf contract call.
BALANCE_RAWNUMBERToken balance in the smallest unit (wei equivalent) without decimal adjustment, as returned by the contract.
BALANCE_PRECISETEXTToken balance with proper decimal adjustment, returned as a string to preserve precision.
BALANCEFLOATToken balance with decimal adjustment converted to a float for easier mathematical operations.
BALANCE_USDFLOATUSD value of the token balance at the end of the day, calculated using hourly price data.
EZ_BALANCES_ERC20_DAILY_IDTEXTPrimary key - unique identifier for each row ensuring data integrity.
INSERTED_TIMESTAMPTIMESTAMP_NTZUTC timestamp when the record was first added to the Flipside database.
MODIFIED_TIMESTAMPTIMESTAMP_NTZUTC timestamp of the most recent update to this record.
BLOCK_NUMBER - Key Facts:
  • Immutable once finalized
  • Primary ordering mechanism for blockchain data
  • Increments by 1 for each new block
  • Used as a proxy for time in many analyses
Usage in Queries: 
-- Recent data
WHERE block_number >= (SELECT MAX(block_number) - 1000 FROM fact_blocks)

-- Historical analysis
WHERE block_number BETWEEN 15000000 AND 16000000

-- Join across tables
JOIN ethereum.core.fact_event_logs USING (block_number)
Important: Block numbers are chain-specific. Block 15000000 on Ethereum ≠ block 15000000 on Polygon. EZ_BALANCES_ERC20_DAILY_ID: Format: Primary key typically generated using a composite of key columns like block_date, address, and contract_address to ensure uniqueness of each daily balance snapshot. Usage:
  • Deduplication in incremental loads
  • Join operations for data quality checks
  • Troubleshooting specific records
  • Identifying unique address-token-date combinations
INSERTED_TIMESTAMP: Format: TIMESTAMP_NTZ Use Cases:
  • Data freshness monitoring - check when balance snapshots were loaded
  • Incremental processing markers - identify latest loaded data
  • Debugging data pipeline issues
  • SLA tracking for daily balance updates
Query Examples: 
-- Check data freshness for recent balance snapshots
SELECT
    block_date,
    DATE_TRUNC('hour', inserted_timestamp) as insert_hour,
    COUNT(*) as records_inserted,
    MAX(inserted_timestamp) as latest_insert
FROM ethereum.balances.ez_balances_erc20_daily
WHERE block_date >= CURRENT_DATE - 7
GROUP BY 1, 2
ORDER BY 1 DESC, 2 DESC;

-- Find the latest balance snapshot date that has been loaded
SELECT
    MAX(block_date) as latest_balance_date,
    MAX(inserted_timestamp) as latest_insert_time,
    DATEDIFF('hour', MAX(block_date), MAX(inserted_timestamp)) as hours_since_balance_date
FROM ethereum.balances.ez_balances_erc20_daily;
MODIFIED_TIMESTAMP: Format: TIMESTAMP_NTZ Triggers for Updates:
  • Data corrections - fixing incorrect balance calculations
  • Enrichment additions - adding missing token metadata
  • Reprocessing for accuracy - recalculation of balances or USD values
  • Schema migrations - structural changes to the table
Monitoring Usage: 
-- Recently modified balance records
SELECT
    block_date,
    address,
    contract_address,
    symbol,
    balance,
    inserted_timestamp,
    modified_timestamp,
    DATEDIFF('hour', inserted_timestamp, modified_timestamp) as hours_between_insert_and_modify
FROM ethereum.balances.ez_balances_erc20_daily
WHERE modified_timestamp > inserted_timestamp
    AND modified_timestamp >= CURRENT_DATE - 1
ORDER BY modified_timestamp DESC
LIMIT 100;

-- Data quality tracking - count modified records by date
SELECT
    DATE(modified_timestamp) as mod_date,
    COUNT(*) as records_updated,
    COUNT(DISTINCT block_date) as balance_dates_affected,
    COUNT(DISTINCT contract_address) as tokens_affected
FROM ethereum.balances.ez_balances_erc20_daily
WHERE modified_timestamp > inserted_timestamp
    AND modified_timestamp >= CURRENT_DATE - 30
GROUP BY 1
ORDER BY 1 DESC;