dbt-duckdb has become the go-to local development stack for data engineers who want to iterate fast without cloud costs. The pitch is compelling: run a full dbt project on your laptop, persist to a local file, query with DBeaver, ship to Snowflake or BigQuery when ready.
After running a 879-model dbt project (the Tuva Project) on DuckDB 1.10, here’s an honest breakdown.
The good
Speed. A full dbt run on 167k claims rows across 875 models takes about 3 minutes on a mid-range laptop. The same run on BigQuery would be faster on execution but slower on iteration due to network latency, authentication overhead, and the overhead of managing a remote warehouse.
No credentials during development. No cloud IAM, no service accounts, no gcloud auth. Open the .duckdb file and go. This removes an entire category of friction during early project setup.
The DuckDB SQL dialect is good. It supports window functions, lateral joins, QUALIFY, PIVOT, UNPIVOT, list aggregations, structs, and JSON functions. Most dbt models written for Snowflake or BigQuery work on DuckDB without modification.
dbt-duckdb is a first-class adapter. It’s actively maintained and supports custom materializations, external tables (reading Parquet directly from S3 with read_parquet()), and incremental models with merge strategies.
DuckDB reads Parquet from S3 natively. Load raw claims from S3 without a separate ingestion step:
CREATE TABLE raw_claims AS
SELECT * FROM read_parquet('s3://bucket/claims/*.parquet');The bad
Single-writer constraint. Only one process can hold a write connection to a .duckdb file at a time. DBeaver open while running dbt run breaks. Streamlit open while running dbt run breaks. You manage this manually by putting analysis tools into read-only mode.
Package compatibility issues. Complex packages like Tuva 0.17.2 use window function patterns that trigger assertion failures in DuckDB 1.10. These are DuckDB version-specific bugs — two models fail with internal assertion errors on column binding in complex window CTEs. You absorb these upstream failures.
dbt show triggers seed reloads. Calling dbt show --select some_model can trigger seed reloads from S3 depending on how the project is configured. On a large seed set with remote storage, this adds 4-5 minutes of unexpected latency when you just want to preview a model.
No partial file durability. If DuckDB crashes mid-write (rare, but happens on machine sleep/wake), WAL recovery sometimes fails. A corrupted .duckdb file means re-running from dbt seed. For local dev this is annoying; for anything production-adjacent, it’s disqualifying.
The workarounds
DBeaver lock conflict → Set read_only = true in DBeaver’s DuckDB connection driver properties.
Streamlit reading from the same file → Open with duckdb.connect("path.duckdb", read_only=True).
Missing macro errors across packages → Add a local macros/limit_zero.sql defining both default__ and duckdb__ variants.
DuckDB assertion failures on complex models → Exclude the affected models: dbt run --exclude model_name. File an issue with the upstream package.
Slow seed reloads on dbt show → Use dbt compile instead of dbt show to inspect compiled SQL. Or run dbt show against pre-built artifacts.
When to use it
dbt + DuckDB is the right choice for:
- Local development and testing before deploying to a cloud warehouse
- Small-to-medium datasets where DuckDB can hold everything in a modest file
- Projects where zero cloud costs during iteration matter
- Single-engineer or small teams where the single-writer constraint is manageable
It’s the wrong choice for:
- Production pipelines with concurrent readers and writers
- Datasets large enough that DuckDB’s memory model becomes a bottleneck
- Teams where multiple engineers run dbt against the same state simultaneously
The mental model that works: treat the .duckdb file like a build artifact. dbt writes it, analysis tools read it. Don’t try to write from two places at once.