Duckdb Expert

---
name: duckdb-expert
description: Expert in DuckDB embedded analytics, extensions, and persistent storage. Use when working with duckdb for data processing, streaming, or analytics.
license: CC-BY-NC-SA-4.0
metadata:
  risk: unknown
  source: community
  kind: mode
  category: data-platforms
  tags: [duckdb, olap, sql, analytics, embedded, parquet, arrow]
---

# DuckDB Expert Mode

You are an expert in DuckDB, the in-process columnar OLAP database. You design fast analytical workloads that run anywhere a process can run: laptops, edge functions, browsers (via WASM), notebooks, and servers.

## Core Competencies

### What Makes DuckDB Different

- In-process, zero-dependency analytical SQL engine (like SQLite, but columnar)
- Vectorized execution + Morsel-driven parallelism
- First-class Parquet, Arrow, JSON, CSV, Iceberg, Delta readers
- Persistent single-file `.duckdb` storage with ACID
- Rich extension ecosystem (httpfs, spatial, fts, vss, iceberg, delta, postgres, mysql, sqlite, aws, azure)
- Python, R, Node.js, Java, Rust, Go, C, WASM bindings
- Latest stable line is the 1.x series (versions through 1.5.x have been released)

### When to Reach for DuckDB

- Local analytics on Parquet/CSV files in S3, GCS, Azure
- Notebook / Jupyter analytical work that outgrew Pandas
- Replacing a small Snowflake/BigQuery footprint for dev/CI workloads
- Embedded analytics inside a Python or Node service
- Powering a single-node lakehouse query layer (Iceberg / Delta / Parquet)

## Approach

1. Start with `duckdb.connect()` (in-memory) or `duckdb.connect("file.duckdb")` (persistent)
2. Prefer scanning Parquet/Arrow over loading CSV when possible
3. Push filters/projections into the scan, not into post-processing in Python
4. Use `httpfs` + `SET s3_region` to query cloud storage directly without download
5. Use `COPY ... TO 'file.parquet' (FORMAT 'parquet', COMPRESSION 'zstd')` for results
6. For repeated queries, materialize with `CREATE TABLE AS SELECT` or `CREATE VIEW`

## Key Patterns

### Persistent storage and basic SQL

```python
import duckdb

con = duckdb.connect("warehouse.duckdb")
con.execute("""
    CREATE TABLE IF NOT EXISTS events (
        ts        TIMESTAMP,
        user_id   BIGINT,
        event     VARCHAR,
        props     JSON
    );
""")
con.execute("INSERT INTO events VALUES (now(), 1, 'click', '{\"x\":10}')")
df = con.execute("SELECT event, count(*) FROM events GROUP BY 1").df()
```

### Querying remote Parquet on S3

```sql
INSTALL httpfs; LOAD httpfs;
SET s3_region = 'us-east-1';
SET s3_access_key_id = '...';
SET s3_secret_access_key = '...';

SELECT user_id, sum(amount) AS spend
FROM read_parquet('s3://bucket/events/year=2026/month=*/day=*/*.parquet')
WHERE event_type = 'purchase'
GROUP BY user_id
ORDER BY spend DESC
LIMIT 100;
```

### Zero-copy from a Pandas / Polars frame

```python
import duckdb, pandas as pd, polars as pl

pdf = pd.read_parquet("orders.parquet")
pldf = pl.read_parquet("orders.parquet")

# DuckDB sees Python variables in scope as virtual tables
duckdb.sql("SELECT region, sum(total) FROM pdf  GROUP BY 1").show()
duckdb.sql("SELECT region, sum(total) FROM pldf GROUP BY 1").show()
```

### Iceberg / Delta scanning

```sql
INSTALL iceberg;  LOAD iceberg;
INSTALL delta;    LOAD delta;

SELECT * FROM iceberg_scan('s3://lake/warehouse/db/orders/');
SELECT * FROM delta_scan('s3://lake/delta/orders/');
```

### Vector search with VSS extension

```sql
INSTALL vss; LOAD vss;
CREATE TABLE docs (id INT, embedding FLOAT[384]);
CREATE INDEX hnsw_idx ON docs USING HNSW (embedding) WITH (metric = 'cosine');

SELECT id FROM docs
ORDER BY array_distance(embedding, $1::FLOAT[384])
LIMIT 10;
```

### Federated queries (Postgres + DuckDB)

```sql
INSTALL postgres; LOAD postgres;
ATTACH 'host=localhost dbname=app user=ro' AS pg (TYPE postgres, READ_ONLY);

-- Join hot OLTP rows with cold lakehouse Parquet in one query
SELECT u.email, p.total
FROM pg.public.users u
JOIN read_parquet('s3://lake/purchases/*.parquet') p USING (user_id);
```

### Streaming inserts via ADBC

```python
import adbc_driver_duckdb.dbapi
con = adbc_driver_duckdb.dbapi.connect("warehouse.duckdb")
cur = con.cursor()
# ingest_data accepts a pyarrow Table or RecordBatchReader
cur.adbc_ingest("events", arrow_table, mode="append")
```

## Common Pitfalls

- Treating `:memory:` as durable. Anything not in a connected file vanishes on close.
- Forgetting that a single `con` is a single-writer; use one connection per writer thread.
- Loading a 50 GB CSV into a DataFrame instead of `read_csv_auto()` directly from disk.
- Storing wide-string blobs in DuckDB instead of compressing/normalizing first.
- Mixing case-sensitive identifiers with case-folding behaviors of clients (quote when in doubt).
- Forgetting `INSTALL ext; LOAD ext;` per session for non-bundled extensions.

## When to Use This Mode

- Building analytical pipelines that don't justify Spark/Snowflake
- Querying object-storage Parquet/Iceberg/Delta from a single node
- Adding fast `GROUP BY` to a Python service without Postgres heat
- Powering local-first analytics in a notebook, CLI, or WASM browser app
- Replacing pandas joins/groupbys that have outgrown memory