Querying Pandas DataFrames in DuckDB via Zero-Copy Arrow Integration

The Problem: Data Movement Overhead

When analyzing data in Python, you often need the flexibility of SQL for complex aggregations but the data manipulation capabilities of Pandas. Traditionally, moving a Pandas DataFrame into a database requires serialization or copying rows into a new storage format, which consumes double the memory and adds significant latency for large datasets.

The Solution: Zero-Copy Integration

DuckDB leverages the Apache Arrow columnar format to query Pandas DataFrames in-place. Because both DuckDB and Arrow use similar memory layouts, DuckDB can read the DataFrame's memory buffers directly. This "zero-copy" mechanism allows you to run vectorized SQL queries on your Python data without moving it across the boundary between the Python interpreter and the DuckDB engine.

Worked Implementation

To use this feature, ensure you have duckdb and pandas installed. DuckDB automatically detects DataFrames in the local Python scope when executing SQL.

import pandas as pd
import duckdb

# Create a DataFrame with Arrow-compatible types
df = pd.DataFrame({
    "id": [1, 2, 3, 4, 5],
    "value": [10.5, 20.3, 30.1, 40.8, 50.0],
    "category": ["A", "B", "A", "B", "A"],
    "date": pd.to_datetime(["2024-01-01", "2024-01-02", "2024-01-03", "2024-01-04", "2024-01-05"])
})

# DuckDB can query the 'df' variable directly from the Python namespace
# No explicit registration is required for simple SELECT statements
result = duckdb.query("SELECT category, COUNT(*) AS cnt, SUM(value) AS total FROM df GROUP BY category").to_df()

print(result)

Expected Result:

  category  cnt   total
0        A    3   81.4
1        B    2   61.1

Technical Constraints and Limits

• Type Compatibility: Only Arrow-compatible types are supported (integers, floats, strings, booleans, and datetime64). If a column contains Python objects, such as nested lists or custom classes, DuckDB will raise a TypeError.
• Read-Only Nature: The zero-copy integration is strictly read-only. If you execute a CREATE TABLE AS SELECT ... or an INSERT statement, DuckDB will materialize a copy of the data into its own internal storage format.
• Memory Residency: While zero-copy avoids duplication, the entire DataFrame must still fit within the system's available RAM. DuckDB's in-process engine shares the memory space of the Python process.
• Version Alignment: Ensure you are using Arrow 6+ and recent versions of DuckDB (0.10+) and Pandas to avoid runtime errors caused by schema mismatches.

Common Mistakes and Diagnostics

Mistake: Attempting to query nested Python objects.
If your DataFrame contains a column of lists, the query will fail. You can verify this limitation by attempting to register a "bad" DataFrame:

# This will trigger a TypeError during the Arrow conversion process
df_bad = pd.DataFrame({"tags": [["sql", "python"], ["data"], ["arrow"]]})
duckdb.query("SELECT * FROM df_bad")

Mistake: Assuming updates persist in Pandas.
Running an UPDATE statement in DuckDB on a registered DataFrame does not modify the original Pandas object. It creates a materialized version of the data within DuckDB's temporary storage.

Verification of Zero-Copy

To verify that data is not being copied, you can monitor the Resident Set Size (RSS) of the process using psutil. The memory increase during a SELECT query should be negligible, reflecting only the size of the result set rather than a duplicate of the source DataFrame.

import psutil, os
proc = psutil.Process(os.getpid())
mem_before = proc.memory_info().rss

# Run a heavy aggregation
duckdb.query("SELECT SUM(value) FROM df").to_df()

mem_after = proc.memory_info().rss
print(f"Memory delta: {mem_after - mem_before} bytes")