OLAP Design with DuckDB, Parquet and Polars

uv add duckdb polars

OLTP shape vs. OLAP shape

A transactional schema (SQL Server, Postgres) is normalized to make single-row writes cheap and safe — a customer’s address lives in one place so an update touches one row. An analytical schema optimizes for the opposite workload: scanning millions of rows and aggregating a handful of columns across most of them. Normalizing that schema the same way just adds joins the query planner has to redo on every scan.

Dimensional modeling: fact and dimension tables

The star schema is still the default starting point for an analytical schema, DuckDB included:

• Fact table — one row per business event at a fixed grain (e.g., one row per order line). Columns are foreign keys to dimensions plus additive measures (quantity, amount) that are safe to SUM() across any combination of dimensions. Decide the grain first — everything else follows from it, and changing it later means reloading the whole table.
• Dimension tables — the descriptive context around a fact: customer, product, date. Deliberately denormalized (a product dimension carries category and brand name directly, not a foreign key to a separate category table) because the whole point is avoiding joins at query time.
• Surrogate keys — an integer key generated by the warehouse, not reused from the source system. Insulates the fact table from a source’s natural key changing format, and lets a dimension track history (Slowly Changing Dimensions) without breaking the fact table’s foreign key.
• Date dimension — worth materializing as an actual table (one row per day, with year/quarter/month/day-of-week/is-holiday columns) rather than computing those from a date column at query time. Cheap to build once, and every downstream query gets consistent fiscal-period logic for free.

Designing for DuckDB specifically

DuckDB is an in-process, columnar, vectorized-execution engine — architecturally closer to a single-node Spark or a mini-Snowflake than to SQLite, despite the “embedded database” comparison it usually gets. That changes some schema advice:

• Indexes are rarely the answer. DuckDB’s ART index mainly helps point lookups and unique constraints; the workhorse for scan performance is automatic zone maps (min/max per column per row group), which the engine uses to skip whole chunks of data on a filter. Sorting or clustering a fact table by the column you filter on most (typically the date) makes zone-map skipping effective without an index to maintain.
• Wide fact tables are fine. Because storage is columnar, a query touching 4 of 40 columns only reads those 4 — unlike a row store, adding columns doesn’t cost anything at query time for queries that don’t select them.
• Prefer native types over strings. Store dates as DATE/TIMESTAMP and categories as ENUM where the value set is fixed — both compress better and let the optimizer reason about ranges, which a VARCHAR holding the same data can’t do.
• Bulk load, don’t row-insert. INSERT INTO ... SELECT from a file or COPY ... FROM is the intended path; thousands of single-row INSERT statements defeat the vectorized engine, which is built to move whole batches through the pipeline at once.
• A single .duckdb file is a real deployment target, not just a dev convenience — it’s a valid way to ship a versioned, queryable analytical dataset (a reporting snapshot, a data package) since the whole database is one portable file.

Where Parquet fits

Parquet is the storage format DuckDB (and the broader Python data ecosystem) is built around — columnar on disk, so a scan reads only the columns a query touches, and self-describing, so schema travels with the data instead of living in a separate catalog.

• DuckDB can query Parquet directly, with no load step: SELECT * FROM 'data/orders/*.parquet' runs against the files in place, with projection pushdown (only requested columns are read off disk) and predicate pushdown (Parquet’s own per-row-group statistics let DuckDB skip row groups the same way zone maps do) both applied automatically.

• Materialize into DuckDB’s native format when a table is queried repeatedly — a CREATE TABLE AS SELECT * FROM 'file.parquet' pays a one-time load cost and gets faster repeat access, DuckDB-specific compression, and multi-column statistics Parquet’s own metadata doesn’t carry. Query Parquet directly for one-off exploration or when the data already lives there for other tools to share.

• Partition by the column most queries filter on — almost always date. Hive-style directories (orders/year=2026/month=09/*.parquet) let DuckDB prune entire partitions before opening a single file, on top of the row-group-level pruning it already does inside each file:

  Copy

  SELECT sum(amount)
  FROM read_parquet('orders/year=*/month=*/*.parquet', hive_partitioning = true)
  WHERE year = 2026 AND month = 9;

• Row group size is the tuning knob that matters most. Too small (the PyArrow default of a few thousand rows in some paths) means pushdown has little to skip and per-group overhead adds up; too large and a filter that only needs one row still pulls in a huge group. 100_000–1_000_000 rows per group is a reasonable default for analytical workloads; PyArrow’s write_table(..., row_group_size=...) and Polars’ write_parquet(..., row_group_size=...) both expose it.

• Compression: snappy (Parquet’s default) trades ratio for decompression speed — usually the right call for data queried often. zstd compresses noticeably tighter at a modest CPU cost, worth it for cold archival data that’s scanned rarely.

Wiring it into a Python + Polars project

DuckDB and Polars aren’t competitors here — they read each other’s in-memory data at zero copy through Arrow, so the natural pattern is Polars for row-level transforms and DuckDB for anything that’s fundamentally a SQL join or a multi-table aggregation.

import duckdb
import polars as pl

orders = pl.scan_parquet("data/orders/*.parquet")
customers = pl.read_parquet("data/customers.parquet")

# DuckDB queries Polars DataFrames/LazyFrames directly by variable name —
# no export step, no copy, via Arrow under the hood.
result = duckdb.sql("""
    SELECT c.region, date_trunc('month', o.order_date) AS month, sum(o.amount) AS revenue
    FROM orders o
    JOIN customers c ON c.customer_id = o.customer_id
    GROUP BY c.region, month
    ORDER BY month
""").pl()  # .pl() collects the result back into a Polars DataFrame

• .pl() / .df() / .arrow() on a DuckDB relation collect the result as a Polars DataFrame, pandas DataFrame, or Arrow table respectively — pick whichever the rest of the pipeline expects next.

• Use Polars for: per-row expressions, string/regex cleanup, reshaping (pivot/unpivot), and anything that stays a single-table operation — its lazy engine pushes filters and projections into the Parquet scan itself (see Polars: Reading & Exploring Data).

• Use DuckDB for: multi-table joins, window functions, and anything more naturally expressed in SQL than in a chained expression API — particularly useful when the query itself (not just the result) needs to be inspectable or shared with someone who reads SQL more easily than Polars syntax.

• A DuckDB view over a Polars-cleaned frame is a common middle ground — run cheap, per-row cleanup in Polars, register the result, then let DuckDB handle the aggregation:

  Copy

  cleaned = orders.filter(pl.col("amount") > 0).collect()
  duckdb.register("clean_orders", cleaned)
  duckdb.sql("SELECT region, sum(amount) FROM clean_orders GROUP BY region").pl()

• Persisting the result: write back out to Parquet (result.write_parquet(...)) to keep it portable and tool-agnostic, or into a .duckdb file (CREATE TABLE AS SELECT ... against a file-backed connection) when the result itself needs to be queried repeatedly rather than just loaded once downstream.