Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 38 ยท Python and Embedded Use

DataFrames, Arrow and Zero-Copy Scans

Query pandas, Polars and Arrow data directly with SQL in DuckDB, write results back to DataFrames, and understand replacement scans and zero-copy reads.

One of the nicest tricks in DuckDB is that it can run SQL directly on a Python variable. If a pandas DataFrame named sales exists in your scope, SELECT * FROM sales just works. There is no import step, no to_sql, and, for most column types, no copy of the data.

As always on this site: the real DuckDB cannot run in your browser, so the duckdb code below is read-only. The runnable examples use pandas plus the built-in sqlite3 as a clearly labelled stand-in for the same data flow.

Replacement scans

When DuckDB meets a table name it cannot find in the catalog, it looks in the Python scope of the caller for a variable with that name. This is called a replacement scan.

Python
import duckdb
import pandas as pd

sales = pd.DataFrame({
    "region": ["north", "south", "north", "east"],
    "amount": [120, 80, 45, 200],
})

duckdb.sql("""
    SELECT region, sum(amount) AS total
    FROM sales
    GROUP BY region
    ORDER BY total DESC
""").show()

The same works for Polars DataFrames, Arrow tables, Arrow datasets, NumPy dictionaries and LazyFrame-like objects exposing the Arrow interface. DuckDB reads the DataFrame's memory in place. For numeric columns this is zero-copy: DuckDB's vectors point at the pandas/Arrow buffers.

Two cautions:

  • The lookup uses the variable name at the moment of the query. Renaming the variable changes the SQL's meaning.
  • Inside functions, the scan sees local variables of the calling frame. It is convenient but implicit, so for production code prefer explicit registration.

Explicit registration

Python
con = duckdb.connect()
con.register("sales_view", sales)     # a virtual table backed by the DataFrame
con.sql("SELECT count(*) FROM sales_view").show()
con.unregister("sales_view")

register creates a view, not a copy. Updating the DataFrame afterwards changes what later queries see. To take a snapshot instead, materialize it:

Python
con.execute("CREATE TABLE sales_copy AS SELECT * FROM sales")

or insert into an existing table with INSERT INTO t SELECT * FROM sales.

Getting data back out

Python
df    = con.sql("SELECT region, sum(amount) AS total FROM sales GROUP BY 1").df()
arrow = con.sql("SELECT * FROM sales").arrow()
pl_df = con.sql("SELECT * FROM sales").pl()

.df() gives pandas, .arrow() an Arrow table, .pl() Polars. For result sets bigger than memory, fetch_record_batch(100_000) returns a reader you iterate batch by batch.

Types map sensibly: BIGINT to int64, DOUBLE to float64, TIMESTAMP to datetime64[ns] (or Arrow timestamps), VARCHAR to object or Arrow string, LIST to Python lists or Arrow lists, STRUCT to dicts. DECIMAL becomes Python Decimal in Arrow, which is exact but slower than floats.

When to use SQL on a DataFrame

SQL shines where pandas becomes awkward or slow:

  • joins between several DataFrames with complex conditions;
  • window functions and GROUP BY over many millions of rows;
  • anything larger than RAM when reading Parquet or CSV instead of loading everything first;
  • mixing sources: SELECT ... FROM sales JOIN 'prices.parquet' USING (sku).

Pandas stays handy for plotting and tiny interactive tweaks. Many teams use both: SQL for heavy lifting, then .df() for the last mile.

flowchart LR
    A["pandas / Polars / Arrow"] -->|"replacement scan"| B["DuckDB engine"]
    C["Parquet / CSV files"] --> B
    B -->|".df() .arrow() .pl()"| D["Results"]

DuckDB can read DataFrames and files in the same query.

Runnable stand-in: SQL over a DataFrame

sqlite3 cannot see Python variables, so the stand-in pushes the DataFrame into an in-memory database first. DuckDB skips that step; everything else about the thinking is the same.

Python Stand-in: aggregate a DataFrame with SQL (pandas + sqlite3)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Joining two DataFrames is the other everyday case:

Python Stand-in: join two DataFrames in SQL

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In DuckDB the same query is duckdb.sql(q).df() with no to_sql calls.

Writing a DataFrame into a table

Python
con.execute("CREATE TABLE sales AS SELECT * FROM sales_df")
con.execute("INSERT INTO sales SELECT * FROM more_sales_df")

Both read the DataFrame in bulk, which is much faster than looping with INSERT ... VALUES. After that you can export in any format: COPY sales TO 'sales.parquet' (FORMAT parquet).

Pitfalls

  • Index columns: a pandas index is not a column. Call reset_index() if you need it in SQL.
  • Object columns that mix types fall back to VARCHAR; clean them in pandas or cast in SQL.
  • Name clashes: if both a table and a variable are named sales, the table in the catalog wins.
  • Lifetime: a registered DataFrame must stay alive while queries use it.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is a replacement scan?

    If a table is not in the catalog, DuckDB checks the caller's Python scope for a matching variable.
  2. 2. What does con.register('v', df) create?

    register makes a view. Use CREATE TABLE AS SELECT to snapshot data.
  3. 3. Which call returns a Polars DataFrame?

    .pl() converts a relation to Polars, .df() to pandas, .arrow() to Arrow.
  4. 4. Why is INSERT from a DataFrame preferred over looping INSERT ... VALUES?

    DuckDB is a columnar engine; bulk inserts are orders of magnitude faster than single-row ones.

Summary

  • DuckDB queries pandas, Polars and Arrow objects by variable name via replacement scans, mostly zero-copy.
  • register makes a live view; CREATE TABLE AS makes a snapshot.
  • .df(), .arrow() and .pl() bring results back; fetch_record_batch streams.
  • Use SQL for joins, windows and big aggregations, pandas for the last mile.

Next: Method Chaining with the Relational API