2.2 Exceptions, Duplicates, and Entity Consistency: Identify Suspicious Records Rather Than Automatically Delete

Ah Hua spotted a temperature reading of 999°C and two similar orders in the incident report, but the administrator explicitly forbids turning "appears suspicious" into a deletion action outright.

A temperature of 999°C might exceed device limits, or it could be a missing placeholder; a sudden tenfold increase in payment amount might indicate an attack, or it could be part of a promotional event. Exception detection first generates investigation candidates, not direct deletion commands.

Learning Objectives

• Distinguish between invalid values, statistical outliers, and genuine anomaly events;
• Correctly apply IQR, robust scores, and group baselines;
• Differentiate between duplicate deliveries, business duplicates, and entity duplicates;
• Merge records using deterministic survivorship rules while preserving audit trails.

1. Don't Mix Up These Three Types of "Anomalies"

Category	Determination Criteria	Example	Common Handling
Invalid Values	Violates contractual or physical boundaries	Device range limit is 150, but records show 999	Isolate, mark as missing, fix at source
Statistical Outliers	Rare in relative distribution	Income in the top 0.1%	Flag, build robust models, verify
Business Anomalies	Related to defined risks or events	A single account makes 100 transfers in one minute	Investigate, trigger alerts, preserve evidence

Outliers are not necessarily errors, fraudulent activity can occur within normal ranges. Removing rare values risks losing the most valuable insights into unusual behavior.

2. Domain Constraints First

invalid_temperature = ~frame["temperature_c"].between(-50, 150)
invalid_duration = frame["ended_at"] < frame["started_at"]
invalid_status = ~frame["status"].isin({"ok", "warning", "failed"})

These rules require versioning and applicable conditions: different sensor models have varying measurement ranges, and certain clock corrections may temporarily produce negative durations. Conditions must be encoded in rule configurations and the reasons for rule matches must be logged.

Cross-field rules often provide greater value than single-column ranges: the end time must not precede the start time, completed status must have a completion time, and currency and amount precision must align.

3. The IQR Rule Only Flags Candidates

$$ IQR = Q_3 - Q_1, $$

Common fence:

$$ [Q_1 - 1.5IQR,\ Q_3 + 1.5IQR]. $$

series = frame["measurement"].dropna()
q1, q3 = series.quantile([0.25, 0.75])
iqr = q3 - q1
lower = q1 - 1.5 * iqr
upper = q3 + 1.5 * iqr
frame["measurement_iqr_flag"] = ~frame["measurement"].between(lower, upper)

The value 1.5 is conventional, not a definitive indicator of outliers. In cases of strong skewness, multimodality, or small sample sizes, the rule often produces excessive false positives. Special handling is required when $IQR = 0$.

Global baselines can mask group-level differences. When data originates from multiple devices, it's essential to first group by model, location, or operating mode. Baselines should be established only when sample sizes are sufficient and grouping doesn't lead to excessive fragmentation.

4. Time Series Anomalies Require Context

Time series data must account for:

• Trends and seasonality;
• Variations in sampling intervals;
• Device restarts and calibrations;
• Sudden changes, persistent drifts, and isolated spikes;
• Late or out-of-order records.

Rolling medians and MAD can provide robust local baselines, but the window must not include future data when used for real-time alerts. Historical retrospective analysis and live detection should be implemented separately to avoid lookahead bias.

5. Truncation and Transformation Alter the Problem

lower, upper = training["amount"].quantile([0.01, 0.99])
transformed = frame.assign(
    amount_clipped=frame["amount"].clip(lower=lower, upper=upper),
    amount_was_clipped=~frame["amount"].between(lower, upper),
)

Winsorization compresses extreme values into boundary limits. It may stabilize certain models, but it is unsuitable for auditing, risk detection, or tail estimation. Thresholds must be fitted solely from training data and original values and labels must be preserved.

Log transformation compresses right-skewed distributions, but handling of zeros, negative values, and interpretability scales remains critical. Transformation does not mean "removing outliers", it changes how the model represents the data.

6. Complete duplication is just the simplest case

exact_duplicates = frame.duplicated(keep=False)

Identical lines may arise from file concatenation, redundant exports, or (more legitimately) two independent measurements of the same value. Without an event ID or business key, one cannot reliably determine duplication based solely on identical rows.

Types of duplication:

• delivery duplicate: The same event ID is resent (re-delivered);
• version duplicate: Multiple versioned updates exist for the same entity;
• business duplicate: A user submits the same business action twice;
• entity duplicate: Different IDs actually refer to the same person or object.

Each type requires distinct handling rules and supporting evidence.

7. Deterministic Deduplication Requires Total Ordering

ordered = frame.sort_values(
    ["mission_id", "sensor_id", "event_time", "ingested_at", "event_id"]
)
latest = ordered.drop_duplicates(
    subset=["mission_id", "sensor_id"],
    keep="last",
)

Sorting solely by timestamp can produce unstable results when events occur at the same time. To ensure determinism, include a unique event ID as a tiebreaker. The choice of "latest" must also respect semantic meaning: event facts may need to be preserved entirely, and only the current state table should select the latest version.

The deduplication output should include:

• a canonical record ID;
• a list of merged records;
• the version of the deduplication rule applied;
• the merge timestamp and run ID;
• field-level source information.

This enables traceability and explanation when rule changes cause certain records to disappear.

8. Entity Resolution Is Not drop_duplicates

Similar names or addresses do not prove that two entities are the same. Entity resolution may employ normalization, blocking, similarity scoring, and manual review, and must evaluate both false merges and false splits.

Merging two distinct real users is typically riskier than missing a merge, especially in domains like finance, healthcare, or access control. Thresholds should be set based on business cost, rather than to maximize overall accuracy alone.

Normalization must be applied with care: standardizing case, whitespace, and Unicode forms can aid matching, but such transformations should not be applied indiscriminately to fields that require original preservation or carry regional semantics. Keep the original value intact and maintain a separate normalized key.

Common Misconceptions

• Values outside the IQR are errors: They are merely candidate outliers in the distribution, not necessarily erroneous data.
• Truncation does not affect conclusions: Tail risks, means, and regression relationships all change with truncation.
• Identical rows across a line always indicate duplication: Without event identity, it's impossible to distinguish between duplicate deliveries and two identical observations.
• Keeping the latest record is always correct: Event tables and current-state tables have different semantic meanings, so this rule does not universally apply.

Exercise

1. Write invalid value, outlier, and business alert rules for temperature data.
2. Construct an example where a global IQR rule incorrectly flags normal data from a specific device model.
3. Design a deterministic tie-breaker for two version records that occur at the same timestamp.
4. Define the business costs associated with false merge and false split in entity resolution.

Summary

Exception rules answer "what warrants investigation," contract rules answer "what cannot be legally valid," and deduplication rules answer "which records represent the same fact." These three types of rules cannot be replaced by a single drop operation. Every transformation must preserve the original value, the reason for the transformation, and the rule version.

In the next lesson, we'll assemble these individual rules into a reproducible data cleaning pipeline and validate the cleaning process using invariants, isolation zones, and diff reports to ensure no unintended data corruption has occurred.