dbt-develop

# dbt Model Development ## Requirements **Agent:** builder or migrator (requires file write access) **Tools used:** bash (runs `altimate-dbt` commands), read, glob, write, edit, schema_search, dbt_profiles, sql_analyze, altimate_core_validate, altimate_core_column_lineage ## When to Use This Skill **Use when the user wants to:** - Create a new dbt model (staging, intermediate, mart, OBT) - Add or modify SQL logic in an existing model - Generate sources.yml or schema.yml from warehouse metadata - Reorganize models into layers (staging/intermediate/mart or bronze/silver/gold) - Convert a model to incremental materialization - Scaffold a new dbt project structure **Do NOT use for:** - Adding tests to models → use `dbt-test` - Writing model/column descriptions → use `dbt-docs` - Debugging build failures → use `dbt-troubleshoot` - Analyzing change impact → use `dbt-analyze` ## Core Workflow: Plan → Discover → Write → Validate ### 1. Plan — Understand Before Writing Before writing any SQL: - Read the task requirements carefully - **Enumerate every concrete deliverable the task asks for** — write down each model name, every column/test/config change mentioned, and any "create N models" count. This list becomes the checklist you verify against in step 4. A task asking for four models is not done if only three exist on disk. If the task references a `schema.yml`, `_models.yml`, or similar spec file, every entry there is a deliverable. - Identify which layer this model belongs to (staging, intermediate, mart) - Check existing models for naming conventions and patterns - **Check dependencies:** If `packages.yml` exists, check for `dbt_packages/` or `package-lock.yml`. Only run `dbt deps` if packages are declared but not yet installed. ```bash altimate-dbt info # project name, adapter type altimate-dbt parents --model <upstream> # understand what feeds this model altimate-dbt children --model <downstream> # understand what consumes it ``` **Check warehouse connection:** Run `dbt_profiles` to discover available profiles and map them to warehouse connections. This tells you which adapter (Snowflake, BigQuery, Postgres, etc.) and target the project uses — essential for dialect-aware SQL. ### 2. Discover — Understand the Data Before Writing **Never write SQL without deeply understanding your data first.** The #1 cause of wrong results is writing SQL blind — assuming grain, relationships, column names, or values without checking. **Step 2a: Search for relevant tables and columns** - Use `schema_search` with natural-language queries to find tables/columns in large warehouses (e.g., `schema_search(query: "customer orders")` returns matching tables and columns from the indexed schema cache) - Read `sources.yml`, `schema.yml`, and any YAML files that describe the source/parent models - These contain column descriptions, data types, tests, and business context - Pay special attention to: primary keys, unique constraints, relationships between tables, and what each column represents **Step 2b: Understand the grain of each parent model/source** - What does one row represent? (one customer? one event? one day per customer?) - What are the primary/unique keys? - This is critical for JOINs — joining on the wrong grain causes fan-out (too many rows) or missing rows ```bash altimate-dbt columns --model <name> # existing model columns altimate-dbt columns-source --source <src> --table <tbl> # source table columns altimate-dbt execute --query "SELECT count(*) FROM {{ ref('model') }}" --limit 1 altimate-dbt execute --query "SELECT * FROM {{ ref('model') }}" --limit 5 altimate-dbt column-values --model <name> --column <col> # sample values for key columns ``` **Step 2c: Query the actual data to verify your understanding** - Check row counts, NULLs, date ranges, cardinality of key columns - Verify foreign key relationships actually hold (do all IDs in child exist in parent?) - Check for duplicates in what you think are unique keys **Step 2d: Read existing models that your new model will reference** - Read the actual SQL of parent models — understand their logic, filters, and transformations - Read 2-3 existing models in the same directory to match patterns and conventions ```bash glob models/**/*.sql # find all model files read <model_file> # understand existing patterns and logic ``` ### 3. Write — Follow Layer Patterns See [references/layer-patterns.md](references/layer-patterns.md) for staging/intermediate/mart templates. See [references/medallion-architecture.md](references/medallion-architecture.md) for bronze/silver/gold patterns. See [references/incremental-strategies.md](references/incremental-strategies.md) for incremental materialization. See [references/yaml-generation.md](references/yaml-generation.md) for sources.yml and schema.yml. ### 4. Validate — Build, Verify, Check Impact Never stop at writing the SQL. Always validate: **Build it:** ```bash altimate-dbt compile --model <name> # catch Jinja errors altimate-dbt build --model <name> # materialize + run tests ``` **Verify transformation correctness with unit tests:** For models with non-trivial transformation logic — aggregations, JOINs, CASE/WHEN, window functions, ratio / rate / NPS calculations, COALESCE / NULL coalescing, date spines, incremental merge keys — generate and run dbt unit tests before declaring the model done. Schema checks ("table exists with the right columns") only verify mechanics; value-level correctness needs unit tests. Invoke the **dbt-unit-tests** skill, which will: - Analyze your SQL for the constructs above - Build typed mock input rows from the manifest - Compute expected outputs by running the SQL against the mocks - Write a `unit_tests:` block in the model's `_models.yml` Then run them: ```bash altimate-dbt test --model <name> # runs unit tests + schema tests ``` If a unit test fails, the transformation logic is wrong — **fix the SQL, do not weaken the test**. Skip unit tests only for genuinely trivial models: pure renames, simple `SELECT *` passthrough, materialization / config-only changes, format-only edits. **Verify every requested deliverable exists:** Walk the checklist you wrote in the Plan step. For each model the task asked for, confirm: (1) the `.sql` file exists in the project, (2) it appears in `altimate-dbt info` / the manifest, (3) `altimate-dbt columns --model <name>` returns the expected columns, (4) the materialization config matches the spec. A task that asked for N models is not complete with N-1 files on disk, even if those N-1 build cleanly. Use: ```bash ls models/ # confirm every requested file exists altimate-dbt info # confirm every requested model is in the project ``` **Diff column shape against the spec — use the `dbt-schema-verify` skill.** For each model the task touched, run `altimate-dbt schema-verify --model <name>` and treat any `mismatch` verdict as "not done." Full procedure, output interpretation, and fallback (when `altimate-dbt` is missing) live in the dedicated **dbt-schema-verify** skill, which auto-loads alongside this one. **Verify the output:** ```bash altimate-dbt columns --model <name> # confirm expected columns exist altimate-dbt execute --query "SELECT count(*) FROM {{ ref('<name>') }}" --limit 1 altimate-dbt execute --query "SELECT * FROM {{ ref('<name>') }}" --limit 10 # spot-check values ``` - Do the columns match what schema.yml or the task expects? - Does the row count make sense? (no fan-out from bad joins, no missing rows from wrong filters) - Are values correct? (spot-check NULLs, aggregations, date ranges) **Check SQL quality** (on the compiled SQL from `altimate-dbt compile`): - `sql_analyze` — catches anti-patterns (SELECT *, cartesian products, missing filters) - `altimate_core_validate` — validates syntax and schema references - `altimate_core_column_lineage` — traces how source columns flow to output columns. Use this to verify your SELECT is pulling the right columns from the right sources, especially for complex JOINs or multi-CTE models. **Check downstream impact** (when modifying an existing model): ```bash altimate-dbt children --model <name> # who depends on this? altimate-dbt build --model <name> --downstream # rebuild downstream to catch breakage ``` Use `altimate-dbt children` and `altimate-dbt parents` to verify the DAG is intact when changes could affect downstream models. ## Iron Rules 1. **Never write SQL without reading the source columns first.** Use `altimate-dbt columns` or `altimate-dbt columns-source`. 2. **Never stop at compile.** Always `altimate-dbt build` to catch runtime errors. 3. **Match existing patterns.** Read 2-3 existing models in the same directory before writing. 4. **One model, one purpose.** A staging model should not contain business logic. An intermediate model should not be materialized as a table unless it has consumers. 5. **Fix ALL errors, not just yours.** After creating/modifying models, run a full `dbt build`. If ANY model fails — even pre-existing ones you didn't touch — fix them. Your job is to leave the project in a fully working state. 6. **Verify transformation correctness, not just mechanics.** For non-trivial models, generate and run dbt unit tests as part of the validate step (use the `dbt-unit-tests` skill). Passing `dbt build` only proves the SQL is syntactically valid — it doesn't prove the *values* are right. 7. **Enumerate deliverables, then check them off.** The task is not done until every model, column, test, and config change explicitly requested exists on disk and in the manifest. Re-read the prompt at the end and verify each requested item — don't trust your own intermediate "done" feeling. 8. **Match the column spec exactly — and verify it mechanically, not by inspection.** Use the dedicated **dbt-schema-verify** skill. Before declaring any model task done, run `altimate-dbt schema-verify --model <name>` and treat any `mismatch` verdict as "not done." Adding "helpful" extras (rank breakdowns, name-resolved fields, lineage metadata), reordering columns "more logically", or substituting synonyms (`supplier_id` for `supplier_company`, `transaction_type_name` for `transaction_type`) all break equality tests. The contract is what the spec says, not what you think would be useful. ## Common Pitfalls in Transformation Logic When the model involves any of the following SQL constructs, watch for these generic bugs that mostly compile cleanly but produce wrong values: ### Incremental models and snapshots - **High-water mark boundary**: in the `{% if is_incremental() %}` filter, use `>=` (not `>`) when the upstream timestamp can repeat or land exactly on the prior max — a strict `>` silently drops every event that ties with the most recent prior load. - **`unique_key` choice**: must be the *natural* unique key of the row. Picking a column that is not actually unique (e.g. a foreign-key like `customer_id` instead of `order_id`) causes silent merges and lost rows. - **`on_schema_change`**: set `append_new_columns` (or `sync_all_columns` if upstream evolves) so a new source column doesn't NULL-out existing data. - **Snapshots — strategy selection**: use `strategy='timestamp'` only when the source has a reliable `updated_at` that monotonically increases on every change. If `updated_at` can be NULL, be reset, or move backwards, switch to `strategy='check'` with an explicit `check_cols` list. Verify by querying the source for `MAX(updated_at)` and looking for repeats or NULLs. - **Backfilling**: `--full-refresh` rebuilds incremental tables from scratch. Use it whenever you change the incremental SQL, the merge key, or `on_schema_change`. ### Date and time arithmetic - **"current age", "days since", "elapsed", "tenure"** — if the column is not pre-computed in the source, compute it. For year-based age, account for month/day so the change happens on the birthday, not on Jan 1: ```sql date_part('year', age(birth_date)) -- in postgres-family EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM birth_date) - CASE WHEN (EXTRACT(MONTH FROM CURRENT_DATE), EXTRACT(DAY FROM CURRENT_DATE)) < (EXTRACT(MONTH FROM birth_date), EXTRACT(DAY FROM birth_date)) THEN 1 ELSE 0 END -- portable form ``` - **Date spines**: when a daily/weekly/monthly model must have a row for every period (even periods with zero events), build a spine first with `dbt_utils.date_spine` or a recursive CTE, then LEFT JOIN the events onto it. Never compute date series by `DISTINCT date_col FROM events` — that silently drops empty periods. - **Date boundaries for windowed sums**: rolling-N-day windows expressed as `BETWEEN d - (N-1) AND d` (inclusive both ends) give a width of exactly N. `BETWEEN d - N AND d` gives N+1 — a classic off-by-one. ### Type harmonization in `COALESCE` / `CASE` / `UNION` `COALESCE(timestamp_col, integer_col)` and `CASE WHEN ... THEN '0' ELSE 0 END` fail at compile or coerce silently to whatever type the engine guesses. Cast every branch / argument to the same explicit type: ```sql COALESCE(CAST(timestamp_col AS TIMESTAMP), CAST(integer_col AS TIMESTAMP)) CASE WHEN cond THEN CAST('0' AS NUMERIC) ELSE CAST(0 AS NUMERIC) END ``` Same applies to `UNION` / `UNION ALL` — column types must match across legs. ### String concatenation with `NULL` operands `||` and `CONCAT()` propagate `NULL` in most engines — a single `NULL` operand makes the whole expression `NULL`. When the result feeds an equality join or surrogate-key generation, that's an invisible row-dropper: ```sql -- Wrong: NULL region OR NULL segment produces NULL geo_segment region || '-' || segment AS geo_segment -- Right: explicit placeholder COALESCE(region, 'UNKNOWN') || '-' || COALESCE(segment, 'UNKNOWN') AS geo_segment ``` Use `CONCAT_WS()` if your dialect supports it (Snowflake, BigQuery) — it skips `NULL` operands instead of propagating them, which is usually safer than a static placeholder. ### dbt model versioning (dbt 1.8+) When the task asks for a v2 of an existing model (and v1 must keep working — common during a rolling schema change), use dbt's **versioned models** feature, not a sibling `.sql` file with a `_v2` suffix: 1. Create the new SQL file (e.g. `dim_accounts_v2.sql`). 2. Add a `versions:` block to the model's entry in `_models.yml`: ```yaml models: - name: dim_accounts latest_version: 1 versions: - v: 1 - v: 2 defined_in: dim_accounts_v2 # filename without .sql ``` 3. Downstream callers reference the version with `{{ ref('dim_accounts', v=2) }}`. Without the `versions:` block, dbt treats `dim_accounts_v2` as an unrelated sibling model — versioning tests will fail and v1↔v2 lineage won't appear in the DAG. ### Refactoring a CTE into its own model — preserve row-count semantics When a task asks to extract a CTE from a larger model into its own intermediate model, the new model's row count must match what the CTE produced inside the original. Common bug: the CTE was on the parent side of a `LEFT JOIN` that preserved parent rows with no children; the agent's extracted model starts `FROM child_table` and joins back to the parent, silently dropping parents that have no children. **Rule of thumb:** the extracted model should start `FROM` the same table the CTE started from. Build the extracted model inside-out from the parent's perspective, not the child's. ```sql -- Original CTE (inside the larger model): -- WITH agg_users AS ( -- SELECT p.project_id, listagg(u.user_id) AS users -- FROM projects p -- LEFT JOIN project_users u ON u.project_id = p.project_id -- GROUP BY p.project_id -- ) -- -- Right refactor — preserves projects with no users: SELECT p.project_id, listagg(u.user_id) AS users FROM {{ ref('projects') }} p LEFT JOIN {{ ref('project_users') }} u ON u.project_id = p.project_id GROUP BY p.project_id -- Wrong refactor — drops projects with no users: SELECT u.project_id, listagg(u.user_id) AS users FROM {{ ref('project_users') }} u GROUP BY u.project_id -- projects with zero users vanish ``` **Verification** (in order of preference): ```sql -- If dbt_utils is installed, add to schema.yml on the extracted model: tests: - dbt_utils.equal_rowcount: compare_model: ref('<parent_table>') -- If dbt-audit-helper is installed: {{ audit_helper.compare_relations( a_relation=ref('<original_or_parent>'), b_relation=ref('<extracted>'), primary_key='<key>' ) }} -- Manual fallback — always available: SELECT (SELECT COUNT(*) FROM {{ ref('<parent>') }}) AS parent_rows, (SELECT COUNT(*) FROM {{ ref('<extracted>') }}) AS extracted_rows -- These must match if the original CTE was LEFT-joined to its parent. ``` If `extracted_rows < parent_rows`, the refactor is wrong — you've turned a LEFT JOIN into an INNER JOIN somewhere. Same trap shows up when filtering a right-side column in `WHERE` (silently converts the LEFT JOIN to an INNER JOIN); move that filter into the `ON` clause.