Skip to main content
Schema: core-chain.balances Table: ez_balances_erc20 Type: Base Table

​
What

This table tracks ERC20 token balance changes at the transaction level by capturing pre- and post-transaction states from contract storage slots. It uses state tracer data to show exactly how each address’s token balance changed during transaction execution for verified ERC20 tokens, including decimal adjustments and USD valuations, where available, for comprehensive token balance analysis. This data set includes both successful and failed transactions, as state may change regardless.

​
Key Use Cases

  • Tracking ERC20 token balance changes at transaction granularity
  • Analyzing token balance impacts of DeFi interactions and trades
  • Monitoring large token balance changes and whale activity
  • Calculating precise token balance evolution over time
  • Identifying addresses with significant token holdings
  • Debugging smart contract effects on token balances
  • Analyzing token distribution and concentration metrics

​
Important Relationships

  • Join with fact_transactions: Use tx_hash for transaction context
  • Join with fact_blocks: Use block_number for block metadata
  • Join with dim_labels: Use address for entity identification
  • Join with dim_contracts: Use contract_address for token contract details
  • Join with ez_token_transfers: Compare balance changes to transfer events
  • Join with ez_prices_hourly: USD valuations already included but can be refreshed
  • Join with ez_balances_native: Compare with native token balance changes

​
Commonly-used Fields

  • address: The account whose token balance changed
  • contract_address: The ERC20 token contract address
  • symbol: Token symbol (USDC, WETH, etc.)
  • pre_balance: Token balance before the transaction
  • post_balance: Token balance after the transaction
  • net_balance: The change in token balance (post - pre)
  • pre_balance_usd / post_balance_usd: USD values at time of transaction
  • decimals: Number of decimal places for the token
  • tx_hash: Transaction that caused the balance change

​
Sample queries

Daily ERC20 Token Balance Changes 
SELECT
    DATE_TRUNC('day', block_timestamp) AS day,
    symbol,
    COUNT(*) AS balance_changes,
    COUNT(DISTINCT address) AS unique_holders,
    SUM(ABS(net_balance)) AS total_balance_moved,
    SUM(net_balance) AS net_balance_change
FROM <blockchain_name>.balances.ez_balances_erc20
WHERE block_timestamp >= CURRENT_DATE - 30
    AND net_balance != 0
    AND symbol IS NOT NULL
GROUP BY 1, 2
ORDER BY 1 DESC, total_balance_moved DESC;
Token Holder Balance Evolution 
-- Track how a specific address's token balances changed over time
SELECT
    block_timestamp,
    tx_hash,
    contract_address,
    symbol,
    pre_balance,
    post_balance,
    net_balance,
    pre_balance_usd,
    post_balance_usd
FROM <blockchain_name>.balances.ez_balances_erc20
WHERE address = LOWER('0x1234567890123456789012345678901234567890')
    AND block_timestamp >= CURRENT_DATE - 30
    AND net_balance != 0
ORDER BY block_timestamp DESC;
DeFi Protocol Token Impact Analysis 
-- Analyze how DeFi interactions affect token balances
SELECT
    t.to_address AS protocol_address,
    b.symbol,
    COUNT(*) AS balance_changes,
    COUNT(DISTINCT b.address) AS unique_users,
    SUM(CASE WHEN b.net_balance > 0 THEN b.net_balance ELSE 0 END) AS total_gains,
    SUM(CASE WHEN b.net_balance < 0 THEN ABS(b.net_balance) ELSE 0 END) AS total_losses
FROM <blockchain_name>.balances.ez_balances_erc20 b
JOIN <blockchain_name>.core.fact_transactions t USING (tx_hash)
WHERE b.net_balance != 0
    AND t.to_address IN (SELECT address FROM dim_contracts)
    AND b.block_timestamp >= CURRENT_DATE - 7
    AND b.symbol IS NOT NULL
GROUP BY 1, 2
HAVING COUNT(*) > 10
ORDER BY total_gains + total_losses DESC
LIMIT 50;
Token Balance Distribution by Token 
-- Analyze current token holders and their balances
SELECT
    contract_address,
    symbol,
    COUNT(DISTINCT address) AS holder_count,
    SUM(post_balance) AS total_supply_tracked,
    AVG(post_balance) AS avg_balance,
    MAX(post_balance) AS max_balance
FROM <blockchain_name>.balances.ez_balances_erc20
WHERE block_timestamp >= CURRENT_DATE - 1
    AND post_balance > 0
    AND symbol IS NOT NULL
GROUP BY 1, 2
ORDER BY holder_count DESC
LIMIT 50;

​
Columns

Column NameData TypeDescription
BLOCK_NUMBERNUMBERSequential counter representing the position of a block in the blockchain since genesis (block 0).
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 <blockchain_name>.core.fact_event_logs USING (block_number)
Important: Block numbers are chain-specific. Block 15000000 on Ethereum ≠ block 15000000 on Polygon. | | BLOCK_TIMESTAMP | TIMESTAMP_NTZ | UTC timestamp when the block was produced by validators/miners. Format: TIMESTAMP_NTZ (no timezone) Precision: Second-level accuracy Reliability:
  • Set by block producer
  • Can have minor variations (±15 seconds)
  • Always increasing (newer blocks = later timestamps)
Best Practices: 
-- Time-based filtering (most efficient)
WHERE block_timestamp >= DATEADD('day', -7, CURRENT_TIMESTAMP)

-- Hourly aggregations
DATE_TRUNC('hour', block_timestamp) AS hour

-- UTC date extraction
DATE(block_timestamp) AS block_date
Note: Use for time-series analysis, but be aware that block production rates vary by chain. | | TX_POSITION | NUMBER | Zero-indexed position of transaction within its block. Insights:
  • Position 0: First transaction in block
  • MEV bots often target early positions
  • Bundle transactions appear consecutively
  • Useful for analyzing transaction ordering | | TX_HASH | TEXT | Unique 66-character identifier for the transaction.
Format: 0x + 64 hexadecimal characters Usage:
  • Primary key for transaction lookups
  • Join key for traces, logs, and token transfers
  • Immutable once confirmed
Example: 0x5c504ed432cb51138bcf09aa5e8a410dd4a1e204ef84bfed1be16dfba1b22060 | | TX_SUCCEEDED | BOOLEAN | Boolean indicator of whether the transaction that caused this balance change was successful. Example: true | | CONTRACT_ADDRESS | TEXT | The ERC20 token contract address whose balance changed in this transaction. Example: ‘0xa0b86a33e6eb88b4d81b15e4e60c8a5b776e3b7a’ | | DECIMALS | NUMBER | Number of decimal places for the token, used for proper decimal adjustment. Example: 6 | | SYMBOL | TEXT | The token symbol for the ERC20 token. Example: ‘USDC’ | | SLOT_NUMBER | NUMBER | The storage slot number used to track balances for this ERC20 token contract. Example: 0 | | ADDRESS | TEXT | The account address whose token balance changed in this transaction. Example: ‘0x1234567890123456789012345678901234567890’ | | STORAGE_KEY | TEXT | | | PRE_BALANCE_HEX | TEXT | Hexadecimal representation of the pre-transaction balance as stored in the contract’s storage slot. Example: ‘0x3b9aca00’ | | PRE_BALANCE_RAW | TEXT | Token balance before transaction in smallest unit, no decimal adjustment. Example: 1000500000 | | PRE_BALANCE_PRECISE | TEXT | Token balance before transaction, decimal adjusted, returned as a string to preserve precision. Example: ‘1000.500000’ | | PRE_BALANCE | FLOAT | Token balance before the transaction execution, decimal adjusted to standard units. Example: 1000.50 | | PRE_BALANCE_USD | FLOAT | USD value of the pre-transaction token balance at the time of the transaction. Example: 1000.50 | | POST_BALANCE_HEX | TEXT | Hexadecimal representation of the post-transaction balance as stored in the contract’s storage slot. Example: ‘0x2cb417800’ | | POST_BALANCE_RAW | TEXT | Token balance after transaction in smallest unit, no decimal adjustment. Example: 750250000 | | POST_BALANCE_PRECISE | TEXT | Token balance after transaction, decimal adjusted, returned as a string to preserve precision. Example: ‘750.250000’ | | POST_BALANCE | FLOAT | Token balance after the transaction execution, decimal adjusted to standard units. Example: 750.25 | | POST_BALANCE_USD | FLOAT | USD value of the post-transaction token balance at the time of the transaction. Example: 750.25 | | NET_BALANCE_RAW | NUMBER | The change in token balance in smallest unit. Example: -250250000 | | NET_BALANCE | FLOAT | The change in token balance (post_balance - pre_balance). Example: -250.25 | | EZ_BALANCES_ERC20_ID | TEXT | Primary key - unique identifier for each row ensuring data integrity. Format: Usually VARCHAR containing composite key generated using MD5 hash of the relevant columns. Example: MD5(block_number, tx_hash, trace_index) Usage:
  • Deduplication in incremental loads
  • Join operations for data quality checks
  • Troubleshooting specific records
Important: Implementation varies by table - check table-specific documentation. | | INSERTED_TIMESTAMP | TIMESTAMP_NTZ | UTC timestamp when the record was first added to the Flipside database. Format: TIMESTAMP_NTZ Use Cases:
  • Data freshness monitoring
  • Incremental processing markers
  • Debugging data pipeline issues
  • SLA tracking
Query Example: 
-- Check data latency
SELECT
    DATE_TRUNC('hour', block_timestamp) as block_hour,
    DATE_TRUNC('hour', inserted_timestamp) as insert_hour,
    AVG(TIMESTAMPDIFF('minute', block_timestamp, inserted_timestamp)) as avg_latency_minutes
FROM <blockchain_name>.core.fact_transactions
WHERE block_timestamp >= CURRENT_DATE - 1
GROUP BY 1, 2;
``` |
| MODIFIED_TIMESTAMP | TIMESTAMP_NTZ | UTC timestamp of the most recent update to this record.

**Format**: TIMESTAMP_NTZ

**Triggers for Updates**:
- Data corrections
- Enrichment additions
- Reprocessing for accuracy
- Schema migrations

**Monitoring Usage**:
```sql
-- Recently modified records
SELECT *
FROM <blockchain_name>.core.fact_transactions
WHERE modified_timestamp > inserted_timestamp
AND modified_timestamp >= CURRENT_DATE - 1;

-- Data quality tracking
SELECT
    DATE(modified_timestamp) as mod_date,
    COUNT(*) as records_updated,
    COUNT(DISTINCT block_number) as blocks_affected
FROM <blockchain_name>.core.fact_transactions
WHERE modified_timestamp > inserted_timestamp
GROUP BY 1
ORDER BY 1 DESC;
``` |