Models — The SQL File as the Atom

A dbt model is a .sql file containing exactly one SELECT statement. That's the whole thing.

Hello World

models/staging/stg_orders.sql:

SELECT
    id        AS order_id,
    user_id   AS customer_id,
    status,
    total     AS order_total,
    created_at AS order_placed_at
FROM {{ source('shopify', 'orders') }}
WHERE NOT is_test_order

That's a complete dbt model. Run dbt run --select stg_orders and dbt:

1. Compiles the SQL — replaces {{ source('shopify', 'orders') }} with the actual table reference (e.g., raw.shopify.orders).
2. Wraps it: CREATE OR REPLACE VIEW analytics_dev.dbt_anuj.stg_orders AS <your select>.
3. Executes against the warehouse.

Done. You now have a view in the warehouse.

The structure of a model

The body of the file can have:

1. A config block (optional) — overrides materialization, schema, tags, etc.
2. CTEs — WITH ... AS ( ... ) for readability.
3. A final SELECT — the model's output. This is what gets materialized.

Full example:

{{ config(materialized='table', tags=['daily']) }}

WITH orders AS (
    SELECT * FROM {{ ref('stg_orders') }}
),

customers AS (
    SELECT * FROM {{ ref('dim_customers') }}
),

joined AS (
    SELECT
        o.order_id,
        o.order_total,
        c.customer_segment,
        c.country
    FROM orders o
    JOIN customers c ON c.customer_id = o.customer_id
)

SELECT * FROM joined

dbt compiles, executes, materializes. The final SELECT is what becomes the table.

The CTE-style convention

You'll see most dbt models written with one CTE per upstream model, then a final CTE for the joined/computed output. It's a community-standard style for readability:

WITH source_a AS (
    SELECT * FROM {{ ref('source_a') }}
),
source_b AS (
    SELECT * FROM {{ ref('source_b') }}
),
joined AS (
    SELECT ... FROM source_a JOIN source_b ...
)
SELECT * FROM joined

You can write the SELECT inline without CTEs and it works fine. But once your model has 3+ sources or any non-trivial logic, the CTE style is much easier to read.

What dbt does for you under the hood

When you run dbt run --select stg_orders, dbt:

1. Compiles — your Jinja gets resolved. {{ source(...) }} becomes the actual table name. {{ ref(...) }} becomes database.schema.<model>.
2. Wraps in DDL — depending on materialization, it generates CREATE OR REPLACE VIEW ..., CREATE TABLE ... AS ..., MERGE INTO ..., etc.
3. Executes — sends the compiled SQL to the warehouse.
4. Logs — records the run in target/run_results.json.

The compiled SQL is visible in target/compiled/<project>/<model>.sql. Open it when debugging — you'll see exactly what hit the warehouse.

Per-model config block

{{ config(
    materialized='incremental',
    unique_key='order_id',
    tags=['hourly', 'finance']
) }}

These overrides apply only to this one model. Common configs:

• materialized — view, table, incremental, ephemeral.
• unique_key — for incremental, the column that identifies a row.
• tags — used for selecting subsets in dbt run --select tag:finance.
• pre_hook / post_hook — SQL to run before/after the model.

Running models

dbt run                                # run all models
dbt run --select stg_orders            # run one model
dbt run --select stg_orders+           # stg_orders and all downstream
dbt run --select +fct_orders           # fct_orders and all upstream
dbt run --select tag:daily             # all models with the 'daily' tag
dbt run --select staging.shopify       # all models in models/staging/shopify/

The + and tag: syntax is dbt's selector language. You'll use it constantly.

What goes wrong

1. Forgetting ref() — hardcoding FROM analytics.stg_orders. The DAG breaks; environment changes don't propagate.
2. Multiple SELECT statements — dbt expects ONE final select. Use CTEs to combine.
3. SELECT * in marts — couples downstream models to source column lists. Fine in staging; risky in marts.
4. Mixing materializations randomly — picking view for one mart, table for another for no reason. Set materializations at the folder level in dbt_project.yml for consistency.

Takeaway

A model is one .sql file, one SELECT, materialized by dbt. The CTE convention makes them readable. The config() block customizes per-model behavior. The --select syntax controls what runs. That's 80% of daily dbt work.