Course contents

The Analytics Engineer’s Field Guidedbt 3: ship it like a teamStep 15: DuckDB locally

DuckDB locally

Part 2 · dbt · step 15

DuckDB: iterate on your laptop, for free

Same project, different engine. Check the logic locally in seconds, keep Snowflake for what only Snowflake can answer.

· · ·

Watch Alice iterate. Change a join, dbt run, wait for the warehouse, read the result, fix a filter, run again. Every loop costs credits and a minute of waiting, and nine times out of ten she's checking something that has nothing to do with Snowflake: does the join keep the right rows, is the grain (step 3: what one row means) still one order, does the case statement handle nulls.

DuckDB is a database that runs inside the program using it, here dbt itself. No server, no account, no credentials. The entire warehouse is one file sitting next to your project, and it's fast on millions of rows.

The trick: one extra target

You don't copy the project. You run the same project, same models, same macros, same tests, pointed at a different engine. An adapter is dbt's plugin for one database. You have dbt-snowflake; here you need dbt-duckdb, which brings DuckDB along. It's already in requirements-dev.txt (step 11). Then add a second personal target:

# ~/.dbt/profiles.yml, Alice's two personal targets
my_project:
  target: userland
  outputs:
    userland:                       # Snowflake sandbox, unchanged from step 14
      # ...
    local:                          # DuckDB on the laptop
      type: duckdb
      path: analytics.duckdb       # one file, created where you run dbt
      threads: 4

No login, just a file. Run dbt from the project root, and add *.duckdb to .gitignore. With no database: or schema: key, dbt-duckdb names the database after the file (analytics) and uses the schema main. Your macros from steps 5 and 14 then route every model to analytics.main:

# the whole workflow change is one flag
dbt run --target local     # seconds, zero credits
dbt test --target local    # data tests + unit tests, same definitions
· · ·

Feeding it: where the local data comes from

DuckDB starts empty, and your sources need tables to read: one CSV per source table. Two kinds, in two folders:

  • Hand-made rows, in sample_data/, committed. Type a dozen rows per file, or generate them with a Python library like Faker. Include a null, a refund, a duplicate. CI uses the same files.
  • A real extract, in local_data/, never committed (add it to .gitignore). Run a small SELECT in a Snowsight worksheet and use Download results. Real shapes, real oddities. Leave personal columns out and follow your company's data rules.

A short script loads either folder. The duckdb Python package came with dbt-duckdb:

# load_local.py, at the project root: each CSV becomes a table
import sys
import duckdb

folder = sys.argv[1] if len(sys.argv) > 1 else "sample_data"
con = duckdb.connect("analytics.duckdb")
con.execute("create schema if not exists public")   # same name as RAW_DB.PUBLIC
for table in ["orders", "customers"]:
    con.execute(
        f"create or replace table public.{table} as "
        f"select * from read_csv('{folder}/{table}.csv')"
    )
con.close()

python load_local.py loads the hand-made rows, python load_local.py local_data your extract. Why not dbt seed? Seeds belong to the project, so every target would load them, production included.

One catch: your sources point at RAW_DB, which doesn't exist inside the DuckDB file. Make the source's database follow the target:

# models/staging/_sources.yml
sources:
  - name: raw
    database: "{{ target.database if target.type == 'duckdb' else 'RAW_DB' }}"
    schema: PUBLIC          # DuckDB ignores case: PUBLIC finds public
    tables:
      - name: orders
      - name: customers

On DuckDB that reads analytics.public.orders. The volume is tiny on purpose: you're checking logic, not performance.

What a local run does and doesn't prove

DuckDB answers this
Does the SQL do what I meant?Joins, filters, grain, null handling.
Is the graph still valid?Every ref() resolves, build order is right.
Do the tests pass?Same YAML. Unit tests fully, data tests on your sample.
Only Snowflake answers this
Is it fast at full volume?Pruning, clustering, warehouse size (part 4).
Is the real data clean?Data tests on ten million real rows, not your sample.
Does Snowflake-specific SQL work?Anything DuckDB doesn't implement. Skip those models locally with --exclude.

So the rule is simple: catch on the laptop everything the laptop can catch, and let the warehouse be the last checkpoint instead of the first.

DuckDBlogic · structureunit tests
green?seconds per loop↺ no: refine the SQL, run again, still free
yes
Warehousefull volumes · performancedata tests on real rowswarehouse-specific SQLthe pre_prod build
ship
· · ·

The same trick in CI

Here's step 11's pipeline, grown up, in its three GitLab stages. While a merge request into pre_prod is open, test runs the sqlfmt, dbt-score and DuckDB jobs side by side, with no warehouse. Then build builds the change on Snowflake with target pre_prod (step 10's state:modified+ command). deploy runs after the merge.

The DuckDB job runs on the CI runner, the throwaway machine GitLab starts for each job. Copy step 12's score job (stage, image, DBT_PROFILES_DIR: ci, rules) and swap in this script. No credentials, no credits:

# the DuckDB job's script, using the local target in ci/profiles.yml
pip install -r requirements-dev.txt
dbt deps                             # install packages (step 6)
python load_local.py                 # sample_data/, committed
dbt run --target local --empty       # every model, zero rows (dbt 1.8+)
dbt test --target local --select test_type:unit

--empty reads no rows, but the source tables must exist, for their columns, so the load runs first. Broken SQL and failing unit tests die here, and only code that passed reaches the build stage:

Push
merge request opens
→
test
format · score · DuckDB
→
build
Snowflake, target pre_prod
→
deploy
after the merge
test runs in the CI runner, before anything is merged: no warehouse, no cost, most failures die here
Where it sits
Userland made iteration safe, DuckDB makes it free. Before CI touches Snowflake, the change has passed twice at no cost: on your laptop and in CI.

Lesson quiz

  1. Which question can a local DuckDB run answer?

    1. Is last night's real data clean?
    2. Is this model fast enough at full volume?
    3. Does this join produce the grain I meant?
    4. Will clustering let this filter prune?
  2. What are the two ways the lesson feeds DuckDB locally?

    1. INSERT statements typed by hand, or rows copied from the dbt docs catalog
    2. A small CSV extract from the warehouse, or a few rows you write by hand
    3. A full copy of production, or a zero-copy clone exported to a local Parquet file
    4. A live link to Snowflake's storage, or last night's backup of the database
  3. A merge request adds a model reading source('raw', 'payments'), but nobody added payments.csv to sample_data/. What happens in CI's DuckDB job?

    1. It skips that model and runs the unit tests only
    2. It fails: the source table isn't in DuckDB
    3. It passes, and reads payments from RAW_DB instead
    4. It passes, since --empty reads no rows at all