Ever opened a CSV that was just a little too big for your laptop’s memory? You try to load it in Pandas, your fans spin up, and then—silence. The kernel dies.

The usual advice: “Just move it to a database!” But that sounds like a chore. Install Postgres, manage ports, set up a username, and remember a password you’ll definitely lose. Most of us just want to analyze the data, not become a Database Administrator (DBA) for the afternoon.

So here’s where DuckDB comes in. Think of it as “SQLite for Analytics”—a database that lives inside your Python script. No server to start, no complex setup. It gives you the performance of a professional data warehouse like Snowflake, but it runs on your local machine, using the files you already have.

Getting started is straightforward. In a traditional database, you’d spend 20 minutes just getting a connection string. In DuckDB, it looks like this:

import duckdb
import pandas as pd

# This is it. No passwords, no servers, no headaches.
# It creates an in-memory database instantly.
con = duckdb.connect(database=':memory:')

print("DuckDB is ready to go!")

What this means is that you now have a high-performance engine sitting in your RAM, waiting to crunch numbers.

What is ‘Columnar Storage’ and Why Should You Care?

Traditional databases like MySQL store data in rows. Picture a library where each book holds everything about one person — name, age, address, favorite color. To find the average age across everyone, you’d pick up every book and flip through each one for a single number.

Data science rarely works that way. We usually want one or two columns spread across millions of rows, not the full record each time.

DuckDB goes the other direction with Columnar Storage. In the library analogy: one scroll for ages, another for names. Want the average age? Grab the Age Scroll and skip the rest. You read only what the query needs, so nothing goes to waste.

Let’s test that. We’ll pull a single column from a large dataset — once in Pandas, once in DuckDB.

import time
import numpy as np

# Let's create a dummy CSV with 1 million rows and 10 columns
df_size = 1_000_000
data = pd.DataFrame(np.random.rand(df_size, 10), columns=[f'col_{i}' for i in range(10)])
data.to_csv('large_data.csv', index=False)

# Timing Pandas
start = time.time()
pd_df = pd.read_csv('large_data.csv', usecols=['col_0'])
pd_time = time.time() - start

# Timing DuckDB
start = time.time()
duck_val = duckdb.query("SELECT col_0 FROM 'large_data.csv'").to_df()
duck_time = time.time() - start

print(f"Pandas took: {pd_time:.4f} seconds")
print(f"DuckDB took: {duck_time:.4f} seconds")
print(f"DuckDB was {pd_time / duck_time:.1f}x faster!")

In most runs, DuckDB comes out well ahead — it only touches the column you asked for, so your CPU skips a lot of unnecessary work. (Re-running this exact snippet three times during a real check against DuckDB 1.5.5 produced 5.4x, 2.9x, and 3.1x — the multiple varies by run and machine, but DuckDB was consistently faster.)

Querying Files Directly (The ‘Magic’ Part)

If you’re used to traditional SQL, here’s the hardest part to grasp: you don’t have to load the data.

The old workflow went: create a table, import the CSV, wait 5 minutes, query the table. With DuckDB, the file is the table — you write SQL directly against a filename string. That’s the “No-ETL” approach, and it saves hours of setup time.

# We don't 'import' the file. We just talk to it.
result = duckdb.query("""
    SELECT 
        AVG(col_0) as average_val, 
        MAX(col_1) as max_val 
    FROM 'large_data.csv' 
    WHERE col_2 > 0.5
""").to_df()

print(result)
# The output is a standard Pandas DataFrame, ready for plotting!

This works for CSVs, but Parquet files take it further. DuckDB can look at a 10GB Parquet file, read just the metadata to figure out which parts it needs, and return an answer in milliseconds. The full 10GB never touches your RAM.

The Best of Both Worlds: DuckDB + Pandas

You don’t have to pick between SQL and Pandas. Both can run together.

DuckDB has a feature called “Zero-Copy” integration. If a Pandas DataFrame already sits in memory, DuckDB can query it like a SQL table. It doesn’t copy the data—that would double your memory usage. The engine points directly at the existing Pandas memory instead.

# Let's say you have an existing Pandas DF
my_pandas_df = pd.DataFrame({'user_id': [1, 2, 3], 'spend': [10, 20, 30]})

# You can query it directly by variable name!
sql_results = duckdb.query("SELECT SUM(spend) FROM my_pandas_df").fetchone()[0]

print(f"The total spend is ${sql_results}")
# The effect is seamless: SQL power on Pandas objects.

What’s happening under the hood? DuckDB uses Apache Arrow to share data between tools without moving it. Two people reading the same map, not printing separate copies.

So, What Does This Mean for Your Workflow?

Here’s what this looks like in practice. Say you have two files—a CSV of sales and a Parquet file of customer details—and you need to join them. Pandas gets you there with .merge() syntax and some memory overhead. DuckDB makes the same join straightforward and readable.

# Creating two quick files for the demo
pd.DataFrame({'id': [1, 2], 'name': ['Alice', 'Bob']}).to_csv('users.csv', index=False)
pd.DataFrame({'user_id': [1, 2, 1], 'amount': [50, 100, 25]}).to_parquet('sales.parquet')

# The Hero Query: Joining different file formats in one line
hero_query = """
    SELECT 
        u.name, 
        SUM(s.amount) as total_spent
    FROM 'users.csv' u
    JOIN 'sales.parquet' s ON u.id = s.user_id
    GROUP BY u.name
"""

final_df = duckdb.query(hero_query).to_df()
print(final_df)

One thing worth keeping in mind: DuckDB isn’t meant to replace a production database that thousands of people use at once. It’s a tool for you, the data scientist, to make your local analysis faster and easier.

import duckdb
from fastapi import FastAPI

app = FastAPI()

# Load features once at import time
con = duckdb.connect(database=':memory:')
con.execute("CREATE TABLE features AS SELECT * FROM 'features.parquet'")

@app.get("/features/{user_id}")
def get_features(user_id: int):
    return con.execute(
        "SELECT * FROM features WHERE user_id = ?", [user_id]
    ).fetchdf().to_dict(orient='records')