When the data is missing, late or wrong: what to do before and during a project

Almost every data-driven project meets the same moment. The sample file that was sent in the first week looked fine. Then the full data arrives, and it is a different story: dates in three formats, districts spelled five ways, a column that means different things in different years, and months of numbers that simply never came in.

This is normal, not a sign of a bad organisation. Real data reflects real people working under real pressure. The question is what you do about it. This article describes a practical approach, from a first audit to rules for living with imperfect data once the system is running.

Step 1: audit before you promise anything

Ask for a real extract of the data, not a polished example, and look at it in the first week. A simple audit answers a handful of questions.

• Completeness: how much is missing, and is it missing at random or in patterns, such as certain regions or months?
• Consistency: are names, codes, units and date formats the same everywhere?
• Accuracy: do values fall in believable ranges, and do they agree with another source?
• Timeliness: how long after an event does the data arrive?
• Coverage: does it include the places, people and periods that matter for the decision?
• Meaning: does everyone agree on what each field means?

Write a short audit note with examples. It sets honest expectations, and often reveals fixes that cost very little.

Step 2: sort the problems by cause

• Entry errors: typos, wrong units, misplaced decimals. Often fixable with checks at the point of entry.
• Process gaps: nobody is responsible for submitting data, or the form does not match the work. Needs a process fix.
• System differences: two systems define the same thing differently. Needs an agreed definition.
• Genuine absence: the data was never collected. Needs a collection plan or a different approach.
• Delay: the data exists but arrives late. Needs a schedule and reminders.

Treating the symptoms downstream, with ever more cleaning code, is expensive and fragile. Wherever possible, fix the cause.

Step 3: agree rules for the hard cases

Before building screens and models, decide what the system will do in each awkward case. These decisions belong to the people who own the data and the decisions, not only to the developers.

• Missing value: leave blank and show it, estimate it and label it clearly, or exclude the record?
• Late data: show the last known value with its date, or flag the area as out of date?
• Suspicious value: accept it, hold it for review or reject it?
• Conflicting sources: which one wins, and why?

Step 4: make gaps visible

A system that hides data problems teaches people not to look at them. A system that shows them turns them into a to-do list.

• Show the date of the last update next to every figure.
• Mark estimated or corrected values, and say how they were produced.
• Give each data owner a simple view of what they have and have not submitted.
• Report quality over time, for example the share of districts that reported on time.

In our experience, this single habit improves data quality more than any amount of retrospective cleaning, because the people who supply the numbers can see that someone is using them.

Step 5: build checks into the pipeline

1. 01Validate at the door: check formats, ranges and required fields when data arrives, and reject or flag bad rows with a clear message.
2. 02Keep the raw data untouched, and apply cleaning in separate, documented steps.
3. 03Record every change: what was changed, why and by which rule.
4. 04Test the pipeline with a small set of known-bad examples, so that rules do not silently break.
5. 05Alert a named person when something unusual happens, for example a sudden drop in records.

Dealing with missing data in analysis

• First ask why it is missing. Missing at random is easier to handle than missing because of the very thing you want to measure.
• Simple fixes, such as using the last known value or an average, can be acceptable for display but can bias analysis.
• Models may need special handling. Some can accept gaps, and others need values filled in, which should be done using only training data to avoid leakage.
• Report the amount of missing data alongside any result, so that readers can judge it.

A short example

A programme collects monthly figures from many districts. In the audit, a third of districts report late, and a handful report numbers that are ten times too large because they entered totals in the wrong unit. The team agrees four rules: figures outside a plausible range are held for review, late districts are shown in grey with their last report date, unit mistakes are caught by a check at entry and a monthly quality report goes to every district office. Within a few months, on-time reporting rises, and the dashboard becomes something that officers rely on, because it is honest about what it does not know.

When to stop cleaning

Perfect data does not exist. Decide in advance what quality is good enough for the decision, and stop there. A map used to prioritise visits does not need the same precision as a financial audit. Spend the remaining effort on the biggest sources of error, and on improving data collection.

A checklist to take away

• Have we audited a real extract, not a sample prepared for us?
• Do we know the causes of the biggest problems?
• Have the data owners agreed rules for missing, late and suspicious data?
• Does every number show its date and any estimate flag?
• Is the raw data preserved, and every cleaning step documented?
• Does someone own data quality, and see it reported regularly?

Frequently asked questions

Should we clean everything before starting?

No. Audit first, fix the worst and most important problems and build checks so that quality improves as you go. Waiting for perfect data means never starting.

Who is responsible for data quality?

The people who collect and supply the data, supported by whoever owns the system. A named person should track quality and report on it.

Can AI clean the data for us?

It can help find patterns of errors and suggest fixes, and it speeds up tedious work. People who understand the data must still review the rules, because a wrong automatic fix can silently spread.

What if the data is too poor to use?

Then the first project is to improve collection, perhaps for a few indicators that matter most. A small, trustworthy dataset is more valuable than a large, doubtful one.

Imperfect data is the normal condition of real work. The teams that succeed are not the ones with the cleanest data. They are the ones that look at it early, agree rules for its flaws, show them honestly and keep improving the source.