Query CSV, Parquet, and DataFrames with DuckDB in Python

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, a glob pattern, 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 through a table function that runs during query execution. If a query references only some columns, the CSV reader can avoid building the unused columns. Because CSV is a row-based text format, the file still has to be scanned, so this reduces parsing and memory overhead rather than reducing bytes read from disk. That is often more efficient than loading the whole 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 can query it without requiring a separate copy into DuckDB storage. For predictable results, treat the DataFrame as read-only while it is registered; mutating it during query execution can lead to surprising behavior.

For very large DataFrames, consider whether you need DuckDB at all. Pandas already provides fast in-memory operations, but DuckDB can express complex joins and aggregations in SQL without manual optimization. If your DataFrame fits in memory, the conversion overhead is usually small; if it does not, consider using 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. Use explicit casts or read_csv options to align types; DuckDB will not guess at incompatible types in a join condition.

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 pushing filters down and, for columnar formats, reading only the columns you need. For CSV files, DuckDB can avoid building unneeded columns. 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 can often use it without creating a full duplicate, but the query engine may still create temporary results for intermediate steps.

A common mistake is loading a CSV into Pandas and then registering the DataFrame with DuckDB, which adds an extra conversion step. Instead, query the CSV directly with read_csv and let DuckDB handle parsing. That avoids materializing the data in Pandas and uses DuckDB's native CSV reader.

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. Check the documentation for your installed version. For instance, read_csv options such as sample_size and all_varchar have been available since early versions, but exact parameter names can change. Use duckdb.__version__ to verify your environment.

Pandas integration relies on the pandas package being installed. DuckDB supports both DataFrame and Series objects, treating a Series as a single-column table. A DataFrame's non-default index is not automatically exposed as a column, so reset the index before registration if you need it as data.

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

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 an in-memory database across processes easily. For file-backed databases, you can open multiple connections, but each connection has its own transaction context.