building-dbt-modelslisted

# Building dbt Models ## When to use - Creating or refactoring `.sql` models in a dbt project. - Deciding materialization (view / table / incremental / ephemeral). - Structuring layers (staging → intermediate → marts). - Writing incremental models for large, growing tables. - Do NOT use for test authoring (use `testing-dbt-projects`) or run failures (use `debugging-dbt-runs`). ## Workflow ``` - [ ] Place the model in the right layer (staging/intermediate/marts) - [ ] Reference upstream only via ref()/source() — never hard-coded names - [ ] Choose materialization by size and refresh needs - [ ] For incremental, set unique_key + is_incremental() filter - [ ] Add a schema.yml entry with tests ``` 1. **Layer it.** `staging/` = one model per source table, light renaming/typing, materialized as views. `intermediate/` = reusable business logic. `marts/` = final dimensional models consumed by BI, materialized as tables. 2. **Reference correctly.** Use `{{ ref('stg_orders') }}` and `{{ source('shop', 'orders') }}` so dbt builds the DAG and manages environments. Never write raw schema.table. 3. **Pick materialization:** view (cheap, always fresh, small), table (fast reads, rebuilt each run), incremental (large append/update tables), ephemeral (inlined CTE, no object). 4. **Incremental models** process only new/changed rows. ## Patterns **Staging model** — one per source, thin and consistent: ```sql -- models/staging/shop/stg_orders.sql with source as (select