DuckDB

A SQL engine with no server, no install drama — that queries your files directly. This one earns its keep daily.

▶ Watch this reel

What you'll learn

What is DuckDB
Query files directly
Joins across files
Python integration
DuckDB vs SQLite vs SQL Server
Persisting & exporting

Remember this

DuckDB = embedded columnar OLAP: pip install, no server, Postgres-flavored SQL
Files are tables: query CSV/Parquet/partitioned lakes directly; join across formats; pandas DataFrames register as views
duckdb.sql() with bound params; COPY … TO Parquet closes the loop; vs SQLite (OLTP) vs SQL Server (system of record)

What it is

Embedded, in-process, columnar OLAP engine. pip install duckdb — no server.
Postgres-flavored SQL; vectorized + parallel; works beyond RAM.

Query files directly

FROM 'data.csv' / 'lake/*/.parquet' — files are tables.
Partitioned folders pruned automatically; globs supported.

Joins across files

csv ⨝ parquet ⨝ JSON in one query.
duckdb.register('name', df) — live pandas DataFrame as a SQL view.
Results: .df() / .arrow() / .fetchall().

Python integration

duckdb.sql("...", params=[...]) — lazy relation, chainable.
Extensions: httpfs (S3/HTTP), spatial, FTS.

DuckDB vs SQLite vs SQL Server

Engine	Job
SQLite	embedded OLTP (app storage, transactions)
DuckDB	embedded OLAP (analytics over files)
SQL Server	enterprise system of record

Persisting & exporting

In-memory by default; duckdb.connect('file.db') persists.
COPY (SELECT …) TO 'out.parquet' (FORMAT PARQUET, PARTITION_BY (col)).
Compose with views; materialize only what's expensive.

Code: DuckDB — the daily workflow in one script

import duckdb

# --- explore files directly -------------------------------------
rel = duckdb.sql("""
    SELECT status, COUNT(*) AS n, SUM(amount) AS total
    FROM 'data/events.csv'
    GROUP BY status
    ORDER BY total DESC
""")
print(rel)                     # lazy relation — pretty-prints a sample

# --- join CSV to a partitioned Parquet lake ----------------------
duckdb.sql("""
    CREATE VIEW events AS
    SELECT e.*, c.team
    FROM 'data/events.csv' e
    JOIN 'lake/customers/year=*/month=03/*.parquet' c USING (cust_id)
""")

# --- register a live pandas DataFrame ----------------------------
import pandas as pd
flags = pd.DataFrame({"status": ["P1", "P2"], "sla_h": [1, 24]})
duckdb.register("flags", flags)

result = duckdb.sql("""
    SELECT e.team, f.sla_h, AVG(e.amount) AS avg_amt
    FROM events e
    JOIN flags f USING (status)
    GROUP BY ALL
    ORDER BY avg_amt DESC
""").df()

# --- persist the pipeline -----------------------------------------
duckdb.sql("""
    COPY (SELECT * FROM events WHERE status = 'P1')
    TO 'out/p1_cases.parquet' (FORMAT PARQUET, COMPRESSION 'snappy')
""")

# Parameterized (the only safe way to mix variables + SQL):
city = "Paris"
safe = duckdb.sql("SELECT * FROM events WHERE city = ?", params=[city])