Query CSV, Parquet, and DataFrames with DuckDB in Python

python duckdb query csv parquet and dataframes requires a clear understanding of the core syntax, runtime behavior, and practical implementation patterns demonstrated in the examples below.

When you need to run SQL queries on CSV, Parquet, and DataFrames in Python, DuckDB provides a lightweight, in-process analytical engine that fits directly into your script. Instead of loading data into a separate database server, DuckDB reads the files or in-memory objects and executes vectorized queries. This article shows the core patterns for querying each source, how to combine them, and where the performance and memory tradeoffs actually matter.

The Core Query Pattern for CSV, Parquet, and DataFrames

DuckDB's Python API exposes a duckdb.sql() function that runs SQL directly against files and registered tables. The simplest way to query a CSV file is to reference it with read_csv inside the SQL string:

import duckdb

result = duckdb.sql("SELECT * FROM read_csv('sales.csv')")
print(result.df())

The same pattern works for Parquet with read_parquet:

result = duckdb.sql("SELECT * FROM read_parquet('sales.parquet')")

For a Pandas DataFrame, you can query it directly by name if you register it first, or use duckdb.from_df():

import pandas as pd

df = pd.read_csv('sales.csv')
duckdb.register('sales_df', df)
result = duckdb.sql("SELECT * FROM sales_df")

These three entry points cover the majority of ad-hoc analytical work. The rest of this article explains the details, options, and limitations you need to handle real data.

Querying CSV Files with DuckDB

read_csv accepts a file path or a list of paths. By default, DuckDB infers column names and types from the header and sample rows. You can override inference with explicit options:

result = duckdb.sql("""
    SELECT * FROM read_csv('data/*.csv',
        header = true,
        delim = ',',
        columns = {'id': 'INTEGER', 'name': 'VARCHAR', 'amount': 'DOUBLE'}
    )
""")

When dealing with messy files, specify sample_size to control how many rows are used for type detection, or set all_varchar = true to load everything as text and cast later. For large files, consider using filename to keep track of which file a row came from when reading multiple files.

DuckDB reads CSV files lazily during query execution. If you only need a subset of columns, the query planner can skip reading unnecessary columns, which reduces I/O. This behavior is automatic and often more efficient than loading the entire CSV into Pandas first.

Querying Parquet Files with DuckDB

Parquet files carry schema information, so read_parquet usually requires no column definitions. DuckDB reads the embedded metadata and can push down filters and column projections directly into the Parquet reader. This makes queries on Parquet files fast even when the files are large.

result = duckdb.sql("""
    SELECT region, SUM(amount)
    FROM read_parquet('sales/*.parquet')
    WHERE date >= DATE '2024-01-01'
    GROUP BY region
""")

DuckDB supports glob patterns for reading multiple Parquet files as a single table. The filename option is also available to identify the source file. Because Parquet is columnar, DuckDB can read only the columns referenced in the query, which is a major advantage over row-based formats.

One practical detail: DuckDB's Parquet reader handles nested structures and complex types, but you may need to cast or unnest them explicitly. For example, a struct column can be accessed with dot notation, and lists can be flattened with UNNEST.

Querying Pandas DataFrames with DuckDB

DataFrames are in-memory objects, so DuckDB does not need to read from disk. You can register a DataFrame with duckdb.register() and then query it as a table. Alternatively, duckdb.from_df() creates a relation that you can chain with other operations:

import duckdb
import pandas as pd

df = pd.DataFrame({'id': [1, 2, 3], 'value': [10, 20, 30]})
rel = duckdb.from_df(df)
result = rel.filter("value > 15").aggregate("sum(value)").execute().fetchone()

When you register a DataFrame, DuckDB does not copy the data by default. It references the same memory. If you modify the DataFrame after registration, the changes are visible to subsequent queries. This behavior is useful but also means you must be careful about mutating data while queries are running.

For very large DataFrames, consider whether you need DuckDB at all. Pandas already provides fast in-memory operations, but DuckDB's SQL engine can handle complex joins and aggregations more gracefully without manual optimization. If your DataFrame fits in memory, the overhead of copying is minimal; if it does not, you should probably use a file-based source instead.

Combining Multiple Data Sources in One Query

DuckDB allows you to join a CSV file, a Parquet file, and a DataFrame in a single SQL statement. This is where the engine shines, because you avoid loading everything into Pandas and merging manually.

import duckdb
import pandas as pd

# DataFrame with customer info
customers = pd.DataFrame({'customer_id': [1, 2, 3], 'name': ['Alice', 'Bob', 'Charlie']})
duckdb.register('customers', customers)

query = """
    SELECT c.name, SUM(o.amount) AS total
    FROM read_csv('orders.csv') AS o
    JOIN read_parquet('customers.parquet') AS p ON o.customer_id = p.customer_id
    JOIN customers AS c ON o.customer_id = c.customer_id
    GROUP BY c.name
"""
result = duckdb.sql(query).df()

DuckDB's optimizer decides how to execute the join and can push filters and projections into each source. For example, a WHERE clause on a Parquet column can be pushed down to skip row groups. This cross-source query capability reduces the need for ETL pipelines that pre-merge data into a single format.

When combining sources, be aware of type mismatches. A CSV column might be inferred as VARCHAR while the Parquet column is INTEGER. Explicitly cast in the query or use read_csv options to align types. DuckDB will not automatically coerce incompatible types in a join condition; you will get an error.

Performance and Memory Considerations

DuckDB uses vectorized execution and a columnar engine, which is well-suited for analytical queries over large datasets. The main performance advantage comes from reading only the columns you need and pushing filters down to the storage layer. For CSV files, this means DuckDB can skip parsing columns that are not referenced. For Parquet, it can skip entire row groups based on metadata.

Memory usage depends on the query. DuckDB can spill intermediate results to disk when the working set exceeds available memory, but this is not always efficient. For very large files, prefer Parquet over CSV because Parquet is compressed and columnar, reducing I/O and memory pressure. If you are querying a DataFrame that is already in memory, DuckDB will not duplicate it unless you explicitly copy it, but the query engine may create temporary results.

A common mistake is loading a CSV into Pandas and then registering the DataFrame with DuckDB, which adds an extra copy and conversion step. Instead, query the CSV directly with read_csv and let DuckDB handle parsing. This often uses less memory and is faster because DuckDB's CSV reader is highly optimized.

For production workloads, consider using DuckDB's COPY statement to convert CSV to Parquet once, then query the Parquet files repeatedly. This reduces repeated parsing overhead and improves query performance because Parquet's columnar layout and statistics enable better pruning.

Handling Large Data and Streaming Results

When a query returns a result set that is too large to fit in memory, you can fetch rows incrementally. DuckDB's fetchmany() method on a cursor allows you to process results in chunks:

conn = duckdb.connect()
cur = conn.cursor()
cur.execute("SELECT * FROM read_parquet('large.parquet')")
while True:
    chunk = cur.fetchmany(10000)
    if not chunk:
        break
    process(chunk)

For even larger datasets, consider using DuckDB's ability to write query results directly to Parquet or CSV without loading everything into Python:

duckdb.sql("COPY (SELECT * FROM read_parquet('input.parquet') WHERE amount > 100) TO 'output.parquet' (FORMAT PARQUET)")

This approach keeps the data inside DuckDB's engine and avoids transferring large result sets over the Python boundary. It is particularly useful when you need to filter, aggregate, or join before saving the output.

Compatibility and Version Notes

DuckDB's Python API is stable for the core functions described here, but some options and behaviors evolve. Always check the documentation for your installed version. For instance, read_csv options like sample_size and all_varchar have been available since early versions, but the exact spelling of certain parameters may change. Use duckdb.__version__ to verify your environment.

Pandas integration relies on the pandas package being installed. DuckDB supports both the DataFrame and Series objects, but the latter is treated as a single column. If you use a DataFrame with a non-default index, DuckDB does not preserve the index by default; you can reset it before registration or use duckdb.from_df(df, index=True) to include it as a column.

When querying files, paths are interpreted relative to the current working directory. Use absolute paths or pathlib.Path to avoid ambiguity. On Windows, be careful with backslashes in SQL strings; use forward slashes or escape them properly.

Finally, DuckDB is an embedded database, so it runs in the same process as your Python script. This means it does not require a separate server, but it also means you cannot share a connection across processes easily. For parallel workloads, you can open multiple connections to the same database file, but each connection has its own transaction context.