---
title: "Local dev and cloud prod for faster dbt development"
canonical: "https://motherduck.com/blog/dual-execution-dbt/"
related:
  - title: "DuckDB + dbt: Faster Data Pipelines with Local Development | MotherDuck"
    url: "https://motherduck.com/videos/duckdb-dbt-accelerating-the-developer-experience-with-local-power/"
  - title: "dbt Dual Execution Across Local DuckDB and MotherDuck | MotherDuck Docs"
    url: "https://motherduck.com/docs/cookbook/dbt-dual-execution/"
  - title: "dbt with DuckDB and MotherDuck | MotherDuck Docs"
    url: "https://motherduck.com/docs/integrations/transformation/dbt/"
gated_asset:
  title: "MotherDuck Postgres Integration"
  url: "https://motherduck.com/product/postgres-integration/"
---

# Local dev and cloud prod for faster dbt development

<style type="text/css">
.image-footnote {
    margin: 20px 0; /* Adds vertical spacing around the figure */
    text-align: center; /* Centers the content of the figure */
    width: fit-content; /* Shrinks the width to fit the content, helping centering */
    margin-left: auto; /* Centers the figure horizontally */
    margin-right: auto;
}

.image-footnote img {
    max-width: 100%; /* Ensures the image is responsive */
    height: auto; /* Maintains the aspect ratio of the image */
    display: block; /* Ensures the image doesn't have extra space below it */
    margin-bottom: 5px; /* Reduces space between the image and the caption */
}

.image-footnote figcaption {
    font-style: italic; /* Makes the caption text italic */
    color: #666; /* Sets the color of the caption text */
    font-size: 0.9em; /* Adjusts the size of the caption text */
    margin: 0; /* Removes default margin to reduce space */
}

.admonition {
    padding: 5px 5px 5px 50px; /* Top, right, bottom, left padding */
    margin: 5px 0;
    border-radius: 5px;
    position: relative;
    box-sizing: border-box; /* Ensures padding is included in the element's total width and height */
}
.admonition p {
    margin: 0; /* Removes default margin */
    padding: 0; /* Ensures there's no padding adding extra space */
}

.admonition-title {
    font-weight: bold;
    font-size: 1em;
}

.admonition-icon {
    position: absolute;
    top: 10px; /* Adjusts vertical alignment */
    left: 10px; /* Adjusts horizontal alignment */
    width: 26px; /* Icon size */
    height: 26px; /* Icon size */
}
.admonition ol {
    counter-reset: section; /* Resets the numbering */
    list-style-type: none; /* Removes default numbering */
}

.admonition li {
    counter-increment: section; /* Increases the counter */
    margin-bottom: 10px; /* Adds space between list items */
}

.admonition li:before {
    content: counter(section) ". "; /* Adds custom numbering */
    font-weight: bold; /* Makes the number bold */
}

.tip {
    background-color: #fff3cd; /* Light yellow background */
    border: 1px solid #ffeeba;
    color: #856404; /* Darker text for better contrast */
}

.tip .admonition-icon {
    background-image: url('https://motherduck-com-web-prod.s3.amazonaws.com/assets/img/light_bulb_b55003f6a4.svg'); /* Path to your light bubble icon */
    background-size: cover;
    
}

.info {
    background-color: #d1ecf1; /* Light blue background */
    border: 1px solid #bee5eb;
    color: #0c5460; /* Dark blue text for visibility */
}

.info .admonition-icon {
    background-image: url('https://motherduck-com-web-prod.s3.amazonaws.com/assets/img/info_b09d9ce06f.svg'); /* Path to your information icon */
    background-size: cover;
}

.footnote-ref {
    vertical-align: super;
    padding-left: 2px;
    font-size: smaller; /* makes the number smaller and keeps it inline as superscript */
    font-weight: bold; /* Makes the superscript bold */
    text-decoration: underline; /* Adds underline */
}

.footnote {
    border-top: 1px solid #ccc;
    padding-top: 5px;
    margin-top: 10px;
    font-size: 0.9em;
}

.footnote p {
    margin-bottom: 0;
}

.footnote sup {
    vertical-align: super;
    font-size: smaller;
    font-weight: bold; /* Makes the superscript bold */
    text-decoration: underline; /* Adds underline */
}

.rf table {
  max-width: 1000px;
  width: 100%;
  background-color: #ffffff;
  border: 2px solid #383838;
  text-align: left;
  margin-top: 10px;
}

.rf th {
  padding: 8px;
  background-color: rgba(255, 222, 0, 0.8);
  border-bottom: 2px solid #383838;
  font-family: 'Inter', sans-serif;
  font-weight: 400;
  text-transform: uppercase;
  vertical-align: middle;
  text-align: left;
}

.rf td {
  padding: 8px;
  border: 1px solid #d7d7d7;
  font-family: 'Inter', sans-serif;
  text-align: left;
}

.rfc p {
  margin-bottom: 10px;
}
</style>

## Introducktion

I hate waiting for slow pipelines to run, so I am delighted to share some strategies to iterate on your data problems at maximum speed \- MotherDuck even gave a talk on this concept at [dbt Coalesce in 2024](https://www.youtube.com/watch?v=oqwIHvSfOVQ). By harnessing the capabilities of DuckDB locally, backed by MotherDuck in the cloud, we can unlock an incredibly fast and efficient development cycle. We'll explore how to configure your dbt profile for dual execution and share some tips on how much data to bring local. By implementing these techniques, you can significantly accelerate your data pipeline development and iterate even faster to solve business problems.

<div class="admonition info">
    <div class="admonition-icon"></div>
    <p>
Looking to following along in the code? 

Check out the <a href="https://github.com/motherduckdb/motherduck-examples/tree/main/dbt-dual-execution">example repo</a>! 
</p>
</div>

![Instant feedback loop](https://motherduck-com-web-prod.s3.amazonaws.com/assets/img/Instant_feedback_loop_b99078a679.png)

## Setting up your Profile

In order to take advantage of these capabilities, we need to configure our dbt profile to execute in the correct place, as well as define the behavior that we want in our sources. In the example dbt profile below, `prod` runs entirely in the cloud, while `local` runs mostly on local but is also linked to MotherDuck for reading data into your local database.

```yml
dual_execution:
  outputs:
    local:
      type: duckdb
      path: local.db 
      attach:
        - path: "md:"	# attaches all MotherDuck databases
    prod:
      type: duckdb
      path: "md:jdw"
  target: local
```

## Sources & Models

With your sources, you need to define which ones to replicate entirely, which ones are ok as views, and which ones to sample. Keep in mind for sampling, you need to think about your data model and make sure that related samples are hydrated (i.e. if you only bring in 100 customers, you need to make sure you also bring in their orders too).

In my example project using TPC-DS as the source data, I am sampling 1% of the data when running locally on the large tables. In general, I am aiming to keep the datasets less than a million rows per table, although there is no hard limit. For the remaining tables, I am replicating the entire data set locally since they are so small.

The way that we conditionally sample our models is by using the [‘target’ variable](https://docs.getdbt.com/reference/dbt-jinja-functions/target).  You can add this parameter by checking your `target` and running it conditionally on your model. 

An example sql snippet is below (using jinja).

```sql
from {{ source("tpc-ds", "catalog_sales") }}
{% if target.name == 'local' %} using sample 1 % {% endif %}
```

As an example of a simple “create local table from cloud”, consider the following query plan. The “L” indicates Local and the “R” indicates Remote (i.e. MotherDuck).

```bash
🦆 explain create table
        "local"."main"."call_center"
      as (
        from "jdw_dev"."jdw_tpcds"."call_center"
      );

┌─────────────────────────────┐
│┌───────────────────────────┐│
││       Physical Plan       ││
│└───────────────────────────┘│
└─────────────────────────────┘
┌───────────────────────────┐
│ BATCH_CREATE_TABLE_AS (L) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│    DOWNLOAD_SOURCE (L)    │
│    ────────────────────   │
│        bridge_id: 1       │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│  BATCH_DOWNLOAD_SINK (R)  │
│    ────────────────────   │
│        bridge_id: 1       │
│       parallel: true      │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│       SEQ_SCAN  (R)       │
│    ────────────────────   │
│        call_center        │
│                           │
│        Projections:       │
│     cc_call_center_sk     │
│     cc_call_center_id     │
│     cc_rec_start_date     │
│      cc_rec_end_date      │
│     cc_closed_date_sk     │
│      cc_open_date_sk      │
│          cc_name          │
│          cc_class         │
│        cc_employees       │
│          cc_sq_ft         │
│          cc_hours         │
│         cc_manager        │
│         cc_mkt_id         │
│        cc_mkt_class       │
│        cc_mkt_desc        │
│     cc_market_manager     │
│        cc_division        │
│      cc_division_name     │
│         cc_company        │
│      cc_company_name      │
│      cc_street_number     │
│       cc_street_name      │
│       cc_street_type      │
│      cc_suite_number      │
│          cc_city          │
│         cc_county         │
└───────────────────────────┘
```

This can also be extended to your `sources.yml` if necessary for testing local datasets (i.e. json or parquet on experimental pipelines that have not yet made it to your data lake). Configuring these is similar: 

```
{%if- target.name == 'local' -%}
   meta:
      external_location:
        data/tpcds/{name}.parquet
{%- endif -%}
```

## Running your pipeline

Once you have this configuration in place, you can simply run your pipeline as normal, although for ease of use, you may want to add tags to the models that you are working on so you can avoid going back to the cloud data set too often. This can be set simply in the `dbt_project.yml` like this:

```yml
models:
  dual_execution:
    tpcds:
      raw:
        +tags: ['raw']
        +materialized: table
      queries:
        +materialized: view
        +tags: ['queries'] 
```

From there, it is as simple as running `dbt build -s tag:raw` to load your raw data and then for subsequent query iteration, run `dbt build -s tag:queries` in the CLI. The subsequent runs can be visualized like this:

![data flow cloud to local](https://motherduck-com-web-prod.s3.amazonaws.com/assets/img/data_flow_cloud_to_local_792a293673.png)

## Shipping dev to the cloud

Certain tables may need to be available in your cloud data warehouse for testing even in the local workflow. This may be something like a BI tool, that is connected to your cloud instance and is difficult to run locally. This can be accomplished by setting the database attribute in your model, so that after the model is run, it is available in the cloud as well.

```yml
{{ config(
    database="jdw_dev",
    schema="local_to_prod"
    materialized="table"
) }}
```

It should be noted that this is a static configuration that is best used for testing. If you don’t want to manually flip models between dev / prod destinations, you can define the database as an attribute of a specific model in your `dbt_project.yml` file.

## Wrapping up

As you can see from this example, using MotherDuck’s dual execution allows us to leverage the unique value proposition of DuckDB to run an accelerated development cycle on your local machine. With some basic optimization, we can get \~5x faster dbt runs by making the data smaller and using local compute. This is a very powerful combination for rapidly iterating on your pipeline and then pushing a high quality change back into your production environment. To see these dual-execution concepts applied further, check out our guide on [building an end-to-end dbt project with rapid local testing](https://motherduck.com/blog/duckdb-dbt-e2e-data-engineering-project-part-2/).

Want to learn more? Join our webinar about Local Dev & Cloud Prod on [February 13th, 2025](https://lu.ma/0die8ual?utm_source=blog).