What is DuckDB?

DuckDB changed how I prototype. The old workflow: spin up a Snowflake warehouse, load a Parquet file into a staging table, run exploratory SQL, drop the table. Five minutes of setup for two minutes of actual analysis. With DuckDB: pip install duckdb, point it at the Parquet file, run SQL. Zero infrastructure, zero warehouse credits, usually faster because there's no network round-trip.

Where it fits in my stack

DuckDB has taken over three jobs for me: (1) local data exploration — any time I'm poking at a dataset under 50GB, DuckDB is faster than uploading to Snowflake; (2) unit-test data transforms — dbt tests, ETL correctness checks, and CI data validation can run in DuckDB for essentially zero cost instead of burning warehouse credits on test data; (3) embedded analytics in Python/Node apps — when the data naturally lives with the application, DuckDB is simpler than a database service.

What it hasn't replaced: the production warehouse. Multi-user concurrency, governance, cross-team data sharing, and multi-TB workloads still belong in Snowflake or BigQuery. DuckDB is a complement, not a replacement.

A quick field story

On one project I inherited a 90-minute nightly Snowflake job that did complex JSON parsing over ~8GB of raw event data. Cost: ~$400/month in credits. I rewrote it as a DuckDB script running on a small container against the same S3 Parquet. Result: 4 minutes of compute, ~$5/month in container costs, identical output. The job wasn't big enough to justify Snowflake's overhead; DuckDB was the right-sized tool. We still use Snowflake for the downstream aggregations that touch cross-team data — but for that one extraction step, DuckDB was dramatically better.

DuckDB is an open-source, in-process SQL OLAP database management system. While SQLite is the embedded database for transactional (OLTP) workloads, DuckDB is designed for analytical (OLAP) workloads — running complex aggregations, joins, and window functions on large datasets without needing a server.

Why DuckDB is Revolutionary

The Problem It Solves

• Setting up a cloud warehouse (Snowflake, BigQuery)
• Loading data into a database
• Paying for compute time
• Waiting for query results over the network

import duckdb

# Query a Parquet file directly — no loading, no server
result = duckdb.sql("""
    SELECT department, AVG(salary) as avg_salary
    FROM 'employees.parquet'
    WHERE hire_date > '2024-01-01'
    GROUP BY department
    ORDER BY avg_salary DESC
""").fetchall()

Key Features

Zero Infrastructure

• No server process to manage
• Runs inside Python, R, Node.js, Java, or any application
• pip install duckdb and you're done

Direct File Querying

• Query Parquet, CSV, JSON files directly
• Read from S3, GCS, Azure Blob without downloading
• Join across multiple file formats in a single query

Columnar Execution

• Vectorized query engine processes data in batches
• Automatic parallelism across CPU cores
• Handles datasets much larger than RAM

Full SQL Support

• Window functions, CTEs, subqueries
• ASOF joins for time-series
• Advanced aggregations (GROUPING SETS, CUBE, ROLLUP)

DuckDB Use Cases

1. Data Exploration: Quickly explore datasets without loading into a warehouse
2. CI/CD Data Testing: Run data quality checks in your pipeline
3. Local Development: Prototype queries before deploying to production warehouse
4. Embedded Analytics: Add SQL analytics to any application
5. File Format Conversion: Transform between Parquet, CSV, JSON efficiently

When NOT to Use DuckDB

• Concurrent Users: DuckDB is single-process; use a real warehouse for multi-user workloads
• Transactional Workloads: Use PostgreSQL or SQLite for OLTP
• Massive Scale: 100GB+ datasets are better served by distributed systems

Real-world DuckDB lessons:

• It's single-process. Concurrent writes from multiple processes aren't supported; DuckDB is for one writer (many readers are OK via read-only mode). If you need multi-user concurrent writes, use a real database.
• Memory management is manual-ish. DuckDB spills to disk when memory gets tight, but you'll want to tune memory_limit and temp_directory explicitly for large workloads. The default config targets small workloads.
• S3 reads can be slow without tuning. Default HTTP settings aren't optimal for large Parquet reads from cloud storage. Set s3_region, use credential chain, and consider httpfs settings (connection pool size, range request granularity) for production workloads.
• Not every SQL dialect feature is there. DuckDB has excellent SQL support, but some Snowflake/BigQuery-specific functions (VARIANT/OBJECT handling, specific date functions, geospatial) need equivalents. dbt-duckdb helps bridge some of this but expect occasional translation.
• MotherDuck is not DuckDB. MotherDuck is a managed cloud service that runs DuckDB plus a hybrid local/cloud execution layer. They're the same SQL dialect but different deployment models and different billing. Don't assume MotherDuck pricing applies to open-source DuckDB or vice versa.
• Writing to Iceberg / Delta is still evolving. DuckDB reads Iceberg and Delta tables well; writing is either limited or via extensions that don't yet match write features of Spark / Snowflake. Check current capability for your use case.