Skip to main content

​
prices.day

​
Overview

The prices.day table provides daily token prices using a hybrid approach that combines multiple data sources for maximum coverage:
  1. Coinpaprika prices for tokens where we have reliable external data (~2,000 major tokens, defined in dbt spellbook lists)
  2. DEX-derived prices for the long tail of tokens not covered by coinpaprika
This hybrid methodology ensures comprehensive token coverage while maintaining price quality through different data sources optimized for different token categories.

​
Table Schema

ColumnTypeDescription
blockchainvarcharBlockchain identifier (e.g., ‘ethereum’, ‘arbitrum’)
contract_addressvarbinaryToken contract address (fixed address for native tokens)
symbolvarcharToken symbol (e.g., ‘ETH’, ‘USDC’)
timestamptimestampDate timestamp (00:00 UTC of each day)
pricedoubleToken price in USD (volume-weighted average)
decimalsintToken decimals
volumedoubleTrading volume in USD (from price source)
sourcevarcharData source (‘coinpaprika’ or ‘dex.trades’)

​
Implementation Details

The daily prices are built through these steps:
  1. Source prioritization: Coinpaprika prices take precedence where available, DEX-derived prices fill gaps
  2. Token list management: Lists defining which tokens use coinpaprika vs DEX are managed in dbt spellbook
  3. Data aggregation: Group by day, calculating volume-weighted average price per day
  4. Forward filling: Fill missing days with the previous day’s price (forward filling)
  5. Expiration: Set a 30-day expiration for forward filling to avoid stale data

​
Alternative Tables

If you need prices from specific sources only:

​
prices_coinpaprika.day

For purely coinpaprika-based daily prices:
  • Coverage: Only tokens with Coinpaprika listings (~2,000 tokens)
  • Source: External exchange-aggregated pricing
  • Use case: When you need stable, externally-validated daily pricing
  • Special note: Used by tokens.transfers for stable pricing
  • Schema: Same as prices.day but source is always ‘coinpaprika’

​
prices_dex.day

For purely DEX algorithm-based daily prices:
  • Coverage: All tokens with sufficient DEX trading volume
  • Source: On-chain DEX trading activity only (from dex.trades table)
  • Use case: When you need pricing that reflects actual on-chain liquidity and trading activity
  • Methodology: Comprehensive outlier detection and quality filtering applied to DEX trades
  • Key difference: Even tokens with coinpaprika coverage use DEX-derived prices for consistency
  • Schema: Same as prices.day but source is always ‘dex.trades’
Example - Compare DEX vs combined pricing: 
SELECT
  d.timestamp,
  d.symbol,
  d.price as dex_price,
  c.price as combined_price,
  (d.price - c.price) / c.price * 100 as price_diff_pct
FROM prices_dex.day d
JOIN prices.day c 
  ON d.blockchain = c.blockchain 
  AND d.contract_address = c.contract_address 
  AND d.timestamp = c.timestamp
WHERE d.blockchain = 'ethereum'
AND d.contract_address = 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 -- WETH
AND d.timestamp >= NOW() - INTERVAL '30' DAY
ORDER BY d.timestamp

​
Usage

This table provides comprehensive daily price coverage by combining coinpaprika and DEX-derived sources. It’s ideal for:
  • Daily price trend analysis
  • Portfolio valuations
  • Day-over-day price comparisons
  • General token price lookups
Note: The underlying data sources are aligned to 5-minute intervals for consistency and improved data quality. For specific use cases: If you need purely coinpaprika or purely DEX-derived prices, use the dedicated source-specific tables instead.

​
Latency and Update Frequency

The prices.day table is updated daily at 00:00 UTC, providing the closing price for the previous day. Data is typically available within 30 minutes of the daily close.

​
Usage Examples

​
Get ETH price history for the last month:

SELECT
  timestamp,
  price
FROM prices.day
WHERE blockchain = 'ethereum'
AND contract_address = 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 -- WETH
AND timestamp >= NOW() - INTERVAL '30' DAY
ORDER BY timestamp

​
Calculate monthly average price for multiple tokens:

SELECT
  blockchain,
  symbol, -- Symbol included for readable output only
  date_trunc('month', timestamp) as month,
  avg(price) as avg_price
FROM prices.day
WHERE blockchain = 'ethereum'
AND contract_address IN (
  0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2, -- WETH
  0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48, -- USDC
  0x2260fac5e5542a773aa44fbcfedf7c193bc2c599  -- WBTC
)
AND timestamp >= NOW() - INTERVAL '90' DAY
GROUP BY 1,2,3
ORDER BY 1,2,3

​
Get native token price using dune.blockchains:

-- First get the native token address
WITH native_token AS (
  SELECT token_address
  FROM dune.blockchains
  WHERE name = 'ethereum'
)
-- Then use it to query the price
SELECT
  timestamp,
  price
FROM prices.day, native_token
WHERE blockchain = 'ethereum'
AND contract_address = token_address
AND timestamp >= NOW() - INTERVAL '30' DAY
ORDER BY timestamp

​
Data Quality Notes

  • Prices older than 30 days will not be forward-filled to avoid using stale data
  • Native tokens (like ETH, BNB) are assigned fixed addresses for consistency
  • For native tokens, use dune.blockchains table to get the standardized address for each blockchain
  • The same token may have different prices on different blockchains due to bridging inefficiencies
  • Always use contract_address and blockchain for precise token identification, never use symbol for joins or filters
  • Prices are calculated using volume-weighted averages for more accurate representation
  • Quality Filtering: The system includes filtering mechanisms to reduce the impact of anomalous trades
  • DEX-derived limitations: DEX-derived prices can contain errors due to low liquidity, market manipulation, or anomalous trades
  • Fallback option: For critical applications, consider using prices_coinpaprika.day for more reliable pricing where available
  • Data validation: Always validate prices for critical applications, especially for lesser-known tokens
⌘I