duckdb

DuckDB - The SQL Engine for Scientific Data

DuckDB brings the power of professional SQL to the Python data science stack. It is optimized for "Online Analytical Processing" (OLAP), meaning it excels at large-scale aggregations, joins, and complex queries on datasets that are larger than memory.

When to Use

• Performing complex SQL queries (JOINs, Window functions) on Pandas or Polars data.
• Querying large Parquet or CSV files directly without loading them into memory.
• Efficiently joining data from different sources (e.g., a CSV file and a Pandas DataFrame).
• Building analytical pipelines where SQL is more concise or faster than DataFrame code.
• Managing local datasets that are too big for Excel but don't need a full PostgreSQL server.
• Intermediate data storage and feature engineering for Machine Learning.

Reference Documentation

Official docs: https://duckdb.org/docs/
Python API: https://duckdb.org/docs/api/python/overview
Search patterns: duckdb.sql, duckdb.query, duckdb.read_parquet, duckdb.from_df

Core Principles

In-Process Execution

DuckDB runs inside your Python process. There is no server to start or manage. The data can be stored in a file (.db) or kept entirely in memory.

Columnar Engine

Like Polars, DuckDB uses a columnar storage and vectorized execution engine, making it orders of magnitude faster than row-based databases (like SQLite) for analytical tasks.

Seamless Interoperability

DuckDB can "see" your Python variables. You can run a SQL query directly against a Pandas DataFrame variable as if it were a table in the database.

Quick Reference

Installation

pip install duckdb

Standard Imports

import duckdb
import pandas as pd
import numpy as np

Basic Pattern - Querying Python Data

import duckdb
import pandas as pd

# 1. Create a sample DataFrame
df = pd.DataFrame({"id": [1, 2, 3], "val": [10.5, 20.0, 15.2]})

# 2. Query the DataFrame directly via SQL
# DuckDB automatically finds the 'df' variable in the local scope
result_df = duckdb.sql("SELECT id, val * 2 AS doubled FROM df WHERE val > 12").df()

print(result_df)