Data cleaning might be the least glamorous part of AI implementation. It's not flashy. It won't make headlines. But it's essential.

In fact, most AI projects spend 70-80% of their time on data cleaning and preparation, and less than 20% on model building. The technology is easy. The data work is hard. And it's the hard work that determines whether your AI actually delivers results.

This lecture covers practical techniques you can use—or help a contractor use—to clean your data and prepare it for AI systems. By the end, you'll understand the main cleaning challenges and the strategies for solving them.

The Data Cleaning Funnel

Think of data cleaning as a funnel. You start with messy, real-world data. As it flows through the cleaning process, problematic records and fields are removed or fixed. What comes out the other end is data clean enough for AI to work with effectively.

The funnel has four stages:

Stage 1: Duplicate Detection and Removal

Real business data accumulates duplicates naturally. A customer registers twice, using slightly different spelling of their name. A vendor is entered multiple times across different systems. A transaction is recorded in two places.

Duplicates cause several problems for AI. They skew pattern recognition by overweighting certain customers or transactions. They inflate metrics—you think you have 10,000 customers when you really have 8,000. They complicate data linking across systems.

Exact duplicates are easy to identify: identical records across all fields. Most systems (Excel, SQL databases, commercial tools) can find and flag these automatically. The hard part is deciding what to keep. If you have two identical customer records, delete one and keep the one with better data in other fields.

Fuzzy duplicates are harder. "John Smith" and "Jon Smith" might be the same person. "123 Main Street" and "123 Main St" are the same address formatted differently. "Widget 2000" and "Widget 2K" might be the same product.

Finding fuzzy duplicates requires either manual review or sophisticated matching algorithms. For small-to-medium datasets, you can use tools like Google Sheets' built-in duplicate finder or Excel's features. For larger datasets, you might use commercial data quality tools or hire help.

Stage 2: Handling Missing Data

Missing data is one of the most common data quality problems. A customer record without an email. A product entry without a category. A transaction without a customer ID.

How you handle missing data depends on three factors: how much is missing, why it's missing, and how important the field is.

Missing Data Scenario	Your Options	Best For
A few missing values (less than 5% of records)	Delete those records, or manually fill in the missing data	When the field is critical and only a small number of records are affected
Systematic missing data (e.g., only old records lack a field)	Investigate why it's missing (was the field not collected before?). Fix the process going forward. For historical records, delete or mark as incomplete	When missing data reflects a process change or system migration
Many missing values (more than 30% of records)	Consider whether the field is valuable. If not, delete it. If it is, investigate why so much data is missing before proceeding	When data collection is optional or inconsistent
Predictable missing data (patterns exist in related fields)	Use AI to predict missing values based on other fields. For example, predict missing product category based on product name	When missing values can be inferred from other data in the record

One advanced approach: use AI to help fill missing values. If you have customer records where some are missing phone numbers but you have names, email addresses, and purchase history, a machine learning model can be trained on the complete records to predict missing phone numbers for incomplete ones. This requires technical skill but can be worth it for important fields.

Stage 3: Format Standardization

One of the most pervasive data quality problems is inconsistent formatting. Phone numbers stored as "(555) 123-4567," "555-123-4567," "5551234567," and "+1 555 123 4567" are all the same value formatted four different ways.

This causes multiple problems: search and matching become difficult, duplicates aren't recognized, database validation fails, and AI models may not learn patterns correctly if the same data is represented inconsistently.

Common formatting problems:

• Phone numbers: Different separators, different lengths (with or without country code), parentheses, spaces
• Addresses: "Street" vs "St," "Avenue" vs "Ave," "North" vs "N," direction abbreviations, capitalization
• Dates: MM/DD/YYYY vs DD/MM/YYYY vs YYYY-MM-DD, two-digit vs four-digit years
• Currency: $1,234.56 vs 1,234.56 vs $1234.56
• Names: "John Smith" vs "smith, john" vs "JOHN SMITH," missing middle names or initials
• Categories: "Office Supplies" vs "office supplies" vs "Office_Supplies," inconsistent naming

Standardization usually means choosing a format and converting all variations to that format. For phone numbers, you might standardize to "(XXX) XXX-XXXX." For dates, pick ISO 8601 format (YYYY-MM-DD) which works universally. For categories, define approved values and map all variations to them.

In Excel or Google Sheets, you can use formulas to standardize. For example, removing all non-numeric characters from phone numbers, converting all text to lowercase, or parsing dates into a consistent format. For larger datasets, Python scripts or commercial tools automate this work.

Stage 4: Validity and Consistency Checks

After removing duplicates, handling missing data, and standardizing formats, check for logical validity. Does a birth date in the future make sense? Is a transaction amount negative when it should be positive? Does a customer's age conflict with their birth date?

These logical checks catch data entry errors, system errors, and bizarre edge cases. A product price of "$0.00" might be an error. A customer age of "999" is probably someone's placeholder for unknown.

For each field, ask:

• What's the valid range? (For ages: 18-100? For prices: greater than $0?)
• What values are impossible or unreasonable?
• Are there fields that should relate to each other? (Address and ZIP code should match? Transaction date should be after customer creation date?)

Flag records that fail these checks. Sometimes they're legitimate edge cases. Sometimes they're errors to fix or records to delete. Manual review of flagged records is usually needed.

Data Cleaning in Practice: Three Common Scenarios

Let's walk through how cleaning actually works for the data types most businesses care about.

Scenario 1: Cleaning Customer Data

Your CRM has 50,000 customer records accumulated over five years. You're implementing AI for customer communication and retention prediction. Here's the cleaning process:

Step 1: Duplicate Detection—Find customers entered twice. Your tool identifies 8,000 potential duplicates (16% of records). Manual review reveals 3,000 actual duplicates you merge, and 5,000 that are different people with similar names you keep.

Step 2: Missing Data Analysis—Check completeness. You find 60% have phone numbers, 95% have email, 75% have postal addresses. Email is mostly complete (missing 5%) so you keep it. Phone is 40% missing—you decide it's optional but flag records without it as "incomplete contact info." Addresses missing for 25%—you investigate and discover very old records don't have addresses. You don't delete the records but mark them as legacy data.

Step 3: Format Standardization—Phone numbers are chaos: different formats, some with country codes, some without. You standardize all to "(XXX) XXX-XXXX" format. Addresses use different abbreviations. You standardize to full words: "Street" not "St," "Avenue" not "Ave." Names are mixed case—you standardize to "First Last" format.

Step 4: Validity Checks—Flag records where creation date is after most recent purchase date (illogical). Flag customers with a "purchase date" more than a year in the future (data entry error). Flag email addresses that don't contain "@" symbol. These flags identify maybe 500 records for manual review.

Result: You now have 47,000 clean customer records (3,000 deleted as duplicates) ready for AI, with clear understanding of what's complete and what's missing.

Scenario 2: Cleaning Sales Data

Your sales data is messier because it comes from multiple systems—some sales recorded in Salesforce, some in older systems, some in spreadsheets. You want to use AI for sales forecasting.

Step 1: Consolidation and Deduplication—Different systems use different deal IDs. Some deals appear in multiple systems with slightly different amounts. You need to identify and merge these. This requires matching deals across systems by customer name, date, and amount. Fuzzy matching tools help identify duplicates that have minor variations.

Step 2: Standardizing Stage Names—Different systems call sales stages by different names: "In Negotiation" vs "Negotiating," "Close/Won" vs "Won" vs "Closed." You map all variations to a standardized set of stages.

Step 3: Historical Data Quality—Very old sales data (5+ years old) might have inconsistencies or missing key fields. You decide whether to include it (historical patterns might be valuable) or exclude it (if data quality is too poor). Often you include it with flags noting its age and quality level.

Step 4: Consistency Checks—Deal amounts should be positive. Closing dates should make logical sense: a deal can't close before it opened. Sales rep assignments shouldn't change after close. You flag violations for review.

Result: You have clean, consolidated sales data from multiple systems with standardized categories, ready for forecasting AI.

Scenario 3: Cleaning Product Catalog Data

Your product database is used for recommendations and inventory. It's a mess because products are added by different people with different standards, and you've acquired competitors whose product data uses different schemas.

Step 1: Consolidating Categories—You have products categorized as "Office Supplies," "office supplies," "supplies," "SUPPLIES," "Office," etc. You define a master category list and map all variations to it.

Step 2: Handling Missing Data—Some products lack descriptions, images, or prices. For newer acquisitions, nearly all lack internal tags and notes. You decide: products without prices or descriptions shouldn't be sold—delete or suppress them. Missing tags are acceptable; you backfill them gradually over time.

Step 3: Standardizing SKUs and IDs—You have multiple ID systems from different companies. You keep all IDs but standardize how they're formatted and add mappings between them so different systems can reference the same product.

Step 4: Validity Checks—Product prices should be positive. Current inventory counts should be realistic. Creation dates shouldn't be in the future. You flag violations for quick fixes.

Result: A unified product catalog where different ID systems coexist, categories are standardized, and critical fields are complete.

Tools for Data Cleaning

You have options depending on your technical skill, budget, and data volume.

Using AI to Help Clean Data

Interestingly, you can use AI to help with data cleaning—particularly large language models. Here's how:

Suggesting Cleaning Strategies—Describe your data problem (inconsistent address formats, missing product categories) to your AI tool. It can suggest standardization approaches and even write formulas to implement them.

Writing Cleaning Code—If you have Python or SQL knowledge, AI can help write cleaning scripts. Describe the transformations you need and the model can generate code to accomplish them.

Pattern Recognition—AI can help identify patterns in errors or missing data. "Here are 100 product entries missing descriptions. Can you identify what they have in common?" Often reveals systematic collection issues.

Validation Rules—AI can help design business rules for detecting invalid data. "What validity checks should we apply to customer birth dates?" generates thoughtful rules you might not think of yourself.

However, AI works best with clear, specific instructions. Vague requests like "fix my data" won't work. Clear requests like "I have phone numbers in multiple formats. Show me how to standardize them to (XXX) XXX-XXXX in Excel" will produce useful results.

What You'll Learn Next

You now understand how to assess and clean your data. But cleaned data alone isn't enough—you also need to protect it. The next lecture covers: . You'll learn data privacy principles, anonymization techniques, and compliance considerations that ensure your data is protected while being useful for AI.