.SQL MODELS

YOUR FIRST SQL MODEL

Drop a .sql file into models/:

-- models/stg_customers.sql

SELECT
    id,
    name,
    email,
    created_at
FROM {{ ref('raw_customers') }}
WHERE email IS NOT NULL

Then run it with dvt run --select stg_customers. DVT creates a view (the default materialization) named stg_customersin your target database. That's the whole loop — everything else on this page is variations on it. More on the run command itself: dvt run.

CONFIGURATION

A {{ config() }} block at the top of the file controls how the model is materialized:

-- models/dim_customers.sql

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

SELECT
    id,
    name,
    email,
    DATE(created_at) as signup_date
FROM {{ ref('stg_customers') }}

{{ config(
    materialized='f_table',            -- + view, table, incremental, ephemeral,
                                       --   f_table, f_incremental (federated)
    target='snowflake_prod',           -- where the result lands (federated models only)
    schema='analytics',                -- override default schema
    tags=['staging', 'daily'],         -- for selection: dvt run --select tag:staging
    pre_hook="DELETE FROM ...",        -- SQL to run before this model
    post_hook="GRANT SELECT ON ...",   -- SQL to run after this model
    unique_key='id',                   -- for incremental: merge key
    incremental_strategy='merge',      -- merge, append, delete+insert
) }}

REFERENCING OTHER MODELS WITH REF()

{{ ref('model_name') }} points at another model. DVT resolves it to the right table name and tracks the dependency in the DAG:

-- models/fct_orders.sql

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

SELECT
    o.order_id,
    o.order_date,
    o.amount,
    c.customer_name,
    c.segment
FROM {{ ref('stg_orders') }} o
JOIN {{ ref('dim_customers') }} c
    ON o.customer_id = c.id

ref() works across languages, in both directions — a SQL model can reference a Python model and vice versa.

One thing to know: if dim_customersabove were a Python model, this SQL model would run as a federated model in DuckDB dialect — dbt's native phase can't see a Python model's output, so DVT coerces the downstream model and warns you. Declare it f_table to make that explicit.

REFERENCING SOURCES WITH SOURCE()

{{ source('source_name', 'table_name') }} reads raw data declared in sources.yml:

-- models/stg_orders.sql

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

SELECT
    id as order_id,
    customer_id,
    CAST(amount AS DECIMAL(10,2)) as amount,
    created_at as order_date
FROM {{ source('crm', 'orders') }}

What happens next depends on the source's connection: property in sources.yml:

TWO SQL DIALECTS

This is the most important idea on this page: one project can carry SQL in two different dialects, and which one a model speaks depends on where it runs.

-- Pushdown to PostgreSQL — use PostgreSQL syntax
{{ config(materialized='table') }}

SELECT
    id,
    name,
    created_at::date as signup_date,          -- PG cast syntax
    COALESCE(email, 'unknown') as email
FROM {{ ref('raw_customers') }}
WHERE created_at > NOW() - INTERVAL '30 days' -- PG interval syntax

-- Federated: Oracle source → DuckDB → PostgreSQL target
{{ config(materialized='f_table', target='pg_docker') }}

SELECT
    m.id,
    m.name,
    m.amount,
    'oracle_to_pg' as federation_path
FROM {{ source('oracle_source', 'orders') }} m   -- lives on Oracle
WHERE m.amount > 20                              -- DuckDB SQL syntax

Rule of thumb: federated models (f_table / f_incremental) are written in DuckDB SQL; standard models are written in the default target's native SQL.

And if you ever move the default target to a different engine, dvt flip-target-to rewrites your standard models into the new dialect for you — the concept is on the switching targets page.

CROSS-ENGINE FEDERATION

DVT's superpower: join data from different databases in a single SQL query.

-- models/federation/cross_engine_report.sql
-- Joins Oracle orders with Oracle inventory — DVT handles everything

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

SELECT
    o.order_id,
    o.product_id,
    o.quantity,
    i.warehouse_location,
    i.stock_level,
    CASE
        WHEN i.stock_level < o.quantity THEN 'backorder'
        ELSE 'in_stock'
    END as fulfillment_status
FROM {{ source('oracle_ops', 'orders') }} o         -- Oracle
JOIN {{ source('oracle_erp', 'inventory') }} i     -- Oracle
    ON o.product_id = i.product_id

Behind the scenes, DVT:

INCREMENTAL MODELS

Incremental models only process new or changed rows — the efficient choice for large, growing tables:

-- models/fct_events.sql

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

SELECT
    event_id,
    user_id,
    event_type,
    event_timestamp,
    properties
FROM {{ source('analytics', 'raw_events') }}

{% if is_incremental() %}
    WHERE event_timestamp > (SELECT MAX(event_timestamp) FROM {{ this }})
{% endif %}

TARGETING DIFFERENT DATABASES

A federated model can send its output to any output in profiles.yml, via the targetconfig. Standard dbt models are refused a non-default target on purpose — dbt would silently ignore it, and DVT won't let that happen quietly:

-- models/federation/clickhouse_to_oracle.sql
-- Reads from Oracle, writes to Oracle

{{
    config(
        materialized='f_table',      -- federated: only f_* models may set target=
        target='oracle_prod',        -- writes to Oracle (from profiles.yml)
    )
}}

SELECT
    id,
    name,
    amount
FROM {{ source('oracle_ops', 'transactions') }}

HOOKS

pre_hook and post_hook run SQL before and after a model — and on federated models they keep dbt semantics in all cases.

A plain string hook runs on the model's effective target: the pinned target= if the model has one, the default target otherwise.

A dict hook with a connection key — the DVT extension — runs on that named connection instead: pre-hooks on source engines before any read, post-hooks on the target after the load and its bookkeeping.

{{ config(
    materialized='f_table',
    target='sf_prod',
    pre_hook=[{"sql": "insert into audit.extract_log values ('{{ this.name }}', systimestamp)", "connection": "eska_oracle"}],
    post_hook="insert into audit.load_log values ('{{ this.name }}', 'landed on {{ target.name }}', current_timestamp())"
) }}

Here the pre-hook writes an Oracle audit row on eska_oracle before the extraction touches it; the post-hook stamps the Snowflake target after the table has landed.

EPHEMERAL MODELS

Ephemeral models never become tables — they're injected as CTEs into whatever references them:

-- models/staging/stg_active_users.sql
{{ config(materialized='ephemeral') }}

SELECT id, name, email
FROM {{ ref('raw_users') }}
WHERE is_active = true

-- models/report.sql
-- The ephemeral model becomes a CTE here:
SELECT * FROM {{ ref('stg_active_users') }}

JINJA TEMPLATING

SQL models support Jinja for dynamic SQL. Three patterns cover most of what you'll reach for:

-- Use variables defined in dbt_project.yml or CLI
SELECT *
FROM {{ ref('orders') }}
WHERE order_date > '{{ var("start_date", "2024-01-01") }}'

SELECT
    id,
    name,
    {% if target.type == 'postgres' %}
        created_at::date as signup_date
    {% elif target.type == 'snowflake' %}
        DATE(created_at) as signup_date
    {% endif %}

{% set categories = ['electronics', 'clothing', 'food'] %}

SELECT
    order_id,
    {% for cat in categories %}
        SUM(CASE WHEN category = '{{ cat }}' THEN amount ELSE 0 END) as {{ cat }}_total
        {% if not loop.last %},{% endif %}
    {% endfor %}
FROM {{ ref('orders') }}
GROUP BY order_id

When a snippet of Jinja earns a name, it becomes a macro — see Macros & Jinja.

COMMON PATTERNS

-- models/staging/stg_orders.sql
-- Clean and standardize raw data

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

SELECT
    id as order_id,
    TRIM(customer_name) as customer_name,
    CAST(amount AS DECIMAL(10,2)) as amount,
    LOWER(status) as status,
    created_at as order_date
FROM {{ source('raw', 'orders') }}
WHERE status != 'cancelled'

-- models/marts/fct_revenue.sql
-- Business logic, joins multiple staging tables

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

SELECT
    o.order_id,
    o.order_date,
    o.amount,
    c.customer_segment,
    p.product_category,
    o.amount * COALESCE(d.discount_rate, 0) as discount_amount,
    o.amount - (o.amount * COALESCE(d.discount_rate, 0)) as net_revenue
FROM {{ ref('stg_orders') }} o
LEFT JOIN {{ ref('dim_customers') }} c ON o.customer_id = c.id
LEFT JOIN {{ ref('dim_products') }} p ON o.product_id = p.id
LEFT JOIN {{ ref('stg_discounts') }} d ON o.discount_code = d.code

RUNNING SQL MODELS

# Run a specific model
dvt run --select stg_customers

# Run all models in a folder
dvt run --select staging.*

# Run a model and all its upstream dependencies
dvt run --select +fct_revenue

# Run a model and everything downstream
dvt run --select stg_customers+

# Run with full refresh (recreate tables from scratch)
dvt run --full-refresh

# Run models with a specific tag
dvt run --select tag:daily

Full selection syntax and flags live on the dvt run page — dvt build runs models, tests, seeds and snapshots together in DAG order.

SQL MODELS VS PYTHON MODELS