Models: SELECT Statements as the Building Block

A dbt Model Is One File Containing One SELECT Statement

Strip away everything dbt does around scheduling, testing, and documentation, and the core primitive is almost anticlimactically simple: a model is a single .sql file, living somewhere under your project's models/ directory, containing exactly one SELECT statement. That's it. No CREATE TABLE, no CREATE VIEW, noDROP, no DDL of any kind. You write the query that describes the transformation you want, and dbt handles everything about turning that query into an actual object in your warehouse.

-- models/staging/stg_orders.sql
select
    order_id,
    customer_id,
    order_status,
    order_placed_at
from raw.orders

That five-line file is a fully functional dbt model. When you rundbt run, dbt reads this file, wraps yourSELECT statement in whatever DDL is appropriate — typically something like CREATE OR REPLACE VIEW dbt_asil.stg_orders AS (select ... ) — and executes that wrapped statement against your warehouse. You never write the wrapping DDL yourself. You are only ever responsible for theSELECT.

This is a genuinely different mental model from writing raw SQL scripts. In a plain SQL script you'd writeCREATE OR REPLACE TABLE analytics.orders AS SELECT ...yourself, decide the object's exact name yourself, and run scripts in whatever order you remembered was correct. A dbt model removes all three of those manual steps — the naming, the DDL, and the run order — and replaces them with configuration and file location, which is what the rest of this module covers.

The Filename Becomes the Object Name — There Is No Separate Naming Step

A model has no explicit "name this object X" setting you fill in by default. The filename itself — minus the .sqlextension — is the name dbt gives the resulting view or table in your warehouse. A file called stg_orders.sql becomes an object named stg_orders. There is no separate naming step to forget, and also no way to give a model a display name that differs from its filename without an explicitalias config override.

models/staging/stg_orders.sql        →  stg_orders   (view, by default)
models/staging/stg_customers.sql     →  stg_customers
models/marts/fct_orders.sql          →  fct_orders
models/marts/dim_customers.sql       →  dim_customers

This has a real practical consequence: renaming a model file renames the object dbt creates. If you renamestg_orders.sql to stg_orders_v2.sql, dbt will build a brand-new object called stg_orders_v2the next time it runs — it does not rename the oldstg_orders object in place, and the old object is left behind in your warehouse until something explicitly drops it. Renaming a model file is therefore not a purely cosmetic change; it is effectively creating a new table and orphaning the old one.

It is possible to override the default filename-based name using an alias config, if you genuinely need the warehouse object name to differ from the model's filename (a common reason: migrating an existing table's name without renaming the model file that many other models already ref() against). But this is the exception, not the default behavior, and should be used deliberately rather than as a routine habit.

An Unconfigured Model Compiles to a View — Not a Table

This is one of the most important defaults to know cold, because it silently shapes both cost and behavior if you're not aware of it: a bare dbt model, with no config() block at all, materializes as a view. Not a table. A view.

select
    order_id,
    customer_id,
    order_status,
    order_placed_at
from raw.orders

Running dbt run against this exact file compiles to something close to this behind the scenes:

create or replace view dbt_asil.stg_orders as (
    select
        order_id,
        customer_id,
        order_status,
        order_placed_at
    from raw.orders
);

A view stores no data of its own — it's a saved query definition that the warehouse re-executes against the underlyingraw.orders table every single time something queriesstg_orders. This has real consequences: a view is cheap to create (no data is copied anywhere) and always reflects the current state of its source data with zero staleness, but every query against it re-runs the full underlying transformation, which gets expensive if the transformation is complex or the underlying table is large and the view gets queried frequently.

Materialization is not a property of the SQL itself — the exact same SELECT statement can be materialized as a view today and a table tomorrow with no change to the query at all, purely by changing configuration. This is the direct payoff of Part 01's "models are just a SELECT" constraint: because dbt owns the wrapping DDL, it can swap that DDL out entirely based on config, without you touching your transformation logic.

The config() Block — Per-Model Settings, Inline With the SQL

Module 03 showed how dbt_project.yml sets default materializations per directory. But sometimes one specific model needs to override that default — a single heavy mart model that should be a table even though its sibling models default to views, for instance. The {{ config(...) }} Jinja block, placed at the very top of a model file, is how you do that — and whatever it sets always wins over the directory-level default in dbt_project.yml.

-- models/marts/fct_orders.sql
{{
  config(
    materialized='table',
    tags=['finance', 'daily']
  )
}}

select
    o.order_id,
    o.customer_id,
    c.customer_name,
    o.order_status,
    o.order_total_cents,
    o.order_placed_at
from {{ ref('stg_orders') }} o
left join {{ ref('stg_customers') }} c
    on o.customer_id = c.customer_id

Everything inside config() is Jinja, evaluated at compile time, before the query is ever sent to the warehouse. Thematerialized key is the one you'll use constantly;tags is useful for selectively running subsets of your project later (dbt run --select tag:finance), and there are many other config keys —unique_key for incremental models,schema to override where a specific model lands,enabled to disable a model without deleting the file — that later modules in this track cover as they become relevant.

staging, intermediate, and marts — A First Look

Once a project has more than a handful of models, dropping them all flat into models/ stops working — you lose any sense of which models are raw cleanup versus final, dashboard- ready output. The convention nearly every dbt project converges on, in some form, is three layers: staging,intermediate, and marts. This module introduces the idea at a beginner level; a later module in this track, on project structure, goes much deeper into naming conventions, cross-layer rules, and when to add more layers than just these three.

models/
├── staging/
│   ├── stg_orders.sql
│   └── stg_customers.sql
├── intermediate/
│   └── int_orders_with_customer_region.sql
└── marts/
    ├── fct_orders.sql
    └── dim_customers.sql

The rough flow of data through these layers is: raw source tables get cleaned up individually in staging, those staging models get combined and enriched inintermediate, and the result gets shaped into final, business-facing tables in marts. Not every project needs an intermediate layer for every mart — a simple mart that only needs one or two staging models joined together often skips straight from staging to marts. The layer exists for when that join logic itself gets complex enough to deserve its own named, testable model rather than being buried inside a much larger mart query.

What Makes a Good Model — One Clear Transformation Step

Because dbt places no technical limit on how complex a single model's SELECT statement can be, it is entirely possible to write one enormous model that reads from six raw source tables, joins them all together, computes a dozen aggregations, and applies business logic — all in one file. dbt will run it without complaint. It is also almost always a mistake.

A good model does one clear transformation step — cleaning one source, joining a small number of closely related staging models, or computing one well-defined business aggregation — rather than trying to be the entire pipeline in a single file. The reason this matters goes beyond readability, though that matters too:

• ✓Testability — a small, single-purpose model is easy to write a meaningful test against (are order totals always non-negative?). A model doing six things at once makes it unclear which of those six things a failing test is even about.
• ✓Reusability — a staging model cleaning one raw table can be ref()'d by many downstream models. A giant do-everything query that mixes cleanup and final business logic in one step usually can't be reused by anything else, so similar logic gets duplicated elsewhere instead.
• ✓Debuggability — when something in a giant model is wrong, you have to mentally untangle which of its many joins or aggregations produced the bad row. A chain of small, named models lets you query each intermediate step directly to find exactly where the data went wrong.
• ✓Compile and query performance — a warehouse optimizer generally handles a chain of smaller, well-defined views and tables more predictably than one enormous, deeply nested query trying to do everything at once.

-- DO NOT model your project this way
select
    o.order_id,
    c.customer_name,
    c.customer_region,
    p.product_name,
    sum(oi.quantity * oi.unit_price_cents) as line_total_cents,
    case when c.customer_region = 'US' then 'domestic' else 'international' end as shipping_class,
    rank() over (partition by c.customer_region order by sum(oi.quantity * oi.unit_price_cents) desc) as region_rank
from raw.orders o
join raw.customers c on o.customer_id = c.customer_id
join raw.order_items oi on o.order_id = oi.order_id
join raw.products p on oi.product_id = p.product_id
where o.order_status != 'cancelled'
group by 1, 2, 3, 4, 6

-- models/staging/stg_orders.sql — clean one raw source, nothing else
select order_id, customer_id, order_status
from raw.orders
where order_status != 'cancelled'

-- models/staging/stg_order_items.sql — clean one raw source, nothing else
select order_id, product_id, quantity, unit_price_cents
from raw.order_items

-- models/intermediate/int_orders_with_line_totals.sql — one clear join + aggregation step
select
    o.order_id,
    o.customer_id,
    sum(oi.quantity * oi.unit_price_cents) as line_total_cents
from {{ ref('stg_orders') }} o
join {{ ref('stg_order_items') }} oi on o.order_id = oi.order_id
group by 1, 2

-- models/marts/fct_orders.sql — final business-facing shape
select
    i.order_id,
    c.customer_name,
    c.customer_region,
    i.line_total_cents,
    case when c.customer_region = 'US' then 'domestic' else 'international' end as shipping_class
from {{ ref('int_orders_with_line_totals') }} i
join {{ ref('stg_customers') }} c on i.customer_id = c.customer_id

Each of the four smaller models in that second version can be tested independently, reused by other downstream models, and debugged in isolation — if fct_orders shows a wrongline_total_cents, you can queryint_orders_with_line_totals directly to check whether the problem is in the join/aggregation step or further downstream, instead of untangling one 20-line query end to end.

Building stg_orders.sql From a Real Raw Table

Here is a complete, realistic staging model, built step by step, showing the kind of light cleanup staging models typically do: renaming cryptic columns, casting types explicitly, and filtering out rows that shouldn't exist in a clean dataset — without adding any business logic or joins, which belong in later layers per Part 05.

Imagine the raw, unmodified source table looks like this — messy column names, an inconsistent status field, and a couple of test rows accidentally left in from a QA process:

order_id | cust_id | ord_status | ord_ts               | is_test_row
---------|---------|------------|----------------------|------------
1001     | 501     | COMPLETE   | 2026-08-01 14:22:03  | false
1002     | 502     | complete   | 2026-08-01 15:03:41  | false
1003     | 501     | CANCELLED  | 2026-08-02 09:11:57  | false
1004     | 999     | complete   | 2026-08-02 10:00:00  | true
1005     | 503     | pending    | 2026-08-03 08:45:12  | false

A staging model built on top of this should rename the cryptic columns to something self-explanatory, normalize the inconsistent casing on ord_status, cast the timestamp to an explicit type, and filter out the QA test rows — all cleanup, no business logic yet.

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

with source as (

    select * from {{ source('freshcart', 'orders') }}

),

renamed as (

    select
        order_id,
        cust_id                          as customer_id,
        lower(ord_status)                as order_status,
        cast(ord_ts as timestamp)         as order_placed_at

    from source
    where is_test_row = false

)

select * from renamed

Two things in that file are worth flagging even though they aren't this module's main focus. First, the{{ config(materialized='view') }} line is actually redundant here — Part 03 established that view is already the default, so this line is included only to make the materialization explicit and self-documenting, not because it changes anything. Second, the{{ source('freshcart', 'orders') }} function call is how this model actually points at the rawraw.orders table, rather than hardcoding a schema and table name directly.

Running dbt run --select stg_orders against this file compiles it and executes it against the warehouse:

$ dbt run --select stg_orders

Running with dbt=1.8.3
Found 12 models, 8 tests, 1 source, 0 exposures, 0 metrics

Concurrency: 4 threads (target='dev')

1 of 1 START sql view model dbt_asil.stg_orders .......... [RUN]
1 of 1 OK created sql view model dbt_asil.stg_orders ..... [SUCCESS 1 in 1.11s]

Finished running 1 view model in 0 hours 0 minutes and 1.34 seconds (1.34s).

Completed successfully

Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

Querying the resulting view confirms the cleanup worked — casing normalized, columns renamed, the QA test row filtered out:

order_id | customer_id | order_status | order_placed_at
---------|-------------|--------------|--------------------
1001     | 501         | complete     | 2026-08-01 14:22:03
1002     | 502         | complete     | 2026-08-01 15:03:41
1003     | 501         | cancelled    | 2026-08-02 09:11:57
1005     | 503         | pending      | 2026-08-03 08:45:12

(4 rows — order_id 1004 was correctly excluded as a test row)

Every downstream model in this project — the intermediate join in Part 06's example, the final fct_orders mart — now builds on top of this clean, renamed, correctly-typedstg_orders view instead of ever touchingraw.orders directly. That single-responsibility staging layer is what makes the rest of the project's models simpler to write and easier to trust.

dbt compile — Seeing Exactly What Your Model Turns Into

Everything in this module so far has described what a model file looks like before dbt touches it — the Jinja, theref() and source() calls, theconfig() block. It's worth seeing the other side: the plain SQL dbt actually produces after resolving all of that, because that compiled SQL is what genuinely executes against your warehouse, and reading it is often the fastest way to understand why a model is behaving unexpectedly.

dbt compile resolves every model's Jinja — everyref(), every source(), every macro call — into plain SQL, and writes the result to files undertarget/compiled/, without executing anything against the warehouse at all. dbt run does that same compilation step internally and then actually executes the result. Compiling without running is how you check your work before spending any warehouse compute on it.

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

with source as (
    select * from {{ source('freshcart', 'orders') }}
),
renamed as (
    select
        order_id,
        cust_id                          as customer_id,
        lower(ord_status)                as order_status,
        cast(ord_ts as timestamp)         as order_placed_at
    from source
    where is_test_row = false
)
select * from renamed

with source as (
    select * from FRESHCART_DEV.raw.orders
),
renamed as (
    select
        order_id,
        cust_id                          as customer_id,
        lower(ord_status)                as order_status,
        cast(ord_ts as timestamp)         as order_placed_at
    from source
    where is_test_row = false
)
select * from renamed

The {{ config(...) }} block disappears entirely in the compiled output — it was only ever an instruction to dbt about how to wrap the query, not part of the query itself. The{{ source('freshcart', 'orders') }} call resolves to a fully qualified table reference,FRESHCART_DEV.raw.orders, using whichever database the active profile target points at. This is exactly why the same model file can be run against dev orprod (Module 03, Part 08) and correctly resolve to each environment's own database without a single line of the model changing.

Ephemeral Models — When a Model Shouldn't Exist in the Warehouse at All

Part 03's table compared view, table, incremental, and one more option worth a closer look here: ephemeral. An ephemeral model is the odd one out — it is the only materialization that creates nothing at all in your warehouse. Instead, dbt inlines its compiled SQL as a Common Table Expression (CTE) directly into every model that references it.

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

select
    order_id,
    lower(trim(ord_status)) as order_status
from {{ source('freshcart', 'orders') }}

When another model references this one with{{ ref('stg_order_statuses_normalized') }}, dbt does not generate a query that selects from a real database object named stg_order_statuses_normalized — because no such object ever gets created. Instead it inlines the ephemeral model's own compiled SQL as a CTE at the top of whatever model referenced it.

-- models/marts/fct_orders.sql, referencing the ephemeral model above
select
    order_id,
    order_status
from {{ ref('stg_order_statuses_normalized') }}
where order_status != 'cancelled'

-- compiles to (note: no real stg_order_statuses_normalized object exists):
with __dbt__cte__stg_order_statuses_normalized as (
    select
        order_id,
        lower(trim(ord_status)) as order_status
    from FRESHCART_DEV.raw.orders
)
select
    order_id,
    order_status
from __dbt__cte__stg_order_statuses_normalized
where order_status != 'cancelled'

A reasonable rule of thumb: reach for ephemeral when a transformation step is genuinely thin (a rename, a light filter, a single case expression) and used by only one or two downstream models — exactly the kind of step that doesn't feel like it deserves its own permanent object in the warehouse. Once a model is referenced by many downstream models or does anything computationally heavier, view or tableare almost always the better choice, since they compute the result once rather than recompiling it into every referencing query.

Before You Commit a New Model, Check These Five Things

Everything in this module compresses into a short, practical checklist worth running through on every new model before opening a pull request, especially while the conventions are still new. None of these take more than a minute to verify, and together they catch the majority of first-pass mistakes covered across the earlier Parts.

1. Does the filename match what the object should be called?
   (Part 02 — there is no separate naming step; the filename IS the name)

2. Is the materialization appropriate, or did I leave an expensive
   model on the unconfigured view default?
   (Part 03 — check whether this model is cheap enough to leave
   unconfigured, or genuinely needs an explicit config() override)

3. Does this model do exactly one clear transformation step?
   (Part 06 — if the description needs "and" more than once,
   it's probably two models pretending to be one)

4. Is it in the right layer — staging, intermediate, or marts?
   (Part 05 — staging cleans one source; intermediate joins;
   marts finalize. A join inside a staging model is a signal
   something is misplaced)

5. Did I run dbt compile to check the actual SQL before running
   the full model against the warehouse?
   (Part 08 — catches a broken ref()/source() or Jinja typo for free,
   before spending any real warehouse compute on it)

None of these checks require deep dbt expertise — they're mechanical, and that's the point. A staging model with a join in it, a mart left as an unconfigured view, a filename that doesn't match its intended object name — all five are easy to catch in a thirty-second self-review, and all five are exactly the kind of mistake that's much more expensive to unwind once several other models have started ref()-ing the broken one.

Five Misconceptions About dbt Models

What This Looks Like on Day One

5 Interview Questions — With Complete Answers

The Modeling Mistakes That Cost the Most Later

Errors You Will Hit — And Exactly Why They Happen