Polymarket Data in BigQuery

• bq load --source_format=PARQUET dataset.books gs://bucket/books_*.parquet for the bulk path.
• Alternatively, the CSV export → bq load --source_format=CSV with an explicit schema (timestamps typed as TIMESTAMP).
• Partition by _PARTITIONTIME over file...date=... only if you partition raw rows; otherwise cluster on market and sort on ts.
• Autodetect schema once locally (bq load --autodetect --dry_run) to catch price-col typing before a full load.

• One-sided settlement: SELECT market, SAFE_DIVIDE(COUNTIF(ask_depth = 0), COUNT(*)) FROM books WHERE ... GROUP BY market.
• Median spread by hour: APPROX_QUANTILES(ask - bid, 100)[OFFSET(50)].
• Point-in-time: SELECT * FROM books WHERE ts <= TIMESTAMP("...") ORDER BY ts DESC LIMIT 1 — the AS-OF primitive.
• Cross-market: join on EXTRACT(EPOCH FROM ts) rounded to the second against exchange series.

Cost scales with bytes scanned; clustering on market and pruning to windows keeps scans small.

Materialize the heavy settlement/one-sided aggregations as tables, then dashboards touch the summaries.

Books, prices, and metrics come back over the same endpoints at the same 250ms resolution, so a dashboard needs one schema, not three.

bq load with PARQUET source materializes the rows; cluster on market and add a partition on the day column so SELECT scans only the slice you study.

The one-sided settlement query is a COUNTIF over ask_depth-0 frames grouped by market — serverless, billed by bytes scanned, cheap when clustered.

Rows loaded per table should equal rows counted in the source parquet/CSV; BigQuery reports row counts after load, so verify once per partitioning scheme.

Cost control is about pruned scans: a clustered, date-partitioned table makes the whole archive cost-per-study trivial.

BigQuery is the shareable warehouse: the same settlement SQL runs serverless, teammates query the shared dataset without keys, and cost is a direct function of bytes scanned, so clustering discipline shows up on the bill.

The honest drawback is the same as any warehouse — query costs and dry-run discipline are real; for a solo laptop study DuckDB is cheaper, for a team BigQuery wins.

The row-count check is free: BigQuery reports loaded rows, so verify each table equals the source count once per partitioning scheme.

First load is one day, one market, PARQUET source, clustered on market; the dry-run before the real load catches price-column typing, and the loaded row count (34,560 for 250ms) confirms the window math.

Then run the one-sided settlement query over the day and compare the ratio to the archived published figure for the same market; agreement on a single day buys trust for the full load.

BigQuery is the shareable endgame for teams already in GCP: the same settlement queries run serverless, teammates share the dataset without key management, and billing follows bytes scanned, so clustering shows up in the cost column where it belongs.

The discipline is dry-run first, partition and cluster on market/day, and double cast timestamps at load. Each habit converts a potential cross-grain bug into a load-time failure that happens in your project, not in a meeting.

When cost is the concern — and it usually is — materialize hourly aggregates and let dashboards read summaries; the raw frames stay loaded, queryable, and cheap because queries barely scan them.

• API data guide
• Data download
• Order book data
• MCP server