EXECUTION PATHS

PATH 1: DEFAULT PUSHDOWN

When ALL sources are on the same connection as the default target, DVT pushes SQL directly to the target engine via the dbt adapter. Zero data movement — identical to stock dbt.

┌─────────────────────────────────────────────┐
│  PostgreSQL (default target)                 │
│                                              │
│  source.customers ─┐                         │
│                     ├── CREATE TABLE report   │
│  source.orders   ──┘    AS (SELECT ...)      │
│                                              │
│  SQL dialect: PostgreSQL                     │
└─────────────────────────────────────────────┘

When it triggers: All sources and model target resolve to the default target connection.

SQL dialect:Target's native SQL (Snowflake SQL, PostgreSQL, etc.)

Sling: Not used. DuckDB: Not used.

PATH 2: NON-DEFAULT PUSHDOWN

When all sources and the model target resolve to the same non-default engine, the adapter pushes SQL directly on that engine — no extraction needed. Same as Path 1 but on a non-default connection.

┌─────────────────────────────────────────────┐
│  Snowflake (non-default target)              │
│                                              │
│  source.sales  ──┐                           │
│                   ├── CREATE TABLE summary    │
│  source.dims  ───┘    AS (SELECT ...)        │
│                                              │
│  SQL dialect: Snowflake SQL                  │
└─────────────────────────────────────────────┘

Model config:
{{ config(materialized='table', target='snowflake_prod') }}

When it triggers: Model has config(target='...') and ALL sources share the same connection as that non-default target.

SQL dialect:That target's native SQL.

Sling: Not used. DuckDB: Not used.

PATH 3: EXTRACTION (FEDERATION)

When at least one source is on a different enginethan the model's target, DVT extracts each remote source via Sling into a local DuckDB cache, executes the cross-engine SQL in DuckDB, then loads the result to the target via Sling.

┌──────────────┐     ┌──────────────┐
│  MySQL       │     │  Snowflake   │
│  source.crm  │     │  source.erp  │
└──────┬───────┘     └──────┬───────┘
       │ Sling               │ Sling
       ▼                     ▼
┌─────────────────────────────────────┐
│  DuckDB Cache (.dvt/cache.duckdb)   │
│                                     │
│  crm__customers + erp__orders       │
│         └── SELECT ... JOIN ──┐     │
│                               ▼     │
│            model_result             │
└─────────────────┬───────────────────┘
                  │ Sling
                  ▼
┌─────────────────────────────────────┐
│  PostgreSQL (target)                 │
│  public.cross_crm_erp               │
└─────────────────────────────────────┘

When it triggers:At least one source is on a different engine than the model's target.

SQL dialect: DuckDB SQL (Postgres-like, universal).

Incremental: Fully supported. DuckDB cache persists between runs, enabling watermark-based delta extraction. Only new/changed rows are extracted.

PATH 4: CROSS-TARGET OUTPUT

When a model targets a non-default database via config(target='other_db'), DVT routes it through the extraction path: Sling extracts each source into the persistent DuckDB cache, DuckDB executes the model SQL, then Sling loads the result directly from the DuckDB cache to the target. The cache file (.dvt/cache.duckdb) serves as the intermediate storage — there are no temporary tables.

┌──────────────┐     ┌──────────────┐
│  PostgreSQL  │     │  MySQL       │
│  source.erp  │     │  source.crm  │
└──────┬───────┘     └──────┬───────┘
       │ Sling               │ Sling
       ▼                     ▼
┌─────────────────────────────────────┐
│  DuckDB Cache (.dvt/cache.duckdb)   │
│                                     │
│  erp__invoices + crm__customers     │
│       └── model SQL executes ──┐    │
│                                ▼    │
│         __model__report (cached)    │
└─────────────────┬───────────────────┘
                  │ Sling (reads from cache)
                  ▼
┌─────────────────────────────────────┐
│  Snowflake (target)                  │
│  analytics.report                    │
└─────────────────────────────────────┘

When it triggers: Model has config(target='...')pointing to a non-default output, and sources don't all live on that target.

SQL dialect: DuckDB SQL (Postgres-like). Because this path involves cross-engine data movement, the model SQL executes in DuckDB — so you must write these models in DuckDB SQL, not in the target's native dialect.

Also supports cloud bucket targets — materialize to S3, GCS, or Azure. Use config(format='delta') to control the output file format (Parquet, Delta, CSV, JSON, Avro).

-- Cloud bucket as target
{{ config(
    materialized='table',
    target='data_lake',        -- S3/GCS/Azure from profiles.yml
    format='delta'             -- overrides profiles.yml default
) }}

SELECT * FROM {{ ref('fct_orders') }}

PATHS CAN COMBINE

Path 3 and Path 4 share the same mechanism — both use the extraction path through DuckDB. If a model has cross-engine sources and a non-default target, Sling extracts all remote sources into DuckDB, the model SQL runs in DuckDB, and Sling loads the result directly from the DuckDB cache to the final target.

┌──────────────┐     ┌──────────────┐
│  MySQL       │     │  Oracle      │
│  source.crm  │     │  source.erp  │
└──────┬───────┘     └──────┬───────┘
       │ Sling               │ Sling
       ▼                     ▼
┌─────────────────────────────────────┐
│  DuckDB Cache                       │
│  crm__customers + erp__orders       │
│  → model SQL → result cached        │
└─────────────────┬───────────────────┘
                  │ Sling (Path 4)
                  ▼
┌─────────────────────────────────────┐
│  S3 bucket (target)                  │
│  s3://lake/reports/combined.parquet  │
└─────────────────────────────────────┘

DECISION TREE

dvt run → for each model:
  │
  ├─ Does the model have config(target='...')?
  │   │
  │   ├─ YES: Do all sources share that target's connection?
  │   │   ├─ YES → Path 2: Non-Default Pushdown
  │   │   │        (adapter executes on that engine directly)
  │   │   │
  │   │   └─ NO  → Path 3 + Path 4: Extraction → Cross-Target
  │   │            (Sling extracts → DuckDB → Sling to target)
  │   │
  │   └─ NO (uses default target):
  │       │
  │       ├─ All sources on default target?
  │       │   └─ YES → Path 1: Default Pushdown
  │       │            (adapter executes on default engine)
  │       │
  │       └─ Any source on a different engine?
  │           └─ YES → Path 3: Extraction
  │                    (Sling → DuckDB → Sling to default target)

TWO DIALECTS, ONE PROJECT

DVT projects naturally contain SQL in two dialects. The dialect is determined automatically by the execution path, not by configuration.

MATERIALIZATION RULES