How To Use DuckDB and Polars Together for Fast Analytics

Intermediate

You are loading a 10-million-row sales dataset. Polars rips through the CSV in under 2 seconds, and you have a clean DataFrame in memory. Then the real work starts: you need a window function ranking each salesperson within their region, a multi-table join against a product catalogue, and a filtered aggregation with a CTE. Polars can do all of that — but the syntax is verbose, the learning curve is steep, and you already know how to express complex analytics in SQL. What if you could just… write SQL on top of your Polars DataFrame?

That is exactly what DuckDB gives you. DuckDB is an in-process SQL OLAP database that natively understands Apache Arrow — the same memory format Polars uses internally. When you point DuckDB at a Polars DataFrame, no data gets copied. DuckDB reads directly from the Arrow buffers already in memory, executes your SQL, and hands back a result you can convert back to Polars with a single method call. Both libraries are available on pip and work without any external database server.

This article covers the full integration: querying Polars DataFrames with DuckDB SQL, converting results back to Polars, using DuckDB to load files directly into Polars, combining lazy Polars scans with DuckDB aggregations, and a real-world analytics project that uses both libraries to process e-commerce event data. By the end you will know exactly when to reach for each tool and how to wire them together in a single pipeline.

DuckDB and Polars Together: Quick Example

Before diving into details, here is the core pattern in its simplest form. You create a Polars DataFrame, register it in a DuckDB connection, run SQL against it, and convert the result back to Polars.

# quick_example.py
import polars as pl
import duckdb

# Create a Polars DataFrame
df = pl.DataFrame({
    "product": ["Widget", "Gadget", "Widget", "Gadget", "Gizmo"],
    "region":  ["North", "North", "South", "South", "North"],
    "revenue": [1200, 850, 990, 1450, 620],
})

# Query it directly with DuckDB SQL -- no copy, no conversion
result = duckdb.sql("""
    SELECT
        region,
        SUM(revenue)  AS total_revenue,
        COUNT(*)      AS sales_count
    FROM df
    GROUP BY region
    ORDER BY total_revenue DESC
""").pl()  # .pl() converts DuckDB result to Polars DataFrame

print(result)

Output:

shape: (3, 3)
+---------+---------------+-------------+
| region  | total_revenue | sales_count |
| ---     | ---           | ---         |
| str     | i64           | i64         |
+=========+===============+=============+
| South   | 2440          | 2           |
| North   | 2670          | 3           |
| ...     | ...           | ...         |
+---------+---------------+-------------+

The key call is duckdb.sql("... FROM df ..."). DuckDB finds the variable df in the calling Python scope automatically — no explicit registration needed when you use duckdb.sql(). The .pl() at the end materializes the result as a Polars DataFrame. That entire round-trip — SQL execution plus result conversion — is typically faster than the equivalent Polars expression on datasets above a few hundred thousand rows, because DuckDB uses vectorized SQL execution tuned for aggregations and joins.

The sections below unpack each part of this integration: installation, how the zero-copy Arrow bridge actually works, complex SQL patterns that shine over Polars expressions, loading files, and a complete real-world pipeline.

What Is DuckDB and Why Pair It with Polars?

DuckDB is an embedded analytical database — “embedded” meaning it runs inside your Python process, not as a separate server. There is no daemon to start, no connection string pointing to localhost:5432, no Docker container. You import duckdb and it is ready. Under the hood, DuckDB uses a columnar execution engine optimized for OLAP queries: aggregations, window functions, multi-table joins over large datasets.

Polars is a DataFrame library written in Rust, built on the Apache Arrow columnar memory format. It is dramatically faster than Pandas for most workloads because it avoids Python’s per-row overhead and uses SIMD instructions for bulk operations. Polars is excellent at row filtering, column expressions, type casting, lazy evaluation, and parallel scans of Parquet files.

So why combine them? Each has a natural home:

Task	Better Tool	Reason
Read Parquet / CSV into memory	Polars	Faster scan, lazy evaluation, schema inference
Row filtering on simple conditions	Polars	Concise expressions, compiled Rust speed
Complex GROUP BY + HAVING	DuckDB	SQL is expressive; DuckDB optimizer handles it well
Window functions (RANK, LAG, LEAD)	DuckDB	SQL window syntax beats Polars .over() for complexity
Multi-table JOINs	DuckDB	SQL JOIN syntax is cleaner than Polars .join() chains
Lazy scan of 50GB dataset	Polars (LazyFrame)	Only reads needed columns/rows from disk
Export cleaned data to Parquet	Polars	Fast write, good compression options

The zero-copy bridge is the technical reason this pairing is fast. Polars stores data in Arrow buffers. DuckDB can read Arrow buffers without duplicating them in memory. Passing a 5GB Polars DataFrame to DuckDB costs essentially zero bytes of additional RAM — DuckDB just borrows the existing memory. The same works in reverse: .pl() on a DuckDB result converts the Arrow-format result to a Polars DataFrame without an intermediate Python object allocation.

Installation

Both packages install from PyPI with no system dependencies.

# install_deps.sh
pip install polars duckdb

Output:

Successfully installed polars-1.x.x duckdb-1.x.x

Verify the install by checking that DuckDB can see a Polars DataFrame:

# verify_install.py
import polars as pl
import duckdb

df = pl.DataFrame({"x": [1, 2, 3]})
result = duckdb.sql("SELECT SUM(x) AS total FROM df").pl()
print(result)
# shape: (1, 1)
# total: 6
print("DuckDB version:", duckdb.__version__)
print("Polars version:", pl.__version__)

If you see the sum and version strings, both libraries are working and the Arrow bridge is operational.

Querying Polars DataFrames with DuckDB SQL

Automatic Scope Lookup

When you call duckdb.sql(), DuckDB inspects the local Python scope for any variable name used in the FROM clause. If df is in scope, FROM df works automatically. This is the simplest pattern and covers most use cases.

# scope_lookup.py
import polars as pl
import duckdb

orders = pl.DataFrame({
    "order_id":   [101, 102, 103, 104, 105],
    "customer":   ["Alice", "Bob", "Alice", "Carol", "Bob"],
    "amount":     [250.0, 89.0, 410.0, 125.0, 320.0],
    "category":   ["Electronics", "Books", "Electronics", "Books", "Electronics"],
})

# DuckDB finds 'orders' in scope automatically
top_customers = duckdb.sql("""
    SELECT
        customer,
        COUNT(*)        AS order_count,
        SUM(amount)     AS total_spent,
        AVG(amount)     AS avg_order
    FROM orders
    GROUP BY customer
    HAVING SUM(amount) > 200
    ORDER BY total_spent DESC
""").pl()

print(top_customers)

Output:

shape: (2, 4)
+----------+-------------+-------------+------------+
| customer | order_count | total_spent | avg_order  |
| str      | i64         | f64         | f64        |
+==========+=============+=============+============+
| Alice    | 2           | 660.0       | 330.0      |
| Bob      | 2           | 409.0       | 204.5      |
+----------+-------------+-------------+------------+

The HAVING clause is a good example of where SQL syntax shines — filtering after aggregation is two words in SQL but requires a second .filter() call in Polars. Carol is excluded because her single $125 order doesn’t meet the threshold.

Explicit Connection for Multiple DataFrames

When your query spans multiple DataFrames, use an explicit duckdb.connect() and register each one. This makes the relationship between SQL table names and Python variables explicit and avoids scope confusion in larger scripts.

# multi_table.py
import polars as pl
import duckdb

products = pl.DataFrame({
    "product_id":  [1, 2, 3, 4],
    "name":        ["Widget", "Gadget", "Gizmo", "Doohickey"],
    "category":    ["Hardware", "Software", "Hardware", "Software"],
    "unit_price":  [49.99, 99.99, 19.99, 149.99],
})

sales = pl.DataFrame({
    "sale_id":    [201, 202, 203, 204, 205, 206],
    "product_id": [1, 2, 1, 3, 4, 2],
    "quantity":   [3, 1, 5, 10, 2, 4],
})

# Register both DataFrames under explicit table names
con = duckdb.connect()
con.register("products", products)
con.register("sales", sales)

revenue_by_category = con.execute("""
    SELECT
        p.category,
        SUM(s.quantity * p.unit_price) AS total_revenue,
        SUM(s.quantity)                AS units_sold
    FROM sales s
    JOIN products p ON s.product_id = p.product_id
    GROUP BY p.category
    ORDER BY total_revenue DESC
""").pl()

print(revenue_by_category)

Output:

shape: (2, 3)
+----------+---------------+------------+
| category | total_revenue | units_sold |
| str      | f64           | i64        |
+==========+===============+============+
| Software | 498.95        | 5          |
| Hardware | 349.83        | 18         |
+----------+---------------+------------+

Even though Hardware sold far more units (18 vs 5), Software wins on revenue because of higher unit prices. A multi-table join in pure Polars requires .join() plus column renaming to resolve name conflicts — the SQL version above is considerably easier to read and maintain.

Window Functions: Where SQL Destroys Polars Syntax

Window functions — RANK(), ROW_NUMBER(), LAG(), LEAD(), running totals — are where SQL earns its keep. The Polars equivalent using .over() gets unwieldy fast. Here is a ranking query that would take 15+ lines of Polars expressions:

# window_functions.py
import polars as pl
import duckdb

employees = pl.DataFrame({
    "name":       ["Alice", "Bob", "Carol", "Dave", "Eve", "Frank"],
    "department": ["Eng", "Eng", "Sales", "Sales", "Eng", "Sales"],
    "salary":     [95000, 88000, 72000, 81000, 102000, 69000],
})

ranked = duckdb.sql("""
    SELECT
        name,
        department,
        salary,
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
        salary - AVG(salary) OVER (PARTITION BY department)       AS vs_dept_avg,
        SUM(salary) OVER (PARTITION BY department ORDER BY salary DESC
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
                                                                  AS running_total
    FROM employees
    ORDER BY department, dept_rank
""").pl()

print(ranked)

Output:

shape: (6, 6)
+-------+------------+--------+-----------+-------------+---------------+
| name  | department | salary | dept_rank | vs_dept_avg | running_total |
| str   | str        | i64    | u64       | f64         | i64           |
+=======+============+========+===========+=============+===============+
| Eve   | Eng        | 102000 | 1         | 16333.33    | 102000        |
| Alice | Eng        | 95000  | 2         | 9333.33     | 197000        |
| Bob   | Eng        | 88000  | 3         | 2333.33     | 285000        |
| Dave  | Sales      | 81000  | 1         | 7333.33     | 81000         |
| Carol | Sales      | 72000  | 2         | -1666.67    | 153000        |
| Frank | Sales      | 69000  | 3         | -4333.33    | 222000        |
+-------+------------+--------+-----------+-------------+---------------+

Three different window computations — rank, deviation from mean, cumulative sum — expressed in a single SQL query. Polars could do this with three separate .with_columns() calls using .over(), but the SQL version communicates intent more clearly: anyone familiar with SQL analytics can read this at a glance.

Loading Files Directly with DuckDB into Polars

DuckDB can read Parquet, CSV, and JSON files directly in SQL without loading them through Python first. The result can be immediately converted to a Polars DataFrame. This is useful when you want DuckDB’s SQL filtering to happen before materializing data in memory — especially for large files where you only need a subset.

# load_files.py
import polars as pl
import duckdb
import tempfile
import os

# Create a sample Parquet file to demonstrate with
sample = pl.DataFrame({
    "date":     ["2024-01-01", "2024-01-02", "2024-01-03", "2024-01-04", "2024-01-05"],
    "product":  ["Widget", "Gadget", "Widget", "Gizmo", "Widget"],
    "sales":    [1200, 850, 990, 430, 1100],
    "region":   ["East", "West", "East", "West", "North"],
})

# Write to a temp Parquet file
parquet_path = "/tmp/sample_sales.parquet"
sample.write_parquet(parquet_path)

# Query the Parquet file directly -- DuckDB reads it without loading into Python first
result = duckdb.sql(f"""
    SELECT
        product,
        SUM(sales)  AS total_sales,
        COUNT(*)    AS num_days
    FROM read_parquet('{parquet_path}')
    WHERE sales > 500
    GROUP BY product
    ORDER BY total_sales DESC
""").pl()

print(result)

Output:

shape: (2, 3)
+---------+-------------+----------+
| product | total_sales | num_days |
| str     | i64         | i64      |
+=========+=============+==========+
| Widget  | 3290        | 3        |
| Gadget  | 850         | 1        |
+---------+-------------+----------+

The WHERE sales > 500 filter runs inside DuckDB’s Parquet reader — rows below the threshold never enter Python memory. For a 50-million-row Parquet file where you only need rows matching certain conditions, this is significantly more memory-efficient than pl.read_parquet() followed by .filter(). DuckDB also supports read_csv() and read_json() with the same pattern.

Converting Between DuckDB and Polars

DuckDB’s result object has several conversion methods. Knowing which one to use avoids unnecessary copies:

# conversions.py
import polars as pl
import duckdb

df = pl.DataFrame({
    "city":       ["Austin", "Denver", "Portland", "Austin", "Denver"],
    "visits":     [340, 210, 175, 290, 310],
    "converted":  [42, 31, 28, 38, 45],
})

rel = duckdb.sql("""
    SELECT city, SUM(visits) AS total_visits, SUM(converted) AS total_converted
    FROM df
    GROUP BY city
""")

# .pl()     -- Polars DataFrame (most common; returns when you want to keep working in Polars)
as_polars = rel.pl()
print("Polars:")
print(as_polars)

# .df()     -- Pandas DataFrame (only if you need Pandas downstream)
# as_pandas = rel.df()

# .fetchall() -- list of tuples (lightweight; no DataFrame overhead)
rel2 = duckdb.sql("SELECT city, SUM(visits) AS total_visits FROM df GROUP BY city")
as_tuples = rel2.fetchall()
print("\nTuples:", as_tuples)

# .arrow()  -- PyArrow Table (if you need Arrow format for another Arrow-aware library)
rel3 = duckdb.sql("SELECT city, SUM(visits) AS total_visits FROM df GROUP BY city")
as_arrow = rel3.arrow()
print("\nArrow schema:", as_arrow.schema)

Output:

Polars:
shape: (3, 3)
+----------+--------------+------------------+
| city     | total_visits | total_converted  |
| str      | i64          | i64              |
+==========+==============+==================+
| Austin   | 630          | 80               |
| Denver   | 520          | 76               |
| Portland | 175          | 28               |
+----------+--------------+------------------+

Tuples: [('Austin', 630), ('Denver', 520), ('Portland', 175)]

Arrow schema: city: string
total_visits: int64

Use .pl() when you want to continue with Polars operations after the SQL step. Use .fetchall() when you just need the values and do not need a DataFrame at all — it skips the overhead of creating a Polars object. Use .arrow() if you are passing the result to another Arrow-aware library such as PyArrow datasets or Lance.

Combining Lazy Polars Scans with DuckDB

Polars LazyFrame lets you describe transformations on data without executing them immediately. You can collect a LazyFrame into an eager DataFrame and then pass it to DuckDB — or you can collect the portion you need and let DuckDB handle the aggregation. This is the most memory-efficient pattern for large files.

# lazy_polars_duckdb.py
import polars as pl
import duckdb

# Simulate multiple Parquet shards (in practice these would be files on disk)
shard1 = pl.DataFrame({
    "user_id":  [1, 2, 3, 4, 5],
    "event":    ["view", "click", "view", "purchase", "click"],
    "page":     ["home", "product", "home", "cart", "category"],
    "duration": [12, 45, 8, 120, 30],
})

shard2 = pl.DataFrame({
    "user_id":  [6, 7, 8, 1, 3],
    "event":    ["view", "purchase", "click", "purchase", "view"],
    "page":     ["product", "cart", "home", "cart", "category"],
    "duration": [55, 200, 15, 180, 25],
})

# Use lazy evaluation to concatenate shards and filter before collecting
lazy_events = pl.concat([shard1.lazy(), shard2.lazy()]).filter(
    pl.col("duration") > 10
)

# Collect only when needed for DuckDB
events = lazy_events.collect()

# DuckDB handles the complex aggregation
summary = duckdb.sql("""
    SELECT
        event,
        COUNT(*)        AS event_count,
        AVG(duration)   AS avg_duration,
        COUNT(DISTINCT user_id) AS unique_users
    FROM events
    GROUP BY event
    ORDER BY event_count DESC
""").pl()

print(summary)

Output:

shape: (3, 4)
+-----------+-------------+--------------+--------------+
| event     | event_count | avg_duration | unique_users |
| str       | i64         | f64          | i64          |
+===========+=============+==============+==============+
| view      | 4           | 25.0         | 4            |
| click     | 3           | 30.0         | 3            |
| purchase  | 3           | 166.67       | 3            |
+-----------+-------------+--------------+--------------+

The lazy filter (duration > 10) runs during the .collect() call and eliminates short-duration events before DuckDB ever sees them. The resulting DataFrame is smaller, the DuckDB query is faster, and peak memory is lower than loading all shards and filtering afterward.

CTEs and Subqueries for Multi-Step Analytics

Common Table Expressions (CTEs) let you break a complex query into named steps that read like documentation. DuckDB handles them efficiently — each CTE is computed once and referenced by name in subsequent CTEs or the final SELECT.

# cte_analytics.py
import polars as pl
import duckdb

transactions = pl.DataFrame({
    "txn_id":      list(range(1, 11)),
    "customer_id": [101, 102, 101, 103, 102, 101, 104, 103, 102, 104],
    "amount":      [120, 45, 330, 90, 210, 75, 450, 60, 180, 300],
    "month":       ["Jan", "Jan", "Jan", "Jan", "Feb", "Feb", "Feb", "Feb", "Feb", "Mar"],
})

result = duckdb.sql("""
    WITH customer_totals AS (
        -- Step 1: total spend per customer
        SELECT customer_id, SUM(amount) AS total_spend
        FROM transactions
        GROUP BY customer_id
    ),
    spend_percentile AS (
        -- Step 2: rank customers by spend
        SELECT
            customer_id,
            total_spend,
            NTILE(2) OVER (ORDER BY total_spend DESC) AS spend_tier
        FROM customer_totals
    )
    -- Step 3: label tiers and return
    SELECT
        customer_id,
        total_spend,
        CASE spend_tier WHEN 1 THEN 'High Value' ELSE 'Standard' END AS segment
    FROM spend_percentile
    ORDER BY total_spend DESC
""").pl()

print(result)

Output:

shape: (4, 3)
+-------------+-------------+------------+
| customer_id | total_spend | segment    |
| i64         | i64         | str        |
+=============+=============+============+
| 104         | 750         | High Value |
| 101         | 525         | High Value |
| 102         | 435         | Standard   |
| 103         | 150         | Standard   |
+-------------+-------------+------------+

The CTE chain reads like a recipe: total up each customer, rank them, then label the tiers. Reconstructing this logic in Polars would require three separate .group_by() and .join() steps with intermediate variable names. For analysts who think in SQL, the CTE version communicates the intent much more directly.

Real-Life Example: E-Commerce Event Analytics Pipeline

This project simulates a complete analytics pipeline for an e-commerce site. We use Polars to clean and filter raw event data, then DuckDB to run the multi-step analytics that produce the business metrics a stakeholder would actually want.

# ecommerce_analytics.py
import polars as pl
import duckdb

# ---- Step 1: Raw event data (simulate 1 day of site events) ----
raw_events = pl.DataFrame({
    "session_id": [f"s{i}" for i in range(1, 11)],
    "user_id":    [201, 202, 201, 203, 204, 202, 205, 203, 201, 204],
    "event":      ["view", "view", "add_to_cart", "view", "purchase",
                   "add_to_cart", "view", "purchase", "purchase", "view"],
    "product_id": [10, 11, 10, 12, 11, 12, 10, 12, 10, 13],
    "price":      [99.0, 149.0, 99.0, 49.0, 149.0, 49.0, 99.0, 49.0, 99.0, 79.0],
    "duration_s": [45, 12, 80, 30, 210, 95, 8, 180, 300, 20],
})

product_meta = pl.DataFrame({
    "product_id":  [10, 11, 12, 13],
    "name":        ["Widget Pro", "Gadget Max", "Mini Gizmo", "Doohickey"],
    "category":    ["Hardware", "Software", "Hardware", "Accessories"],
})

# ---- Step 2: Polars cleaning -- filter bot-like sessions (<10s) ----
clean_events = raw_events.filter(pl.col("duration_s") >= 10)
print(f"After bot filter: {clean_events.height} events (was {raw_events.height})")

# ---- Step 3: Register both DataFrames ----
con = duckdb.connect()
con.register("events", clean_events)
con.register("products", product_meta)

# ---- Step 4: Purchase funnel analysis ----
funnel = con.execute("""
    SELECT
        event,
        COUNT(DISTINCT session_id) AS sessions,
        ROUND(
            100.0 * COUNT(DISTINCT session_id) /
            MAX(COUNT(DISTINCT session_id)) OVER (), 1
        ) AS pct_of_views
    FROM events
    GROUP BY event
    ORDER BY sessions DESC
""").pl()
print("\n--- Funnel ---")
print(funnel)

# ---- Step 5: Revenue by product with conversion rate ----
revenue = con.execute("""
    WITH views AS (
        SELECT product_id, COUNT(*) AS view_count
        FROM events WHERE event = 'view'
        GROUP BY product_id
    ),
    purchases AS (
        SELECT product_id, COUNT(*) AS purchase_count, SUM(price) AS revenue
        FROM events WHERE event = 'purchase'
        GROUP BY product_id
    )
    SELECT
        p.name,
        p.category,
        COALESCE(v.view_count, 0)     AS views,
        COALESCE(pu.purchase_count, 0) AS purchases,
        COALESCE(pu.revenue, 0)        AS revenue,
        ROUND(100.0 * COALESCE(pu.purchase_count, 0) /
              NULLIF(COALESCE(v.view_count, 0), 0), 1) AS conversion_pct
    FROM products p
    LEFT JOIN views    v  ON p.product_id = v.product_id
    LEFT JOIN purchases pu ON p.product_id = pu.product_id
    ORDER BY revenue DESC
""").pl()
print("\n--- Revenue by Product ---")
print(revenue)

# ---- Step 6: Return result to Polars for export ----
revenue.write_csv("/tmp/daily_product_report.csv")
print("\nReport written to /tmp/daily_product_report.csv")

Output:

After bot filter: 9 events (was 10)

--- Funnel ---
shape: (3, 3)
+-------------+----------+--------------+
| event       | sessions | pct_of_views |
| str         | i64      | f64          |
+=============+==========+==============+
| view        | 5        | 100.0        |
| purchase    | 3        | 60.0         |
| add_to_cart | 2        | 40.0         |
+-------------+----------+--------------+

--- Revenue by Product ---
shape: (4, 6)
+------------+-------------+-------+-----------+---------+----------------+
| name       | category    | views | purchases | revenue | conversion_pct |
| str        | str         | i64   | i64       | f64     | f64            |
+============+=============+=======+===========+=========+================+
| Widget Pro | Hardware    | 3     | 2         | 198.0   | 66.7           |
| Gadget Max | Software    | 1     | 1         | 149.0   | 100.0          |
| Mini Gizmo | Hardware    | 1     | 1         | 49.0    | 100.0          |
| Doohickey  | Accessories | 1     | 0         | 0.0     | 0.0            |
+-------------+-------------+-------+-----------+---------+----------------+

Report written to /tmp/daily_product_report.csv

The pipeline uses each tool for what it is best at: Polars for fast, expressive row filtering (the bot session removal), and DuckDB for the complex multi-CTE JOIN query that would require many lines of Polars chaining. The final result is handed back to Polars for .write_csv(). You can extend this by reading from real Parquet shards on disk and adding a second daily run to compare day-over-day conversion trends.

Frequently Asked Questions

Do I still need Pandas if I use Polars and DuckDB?

For most analytical workloads, no. Polars handles data loading, filtering, and transformation faster than Pandas, and DuckDB handles complex SQL analytics. The main reason to keep Pandas is compatibility with libraries that require it — for example, some scikit-learn utilities, certain plotting libraries, or legacy code that calls .to_csv() on a Pandas DataFrame. You can convert a Polars DataFrame to Pandas on demand with df.to_pandas() when needed, so the two ecosystems coexist without conflict.

Is the zero-copy claim really true for large DataFrames?

Yes, with one caveat. When you pass a Polars DataFrame to DuckDB via duckdb.sql("... FROM df ..."), DuckDB reads the underlying Arrow buffers directly. The DataFrame’s data is not duplicated. However, DuckDB may allocate memory for intermediate results during query execution — for example, a large sort or a hash join builds internal hash tables. The input data itself is zero-copy; the computation overhead is proportional to the complexity of the query, not the input size. For a simple SELECT SUM(x) FROM df, memory usage barely moves.

Can I use DuckDB with a persistent database file instead of in-memory?

Yes. duckdb.connect("my_analytics.db") creates a persistent DuckDB database file on disk. You can use CREATE TABLE sales AS SELECT * FROM polars_df to copy a Polars DataFrame into the persistent DuckDB table, then query it across sessions without loading from Polars again. This is useful when the source data is expensive to reload but the DuckDB database fits on disk. The in-memory mode (default when you call duckdb.connect() with no arguments) is faster for single-session analytics pipelines.

Polars has its own SQL interface — why not use that instead?

Polars 0.20+ includes pl.SQLContext, which lets you run SQL directly on Polars DataFrames. For straightforward queries it works well. DuckDB has advantages in two areas: a more complete SQL dialect (full window function support, CTEs, recursive queries) and a mature query optimizer that was built specifically for analytical workloads. If you are already using DuckDB elsewhere in your stack, or if you need advanced SQL features, DuckDB is the stronger choice. For simpler queries on Polars-only pipelines, pl.SQLContext avoids adding an extra dependency.

When is Polars faster than DuckDB, and when is DuckDB faster?

For operations that map cleanly to Polars expressions — type casting, column arithmetic, simple row filters, string manipulation, pivots — Polars is typically faster because it avoids SQL parsing overhead and its Rust implementation is highly tuned for DataFrame operations. DuckDB tends to win on complex aggregations, multi-table joins, and window functions where its query optimizer can reorder operations, use predicate pushdown, and parallelize aggregation phases. For most production pipelines, the bottleneck is neither library but I/O — so reading from Parquet with either tool’s native reader and then doing computation is fast in both cases.

What is the difference between registering a DataFrame and using automatic scope lookup?

Automatic scope lookup (duckdb.sql("SELECT ... FROM df")) searches the calling Python frame for a variable named df. It is convenient for quick one-off queries. Explicit registration (con.register("my_table", df)) binds the DataFrame to a specific name on a specific connection object, which is safer in functions, threads, or Jupyter notebooks where scope can be ambiguous. For production code, explicit registration is preferred because it makes dependencies visible and avoids subtle bugs when the same variable name exists in nested scopes.

Conclusion

DuckDB and Polars together form one of the most capable in-memory analytics stacks available in Python today. Polars handles fast ingestion, cleaning, and expression-based transformations using its Rust-backed DataFrame engine. DuckDB handles SQL analytics — aggregations, window functions, multi-table joins, CTEs — without any data copying because both libraries speak Apache Arrow natively. The two tools are installed from pip, require no server, and hand data back and forth with a single method call.

The real-world e-commerce pipeline in this article is a starting point. You can extend it by reading from real Parquet shards on disk using read_parquet() inside DuckDB, by adding a persistent DuckDB database file to accumulate historical data, or by parallelizing the Polars ingestion step across multiple files using pl.scan_parquet("data/*.parquet"). The integration is straightforward enough that you can adopt it incrementally — replace one complex Polars expression with a DuckDB SQL query, verify the output matches, then keep going.

For further reading, see the DuckDB Python Polars integration guide and the Polars documentation. Both are actively maintained and updated as the libraries evolve.

Related Articles

• How To Use Ibis for Portable DataFrames Across SQL Backends
• How To Use Python Subinterpreters for True Parallelism