DuckDB vs Pandas: SQL or DataFrames in Python? (2026)

Quick verdict

Short answer

Choose DuckDB when you think in SQL, when the data lives in Parquet or CSV files, or when a dataset is too large to hold comfortably in memory: DuckDB documents out-of-core processing for grouping, joins, sorting and window functions. Choose pandas when you want a Python API for step-by-step data cleaning, time series work and integration with libraries that expect a DataFrame. You rarely have to pick one: DuckDB queries pandas DataFrames by variable name and returns results with .df().

How we know: This comparison is research-based: features, versions, licences and documented behaviour were checked against duckdb.org, pandas.pydata.org and the pandas GitHub repository in October 2026. The side-by-side groupby example was run with pandas 3.0.6 and DuckDB 1.5.6 to confirm that the code is correct and that both return the same result; we have not run performance benchmarks and make no speed claims.

DuckDB is an in-process analytical SQL database. It runs embedded within a host process with no server to install, uses a columnar, vectorised query engine and is released under the MIT License. Its Python client is installed with pip install duckdb and requires Python 3.10 or newer. The duckdb.org release calendar lists 1.5.6 as the latest stable release and 1.4.5 as the current Long-Term Support release, with 2.0.0 scheduled for 21 October 2026; if you read this after that date, check the 2.0 release notes for changes to the Python API.

pandas is a Python library that its README describes as providing "fast, flexible, and expressive data structures designed to make working with 'relational' or 'labeled' data both easy and intuitive". Its core objects are the DataFrame and Series, manipulated with Python method calls rather than SQL. pandas is licensed under BSD-3-Clause. The current release on pandas.pydata.org is 3.0.6 (17 September 2026); the 3.0 series, first released on 21 January 2026, changed several defaults, described below.

This is a paradigm comparison rather than a product shoot-out: SQL against a DataFrame API, both running locally in Python. If you are deciding between DuckDB and a database for application storage, see DuckDB vs SQLite instead.

Side by side

Aspect	DuckDB	pandas
What it is	In-process analytical SQL database engine with a Python client	Python library of DataFrame and Series data structures
How you express work	SQL (PostgreSQL-like dialect), plus a Python relational API	Python method calls: `groupby`, `merge`, `pivot_table`, indexing
Data larger than memory	Documented out-of-core processing for GROUP BY, joins, ORDER BY and window functions, spilling to a temporary directory	Designed for in-memory analytics; the pandas docs say larger-than-memory data is "somewhat tricky" and suggest chunking or other libraries
Reading files	Queries Parquet, CSV and JSON files directly in SQL, including glob patterns	`read_csv`, `read_parquet`, `read_json` and others load data into a DataFrame
Working with the other	Queries pandas DataFrames, Polars DataFrames and Arrow tables by variable name; returns results with `.df()`, `.pl()` or `.arrow()`	Receives DuckDB results as ordinary DataFrames
Persistence	Optional database file with tables, or in-memory	In memory; persist by writing files (Parquet, CSV, and so on)
Python requirement	Python 3.10 or newer	pandas 3.0 requires Python 3.11 or newer
Licence	MIT License	BSD-3-Clause
Main trade-off	SQL strings inside Python code; less natural for row-by-row or highly iterative manipulation	Memory-bound; some operations make intermediate copies, per the pandas docs

Key differences

SQL versus a DataFrame API: the same groupby both ways

The clearest way to see the difference is one aggregation written twice. Both examples below start from the same pandas DataFrame, count orders and sum revenue per region, and sort by revenue. We ran both with pandas 3.0.6 and DuckDB 1.5.6, and they return the same rows.

# Python setup (shared by both examples)
import pandas as pd
import duckdb

orders = pd.DataFrame({
    "region": ["North", "South", "North", "East", "South", "North"],
    "amount": [120.0, 80.0, 200.0, 50.0, 70.0, 30.0],
})

# pandas 3.0
summary = (
    orders.groupby("region", as_index=False)
          .agg(orders=("amount", "size"), revenue=("amount", "sum"))
          .sort_values("revenue", ascending=False)
          .reset_index(drop=True)
)

# DuckDB SQL, querying the pandas DataFrame "orders" by name
summary = duckdb.sql("""
    SELECT region,
           count(*)    AS orders,
           sum(amount) AS revenue
    FROM orders
    GROUP BY region
    ORDER BY revenue DESC
""").df()

  region  orders  revenue
0  North       3    350.0
1  South       2    150.0
2   East       1     50.0

The DuckDB version is plain SQL: anyone who has written a GROUP BY in another database can read it, and the FROM orders clause refers to the Python variable. The pandas version uses named aggregation, which keeps everything in Python and is easy to extend with further method calls. Which reads better is mostly a matter of background; in our view SQL tends to stay clearer as joins and multiple aggregations pile up, while pandas is more convenient for step-by-step reshaping. To practise the SQL side, see the SQL exercises.

DuckDB queries pandas DataFrames directly

DuckDB's Python client uses what it calls replacement scans: when a query names a table that does not exist in DuckDB, it looks for a Python variable of that name and reads the DataFrame instead, without you registering or copying it first. Results come back as a DataFrame with .df(). You can also store a DataFrame as a DuckDB table, for example CREATE TABLE my_table AS SELECT * FROM my_df, or append it with con.append().

This makes mixing the two practical: load and clean data in pandas, run the heavy join or aggregation in DuckDB SQL, and continue in pandas for plotting or modelling. The same mechanism works for Polars DataFrames and Arrow tables.

Memory: out-of-core processing versus in-memory DataFrames

The pandas user guide is direct about its design: "pandas provides data structures for in-memory analytics, which makes using pandas to analyze datasets that are larger than memory somewhat tricky", and it adds that even datasets that are a sizable fraction of memory become unwieldy because some operations make intermediate copies. Its recommendations are to load fewer columns, use efficient data types, process files in chunks, or use other libraries listed on the pandas ecosystem page. It notes that operations such as groupby are much harder to do chunk by chunk.

DuckDB documents larger-than-memory (out-of-core) processing: when memory runs short it spills intermediate data to a temporary directory, which you can move with the temp_directory setting. Grouping, joins, sorting and window functions all support this. DuckDB also lists limits: a query with several blocking operators can still run out of memory, and aggregates such as list() and string_agg() cannot offload their state to disk. Querying a Parquet file directly also means only the columns and row groups a query needs are read, rather than loading the whole file into a DataFrame first.

pandas 3.0: what changed

pandas 3.0.0 was released on 21 January 2026, and 3.0.6 is the current release. According to the 3.0 release notes, the notable changes are:

A dedicated string dtype by default. String columns are inferred as str rather than object, backed by PyArrow if it is installed and otherwise by NumPy object arrays. These columns hold only strings or missing values.
Copy-on-Write is the default. The result of any indexing operation or method behaves as if it were a copy, chained assignment no longer works, and SettingWithCopyWarning is gone.
Datetime resolution is inferred instead of always nanoseconds; for example, parsing strings gives datetime64[us].
Time zones use the standard library zoneinfo, and pytz is no longer a required dependency.
Python 3.11 or newer and NumPy 1.26.0 or newer are required; PyArrow is optional, with 13.0.0 as the minimum supported version.

The pandas project recommends upgrading to 2.3 first and fixing every warning before moving to 3.0, because 3.0 removed a lot of previously deprecated functionality. In our run the DuckDB result returned through .df() used the new str dtype for the text column, the same as the pandas result.

Apache Arrow as the common format

Arrow is the columnar in-memory format that ties these tools together. DuckDB can query Arrow tables, datasets, scanners and record batch readers as if they were tables, and its documentation says it pushes column selections and row filters down into Arrow dataset scans so that only the necessary data is pulled into memory. Results can be returned as Arrow with .arrow().

On the pandas side, PyArrow is an optional dependency that backs the new string dtype, provides pd.ArrowDtype for Arrow-backed columns, and can be selected in readers with dtype_backend="pyarrow" or engine="pyarrow". Using Arrow as the exchange format is the usual way to pass data between DuckDB, pandas and other tools such as Polars.

Pricing and licensing

DuckDB is free and open source under the MIT License. duckdb.org notes that extended support for LTS releases is available commercially from DuckDB Labs; no prices are published.

pandas is free and open source under the BSD-3-Clause licence, published by the pandas development team on GitHub.

Neither has a paid edition, so cost is not a factor in the choice. We describe licence terms only; take your own advice if licence obligations matter for software you distribute.

Pricing checked on the vendors' official pages on 7 October 2026. Prices change; confirm before buying.

Where each one leads

DuckDB strengths

Standard SQL for joins, aggregations and window functions, readable by anyone who knows SQL
Documented out-of-core processing for grouping, joins, sorting and window functions
Queries Parquet, CSV and JSON files in place, reading only the columns a query needs from Parquet
Queries pandas and Polars DataFrames and Arrow tables by variable name and returns DataFrames with .df()
Can persist tables in a database file, unlike an in-memory DataFrame

pandas strengths

Python-native API that fits step-by-step cleaning, reshaping and feature engineering
The DataFrame type that much of the Python data ecosystem accepts as input
Rich time series, indexing and reshaping functions (pivot_table, melt, resample)
pandas 3.0 brings a dedicated string dtype and Copy-on-Write by default
Mature, BSD-3-Clause licensed and widely documented

Limitations

DuckDB limitations

SQL lives in strings inside Python code, so editors and linters see less of it
Less natural than pandas for row-by-row or highly iterative manipulation
Some aggregates, such as list() and string_agg(), cannot spill to disk, and complex queries can still run out of memory
Fast release cadence, with 2.0.0 scheduled for 21 October 2026; check release notes when upgrading

pandas limitations

Built for in-memory data; the pandas docs say larger-than-memory datasets are somewhat tricky
Some operations make intermediate copies, per the pandas user guide
Grouped operations are hard to do in chunks when data does not fit in memory
pandas 3.0 removed deprecated features and changed defaults, so older code may need changes

When to choose each

Choose DuckDB if

You already know SQL and want to analyse data in Python without learning a new API
The data sits in Parquet or CSV files, possibly many of them, and you want to query them in place
A join or aggregation does not fit comfortably in memory as a DataFrame
You want to keep intermediate results as tables in a local database file

Choose pandas if

You are cleaning and reshaping data step by step and want to stay in Python
The next step is a library that expects a pandas DataFrame, such as a plotting or modelling package
You need pandas' time series and indexing features
The data fits comfortably in memory and the team already writes pandas

When neither is right

You want a DataFrame API but with lazy evaluation and a streaming engine for data larger than memory: look at Polars, an MIT-licensed DataFrame library built on Apache Arrow for Python and Rust, which DuckDB can also query.
Many users or services must share and update the data concurrently: use a database server; see DuckDB vs PostgreSQL.
Analytics must serve many concurrent users from a shared server: see ClickHouse vs PostgreSQL.
You need a local database for an application rather than for analysis: see DuckDB vs SQLite.

Final recommendation

Bottom line

DuckDB and pandas are better treated as partners than rivals. pandas remains the Python-native way to clean, reshape and hand data to the rest of the ecosystem, and pandas 3.0 modernises its defaults. DuckDB adds SQL, direct file querying and documented out-of-core processing to the same Python session, and it reads your DataFrames without a separate load step. A practical pattern is to let DuckDB do the large joins and aggregations, including over Parquet files, and bring the smaller result into pandas with .df() for the final steps.

Frequently asked questions

Can DuckDB query a pandas DataFrame directly?

Yes. DuckDB's Python client finds a DataFrame by its variable name through replacement scans, so duckdb.sql("SELECT * FROM my_df") works without registering or copying the DataFrame first. Add .df() to get the result back as a pandas DataFrame.

Is DuckDB a replacement for pandas?

For some workloads, such as large aggregations and joins or querying Parquet files, DuckDB SQL can do the job pandas would otherwise do. But pandas remains the DataFrame type much of the Python ecosystem expects, and it is more convenient for step-by-step manipulation. Most people use both.

Can pandas handle data larger than memory?

Not directly. The pandas user guide says it provides data structures for in-memory analytics and that larger-than-memory datasets are somewhat tricky. It suggests loading fewer columns, efficient dtypes, chunking or other libraries. DuckDB documents out-of-core processing for grouping, joins, sorting and window functions.

What changed in pandas 3.0?

According to the release notes, pandas 3.0 (January 2026) infers a dedicated str dtype for string columns, enables Copy-on-Write by default, infers datetime resolution instead of always using nanoseconds, uses zoneinfo for time zones and requires Python 3.11 or newer. The current release is 3.0.6.

Does DuckDB work with Polars too?

Yes. DuckDB's Python documentation says it can query Polars DataFrames and Arrow tables directly, and return results as Polars with .pl() or as Arrow with .arrow().

Sources

Checked October 2026.

How we research comparisons: our editorial method.