Generate a Data Test Suite from Profiling, Then Prune It

This playbook covers the profile-propose-prune loop for dbt and Great Expectations suites, plus generating the synthetic edge-case fixtures that prove the tests fire when they should.

When to reach for this

Use this when a model or table has no tests (the usual state of inherited pipelines), when onboarding a new source whose behavior you do not yet know, or when an incident reveals a class of check that should exist everywhere.

Triggers:

• •
  A critical model with zero or trivial-only tests.
• •
  A new source table entering production use.
• •
  Post-incident action item: "add coverage so this cannot recur silently."

The workflow

1. 1.

   Profile with a query, not an impression. Generate the facts the proposals will be grounded in:

   Copy

   SELECT
     COUNT(*) AS row_count,
     COUNT(DISTINCT order_id) AS distinct_ids,
     SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) AS null_customer,
     MIN(order_date) AS min_date, MAX(order_date) AS max_date,
     MIN(amount) AS min_amount, MAX(amount) AS max_amount,
     COUNT(DISTINCT status) AS status_cardinality
   FROM stg_orders;
   -- plus: SELECT status, COUNT(*) FROM stg_orders GROUP BY status;
   -- and daily row counts for the last 90 days for volume behavior
   

2. 2.

   Propose tests from the profile — with reasons and false-positive risk.

   Copy

   Below: schema, profile stats, value distributions, and 90 days of
   daily row counts for stg_orders.
   
   Propose a dbt test suite (schema.yml) covering: uniqueness, nulls,
   accepted values, ranges, referential integrity to stg_customers,
   and freshness/volume expectations.
   
   For EVERY proposed test include:
   - the evidence in the profile that justifies it
   - false-positive risk (low/medium/high) and what normal event would
     trip it (month-end spike, new enum value, backfill)
   - severity: error (block) vs warn.
   Do not propose tests the profile cannot justify. Flag columns where
   you need more history to say anything.
   
   [paste profile output]
   

3. 3.

   Prune like the on-call engineer you will be. This is the step that decides whether the suite helps or trains everyone to ignore alerts. Cut or downgrade: range tests pinned to the observed min/max (tomorrow's legitimate outlier fails), accepted-values on enums that grow (new product line = 3 a.m. page), volume checks with hard thresholds instead of tolerances. Keep hard error severity only where a violation genuinely means stop-the-pipeline: key uniqueness, critical non-nulls, referential integrity.

4. 4.

   Prove the tests fire — with synthetic edge-case fixtures. A test you have never seen fail is a hope, not a check. Generate fixtures that violate each rule:

   Copy

   Generate 25 synthetic rows for stg_orders as CSV matching the schema:
   - 15 valid rows with realistic variety (unicode names, boundary dates
     like Feb 29, DST-transition timestamps, zero and max amounts)
   - 10 violating rows, one per proposed test, each labeled in a
     _violates column: duplicate order_id, null customer_id, status
     outside accepted set, negative amount, order_date in the future,
     customer_id absent from stg_customers, etc.
   Foreign keys among valid rows must resolve against this fixture set.
   

   Load the fixtures into a scratch schema, point the suite at it, and confirm: all 10 violations caught, all 15 valid rows pass. Any miss is a broken test — fix it now, while it costs nothing.

5. 5.

   Install with ownership and routing. Every error-severity test gets an owner and a documented response ("uniqueness failure on order_id: stop, check upstream dedup job"). Warn-severity results route to a channel reviewed weekly, not to pages.

6. 6.

   Schedule the re-profile. Data drifts; suites rot. A quarterly re-run of steps 1–3 against fresh history catches ranges that have shifted and enums that have grown before they page anyone.

Verify before you trust it

• •
  Fixture run: every test demonstrated to fail on its violating row and pass on valid rows — 100% of the suite, not a sample.
• •
  No range or accepted-values test pinned exactly to observed min/max/current enum without a stated business rule behind it.
• •
  Severity audit: every error-level test has a written justification for blocking the pipeline; everything else is warn.
• •
  Referential tests checked in the failing direction (orphaned child rows) — the direction generated suites most often invert or omit.
• •
  The suite runs green on current production data before merge; a suite born red is born ignored.

Pitfalls

• Overfitting to the profiled sample. The signature failure: amount BETWEEN 0.01 AND 4999.99 because that was the observed range. Normal growth then generates failures until someone deletes the test in irritation.
• Testing what is easy over what matters. Generated suites pile up not-null checks and skip the cross-column invariant (ship_date >= order_date) that actually catches corruption. Ask explicitly for cross-column and cross-table invariants.
• Fixtures that mirror the happy path. Synthetic data generated without adversarial instruction is suspiciously clean — no unicode, no boundary dates, no nulls in nullable columns. Demand the ugly rows by name.
• Every test at error severity. Blocks the pipeline nightly for cosmetic issues; within a month the team auto-reruns past failures without reading them. Severity triage is part of the suite, not an afterthought.
• Referential checks that scan giant parents unfiltered. A generated FK test that joins the full dimension every run can cost more than it protects. Scope to the incremental window where the platform allows.

Tools

• dbt Copilotproposes tests directly from project metadata; strong for the propose step on dbt-managed models (paid plan; on dbt Core, pair a generic assistant with the profile prompt above).
• Great Expectations + LLMGX Core's expectation vocabulary is wider than dbt's (distributions, quantiles); have the LLM draft expectation suites from the same profile.
• Sodachecks-as-YAML with an accessible DSL; a good target format when non-engineers co-own the rules.

Client-safe variant

Profiles, not rows: the propose step needs aggregate statistics (counts, ranges, cardinalities, distribution shapes), which are far less sensitive than data — but on client engagements even value distributions of PII columns (names, cities) should be summarized as shapes ("varchar, 98% unique, unicode present"), never as value lists. Run proposal and fixture generation through client-tenancy Bedrock/Azure OpenAI or local Ollama; generate fixtures synthetically rather than sampling client rows. All profiling queries, fixture loads, and test runs execute inside the client warehouse, so no record ever appears in a prompt.