DuckDB for Database Engineers: In-Process Analytics Without the Cluster

import duckdb

con = duckdb.connect()

# Query a 10GB Parquet file directly from S3
result = con.sql(
    "SELECT date_trunc('month', event_ts) as month, event_type, "
    "count(*) as events, avg(duration_ms) as avg_duration "
    "FROM read_parquet('s3://my-bucket/events/*.parquet') "
    "WHERE event_ts >= '2024-01-01' "
    "GROUP BY 1, 2 ORDER BY 1, 3 DESC"
).fetchdf()

print(result.head(20))

-- Query multiple Parquet files with glob
SELECT * FROM read_parquet('data/events/2024-*.parquet')
WHERE user_id = 12345;

-- Query CSV with automatic type inference
SELECT AVG(revenue), MAX(order_date)
FROM read_csv_auto('orders.csv');

-- Join across different file formats
SELECT p.product_name, SUM(o.quantity) as total_sold
FROM read_parquet('products.parquet') p
JOIN read_csv_auto('orders.csv') o ON p.id = o.product_id
GROUP BY 1 ORDER BY 2 DESC;

import duckdb

con = duckdb.connect('analytics.duckdb')

# Incremental load from S3 Parquet
con.execute(
    "INSERT INTO daily_aggregates "
    "SELECT date_trunc('day', event_ts) as day, user_segment, "
    "count(*) as events, sum(revenue_cents) / 100.0 as revenue "
    "FROM read_parquet('s3://events/2025/06/24/*.parquet') "
    "GROUP BY 1, 2"
)

con.execute("CHECKPOINT")

import duckdb

con = duckdb.connect()
con.execute("INSTALL postgres; LOAD postgres;")

con.execute(
    "ATTACH 'host=prod-db.internal port=5432 dbname=appdb user=analyst' "
    "AS pg (TYPE postgres, READ_ONLY)"
)

# Vectorized analytics directly against Postgres tables
result = con.execute(
    "SELECT p.category, count(*) as orders, sum(o.total_cents)/100.0 as revenue "
    "FROM pg.orders o JOIN pg.products p ON o.product_id = p.id "
    "WHERE o.created_at >= '2025-01-01' "
    "GROUP BY 1 ORDER BY 3 DESC"
).fetchdf()