Can AI Write Data Quality Rules? A Review Rubric for AI-Generated Checks

An AI drafted twenty-three data quality rules against a retail schema in about forty seconds. Fourteen shipped as written. Five needed retuning. Four went in the bin. And one of the ones that looked perfect would have generated roughly a thousand false alerts a week.

That last one is the whole story. The question worth asking is not whether a language model can produce data quality rules — it obviously can, and it will produce a hundred of them before you finish your coffee. The question is whether reviewing AI-drafted rules is cheaper than writing them yourself. If review costs more than authoring, the workflow is theatre.

What follows is the prompt contract, the review rubric, and the deployment posture from a single documented run. Read the “what this evidence is not” section before you quote any of the numbers.

The failure of the blank rule file

Hand-authored rule coverage rots. Not because stewards are lazy — because the surface area moves faster than the catalogue does.

A column lands. Nobody adds a check for it. A field changes meaning after a source migration and the old range test keeps passing while the semantics quietly break. Two stewards profile the same orders table and produce two different rule sets, because one of them cares about referential integrity and the other cares about thresholds, and neither is wrong.

By column ninety of a hundred and forty, the honest failure mode is fatigue. shipped_date >= order_date is obvious when someone points at it and genuinely easy to forget when you’re grinding through a wide table by hand.

So there is a real gap to fill. The interesting part is what fills it, and under what supervision.

Reframe the question: what makes a rule deployable

Before generating anything, fix the bar. A rule is deployable when four things are true:

1. Correct — it is factually true about the data as it exists, not as the schema implies.
2. Enforceable — it compiles and runs in your actual executor, not in a hypothetical one.
3. Clear failure semantics — you know what happens when it breaks and who cares that it broke.
4. Owned — a named human is accountable for it.

Fail any one of the four and it does not ship, regardless of how sophisticated it sounds. This bar is what turns a generation exercise into a review exercise, which is where the actual work lives.

Why cold prompts produce template rules

Give a model only the DDL and you get exactly what you’d expect:

• customer_id is not null
• email matches an email pattern
• order_date is a valid date

That is not a rule set. That is a rules template, and you did not need a language model to generate it — you needed a for-loop over your column list. It isn’t wrong. It’s worthless, because it contains no information that wasn’t already in the schema.

The difference between template output and useful output is a profile.

Profile before you prompt

This is the step people skip, and skipping it explains most of the disappointing results I hear about.

Run a profiling query first and paste the output into the prompt. The fields that earn their place:

• Row count — gives the model a sense of scale for threshold suggestions.
• Null rate per column — separates “should never be null” from “legitimately sparse”.
• Distinct count — distinguishes an identifier from a category from free text.
• Min / max on numerics and dates — surfaces negative values, sentinel dates, impossible ranges.
• Top-N values on low-cardinality columns — this is the one that does the heavy lifting.
• Masked format samples for strings — shape without content.

Top-N is the difference between the model knowing order_status is a string and the model knowing order_status has exactly five values, one of which is REFUNDED. That single fact is what let it produce the best rule in the run.

On privacy: the model never sees raw rows. It sees a schema and aggregate statistics, plus format samples that are masked before they leave your environment. That distinction is what makes this defensible in a privacy review, and it also happens to improve quality — aggregates force the model to reason about distributions rather than pattern-match on individual records.

The prompt contract

Structure is the trick. Four blocks, and a strict output schema.

ROLE AND TARGET
You are drafting data quality rules to be executed as dbt tests against DuckDB.
Use only syntax valid in that executor. Do not invent macros or packages.

SCHEMA
<paste table DDL, including data types and any declared constraints>

PROFILE
<paste profiling output as a table: row count, null rate, distinct count,
min/max, top-10 values for low-cardinality columns, masked format samples>

OUTPUT CONTRACT
Return YAML only. One entry per rule, with these fields:
  rule_id:      short stable identifier
  column:       target column (or list, for multi-column rules)
  rule_type:    not_null | accepted_values | range | regex | relationship | expression
  expression:   the executable SQL predicate
  severity:     warn | error
  rationale:    one line, why this rule follows from the profile
  assumptions:  every business assumption this rule depends on, stated explicitly
Do not propose rules you cannot justify from the schema or profile.

Naming the executor stops the model inventing syntax. The output contract stops you reverse-engineering intent out of raw SQL.

The assumptions field is the review accelerator

Of everything in that contract, the assumptions field is what makes review fast rather than painful.

When a rule arrives with assumption: discount_pct is expressed as a percentage between 0 and 100, not a fraction, that is a question a steward answers in five seconds. Without it, you have to infer the intent from the predicate, decide whether the inference is right, and then decide whether the rule is right. Three judgements instead of one.

Make the model state its assumptions and you convert rule review into assumption review. Assumption review is much cheaper.

The four-bucket review rubric

Sort every generated rule into exactly one bucket. Steal this shape verbatim.

Worked examples from the run:

Accept. order_total >= 0 WHERE order_status <> 'REFUNDED'. The profile showed negative values in order_total and showed REFUNDED in the status distribution, and the model connected the two. That is a rule I would have written and it would have taken me longer.

Accept. shipped_date >= order_date. Trivially obvious once stated. Genuinely easy to miss by hand.

Accept, with a caveat. orders.customer_id must exist in customers. Inferred from naming convention alone. Correct here. It would not always be correct, and that inference deserves a second look every time it appears.

Retune. postcode must match a UK postcode regex. Ninety-four percent of values matched that shape, so the model generalised. The remaining six percent are Irish Eircodes, because the business ships to Ireland. That is not a defect — it is a legitimate minority the rule would have flagged forever. The intent is sound, so this goes to retune, not reject: scope it by country, or widen the pattern.

Reject. Four rules. Two were duplicates of each other under different IDs. One referenced a column that does not exist, because the model pattern-matched on a similar table it had seen. One was a completeness check on a column that is ninety-nine percent null by design. Hallucinated column names are the failure mode to watch — and they are cheap to catch, because they do not compile.

The false positive problem

The postcode rule matters more than the rejects, because the rejects are loud and the false positives are quiet.

A rule that fails to compile costs you thirty seconds. A rule that is plausible, executable, and wrong costs you your alert channel. It fires every day. It fires on legitimate records. Stewards learn that this check is noise, then that the dashboard is noise, then that alerts in general are somebody else’s problem.

The worst outcome in data quality is not a missed defect. It is a discredited alert channel — because after that, every subsequent defect is also missed, and you have no signal left to tell you so.

Which is why the rubric puts retune between accept and reject. Plausible-but-overgeneralised is the dominant failure mode of AI-drafted rules, and it needs its own bucket.

What the model cannot infer

Running the accepted set surfaced the honest limit. freight_cost > 0 failed on four hundred rows. Those rows are click-and-collect orders, which have no freight. Nothing in the schema said so. Nothing in the profile said so. That is not a model failure — it is a context gap, and the context lives in people’s heads.

Permanently your job, not the model’s:

• Freshness and SLA. No profile tells you this table is supposed to land by 06:00.
• Consent, retention, and regulatory flags. These come from policy, not distribution.
• Cross-system reconciliation. Counts and sums against the source of record — usually where the defects that actually cost money show up.
• Policy thresholds. “No single order above fifty thousand without approval” is a decision somebody made, not a pattern in the data.
• Cross-domain referential integrity where naming conventions don’t help. If the foreign key is called party_ref, no amount of pattern matching will find its parent.

Treat the model as a fast junior analyst with excellent pattern recognition and zero knowledge of your business. Which, to be fair, describes a lot of first weeks.

Deployment posture

Warn first, always. Every generated rule goes live in warn mode. Watch it for two weeks against real volume. Promote to blocking only the rules that stayed quiet or fired correctly. That single decision removes most of the risk from the entire approach.

Tag provenance on every rule. When an auditor asks why a threshold is 0.95, “the AI suggested it” is not an answer. This is:

version: 2
models:
  - name: orders
    columns:
      - name: order_total
        tests:
          - dbt_utils.expression_is_true:
              expression: ">= 0 OR order_status = 'REFUNDED'"
              config:
                severity: warn
              meta:
                rule_id: DQ-ORD-014
                generated_by: "llm-draft, profile-prompt v1.2"
                reviewed_by: "a.steward"
                review_date: "2025-03-14"
                accepted_assumption: >-
                  Negative order_total is only valid on refund rows;
                  confirmed with finance ops.
                promotion_status: warn_until_2025-03-28

Version rules in git alongside your models. Rules are code. They get reviewed, diffed, and blamed like code.

Regenerate on schema change. This is the actual win, and it isn’t the first draft — it’s the redraft. A new column lands, you regenerate the whole set, you diff against the committed version, you review only the delta. That loop is what stops coverage from rotting, and it is far cheaper than the initial pass.

What this evidence is not

Be clear about the limits, because the numbers above are easy to misuse.

This was one dataset (an orders table and a customers table in DuckDB with dbt on top), one model, one run. Fourteen accept, five retune, four reject is a count from a single documented pass, not an accuracy figure. There was no gold-standard rule set to compare against, so there is no precision or recall here — the “missing” bucket was populated by my judgement of what should have been proposed, which is exactly the kind of judgement a proper evaluation would need to control for. Review took around twenty-five minutes; that is my speed on a schema I already knew well.

A defensible evaluation would need: multiple domains, a human-authored reference rule set per table, multiple models and prompt variants, blind review by stewards who did not write the prompt, and measurement of false positive rate against production volume over time. If you build that, it will be far more useful than anything a single run can tell you.

Takeaway: run the three-table pilot this week

You can settle this for your own estate in about an hour.

1. Pick three tables you know cold. Familiarity is the point — you need to be able to judge output instantly.
2. Profile them. Row count, null rate, distinct count, min/max, top-10 values, masked format samples. No raw rows leave your environment.
3. Run the prompt contract above. Four blocks, strict YAML output, assumptions field mandatory.
4. Score every rule into one bucket — accept, retune, reject, missing. Record a one-line reason for each reject and each retune.
5. Time both sides. How long did review take? How long would authoring the same coverage have taken you? That ratio is your answer, and it will differ by domain.
6. Ship the accepted set in warn mode only, with provenance metadata, committed to git.
7. Set a calendar reminder for two weeks out to review what fired and promote selectively.

If your accept rate looks nothing like mine, that is useful information — the variable that matters most is probably how much of your data quality logic is business context rather than distributional pattern. Ten real results from ten estates beat one benchmark from a single run.