Querying Polymarket data with DuckDB

• DuckDB queries Parquet files in place with SQL, pushing filters into the scan
• Window functions express rolling spread and depth per outcome
• Joins let you combine a prices table with a books table on timestamp
• It runs in-process, so no database server is required

Once books are exported to Parquet, DuckDB's read_parquet turns the file into a queryable relation. Filters in the WHERE clause are pushed into the scan, so a query for one outcome and one hour reads only what it needs.

This is the payoff of exporting: the expensive part - fetching and flattening - happens once, and every subsequent question is a cheap SQL query over local files.

Rolling depth, a moving average spread, or a rank of the widest windows are all window functions. Partition by outcome and order by timestamp, then compute over a preceding frame - no Python loop, no manual state.

For comparing a market's calm windows to its volatile ones, rank rows by spread within each outcome and inspect the top decile. This is the kind of query that is awkward in a loop and natural in SQL.

Export prices and books from the same market and join them on timestamp. The result lets you compare the traded price with the quoted touch at each snapshot, which is the basis for spotting when the book lagged the trade or the reverse.

Watch the timestamp resolution on both sides. If one series is at one-second resolution and the other at 250ms, join on the coarser bucket or the join will miss rows.

DuckDB can write query results to Parquet with COPY, which turns an expensive join into a small reusable artifact. Compute the derived table once, export it, and let later queries read the much smaller result instead of re-joining the raw books.

This is the natural end of the pipeline: fetch under quota, cache the raw JSON, export tidy Parquet, and query with SQL. Each stage shrinks the data and moves the heavy work earlier.

You can also point DuckDB at a directory of Parquet files and query them as one relation, which means a partitioned export needs no per-file handling in your SQL.

import duckdb

# books.parquet and prices.parquet were exported earlier.
con = duckdb.connect()

con.execute("""
    CREATE VIEW books AS SELECT * FROM read_parquet('books.parquet');
""")

# Rolling 60-row average spread per outcome, ordered by time.
print(con.execute("""
    SELECT outcome, t, spread,
           AVG(spread) OVER (
               PARTITION BY outcome ORDER BY t
               ROWS BETWEEN 59 PRECEDING AND CURRENT ROW
           ) AS avg_spread_60
    FROM books
    WHERE outcome = 'Yes'
    ORDER BY t
    LIMIT 10;
""").fetchdf())

The Starter plan is free, includes order books, prices, and metrics at 250ms resolution, with 3 days of history, 60 requests/min, 1,000/day, 1 free AI backtest, and 3 strategy backtests. No credit card required.

Paid data windows from $19/mo extend history to 30–120 days at higher throughput. Backtest AI add-on is +$19/mo or standalone at $29/mo.

• Parquet export
• Polars
• Spread analysis