# 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.

- Topic: Data & Analysis
- Framework: Few-Shot
- Level: Standard
- Tags: Sales, Data & Analytics, Fill-in Template
- Source: https://www.thinkwithprompt.com/prompts/design-automated-data-quality-tests-for-a-pipeline

## Prompt

```text
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.
```

## What to fill in

- names and what each represents, including grain
- PASTE column names and types

Free to copy and use. From ThinkWithPrompt (https://www.thinkwithprompt.com/), a library of 2,663 curated AI prompts.