EXECUTION PATHS

PATH 1: NATIVE DBT (PUSHDOWN ON THE DEFAULT TARGET)

{{ config(materialized='table') }}    -- or view / incremental / ephemeral

select * from {{ source('analytics', 'orders') }}   -- on the default target

When:standard dbt materializations. The model compiles in the default target's dialect and the official dbt adapter executes it natively — the entire query "pushes down" to the engine. Zero data movement, full adapter speed, every adapter-specific feature available.

This is stock dbt, untouched. All sources must live on the default target.

PATH 2: FEDERATED (F_TABLE / F_INCREMENTAL)

{{ config(materialized='f_table') }}

select o.*, c.region
from {{ source('mysql_crm', 'orders') }} o       -- MySQL
join {{ source('sf_finance', 'customers') }} c    -- Snowflake
  on o.customer_id = c.customer_id

When: federated materializations. DVT decomposes the query, extracts each source with pushdownin that engine's dialect, computes in local DuckDB, and loads the result to the target. Sources can be on any mix of engines — including all on one.

A federated model federates always — even single-engine — so it stays portable when sources move or the default target changes. Single-connection models are optimized automatically: the whole query pushes down to the source engine and Sling streams the result directly to the target, skipping DuckDB entirely (as long as the target is a SQL engine — bucket targets and file-based sources always take the standard pipeline). By default the result lands on the default target; add target='any_output' to land it on another engine or a bucket instead.

PATH 3: PYTHON MODELS (LOCAL EXECUTION)

# models/ingest/fx_rates.py
def model(dbt, session):
    dbt.config(materialized="f_table", target="pg_dev")
    import requests, pandas as pd
    data = requests.get("https://api.frankfurter.app/latest").json()
    return pd.DataFrame(data["rates"].items(), columns=["currency", "rate"])

When: .pymodels. They always run through DVT — locally, on your machine — and the returned DataFrame materializes through the same load pipeline as any federated model. APIs, MongoDB, ML scoring: if Python reaches it, it's a source. Python models docs →

WHAT IS NOT A PATH: TARGET= ON A STANDARD MODEL

-- ❌ REFUSED by dvt run
{{ config(materialized='table', target='snowflake_prod') }}

dbt ignores target= on its own materializations, so this model would silently materialize on the default target — not where you asked. DVT refuses it with an error telling you to switch to f_table / f_incremental. Writing across engines is exactly what federated models are for.

TWO DIALECTS, ONE PROJECT

Federated models never touch the default target's dialect — which is why switching the default target only ever affects Path 1 models. Macros used inside federated models must emit DuckDB-compatible SQL.

DECISION TREE

Where do the model's sources live?
  ├─ All on the default target, result on the default target
  │     → materialized='table' / 'view' / 'incremental'   (Path 1: native dbt)
  │
  ├─ More than one engine — OR result must land elsewhere —
  │  OR the model should survive a default-target switch untouched
  │     → materialized='f_table' / 'f_incremental'        (Path 2: federated)
  │        └─ add target='output_name' to choose where the result lands
  │
  └─ Source is an API / NoSQL / anything Python reaches
        → write a .py model                               (Path 3: python)