Generate Data-Quality Rules from a Profiling Pass

Tier: Core · Workflow area: Data Quality & Profiling · ~35 minutes

The gap between "we profiled it" and "we protect it" is where data quality programs stall: writing expectation suites by hand is tedious enough that coverage stays thin forever. AI closes the gap mechanically — profiling statistics are exactly the evidence a model needs to draft candidate rules, complete with thresholds. But a drafted rule is a proposal about how the business works, and only the data owner knows whether discount_pct <= 40 is an invariant or last quarter's coincidence. The workflow is therefore a pipeline with a mandatory human valve: profile → candidate rules → owner review → deploy → tune.

When to use

• Right after a profiling pass (pb-25) — the census is fresh, the suspicious columns are known, and every finding wants to become a standing check instead of a one-time observation.
• Guarding pipeline boundariesvendor feeds, ERP extracts and inter-team handoffs where silent upstream changes currently surface as month-end surprises.
• Master-data governancecustomer/vendor/material tables feeding MDG-class engines, where rules become the machine-readable quality contract.

When NOT to use

• Auto-deploying every drafted ruleunreviewed generated rules encode today's accidents as law; profiles capture coincidences (every current customer happens to be Indian) that a rule would enforce as policy.
• When failure has no owner or actiona rule that fires into an unread channel is alert debt. No rule ships without a named owner and a defined response.
• As anomaly detection on genuinely chaotic datarules assert invariants; if the data has none yet, fix ingestion first.

The recipe

1. 1.

   Start from real profiling output, not schema imagination. Reuse the pb-25 census — SUMMARIZE results, null rates, distinct counts, value patterns, orphan counts — or generate it now with DuckDB. Rules drafted from column names alone are guesses; rules drafted from measurements inherit evidence.

2. 2.

   Ask for candidate rules in a strict, reviewable format.

   Copy

   Below: profiling output for the vendor_master and invoice tables
   (census, null rates, patterns, orphan counts, per-record-type
   completeness). Draft candidate data-quality rules.
   
   For EACH rule emit exactly:
   - rule_id, column(s)
   - type: format / range / completeness / uniqueness / referential /
     cross-field dependency / freshness / volume
   - the check, as a Great Expectations expectation (name + kwargs)
   - evidence: the profiling measurement justifying it, quoted
   - proposed severity: BLOCK (quarantine batch) or WARN (log + notify)
   - confidence: INVARIANT (business logic requires it) vs OBSERVED
     (true in this profile, may be coincidence)
   
   Cover at least: tax-ID and IFSC/IBAN format checks; completeness
   conditional on record type (e.g., bank fields required only for
   payment-relevant vendors); invoice→vendor referential integrity;
   date ordering (invoice_date <= posting_date); amount sign vs
   document type; volume bounds per load.
   
   Mark every threshold you inferred from the profile as OBSERVED.
   

   The INVARIANT/OBSERVED split is the load-bearing feature: it pre-sorts the owner review, and it is where models are honest if asked and silently overconfident if not.

3. 3.

   Run the owner review as a working session, not an email. Walk the data owner through the OBSERVED rules; each one gets promoted (it is a real invariant), adjusted (right idea, wrong threshold), or rejected (coincidence). Real examples of each from a typical session: country IN ('IN','AE','SG') rejected (expansion planned), payment_terms <= 90 adjusted to 120 (one negotiated exception class), bank_account NOT NULL WHERE vendor_type='PAYABLE' promoted to BLOCK. Record the verdicts in the rule file itself — the review trail is part of the artifact.

4. 4.

   Encode the survivors as a Great Expectations suite.

   Copy

   import great_expectations as gx
   
   context = gx.get_context()
   batch = context.data_sources.add_pandas("vendor_loads") \
       .read_dataframe(df)
   
   # R-07: conditional completeness (owner-promoted, BLOCK)
   batch.expect_column_values_to_not_be_null(
       "bank_account",
       row_condition='vendor_type == "PAYABLE"',
       condition_parser="pandas")
   
   # R-11: format, GSTIN (INVARIANT, BLOCK)
   batch.expect_column_values_to_match_regex(
       "gstin",
       r"^[0-9]{2}[A-Z]{5}[0-9]{4}[A-Z][1-9A-Z]Z[0-9A-Z]$")
   
   # R-15: volume bound (OBSERVED->adjusted, WARN)
   batch.expect_table_row_count_to_be_between(
       min_value=800, max_value=15000)
   

   Keep rule IDs from the review in comments or expectation meta — six months later, "why does this rule exist" must be answerable by grep.

5. 5.

   Calibrate against history before enforcement. Run the suite over the last 60–90 days of loads in WARN-only mode. Two numbers per rule: how often it would have fired, and how many firings were true defects (spot-check a sample). A rule firing on 30% of historical clean loads has a wrong threshold; a BLOCK rule that would have quarantined a legitimate month-end volume spike gets a widened bound or a WARN downgrade. This backtest is what separates a quality gate from a pipeline-outage generator — do not skip it because the rules "look reasonable."

6. 6.

   Deploy with routing, and schedule the tuning loop. BLOCK failures quarantine the batch and page the pipeline owner; WARN failures land in a triaged channel with a weekly review. Then close the loop quarterly: re-profile, diff against the rule set, and ask AI to propose additions for new columns and drift ("null rate on payment_terms moved 2%→11% — investigate or re-baseline?"). Rules are a living contract with the data, not a launch artifact.

Make it yours

• dbt-centric stacksthe same candidate rules emit as dbt tests (not_null, accepted_values, relationships, custom generic tests for formats); keep the evidence/verdict trail in YAML comments.
• In-engine enforcementpromote true invariants to constraints where the engine allows (CHECK, foreign keys, unique indexes): a constraint is a rule that cannot be ignored. The suite then covers what constraints cannot express — conditional completeness, freshness, volume.
• MDG/ERP master datatranslate the surviving rules into the ERP's validation framework (MDG rule sets, D365 quality policies) so quality enforces at entry, with the GX suite as the independent auditor of what gets through anyway.

Quality check

• Every rule carries quoted profiling evidencea rule with no measurement behind it is someone's assumption in YAML; it can ship, but only labeled as such and owner-approved.
• Every OBSERVED rule has an owner verdict recordedpromoted, adjusted or rejected, with a name and date. Unreviewed OBSERVED rules do not deploy; this is the valve that keeps coincidence out of law.
• The suite was backtested on historical loadsfiring rates measured, BLOCK rules checked against legitimate historical spikes, thresholds tuned on evidence.
• Severity maps to responseeach BLOCK names who unblocks and how; each WARN lands in a reviewed channel. A rule whose failure changes nothing gets deleted, not muted.
• The tuning loop has a calendar entryquarterly re-profile and drift review, with an owner. Rules without a review cadence rot into either noise or blindness.