> ## Documentation Index
> Fetch the complete documentation index at: https://docs.flipsidecrypto.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Schema Overview

> Understanding Flipside's blockchain database organization and schema structure

Flipside organizes blockchain data into logical schemas, each serving different analytical needs.
This structure is consistent across all supported blockchains.

## Database structure

Each blockchain has its own database in Snowflake:

```
ETHEREUM_CORE (or ETHEREUM_PREMIUM)
├── ETHEREUM (Core schema)
├── BALANCES (Balances schema)
├── DEFI (Premium only)
├── NFT (Premium only)
├── PRICE (Premium only)
├── STATS (Premium only)
└── [Protocol-specific schemas] (Premium only)
```

<Note>
  **Naming convention:** Databases are typically named `[BLOCKCHAIN]_CORE` for core shares or
  `[BLOCKCHAIN]_PREMIUM` for premium shares.
</Note>

## Schema breakdown

### Core schema (Core + Premium)

The **Core** schema contains foundational blockchain data—blocks, transactions, logs, and basic
token transfers.

<AccordionGroup>
  <Accordion title="Fact tables" icon="table">
    Raw blockchain data organized by entity type.

    **Common tables:**

    * `fact_blocks` - Block-level data (timestamps, gas used, tx counts)
    * `fact_transactions` - All transactions with gas, value, success status
    * `fact_event_logs` - Raw event logs emitted by contracts
    * `fact_traces` - Internal transactions and calls (EVM chains)
    * `fact_token_transfers` - Native and fungible token transfers

    **Characteristics:**

    * Highest data volume
    * Most frequently updated (30 min - 1 hour latency)
    * Optimal for raw blockchain analysis
  </Accordion>

  <Accordion title="Dimension tables" icon="tags">
    Reference data and metadata for enrichment.

    **Common tables:**

    * `dim_contracts` - Contract addresses with metadata
    * `dim_labels` - Address labels (exchanges, protocols, etc.)
    * `dim_contract_abis` - Contract ABIs for decoding

    **Characteristics:**

    * Lower update frequency (daily updates)
    * Used for joining with fact tables
    * Provides context and human-readable names
  </Accordion>

  <Accordion title="EZ (Easy) tables" icon="wand-magic-sparkles">
    Simplified, curated views of core data.

    **Common tables:**

    * `ez_token_transfers` - Decoded token transfers with USD values
    * `ez_decoded_event_logs` - Human-readable event logs
    * `ez_native_transfers` - Native token transfers (ETH, SOL, etc.)

    **Characteristics:**

    * Pre-joined with labels and metadata
    * Includes USD pricing where applicable
    * Easier to query for common use cases
  </Accordion>
</AccordionGroup>

<Tip>
  **Core shares** include the entire Core schema and Balances schema. This is often sufficient for
  raw blockchain analysis, token tracking, custom analytics, and daily balance monitoring.
</Tip>

### Balances schema (Core + Premium)

The **Balances** schema contains daily balance snapshots for native assets and ERC20 tokens (EVM
chains). These tables provide standardized, daily granularity balance data with USD valuations.

<AccordionGroup>
  <Accordion title="Native balances" icon="coins">
    Daily native asset balance snapshots.

    **Common tables:**

    * `ez_balances_native_daily` - Daily native asset (ETH) balance snapshots

    **Characteristics:**

    * Direct RPC calls (`eth_getBalance`) at end of day
    * Decimal-adjusted balances
    * USD valuations included
    * Historical data available

    **Use cases:**

    * Daily portfolio tracking
    * Historical balance analysis
    * Whale tracking and distribution analysis
    * Balance-based yield calculations
  </Accordion>

  <Accordion title="ERC20 balances" icon="wallet">
    Daily ERC20 token balance snapshots.

    **Common tables:**

    * `ez_balances_erc20_daily` - Daily ERC20 token balance snapshots

    **Characteristics:**

    * Direct contract calls (`balanceOf`) at end of day
    * Verified token contracts only
    * Decimal-adjusted balances
    * USD valuations included
    * Historical data available

    **Use cases:**

    * Token holder distribution analysis
    * Cross-token balance comparisons
    * Token supply distribution monitoring
    * Wallet balance snapshots for reporting
  </Accordion>
</AccordionGroup>

<Note>
  **Migration from Core schema:** The old balance tables in Core schema (`fact_eth_balances`,
  `fact_token_balances`, `ez_current_balances`) are deprecated and will be removed on December 11,
  2025\. Please migrate to the new standardized balances tables in the Balances schema for improved
  performance, daily granularity, and USD pricing.
</Note>

### DeFi schema (Premium only)

The **DeFi** schema contains curated DeFi protocol data—swaps, lending, liquidity, bridges, and
more.

<AccordionGroup>
  <Accordion title="DEX tables" icon="arrow-right-arrow-left">
    Decentralized exchange activity.

    **Common tables:**

    * `ez_dex_swaps` - All DEX swaps with USD values
    * `fact_dex_swaps` - Raw DEX swap events
    * `dim_dex_liquidity_pools` - Pool metadata

    **Use cases:**

    * Trading volume analysis
    * Liquidity tracking
    * Price discovery
    * Arbitrage detection
  </Accordion>

  <Accordion title="Lending tables" icon="hand-holding-dollar">
    Lending protocol activity (Aave, Compound, etc.).

    **Common tables:**

    * `ez_lending_deposits` - Deposit events with USD values
    * `ez_lending_borrows` - Borrow events with USD values
    * `ez_lending_repayments` - Repayment events
    * `ez_lending_liquidations` - Liquidation events
    * `ez_lending_flashloans` - Flash loan activity

    **Use cases:**

    * TVL tracking
    * Risk monitoring
    * Liquidation analysis
    * Interest rate tracking
  </Accordion>

  <Accordion title="Liquidity tables" icon="droplet">
    Liquidity pool actions (adds, removes).

    **Common tables:**

    * `ez_liquidity_pool_actions` - LP adds and removes
    * `dim_liquidity_pools` - Pool reference data

    **Use cases:**

    * Liquidity provision analysis
    * Impermanent loss calculations
    * Pool performance tracking
  </Accordion>

  <Accordion title="Bridge tables" icon="bridge">
    Cross-chain bridge activity.

    **Common tables:**

    * `ez_bridge_activity` - Bridge transfers with USD values
    * `fact_bridge_transfers` - Raw bridge events

    **Use cases:**

    * Cross-chain flow analysis
    * Bridge volume tracking
    * Capital migration patterns
  </Accordion>

  <Accordion title="Staking tables" icon="layer-group">
    Staking and governance activity.

    **Common tables:**

    * `ez_staking` - Staking deposits and withdrawals
    * `fact_staking_events` - Raw staking events

    **Use cases:**

    * Staking participation tracking
    * Validator analysis
    * Reward distribution
  </Accordion>
</AccordionGroup>

### NFT schema (Premium only)

The **NFT** schema contains NFT marketplace and transfer data.

<AccordionGroup>
  <Accordion title="NFT sales" icon="money-bill-wave">
    Marketplace sales across all major platforms.

    **Common tables:**

    * `ez_nft_sales` - All NFT sales with USD values
    * `fact_nft_sales` - Raw sale events

    **Platforms covered:**

    * OpenSea
    * Blur
    * LooksRare
    * X2Y2
    * Magic Eden (Solana)
    * And more...

    **Use cases:**

    * Sales volume tracking
    * Floor price monitoring
    * Whale activity analysis
    * Collection performance
  </Accordion>

  <Accordion title="NFT transfers" icon="right-left">
    All NFT transfer events (ERC-721, ERC-1155, etc.).

    **Common tables:**

    * `ez_nft_transfers` - Decoded NFT transfers
    * `fact_nft_transfers` - Raw transfer events

    **Use cases:**

    * Holder distribution
    * Transfer patterns
    * Wash trading detection
  </Accordion>

  <Accordion title="NFT metadata" icon="image">
    Collection and token metadata.

    **Common tables:**

    * `dim_nft_collections` - Collection-level metadata
    * `dim_nft_metadata` - Token-level attributes

    **Use cases:**

    * Collection discovery
    * Rarity analysis
    * Metadata enrichment
  </Accordion>
</AccordionGroup>

### Price schema (Premium only)

The **Price** schema contains historical price data for tokens and assets.

<AccordionGroup>
  <Accordion title="Price tables" icon="chart-line">
    OHLC price data at various granularities.

    **Common tables:**

    * `ez_prices_hourly` - Hourly OHLC + volume
    * `fact_prices` - Raw price points

    **Characteristics:**

    * Aggregated from multiple sources
    * Includes volume data
    * Cross-chain coverage
    * Historical data available

    **Use cases:**

    * Historical price analysis
    * Correlation studies
    * Arbitrage detection
    * Portfolio valuation
  </Accordion>

  <Accordion title="Asset metadata" icon="coins">
    Token and asset reference data.

    **Common tables:**

    * `dim_asset_metadata` - Asset symbols, decimals, etc.

    **Use cases:**

    * Price joins
    * Asset identification
    * Symbol normalization
  </Accordion>
</AccordionGroup>

### Stats schema (Premium only)

The **Stats** schema contains pre-computed metrics and aggregations.

<AccordionGroup>
  <Accordion title="Core metrics" icon="chart-simple">
    Blockchain-level statistics.

    **Common tables:**

    * `ez_core_metrics_hourly` - Hourly chain stats
    * `ez_core_metrics_daily` - Daily chain stats

    **Metrics include:**

    * Transaction counts
    * Active addresses
    * Gas metrics
    * Fee statistics
  </Accordion>

  <Accordion title="Protocol metrics" icon="gauge">
    Protocol-specific aggregates.

    **Common tables:**

    * `ez_defi_metrics_daily` - DeFi protocol stats
    * `ez_nft_metrics_daily` - NFT collection stats

    **Use cases:**

    * Dashboard creation
    * Trend analysis
    * Comparative metrics
    * Performance optimization (pre-aggregated)
  </Accordion>
</AccordionGroup>

### Protocol-specific schemas (Premium only)

Some blockchains include additional schemas for major protocols.

<Tabs>
  <Tab title="Ethereum">
    * **MAKER** - MakerDAO governance and vaults
    * **ENS** - Ethereum Name Service registrations
    * **BEACON** - Ethereum 2.0 beacon chain data
    * **L2** - Layer 2 rollup data
  </Tab>

  <Tab title="Solana">
    * **MARINADE** - Marinade Finance staking - **GOV** - Solana governance data
  </Tab>

  <Tab title="Other chains">
    Protocol schemas vary by blockchain. Check the specific chain documentation for details.
  </Tab>
</Tabs>

## Schema naming patterns

Flipside uses consistent naming conventions across all blockchains:

### Table prefixes

| Prefix  | Description                                 | Example              |
| ------- | ------------------------------------------- | -------------------- |
| `fact_` | Raw fact tables (large, frequently updated) | `fact_transactions`  |
| `dim_`  | Dimension tables (reference data)           | `dim_labels`         |
| `ez_`   | Easy tables (curated, pre-joined)           | `ez_token_transfers` |

### Schema organization

```sql theme={null}
-- Core schema (available in core shares)
[BLOCKCHAIN].core.fact_transactions
[BLOCKCHAIN].core.dim_labels

-- Balances schema (available in core shares)
[BLOCKCHAIN].balances.ez_balances_native_daily
[BLOCKCHAIN].balances.ez_balances_erc20_daily

-- Premium schemas (available in premium shares)
[BLOCKCHAIN].defi.ez_dex_swaps
[BLOCKCHAIN].nft.ez_nft_sales
[BLOCKCHAIN].price.ez_prices_hourly
[BLOCKCHAIN].stats.ez_core_metrics_daily
```

## Choosing the right schema

<CardGroup cols={2}>
  <Card title="Use Core for" icon="database">
    * Raw blockchain exploration
    * Custom event decoding
    * Token transfer analysis
    * Building custom metrics
    * Maximum flexibility
  </Card>

  <Card title="Use Balances for" icon="wallet">
    * Daily balance tracking - Portfolio monitoring - Holder distribution analysis - Historical
      balance trends - USD valuation snapshots
  </Card>

  <Card title="Use DeFi for" icon="arrow-right-arrow-left">
    * DEX analytics - Lending protocol tracking - TVL calculations - Bridge flow analysis -
      Cross-protocol comparisons
  </Card>

  <Card title="Use NFT for" icon="image">
    * NFT sales tracking - Collection performance - Marketplace analytics - Holder distribution -
      Rarity analysis
  </Card>

  <Card title="Use Price for" icon="chart-line">
    * Historical pricing - USD value conversions - Arbitrage detection - Portfolio valuation -
      Correlation analysis
  </Card>

  <Card title="Use Stats for" icon="chart-bar">
    * Dashboard creation - Pre-aggregated metrics - Performance optimization - Trend analysis - Quick
      insights
  </Card>

  <Card title="Use Protocol-specific for" icon="cubes">
    * Protocol-specific analysis
    * Governance tracking
    * Specialized metrics
    * Deep dives
  </Card>
</CardGroup>

## Common query patterns

### Start with Core, join with DeFi

```sql theme={null}
-- Find DEX swaps for specific transactions
SELECT
    t.tx_hash,
    t.from_address,
    t.block_timestamp,
    s.platform,
    s.amount_in_usd,
    s.token_in,
    s.token_out
FROM ethereum.core.fact_transactions t
JOIN ethereum.defi.ez_dex_swaps s
    ON t.tx_hash = s.tx_hash
WHERE t.block_timestamp >= CURRENT_DATE - 7
  AND s.block_timestamp >= CURRENT_DATE - 7
LIMIT 1000;
```

### Enrich with labels and prices

```sql theme={null}
-- Token transfers with USD values and labels
SELECT
    tt.block_timestamp,
    tt.from_address,
    l_from.label AS from_label,
    tt.to_address,
    l_to.label AS to_label,
    tt.symbol,
    tt.amount,
    tt.amount_usd
FROM ethereum.core.ez_token_transfers tt
LEFT JOIN ethereum.core.dim_labels l_from
    ON tt.from_address = l_from.address
LEFT JOIN ethereum.core.dim_labels l_to
    ON tt.to_address = l_to.address
WHERE tt.block_timestamp >= CURRENT_DATE - 1
  AND tt.amount_usd > 10000
ORDER BY tt.amount_usd DESC
LIMIT 100;
```

### Use Balances for portfolio tracking

```sql theme={null}
-- Daily portfolio balance tracking
SELECT
    block_date,
    address,
    SUM(balance_usd) AS total_portfolio_usd,
    SUM(CASE WHEN contract_address IS NULL THEN balance_usd ELSE 0 END) AS native_balance_usd,
    SUM(CASE WHEN contract_address IS NOT NULL THEN balance_usd ELSE 0 END) AS token_balance_usd
FROM (
    SELECT block_date, address, balance_usd, NULL AS contract_address
    FROM ethereum.balances.ez_balances_native_daily
    WHERE block_date >= CURRENT_DATE - 30
    UNION ALL
    SELECT block_date, address, balance_usd, contract_address
    FROM ethereum.balances.ez_balances_erc20_daily
    WHERE block_date >= CURRENT_DATE - 30
)
WHERE address = LOWER('0x1234567890123456789012345678901234567890')
GROUP BY 1, 2
ORDER BY 1 DESC;
```

### Use Stats for quick insights

```sql theme={null}
-- Daily Ethereum metrics
SELECT
    date,
    transaction_count,
    active_addresses,
    avg_gas_price,
    total_fees_usd
FROM ethereum.stats.ez_core_metrics_daily
WHERE date >= CURRENT_DATE - 30
ORDER BY date DESC;
```

## Schema-specific documentation

For detailed table structures and column definitions:

<CardGroup cols={2}>
  <Card title="Ethereum" icon="ethereum" href="/blockchain-data/ethereum/overview">
    Ethereum-specific tables and schemas
  </Card>

  <Card title="Solana" icon="coins" href="/blockchain-data/solana/overview">
    Solana-specific tables and schemas
  </Card>

  <Card title="Other chains" icon="link">
    Additional blockchain documentation coming soon
  </Card>
</CardGroup>

## Next steps

<CardGroup cols={2}>
  <Card title="Core listings" icon="store" href="/snowflake-data-shares/core-listings">
    Access Core and Balances schemas via core data shares
  </Card>

  <Card title="Premium listings" icon="star" href="/snowflake-data-shares/premium-listings">
    Request access to all schemas
  </Card>

  <Card title="Query optimization" icon="gauge-high" href="/snowflake-data-shares/query-optimization">
    Learn best practices for querying
  </Card>

  <Card title="Use cases" icon="lightbulb" href="/snowflake-data-shares/use-cases">
    See example queries across schemas
  </Card>
</CardGroup>