Separate true anomalies from pipeline data errors

Classify extreme values as either legitimate business events or pipeline errors. Use it when high or low metrics distort downstream reporting.

You getClassification Table, pipeline Bug Root-Cause Checks and modeling Treatment

Prompt

Prompt
Act as a senior data quality analyst. Review the anomalous records and data summary below to separate true statistical outliers from pipeline or ingestion bugs.
- Group anomalies into two categories: Likely Pipeline Bugs (formatting, duplicate summing, default values, sensor failure) and Genuine Business Outliers.
- State the exact diagnostic signals that support each classification (such as round numbers, repeated timestamps, or mismatched foreign keys).
- Recommend an immediate filtering rule or transformation for the data model.
Output format:
1. Classification Table: Record Identifier, Value, Classification, Primary Reason
2. Pipeline Bug Root-Cause Checks: Bulleted checklist for engineers
3. Modeling Treatment: Specific filtering or imputation recommendation

The sample data and context are below:

Example output

Example for: daily order revenue anomalies in an e-commerce database

1. Classification Table
Record IdentifierValueClassificationPrimary Reason
Order #91024$999,999.00Likely Pipeline BugHardcoded default test ceiling value; customer account registered to QA team.
Order #91482$48,250.00Genuine Business OutlierValid wholesale buyer purchasing 250 bulk units with successful payment gateway settlement.
Order #92100-$12.50Likely Pipeline BugNegative total without refund flag; indicates broken tax calculation subtraction.
Order #93411$14,200.00Genuine Business OutlierVIP retail customer during flash sale; item quantities match warehouse pick lists.
2. Pipeline Bug Root-Cause Checks
  • Check whether Order #91024 originates from an internal staging IP that bypassed production filters.
  • Inspect the tax microservice logs for Order #92100 to see if coupon discounts were applied twice during currency conversion.
  • Verify if the payment reconciliation job marks test transactions with a specific metadata flag before ETL ingestion.
3. Modeling Treatment

Exclude test accounts by adding WHERE customer_type != 'internal_qa' and flag negative balances for quarantine using CASE WHEN total_amount < 0 THEN 'QUARANTINE' ELSE 'VALID' END. Do not winsorize or trim wholesale outliers; instead, split reports into wholesale and consumer segments to prevent metric distortion.

Written by AI from this prompt. Your results will vary.

Paste your suspect data rows, metric summaries, or column distributions into ChatGPT, Claude or Gemini right after the prompt. As a follow-up, ask the model for a SQL CASE statement to handle the identified cases.

Make it repeatable

Keep using this prompt without pasting it in every time.

Keep exploring

More in Data Analysis
Data Analysis

Plan an exploratory data analysis from scratch

Generate a practical exploratory data analysis plan when starting with unfamiliar data. Frame key business questions, data hygiene checks, and visualization priorities.