Skip to main content
This example demonstrates dual execution in MotherDuck with dbt - the ability to run models across both cloud (MotherDuck) and local (DuckDB) databases in the same pipeline.

​
Overview

Dual execution allows you to:
  • Store some models in the cloud (MotherDuck)
  • Store other models locally (DuckDB file)
  • Reference models across both databases seamlessly
  • Control data placement with simple configuration

​
How It Works

By using the attach parameter in your dbt profile, you can add a second database to DuckDB’s execution context. Models can then specify which database to use via the database parameter.

​
Configuration

​
profiles.yml - Cloud-First Approach

Connect to MotherDuck and attach a local database:
profiles.yml
dual_execution:
  outputs:
    dev:
      type: duckdb
      path: "md:my_db"   # MotherDuck path (cloud)
      attach:
        - path: local.db # Local path
  target: dev

​
profiles.yml - Local-First Approach

Connect locally and attach MotherDuck databases:
profiles.yml
dual_execution:
  outputs:
    local:
      type: duckdb
      path: local.db 
      attach:
        - path: "md:"  # Attaches all MotherDuck databases
      threads: 4
    prod:
      type: duckdb
      path: "md:jdw_dev"
      threads: 4
  target: local

​
Model Configuration

​
Specify Database for Local Models

Use the database config to direct models to local storage:
models/my_second_dbt_model.sql
{{ config(
    database="local",
    materialized="table"
) }}

select * from {{ ref('my_first_dbt_model') }}
where id = 1

​
Cloud Models (Default)

Models without a database config write to the main connection (cloud):
models/my_first_dbt_model.sql
{{ config(
    database="my_db",
    materialized="table"
) }}

with source_data as (
    select 1 as id
    union all
    select null as id
)

select *
from source_data
where id is not null

​
Reference Across Databases

Models can reference other models regardless of location:
models/my_third_dbt_model.sql
{{ config(
    database="my_db",
    materialized="table"
) }}

select * from {{ ref('my_second_dbt_model') }}

​
Data Flow Example

This example shows data flowing between cloud and local:
1

Cloud model runs first

my_first_dbt_model materializes in MotherDuck (my_db database)
2

Data moves to local

my_second_dbt_model reads from the cloud model and writes to local.db
3

Data returns to cloud

my_third_dbt_model reads from the local model and writes back to MotherDuck

​
Running the Project

1

Install dependencies

uv sync
2

Build models

uv run dbt build
Your browser will prompt for MotherDuck authentication unless you have token authentication configured.
3

Verify results

Check that models exist in both locations:
  • Cloud models in MotherDuck UI
  • Local models in local.db file

​
Use Cases

Develop and test models locally, then promote to cloud:
  • Iterate quickly on local data
  • Push production models to MotherDuck
  • Keep test data local while production is in cloud
Combine local and cloud storage strategically:
  • Large raw data in cloud
  • Processed aggregates locally for faster access
  • Temporary work tables local, final outputs cloud
Control storage costs by placement:
  • Frequently accessed data locally
  • Archive data in cloud
  • Intermediate transformations local
Optimize for performance:
  • Keep related data together
  • Minimize cross-database transfers
  • Cache frequently joined tables locally

​
Best Practices

Use the database configuration intentionally - every model should explicitly specify its target database for clarity.
Data transfers between local and cloud have latency implications. Design your pipeline to minimize unnecessary data movement.
The attach parameter can accept multiple paths, allowing you to work with multiple databases simultaneously:
attach:
  - path: local.db
  - path: "md:prod_db"
  - path: "md:dev_db"

​
Advanced: TPC-DS Example

For a more complex example, see the dbt-dual-execution repository which includes:
  • 24 TPC-DS raw tables
  • 99 TPC-DS analytical queries
  • Models distributed across cloud and local
  • Complex query patterns with cross-database references

Build docs developers (and LLMs) love

Get started for freeTalk to us