DuckDB primer

Why DuckDB

Datasets are served as Parquet files, and DuckDB can query Parquet directly over HTTP via its httpfs extension — no server-side query engine, no copying the whole file down first. It performs predicate and projection pushdown: a query that filters or selects a subset of columns only pulls the row-groups it actually needs across the network. That's what makes ds.sql(...) and ds.to_duckdb() fast even on large datasets, and it's why limit on to_pandas()/to_polars()/to_csv() cuts network transfer, not just the in-memory result (see Loading data). It also converts to pandas/polars/Arrow natively with no extra copy step.

The result: DuckDBPyRelation

ds.sql() and ds.to_duckdb().sql() return a duckdb.DuckDBPyRelation — a lazy query result, not a DataFrame. Nothing is fetched until you materialize it:

rel = ds.sql("SELECT county, avg(price) FROM data GROUP BY 1")

rel.df()        # pandas.DataFrame
rel.pl()        # polars.DataFrame
rel.arrow()     # pyarrow.Table
rel.fetchall()  # list of plain Python tuples

Examples

The dataset is always queried as a table named data.

Aggregation

ds.sql("""
    SELECT county, avg(price) AS avg_price, count(*) AS n
    FROM data
    GROUP BY county
    ORDER BY avg_price DESC
""").df()

Filtering + top-N

ds.sql("""
    SELECT *
    FROM data
    WHERE year >= 2020 AND county = 'Nairobi'
    ORDER BY price DESC
    LIMIT 20
""").df()

Window functions

ds.sql("""
    SELECT
        county,
        price,
        rank() OVER (PARTITION BY county ORDER BY price DESC) AS price_rank
    FROM data
    QUALIFY price_rank <= 3
""").df()

Reusable connection for multiple queries

ds.to_duckdb() gives you the underlying connection directly, useful when you want to run several queries without re-parsing data each time:

con = ds.to_duckdb()
counties = con.sql("SELECT DISTINCT county FROM data").df()
yearly = con.sql("SELECT year, avg(price) FROM data GROUP BY 1 ORDER BY 1").df()

Learn more

If SQL or DuckDB itself is new to you, the DuckDB docs are a good starting point, and the Python client guide covers the API this SDK builds on.