Design automated data quality tests for a pipeline
A prioritized set of data tests covering freshness, volume, schema, uniqueness, and business rules, with thresholds and owners.
The prompt
Help me design automated data quality tests for a data pipeline or set of tables.
Pipeline/tables: [names and what each represents, including grain]
Schemas: [PASTE column names and types]
Tooling: [e.g. dbt tests, Great Expectations, SQL checks in Airflow, Soda - or "plain SQL"]
Past incidents or known problems: [e.g. "duplicate orders after retries", "late file from vendor"]
Downstream use: [dashboards, ML models, finance reporting - and how critical each is]
Cover these test types and tell me which matter most here:
- Freshness: data arrived on time.
- Volume: row counts within an expected range vs. history.
- Schema: expected columns and types, no unexpected changes.
- Integrity: primary key unique and not null; foreign keys resolve.
- Validity: accepted values, ranges, formats, no future dates.
- Consistency: totals match source or between layers (raw -> staging -> mart).
- Business rules: e.g. "refund amount never exceeds order amount".
- Distribution drift: sudden changes in null rates or category shares.
Return:
1. A test plan table: Table | Column | Test | Threshold or rule | Severity (block / warn) | Why it matters.
2. The test code for the top 10 tests in my tooling.
3. Guidance on alert routing and who owns each failure type.
4. How to set thresholds so tests catch real problems without constant false alarms.
Keep severity honest: only block the pipeline for failures that would make downstream numbers wrong.
Highlighted [brackets] are blanks to replace with your own details.
Fill in the blanks and use this promptMore Data & Analysis prompts
What to fill in
- names and what each represents, including grain
- PASTE column names and types
How this prompt is built
This prompt follows the Few-Shot structure (Few-shot prompting). A few-shot prompt includes one or more worked examples of the input and the output you want, so the model can copy the pattern instead of guessing it.
Related prompts
- Build a simple spreadsheet to track ticket sales for an eventStep-by-step setup for a ticket sales tracker with a data entry form, search, totals, and payment status for fundraisers and community events.
- Test that a migrated report or pipeline matches the old oneA parity-testing plan for migrations that compares old and new outputs at the right level, with tolerances and sign-off criteria.
- Identify the leading and lagging indicators for my business or teamSorts your metrics into leading and lagging indicators, links each lead to the outcome it predicts, and fills gaps in early-warning measures.
- Explain a dataset or report in plain business languageExplains what a dataset or report contains, what each column or metric means, and what it shows, in words anyone can follow.