Writing a Model
A dbt model is nothing more than a single .sql file containing one SELECT statement placed inside the models/ directory; dbt takes the filename, minus its extension, as the model's name, and wraps the SELECT in a CREATE statement when it runs. There's no INSERT, UPDATE, or DDL to write by hand — you describe the shape of the data you want as a query, and dbt's materialization logic handles turning that query into an actual object in the warehouse.
Cricket analogy: Writing a model is like a captain declaring a batting order on paper — you describe the desired outcome (the lineup) and let the system (dbt) handle actually sending players out to bat.
-- models/staging/stg_customers.sql
with source as (
select * from {{ source('jaffle_shop', 'customers') }}
)
select
id as customer_id,
first_name,
last_name,
lower(email) as email
from sourceMaterializations
A materialization determines how dbt physically persists a model's SELECT statement in the warehouse: view recreates a lightweight SQL view on every dbt run and always reflects fresh data at query time with no storage cost; table fully rebuilds a physical table on every run, trading longer build time for faster downstream query performance; incremental only inserts or merges new/changed rows into an existing table instead of rebuilding it entirely, which matters enormously for large fact tables; and ephemeral doesn't create a database object at all, instead being inlined as a CTE into whatever model references it. You set the materialization per model with a config block or globally in dbt_project.yml, and picking the wrong one is one of the most common beginner mistakes — for example, using table for a huge, frequently-updated events table instead of incremental.
Cricket analogy: Choosing a materialization is like choosing a format — a T20 (view, quick and fresh every time), a Test match (table, full rebuild taking longer but thorough), or a rain-affected DLS-adjusted match (incremental, only recalculating what changed).
-- models/marts/finance/fct_orders.sql
{{
config(
materialized='incremental',
unique_key='order_id'
)
}}
select
order_id,
customer_id,
order_date,
amount
from {{ ref('stg_orders') }}
{% if is_incremental() %}
where order_date > (select max(order_date) from {{ this }})
{% endif %}Running Your Model and the DAG
Instead of hardcoding another model's schema and table name, you reference it with {{ ref('model_name') }}, and dbt uses every ref() call across the project to build a directed acyclic graph (DAG) that determines the correct build order automatically — a mart model that refs a staging model will always be built after that staging model, without you writing any explicit dependency logic. Running dbt run --select stg_customers builds just that one model, dbt run --select stg_customers+ builds it and everything downstream of it, and a plain dbt run builds every model in the project in DAG order.
Cricket analogy: The ref() function is like a fielding rotation plan that automatically determines who bats next based on the fall of wickets — dbt figures out the correct build order from dependencies rather than a fixed, hardcoded batting list.
dbt run --select stg_customers # build just this one model
dbt run --select stg_customers+ # build it and everything downstream
dbt run # build every model in DAG order
dbt run --select +fct_orders # build fct_orders and everything it depends onAlways use {{ ref('model_name') }} to reference other models instead of hardcoding a database.schema.table string. ref() is what allows dbt to build the dependency DAG, automatically prefix the correct environment's schema, and safely rename or move models without breaking downstream references.
- A dbt model is a single .sql file with one SELECT statement inside the models/ directory.
- The filename (minus .sql) becomes the model's name in the warehouse.
- Materializations (view, table, incremental, ephemeral) control how a model is physically persisted.
- incremental materializations only insert/merge new or changed rows instead of a full rebuild.
- {{ ref('model_name') }} references another model and lets dbt build the dependency DAG automatically.
- dbt run --select supports building specific models and their upstream/downstream dependencies with + syntax.
- Never hardcode schema.table references — always use ref() so dbt can track dependencies correctly.
Practice what you learned
1. What determines the name of a dbt model in the warehouse?
2. Which materialization only inserts or merges new/changed rows instead of rebuilding the whole table?
3. Why should you use {{ ref('model_name') }} instead of hardcoding a schema.table string?
4. What does dbt run --select stg_customers+ do?
Was this page helpful?
You May Also Like
The dbt Project Structure
A tour of a dbt project's folders and configuration files, and the staging/intermediate/marts convention used to organize models.
What Is dbt?
An introduction to dbt (data build tool), the SQL-first framework that lets analysts and engineers transform data already loaded in a warehouse using version-controlled, testable code.
Connecting to a Warehouse
How dbt uses adapters and profiles.yml to authenticate against a data warehouse, and how to verify the connection with dbt debug.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
ProgrammingPowerShell Study Notes
Programming · 30 topics