dbt-fix-conventions

dbt Fix Conventions — DataPlatformSnowflake

IL-specific conventions for editing dbt models in ImagineLearning/DataPlatformSnowflake. These rules are not generic dbt — they encode the personal-target sandbox workflow, the WNE_DIM_MODEL/STAGING quirks, and the SCD-chain pitfalls that have produced production incidents.

This skill is a reference, not a workflow. Code generation, branching, audits, and review are handled by /il:generate-code. Apply these rules when generating or modifying anything under dw_dbt/app/snowflake/.

---

⛔ Hard Rules

1. Production database is WELD_NORTH_PROD. Never write to it. All dbt runs use the engineer's personal target (DBT_TARGET env var). The profiles.yml database is hardcoded to WELD_NORTH_TEST.
2. No git commit/push from generated code. The orchestrator stops before commit so the engineer reviews.
3. Anti-hallucination: verify model files via Glob/Grep against $DPS_DBT_ROOT/models/ and verify columns via INFORMATION_SCHEMA.COLUMNS before referencing them. Do not invent column names.
4. PII protection applies to all model SQL, schema.yml descriptions, and any validation output (FERPA/COPPA).

---

Naming and Macro Conventions

• Snowflake objects: UPPER_SNAKE_CASE (tables, columns).
• dbt SQL: lower_snake_case for CTEs, aliases, locally-bound names.
• Composite keys: use {{ surrogate_key([...]) }} — never hand-roll MD5(CONCAT(...)).
• Raw inputs: {{ source('schema', 'table') }}. Upstream models: {{ ref('model') }}.
• Schema routing: do not override generate_schema_name per-model. The repo macro routes by directory.

---

schema.yml Requirements

When adding or modifying a column, the matching schema.yml entry MUST be updated in the same change:

• Column description (drives the Snowflake COMMENT, which /dp:investigate-data reads as the business definition).
• Tests: at minimum not_null on grain columns, unique on natural/surrogate keys, relationships to upstream dims, accepted_values for enums.
• Mark PII columns explicitly in the description (PII, FERPA, COPPA) so downstream consumers can detect them.

If the model directory has no schema.yml, create one — every model directory in this repo has one by convention.

---

Materialization Rules

Schema target	dbt path	Materialization
WNE_DIM_MODEL	models/core/dimension/, models/core/fact/	table, incremental
STAGING	models/staging/	table, incremental
SISENSE	models/standalone/	table, view
IL_EVENTS	models/il-events/	incremental

For incremental models, preserve the existing unique_key and strategy. Do not switch strategies as a side-effect of an unrelated change.

---

Incremental + New Column = Manual ALTER First

dbt does not auto-evolve schema on incremental targets unless on_schema_change is configured (most models in this repo aren't). When adding a column to an incremental model:

1. Add the column to WELD_NORTH_TEST.<schema>.<table> manually before running dbt:

   ALTER TABLE WELD_NORTH_TEST.<schema>.<table>
   ADD COLUMN IF NOT EXISTS <new_col> <type>
   COMMENT '<business definition>';
   

2. Then run dbt run -t $DBT_TARGET -s <model>.
3. Add a Snowchange migration (see below) for the production rollout.

--full-refresh on a >10M row incremental requires the engineer's explicit confirmation in the orchestrator.

---

SCD Chain Gotcha — class_dim and friends

SCD Type 2 models in this repo have a CTE chain that all must be updated when a column is added or renamed. For class_dim specifically:

new_class_events → new_class → new_and_updated_class → updated_class → new_class1 → updates_deletes → inserts → dummy_row

new_class1 is the one engineers most commonly miss. Generated edits to any SCD model must touch every CTE in the chain or the new column will silently drop on update rows.

Other SCD models follow the same pattern with different CTE names — search the model's existing structure with Grep before editing.

---

Personal-Target Gotchas (hard-won)

These apply when running dbt locally against WELD_NORTH_TEST via DBT_TARGET. They do not apply in CI/CD (the staging target).

1. Personal targets write to WNE_DIM_MODEL, not STAGING. A staging model built locally lands in WELD_NORTH_TEST.WNE_DIM_MODEL.<name>_STAGING, but downstream models hardcode STAGING. To validate locally, query the WNE_DIM_MODEL copy directly.
2. Run staging before downstream, with --threads 1 to prevent races: dbt run -s <staging_model> --threads 1 && dbt run -s <downstream> --threads 1.
3. packages.yml blocks compile if dbt deps failed. Comment it out for local testing only — restore before commit.
4. DATA_ENGINEER role cannot write to STAGING. Don't try to clone tables there for validation; use compiled SQL + the WNE_DIM_MODEL copy.
5. PowerShell exit code 1 from Python warnings is a false alarm. Trust dbt's "Completed successfully" output, not $LASTEXITCODE alone.
6. Snowchange runs before dbt in CI/CD when columns are added. If the migration script exists, it must be deployed to WELD_NORTH_DEV/QA before the dbt PR merges.

---

Snowchange Migration Template

Path: snowchange/sql/WELD_NORTH/V<version>__<desc>_dp_<TICKET>.sql

Standards:

• One ALTER TABLE ADD COLUMN IF NOT EXISTS <col> per statement (the multi-column form does not support IF NOT EXISTS).
• Include a COMMENT on every new column.
• DDL outside transactions; DML wrapped in BEGIN; ... COMMIT;.
• Backfill UPDATEs use WHERE <col> IS NULL for idempotency, ROW_NUMBER() ... QUALIFY rn = 1 for dedup.
• Tables >100M rows: optionally clone to sandbox.scratch.<table>_<env>_<ticket>_bkp (transient) before backfill. Any DDL like this clone step requires explicit user confirmation before execution — never auto-execute CREATE / ALTER from this skill.

Reference: any recent V*.sql under DataPlatformSnowflake/snowchange/sql/WELD_NORTH/ that adds a column with backfill (sort by version desc).

---

Verifying the Result in Snowflake

After dbt run, verify the materialization landed and columns evolved:

SELECT TABLE_NAME, ROW_COUNT, LAST_ALTERED
FROM WELD_NORTH_TEST.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME ILIKE '<model_name>';

SELECT COLUMN_NAME, DATA_TYPE, COMMENT
FROM WELD_NORTH_TEST.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ILIKE '<model_name>'
ORDER BY ORDINAL_POSITION;

For backfills, snapshot null-rate before and after the UPDATE — aggregates only, no PII.

---

Related Skills

Need	Skill
Find a model, lineage, materialization	dp:explore-dbt-models
Run validation queries against WELD_NORTH_TEST	dp:query-snowflake
Investigate the data before fixing	/dp:investigate-data <TICKET>
Publish the fix summary	Skill dp:publish-ticket-confluence