DuckDB — SQL on Files, Without a Database Server

SQLite for analytics

DuckDB is an in-process OLAP SQL database. The pitch is simple: you pip install duckdb, you write SQL, and you can query Parquet files, Arrow tables, Pandas DataFrames, Polars DataFrames, CSVs — directly, without copying them or loading them into a server. Current stable: 1.5.2.

If SQLite is the in-process database for transactional row-oriented workloads, DuckDB is the in-process database for analytical column-oriented workloads. Same idea — no server, runs in your Python process — different optimization target.

What it's good at

Querying Parquet files with column pruning and predicate pushdown automatically.
Joining several Parquet files together as if they were tables.
Querying a Pandas/Polars DataFrame with SQL when SQL is the more natural way to express the transformation.
Acting as an analytical "warehouse" for medium-sized data (hundreds of GB) where spinning up Snowflake would be overkill.

What it's not

DuckDB is not for high-concurrency OLTP (use Postgres). It's not distributed (use BigQuery/Snowflake/Spark for petabytes across many nodes). And it's optimized for read-heavy analytics, so you won't write rows one at a time — you'll insert in batches.

Code

Three patterns: query a file, query a DataFrame, persist to a database file·python

import duckdb
import pandas as pd

# 1. Query Parquet directly — no DataFrame in memory
result = duckdb.sql('''
    SELECT date_trunc('month', order_date) AS month,
           SUM(amount_usd) AS revenue
    FROM 'warehouse/orders/year=2026/month=*/*.parquet'
    GROUP BY 1 ORDER BY 1
''').to_df()

# 2. Query a Pandas DataFrame as if it were a table
df = pd.read_parquet('customers.parquet')
by_country = duckdb.sql('''
    SELECT country, COUNT(*) AS n FROM df GROUP BY 1 ORDER BY n DESC
''').to_df()

# 3. Persist to a DuckDB file — like a SQLite for analytics
con = duckdb.connect('analytics.duckdb')
con.sql('''
    CREATE OR REPLACE TABLE monthly_revenue AS
    SELECT date_trunc('month', order_date) AS month,
           SUM(amount_usd) AS revenue
    FROM 'warehouse/orders/year=2026/month=*/*.parquet'
    GROUP BY 1
''')
con.close()

DuckDB — SQL on Files, Without a Database Server

SQLite for analytics

What it's good at

What it's not

Code

External links

Exercise

Progress

Comments 0