What this prompt does
This prompt makes the AI build a data quality validation framework that catches drift before it surfaces three dashboards downstream. You describe the [data_environment], the [table_count] tables, [total_volume] rows, and [source_systems], and the model implements schema validation against a catalog (BLOCK on type changes, WARN on new columns), completeness checks that block loads when [critical_columns] exceed [null_threshold]% nulls, and freshness checks keyed to [freshness_limit].
The structure works because it layers cheap, deterministic checks (schema, completeness, freshness, referential integrity) with statistical detection. It computes row counts, distinct counts, and numeric stats against a rolling [baseline_window]-day baseline using [anomaly_method], validates foreign keys between [related_tables] before loading facts, runs custom rules via [rule_engine] for [business_rules], and produces an HTML report delivered via [delivery_method]. Configuration lives in [config_format], so checks are declarative per table rather than buried in code. Silent data drift is worse than a loud failure, and this makes the failures loud.
When to use it
- You have a warehouse or dbt project and want quality gates so bad data is caught at load time.
- You need schema-change detection that blocks type changes but tolerates additive columns.
- You want completeness thresholds on critical columns that stop a load rather than passing nulls through.
- You need freshness alerts when a source goes stale.
- You want statistical anomaly detection on row counts and numeric distributions against a baseline.
- You need referential integrity validated before fact tables load, and a shareable quality report.
Example output
Expect a framework: per-table expectation files in [config_format], a schema-validation routine with severity levels, completeness and freshness checks with thresholds, an anomaly-detection module using [anomaly_method] over a [baseline_window]-day window, referential-integrity checks across [related_tables], custom business-rule checks via [rule_engine], and an HTML report with a pass/fail dashboard, trend charts, and drill-downs, delivered through [delivery_method].
Pro tips
- Reserve BLOCK severity for genuine breakers (type changes, critical-column nulls) and WARN for additive changes — blocking on every new column will get the gates disabled.
- Set
[null_threshold]per the column's real nullability; a 5% threshold on a column that's legitimately sparse causes constant false blocks. - Tune
[anomaly_method]carefully — a Z-score band that's too tight flags normal seasonal swings as anomalies and erodes trust in the report. - Size
[baseline_window]to cover your real cycles; a 30-day baseline smooths weekly patterns better than a 7-day one for most warehouses. - Order the checks so referential integrity on
[related_tables]runs before the fact load, catching orphans at the gate rather than after. - Keep the
[config_format]expectation files close to the table definitions so the checks evolve with the schema instead of rotting. - Scope the
[critical_columns]list tightly — every column you mark critical can block a load, so reserve it for fields a downstream report genuinely can't tolerate as null. - Use the trend charts in the report, not just the latest pass/fail, to spot a metric drifting slowly toward a threshold before it actually trips.
