​

Prices (Token Price Data)

​

Key Features

• Multi-blockchain support: Prices for tokens across 40+ blockchains
• Standardized native token addresses: Native tokens (like ETH, BNB) have fixed contract addresses for consistency
• Multiple time granularities: hourly, daily, and latest price data
• Source transparency: Each price point includes its data source
• Data quality: Comprehensive filtering to ensure price reliability
• Volume-weighted pricing: Prices are calculated using volume-weighted averages for more accurate representation
• Consistent 5-minute intervals: All data sources aligned to 5-minute intervals for improved consistency
• Enhanced outlier detection: Multi-layered filtering system to reduce impact of anomalous trades

​

Available Tables

​

Implementation Details

1. Base data sources:
   • External price feeds (Coinpaprika) - provides trusted token prices at 5-minute intervals
   • DEX trading data (from dex.trades) - aggregated into 5-minute intervals with enhanced outlier detection
2. Data processing pipeline:
   • USD price calculation: DEX trades are converted to USD using trusted token prices as reference
   • Quality filtering applied to DEX trades to reduce anomalous data impact
   • Sparse 5-minute data collected from both sources (aligned intervals for consistency)
   • Aggregated to hourly and daily sparse records using volume-weighted averages
   • Filled into continuous time series for steady time intervals
3. Token handling:
   • Native tokens are assigned fixed addresses instead of NULL or 0xeeee…
   • You can find the correct native token address for each blockchain in the dune.blockchains table
   • Trusted tokens (major stablecoins, wrapped assets) serve as price anchors
   • Non-trusted tokens derive prices through DEX trades against trusted tokens
   • Simply put: We use prices from trusted tokens to calculate USD prices of other tokens based on the data from DEX trades, then apply quality filtering

​

Coverage

• 900,000 unique tokens
• 40+ blockchains
• new tokens are automatically added when they exceed a $10k volume threshold

​

Schema

​

Technical Notes

• Prices are calculated independently per blockchain
• Token identification requires both contract_address AND blockchain
• symbol is not unique - do not use for token identification or joins
• For native tokens, use the standardized addresses from dune.blockchains table
• If there are no trades for a token, we use the last available price and carry it forward for a limited time period:
  • 30 days for daily prices
  • 7 days for hourly prices
  • 2 days for minute prices
• Quality Filtering: The system includes filtering mechanisms to reduce the impact of anomalous trades, though this is not foolproof
• Volume-Weighted Pricing: Prices are calculated using volume-weighted averages rather than simple medians for more representative pricing
• 5-Minute Intervals: All data sources are aligned to 5-minute intervals for consistency and improved data quality
• Enhanced Protection: Multi-layered outlier detection provides better protection against price manipulation and flash crashes

​

Methodology

​

Step 1: Importing Trusted Token Prices

• Cover major tokens that are defined in the prices.trusted_tokens table
• Serve as base prices for calculating prices of other tokens traded against them in DEX pairs
• Are updated at 5-minute intervals

​

Step 2: DEX-Derived Prices with Quality Filtering

• We start by collecting raw trading data from the dex.trades table
• We calculate USD prices for trading pairs where one token is from our trusted token list using trusted token prices as reference
• Quality filtering is then applied to reduce the impact of anomalous trades:
  • VWMP Outlier Detection: Prices that deviate more than 3x from the rolling 7-day volume-weighted median price
  • MAD Outlier Detection: Prices that deviate more than 2x the median absolute deviation from the hourly VWMP
  • Transfer Outlier Detection: Token amounts that exceed the maximum transfer amount seen in the past 7 days
  • Volume Outlier Detection: Trade volumes below $0.01 or above$1,000,000
• The filtered data then goes through our processing pipeline which:
  • Excludes trades with less than $10,000 USD in total volume
  • Aggregates trades into 5-minute intervals using volume-weighted averages
  • Takes the volume-weighted average price from each interval to reduce outlier impact
  • Uses forward-filling to handle periods without valid trades, with time limits
    • 30 days for daily prices
    • 7 days for hourly prices
    • 2 days for minute prices

​

Quality Filtering Rules

1. VWMP Outlier Detection:
   • Trades where the relative price deviation from the 7d rolling median price is greater than a factor 3 (in both directions)
2. MAD Outlier Detection:
   • Trades where the absolute price deviation from the 4h rollingmedian price is greater than 2 times the 4h rolling median absolute deviation
3. Transfer Outlier Detection:
   • Trades where the token amount is greater than the maximum transfer amount
   • This helps identify trades that report larger amounts than the actual transfers
4. Volume Outlier Detection:
   • Trades with volumes below $0.01 or above$1,000,000
   • This helps filter out dust trades and suspiciously large volumes

​

Usage Examples

​

Basic query to get the latest ETH price:

SELECT price
FROM prices.latest
WHERE blockchain = 'ethereum' 
AND contract_address = 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 -- WETH

​

Getting historical price data:

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

​

Finding the right native token address:

-- Query to get the native token address for a specific blockchain
SELECT 
  name,
  token_address,
  token_symbol
FROM dune.blockchains
WHERE name = 'arbitrum'

​

Legacy Tables

• prices.usd
• prices.usd_daily
• prices.usd_latest