Onchain SQL
Query 106 ClickHouse tables directly with SQL — DEX trades, prices, transfers, DeFi metrics, prediction markets, and more.
Run SQL queries against Surf's on-chain data warehouse. 106 tables across 11 categories, accessible via the Data API's POST /onchain/sql endpoint or a readonly ClickHouse connection.
Quick Start
Via API:
curl -X POST https://api.asksurf.ai/gateway/v1/onchain/sql \
-H "Authorization: Bearer <API_KEY>" \
-H "Content-Type: application/json" \
-d '{"query": "SELECT project, sum(amount_usd) AS vol FROM agent.ethereum_dex_trades WHERE block_date = today() - 1 GROUP BY project ORDER BY vol DESC LIMIT 10"}'Via ClickHouse client:
| Setting | Value |
|---|---|
| Protocol | HTTPS (port 8443) |
| User | agent (readonly) |
| Database | agent (all tables — the only database exposed) |
| Max execution time | 120 s |
| Max memory | 16 GB |
| Max result rows | 1 M |
Table Discovery
Use the GET /onchain/schema endpoint to get all table names, columns, and types programmatically. Or browse the reference pages below.
Tables at a Glance
| Category | Tables | Chains / Platforms | What's in it |
|---|---|---|---|
| DEX Trades | 13 | ETH, Base, Arb, BSC, Tron, HyperEVM, Tempo | Every swap event with USD amounts and protocol name, plus Tempo pair/pool registries |
| Prices | 7 | ETH, Base, Arb, Tron, HyperEVM, Tempo | Daily token prices (CoinGecko + DEX VWAP) |
| Token Metadata | 4 | ETH, Base, Arb, BSC | ERC-20 address → symbol / name / decimals |
| Transfers | 6 | ETH, Base, Arb, BSC, Tron, Tempo | ERC-20 + native transfers with USD pricing |
| TVL / Fees / Yields | 15 | ETH, Base, Arb, Tron, Tempo (TVL); ETH, Base (fees, yields, top pools) | Protocol- and pool-level daily TVL, fee revenue, pool APY |
| Lending & Staking | 2 | ETH | Lending flows + beacon chain staking by entity |
| Bridges & Payments | 5 | ETH-centric, Tempo | Cross-chain bridge volume + Tempo MPP payment analytics |
| Prediction Markets | 31 | Polymarket, Kalshi | Trades, markets, prices, volume, OI, positions, smart money, custom views |
| Hyperliquid | 10 | Hyperliquid L1 | Perp snapshots, fills, trade episodes, wallet performance, funding, metadata |
| Chain Metrics | 10 | ETH, Base, Arb, Tempo | Daily tx count, active addresses, gas, contract deploys, fees, cohorts |
| Stablecoin | 3 | Tempo | Daily stablecoin supply changes and per-token activity metrics |
Essential Rules
These apply to every query. Ignore them and your queries will be slow or wrong.
-
Always filter on
block_datefirst. It's the partition key on almost every table. Without it, ClickHouse scans the entire table. -
Use
FINALon ReplacingMergeTree tables. Hyperliquid tables and most Tempo daily-aggregate tables use this engine. Syntax:FROM table_name FINAL(place FINAL after the table name, before any alias). -
Handle NULLs explicitly. ClickHouse silently drops NULL rows in WHERE filters. Use
ifNull(col, default)orcoalesce(). -
amount_usdis one-sided. DEX trade USD values follow DefiLlama methodology — they represent the token_sold side. Multiply by 2 for two-sided volume. -
USD prices come from CoinGecko. Hourly snapshots, not tick-level. New, illiquid, or unpriced tokens will have
NULLamount_usd.
Data Freshness
| Source | Schedule | Typical Lag |
|---|---|---|
| Ethereum, Arbitrum, Bitcoin, Tron | Daily (BQ export) | ~1 day |
| Base | Sensor (6h, cryo) | ~6 hours |
| BSC | Sensor (6h, cryo) | ~6 hours |
| Tempo | Sensor (60 min, cryo) | ~1 hour |
| HyperEVM | Daily (S3) | ~1 day |
| Hyperliquid (API) | Hourly | ~1 hour |
| CoinGecko prices | Hourly | ~1 hour |
| Kalshi | Every 15 min (API) | ~15 min |
| Polymarket (on-chain) | Every 30 min (BQ) | ~1 hour |
| Polymarket (real-time) | Every 30 min (WebSocket) | ~30 min |
| dbt models | Post-ingestion | +30 min |
Check freshness for any table:
SELECT max(block_date) FROM agent.<table_name>Common Join Keys
| From | To | Join column |
|---|---|---|
Any table with token_address | Token symbol/name | JOIN agent.ethereum_erc20 ON contract_address |
| Polymarket tables | Market question | JOIN agent.polymarket_market_details ON condition_id |
| Kalshi tables | Market category | JOIN agent.kalshi_market_details ON market_ticker (has category / subcategory) |
| Hyperliquid tables | Contract metadata | JOIN agent.hyperliquid_perp_meta ON coin |
| DeFi tables | TVL context | JOIN agent.ethereum_tvl_daily ON (project, version, block_date) |
For query patterns and worked examples, see Common Patterns.