duckdb-and-polars-fast-data

DuckDB & Polars: Fast Local & In-Process Data Engineering

Production guide for architecting ultra-fast, single-node data processing engines using DuckDB and Polars. Ideal for sub-second analytics, serverless micro-ETL pipelines, and memory-efficient out-of-core computations.

---

1. Architectural Foundation & Comparison

Feature	DuckDB	Polars
Engine Architecture	In-process vectorized C++ SQL OLAP Database	Multi-threaded Rust DataFrame Engine
Primary Interface	SQL, Python DB-API, Relational API	Expression API, LazyFrame / DataFrame
Execution Model	Vectorized Query Execution (Morsel Driven)	Query Engine with Expression Fusion & Pushdowns
Out-Of-Core Execution	Native automatic disk spilling for larger-than-RAM	Native sink_parquet() / streaming engine
Data Interop	Zero-copy Apache Arrow, Parquet, Iceberg	Zero-copy PyArrow, Arrow C Data Interface

---

2. Zero-Copy Interoperability & Memory Pipeline

┌─────────────────────────┐     Apache Arrow Interop      ┌─────────────────────────┐
│ DuckDB (SQL Engine)     │ ◄───────────────────────────► │ Polars (Lazy Engine)    │
│ Direct Parquet Scan     │         (Zero Copy)           │ Expressions & Streaming │
└─────────────────────────┘                               └─────────────────────────┘

By leveraging Apache Arrow as a unified in-memory representation, datasets can be passed between DuckDB and Polars with zero memory duplication overhead.

---

3. Idempotent Write Patterns & Partitioning

3.1 DuckDB Atomic Partition Overwrite

import duckdb

conn = duckdb.connect("analytics.duckdb")

# Atomic replacement of table partition within transaction block
conn.execute("""
BEGIN TRANSACTION;

DELETE FROM sales_fact WHERE sale_date = '2026-07-18';

INSERT INTO sales_fact 
SELECT * FROM read_parquet('s3://ingest/2026-07-18/*.parquet');

COMMIT;
""")

3.2 Polars Atomic Out-of-Core Partition Sink

import polars as pl

# Write LazyFrame stream to Parquet atomically
lazy_df = pl.scan_parquet("s3://raw-events/*.parquet") \
    .filter(pl.col("event_date") == "2026-07-18") \
    .with_columns([
        (pl.col("raw_amount") * pl.col("fx_rate")).alias("amount_usd")
    ])

# Sink directly to disk with chunked memory allocation
lazy_df.sink_parquet(
    "s3://silver-events/event_date=2026-07-18/data.parquet",
    compression="snappy",
    statistics=True
)