How to Query Parquet Files With DuckDB

Why DuckDB for Parquet

Parquet is the standard storage format for data lakes, but querying it traditionally meant spinning up Spark or loading files into pandas (which pulls everything into memory first). DuckDB solves both problems: it's an embedded SQL engine that reads .parquet natively, pushes filters down to the file level (column + row group pruning), and handles tens of GB on a single machine without a cluster.

Opening a partitioned lake

A Hive-partitioned lake stores files in directories like lake/events/year=2024/month=01/part-0.parquet. DuckDB detects those path segments and exposes them as real columns when you pass hive_partitioning=1:

import duckdb

con = duckdb.connect()

# 'year' and 'month' become queryable columns automatically
rows = con.execute("""
    SELECT year, month, user_id, SUM(payload_size) AS total_size
    FROM read_parquet('lake/events/**/*.parquet', hive_partitioning=1)
    WHERE year = 2024
    GROUP BY year, month, user_id
    LIMIT 5
""").fetchall()

No file scanning loops, no pd.read_parquet, no schema file - just SQL against the glob.

Top-N per group with a window function

To find the top 5 users by spend for each month, use ROW_NUMBER() inside a CTE. A Python loop over query results is fragile and slow; the window function does it in one SQL pass:

import json
import duckdb

con = duckdb.connect()

rows = con.execute("""
    WITH per_month AS (
      SELECT
        year,
        month,
        user_id,
        SUM(payload_size) AS total_size
      FROM read_parquet('lake/events/**/*.parquet', hive_partitioning=1)
      WHERE year = 2024
      GROUP BY year, month, user_id
    ),
    ranked AS (
      SELECT
        printf('%04d-%02d', year, month) AS month,
        user_id,
        total_size,
        ROW_NUMBER() OVER (
          PARTITION BY year, month
          ORDER BY total_size DESC
        ) AS rn
      FROM per_month
    )
    SELECT month, user_id, total_size
    FROM ranked
    WHERE rn <= 5
    ORDER BY month, total_size DESC
""").fetchall()

result = [{"month": m, "user_id": int(u), "total_size": int(t)} for m, u, t in rows]
print(json.dumps(result))

PARTITION BY year, month ORDER BY total_size DESC restarts the rank counter for each month, so WHERE rn <= 5 keeps exactly the top 5 per month.

Partition pruning in practice

The WHERE year = 2024 clause is not just a row filter - DuckDB reads the path segments and skips entire directories for other years before opening a single file. On a large lake (thousands of Parquet files), this makes the difference between a 10-second scan and a sub-second one.

Running the same query on the cloud

The exact same SQL runs unchanged on MotherDuck (DuckDB-as-a-service) over Parquet in S3, or via Athena/Trino against the same files. The lakehouse pattern decouples storage (open Parquet format, you own it) from compute - swap the engine without touching the data.