Single-Node Analytics: DuckDB, Polars, and Arrow

Three tools, different responsibilities

DuckDB is an analytical SQL engine that can run inside a process and query files or registered tables. Polars is a DataFrame library with an expression engine and lazy query planning. Apache Arrow defines a columnar representation and tools for exchanging data. Arrow buffers are not themselves a SQL optimizer, and neither engine requires moving every source into a server database before analysis.

A useful starting workload is a bounded investigation over a reproducible file set: select relevant columns, filter rows, aggregate, and inspect a small answer. The same tools can run scheduled production jobs, but scheduling, access control, publication, monitoring, and recovery still need owners. Local execution does not remove those responsibilities.

Size the working set, not only the files

Out-of-Core Processing uses storage to support work whose intermediate state does not all fit in memory. DuckDB can spill supported operations to temporary storage; Polars can execute supported plans in streaming batches. This does not mean that every query works with an arbitrary memory limit. Large join outputs, high-cardinality groups, sorts, unsupported paths, and the final materialized result can still exhaust resources.

A compressed Parquet file size is not the required RAM. Include decoded columns, concurrent operators, thread-local state, result size, and other processes. In DuckDB, a buffer-manager memory limit is not a hard ceiling on all process allocations. Reserve and monitor scratch space too. In Polars, collect(engine=”streaming”) requests streaming execution, while unsupported operations may use in-memory execution; inspect the plan and measure the actual workload.

The examples below deliberately use tiny data. They verify results and API behavior, not spilling, peak memory, speed, or a maximum dataset size. Establish capacity with representative distributions and a bounded test, recording peak memory, temporary disk usage, elapsed time, and failure behavior. Avoid filling a shared machine until it crashes.

Read lazily and preserve the intended answer

Use scan_parquet in a Polars lazy plan when the source is Parquet. Reading a whole file eagerly and then calling lazy() cannot undo that earlier read. DuckDB can place filters and column selection in a file query. A displayed plan is useful evidence about intended work, but a nonempty plan does not prove that bytes were skipped or that a query was fast.

The first exercise accepts known groups with nonmissing, nonnegative amounts. A has zero and ten, so its total is ten across two rows. B has seven and minus one, so only seven remains. A missing group and a missing amount are excluded. Both engines must match this independently written answer and accept a typed empty file. The integer cast makes the displayed aggregate type explicit for this small safe range; test overflow and larger values separately.

Port semantics before comparing speed

The second exercise joins three facts to a dimension with a repeated key. The matching fact appears twice; an unmatched key remains once. Ordinary equality leaves a null key unmatched. Null-safe equality in SQL, or nulls_equal=True in Polars, deliberately matches it to the null dimension key. Neither answer is universally correct: the data contract decides whether missing identifiers may match.

When porting a Spark or warehouse query, compare schema, duplicate multiplicity, null and NaN behavior, time zones, casts, decimal arithmetic, and required ordering. A small DuckDB test is useful for a shared rule, but does not replace tests in the deployed engine for its dialect, execution, storage integration, or permissions. Several engines can agree on the same incorrectly translated business rule.

Arrow exchange and operational boundaries

The final exercise passes an Arrow table through Polars and DuckDB and checks values and nulls. Successful interchange is not proof of zero-copy. Type conversions, buffer layout, ownership and lifetime, and materializing an output can require allocations. Parquet decoding is also different from sharing an existing compatible Arrow buffer.

Choose a single-node job using measured workload requirements, not a universal gigabyte threshold. Compare the same input, answer, hardware, concurrency, and cold or warm conditions. Include file requests, transfer, scratch storage, and operation costs where relevant. A fast local sample cannot establish production service latency or a cheaper cloud bill.

Embedded concurrency and a shared warehouse service have different operating models. DuckDB supports concurrent work within a process; multi-process read/write arrangements require the supported access architecture and coordination. Remote files still require network access and appropriate credentials. Pin a consistent file set, publish results safely, and decide how another machine resumes after failure before relying on a local job for shared reporting.

Lab: compare two engines and an Arrow boundary

Tested with Python 3.12, duckdb==1.5.5, polars==1.44.2, pyarrow==25.0.1. Install these packages in an isolated environment. Run the setup before each example. Examples use temporary local files and clean them up; no cloud account is required for this local lab. Inspect the assertions before opening each solution. CPU-cache detection warnings in the restricted test environment did not change the recorded output.

from pathlib import Path
import tempfile
import duckdb
import polars as pl
import pyarrow as pa
import pyarrow.parquet as pq

schema = pa.schema([("id",pa.int64()),("group",pa.string()),("amount",pa.int64())])

def fixture():
    return pa.Table.from_pylist([
        {"id":1,"group":"A","amount":0},
        {"id":2,"group":"A","amount":10},
        {"id":3,"group":"A","amount":None},
        {"id":4,"group":"B","amount":7},
        {"id":5,"group":"B","amount":-1},
        {"id":6,"group":None,"amount":5}], schema=schema)

sql = '\n'.join([
    'SELECT "group", CAST(sum(amount) AS BIGINT) AS total, count(*) AS n',
    'FROM read_parquet(?)',
    'WHERE "group" IS NOT NULL AND amount IS NOT NULL AND amount >= 0',
    'GROUP BY "group" ORDER BY "group"'])

def polars_query(path):
    return (pl.scan_parquet(path)
        .filter(pl.col("group").is_not_null() & pl.col("amount").is_not_null() & (pl.col("amount") >= 0))
        .group_by("group").agg(pl.col("amount").sum().alias("total"),pl.len().cast(pl.Int64).alias("n"))
        .sort("group"))

Keep zero, exclude missing and negative values, then check empty input.

with tempfile.TemporaryDirectory(prefix="de026-") as folder:
    path = str(Path(folder)/"input.parquet")
    pq.write_table(fixture(), path)
    with duckdb.connect() as db:
        duck_rows = db.execute(sql,[path]).fetchall()
        duck_plan = db.execute("EXPLAIN "+sql,[path]).fetchall()
    lazy = polars_query(path)
    polars_rows = lazy.collect(engine="streaming").rows()
    expected = [("A",10,2),("B",7,1)]
    assert duck_rows == polars_rows == expected
    assert duck_plan and lazy.explain(optimized=True)
    empty_path = str(Path(folder)/"empty.parquet")
    pq.write_table(pa.Table.from_pylist([],schema=schema),empty_path)
    with duckdb.connect() as db:
        assert db.execute(sql,[empty_path]).fetchall() == []
    assert polars_query(empty_path).collect(engine="streaming").rows() == []
    print("DuckDB and Polars match the explicit expected groups:", True)
    print("zero retained, null and negative values excluded:", True)
    print("typed empty Parquet input supported by both:", True)
# DuckDB and Polars match the explicit expected groups: True
# zero retained, null and negative values excluded: True
# typed empty Parquet input supported by both: True

Check duplicate join matches and choose the null-key rule explicitly.

facts = pa.table({"id":[1,2,3],"key":pa.array([1,None,9],type=pa.int64())})
dimension = pa.table({"key":pa.array([1,1,None],type=pa.int64()),"label":["x","y","unknown"]})
with duckdb.connect() as db:
    db.register("facts",facts)
    db.register("dimension",dimension)
    ordinary = db.execute('SELECT id,label FROM facts f LEFT JOIN dimension d ON f.key=d.key ORDER BY id,label').fetchall()
    null_safe = db.execute('SELECT id,label FROM facts f LEFT JOIN dimension d ON f.key IS NOT DISTINCT FROM d.key ORDER BY id,label').fetchall()
f, d = pl.from_arrow(facts), pl.from_arrow(dimension)
p1 = f.join(d,on="key",how="left",nulls_equal=False).select("id","label").sort("id","label").rows()
p2 = f.join(d,on="key",how="left",nulls_equal=True).select("id","label").sort("id","label").rows()
assert ordinary == p1 == [(1,"x"),(1,"y"),(2,None),(3,None)]
assert null_safe == p2 == [(1,"x"),(1,"y"),(2,"unknown"),(3,None)]
print("duplicate matches and unmatched rows preserved:", True)
print("null-safe matching is an explicit different answer:", True)
# duplicate matches and unmatched rows preserved: True
# null-safe matching is an explicit different answer: True

Check Arrow values and nulls without claiming buffer sharing.

source = fixture()
frame = pl.from_arrow(source)
round_trip = frame.to_arrow()
assert round_trip.to_pylist() == source.to_pylist()
with duckdb.connect() as db:
    db.register("arrow_input",round_trip)
    result = db.execute('SELECT id,"group",amount FROM arrow_input ORDER BY id').to_arrow_table()
assert result.to_pylist() == source.to_pylist()
assert result.num_rows == 6 and result.column("amount").null_count == 1
print("Arrow values and nulls survive both engine boundaries:", True)
print("buffer sharing or zero-copy was measured:", False)
# Arrow values and nulls survive both engine boundaries: True
# buffer sharing or zero-copy was measured: False

References: DuckDB workload tuning, DuckDB concurrency, Polars streaming, Arrow columnar format.