A query-driven data model
Six objects in ClickHouse Cloud: markets, price_ticks, trades, a FINAL view over them, the one-minute aggregate, and its materialized view — typed with UInt256 token IDs, exact decimals, and enums.
ClickHouse BUILD Series
In two and a half hours you stream public prediction-market data into your own ClickHouse Cloud service, keep a one-minute midpoint series maintained on insert, and publish a market pulse dashboard — then say whether a move came with a wider spread or faster trading.
Not a trading tutorial. A live data problem.
A public prediction market is one of the few datasets that is genuinely live, genuinely messy, and free to read. This workshop uses only the public Gamma, CLOB, and Data APIs: no wallet, no deposit, no order, and no Polymarket secret at any point.
The interesting part is not the feed, it is what the feed does to your design. The WebSocket is fast but is not a durable log, so the collector detects a quiet or stalled socket, reconnects, and polls the public CLOB book while degraded. A separate loop reconciles trades on a five-second overlap with deterministic IDs, so the same trade arriving twice lands once.
Every table is keyed for the queries you will actually run — an hour bucket first, then the token or condition you group by — and the one-minute midpoint series is maintained on insert rather than recomputed on every dashboard refresh.
What you walk away with
On your own ClickHouse Cloud service, fed by public APIs. One stateless Docker container is the only thing running locally.
Six objects in ClickHouse Cloud: markets, price_ticks, trades, a FINAL view over them, the one-minute aggregate, and its materialized view — typed with UInt256 token IDs, exact decimals, and enums.
Heartbeat and stall detection, reconnect, CLOB REST book fallback while degraded, and a health endpoint where degraded is an honest state rather than a failure.
A public trades loop on a five-second overlap with deterministic IDs, plus ReplacingMergeTree as the second safety net behind it.
AggregatingMergeTree holding argMin, argMax, min, max and count states, finalized with the matching Merge functions — and you compare read rows against the raw scan.
Current probability, five-minute movers, spread and freshness, and volume velocity — so a move gets confidence or doubt instead of a headline.
Polymarket market pulse — five saved queries including the one-minute line chart, built entirely in the ClickHouse Cloud console. No local dashboard product.
The route · 2h30 hands-on
One flow, start to finish: find the markets, model them, stream them, aggregate them, then explain and publish the move.
Prepare macOS or Ubuntu on WSL 2, install clickhousectl and the ClickHouse client, create the Cloud service, and get the preflight to print READY.
Ask Gamma for the busiest active markets and learn why a condition ID and an outcome token ID are different things.
Create the typed market, tick and trade tables around the filters they will serve, plus the one-minute midpoint aggregate and its materialized view.
Start the collector, read its health contract, and prove rows are landing in Cloud — with fixture mode as the tested fallback for a blocked or quiet feed.
Finalize the aggregate states, compare read rows against the raw equivalent, and confirm the newest minute advances with no scheduled refresh job.
Four explicit queries: current probability, which outcome moved most, whether the spread is wide or the quote stale, and whether trade volume accelerated.
Save the five queries under their exact names and assemble Polymarket market pulse with four current views and the one-minute line chart.
Give a ClickHouse Agent the live tables and make it detect a move, investigate it against spread, freshness and volume, then referee its verdict with deterministic SQL and record what it got wrong.
Capture the final proof query, stop the collector, and map the path to ClickPipes for a production relay that already publishes to a stream.
Self-paced by default. Every module names its starting point and ends with a completion check you can run yourself. live, degraded with fresh REST timestamps, and fixture are all valid teaching states — the instructor track says which one the room is in, and never calls fixture data live.
Before you join
Bring a laptop with Docker and a ClickHouse Cloud organization. Leave with a live feed, a one-minute aggregate maintained on insert, and a market pulse dashboard you can point at any public market.