Investigate Data Quality

English | 中文

Investigate data quality incidents with a fixed, evidence-first sequence. Keep four failure modes separate throughout the analysis:

• Non-null key duplicates: two or more rows share the same expected unique key, and every key component is non-null.
• Null keys: one or more expected key components are null. These rows can make COUNT(*) - COUNT(DISTINCT key) look like duplicate volume because COUNT(DISTINCT ...) ignores nulls.
• Join amplification: inputs may be valid at their own grains, but a one-to-many or many-to-many join produces multiple output rows for one left-side entity.
• Incorrect grain definition: the proposed key is incomplete or the expected row meaning is wrong. Multiple rows are valid at the actual grain and must not be labeled duplicates.

Do not collapse these into a generic "duplicate data" conclusion.

Required Investigation Order

Follow every stage in order. Do not skip directly to deduplication.

1. Confirm the Symptom and Time Range

Record the affected dataset, observed metric or invariant, expected behavior, first reported time, suspected partitions, and comparison baseline. Reproduce the symptom with a partition-bounded query. State whether the anomaly is missing rows, extra rows, duplicate-looking rows, null keys, or a changed aggregate.

If the symptom cannot be reproduced, report the exact query, bounds, and remaining uncertainty before continuing.

2. Define Grain and Expected Key

Write one sentence describing what one row represents. Name the complete candidate unique key, including date, tenant, version, or sequence columns where required. Confirm the definition from transformation logic, schema contracts, or consumer expectations.

If no defensible grain exists, treat that as a grain-definition problem. Do not use an assumed key to claim duplicates.

3. Separate Null Keys from Non-Null Duplicates

For the bounded interval, measure total rows, null-key rows, distinct non-null keys, duplicated non-null key groups, and excess rows within those groups. Inspect a small duplicate sample.

Run null checks and duplicate checks separately. A difference between total rows and distinct keys is not sufficient evidence of non-null duplicates. Use the templates in references/sql-checks.md.

4. Validate Join Input Cardinality

List joins in execution order. For each join, state the expected relationship (one-to-one, many-to-one, one-to-many, or many-to-many) and the grain of both inputs. Check key multiplicity on both sides within the same bounded partitions before evaluating output counts.

An input can be unique on its declared key but non-unique on an incomplete join key. Validate the exact join columns, including null behavior and type normalization.

5. Locate the First Amplifying Step

Measure row count and stable entity count before and after each filter, union, aggregation, window, and join. For joins, compare:

• left and right input row counts;
• distinct join keys on each side;
• unmatched left rows;
• output row count;
• output count per stable left-side identifier.

The first step where counts violate the declared grain is the introduction point. Do not blame the final dataset merely because that is where the anomaly was detected.

6. Trace the First Abnormal Partition

Compare partition-scoped quality metrics over a bounded date range. Narrow the transition interval by checking a midpoint partition, then recurse into the half containing the first bad result. Stop at the earliest anomalous partition and correlate it with input availability, code versions, schema changes, and upstream quality changes.

Use binary search only when partitions are ordered and the condition remains bad after it first appears. If quality can recover or fluctuate, scan bounded partition summaries instead.

7. Assess Downstream Impact

Identify downstream datasets and metrics that consume affected partitions or keys. Quantify the impacted partition range, key count, row count, and conserved measures where possible. Distinguish direct duplication from aggregate overstatement, missing matches, and schema or grain incompatibility.

Do not execute backfills or writes. State which downstream artifacts may require revalidation or recomputation after the source fix.

8. Propose the Fix and Verify It

Fix the earliest demonstrated cause:

• repair or reject invalid null keys;
• restore uniqueness using business-correct deterministic selection;
• pre-aggregate or constrain a one-to-many join to the intended relationship;
• correct the grain, key, aggregation, or consumer contract.

Do not apply a final DISTINCT as a default remedy; it can hide the cause or discard valid rows.

Verify the fix on a small affected partition and a known-good control partition. Repeat the same grain, null, duplicate, join-cardinality, and measure-reconciliation checks. Confirm downstream invariants and rerun safety before proposing a wider recomputation.

Evidence and Output

Use this report structure:

## Summary
Dataset, bounded interval, confirmed symptom, and root-cause category.

## Grain and Key
Expected row meaning, complete key, and evidence for that definition.

## Evidence
Null-key counts, non-null duplicate groups, join cardinalities, and step counts.

## First Introduction Point
Transformation or join where the invariant first fails.

## First Abnormal Partition
Earliest affected partition, search method, and boundary evidence.

## Downstream Impact
Affected datasets, partitions, keys, rows, and measures.

## Fix and Verification
Cause-level fix, bounded checks, control partition, and residual risks.

## Unknowns
Facts that still require confirmation.

Label estimates and assumptions. Never invent row counts, uniqueness guarantees, grains, or lineage.

See references/examples.md for fictional worked investigations.

Related Skills

• Static Spark/SQL/pipeline diff review: review-data-pipeline.
• YARN runtime failure, kill, or stall (not data semantics): debug-spark-yarn.
• After the cause is known, plan a bounded historical rerun: plan-safe-backfill.
• Document the production fix for review or release: prepare-production-data-change.

Safety

Investigation is read-only unless the user separately authorizes a change. Large tables must always be queried with an explicit partition or similarly selective bound; never default to a full-table scan. Start with summary counts, limit samples, and explain expected query cost. Do not run repairs, deletes, overwrites, backfills, or downstream recomputations. Redact credentials, connection strings, personal data, and proprietary values from evidence. This first version provides only portable methodology and SQL templates; it does not require or implement any platform-specific client or connector.