dbt Model Contracts
Turn "we should add contracts" into a reviewed, enforceable properties patch. Types come from the
compiled catalog, existing column semantics are preserved, and anything ambiguous is blocked with a
reason instead of guessed. This skill does not infer business keys or nullability from data samples,
and it does not enable enforcement as a side effect of scaffolding.
When to Use
- A dbt project has models but no
config.contract.enforced: true, and someone must declare column
types without hand-copying them out of the warehouse.
- A contract already exists but drifted: renamed columns, changed precision, or columns present in
the catalog and missing from
schema.yml.
- A downstream consumer (BI tool, reverse ETL, another team's model) needs a stable column contract
before a model is refactored.
- A CI gate must fail when a model's compiled columns stop matching its declared contract.
Skip this skill when the task is writing model SQL, designing the DAG, or authoring tests — and skip
it for ephemeral/snapshot resources, which cannot carry a contract.
Prerequisites
- Python 3.10+ with PyYAML (
pip install pyyaml). Any dbt install already has it.
- Fresh artifacts from the target project:
dbt build (or dbt run) followed by
dbt docs generate, which produces target/manifest.json and target/catalog.json.
- The properties YAML you intend to change (usually
models/**/schema.yml).
- No warehouse connection is required by the helper.
dbt build at the end does need one.
How to Run
python3 scripts/scaffold_contracts.py \
--manifest target/manifest.json \
--catalog target/catalog.json \
--properties models/marts/schema.yml \
--out /tmp/proposed-schema.yml \
--report /tmp/contract-report.json \
--adapter snowflake
Review /tmp/proposed-schema.yml and /tmp/contract-report.json, then apply with a snapshot:
python3 scripts/scaffold_contracts.py ... --apply --snapshot-dir .contract-snapshots/
--apply refuses to run without --snapshot-dir; the snapshot is written mode 0700 and its
checksum is recorded in the report. Verify the helper itself with
python3 scripts/test_scaffold_contracts.py (23 offline tests, no network, no dbt).
Quick Reference
| Model state |
Status |
What it means |
materialized is table/view, all catalog types usable |
READY |
Contract proposed |
incremental, materialized_view, ephemeral, snapshot |
SKIPPED_MATERIALIZATION |
Needs a human decision first |
Model absent from catalog.json |
SKIPPED_NOT_IN_CATALOG |
Stale artifacts — rebuild and regenerate |
| A column has no catalog type, or a nested/opaque type |
BLOCKED_COLUMN_TYPE |
Never invent a type; hand-author or exclude |
| Same model name in two packages |
BLOCKED_DUPLICATE_NAME |
Ambiguous identity |
| Zero catalog columns |
BLOCKED_EMPTY_COLUMNS |
Artifact problem, not a contract problem |
Exit codes: 0 proposal written, 2 refused (bad artifact, ambiguous target, unsafe --apply),
3 write failure. A 2 means no file was written — fix the input, do not hand-edit around it.
Procedure
- Refresh artifacts. Run the project's normal build, then
dbt docs generate. A catalog older
than the last model change produces confidently wrong types — check catalog.json mtime against
your last dbt run.
- Run the helper in propose mode (no
--apply). Read the JSON summary first: ready,
skipped, blocked, and per-model reasons/warnings.
- Resolve every
BLOCKED_COLUMN_TYPE by hand. A missing type usually means the model was not
materialised; a nested type means the contract cannot express the column. Do not paper over it by
dropping the column from the catalog — that silently weakens the contract.
- Decide on
SKIPPED_MATERIALIZATION models case by case. Incremental models accept contracts
only when on_schema_change is fail or append_new_columns; a contract cannot retro-fit a type
change on an existing incremental table, so set the strategy before enabling enforcement.
- Diff the proposal against the original. The helper round-trips YAML, so comments and key
ordering in the original file are lost. Re-apply comment blocks by hand in the diff, or keep
comments in a separate file — reviewable YAML beats silent comment loss.
- Check the type choices, not just their presence. Length parameters are dropped for adapters
that do not enforce them (Snowflake, BigQuery, Databricks, Spark, Athena, Trino, DuckDB) and kept
where they are enforced (Postgres) — the report says which case applied and why.
- Apply, then compile.
dbt parse catches malformed properties; dbt build --select <models>
proves the declared types match the SQL output. A type mismatch fails at build time, which is the
whole point of the contract.
- Enable in CI only after the models build clean: run
dbt build --select state:modified+ on
pull requests so a column change that breaks a contract fails review rather than production.
- Record the rollout in the PR: which models are now enforced, which were skipped and why, and
what consumers depend on them.
Completion criteria: dbt parse succeeds, dbt build passes for every newly contracted model,
the report has zero unexplained BLOCKED_* entries, and the diff shows no lost descriptions or
tests.
Type Normalization Rules
| Catalog type |
Emitted data_type |
Rationale |
NUMBER(38,9) / NUMERIC(10,2) |
number(38,9) / numeric(10,2) |
Precision and scale are part of the contract |
VARCHAR(16777216) on Snowflake |
varchar |
Snowflake does not enforce the length; declaring it is false precision |
VARCHAR(255) on Postgres |
varchar(255) |
Postgres rejects longer values, so the length is real |
NUMERIC with no precision |
numeric + warning |
Confirm the adapter default before enforcing |
VARIANT, JSON, STRUCT<…>, ARRAY<…> |
blocked |
dbt contracts cannot express nested or opaque shapes |
Quotes and stray whitespace are normalised ("NUMBER(38, 9)" → number(38,9)); nothing else is
rewritten. A type you disagree with should be corrected in the properties file consciously, not by
teaching the helper a special case.
Pitfalls
- Treating the catalog as the source of truth for intent. It describes what the warehouse built,
not what consumers were promised. Review each type against the SQL that produces it.
- Enabling contracts on a model with an undeclared column. dbt fails the build when the SQL emits
a column the contract omits — that is correct behaviour, but it turns a green pipeline red. Stage
contracts model by model.
- Inferring keys or nullability from samples.
not_null and primary keys are business decisions.
The helper only preserves constraints already declared in YAML.
- YAML round-trip losing comments. Always propose to a separate file, diff it, then apply.
- Stale catalog after a rename. A renamed column shows up as a removal plus an addition; the
resulting contract legitimately breaks downstream consumers. Announce it, do not quietly edit the
old column out.
- Assuming
--apply is reversible. It is only reversible through the snapshot the helper writes;
check the snapshot path and checksum in the report before you keep working.
- Versioned models. For versioned models, scaffold the version currently in production first and
confirm the version pins in
schema.yml before touching others.
Verification
python3 scripts/test_scaffold_contracts.py # helper's own offline suite
dbt parse --project-dir . # properties YAML is valid
dbt build --select <contracted models> # declared types match the SQL output
git diff --stat -- models/ # nothing outside the intended models changed
Fail-closed checks worth exercising before trusting the helper on a new project: a truncated
manifest.json (exit 2, nothing written), a properties file with duplicate model entries (exit 2),
an --apply without --snapshot-dir (exit 2, original untouched), and an unwritable --out path
(exit 3, original untouched).
Test Prompts
- Normal: "This dbt project has three
table models and no contracts. Add contracts with the
right column types and keep our existing descriptions and unique tests."
- Difficult edge: "Same request, but one model is incremental with
on_schema_change: ignore, one
was never built, and one column has no type in catalog.json."
- Should not activate: "Write a dbt model that computes monthly revenue by region." — that is dbt
authoring, not contract scaffolding.
Related skill: dbt-expert for model SQL, materialization strategy, and YAML authoring.
1---2name: dbt-model-contracts3description: >-4 Use when adding or repairing dbt model contracts — generating data_type declarations from5 manifest.json and catalog.json, preserving existing column tests and docs, and validating a6 contract before it is enforced in CI.7version: "1.0.0"8author: Mouad Jaouhari9license: MIT10metadata:11 source: https://skillmd.com/skills/mouadja02/dbt-model-contracts12---1314# dbt Model Contracts1516Turn "we should add contracts" into a reviewed, enforceable properties patch. Types come from the17compiled catalog, existing column semantics are preserved, and anything ambiguous is blocked with a18reason instead of guessed. This skill does not infer business keys or nullability from data samples,19and it does not enable enforcement as a side effect of scaffolding.2021## When to Use2223- A dbt project has models but no `config.contract.enforced: true`, and someone must declare column24 types without hand-copying them out of the warehouse.25- A contract already exists but drifted: renamed columns, changed precision, or columns present in26 the catalog and missing from `schema.yml`.27- A downstream consumer (BI tool, reverse ETL, another team's model) needs a stable column contract28 before a model is refactored.29- A CI gate must fail when a model's compiled columns stop matching its declared contract.3031Skip this skill when the task is writing model SQL, designing the DAG, or authoring tests — and skip32it for `ephemeral`/`snapshot` resources, which cannot carry a contract.3334## Prerequisites3536- Python 3.10+ with PyYAML (`pip install pyyaml`). Any dbt install already has it.37- Fresh artifacts from the target project: `dbt build` (or `dbt run`) followed by38 `dbt docs generate`, which produces `target/manifest.json` and `target/catalog.json`.39- The properties YAML you intend to change (usually `models/**/schema.yml`).40- No warehouse connection is required by the helper. `dbt build` at the end does need one.4142## How to Run4344```bash45python3 scripts/scaffold_contracts.py \46 --manifest target/manifest.json \47 --catalog target/catalog.json \48 --properties models/marts/schema.yml \49 --out /tmp/proposed-schema.yml \50 --report /tmp/contract-report.json \51 --adapter snowflake52```5354Review `/tmp/proposed-schema.yml` and `/tmp/contract-report.json`, then apply with a snapshot:5556```bash57python3 scripts/scaffold_contracts.py ... --apply --snapshot-dir .contract-snapshots/58```5960`--apply` refuses to run without `--snapshot-dir`; the snapshot is written mode `0700` and its61checksum is recorded in the report. Verify the helper itself with62`python3 scripts/test_scaffold_contracts.py` (23 offline tests, no network, no dbt).6364## Quick Reference6566| Model state | Status | What it means |67|---|---|---|68| `materialized` is `table`/`view`, all catalog types usable | `READY` | Contract proposed |69| `incremental`, `materialized_view`, `ephemeral`, `snapshot` | `SKIPPED_MATERIALIZATION` | Needs a human decision first |70| Model absent from `catalog.json` | `SKIPPED_NOT_IN_CATALOG` | Stale artifacts — rebuild and regenerate |71| A column has no catalog type, or a nested/opaque type | `BLOCKED_COLUMN_TYPE` | Never invent a type; hand-author or exclude |72| Same model name in two packages | `BLOCKED_DUPLICATE_NAME` | Ambiguous identity |73| Zero catalog columns | `BLOCKED_EMPTY_COLUMNS` | Artifact problem, not a contract problem |7475Exit codes: `0` proposal written, `2` refused (bad artifact, ambiguous target, unsafe `--apply`),76`3` write failure. A `2` means no file was written — fix the input, do not hand-edit around it.7778## Procedure79801. **Refresh artifacts.** Run the project's normal build, then `dbt docs generate`. A catalog older81 than the last model change produces confidently wrong types — check `catalog.json` mtime against82 your last `dbt run`.832. **Run the helper in propose mode** (no `--apply`). Read the JSON summary first: `ready`,84 `skipped`, `blocked`, and per-model `reasons`/`warnings`.853. **Resolve every `BLOCKED_COLUMN_TYPE` by hand.** A missing type usually means the model was not86 materialised; a nested type means the contract cannot express the column. Do not paper over it by87 dropping the column from the catalog — that silently weakens the contract.884. **Decide on `SKIPPED_MATERIALIZATION` models case by case.** Incremental models accept contracts89 only when `on_schema_change` is `fail` or `append_new_columns`; a contract cannot retro-fit a type90 change on an existing incremental table, so set the strategy before enabling enforcement.915. **Diff the proposal against the original.** The helper round-trips YAML, so comments and key92 ordering in the original file are lost. Re-apply comment blocks by hand in the diff, or keep93 comments in a separate file — reviewable YAML beats silent comment loss.946. **Check the type choices**, not just their presence. Length parameters are dropped for adapters95 that do not enforce them (Snowflake, BigQuery, Databricks, Spark, Athena, Trino, DuckDB) and kept96 where they are enforced (Postgres) — the report says which case applied and why.977. **Apply, then compile.** `dbt parse` catches malformed properties; `dbt build --select <models>`98 proves the declared types match the SQL output. A type mismatch fails at build time, which is the99 whole point of the contract.1008. **Enable in CI** only after the models build clean: run `dbt build --select state:modified+` on101 pull requests so a column change that breaks a contract fails review rather than production.1029. **Record the rollout** in the PR: which models are now enforced, which were skipped and why, and103 what consumers depend on them.104105Completion criteria: `dbt parse` succeeds, `dbt build` passes for every newly contracted model,106the report has zero unexplained `BLOCKED_*` entries, and the diff shows no lost descriptions or107tests.108109## Type Normalization Rules110111| Catalog type | Emitted `data_type` | Rationale |112|---|---|---|113| `NUMBER(38,9)` / `NUMERIC(10,2)` | `number(38,9)` / `numeric(10,2)` | Precision and scale are part of the contract |114| `VARCHAR(16777216)` on Snowflake | `varchar` | Snowflake does not enforce the length; declaring it is false precision |115| `VARCHAR(255)` on Postgres | `varchar(255)` | Postgres rejects longer values, so the length is real |116| `NUMERIC` with no precision | `numeric` + warning | Confirm the adapter default before enforcing |117| `VARIANT`, `JSON`, `STRUCT<…>`, `ARRAY<…>` | blocked | dbt contracts cannot express nested or opaque shapes |118119Quotes and stray whitespace are normalised (`"NUMBER(38, 9)"` → `number(38,9)`); nothing else is120rewritten. A type you disagree with should be corrected in the properties file consciously, not by121teaching the helper a special case.122123## Pitfalls124125- **Treating the catalog as the source of truth for intent.** It describes what the warehouse built,126 not what consumers were promised. Review each type against the SQL that produces it.127- **Enabling contracts on a model with an undeclared column.** dbt fails the build when the SQL emits128 a column the contract omits — that is correct behaviour, but it turns a green pipeline red. Stage129 contracts model by model.130- **Inferring keys or nullability from samples.** `not_null` and primary keys are business decisions.131 The helper only preserves constraints already declared in YAML.132- **YAML round-trip losing comments.** Always propose to a separate file, diff it, then apply.133- **Stale catalog after a rename.** A renamed column shows up as a removal plus an addition; the134 resulting contract legitimately breaks downstream consumers. Announce it, do not quietly edit the135 old column out.136- **Assuming `--apply` is reversible.** It is only reversible through the snapshot the helper writes;137 check the snapshot path and checksum in the report before you keep working.138- **Versioned models.** For versioned models, scaffold the version currently in production first and139 confirm the version pins in `schema.yml` before touching others.140141## Verification142143```bash144python3 scripts/test_scaffold_contracts.py # helper's own offline suite145dbt parse --project-dir . # properties YAML is valid146dbt build --select <contracted models> # declared types match the SQL output147git diff --stat -- models/ # nothing outside the intended models changed148```149150Fail-closed checks worth exercising before trusting the helper on a new project: a truncated151`manifest.json` (exit 2, nothing written), a properties file with duplicate model entries (exit 2),152an `--apply` without `--snapshot-dir` (exit 2, original untouched), and an unwritable `--out` path153(exit 3, original untouched).154155## Test Prompts1561571. *Normal:* "This dbt project has three `table` models and no contracts. Add contracts with the158 right column types and keep our existing descriptions and unique tests."1592. *Difficult edge:* "Same request, but one model is incremental with `on_schema_change: ignore`, one160 was never built, and one column has no type in `catalog.json`."1613. *Should not activate:* "Write a dbt model that computes monthly revenue by region." — that is dbt162 authoring, not contract scaffolding.163164**Related skill:** `dbt-expert` for model SQL, materialization strategy, and YAML authoring.