Dbt Data Quality Gate

dbt data-quality gate (verified over artifacts)

Hold a dbt project to a data-quality and governance policy and prove it — conformance is gated by a script that reads dbt's own compiled artifacts, maps each breach to a rule id + severity, and exits non-zero on blocking failures, not by assertion.

Core principle

Quality is enforced, not assumed. The loop is: run the gate → triage by severity → fix the root cause (add a test, a description, a tag, an owner) → re-run, until the blocking-severity count is zero.

Be honest about scope (this is the rule that keeps the skill correct): tests only assert what you encode. A green gate means your declared expectations held, not that the data is correct, complete, or compliant. PII detection by column name is heuristic — it misses unnamed/encoded PII and false-positives on lookalikes. Freshness and volume anomalies need runtime data, not just the manifest. This assists data governance; it is not a guarantee of data correctness or GDPR compliance. → references/01-data-contracts-and-quality.md

When to use vs. not

• Use for: adding a data-quality / data-contract CI gate to a dbt project; requiring tests, descriptions, owners, and freshness on models and sources; enforcing not_null/unique on keys; finding untagged likely-PII columns; setting a minimum test count or a test pass-rate threshold.
• Not for: profiling raw data values or detecting drift/anomalies at the row level (needs a runtime data-observability tool); non-dbt pipelines; or certifying GDPR compliance (this assists, it does not certify).

Inputs to gather first

1. The artifacts — target/manifest.json is required (run dbt compile or dbt build). target/run_results.json is optional but enables the test pass-rate check (run dbt build/dbt test). → references/02-the-manifest-gate.md
2. The policy — minimum tests per model, which columns are "keys", which meta/tags are required (owner?), and the PII tag + name patterns. Defaults are sensible; confirm the bar with the user. → references/04-dbt-tests-and-freshness.md
3. Severity bar — the gate blocks on blocking-severity rules by default; warn rules are reported only. Promote/demote rules per project.

Workflow

Load each reference when you reach its step.

1. Set the policy & scope. Confirm the quality bar and that "green gate" ≠ "correct data". → references/01-data-contracts-and-quality.md

2. Produce the artifacts. From the dbt project root, generate the manifest (and run_results for the pass-rate check). → references/02-the-manifest-gate.md

   dbt deps && dbt build            # -> target/manifest.json + target/run_results.json
   # or, contract/metadata-only check without running models:
   dbt compile                       # -> target/manifest.json
   

3. Configure the gate. Copy the example config and tune thresholds, key patterns, PII patterns, required meta/tags, and the severity gate. → references/04-dbt-tests-and-freshness.md

   cp scripts/dbt-quality.config.example.json dbt-quality.config.json   # then edit
   

4. Run the gate and triage by severity. → references/02-the-manifest-gate.md

   python3 scripts/dbt_quality_gate.py \
     --manifest target/manifest.json \
     --run-results target/run_results.json \
     --config dbt-quality.config.json        # -> dbt-quality-report/report.md
   

5. Scan for untagged PII and tag or remove what it finds. → references/03-pii-governance.md

   python3 scripts/pii_scan.py --manifest target/manifest.json --config dbt-quality.config.json
   

6. Fix root causes in the dbt project — add generic tests (not_null/unique/accepted_values/relationships), descriptions, meta.owner, source freshness, and pii tags in your schema.yml / _models.yml — then re-run steps 4–5 until the blocking count is zero. → references/04-dbt-tests-and-freshness.md

7. Gate in CI on every PR, archiving the report. → references/05-running-it-and-ci.md

What's in this skill

• scripts/dbt_quality_gate.py — the gate backbone: enforces tests-per-model, key not_null/unique, descriptions, source freshness, required meta/tags, PII tagging + exposure, and (with run_results) test pass-rate. Maps each breach to a rule id + severity, writes report.json + report.md, exits non-zero on blocking failures.
• scripts/pii_scan.py — flags likely-PII columns (email, ssn, dob, name, phone, address, iban, credit_card, …) that aren't tagged; exits non-zero if any are found.
• scripts/dbt-quality.config.example.json — thresholds, key/PII patterns, required meta/tags, severity gate + overrides, ignore list.
• scripts/requirements.txt — stdlib-only; nothing to install.
• scripts/sample/ — a tiny hand-built manifest + run_results used to self-test the gate.
• references/01–05 — data contracts & quality dimensions, the manifest gate, PII governance, dbt tests & freshness, and running it in CI.

Definition of done

• dbt_quality_gate.py reports 0 blocking-severity violations across all models and sources.
• Every model has >= minTestsPerModel tests; key columns carry not_null + unique.
• Every model and source has a description; every source has freshness configured.
• pii_scan.py finds no untagged likely-PII columns; tagged PII is protected (masked/hashed) or justified.
• Required meta/tags (e.g. owner) present on every model.
• If run with --run-results, test pass-rate meets the threshold.
• CI runs both scripts on every PR; report.md archived.

Guardrails — avoid these mistakes

• Don't claim "the data is correct" from a green gate. State "0 blocking policy violations against the declared contract; tests passed." Overclaiming is the cardinal error here.
• Don't add a test to silence the count — a not_null on a column that's never null proves nothing. Test the invariant that actually matters.
• Tag PII at the source, not just in marts; a pii tag on a downstream model doesn't protect the raw column. Re-run pii_scan.py after schema changes.
• Don't widen ignore or demote a severity to go green. Suppress only verified exceptions, with a written reason in review.
• Compile against the real target. A stale target/manifest.json gates the old graph — regenerate after every model/yml change.
• Freshness in the manifest is config, not a result. This gate checks that thresholds are declared; run dbt source freshness to check they're met.
• Heuristic PII detection is a floor, not a ceiling. A human still owns the data-minimization and exposure decision.