How DuckDB Complements Pandas for Large-Scale Analytics

Pandas has been the standard choice for data manipulation in Python for more than a decade. If you’ve been cleaning features for a machine learning model, exploring a CSV in a notebook, or writing a lightweight ETL pipeline, it’s likely that your code has revolved around the Pandas DataFrame. It has occupied a central place in Python’s data landscape.

However, larger datasets and more compute-intensive analytical workloads have long been stretching the in-memory, single-threaded limits of Pandas. Enter DuckDB, an in-process SQL OLAP(Online Analytical Processing) engine whose “SQLite for analytics” tagline reflects its promise of:

• High-performance, scalable analytical queries
• Native Parquet and Arrow support
• Parallel execution
• Handling of datasets larger than memory

This article presents these two tools through a practitioner-focused, on-the-ground comparison. We examine how the tools differ in architecture, performance, scalability, and developer experience. We show where DuckDB can meaningfully replace Pandas and where Pandas still has the edge.

• DuckDB is a complement, not a replacement for Pandas. DuckDB was designed for large-scale analytical workloads, while Pandas is a great choice for small to medium-sized datasets, feature engineering, and general Python-native data manipulation tasks.
• Architecture fundamentally differs: Pandas is a Python library that implements an in-memory DataFrame object; DuckDB is a vectorized, SQL-first, columnar OLAP engine with support for parallel execution and querying data larger than memory.
• Performance and scalability favor DuckDB, especially on large Parquet/Arrow datasets. Query execution can be orders of magnitude faster than Pandas, thanks to projection and filter pushdown, multi-threading, and disk-backed query execution.
• A key part of this is DuckDB’s ability to integrate with hybrid workflows: DuckDB can query Pandas, Polars, and Arrow DataFrames directly, join them with large external Parquet/CSV/SQLite files, and return results in a variety of formats. This enables a seamless “SQL inside your notebook” workflow.
• Use the right tool for the job: DuckDB is ideal for OLAP-style queries, heavy joins, and large datasets; Pandas shines in interactive data cleaning, custom logic, tight integration with ML/visualization libraries, and scenarios where everything fits comfortably in RAM.

Understanding what each tool represents will better prepare you for comparing performance and workflow capabilities. Pandas is a DataFrame library that is very tightly coupled to Python. DuckDB, on the other hand, is an embedded SQL analytics engine that runs in your Python process.

Pandas as a Python DataFrame Library

Pandas is the de facto standard library for data wrangling in Python. Pandas brought the DataFrame to Python back in 2008; it’s become one of the key tools for the data science ecosystem. A Pandas DataFrame is a 2D, in-memory tabular data structure (axes labeled by rows and columns), providing fast, indexed access with a Python API. Operations on Pandas DataFrames (e.g., slicing, filtering, transformations) are backed by C/NumPy operations for vectorized execution.

Key characteristics of Pandas:

• In-memory operation: Pandas DataFrames live in RAM. This allows very fast random access to small data, but all data must fit in memory. When scaling Pandas to a few million rows, you quickly hit memory errors and significant slowdowns as the system runs out of memory.
• Rich API and ecosystem: Pandas is a rich, native Pythonic API for filtering, aggregating, merging, and handling time series, among other features. It’s very flexible – it also allows you to write your own custom Python code for complex logic in the DataFrame context. Pandas also provides great interoperability with other libraries (visualization libraries, scikit-learn, etc. ), so it’s easy to pass a DataFrame and then either visualize the data or feed it to an ML model.
• Ease of use: Most data scientists find Pandas to be quite intuitive to use. The code is written in plain Python, so it’s relatively easy to debug and iterate on. Learning how to use Pandas for basic operations has a shallow learning curve.

DuckDB as an In-Process SQL

DuckDB is sometimes described as the “SQLite for analytics”. You can query data inside your Python process, like Pandas. However, under the hood, DuckDB is very different from Pandas:

• Columnar, vectorized engine: DuckDB stores data in columnar format and executes in a vectorized model (processing data in chunks of columns (“vectors”) at a time).
• SQL-centric interface: You interact with DuckDB primarily through SQL (queries, SELECT, JOIN, GROUP BY, etc.). It’s a full-featured SQL engine with a rich dialect (similar to PostgreSQL’s SQL). Operations that require many lines of Pandas code can often be expressed as a single SQL query in DuckDB.
• Disk-based and memory-friendly: DuckDB can query data directly from disk-based columnar formats (Parquet, CSV, JSON, etc.) without having to load it into memory first. In practice, this means you can point DuckDB at a 10 GB Parquet file, and run SELECT * FROM ‘file.parquet’ LIMIT 10 almost instantly, with very little memory overhead.
• Embedded OLAP database feature: Since DuckDB is a database engine, it has all the features you would expect from a database: support for concurrent queries, transactions, advanced SQL features (window functions, CTEs, etc. ), parallel execution, etc.
• Lightweight and zero-dependency: DuckDB is a ~10MB package with zero external dependencies – no server to run – you pip install duckdb, and you’re good to go. It statically links in everything it needs (no separate database server).

Comparing DuckDB and pandas reveals several critical architectural differences:

DuckDB is designed as a columnar database, so an aggregation such as SELECT SUM(value) only reads the relevant columns instead of the entire rows. Operations are applied to large blocks of values at once using vectorized execution, which reduces per-operation overhead. Pandas, by default, processes data one row at a time, or in small slices of data. The end result is that DuckDB is often faster and more memory‑efficient for operations such as aggregation, filtering, and joins, particularly with larger datasets.

The following is a recent benchmark comparison between DuckDB, Polars, and Pandas. The hardware specification on which this example was run: MacBook Pro M2, 16 GB RAM. The artificial benchmark dataset represents 1 million persons and 10 million position records. The performance results showed that DuckDB is the fastest performing solution. On average, DuckDB completed in 3.84 seconds. Polars comes in a close second with a runtime average of 5.77 seconds. Pandas is a slower solution compared to both DuckDB and Polars, as it took an average runtime of 19.57 seconds, over five times slower than DuckDB.

This result can inform discussions about the use cases and limitations of Pandas, especially for large datasets or performance-sensitive data processing tasks. It also highlights the increasing importance of newer, more specialized data processing libraries such as DuckDB and Polars. This may be better suited to certain types of workloads and offer significant performance advantages over Pandas.

Integrating DuckDB and pandas

One of DuckDB’s biggest advantages is that it does not force you to choose between SQL and pandas. Instead, it plugs into your existing Python workflow:

• Run SQL queries directly against in-memory pandas DataFrames.
• Fetch the results back as Pandas (or Polars / Arrow) objects.
• Mix external files (CSV/Parquet/JSON) with DataFrames in the same query.
• The same style of integration is possible with Polars and Apache Arrow.

DuckDB becomes a “SQL engine inside your notebook” rather than a separate system to manage.

1. Querying pandas DataFrames with SQL (replacement scans)

With DuckDB’s replacement scan mechanism, you can treat a pandas DataFrame like a SQL table by just referencing the variable name in the query. No explicit registration required.

Example: filter and transform a pandas DataFrame with SQL

pip install duckdb
import duckdb
import pandas as pd

# A small DataFrame in pandas
df = pd.DataFrame(
    {
        "city": ["Paris", "Berlin", "Lagos", "Delhi"],
        "temp_c": [21, 19, 30, 35],
    }
)

# DuckDB sees 'df' as a table via replacement scans
result = duckdb.sql("""
    SELECT
        city,
        temp_c,
        temp_c * 9.0/5.0 + 32 AS temp_f
    FROM df
    WHERE temp_c >= 25
""").df()

print(result)

Key points:

• df should be a plain pandas DataFrame.
• duckdb.sql(…) implicitly reads df as a table called df.
• There is no copy operation; DuckDB accesses the existing DataFrame in memory.
• The result is materialized as another pandas DataFrame at the end.

You may also use an explicit connection and registration if you want more control:

con = duckdb.connect()
con.register("cities", df)

hot_cities = con.sql("""
    SELECT city, temp_c
    FROM cities
    WHERE temp_c >= 25
""").df()

2. Fetching results back as pandas / Polars / Arrow

After running a SQL query, you typically want to continue working in your favorite Python data library. For this purpose, DuckDB’s Python API exposes convenient conversion methods:

• .df() → pandas DataFrame
• .pl() → Polars DataFrame
• .arrow() → Apache Arrow Table
• .fetchnumpy() → NumPy arrays

Example: one SQL query, multiple result formats

import duckdb
import pandas as pd
import polars as pl
import pyarrow as pa

# Simple demo dataset
df = pd.DataFrame(
    {
        "product": ["A", "A", "B", "B"],
        "amount":  [100, 150,  80, 120],
    }
)

rel = duckdb.sql("""
    SELECT
        product,
        SUM(amount) AS total_amount
    FROM df
    GROUP BY product
    ORDER BY total_amount DESC
""")

# 1) Get results as pandas
pandas_result = rel.df()

# 2) Get results as Polars
polars_result = rel.pl()

# 3) Get results as an Arrow table
arrow_result = rel.arrow()

print("Pandas:\n", pandas_result)
print("Polars:\n", polars_result)
print("Arrow schema:\n", arrow_result.schema)

In a real workflow, you might:

• Use .df() if you are feeding the results into scikit-learn or other Python libraries for plotting.
• Use .pl if your downstream processing code is also written in Polars.
• Use .arrow() when you need to pass your data into systems that speak Arrow (or for zero-copy interchange).

3. Hybrid workflows: joining files and DataFrames in one query

where DuckDB excels, however, is in hybrid workflows that involve:

• large datasets in file-based storage (Parquet on disk or object storage, for example).
• smaller reference tables loaded in-memory via pandas.

You can join them in a single SQL statement, without having to write custom ETL steps.

Example: joining a large Parquet fact table with a small pandas dimension table

For example, you might have:

• data/orders.parquet – tens of millions of orders (too large to work with comfortably in pandas).
• A small pandas DataFrame with customer segments, computed from a CRM export.

import os
import duckdb
import pandas as pd
from pathlib import Path

# 1. Ensure the 'data' directory exists
data_dir = Path("data")
data_dir.mkdir(exist_ok=True)

# 2. Create a small demo "orders" dataset in pandas
orders = pd.DataFrame(
    {
        "order_id":    [101, 102, 103, 104, 105],
        "customer_id": [1,   2,   1,   3,   2],
        "order_date":  pd.to_datetime(
            ["2025-01-02", "2025-01-03", "2025-01-05", "2025-02-01", "2025-02-10"]
        ),
        "order_amount": [200.0, 150.0, 300.0, 80.0, 120.0],
    }
)

parquet_path = data_dir / "orders.parquet"

# 3. Save as Parquet (requires pyarrow or fastparquet installed)
orders.to_parquet(parquet_path, index=False)

print(f"Saved demo file at: {parquet_path.resolve()}")

# 4. Create a small customer segments DataFrame
customer_segments = pd.DataFrame(
    {
        "customer_id": [1, 2, 3],
        "segment":     ["Enterprise", "SMB", "Consumer"],
    }
)

# 5. Connect DuckDB and register the dimension table
con = duckdb.connect()
con.register("customer_segments", customer_segments)

# 6. Run the hybrid query joining Parquet + pandas
query = f"""
    SELECT
        o.customer_id,
        s.segment,
        SUM(o.order_amount) AS total_spend
    FROM '{parquet_path.as_posix()}' AS o
    JOIN customer_segments AS s
      ON o.customer_id = s.customer_id
    WHERE o.order_date >= DATE '2025-01-01'
    GROUP BY o.customer_id, s.segment
    ORDER BY total_spend DESC
"""

top_customers = con.sql(query).df()
print(top_customers)

This pattern illustrates the “best-of-both-worlds” approach:

• Pandas for ad-hoc data munging, feature engineering, or building lookup tables.
• DuckDB for efficiently scanning and aggregating large columnar files.
• All within a single Python process/notebook with no Spark, Dask, or warehouse to spin up.

4. Replacement scans with Polars and Arrow

DuckDB plays well with pandas, but that is not the end of the story. It also integrates very nicely with Polars as well as Apache Arrow. This is essential if you are using a heterogeneous stack.

There are two common scenarios:

1. Pass a Polars DataFrame or Arrow table to DuckDB as a query input.
2. Retrieve DuckDB query results as Polars or Arrow.

Example A: querying a Polars DataFrame with SQL

import duckdb
import polars as pl

# Polars DataFrame
pl_df = pl.DataFrame(
    {
        "id":    [1, 2, 3, 4],
        "value": [10, 20, 30, 40],
    }
)

# DuckDB can see 'pl_df' similarly to pandas via replacement scans
rel = duckdb.sql("""
    SELECT
        id,
        value,
        value * 2 AS value_x2
    FROM pl_df
    WHERE value >= 20
""")

# Get the result as Polars again
result_pl = rel.pl()
print(result_pl)

Example B: working with Arrow tables

import duckdb
import pyarrow as pa

# Create a small Arrow table
arrow_table = pa.table(
    {
        "event_type": ["click", "view", "click", "purchase"],
        "user_id":    [10, 10, 11, 10],
        "amount":     [None, None, None, 99.0],
    }
)

# Register the Arrow table explicitly (optional but clear)
con = duckdb.connect()
con.register("events", arrow_table)

# Run SQL on the Arrow table
agg = con.sql("""
    SELECT
        user_id,
        COUNT(*)                    AS num_events,
        SUM(CASE WHEN event_type = 'purchase' THEN amount ELSE 0 END) AS revenue
    FROM events
    GROUP BY user_id
""").arrow()

print(agg)

In both cases:

• You avoid unnecessary copies by remaining in Arrow/Polar’s memory layouts.
• DuckDB acts as a high-performance SQL layer on top of your existing DataFrame/Arrow data.
• You are free to convert between pandas, Polars, and Arrow at the edges of your pipeline as each step demands.

Should you drop Pandas entirely in favor of DuckDB? In some cases, the answer is yes, but not always. To clarify, let’s look at the types of problems DuckDB can (and should) solve, and those that are better left to Pandas:

Pandas retains an edge for flexibility and for the long tail of small or medium data workloads where its overhead is unnoticeable, and its native Python integration is a benefit. DuckDB doesn’t aim to be a drop-in replacement for that; it’s designed for cases where Pandas struggles (large data, intensive analytics, SQL users).

Despite its advantages, DuckDB is not a silver bullet. Some limitations include:

• Concurrency: DuckDB has a single‑writer model. It supports concurrent readers but only one write transaction at a time. For multi‑writer workloads or heavy OLTP, a server database such as PostgreSQL is more suitable.
• Maturity of ecosystem: Pandas has existed since 2008 and has a well‑developed ecosystem of associated visualization and machine‑learning libraries. While DuckDB’s ecosystem is rapidly developing, some higher‑level convenience functions found in pandas are still missing.
• Learning curve: The syntax of pandas may be more intuitive to analysts with no SQL background, but SQL has the benefits of long‑term stability, portability, and universal familiarity.
• No built‑in plotting: DuckDB is a query engine. To plot results, you’ll still need to use pandas, matplotlib, or other libraries.
• Experimental features: DuckDB is still actively being developed, and some functionality may be considered experimental (e.g., installing extensions, support for new file formats, etc.) and may change across versions.

DuckDB is not here to kill pandas, but rather it is here to complete it. As data workloads outgrow a single, in-memory DataFrame, DuckDB can bring a modern, high-performance engine right into your Python workflow. Read Parquet files, push filters down to storage, scale across all CPU cores, and work with datasets much larger than RAM. You can perform this without deploying a server, changing your environment, or any other hassles of distributed databases.

Pandas remains a great fit for day-to-day exploration, prototyping, or the kind of quick feature engineering that can benefit from Python’s flexibility. However, when the job requires scanning millions of rows or joining large Parquet files, or simply working with Arrow-backed datasets, DuckDB provides the speed and scale that pandas does not.

DuckDB empowers data scientists with the speed and power of a local analytical database from within a notebook, while seamlessly extending pandas, Polars, Arrow, and dbt.

In practice, the future is not “DuckDB versus pandas,” but DuckDB and pandas together. Each has a role to play in the modern analytics stack. Together, they compose a workflow that is fast, flexible, and fit for the scale of today’s data.

• Why Developers Are Talking About DuckDB
• DuckDB vs Pandas vs Polars for Python Developers
• Is DuckDB the SQLite for analytics?
• How to use DuckDB to speed up local data processing?
• DuckDB for Data Engineers: Speed Up Your Data Pipelines 10x and More