Ez Token Transfers

core.ez_token_transfers

Schema: boba.core Table: ez_token_transfers Type: Base Table

What

This convenience table provides a comprehensive view of all ERC-20 token transfers with enriched metadata including decimal adjustments, USD values, and token information. It simplifies token flow analysis by joining transfer events with contract details and price data.

Key Use Cases

• Tracking token movements and holder activity
• Analyzing stablecoin flows and volumes
• Monitoring DEX token inflows and outflows
• Detecting new token launches and adoption
• Calculating wallet token balances from transfer history

Important Relationships

• Join with fact_event_logs: Use tx_hash and event_index for raw event details
• Join with fact_transactions: Use tx_hash for transaction context
• Join with dim_contracts: Use contract_address for token metadata
• Complement to ez_native_transfers: Complete picture of value flows

Commonly-used Fields

• contract_address: The token contract address (NOT the recipient)
• from_address: Token sender address
• to_address: Token recipient address
• amount: Decimal-adjusted transfer amount
• amount_usd: USD value at time of transfer
• symbol: Token symbol (e.g., USDC, DAI)
• raw_amount: Original amount without decimal adjustment

Sample queries

Top Token Transfers by USD Value

SELECT
    block_timestamp,
    tx_hash,
    symbol,
    from_address,
    to_address,
    amount,
    amount_usd,
    token_price,
    contract_address
FROM <blockchain_name>.core.ez_token_transfers
WHERE block_timestamp >= CURRENT_DATE - 7
    AND amount_usd > 100000  -- Over $100k
    AND has_decimal = TRUE
    AND has_price = TRUE
ORDER BY amount_usd DESC
LIMIT 100;

Daily Stablecoin Volume Analysis

SELECT
    DATE_TRUNC('day', block_timestamp) AS day,
    symbol,
    COUNT(*) AS transfer_count,
    COUNT(DISTINCT from_address) AS unique_senders,
    SUM(amount) AS total_amount,
    SUM(amount_usd) AS total_usd,
    AVG(amount_usd) AS avg_transfer_usd
FROM <blockchain_name>.core.ez_token_transfers
WHERE symbol IN ('USDC', 'USDT', 'DAI', 'BUSD')
    AND block_timestamp >= CURRENT_DATE - 30
    AND has_decimal = TRUE
GROUP BY 1, 2
ORDER BY 1 DESC, 6 DESC;

DEX Token Flow Analysis

WITH dex_addresses AS (
    SELECT address
    FROM <blockchain_name>.core.dim_labels
    WHERE label_type = 'dex'
    AND label_subtype IN ('pool', 'router')
)
SELECT
    DATE_TRUNC('hour', block_timestamp) AS hour,
    symbol,
    CASE
        WHEN from_address IN (SELECT address FROM dex_addresses) THEN 'DEX Outflow'
        WHEN to_address IN (SELECT address FROM dex_addresses) THEN 'DEX Inflow'
    END AS flow_type,
    COUNT(*) AS transfers,
    SUM(amount) AS total_amount,
    SUM(amount_usd) AS total_usd
FROM <blockchain_name>.core.ez_token_transfers
WHERE block_timestamp >= CURRENT_DATE - 1
    AND (from_address IN (SELECT address FROM dex_addresses)
         OR to_address IN (SELECT address FROM dex_addresses))
    AND symbol IS NOT NULL
GROUP BY 1, 2, 3
ORDER BY 1 DESC, 6 DESC;

Token Holder Activity

-- Most active token senders
SELECT
    from_address,
    COUNT(DISTINCT contract_address) AS tokens_sent,
    COUNT(DISTINCT DATE(block_timestamp)) AS active_days,
    COUNT(*) AS total_transfers,
    SUM(amount_usd) AS total_usd_sent
FROM <blockchain_name>.core.ez_token_transfers
WHERE block_timestamp >= CURRENT_DATE - 30
    AND has_price = TRUE
    AND amount_usd > 10  -- Filter dust
GROUP BY 1
HAVING COUNT(*) > 10
ORDER BY total_usd_sent DESC
LIMIT 100;

New Token Detection

WITH first_transfers AS (
    SELECT
        contract_address,
        symbol,
        MIN(block_timestamp) AS first_transfer,
        COUNT(*) AS transfer_count,
        COUNT(DISTINCT from_address) AS unique_senders,
        COUNT(DISTINCT to_address) AS unique_receivers
    FROM <blockchain_name>.core.ez_token_transfers
    WHERE block_timestamp >= CURRENT_DATE - 7
    GROUP BY 1, 2
    HAVING MIN(block_timestamp) >= CURRENT_DATE - 1
)
SELECT
    ft.*,
    dc.name AS token_name,
    dc.decimals
FROM first_transfers ft
LEFT JOIN <blockchain_name>.core.dim_contracts dc ON ft.contract_address = dc.address
ORDER BY transfer_count DESC;

Columns

Column Name	Data Type	Description
BLOCK_NUMBER	NUMBER	Sequential 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_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_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 | | EVENT_INDEX | NUMBER | Zero-based sequential position of the event within a transaction’s execution.

Key Facts:

• Starts at 0 for first event
• Increments across all contracts in transaction
• Preserves execution order
• Essential for deterministic event ordering

Usage Example:

-- Trace event execution flow
SELECT
    event_index,
    contract_address,
    topic_0,
    SUBSTRING(data, 1, 10) AS data_preview
FROM <blockchain_name>.core.fact_event_logs
WHERE tx_hash = '0xabc...'
ORDER BY event_index;
``` |
| FROM_ADDRESS | TEXT | The from address for the token transfer. This may or may not be the same as the origin_from_address.

Example: '0x1234567890123456789012345678901234567890' |
| TO_ADDRESS | TEXT | The to address for the token transfer. This may or may not be the same as the origin_to_address.

Example: '0xabcdefabcdefabcdefabcdefabcdefabcdefabcd' |
| CONTRACT_ADDRESS | TEXT | The contract address for the token transfer.

Example: '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' |
| TOKEN_STANDARD | TEXT | The token standard for the transfer, in this case always erc20.

Example: 'erc20' |
| TOKEN_IS_VERIFIED | BOOLEAN | Boolean flag indicating if the token is verified by the Flipside team.

Example: true |
| NAME | TEXT | Human-readable contract name from the name() function.

Example: 'USD Coin' |
| SYMBOL | TEXT | Token/contract symbol from the symbol() function.

Example: 'USDC' |
| DECIMALS | NUMBER | Number of decimal places for token amounts, read directly from the contract code.

Example: 6 |
| RAW_AMOUNT_PRECISE | TEXT | String representation of raw amount for precision preservation.

Example: '1000500000' |
| RAW_AMOUNT | FLOAT | Original token amount without decimal adjustment.

Example: 1000500000 |
| AMOUNT_PRECISE | TEXT | String representation of decimal-adjusted amount preserving full precision.

Example: '1000.500000' |
| AMOUNT | FLOAT | Decimal-adjusted token amount for human-readable values.

Example: 1000.50 |
| AMOUNT_USD | FLOAT | USD value of the token transfer at transaction time.

Example: 1000.50 |
| ORIGIN_FUNCTION_SIGNATURE | TEXT | Function signature (first 4 bytes) of the called method.

**Format**: 0x + 8 hex characters

**Common Signatures**:
- 0xa9059cbb: transfer(address,uint256)
- 0x095ea7b3: approve(address,uint256)
- 0x23b872dd: transferFrom(address,address,uint256)

**Note**: NULL for simple transfers or invalid calls |
| ORIGIN_FROM_ADDRESS | TEXT | The externally-owned account (EOA) or contract address that initiated the transaction.

**Key Points**:
- Always 42 characters (0x + 40 hex chars)
- Lowercase normalized in all tables
- Cannot be NULL for valid transactions
- For contract creation: sender of creation transaction

**Common Patterns**:
- EOA → EOA: Simple transfer
- EOA → Contract: User interaction
- Contract → Contract: Internal calls (see fact_traces)
- Known addresses: Exchange hot wallets, protocol deployers

**Query Examples**:
```sql
-- User activity analysis
SELECT from_address, COUNT(*) as tx_count
FROM <blockchain_name>.core.fact_transactions
WHERE block_timestamp >= CURRENT_DATE - 30
GROUP BY 1
ORDER BY 2 DESC;

-- New user detection
SELECT DISTINCT from_address
FROM <blockchain_name>.core.fact_transactions t1
WHERE NOT EXISTS (
    SELECT 1 FROM <blockchain_name>.core.fact_transactions t2
    WHERE t2.from_address = t1.from_address
    AND t2.block_number < t1.block_number
);
``` |
| ORIGIN_TO_ADDRESS | TEXT | The destination address for the transaction - either an EOA or contract address.

**Special Cases**:
- NULL: Contract creation transaction
- Contract address: Interacting with smart contract
- EOA address: Simple transfer or receiving funds

**Important Patterns**:
```sql
-- Contract deployments
WHERE to_address IS NULL

-- Popular contracts
SELECT to_address, COUNT(*) as interactions
FROM <blockchain_name>.core.fact_transactions
WHERE to_address IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC;

-- Direct transfers only
WHERE to_address NOT IN (SELECT address FROM dim_contracts)

Note: For token transfers, this is the token contract, not the recipient. See ez_token_transfers tables for recipient. | | EZ_TOKEN_TRANSFERS_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;
``` |