Why look for problems at all
Data quality problems rarely announce themselves. A report still runs, a chart still draws, and the figures look plausible. The trouble appears later, when a mailing goes to the wrong person, a stock count does not match the shelf, or two teams cannot agree on the number of customers.
Most data quality issues fall into a small number of families. Knowing the families makes them easier to find. The examples below use invented values for illustration.
Duplicates
A duplicate is the same real-world thing recorded more than once. It happens when customers register twice, when two systems are merged, or when staff create a new record because they cannot find the existing one.
Duplicates come in two kinds:
- Exact duplicates: identical rows, often caused by a file being loaded twice.
- Near duplicates: “J. Smith, 12 High St” and “John Smith, 12 High Street”, which are probably the same person but do not match character for character.
Simple checks
- Count rows against the number of distinct values in a field that should be unique, such as an account number. If the counts differ, something is repeated.
- Sort by name and address and scan neighbouring rows for close matches.
- Compare phone numbers or email addresses after stripping spaces and converting to lower case. Different spellings of a name often share the same contact detail.
Deciding whether two near duplicates really are the same entity can need human judgment, which is why merging records should usually be done carefully and with a way to undo it.
Missing values
Some fields are blank when they should not be: no postcode, no order date, no supplier. Others are filled with placeholders such as “N/A”, “unknown”, “0000” or a default date, which hide the gap instead of showing it.
Simple checks
- For each important field, count blanks and divide by the total rows to see what share is empty. Even a rough figure shows where the weak spots are.
- Look at the most common values in each field. If one odd value, such as 1 January 1900, appears far more often than any plausible date, it is probably a stand-in for “missing”.
- Check whether blanks cluster by source, team or time period. A sudden increase after a system change points to a mapping problem.
Not every blank is an error. An optional field may legitimately be empty. The useful question is whether the field is needed for the purpose the data serves.
Stale records
Data can be correct when entered and wrong later. People move, suppliers close, prices change and employees leave. Stale data is difficult to catch because nothing looks malformed.
Simple checks
- Record when each row was last updated, and list the oldest entries in the groups that matter most.
- Compare against an independent source, such as returned mail, bounced emails or payroll lists of current staff.
- Ask whether any field has a natural shelf life. A contact phone number might need checking more often than a date of birth.
A related problem arises when copies of the data refresh at different times, so that two reports disagree even though neither is wrong. Clear refresh times on reports reduce this confusion.
Inconsistent formats
The same information written in different ways is hard to match, sort or total. Typical cases are:
- dates written as 03/04/2026, which could be 3 April or 4 March depending on convention, and “2026-04-03”;
- country recorded as “UK”, “U.K.”, “United Kingdom” and “Great Britain”;
- units mixed within a column, such as kilograms and pounds;
- names in capitals in one system and mixed case in another;
- numbers stored as text, so “100” sorts before “20”.
Simple checks
- List the distinct values in a category field. Variants of the same value stand out in a short list.
- Check the length and pattern of fields with a fixed structure, such as postcodes or product codes.
- Sort a numeric column and look at both ends for entries that are suspiciously large, small or non-numeric.
Other patterns worth a glance
Two further problems often sit alongside the four above. Invalid values break a basic rule, for example a ship date earlier than an order date or an age of 250. Broken relationships occur when a record points to something that does not exist, such as an order referring to a customer who was deleted. Both are caught with simple rule checks: write down a statement that must always be true and count the rows that violate it.
Making the checks routine
A one-off clean-up fades quickly if new errors keep arriving. Some sensible habits are:
- Run the same few checks on a schedule and keep the results, so you can see whether things are getting better or worse.
- Trace recurring errors back to where they enter, for example a form that accepts free text where a drop-down would do.
- Give someone responsibility for fixing and for deciding what counts as acceptable. This is where data stewardship comes in, since problems found without someone to act on them tend to be found again.
- Record what you changed and why, so that a correction can be explained or reversed.
For a fuller treatment of rules, monitoring and prevention across an organization, see the separate discussion of data quality management. At small scale, a spreadsheet filter, a pivot table or a basic database query is enough to start.
A caution about perfection
No real-world dataset is perfectly clean, and chasing every last error can cost more than it returns. It is usually better to decide which fields drive important decisions, set an acceptable level of error for those, and put effort there first. Where data affects people directly, for example in billing or personal records, errors deserve more urgency.
This article is general information and not advice on any particular system.