Handling Missing and Duplicate Data

Section 3: Data Assessment and Preparation. Lesson 15.

Handling missing and duplicate data is a critical part of data preparation:

• Missing data is information that is absent or incomplete.
• Duplicate data is multiple records representing the same real-world entity.

Both reduce data quality and can lead to migration failures, inaccurate reporting, poor decisions, operational inefficiency and reduced trust in the target system. Of the two, the lecture treats missing data as the bigger problem, since it cannot simply be merged away.

Missing data

Missing data occurs when required values are not available in the dataset. In the video game spreadsheet, several rows and cells are completely empty.

Why values go missing

• Manual data entry: the most common cause. A person recording data by hand skips fields or leaves entries out.
• Optional fields: forms that never required the value
• System integrations: fields dropped or not mapped during imports and exports
• Schema changes: columns added later, so older records have no value
• Data loss: failed jobs, truncation or corruption

Back-tracing: the reliable fix

Back-tracing is not a technical term. It means escalating a gap to the people who own the data so the true value can be recovered:

1. Report the missing values to the stakeholders or business owners, with exactly which records and fields are affected.
2. They trace each record back to its origin, such as the employee who entered it or the source system and documents.
3. They supply the correct values, which are then applied in the cleansed dataset.

For values that matter for reporting, finance or compliance, this is the only trustworthy option: the real value, from the real source.

Why not fill gaps with synthetic data?

An alternative is to give the dataset to an AI tool with a prompt like "Provide synthetic data in the missing areas." The AI learns the trends and fills each gap with plausible values it has made up.

That is not acceptable for real migrations. The values look right, but they are fake. They distort totals and reports, and they cannot be told apart from real data later. Synthetic data is useful for testing, such as populating a test environment without real customer data, but it must never be loaded into production as if it were real.

Options when back-tracing fails

Whatever option is used, mark the record, for example with an is_imputed or needs_review flag, so nobody mistakes a filled value for an original one.

Duplicate data

Duplicates occur when the same information exists multiple times, within one dataset or across several systems. The game data has little of this, but a customer sales dataset shows it clearly: one customer recorded as "Johnson", "Johnson " and "M. Johnson", each with a share of the sales history.

Causes

• Data entry mistakes: the same person or record entered again
• System mergers: two companies or systems holding the same customers
• Multiple data sources feeding the same target
• Lack of validation controls, such as no unique constraint or no duplicate check on entry
• Repeated imports or exports of the same file

Impact if migrated unresolved

• Reporting errors: inflated customer counts and split revenue
• Customer confusion: duplicate emails and inconsistent account details
• Operational inefficiency: more storage, slower processing, and problems that multiply with every re-import

Finding and resolving duplicates

1. Exact matching: identical rows or identical keys, which are easy to find with GROUP BY or drop_duplicates.
2. Normalised matching: compare after trimming spaces, lowercasing and standardising formats, so "Johnson " matches "johnson".
3. Fuzzy matching: similarity scores on names and addresses (for example Levenshtein distance), combined with a strong identifier such as email or phone.
4. Review: business users confirm uncertain matches before merging.
5. Merge with survivorship rules: decide which value wins for each field, such as the most recent address, the most complete record, or the value from the system of record. Keep a mapping from old IDs to the surviving ID so related records like orders can be re-pointed.

-- Likely duplicate customers after normalising name and email
SELECT LOWER(TRIM(email)) AS email_key,
       COUNT(*)           AS copies,
       STRING_AGG(name, ' | ') AS names
FROM customers
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1
ORDER BY copies DESC;

import pandas as pd

df = pd.read_csv("customer_sales.csv")
df["email_key"] = df["email"].str.strip().str.lower()

# Exact duplicates
exact = df[df.duplicated(keep=False)]

# Same customer, different spellings: group by the normalised key
dupes = df[df.duplicated("email_key", keep=False)].sort_values("email_key")

# Survivorship: keep the most recently updated record per customer
survivors = (df.sort_values("updated_at")
               .drop_duplicates("email_key", keep="last"))

Benefits of proper handling

• Improves migration accuracy
• Enhances data integrity
• Reduces storage requirements
• Strengthens analytics and reporting
• Increases confidence in the new system

Try it: Duplicate Customer Matcher

Nine customer records merged from a CRM and a billing system, using the lecture's names (Johnson, Clark, McHale, Rutherford). Switch between exact, normalised and fuzzy matching to see which copies each strategy catches, and change the survivor rule to see which record wins and how the ID map changes. The second panel shows one missing order total: compare leaving it null, back-tracing it, filling 0, or inventing a synthetic value, and watch what each does to reported revenue.

Key takeaways

• Missing data is the bigger problem, and manual entry is its most common cause.
• Back-trace missing values to the data owners; that is the only reliable fix for data of record.
• Never load AI-generated synthetic values into production as real data. Flag any filled value.
• Find duplicates with exact, normalised and fuzzy matching, then merge with survivorship rules and keep an ID map.

Next: 16. Data Standardization and Formatting.