Skip to main content

04 - DuckDB + Polars: pipeline lean Python

DuckDB and Polars together in a Python pipeline: SQL queries, columnar transformations, Parquet conversion and four paths measured on 10 million rows.

What do you think about this article?

04 - DuckDB + Polars: pipeline lean Python
Data Engineering 2027Photo:Firmbee.comPexels

At a Glance

DuckDB and Polars together in a Python pipeline: SQL queries, columnar transformations, Parquet conversion and four paths measured on 10 million rows.

DuckDB and Polars are often used together: the first to write SQL on files, the second to build columnar transformations in Python. Are they really complementary, or is one of the two enough? On a 10-million-row CSV I measured four equivalent paths: none wins by a landslide, and the "DuckDB then Polars" path is neither the fastest nor the lightest. The reason to combine them is ergonomics, and here you see where it pays off.

What each one does, in a few lines

DuckDB is an analytical SQL engine that runs inside your Python process and reads CSV, Parquet and JSON directly. It supports ACID transactions, as its documentation declares, and remains a real database even when you use it as a query engine on files. Polars is a DataFrame library with an expression API and a lazy mode, in which you build a computation plan and run it at the end. The comparison between Polars and pandas, with the measurements, is in the article Polars vs pandas; the choice between DuckDB and Spark is in DuckDB in production.

It is best to have read those two articles first or to know the equivalent: here I take for granted what a lazy plan is and why a Parquet file is read by columns. For the rest, Python and a bit of SQL are enough. The versions used are DuckDB 2.0.0 and Polars 2.1.0, both current as of 2026-11-13; both have changed API compared with the versions of the old tutorials, and along the article I point out where.

The test dataset

The file is a ledger of 10 million transactions over three years, with date, country (20 values), customer and amount. It is generated with a fixed seed, so two runs produce the same file and the sums reported can be repeated. The CSV weighs about 274 MB: big enough to make the engines work, small enough to fit in memory on a laptop.

import numpy as np, polars as pl, pathlib

pathlib.Path("dati").mkdir(exist_ok=True)
rng = np.random.default_rng(2024)                    # fixed seed
n = 10_000_000
countries = np.array(["IT", "DE", "FR", "ES", "NL", "PL", "SE", "AT", "BE", "PT", "IE", "DK", "FI", "GR", "CZ", "HU", "RO", "BG", "HR", "SK"])
days = np.datetime64("2023-01-01") + rng.integers(0, 3 * 365, n).astype("timedelta64[D]")

df = pl.DataFrame({
    "transaction_date": days,
    "country": countries[rng.integers(0, len(countries), n)],
    "customer_id": rng.integers(0, 2_000_000, n),
    "amount": np.round(rng.lognormal(3.5, 0.8, n), 2),
})
df.write_csv("dati/transazioni.csv")
print(df.schema)
print("rows:", df.height, "| CSV:", round(pathlib.Path("dati/transazioni.csv").stat().st_size / 1e6), "MB")
print("date range:", df["transaction_date"].min(), "->", df["transaction_date"].max())

# Output:
# Schema({'transaction_date': Date, 'country': String, 'customer_id': Int64, 'amount': Float64})
# rows: 10000000 | CSV: 274 MB
# date range: 2023-01-01 -> 2025-12-30

The schema that Polars prints (Date, String, Int64, Float64) is the one you will also get when reading the file with try_parse_dates=True or with DuckDB's read_csv, which recognizes dates on its own. In real CSVs this inference is the first breaking point: a single row with an anomalous value can change the type of the column, so in production declare the types explicitly. The folder and the file keep their Italian names, dati (data) and transazioni.csv (transactions), as in the original run.

DuckDB only: one query

The question "which are the three countries with the most revenue in 2024?" is a natural SQL query. duckdb.sql(...) returns a relation, that is a query not yet executed, and .pl() executes it and returns a Polars DataFrame. To print the numbers in full form I would have to set pl.Config.set_fmt_float("full"), otherwise Polars uses scientific notation, as the output below shows.

import duckdb

top = duckdb.sql("""
    SELECT country, round(sum(amount), 2) AS revenue, count(*) AS transactions
    FROM read_csv('dati/transazioni.csv')
    WHERE year(transaction_date) = 2024
    GROUP BY country
    ORDER BY revenue DESC
    LIMIT 3
""")
print(top.pl())

# Output:
# shape: (3, 3)
# ┌─────────┬──────────┬──────────────┐
# │ country ┆ revenue  ┆ transactions │
# │ ---     ┆ ---      ┆ ---          │
# │ str     ┆ f64      ┆ i64          │
# ╞═════════╪══════════╪══════════════╡
# │ AT      ┆ 7.6644e6 ┆ 167582       │
# │ SE      ┆ 7.6540e6 ┆ 167269       │
# │ NL      ┆ 7.6511e6 ┆ 167853       │
# └─────────┴──────────┴──────────────┘

A novelty of 2.0 that old tutorials do not know: .pl() exists on relations created with duckdb.sql or con.sql, but not on the result of con.execute(), and on relations fetch_arrow_table and fetch_record_batch are deprecated aliases of to_arrow_table and to_arrow_reader. If you copy two-year-old code that chains conn.execute(...).fetch_arrow_table(), check the Python page of the documentation for your version before blaming the data.

Handing over to Polars: filter in SQL, transform by columns

Some transformations are awkward in SQL and comfortable in Polars: wide tables with pivot, rolling sums by group, chains of reusable expressions. The scheme I propose is this: DuckDB reads the file and returns only the columns and rows that are needed, Polars does the rest in lazy mode. The listing ends with a cross-check: the per-country total computed by Polars must coincide with the one DuckDB computes by itself in SQL.

import duckdb
import polars as pl
pl.Config.set_fmt_float("full")

# 1) DuckDB reads the CSV, filters and projects ONLY the columns that are needed; .pl() returns a Polars DataFrame
year_df = duckdb.sql("""
    SELECT country, transaction_date, amount
    FROM read_csv('dati/transazioni.csv')
    WHERE year(transaction_date) = 2024
""").pl()
print("rows after the filter:", year_df.height, "| columns:", year_df.columns)

# 2) Polars: monthly revenue by country, wide table, 3-month rolling sum
monthly = (
    year_df.lazy()
    .group_by("country", pl.col("transaction_date").dt.month().alias("month"))
    .agg(pl.col("amount").sum().round(2).alias("revenue"))
    .sort("country", "month")
    .with_columns(pl.col("revenue").rolling_sum(window_size=3).over("country").alias("rolling_3_months"))
    .collect()
)
print(monthly.filter(pl.col("country") == "IT").head(4))

wide = monthly.pivot(on="month", index="country", values="revenue").sort("country")
print("wide table:", wide.shape)

# 3) cross-check: the per-country total must coincide with the one computed by DuckDB in SQL
sql = duckdb.sql("""
    SELECT country, round(sum(amount), 2) AS revenue FROM read_csv('dati/transazioni.csv')
    WHERE year(transaction_date) = 2024 GROUP BY country ORDER BY country""").pl()
totals = monthly.group_by("country").agg(pl.col("revenue").sum().round(2)).sort("country")
difference = (totals["revenue"] - sql["revenue"]).abs().max()
print("maximum gap between Polars and SQL:", round(difference, 2))

wide.write_parquet("dati/revenue_2024_wide.parquet")

# Output:
# rows after the filter: 3341070 | columns: ['country', 'transaction_date', 'amount']
# shape: (4, 4)
# ┌─────────┬───────┬───────────┬──────────────────┐
# │ country ┆ month ┆ revenue   ┆ rolling_3_months │
# │ ---     ┆ ---   ┆ ---       ┆ ---              │
# │ str     ┆ i8    ┆ f64       ┆ f64              │
# ╞═════════╪═══════╪═══════════╪══════════════════╡
# │ IT      ┆ 1     ┆ 643413.45 ┆ null             │
# │ IT      ┆ 2     ┆ 593282.06 ┆ null             │
# │ IT      ┆ 3     ┆ 646162.44 ┆ 1882857.95       │
# │ IT      ┆ 4     ┆ 625001.08 ┆ 1864445.58       │
# └─────────┴───────┴───────────┴──────────────────┘
# wide table: (20, 13)
# maximum gap between Polars and SQL: 0.0

The filter on the year 2024 reduces the data from 10 million to 3,341,070 rows and to three columns before entering Polars. The rolling sum rolling_sum(window_size=3).over("country") returns null for the first two months of each country, because the window is not yet complete: that is correct, and it has to be handled when you use that column. The maximum gap between the two computations is 0.0 after rounding to two decimals: the engines add up decimals in a different order, and without rounding you would see differences in the last decimal.

The four paths, measured

The decisive question remains: is it really worth having DuckDB read the file? To answer I compared four ways of computing the same result (2024 revenue by country), each in a new process: Polars reading the whole CSV and then filtering, Polars in lazy mode with scan_csv, DuckDB alone, and the hybrid path DuckDB then Polars. The file percorsi.py ("paths") contains the four ways; below you find the driver and the output, which shows minimum, median and maximum of 5 repetitions and the memory peak. Both listings print timings, so they keep the names and labels of the original run: duckdb_poi_polars is DuckDB then Polars, mediana is the median and picco the peak.

"""One path at a time, in a new process: python percorsi.py <path>. Prints: min median max (ms) and peak (MB)."""
import resource, sys, time
nome = sys.argv[1]
FILE = "dati/transazioni.csv"

def polars_eager():
    import polars as pl
    df = pl.read_csv(FILE, try_parse_dates=True)
    return df.filter(pl.col("transaction_date").dt.year() == 2024).group_by("country").agg(pl.col("amount").sum())

def polars_lazy():
    import polars as pl
    return (pl.scan_csv(FILE, try_parse_dates=True).filter(pl.col("transaction_date").dt.year() == 2024)
              .group_by("country").agg(pl.col("amount").sum()).collect())

def duckdb_sql():
    import duckdb
    return duckdb.sql(f"""SELECT country, sum(amount) AS amount FROM read_csv('{FILE}')
                          WHERE year(transaction_date) = 2024 GROUP BY country""").pl()

def duckdb_poi_polars():
    import duckdb, polars as pl
    anno = duckdb.sql(f"""SELECT country, amount FROM read_csv('{FILE}')
                          WHERE year(transaction_date) = 2024""").pl()
    return anno.group_by("country").agg(pl.col("amount").sum())

F = {"polars_eager": polars_eager, "polars_lazy": polars_lazy, "duckdb_sql": duckdb_sql, "duckdb_poi_polars": duckdb_poi_polars}[nome]
tempi = []
for _ in range(5):
    t0 = time.perf_counter(); F(); tempi.append(time.perf_counter() - t0)
tempi.sort()
print(f"{tempi[0]*1000:.0f} {tempi[2]*1000:.0f} {tempi[-1]*1000:.0f} {resource.getrusage(resource.RUSAGE_SELF).ru_maxrss/1024:.0f}")
import subprocess, sys

for nome in ("polars_eager", "polars_lazy", "duckdb_sql", "duckdb_poi_polars"):
    r = subprocess.run([sys.executable, "percorsi.py", nome], capture_output=True, text=True)
    if r.returncode:
        print(nome, "ERRORE:", r.stderr.strip().splitlines()[-1]); continue
    lo, med, hi, rss = r.stdout.split()
    print(f"{nome:<18} min {lo:>5}  mediana {med:>5}  max {hi:>5} ms   picco {rss:>5} MB")

# Output:
# polars_eager       min   249  mediana   314  max   415 ms   picco   912 MB
# polars_lazy        min   256  mediana   280  max   503 ms   picco   528 MB
# duckdb_sql         min   333  mediana   393  max   587 ms   picco   864 MB
# duckdb_poi_polars  min   347  mediana   378  max   642 ms   picco   969 MB

The verdict is not what you expect from an article about combining the two tools. The lightest path in memory is lazy Polars, with about 525-540 MB, because scan_csv pushes the filter into the read; it is also the fastest or tied. DuckDB alone sits in the middle, and the hybrid path is last in time and, with Polars reading everything, the heaviest in memory: the most likely cause is that it materializes in a DataFrame the 3.3 million filtered rows before Polars starts working (I did not isolate it with a separate measurement). The time differences among the first three are on the order of a few tens of milliseconds, within the variability of the runs.

How to read it: do not choose DuckDB to "read faster" a CSV, because on this workload it does not. Choose it when the read comes with joins, window functions or several files to merge with a glob, things for which SQL is shorter and clearer; choose Polars when the hard part is the transformation. This measurement concerns a single file, one filter and one aggregation: with more files, large joins or remote sources the result can change, and must be measured again.

Converting to Parquet without loading everything

A frequent task is converting the CSV to Parquet, which takes less space and is read by columns. The naive method in Polars is read_csv followed by write_parquet, which brings everything into memory; scan_csv followed by sink_parquet processes the file in chunks. DuckDB offers COPY (...) TO ... (FORMAT parquet). The listing runs the three methods in separate processes and verifies that the three files contain the same rows and the same sum. Since it prints times, it keeps the labels of the original run: righe is rows, somma the sum.

"""python conversione.py <method>: converts the whole CSV to Parquet. Prints seconds and memory peak (MB)."""
import resource, sys, time
metodo, SRC = sys.argv[1], "dati/transazioni.csv"
DST = f"dati/out_{metodo}.parquet"
t0 = time.perf_counter()
if metodo == "polars_eager":
    import polars as pl
    pl.read_csv(SRC, try_parse_dates=True).write_parquet(DST)
elif metodo == "polars_sink":
    import polars as pl
    pl.scan_csv(SRC, try_parse_dates=True).sink_parquet(DST)
else:
    import duckdb
    duckdb.sql(f"COPY (SELECT * FROM read_csv('{SRC}')) TO '{DST}' (FORMAT parquet)")
print(f"{time.perf_counter() - t0:.2f} {resource.getrusage(resource.RUSAGE_SELF).ru_maxrss / 1024:.0f}")
import subprocess, sys, duckdb

for metodo in ("polars_eager", "polars_sink", "duckdb_copy"):
    r = subprocess.run([sys.executable, "conversione.py", metodo], capture_output=True, text=True)
    if r.returncode:
        print(metodo, "ERRORE:", r.stderr.strip().splitlines()[-1]); continue
    secondi, picco = r.stdout.split()
    controllo = duckdb.sql(f"SELECT count(*), round(sum(amount), 2) FROM 'dati/out_{metodo}.parquet'").fetchall()[0]
    print(f"{metodo:<13} {secondi:>5} s  picco {picco:>5} MB  righe {controllo[0]}  somma {controllo[1]}")

# Output:
# polars_eager   0.76 s  picco   783 MB  righe 10000000  somma 456197116.21
# polars_sink    0.83 s  picco   565 MB  righe 10000000  somma 456197116.21
# duckdb_copy    0.90 s  picco  1006 MB  righe 10000000  somma 456197116.21

The three methods produce files with the same 10 million rows and the same sum (456,197,116.21), and that is the first thing to check in any conversion. The sink_parquet path has the lowest memory peak, about 570-580 MB against about 775 MB for the full read and over 1 GB for DuckDB, which by default uses all threads and a high memory limit. If memory is the constraint, limit DuckDB with memory_limit and threads as in the article DuckDB in production.

Testing the transformations

A data pipeline is code, and a function that receives a LazyFrame and returns another is easy to test without touching the disk. The listing defines revenue_by_country, the test on a four-row DataFrame in which one date falls on 31 December 2023 to verify that the filter works at the boundary, and the same logic written in SQL with Polars' sql() method, which queries the DataFrame by calling it self.

import polars as pl
from polars.testing import assert_frame_equal

def revenue_by_country(lf: pl.LazyFrame, year: int) -> pl.LazyFrame:
    """Pure transformation: receives a LazyFrame, returns another, does not touch the disk."""
    return (lf.filter(pl.col("transaction_date").dt.year() == year)
              .group_by("country").agg(pl.col("amount").sum().alias("revenue"))
              .sort("country"))

small = pl.DataFrame({
    "transaction_date": ["2024-03-01", "2024-07-15", "2023-12-31", "2024-01-10"],
    "country": ["IT", "IT", "IT", "DE"],
    "amount": [10.0, 5.5, 99.0, 7.0],
}).with_columns(pl.col("transaction_date").str.to_date())

expected = pl.DataFrame({"country": ["DE", "IT"], "revenue": [7.0, 15.5]})
assert_frame_equal(revenue_by_country(small.lazy(), 2024).collect(), expected)
print("the year filter excludes 2023-12-31: test passed")

# the same transformation, written in SQL on the DataFrame with Polars' sql() method
via_sql = small.sql("""
    SELECT country, sum(amount) AS revenue FROM self
    WHERE year(transaction_date) = 2024 GROUP BY country ORDER BY country""")
assert_frame_equal(via_sql, expected)
print("the SQL version gives the same result")

# Output:
# the year filter excludes 2023-12-31: test passed
# the SQL version gives the same result

The tests use assert_frame_equal from polars.testing, which compares types, names and values. Choosing test cases with a boundary (the last day of the previous year) catches the most common defect, a badly written year filter. Keep the tests on tiny data: they are there to protect the logic, not to measure performance, for which you have the measurements of the previous section.

Understanding where the time goes

Tutorials often point to LazyFrame.profile() to measure the time of each node: in Polars 2.x the method was removed, because it was designed for the in-memory engine and misleading with the streaming engine that is now the default. The listing tries it, prints the optimized plan with explain() and asks DuckDB for the EXPLAIN ANALYZE, filtering the output to show only the row counts and not the times, which change on every run.

import re
import duckdb
import polars as pl
from polars.exceptions import AttributeRemovedError

lf = (pl.scan_csv("dati/transazioni.csv", try_parse_dates=True)
        .filter(pl.col("transaction_date").dt.year() == 2024)
        .group_by("country").agg(pl.col("amount").sum()))

try:
    lf.profile()                                   # the method many tutorials point to
except AttributeRemovedError as e:
    print("Polars:", str(e).split(";")[0])

print(lf.explain())                                # the plan, without running the query

plan = duckdb.sql("""EXPLAIN ANALYZE SELECT country, sum(amount) FROM read_csv('dati/transazioni.csv')
                     WHERE year(transaction_date) = 2024 GROUP BY country""").fetchall()
for line in plan[0][1].splitlines():                 # the plan text also contains the times: I print only the counts
    text = line.strip("│╭╮╰╯┰┸┒┎┚┖─ ")
    if "BETWEEN" in text:
        print("DuckDB, the rewritten filter:", text)
    elif re.match(r"[\d,]+ rows", text):
        print("DuckDB, rows passing through an operator:", text.split()[0])

# Output:
# Polars: `profile` was removed in version 2.0
# AGGREGATE[maintain_order: false]
#   [col("amount").sum()] BY [col("country")]
#   FROM
#   simple π 2/2 ["country", "amount"]
#     Csv SCAN [dati/transazioni.csv]
#     PROJECT 3/4 COLUMNS
#     SELECTION: (col("transaction_date").dt.year() == 2024)
#     ESTIMATED ROWS: 10523554
# DuckDB, rows passing through an operator: 20
# DuckDB, rows passing through an operator: 3,341,070
# DuckDB, rows passing through an operator: 3,341,070
# DuckDB, the rewritten filter: transaction_date BETWEEN '2024-01-01'::DATE AND '2024-12-31'::DATE
# DuckDB, rows passing through an operator: 3,341,070
# DuckDB, rows passing through an operator: 10,000,000

In the Polars plan you see that the year filter and the choice of three columns out of four are inside the CSV scan; in the DuckDB plan the filter year(transaction_date) = 2024 was rewritten as a BETWEEN range between two dates, the form the optimizer knows how to push into the read. The number 10,000,000 next to 3,341,070 says how many rows the scan read and how many were left after the filter: if the two numbers coincide, your filter is not reducing anything.

How to choose

On the basis of the measurements and the listings, the practical rule is summed up in this table. The order of the rows does not matter: they are independent cases.

SituationChoiceWhy
One file, filter and aggregationLazy Polars or DuckDBComparable times; lazy Polars has the lowest memory peak in my measurement
Joins, windows, several files to mergeDuckDBSQL is shorter and the sources are read with a glob
Pivots, rolling sums by group, reusable transformationsPolarsExpressions compose into testable functions
Converting a large file to ParquetPolars with sink_parquetProcesses in chunks with the lowest peak
Versioning the outputsAn external systemSee lakeFS and the license to evaluate

The hybrid path makes sense when the SQL part really reduces the data: if DuckDB filters 10 million rows down to 10 thousand, the handover to Polars costs nothing. If the filter lets a third of the data through, as here, the handover is what costs. On where to place the pipeline inside a complete architecture, the last article of the series is Modern Data Stack: a minimal architecture. For the table format to publish the results on, see Iceberg, Delta Lake and Hudi.

What you can do now

You can make DuckDB and Polars talk with the current methods (duckdb.sql(...).pl()), measure how much memory and time each path costs, convert a large CSV to Parquet in chunks, write a testable transformation and read the plans of both engines. You also know that combining the two is not an automatic gain: it depends on how many rows the initial filter removes.

Frequently asked questions

Is DuckDB transactional?

Yes: the documentation declares support for ACID transactions. The constraint that matters in production is not transactionality but concurrency, with a single process reading and writing per database file.

Why does .pl() not work after con.execute()?

In DuckDB 2.0 the method is available on relations created with duckdb.sql or con.sql, not on the result of con.execute(). Use con.sql(...) and call .pl() on the relation.

Is lazy mode or eager mode better in Polars?

In my measurements the lazy read with scan_csv used less memory than the full read (about 530 MB against 910 MB) with equal or better times. For simple queries on small data the difference is not noticeable.

Can I query a Polars DataFrame from DuckDB?

Yes, by citing it by name in the query. The documentation specifies that a LazyFrame receives the column selection and the translatable filters, while a DataFrame is converted in full and filtered afterwards.

Sources

All the listings with output were run with Python 3.14.4, DuckDB 2.0.0 and Polars 2.1.0; the deterministic ones twice with identical output. The tables of times and memory come from a single run and change on every launch; the four-path comparison was repeated twice with the same ordering of results. The code of the paths and of the conversion is shown in the text. The listings that print timings keep the labels of the original run, in Italian, and were not rerun for the English edition.

Try These Tools

Free developer tools related to this topic.

Commenti

Caricamento commenti...

Sign in to comment

New articles and tools on Telegram too

The Telegram channel flags the site's new articles and tools.

Did you like this article?

Explore other content on the blog or discover my projects