DuckDB in Dev, Snowflake in Prod: A Practical dbt Workflow for a Medallion Project

“DuckDB in dev. Snowflake in prod. Best of both.” is an appealing premise. dbt announced beta DuckDB support in the Fusion CLI in April 2026. As of July 15, 2026, it remained a beta way to run a dbt project against an embedded database without first obtaining warehouse credentials.

The useful version of the idea is more precise:

Use DuckDB as a fast inner development loop. Keep a smaller Snowflake validation loop as the production-compatibility gate.

DuckDB can validate a large amount of transformation logic cheaply and quickly. It cannot prove that Snowflake-specific SQL, incremental behavior, security policies, identifiers, time zones, or physical performance will behave correctly. Fusion improves parsing; paired with the dbt VS Code extension, it also adds editor feedback and column-level lineage. It is not a SQL-dialect translation layer.

This article works through a representative existing Idea app where users submit ideas, vote, comment, and generate product events. Its analytics project has many dbt models organized into bronze, silver, and gold layers. The example is intentionally concrete, but the workflow applies to other mature dbt projects.

Audience: engineers and architects responsible for analytics development, CI, data security, and warehouse cost.

TL;DR

• As of July 15, 2026, DuckDB support in the dbt Fusion CLI is beta. One dbt codebase can target local DuckDB and production Snowflake, but that does not mean identical compiled SQL or runtime behavior.
• Do not point bronze models directly at read_parquet() or read_csv() if you want reliable Fusion static analysis. Load sanitized fixtures into source-shaped DuckDB tables first, then let dbt read those tables through normal source() calls.
• For a large medallion graph, use a persistent .duckdb file, build the full portable graph once, then use state selection for changed models. A production Snowflake manifest can help identify code changes, but cross-engine --defer does not make Snowflake relations queryable from DuckDB.
• Put database-specific expressions behind dispatched macros, and tag truly Snowflake-only models so local runs exclude them deliberately.
• Treat incremental-model support as version-specific: test portable logic in DuckDB, then test production materialization behavior in Snowflake.
• Keep a test pyramid: dbt unit tests and a portable DuckDB build for fast feedback, then Snowflake Slim CI or a governed clone for production parity.
• Local compute is not automatically free. Exporting fixtures consumes Snowflake compute and may incur cross-region/cross-cloud transfer charges, depending on the unload path.

What You Will Learn Here

• What dbt Fusion’s DuckDB support changes—and what it does not
• How to map an existing bronze/silver/gold dbt project onto local DuckDB
• How to create source-shaped, sanitized local data without rewriting model references
• How to structure profiles, adapter-dispatched macros, model tags, and state selection
• Which checks belong in DuckDB and which must still run in Snowflake
• The most likely portability, data, security, concurrency, and performance failures
• A symptom-to-diagnosis troubleshooting playbook

What Changed in April 2026—and Where It Stood in July

The local DuckDB pattern is not new. The community dbt-duckdb adapter already allowed dbt Core projects to run against DuckDB. The new part is that DuckDB is now built into the Fusion CLI. The CLI supplies the Rust engine’s parsing and execution workflow; column-level lineage and the richer editor experience require the dbt VS Code extension.

The official DuckDB setup page makes four boundaries important:

1. The Fusion DuckDB adapter is beta.
2. It does not yet have full feature parity with the existing dbt-duckdb adapter.
3. Fusion’s bundled DuckDB driver cannot load extensions such as httpfs, parquet, or spatial; extension use requires a system-installed DuckDB driver installed with the dbc CLI.
4. Static analysis may not infer the schemas of local CSV, Parquet, or JSON files referenced through read_csv(), read_parquet(), or read_json(). A query can therefore work at runtime while Fusion reports a type-resolution warning or compilation error.

dbc is an ADBC-driver installation CLI; it is not another dbt adapter:

dbc install duckdb

Install the current preview using the command from the official quickstart, then pin the exact working version in the team’s development image and CI:

python -m pip install --pre dbt
dbt --version

Preview builds change quickly. Do not copy a floating 2.0.0-preview.x placeholder into a reproducible build.

Choose the local runner deliberately:

If both local runners are needed during migration, pin them in separate virtual environments or containers. Do not let two installations compete for the same dbt command.

The adapter removes connection friction; it does not remove the second execution engine:

dbt project + Jinja + DAG
              │
      ┌───────┴────────┐
      │                │
      ▼                ▼
 Fusion + DuckDB    Fusion + Snowflake
 local relations    warehouse relations
 DuckDB SQL          Snowflake SQL
 local resources     Snowflake policies and compute
      │                │
 fast logic signal   production-parity signal

dbt’s adapter dispatch exists precisely because SQL syntax, data types, and supported behavior differ across data platforms. Fusion can understand both targets without making them equivalent.

The Existing Idea App Case

Assume the application already has:

• PostgreSQL transactional tables for users, ideas, votes, and comments
• product events delivered as JSON
• a billing source with workspace plans
• ingestion jobs landing source data in Snowflake
• roughly 140 dbt models accumulated over time

The exact count is illustrative, not a benchmark. The important part is the shape of the graph:

The medallion names describe increasing quality: bronze is raw, silver is validated and integrated, and gold is business-ready. They do not require a particular database. That makes the logical layers portable, while their ingestion and physical implementation can remain platform-specific.

Start the pilot with one complete vertical slice rather than all 140 models:

dbt build --target duckdb --select +mart_idea_engagement

The leading + selects the mart’s ancestors, forcing the pilot to exercise its raw sources, bronze staging, silver vote rollup, and gold output together.

Target architecture

                         EXISTING PRODUCTION PATH

 Postgres ─┐
 Events  ──┼─> ingestion ─> Snowflake RAW ─> bronze ─> silver ─> gold
 Billing ──┘                                  dbt on Snowflake
                                                    │
                                              dashboards / ML

                         LOCAL DEVELOPMENT PATH

 sanitized, versioned source snapshot
                │
                ▼
      bootstrap raw DuckDB tables
                │
                ▼
       bronze ─> silver ─> gold
          dbt Fusion on DuckDB
                │
       unit + data-test feedback

 Pull request ─> DuckDB check ─> Snowflake parity check ─> merge

The local path does not replace ingestion or copy the warehouse. It supplies a small, production-shaped data contract to the same dbt graph.

Step 1: Keep One Logical Source Contract

The dbt models should keep using stable logical sources:

# models/sources/app.yml
version: 2

sources:
  - name: app
    database: "{{ env_var('DBT_RAW_DATABASE', 'idea_local') }}"
    schema: raw
    tables:
      - name: users
      - name: ideas
      - name: votes
      - name: comments
      - name: product_events

In local development, idea_local is the basename of idea_local.duckdb. In Snowflake CI and production, DBT_RAW_DATABASE points to the governed raw database. Model SQL remains unchanged:

select
    idea_id,
    workspace_id,
    title,
    status,
    created_at,
    updated_at,
    _ingested_at
from {{ source('app', 'ideas') }}

Do not fork the graph into models/duckdb/ and models/snowflake/ copies. That creates two products that drift. Keep one graph and isolate only the expressions or materializations that genuinely differ.

Step 2: Bootstrap Source-Shaped Local Tables

For a mature project, the cleanest local boundary is a set of physical DuckDB tables that match the raw Snowflake source contract.

-- scripts/bootstrap_local.sql
create schema if not exists raw;

create or replace table raw.ideas as
select
    idea_id::varchar as idea_id,
    workspace_id::varchar as workspace_id,
    title::varchar as title,
    status::varchar as status,
    created_at::timestamp as created_at,
    updated_at::timestamp as updated_at,
    _ingested_at::timestamp as _ingested_at
from read_parquet('fixtures/raw/ideas.parquet');

create or replace table raw.votes as
select *
from read_parquet('fixtures/raw/votes.parquet');

Run this bootstrap with a DuckDB client that can read the selected file format:

export DUCKDB_PATH="$(pwd)/local/idea_local.duckdb"
export DUCKDB_TEMP_DIRECTORY="$(pwd)/local/tmp"
mkdir -p local/tmp
duckdb "${DUCKDB_PATH}" < scripts/bootstrap_local.sql

This indirection matters. It avoids Fusion’s documented flat-file reader analysis gap by presenting dbt with ordinary table sources. It also prevents file paths and fixture mechanics from leaking into production models.

Use three data sizes for different jobs:

Randomly sampling each table independently is a common mistake. It destroys foreign-key relationships: sampled votes no longer have sampled ideas, and sampled ideas no longer have sampled users. Sample from business roots such as workspace_id, then include the related rows from every source.

For sensitive data, the bootstrap pipeline should drop unused columns, tokenize stable join keys, generalize timestamps where necessary, and publish a manifest containing schema and row-count checks. A laptop should not become an ungoverned replica of production.

Generate fixtures by business root

The fixture generator must preserve relationships across tables. The following Snowflake-flavored pseudocode selects a deterministic set of workspaces, then exports every related entity. secure_token_v1 represents an organization-owned keyed tokenization function—not a plain public hash:

create or replace transient table fixture_roots as
select workspace_id
from idea_raw.raw.workspaces
where mod(abs(hash(workspace_id)), 1000) < 5
qualify row_number() over (order by workspace_id) <= 100;

create or replace transient table fixture_ideas as
select
    secure_token_v1(i.idea_id) as idea_id,
    secure_token_v1(i.workspace_id) as workspace_id,
    concat('Idea ', row_number() over (order by i.idea_id)) as title,
    i.status,
    date_trunc('day', i.created_at) as created_at,
    date_trunc('day', i.updated_at) as updated_at,
    i._ingested_at
from idea_raw.raw.ideas i
join fixture_roots r using (workspace_id);

create or replace transient table fixture_votes as
select
    secure_token_v1(v.vote_id) as vote_id,
    secure_token_v1(v.idea_id) as idea_id,
    secure_token_v1(v.user_id) as user_id,
    v.voted_at,
    v._ingested_at
from idea_raw.raw.votes v
join idea_raw.raw.ideas i using (idea_id)
join fixture_roots r using (workspace_id);

copy into @dev_fixture_stage/idea-app/2026-07-15.1/ideas/
from fixture_ideas
file_format = (type = parquet compression = snappy)
header = true
overwrite = true;

The same tokenization version must be used for a key everywhere it appears. Repeat the unload for each approved fixture table. Keep the stage access-controlled and version the path so developers and CI can reproduce the same input.

Publish a small manifest with every fixture set:

fixture_version: "2026-07-15.1"
source_snapshot_at: "2026-07-15T00:00:00Z"
sampling_root: workspace_id
sanitizer_version: secure_token_v1
tables:
  raw.ideas: {rows: 842, schema_md5: "..."}
  raw.votes: {rows: 3217, schema_md5: "..."}

After local bootstrap, compare observed row counts and schema hashes with this manifest and fail before running dbt if they differ. The fixture publisher can render the expected values into a validation script:

select case
    when count(*) = 842 then true
    else error('raw.ideas row count does not match fixture manifest')
end as ideas_row_count_ok
from raw.ideas;

with observed as (
    select md5(
        string_agg(
            column_name || ':' || data_type,
            ',' order by column_index
        )
    ) as schema_md5
    from duckdb_columns()
    where database_name = current_database()
      and schema_name = 'raw'
      and table_name = 'ideas'
)
select case
    when schema_md5 = '<manifest schema_md5>' then true
    else error('raw.ideas schema does not match fixture manifest')
end as ideas_schema_ok
from observed;

Run these assertions inside bootstrap_local.sql so a partial download or stale fixture schema stops the workflow before dbt starts.

The platform or data-governance owner should own extraction and sanitization. Analytics engineers should own the deliberately crafted edge-case rows. Refresh the snapshot when source contracts change or when it no longer represents important distributions—not on every local run.

Fixture fidelity also differs by layer: bronze needs malformed and source-specific edge cases; silver needs relationship and grain integrity; gold needs enough group cardinality and skew to exercise business metrics.

Step 3: Define DuckDB and Snowflake Targets

Keep credentials outside the repository, normally in ~/.dbt/profiles.yml or a managed secret:

idea_analytics:
  target: "{{ env_var('DBT_TARGET', 'duckdb') }}"
  outputs:
    duckdb:
      type: duckdb
      path: "{{ env_var('DUCKDB_PATH', './local/idea_local.duckdb') }}"
      schema: analytics
      threads: 4
      settings:
        memory_limit: "4GB"
        temp_directory: "{{ env_var('DUCKDB_TEMP_DIRECTORY', './local/tmp') }}"

    snowflake:
      type: snowflake
      account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
      user: "{{ env_var('SNOWFLAKE_USER') }}"
      private_key_path: "{{ env_var('SNOWFLAKE_PRIVATE_KEY_PATH') }}"
      role: TRANSFORMER
      database: IDEA_ANALYTICS
      warehouse: DBT_CI
      schema: "DBT_{{ env_var('CI_RUN_ID', 'DEV') }}"
      threads: 8

Then verify the targets separately:

export DUCKDB_PATH="$(pwd)/local/idea_local.duckdb"
export DUCKDB_TEMP_DIRECTORY="$(pwd)/local/tmp"
DBT_TARGET=duckdb dbt debug --target duckdb
DBT_TARGET=snowflake dbt debug --target snowflake

DuckDB resolves relative database paths from the profiles.yml location, which may be ~/.dbt rather than the project. An absolute DUCKDB_PATH keeps the bootstrap command and dbt pointed at the same file.

Use a Fusion-supported non-password option such as key-pair authentication in Snowflake CI. If you want workload identity federation, first confirm support in the pinned Fusion driver and authentication method. The profile above only shows where secret-backed fields belong.

A persistent .duckdb file is preferable to :memory: for a large graph. An in-memory database starts empty on every invocation, so separate dbt run and dbt test commands cannot rely on materialized parents from a previous process.

Step 4: Define a Portability Boundary

Most select/join/filter/window logic can be shared. Database-specific syntax should be visible rather than accidental.

For example, JSON extraction differs between Snowflake and DuckDB. Put that difference behind adapter dispatch:

The example assumes the project’s name in dbt_project.yml is idea_analytics; replace that dispatch namespace with the real project or package name.

-- macros/json_string.sql
{% macro json_string(column_name, json_path) %}
  {{ return(adapter.dispatch('json_string', 'idea_analytics')(column_name, json_path)) }}
{% endmacro %}

{% macro snowflake__json_string(column_name, json_path) %}
  get_path(
    parse_json({{ column_name }}),
    {{ dbt.string_literal(json_path) }}
  )::varchar
{% endmacro %}

{% macro duckdb__json_string(column_name, json_path) %}
  json_extract_string(
    {{ column_name }},
    {{ dbt.string_literal('$.' ~ json_path) }}
  )
{% endmacro %}

This example defines payload as JSON text at the shared boundary. The model keeps the business intent:

-- models/silver/int_event_idea_ids.sql
select
    event_id,
    {{ json_string('payload', 'idea_id') }} as idea_id,
    occurred_at
from {{ source('app', 'product_events') }}

Prefer dbt’s existing cross-database macros for common operations such as date arithmetic, safe casts, string functions, and type names. Add project dispatch only where the built-in abstraction does not cover the requirement.

This pattern improves maintainability, but it creates two compiled expressions. Shared tests must assert that both implementations produce the same output.

unit_tests:
  - name: extracts_idea_id_from_json_text
    model: int_event_idea_ids
    given:
      - input: source('app', 'product_events')
        rows:
          - event_id: e1
            payload: '{"idea_id":"i1"}'
            occurred_at: '2026-07-15 10:00:00'
    expect:
      rows:
        - event_id: e1
          idea_id: i1
          occurred_at: '2026-07-15 10:00:00'

Run the same test on both targets. A DuckDB pass says nothing about the Snowflake-dispatched implementation:

dbt test --target duckdb --select extracts_idea_id_from_json_text
dbt test --target snowflake --select extracts_idea_id_from_json_text

This test covers one scalar path. Add explicit cases for a missing key, JSON null, SQL NULL, arrays, numbers, and nested objects before treating the macro as a general JSON compatibility layer.

Tag models whose behavior cannot be reproduced locally:

{{ config(tags=['snowflake_only']) }}

select
    idea_id,
    ai_complete('model-name', moderation_prompt) as moderation_result
from {{ ref('ideas_requiring_moderation') }}

Run the portable graph explicitly:

dbt build --target duckdb --exclude tag:snowflake_only+

Do not replace a Snowflake-only model with fake local SQL that always passes. Either provide a meaningful contract stub at a clearly defined boundary or exclude the model and validate it in Snowflake.

The tag boundary must also be valid in the DAG. Excluding a Snowflake-only parent while selecting its portable descendants leaves a reference to a relation that the local run did not build. Exclude that complete branch or provide a tested boundary relation with the same logical contract.

Inspect and exclude the complete Snowflake-only branch, including its descendants:

dbt ls --select tag:snowflake_only+
dbt build --target duckdb --exclude tag:snowflake_only+

If a portable mart genuinely requires one of those descendants, the boundary needs a real local implementation or a fixture-backed contract relation. A tag alone cannot make the dependency portable.

Step 5: Make a Large Graph Fast

On a fresh checkout:

export DUCKDB_PATH="$(pwd)/local/idea_local.duckdb"
export DUCKDB_TEMP_DIRECTORY="$(pwd)/local/tmp"
mkdir -p local/tmp
duckdb "${DUCKDB_PATH}" < scripts/bootstrap_local.sql
dbt build --target duckdb --exclude tag:snowflake_only+
mkdir -p .dbt-state/local
cp target/manifest.json .dbt-state/local/manifest.json

During normal iteration:

dbt build \
  --target duckdb \
  --select state:modified+ \
  --state .dbt-state/local \
  --exclude tag:snowflake_only+

This works when the unchanged upstream relations are still present in the persistent DuckDB file. If the file is new or an upstream relation is missing, include ancestors:

dbt build \
  --target duckdb \
  --select +state:modified+ \
  --state .dbt-state/local \
  --exclude tag:snowflake_only+

Do not blindly add production --defer to the DuckDB command. dbt deferral resolves unbuilt ref() calls to relations from another manifest. A Snowflake relation named in a production manifest does not become readable by a local DuckDB process. Use a DuckDB manifest built from the comparison branch and local relations for local execution.

A Snowflake manifest can still be useful for inspecting logical changes, but environment-aware database, schema, alias, env_var, and var configuration can create surprising state:modified results across targets. dbt documents these state-comparison caveats. Inspect the selected set before treating a cross-engine manifest as authoritative.

Step 6: Test Logic and Incremental State Separately

dbt unit tests are a good fit for narrow transformation rules because they use static inputs. A lifecycle model can test invalid transitions without building the full graph:

unit_tests:
  - name: closed_ideas_cannot_return_to_review
    model: int_idea_lifecycle
    given:
      - input: ref('stg_app__ideas')
        rows:
          - {idea_id: i1, status: closed, updated_at: '2026-07-10 09:00:00'}
      - input: ref('stg_app__idea_status_events')
        rows:
          - {idea_id: i1, status: in_review, occurred_at: '2026-07-10 10:00:00'}
    expect:
      rows:
        - {idea_id: i1, effective_status: closed}

Run the fast unit layer:

dbt test --target duckdb --select test_type:unit

dbt v2’s built-in column awareness removes the dbt Core v1 requirement to materialize every direct parent before unit tests. If the exact preview still asks for a relation—especially for an incremental model—create schema-only parents with dbt run --empty first.

Then build and run data tests against the local relations:

dbt build --target duckdb --exclude tag:snowflake_only+

Incremental models need an additional stateful test. The mature dbt-duckdb and dbt-snowflake adapters advertise merge and delete+insert, and dbt documents that incremental mechanics vary by database. The Fusion DuckDB adapter is beta and does not yet promise full dbt-duckdb parity, so verify each required strategy against the exact pinned preview before adopting it.

{{ config(
    materialized='incremental',
    unique_key='event_id',
    incremental_strategy='merge'
) }}

select
    event_id,
    idea_id,
    event_type,
    occurred_at,
    _ingested_at,
    _source_sequence
from {{ ref('stg_events__idea_events') }}

{% if is_incremental() %}
where _ingested_at >= (
    select coalesce(
        {{ dbt.dateadd(
            datepart="day",
            interval=-3,
            from_date_or_timestamp="max(_ingested_at)"
        ) }},
        timestamp '1970-01-01'
    )
    from {{ this }}
)
{% endif %}

qualify row_number() over (
    partition by event_id
    order by _ingested_at desc, _source_sequence desc
) = 1

The three-day overlap is illustrative. Set the lookback from the source’s late-arrival service level, or use a durable source offset. A strict > max(_ingested_at) watermark can miss rows that share the maximum timestamp or arrive late. The example assumes ingestion supplies a stable _source_sequence; if no deterministic tie-breaker exists, reject ambiguous duplicates instead of letting row_number() pick one arbitrarily.

Exercise at least these cases on each engine:

1. Empty target and initial load
2. Re-running the same batch
3. A late-arriving event
4. An updated row with the same unique_key
5. Duplicate source rows for one unique_key
6. A schema change

If the current Fusion DuckDB preview does not support the required strategy, use DuckDB to test the model’s full-refresh SQL and keep incremental materialization tests in Snowflake. Do not present a local table rebuild as evidence that a production merge works.

With ERROR_ON_NONDETERMINISTIC_MERGE=TRUE—the Snowflake default—Snowflake errors when multiple source rows match one target row and make an update or update/delete outcome nondeterministic. Duplicate unmatched source rows can still be inserted separately. Switching to delete+insert can change behavior, but it should not hide an incorrect grain. Add a uniqueness test and fix the source logic first.

The CI Design: Fast First, Faithful Second

The practical workflow is a test pyramid, not an engine swap:

Every save / local command
  └─ Fusion parsing + selected DuckDB model
     (+ editor and column lineage with the dbt VS Code extension)

Every commit
  └─ dbt unit tests + portable DuckDB graph on deterministic fixtures

Every pull request
  └─ Snowflake Slim CI for state:modified+ and downstream tests

Before production deployment
  └─ Snowflake-native models, policies, hooks, incremental paths,
     query plans, and required scale checks

An illustrative PR pipeline:

# Gate 1: fast and warehouse-independent
export DUCKDB_BASENAME="ci_${CI_RUN_ID}"
export DUCKDB_PATH="$(pwd)/local/${DUCKDB_BASENAME}.duckdb"
export DUCKDB_TEMP_DIRECTORY="$(pwd)/local/tmp"
export DBT_RAW_DATABASE="${DUCKDB_BASENAME}"
mkdir -p local/tmp
duckdb "${DUCKDB_PATH}" < scripts/bootstrap_local.sql
DBT_TARGET=duckdb dbt build \
  --target duckdb \
  --exclude tag:snowflake_only+

# Gate 2: real target semantics
DBT_TARGET=snowflake dbt build \
  --target snowflake \
  --select state:modified+ \
  --defer \
  --state path/to/production-artifacts

The second gate follows dbt’s Slim CI pattern: build changed models and their downstream dependencies in an isolated Snowflake schema while resolving unselected parents from a prior production manifest.

For high-fidelity tests, Snowflake also documents zero-copy cloning of databases or schemas. A clone can expose production-shaped data without duplicating all storage, but CI still consumes warehouse compute and stores changed micro-partitions. Access to cloned data also remains a governance decision.

The cost goal should be less warehouse work, not “no Snowflake anywhere.” A small, targeted Snowflake gate preserves production semantics while reducing warehouse work relative to running the full Snowflake graph on every edit.

Where Portability Usually Breaks

Troubleshooting Playbook

Start with the narrowest diagnostic:

“Fusion cannot resolve the type from read_parquet()”

Likely cause: Fusion’s current static analysis cannot infer the flat file’s schema, even though DuckDB can execute the query.

Fix:

1. Load the file into a physical raw table in the bootstrap step.
2. Reference that table through source().
3. If an extension is required, install the system DuckDB driver with dbc install duckdb; the bundled Fusion driver cannot load extensions.
4. If strict analysis blocks otherwise valid file-reader SQL, relax only the affected branch to static_analysis: baseline. Baseline can still warn and does not provide strict type checking or rich column lineage.

“The model passes locally but Snowflake says unknown function or syntax error”

Likely cause: warehouse-specific SQL leaked into a supposedly portable model.

Fix:

• Inspect compiled SQL for both targets.
• Replace the expression with a dbt cross-database macro where available.
• Otherwise add snowflake__... and duckdb__... dispatched implementations.
• Add a fixture asserting equal results and keep the Snowflake PR gate.

“Source table not found in DuckDB”

Likely cause: the source’s database does not match the DuckDB file basename, the bootstrap used another schema, or a relative path resolved from a different profiles directory.

Fix:

dbt debug --target duckdb
duckdb "${DUCKDB_PATH}" \
  -c "select database_name, schema_name, table_name from duckdb_tables();"

Align DBT_RAW_DATABASE, the .duckdb basename, source schema, and bootstrap output. Prefer an absolute DuckDB path in CI.

“Could not set lock on DuckDB file”

Likely cause: another process has the file open for writing. DuckDB’s native file supports one read-write process at a time, although that process can use multiple threads.

Fix:

• Close interactive DuckDB clients before running dbt.
• Give every CI job its own database path.
• Do not share one writable .duckdb file across containers or a network filesystem.
• Keep dbt’s internal model concurrency inside one process.

“DuckDB is killed or fills the disk”

Likely cause: a wide join, sort, or high thread count exhausted memory or temporary storage.

Fix:

• Lower dbt and DuckDB thread counts.
• Set memory_limit, temp_directory, and max_temp_directory_size.
• Ensure the temp path has space.
• Reduce fixture size while preserving edge cases and relational integrity.
• Use preserve_insertion_order=false for applicable bulk workloads after confirming order is not part of the contract.

DuckDB’s own out-of-memory guide notes that lowering the memory limit to roughly 50–60% of machine RAM can prevent the operating system from killing the process because some operations allocate outside the buffer manager.

“Snowflake reports a nondeterministic merge”

Likely cause: the incremental model’s incoming rows are not unique on unique_key.

Fix:

1. Run a grouped duplicate query on the incremental input.
2. Add a dbt uniqueness test at the model’s declared grain.
3. Deduplicate deterministically with an explicit ordering rule.
4. Use delete+insert only when its semantics are actually intended.

A tiny local fixture often misses this because it contains only one row per key.

“Counts differ between DuckDB and Snowflake”

Check these in order:

1. Fixture version and source row counts
2. Null handling in joins and predicates
3. Identifier and string case
4. Timestamp type, session time zone, and date-boundary casts
5. Decimal precision and integer division
6. JSON null versus SQL NULL
7. Nondeterministic row_number() or limit without a complete order by
8. Incremental target state

Turn the smallest differing rows into a deterministic unit-test fixture before changing production SQL.

“The local graph is stale or a relation is missing”

Likely cause: state selection assumed a relation from a previous local build, but the database file or manifest came from another branch.

Fix:

export DUCKDB_PATH="$(pwd)/local/idea_local.duckdb"
rm -f "${DUCKDB_PATH}"
duckdb "${DUCKDB_PATH}" < scripts/bootstrap_local.sql
dbt build --target duckdb --exclude tag:snowflake_only+
mkdir -p .dbt-state/local
cp target/manifest.json .dbt-state/local/manifest.json

Treat the database file and its saved manifest as one cache generation. Rebuild both after changing branches, upgrading dbt, or changing source fixtures materially.

“A package or hook worked in dbt Core but fails in Fusion”

Likely cause: incomplete Fusion compatibility, removed deprecated behavior, intricate introspection, or unsupported Jinja/adapter behavior.

Fix:

• Upgrade packages and check that their supported dbt range includes 2.0.0.
• Run the official Fusion readiness checks.
• Start affected resources in baseline static-analysis mode.
• Pin the known-good Fusion preview.
• Keep the existing dbt Core/Snowflake production path until the blocking feature has a verified migration.

The presence of a DuckDB target does not make an existing project Fusion-ready.

“The Snowflake bill moved instead of shrinking”

Likely cause: developers repeatedly export large snapshots, read cross-region object storage, or still run a full Snowflake graph in CI.

Fix:

• Publish small, versioned, relationally consistent fixtures on a controlled cadence.
• Keep artifacts in the appropriate region.
• Use state selection in both local and Snowflake gates.
• Measure export compute, applicable data-transfer charges, and CI warehouse credits together.
• Do not refresh local data when a code-only fixture is sufficient.

Security and Governance Are Architecture, Not Cleanup

Local DuckDB changes the trust boundary. Snowflake centralizes authentication, RBAC, masking, row-access policies, and access history. On Enterprise Edition or higher, Access History can record masking and row-access policy references when protected objects are queried. A .duckdb or Parquet file on a laptop does not inherit those controls.

A safe fixture pipeline should answer:

• Who is allowed to produce and download a fixture?
• Which columns are removed, masked, tokenized, or generalized?
• Can stable tokens be joined back to another leaked dataset?
• How long do local files live, and how are they deleted?
• Are fixture versions and generation code auditable?
• Can CI use synthetic data while only a restricted Snowflake gate sees governed data?

If the organization cannot answer those questions, local production-shaped data is not ready for broad developer use. Start with synthetic or hand-curated fixtures.

Related reading: EDA with NATS: CQRS Projections places the warehouse on the analytics read path, while Preparing Databases for Secure AI Agents goes deeper on least privilege and governed data access.

When This Pattern Is a Good Fit

Use DuckDB as the default inner loop when:

• most models are relational SQL rather than Snowflake-native functions
• developers spend meaningful time waiting for small model iterations
• the project can create representative, sanitized source fixtures
• the team will maintain adapter dispatch and parity tests
• a real Snowflake gate remains before merge or deployment

Keep Snowflake as the main development target when:

• the graph depends heavily on Snowpark, Cortex, external functions, streams/tasks, secure objects, or platform policies
• correctness depends on production-scale distribution and query plans
• data cannot legally or safely leave the governed platform
• source replication would cost more than the development queries it replaces
• maintaining two adapter implementations would exceed the saved feedback time

This does not have to be all-or-nothing. A project may find that a large share of its silver and gold logic is portable while ingestion, governance, and specialized models remain Snowflake-only.

A Low-Risk Adoption Plan

1. Inventory portability. Classify models and packages as portable, dispatch-needed, or Snowflake-only.
2. Pick one vertical slice. For the Idea app, use ideas + votes → idea engagement, spanning bronze, silver, and gold.
3. Build source-shaped fixtures. Include nulls, duplicate votes, deleted ideas, late events, large decimals, and time-zone boundaries.
4. Add the DuckDB target. Use a persistent file and pin the Fusion preview version.
5. Remove direct file readers from model SQL. Bootstrap files into raw tables.
6. Add cross-engine tests. Start with dispatched functions and incremental models.
7. Add the fast CI gate. Run unit tests and the portable graph on an isolated DuckDB file.
8. Keep Snowflake Slim CI. Run changed models and descendants with real target semantics.
9. Measure the whole system. Compare feedback latency, Snowflake credits, fixture-generation cost, applicable data-transfer charges, and parity failures.
10. Expand only when parity failures stay visible. A failure caught by Snowflake CI is useful evidence about the boundary, not a reason to hide the gate.

The Practical Verdict

“DuckDB in dev, Snowflake in prod” is valuable when treated as a layered verification strategy:

• DuckDB answers: Does the portable transformation logic work on a small, representative dataset?
• Snowflake answers: Will the production target compile, materialize, secure, and execute it correctly?

Fusion makes the first answer faster and richer through local execution and SQL comprehension. With the dbt VS Code extension, the same workflow also exposes editor feedback and column-level lineage. It does not collapse the two questions into one.

For a large medallion project, the best implementation is not to mirror the warehouse on every laptop. It is to preserve one dbt graph, establish a deliberate portability boundary, provide small source-shaped fixtures, and spend Snowflake compute only where Snowflake is the thing being tested.

Sources

Consulted July 15, 2026. Product status is date-sensitive.

• dbt Developer Hub: DuckDB setup — Fusion DuckDB beta status, built-in adapter, driver/extension requirements, profile options, and static-analysis limitations for flat files
• dbc documentation: Installing drivers — installing the system DuckDB ADBC driver with dbc
• dbt Developer Hub: April 2026 release notes — April 2026 announcement of beta DuckDB support in the CLI
• dbt Developer Hub: Quickstart for the dbt Fusion engine — current preview installation and local CLI workflow
• dbt Developer Hub: About data platform connections — built-in Fusion adapters and ADBC connection architecture
• dbt Developer Hub: Static analysis — baseline/strict modes and known analysis constraints
• dbt Developer Hub: Supported Fusion features — distinction between standalone CLI capabilities and VS Code extension features
• dbt Developer Hub: Upgrading to v2.0 — v2 unit-test ordering and built-in column awareness
• dbt Developer Hub: Fusion readiness checklist — package and project migration checks
• dbt Developer Hub: Adapter dispatch and cross-database macros — database-specific macro implementations and portable operations
• dbt Developer Hub: Unit tests — static model inputs and recommended development/CI use
• dbt Developer Hub: Incremental strategy and Snowflake configurations — supported strategies and Snowflake merge behavior
• dbt Developer Hub: Best practices for workflows, Defer, and state comparison caveats — state selection, sandboxed environments, Slim CI, and environment-aware comparison risks
• DuckDB documentation: SQL dialect — PostgreSQL-based dialect and documented semantic differences
• DuckDB documentation: Concurrency — single read-write process and file locking
• DuckDB documentation: Out-of-memory troubleshooting and Configuration — thread, memory, and temporary-storage controls
• DuckDB documentation: Timestamp types — time-zone representation and configured-zone behavior
• DuckDB documentation: JSON functions — json_extract_string behavior used by the dispatched macro
• DuckDB documentation: Utility functions — error() and hash functions used by fixture-manifest validation
• Snowflake documentation: Identifier requirements — quoted and unquoted identifier case
• Snowflake documentation: Date and time types — TIMESTAMP_NTZ, TIMESTAMP_LTZ, TIMESTAMP_TZ, precision, and session parameters
• Snowflake documentation: FLATTEN — Snowflake-native semi-structured expansion behavior
• Snowflake documentation: GET_PATH — Snowflake JSON path extraction used by the dispatched macro
• Snowflake documentation: MERGE — deterministic and nondeterministic merge behavior
• Snowflake documentation: AI_COMPLETE — current AI completion function used by the Snowflake-only example
• Snowflake documentation: Data transfer cost — when unloads incur cross-region or cross-cloud transfer charges
• Snowflake documentation: Compute cost — warehouse compute consumed by export and CI queries
• Snowflake documentation: Cloning considerations — storage and governance behavior of zero-copy clones
• Snowflake documentation: Access History — auditing object access and applied masking or row-access policies
• dbt Developer Hub: Snowflake setup — authentication methods supported by Fusion
• Snowflake documentation: dbt CI/CD tutorial — isolated dev targets and zero-copy-clone options
• Databricks documentation: Medallion architecture — source for the bronze, silver, and gold layer definitions used here

Inspiration, not evidence: Joachim Hodana’s LinkedIn post supplied the “DuckDB in dev, Snowflake in prod” premise.

Editorial judgment: the two-engine test pyramid, the source-shaped fixture boundary, the model classification, the Idea app implementation, and the adoption sequence are recommended architecture patterns synthesized from the documented capabilities and constraints. They are not claims that DuckDB and Snowflake produce identical results, nor a measured cost benchmark. Validate the pattern against your own graph, data policy, and warehouse bill.