In dbt, what is a model, and what does a model's .sql file actually contain?

## What a model is A dbt **model** is a file of SQL that lives under the `models/` directory of a dbt project and contains exactly one `SELECT` statement. dbt does not execute that file as written. When you run `dbt run` (or `dbt build`), dbt renders the Jinja templating in the file, wraps the resulting query in the DDL appropriate to the model's *materialization*, and executes that statement against the warehouse connection defined by your profile. The file you author describes **what the result set is**; dbt decides **how it is persisted**. That split is the whole idea. Analysts write the part they are good at — a query — and the boilerplate that differs per warehouse and per environment (create-or-replace semantics, transactions, temp tables, schema names) is generated for them and is identical across every model in the project. ## One file, one SELECT, one relation The conventions are strict and worth stating precisely: - **One model per file.** A model file holds a single `SELECT`. It is not a script: you cannot put two statements in it, and DML such as `INSERT`, `UPDATE` or `DELETE` does not belong there. Work that genuinely needs extra statements goes into `pre_hook`/`post_hook` config or a separate operation. - **The filename is the model name.** `models/marts/fct_orders.sql` is the model `fct_orders`. That name is what `ref('fct_orders')` resolves against, and by default it is also the identifier of the relation dbt creates in the warehouse. The `alias` config overrides the relation name while leaving the model name — and therefore every `ref()` to it — unchanged. - **Model names must be unique project-wide.** Two files called `orders.sql` in different folders is an error, because `ref()` selects by name and dbt would not know which one you meant. - **Folders are organisation, not namespacing.** A common layout is `models/staging/`, `models/intermediate/` and `models/marts/`. Paths matter because `dbt_project.yml` can apply configuration by directory (for example, everything under `staging` materialized as a view), and because selection syntax can target a path — but they never form part of the model's name. ## You never write the DDL The materialization decides the wrapper. A model configured as a view becomes roughly `create or replace view <db>.<schema>.<name> as (<your select>)`; a table becomes a `create table as select`; an incremental model becomes a create on first run and a merge or insert on later runs. You change the strategy by changing one config value, not by rewriting the SQL: ```sql {{ config(materialized='table') }} select o.order_id, o.customer_id, sum(l.amount) as order_total from {{ ref('stg_orders') }} o join {{ ref('stg_order_lines') }} l using (order_id) group by 1, 2 ``` Everything outside the `select` in that file is Jinja: a `config()` call that sets options, and `ref()` calls that both name upstream models and declare dependencies on them. ## Where the model lands The relation dbt builds is addressed as `database.schema.identifier`. The database and schema default to the ones in the active target of your `profiles.yml`, which is why the same project builds into a personal dev schema for you and into the production schema on the scheduled run. A `schema` config on a model is, by default, *appended* to the target schema rather than replacing it, so a model configured with `schema='marts'` running against a target schema of `dbt_prod` lands in `dbt_prod_marts`. That behaviour is produced by a macro the project can override. ## What is not a model Several other resource types live in a dbt project and are frequently confused with models: **seeds** are CSV files loaded as small static tables, **snapshots** capture history of a changing source, **tests** are assertions, **macros** are reusable Jinja, and **analyses** are compiled but never built. Only files under the model paths become relations from a `SELECT`. ## Why interviewers ask this It is the screening question for the tool. A candidate who describes a model as "a table I create with DDL" has not used dbt; a candidate who says "a `SELECT` that dbt materialises for me, named after the file, with dependencies declared through `ref()`" has. It also sets up everything else: build order, environments, testing and documentation all hang off the fact that a model is a named query dbt owns end to end.