Skip to content
DMDQP AI Capability Hub

Data Quality Anomaly Investigation

Data Quality Anomaly Investigation

Context

Data quality anomalies — unexpected nulls, sudden metric shifts, schema mismatches — often have several plausible root causes. Systematically ruling them out before acting saves significant investigation time. This prompt takes your observed anomaly and context, and produces a ranked list of root causes with specific checks or queries to confirm or rule out each one, plus an assessment of upstream and downstream impact.

The AI cannot query your database. It will suggest what to run, not run it for you. After running the suggested checks, paste the results back in and ask: “Based on these results, what is the most likely root cause?”

The Prompt

Help me investigate a data quality anomaly. I'll describe what I observed and the context.
Please suggest:
1. The most likely root causes, ranked by probability
2. Specific checks or queries I should run to confirm or rule out each cause
3. Any upstream or downstream impacts I should assess


Anomaly observed:
[DESCRIBE WHAT YOU SAW — e.g., "12% of customer records in the daily load have null country_code
starting from the 2024-11-15 load. Previous loads had <0.1% null rate."]


Dataset: [TABLE / PIPELINE NAME]
Pipeline / source: [WHERE DOES THIS DATA COME FROM]
Recent changes: [ANY KNOWN CHANGES TO SOURCE SYSTEMS, PIPELINE, OR UPSTREAM PROCESSES]

Usage Instructions

  1. Open build-cli in your terminal.
  2. Copy the prompt above.
  3. Describe the anomaly precisely: what metric changed, by how much, starting when. Include specific numbers if you have them.
  4. Describe the dataset and pipeline, and list any recent changes to source systems, ETL code, or upstream processes.
  5. Run the suggested verification queries.
  6. Paste the query results back to build-cli and ask for a root cause assessment based on the evidence.

Example Output

The AI will produce a structured investigation plan such as:

Most likely root causes (ranked):

  1. Source system schema change (probability: High) — A new release or migration in the upstream system may have changed the column name or nulled out country_code for new records. Check: compare the source schema before and after 2024-11-15 using INFORMATION_SCHEMA.COLUMNS.

  2. ETL pipeline filter change (probability: Medium) — A pipeline code change may have introduced a condition that drops or overwrites country_code. Check: review git diff for the pipeline since 2024-11-12; look for any WHERE clause or CASE expression touching country_code.

  3. Data entry issue in source (probability: Low) — A UI change in the source application may have made country_code optional. Check: SELECT created_by, COUNT(*) FROM customer WHERE country_code IS NULL AND load_date >= '2024-11-15' GROUP BY created_by — if one user/process dominates, it’s a source issue.

Downstream impacts to assess: customer_segmentation, order_summary_by_country, tax_calculation models all join on country_code — run DQ checks on these after root cause is confirmed.

The more context you provide (exact metrics, date ranges, known changes), the better the investigation suggestions.