dbt with DuckDB: a warehouse that costs nothing

DuckDB gives you an engine. dbt gives you the discipline — models with a dependency graph, tests that run every build, documentation and lineage that stay attached to the SQL. Put them together and you have a warehouse that runs on one machine, needs no credentials, no cluster and no per-query bill, and that a single pip install gets you into.

The connector is dbt-duckdb. It lives in the DuckDB organisation on GitHub, under the Apache 2.0 licence. I built a small project with it while writing this post, so everything below is something I ran or read in the README today rather than something I remember.

What I installed

$ dbt --version
Core:
  - installed: 1.12.5
  - latest:    1.12.5 - Up to date!

Plugins:
  - duckdb: 1.11.0 - Up to date!

$ python -c "import duckdb; print(duckdb.__version__)"
1.5.5

That is what pip install dbt-duckdb resolved to on Python 3.12, today. One command pulls the adapter, a compatible dbt Core and DuckDB itself. The README's compatibility line reads: "The latest supported version targets dbt-core versions >= 1.8.x and duckdb version >= 1.0.0, but we work hard to ensure that newer versions of DuckDB will continue to work with the adapter as they are released."

Why pair them at all

Three reasons, and the first one alone is usually enough.

Development environments become free and instant. A dbt developer normally needs warehouse credentials, a personal schema and a network path to it. On DuckDB they need a file. New laptop to first dbt build is a virtualenv and a checkout.

Continuous integration needs no warehouse. A GitHub Actions runner can build your whole project against seeded Parquet fixtures with no secret in the repo and no bill attached to the pull request. That is the piece teams most often say they want dbt Cloud for, and here it costs nothing.

A lot of production work fits on one machine. If your working set is gigabytes and your freshness is measured in hours, the warehouse was never earning its keep. I have written about what DuckDB is genuinely good at and the batch-job shape is the one where it is strongest. dbt is the missing half of that shape: the part that says which model depends on which, and fails the run when a key stops being unique.

The project that works

Four files decide whether this is pleasant. Here is what I ran.

profiles.yml — the whole connection is a path to a file:

duckdb_demo:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: warehouse.duckdb
      threads: 4
      extensions:
        - parquet
      settings:
        memory_limit: "2GB"
        temp_directory: "./tmp_spill"

extensions and settings are adapter fields: the first loads DuckDB extensions, the second sets any DuckDB configuration option. Set memory_limit below whatever the container gets, always — DuckDB runs inside the dbt process, so an over-eager join takes the run down rather than one query. The self-hosting post goes into which settings actually move the needle.

sources.yml — raw Parquet files, declared as dbt sources:

version: 2

sources:
  - name: raw
    meta:
      external_location: "raw/{name}.parquet"
    tables:
      - name: orders
      - name: customers

This is the adapter's external source support and it is the feature that makes the pairing click. external_location is an f-string pattern, so one line covers every table in the source. The README explains the choice between putting it under meta and under config: settings under meta are propagated to the documentation generated by dbt docs generate, settings under config are not. A source call for the orders table then compiles to a read of 'raw/orders.parquet', as in the model below. No load step, no staging bucket, no copy.

A model is then just SQL:

select
    order_id,
    customer_id,
    ordered_at::date as ordered_on,
    amount,
    status
from {{ source('raw', 'orders') }}
where status != 'cancelled'

And the output goes back to Parquet. This is the bit I care about most, because it keeps the durable artefact durable:

{{ config(
    materialized = 'external',
    location = 'out/daily_revenue.parquet',
    format = 'parquet'
) }}

select
    ordered_on,
    count(*) as order_count,
    sum(amount) as revenue
from {{ ref('stg_orders') }}
group by 1
order by 1

The external materialization writes the model to a file instead of a table in the database. format takes parquet, csv or json, and the adapter infers it from the file extension if you leave it out. There is an options dictionary for anything else you would pass to DuckDB's COPY, including partition_by.

With two staging views, a table mart, that external model and five generic unique and not_null tests, dbt build finished like this:

Finished running 1 external model, 1 table model, 5 data tests, 2 view models
in 0 hours 0 minutes and 1.07 seconds (1.07s).

Completed successfully

Done. PASS=9 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=9

Two seconds from nothing to a tested, documented mart. That is the pitch, and it survives contact with a keyboard.

Parquet in, Parquet out

The pattern above is deliberate. My position on DuckDB has not changed: Parquet is the durable artefact and the .duckdb file is a cache you can rebuild. dbt-duckdb lets you hold that line without giving anything up — sources read Parquet, the intermediate models live in the database file where they are fast to join, and the models anything downstream depends on go back out as Parquet.

That gives you a rebuild story that is one command, and a serving story that does not fight the engine, because the files anyone else reads are not the file dbt is writing to.

One limit to know before you design around it. The README is blunt: "Unfortunately incremental materialization strategies are not yet supported for external models." Incremental works fine for ordinary table models — the adapter supports delete+insert, append, merge and microbatch, with merge needing DuckDB 1.4.0 or newer — but an incrementally-built model lives in the database file, not in a Parquet file. Making incremental models and snapshots work with external materializations is on the project's roadmap, not in it.

Running it in production

A dbt-duckdb production job is a container that starts, reads Parquet from object storage, runs dbt build, writes Parquet back and exits. Nothing stays alive between runs, retries cost nothing, and scaling means a bigger box for ten minutes.

Something has to start it. I run Dagster for my own pipelines; Airflow, or plain cron and GitHub Actions for a small project, do the same job here. The orchestration requirements are the ones in the dbt Core self-hosting list and none of them change because the adapter is DuckDB — a schedule, alerting that reaches a person, somewhere to keep secrets, and separate dev and prod targets.

Reading from S3 is a profile block rather than code. From the README:

default:
  outputs:
    dev:
      type: duckdb
      path: /tmp/dbt.duckdb
      extensions:
        - httpfs
        - parquet
      secrets:
        - type: s3
          region: my-aws-region
          key_id: "{{ env_var('S3_ACCESS_KEY_ID') }}"
          secret: "{{ env_var('S3_SECRET_ACCESS_KEY') }}"
  target: dev

In a container you usually want provider: credential_chain instead of a key pair, which tells DuckDB to use whatever AWS mechanism is already there. Secrets can also carry a scope, so one bucket uses one set of credentials and another uses a different set, with the longest matching prefix winning.

The limits, which are all one limit

DuckDB allows one writing process at a time against a database file. Everything awkward about running dbt this way comes from that, so it is worth seeing rather than reading about. I opened a second Python process holding a write connection to the same file, then ran the build:

_duckdb.IOException: IO Error: Could not set lock on file "warehouse.duckdb":
Conflicting lock is held in /usr/bin/python3.12 (PID 1644594) by user arshad.
See also https://duckdb.org/docs/stable/connect/concurrency

(That is the real error, with the directory trimmed.) It does not queue and it does not degrade — it refuses. Three consequences:

• One dbt run at a time per database file. Two schedules that can overlap will eventually overlap. That is a scheduling decision, not a configuration one.
• Nothing else holds the file open during the run. Not a BI tool, not a notebook you left connected, not a read-only process. While a writer holds the file, other processes cannot open it at all — I tried a read-only connection during the same test and it failed with the identical lock error.
• So serve something else. Point dashboards at the Parquet the run wrote, or at a copy of the file swapped in after the run finishes. Never at the file dbt writes to.

Inside one process, parallelism is fine. dbt build --threads 4 is still one process holding one lock, and it ran the same nine nodes without complaint.

The other two limits are the familiar ones and I will not repeat the detail here: memory is the failure you will actually hit, and there is no governance layer, because the file is the permission boundary. Both are covered in the honest limitations post. And a .duckdb file is not a backup — your backup is the Parquet.

Using DuckDB only as the dev and CI target

There is a narrower version of this that suits teams whose production target is Snowflake or BigQuery: keep the warehouse for production, and add DuckDB as the target developers and CI use.

It works, with a caveat I want to state plainly rather than sell past. dbt abstracts the connection, the dependency graph, the tests and the materializations. It does not translate SQL dialects. Standard SQL — joins, aggregates, window functions, CTEs — behaves the same on both. Anything vendor-specific does not: Snowflake's LATERAL FLATTEN, BigQuery's UNNEST semantics, date and regex functions with vendor-specific names, semi-structured access, and any function whose overloads differ. The more interesting your SQL, the less of it survives the swap.

Two habits make the difference. Put dialect-specific SQL behind macros with a per-adapter implementation, which is what adapter.dispatch exists for. And keep a CI job that builds against the real warehouse before merge — the DuckDB job is the fast one that catches most mistakes, not the one you trust with the release. Used that way it earns its place. Sold as "develop locally, deploy anywhere", it will embarrass you.

When you outgrow it

Two exits, both cheaper than a migration project.

MotherDuck is the same adapter with a different path. From dbt-duckdb 1.5.2 you point path at an md: connection string, as you would in the DuckDB CLI or the Python API, and your models do not change. The README flags two differences: MotherDuck preloads common extensions but does not support loading custom extensions or user-defined functions, and it is compatible with client DuckDB 0.10.2 and newer. From 1.9.6 the adapter can target a hosted DuckLake on MotherDuck too.

Switching to a warehouse means changing the adapter and the profile. Most of the project survives — the DAG, the tests, the YAML, the macros, the ref graph. What does not survive is DuckDB-specific SQL, which is exactly the mirror of the CI caveat above, and the external Parquet materializations, which have no equivalent on a warehouse. Budget for a pass over every model that does something clever. If the thing pushing you off one machine is constant sub-second dashboard load from many people at once, the comparison to make next is DuckDB against Snowflake rather than a straight port.

Two things worth knowing about the adapter

It does more than SQL. Python models run in the same process that owns the connection to the DuckDB database, so dbt.ref hands you a DuckDB relation you turn into a pandas or Polars frame or an Arrow table, and the return value is whatever DuckDB can materialise. There is an attach setting for reading and writing other databases in the same run — DuckDB files, SQLite, Postgres. And there is a plugin system with built-ins for Excel, Google Sheets, SQLAlchemy and Iceberg, plus an experimental Delta plugin.

dbt Core v2 is a live question, and the answer today is not what I expected. pip install dbt-duckdb still lands on the v1 line — dbt-core 1.12.5, above — and the adapter's own requirements pin dbt-adapters and dbt-common below 2.0. But v2 does not need the adapter package at all. dbt Core v2 ships on PyPI as dbt rather than dbt-core, and a fresh virtualenv containing only dbt==2.0.4 ran the same project for me, external Parquet model included, with DuckDB support built in. One thing had to change: v2 rejected external_location under a source's meta block with an unexpected-key error and accepted it under config. That is one project of mine, not a compatibility audit — check your own before you move. The versioning background is in the dbt Core and dbt Cloud comparison linked above, and if you are weighing frameworks rather than adapters, SQLMesh vs dbt is the other comparison worth making.

Where to start

Install the adapter, point a source at Parquet you already have, write one staging model and one test, and run dbt build. It takes about twenty minutes and it tells you more than any comparison table will. Agents are unusually good at the next part — generating the models and iterating on the errors — which I wrote up in letting Claude Code write your dbt models, and the same warning applies here: read the grain and the tests yourself.

If metrics are where this is heading, the dbt Semantic Layer is the next piece to understand.

---

The book: Local-First Analytics is the long version of the DuckDB, Parquet and Arrow side of this, with runnable code and real datasets. It is on Amazon, and you can request a review copy — I send those out by hand.