Sunitha Krishnan had been at her company for three years when she inherited the customer dataset from a merger. On paper, the combined database had 240,000 customer records, exactly the scale the analytics team needed for their AI churn model. Three weeks into cleaning, the picture looked different: roughly 18,000 duplicate records, date fields in four different formats, a "region" column that contained country names in one system and city codes in the other, and about 9% of revenue figures entered in thousands in the legacy system but units in the new one. "We had a lot of data," she said. "We didn't have clean data. Those aren't the same thing."

You assess data quality first and discover the problems; cleaning is where you fix them. It is detailed, systematic, unglamorous work, and it is also where AI projects live or die. The old saying in data science is "garbage in, garbage out." A more honest version is "garbage in, garbage out, after spending weeks on analysis," because the cost of skipping this stage is almost never paid at the time. It is paid later, by a team debugging a model built on foundations nobody checked.

Document Before You Touch Anything

Before changing a single record, document what your quality assessment found and what you intend to do about it. This is not optional. It is the difference between a cleaning process that can be reproduced, audited and explained, and one that exists only in one person's head. A cleaning decision log should record, for each issue: the field or set of records affected, the problem observed, the business logic you applied to resolve it, and the date and person who made the decision. When a compliance team asks six months later why certain records were excluded, or a data scientist notices an anomaly, that log is the only way to reconstruct what happened.

Be specific about what gets logged. When you delete duplicates, record which fields constituted identity for that decision. When you impute missing values, record the method used and the percentage of the field imputed. When you standardise a category, keep the mapping table itself. Think of the cleaning log as the provenance trail for your dataset: just as a financial audit trail shows every transaction and who authorised it, your log shows every transformation and why it was made. It is what a data governance team will ask for first, and producing it after the fact is far harder than keeping it as you go.

Deduplication: Identifying and Removing Duplicate Records

Duplicates skew every analysis they appear in. A customer who appears twice looks like two customers. A transaction recorded twice inflates revenue figures. A product defect logged twice makes defect rates appear higher than they are. The damage compounds when the data feeds a model: a database holding 100,000 records that actually represents 85,000 unique customers with 15,000 duplicates teaches the model that the duplicated examples are more common than they are, and it overfits to exactly those records.

Exact duplicates, where every field matches another record perfectly, are the easy case. Most databases and cleaning tools detect them automatically. The only real decision is which copy to keep: the first occurrence, or the most recent by timestamp. Either rule is defensible; what matters is choosing one and recording the choice. Near duplicates are harder. A customer recorded as "John Smith" at "123 Main St, Boston MA" may also appear as "Jon Smith" at "123 Main Street, Boston Massachusetts." Almost certainly the same person, and not an exact match on any field. "Sunitha Krishnan" and "S Krishnan" at the same address raise the same question, as do "ABC Logistics Pvt Ltd" and "ABC Logistics." Deciding these cases requires business logic, not just a matching algorithm.

Fuzzy matching handles near duplicates by comparing fields with string similarity metrics rather than equality. If two records have names that are 95% similar and addresses that are 90% similar, they are probably the same person. Set the thresholds using domain knowledge, and start conservative: require 99% similarity at first and relax gradually if you find you are missing obvious duplicates. A practical deduplication run then proceeds in four steps.

• Identify key fields. Which fields define uniqueness in this dataset? For customers it is typically name and address plus one of phone, email or account number. For transactions it is usually date, amount and account. For products it is a SKU or UPC.
• Run exact match. Use database tooling to find and remove records that match exactly on the key fields. Clear these first so the harder work runs against a smaller set.
• Run fuzzy match. Apply similarity matching to the same key fields at a high threshold, then manually review the matches above it before deleting anything.
• Document and validate. Record every duplicate removed, with counts and methods, then verify that the remaining records really are unique.

Sunitha's 18,000 duplicates show why the manual review step is not optional. About 14,000 were exact matches created by the system migration and cleared automatically. The remaining 4,000 needed fuzzy matching on name and address. Of those, 2,400 turned out to be genuine duplicates. The other 1,600 were different people with similar names, and a blind deletion at the fuzzy threshold would have destroyed 1,600 real customer records.

Handling Missing Values

Missing values are universal in real datasets. The question is not whether you have them, but what to do about them, and the answer differs field by field. A customer database might carry nulls in 25% of phone fields, 40% of secondary address fields and 5% of email addresses; each of those calls for a different response. Before choosing one, understand why the values are missing at all. Statisticians divide missingness into three patterns: Missing Completely At Random, where the gaps follow no pattern; Missing At Random, where missingness relates to other fields; and Missing Not At Random, where the fact of the value being absent carries information in itself. A phone number is often the third case, because customers who decline to give one may differ systematically from those who do.

Deletion. Remove records where values are missing in critical fields. This is appropriate when the field is essential to the analysis, when missing values affect only a small share of records, and when those records are not systematically different from the rest. If 1% of records have a gap, deletion is reasonable; if 30% do, deletion throws away most of your dataset to fix a minority problem. Watch the pattern as well as the rate: if the missing records cluster in a particular region or time period, deletion introduces exactly the representation bias you are trying to avoid.

Imputation. Fill in missing values from the information you do have. Mean or median substitution is the simplest method, replacing gaps with the average or the midpoint of the observed values; it is quick and it can flatten the distribution. Forward fill carries the previous value forward and suits time series where values change gradually. Regression imputation builds a small model to predict the missing value from other fields, which is more accurate and more work. Domain-based imputation uses knowledge of the subject, such as filling a missing age from the population average for that region or occupation. Sunitha's case called for none of these: her revenue gaps existed because the legacy system used a different format, so she looked the values up against the original source records. That is source correction, not statistical estimation, and it is always the better option when it is available.

Flagging. Keep the record and add a binary field marking that the original value was missing. This preserves information that imputation destroys, because missingness itself is sometimes predictive: a customer with no phone number on file may behave differently from one with a number. The model can then be designed to handle flagged values explicitly rather than treating them as zeros or averages, and for genuinely unknown values it is the most honest option available.

Imputation works well when missingness is random and the proportion imputed is small. When you impute more than 15 to 20% of the values in a field, the imputed values start to dominate the patterns in it, and you have crossed from estimation into manufacturing data. Be transparent about what you imputed and how, and consider flagging heavily imputed fields so downstream analysts know what they are working with.

Standardisation: Bringing Consistency to Messy Fields

Standardisation is the work of making the same thing look the same across the entire dataset. It sounds trivial and it is the most labour-intensive cleaning task, because it requires understanding what the values in a field are supposed to mean. It matters more for AI systems than for human readers: a phone number written as (555) 555-5555, as 555-555-5555 and as 5555555555 carries identical information to you and three distinct values to a model, which reads format as information.

Dates are the classic case. "01/02/2024" means January 2nd in the United States and February 1st in most of the world. "2024-02-01" is unambiguous because ISO 8601 fixes the order as YYYY-MM-DD. "1 Feb 24" is informal but readable. All three might appear in the same column. Standardising them requires deciding, with business input, which date each record actually refers to, not merely reformatting the string. The same discipline applies to the rest of your text fields: use a single postal convention for states and regions, such as USPS abbreviations where CA replaces California; standardise name capitalisation to one convention; and handle titles and spacing consistently so that "Dr." and "Dr" stop being two different people.

Category fields are equally tricky and more insidious, because the variants look correct individually. "Tamil Nadu," "TN," "Tamilnadu," and "TAMIL NADU" are all the same region, but a system treating them as strings counts four separate values. A product catalogue might list "iphone15", "iPhone 15", "iPhone15Pro" and "iphone 15" as four separate products. The fix is a controlled vocabulary, meaning a definitive list of valid values, plus a lookup table mapping every observed variant to its canonical form. Build the lookup table as a file you keep, not as a one-off script, because new variants arrive with every new data source.

Numeric fields need consistent units and precision. Prices should sit in a single currency with exchange rates documented. Weights should use one unit rather than pounds in some rows and kilograms in others. Percentages should use one scale, either 0 to 100 or 0 to 1, never both in the same column. One practical rule governs all of this: standardise to the most specific unambiguous format you can. For geographic units, use the most granular level your analysis requires. Aggregating to a coarser level later is straightforward; disaggregating after the fact is usually impossible.

Outlier Detection and Treatment

Outliers are values that fall far outside the normal range. A customer with a $1,000,000 purchase in a dataset where typical purchases run to $100 is an outlier. An employee recorded as working 200 hours in a week when the maximum is 40 is an outlier. They may be genuine, such as a very large customer or a record-breaking quarter, or they may be errors, or in some domains they may be fraud. Before treating an outlier, determine which it is.

Statistical methods find candidates quickly. The 3-sigma rule flags values more than three standard deviations from the mean, which catches obvious errors. The interquartile range method flags values below Q1 minus 1.5 times the IQR or above Q3 plus 1.5 times the IQR. Both work well on roughly normal distributions and considerably less well on skewed ones, where a long tail is a genuine feature of the data rather than a defect. Domain rules catch what statistics miss: a salary above $1,000,000 may warrant a flag for most job families, employee hours above 60 per week may trigger review, and a customer spending 10 times their average order value may be worth verifying. A revenue figure of ₹1,200,000 in a customer dataset might be entirely correct for a large enterprise account and clearly wrong for a small retail one, and only the business context tells you which.

Once you know what an outlier is, there are four ways to treat it. Keep it as-is when it is real and valid, because removing real data to make a distribution look tidier distorts the analysis in the other direction. Cap or floor it when the extreme is implausible but the record is otherwise sound, for example capping salaries at the 99th percentile or setting a floor of 18 on ages in employment records. Remove it when it clearly represents an error or fraud and the correct value cannot be recovered. Or flag it, adding a binary field marking the record as an outlier and letting the model learn how to handle it. Never delete a value merely because it is large or unusual.

Quality Control During Cleaning

Cleaning creates its own risks. You can accidentally delete valid records, impute incorrect values, or introduce errors through a standardisation rule that is almost right. Almost right is the dangerous case, because it mis-converts a small number of records quietly rather than failing loudly. Quality control during cleaning is therefore as important as quality control of the original data, and it needs to run after each step rather than once at the end.

Four checks catch most problems. Track row counts before and after every step, since a large unexplained drop points straight at faulty cleaning logic. Compare value distributions before and after: if you imputed with means, the distribution should look similar with less variance, and anything else means the imputation did not do what you thought. Sample 50 to 100 cleaned records at random and read them, looking for the obvious errors and concerning patterns that automated checks never catch. And validate a sample against the source system, because if ten sampled records all reconcile against the original, you probably did not introduce systematic errors.

Sunitha's revenue field shows why the last check matters. Legacy values were in thousands and new system values were in units, so the conversion rule was to multiply legacy records by 1,000. She sampled twenty converted records and compared them against original invoices. Nineteen matched. One had been entered in units already, an exception in the legacy system nobody had documented. A seemingly simple fix carried an exception that only sampling exposed, and applying it blind would have inflated that customer's revenue by the full multiplier.

Documenting Data Lineage

Data lineage is the documented path showing how data flowed from source, through cleaning, to final form. It extends the cleaning decision log into a single artefact covering the original data source and its quality assessment, every cleaning step applied with dates and responsible party, the business logic used for each class of issue, and the characteristics of the final output. Maintained properly, it lets you explain to stakeholders what was done and why, reproduce the process when the source data refreshes, debug the pipeline when someone finds a problem downstream, defend your decisions when governance teams challenge them, and train others on procedures that would otherwise live only in your head.

Anti-Patterns

• Cleaning first and documenting later. The decisions are obvious while you are making them and unrecoverable six months on. The log has to be written as you work, not reconstructed afterwards.
• Blind fuzzy deletion. Trusting the similarity threshold without human review. Sunitha's 1,600 similarly-named but distinct customers sat above it.
• Imputing without asking why. Filling gaps statistically when the values are recoverable from the source system, or when the missingness itself is the signal you should be modelling.
• Reformatting instead of standardising. Rewriting date strings into one format without establishing which date each record actually meant, which produces consistent and wrong data.
• Treating outliers as errors by default. Trimming the tail because it is inconvenient, which removes the largest customers from the analysis of what large customers do.
• Checking only at the end. Running quality control once after the whole pipeline, so that when the row count is wrong you have no idea which step caused it.

Practice Prompts

• Write the cleaning decision log entry for a single field of a dataset you already handled, covering the problem, the business logic and the person responsible. Note how much you had to reconstruct from memory.
• Identify the key fields that define uniqueness in your main dataset, then run an exact-match check and count what comes back.
• Pick a field with missing values and classify the missingness as Missing Completely At Random, Missing At Random or Missing Not At Random. Then decide whether deletion, imputation or flagging follows from that classification.
• Build a lookup table for one messy categorical field, mapping every observed variant to a canonical value, and keep it as a file rather than a script.
• After your next transformation, sample records against the source system and read them line by line. Record how many reconciled.

Reflection

Think about the last dataset you handed to a model or an analyst. Could someone else reconstruct what you did to it, in what order and why, from documentation alone? If not, the dataset carries a hidden dependency on you that will surface at the least convenient moment. Consider too where your instincts sit on the deletion, imputation and flagging choice: most practitioners have a default they reach for regardless of why values are missing, and identifying yours is the first step to choosing deliberately. Then ask what you would do if a compliance team asked tomorrow to see the business logic behind every excluded record.

Glossary

• Fuzzy matching. Comparing records with string similarity metrics rather than equality, so that near duplicates can be found and reviewed.
• Missing Completely At Random. Missingness with no pattern relating it to any field.
• Missing At Random. Missingness that relates to other observed fields, so the gaps can often be estimated from them.
• Missing Not At Random. Missingness that carries information in itself, where the absence of a value is a signal worth preserving.
• Missing indicator. A binary field recording that the original value was absent, preserving information that imputation would erase.
• Controlled vocabulary. A definitive list of valid values for a categorical field, paired with a mapping from every variant to its canonical form.
• Data lineage. The documented path of data from source through every transformation to final form, including who made each decision and when.

Related Lessons

This lesson picks up where Data Quality Assessment leaves off: that lesson tells you what is wrong with your data, and this one tells you what to do about it. The natural next step is Structuring Data for AI, which covers converting unstructured data into AI-ready formats, designing effective schemas, and preparing data in the shapes that different systems and use cases require, since clean data in the wrong structure is still unusable. Privacy-Preserving Data Preparation covers the constraints that run alongside all of this, because several of the cleaning decisions described here, particularly around identity fields and record-level review, touch personal data directly.

Closing

Cleaning is the bridge between a quality assessment and an AI-ready dataset. Deduplication removes records that would distort the analysis and teach a model the wrong frequencies. Missing value handling decides, field by field, whether to delete, estimate or flag. Standardisation makes identical information look identical to a system that reads format as meaning. Outlier treatment separates real anomalies from errors instead of trimming both. Running through all four is the documentation discipline that makes the work defensible and the quality control that stops cleaning from becoming a new source of error. None of it is glamorous, and all of it pays back in more reliable models, faster development and fewer surprises in production, which is the trade Sunitha's three weeks bought for her team.

Key Takeaways

• Document every cleaning decision as you make it. The cleaning log is the provenance trail for your dataset and the only way to answer an audit six months later.
• Fuzzy duplicates require human review, not blind deletion. Similar-looking records may be one entity or several, and only business logic decides which.
• Understand why values are missing before choosing what to do. The three missingness patterns point to different remedies, and source correction beats statistical estimation whenever the source is available.
• Imputing more than 15 to 20% of a field crosses from estimation into manufacturing data. Be transparent about the rate and flag heavily imputed fields.
• Standardisation requires a controlled vocabulary and a mapping table, not just reformatting. AI systems read format as information, so inconsistent formats become spurious distinctions.
• Outliers are not automatically errors. Investigate first, then keep, cap, remove or flag, and never delete a value simply because it is large.
• Cleaning introduces its own errors. Check row counts and distributions after every step, sample cleaned records by hand, and reconcile a sample against the source system.

Frequently Asked Questions

How do I choose a fuzzy matching threshold? Start conservative, at a high similarity requirement, and relax it only if you are visibly missing duplicates you can see by eye. The right threshold depends on the field and the domain, so it is set by testing against records you already understand, not by copying a number from another project.

Should I clean the data or fix the source system? Both, in that order. Cleaning gets the current dataset usable; a note back to the source system owner stops the same problem arriving with every refresh. Rules that quietly compensate for an upstream defect year after year are how pipelines become unmaintainable.

How much cleaning is enough? Enough that the remaining defects are documented and understood rather than absent. Perfect data does not exist. What matters is that anyone using the dataset knows which fields were imputed, at what rate, and which records were excluded and why.