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.
| Situation | Choice | Why |
|---|---|---|
| One file, filter and aggregation | Lazy Polars or DuckDB | Comparable times; lazy Polars has the lowest memory peak in my measurement |
| Joins, windows, several files to merge | DuckDB | SQL is shorter and the sources are read with a glob |
| Pivots, rolling sums by group, reusable transformations | Polars | Expressions compose into testable functions |
| Converting a large file to Parquet | Polars with sink_parquet | Processes in chunks with the lowest peak |
| Versioning the outputs | An external system | See 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()?
.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
- DuckDB 2.0: Integration with Polars -
.pl()method on relations,lazyparameter, pushdown towards LazyFrames, consulted on 2026-11-13 - DuckDB 2.0: Export to Apache Arrow -
to_arrow_table,to_arrow_readerand deprecated aliases, consulted on 2026-11-13 - DuckDB 2.0: Transaction Management - support for ACID transactions, consulted on 2026-11-13
- Polars user guide: Upgrade guide to version 2 - default streaming engine and removal of
profile, consulted on 2026-11-13 - Polars: LazyFrame.collect - the available engines, consulted on 2026-11-13
- PyPI: duckdb - version 2.0.0 and MIT license, consulted on 2026-11-13
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.

Commenti
Caricamento commenti...
Sign in to comment