Bad data: how I would check quality before analysing it

A file arrives. Its columns have headings, its dates look like dates, and a total at the bottom seems plausible. Time to build a chart? I would stop at a less glamorous question first: what does one row actually represent? Without that answer, a beautifully presented report can tell a very confused story.

Some bad data is spectacular. A delivery happens in the next century, a customer count is negative, or the same record appears ten times. The harder defects look reasonable. The amount is real but uses another currency. The date is valid but describes an invoice rather than a delivery. The average is calculated correctly for a group that cannot answer the question.

I will walk through the first inspection I would make. Every situation and number below is synthetic. This is an analytical exercise rather than an account of a client project. We do not need a large data-quality platform to begin. We need a few useful questions and a way to compare the answers with the source.

Start with the decision and the unit of observation

Suppose a warehouse manager wants to assess delivery punctuality for last week. That sounds specific, but the unit is still unresolved. Are we counting orders, parcels or deliveries to collection points? An order can travel in two parcels. A parcel can have several delivery attempts. Choosing the unit changes the denominator before we even discuss a formula.

My proposed definition would be parcels whose promised deadline fell within the week. A parcel is on time when its confirmed delivery occurred no later than its promised deadline. I would agree that definition with the recipient. I would not present it as the universally correct definition of punctuality for every organisation and every operational purpose.

I would also establish the observation time. A report prepared on Monday morning might miss confirmations that a carrier sends that afternoon. I want both the event time and the time the information became available. Otherwise, late information can look like a late parcel. Both can cause trouble, but they call for different responses.

This preparation protects us against enthusiastic cleaning. If the table contains status history, several rows for one parcel can be expected. Keeping only its first row could discard the eventual delivery confirmation. Before using a remove-duplicates command, I want to understand the key, the grain of the table and the meaning of subsequent events.

Six quality dimensions without a magic score

The Government Data Quality Framework distinguishes completeness, uniqueness, consistency, timeliness, validity and accuracy. The distinction matters because a date can have the right format while referring to an event that never happened. A table can also contain every expected record and still describe those records incorrectly.

For our delivery example, I would turn the dimensions into inspection questions. Did we receive the expected parcel population? Does each parcel appear once in the summary? Do its dates and status agree? Were confirmations available in time? Do fields satisfy the agreed formats and ranges? Can a selected confirmation be reconciled with the event it claims to describe?

I would resist combining the answers into a single percentage labelled quality. The warehouse-name column might be complete while delivery dates are missing for the carrier we most need to assess. Averaging those scores would conceal the problem. I would first identify fields that matter to the decision, then show their condition separately.

A target of complete population also needs context. An apartment number may legitimately be absent for a detached house. A missing delivery date might describe a parcel still in transit. The introduction to quality dimensions stresses fitness for the intended use. Our own rules should recognise the cases where a field does not apply.

A small example that exposes the uncertainty

Imagine five parcels, all promised for 5 November. The two D rows below are copies of the same confirmation for the same parcel. In a real investigation that would require verification; here it is an explicit assumption. The snapshot was taken at midday on 6 November, and all dates refer to the same local calendar.

There are six rows on the screen and five parcels in the population. Once we remove the agreed copy, A, B and D are confirmed on time. C is late. For E, we cannot yet distinguish an undelivered parcel from a missing confirmation. I would not insert an invented delivery date to make the table look finished.

Among the four confirmed deliveries, three were on time: 75%. That is a result for the confirmed group. Across all five parcels, three are confirmed on time: 60%, with one unresolved case. If E turns out to have been on time, the complete result becomes 80%. If it was late, the result remains 60%, assuming the other four classifications are correct.

Would I publish 75% as the punctuality of all deliveries? I would explain the selected denominator before using that figure. Missing confirmations might be concentrated among difficult deliveries. Excluding them from a calculation does not make the uncertainty disappear. It only gives us a neater table, and the table is supposed to describe something beyond itself.

Missing, zero and not applicable

Consider three fields: number of delivery attempts, delivery date and apartment number. Their empty values may have very different explanations. In the first, we might lack information about an attempt. In the second, the event may not have happened yet. In the third, there may be no apartment number to record in the first place.

For important missing values, I would propose recording a reason separately. We do not need twenty categories for the pleasure of maintaining them. We need distinctions that affect interpretation: awaiting an update, event has not occurred, field does not apply, or reason unknown. A generic missing label often leaves the recipient doing unnecessary detective work.

Zero makes a specific claim. Zero attempts says something different from no information about attempts. Replacing unknown counts with zero can reduce an average while creating the appearance of complete information. If an analysis uses imputation or another replacement rule, I would explain the rule, the justification and its effect on what the result means.

I would also preserve the ability to compare the prepared version with the source. After a correction, I want to know which records changed and why. Otherwise the next analyst sees an attractive clean table, receives a difficult question, and has to reconstruct the investigation. A visible transformation history can save a surprising amount of dull work.

Exploration helps me ask better questions

NIST describes exploratory data analysis as an approach to understanding structure, finding anomalies and examining assumptions, often through simple graphical techniques. That is a useful reason to inspect distributions and small samples before choosing the final chart. The first useful output may be a better question rather than a polished finding.

For the parcels, I would examine counts and missing confirmations by day and carrier. If all unresolved confirmations belong to one incoming feed, the conversation becomes more precise. Instead of saying that the data is poor, I can report that updates from a particular stream are missing after a particular time. That is an observation, not yet a proven explanation.

I would inspect extreme delivery times too. A very long delivery might reflect a wrong date, a return, rerouting or a real delay. Being unusual on a chart does not give me permission to delete it. I would compare the parcel history and decide whether it belongs to the population we agreed to measure.

Small samples also have limits. Reviewing five records can help us understand a rule, but it cannot establish that an entire import is correct. I would use the sample as a starting point. Once a rule is agreed, we need a check across its relevant scope and a way to inspect the exceptions it identifies.

Talk to the source without starting a blame tournament

I would approach the source owner with an example, an expectation and a question. Parcel E has no delivery date in the midday snapshot: does that mean it was undelivered, or could the import be late? This gives someone a question they can answer. Declaring their entire dataset useless mostly gives them a reason to avoid our next meeting.

The request would include the snapshot time, the field definition, the affected scope and the point where the issue was observed. I would separate a confirmed fact from a hypothesis. In our exercise, the copied confirmation is a fact. Its cause is another question: a repeated import, a join that multiplied rows, or an event model we misunderstood?

The framework’s practical guidance recommends investigating causes and targeting improvements at important uses. In this scenario, I would propose agreeing an event identifier at the source. Manually deleting copies every week leaves the same uncertainty for whoever inherits the report and makes a fragile process look deceptively routine.

After the conversation, I would record the agreed rule beside the source or report definition. I would include the person responsible for a correction and the point when we will reassess it. Without those details, a report can circulate between teams for weeks while everyone agrees that someone should probably do something about it.

Is the result good enough for this decision?

Suppose the manager needs to know whether punctuality exceeded 90%. Even the best outcome for parcel E gives us 80% in the exercise. The unresolved case cannot change the answer to that particular question. We can communicate a cautious conclusion with its scope and uncertainty while continuing to investigate the missing confirmation.

If the threshold is 70%, the situation changes. The possible result lies between 60% and 80%, so the missing information can change the decision. I would ask for confirmation before giving a categorical judgement. The same gap can block one use while leaving another useful. That is why a defect count alone does not tell us whether publication should stop.

The limitation should appear where the recipient sees the result. I would not hide unresolved parcels at the end of a document whose first page celebrates a large green percentage. Beside the result, I would show the number of classified cases, the observation time and the definition. Understanding the boundary should not require a private briefing from the author.

Sometimes the data cannot support the requested conclusion. I would say that plainly, then offer something actionable: the missing confirmations, their possible effect and the next step. A bare refusal leaves the recipient with the entire problem. Clear conditions for using the report again make it possible to assign the repair and revisit the decision.

Correct rows can still produce the wrong aggregation

Suppose a second table contains the items inside each parcel. Parcel A contains two products and every other parcel contains one. Joining the tables can make A appear twice. Every product row may be correct, while counting the resulting rows as parcels gives us the wrong population. The error emerges in the interpretation of the combined table.

Before joining, I would write down the expected relationship: a parcel can contain several items. Afterwards, I would check whether I can recover the parcel population without multiplying its punctuality classification. A familiar-looking total is insufficient. We need to understand which units influenced it more than once and whether that weighting was intentional.

Combining percentages creates a related trap. Imagine one warehouse delivers one of two parcels on time, while another delivers nine of ten on time. Their rates are 50% and 90%. The ordinary average is 70%, but together they delivered ten of twelve parcels on time. Those answers differ because the warehouse populations have different sizes.

For a result about all parcels, I would retain the numerators and denominators and calculate the combined proportion again. Equal warehouse weights can make sense for another question, such as the average warehouse score. I would label that result accordingly and explain that a small collection point then contributes as much as a large distribution centre.

Quality inspection should therefore include the journey from table to metric. Correct types, complete dates and an absence of copied records cannot protect us against choosing the wrong aggregation unit. I would ask a second person to reconstruct a small result on paper. A disagreement there is a useful invitation to revisit our assumptions before the report spreads.

Monitor what happens after the first correction

Suppose E’s confirmation arrives, D’s copy is explained and the report can be published. I would now agree how to recognise a recurrence. I would retain separate observations for copied events, unresolved parcels and the time of the latest incoming update. Each answers a different question and needs an agreed response when it moves unexpectedly.

Week-to-week comparisons also need the population. An increase from two missing confirmations to four may look dramatic, but its meaning depends on parcel counts and their distribution across sources. A falling overall percentage should not reassure us automatically if every unresolved parcel belongs to the contract the recipient cares about most. Show the count and its context together.

I would agree intervention thresholds with the recipient and source team rather than inventing a universal acceptable percentage. Some uses can be stopped by one wrong identifier. Other uses can support a limited analysis while information is completed. I want that decision established before the next incident, when there will be less time for a calm conversation.

Finally, I would make sure the correction did not merely hide the symptom. Filtering out all parcels without confirmation makes the report’s missing count zero without improving the carrier feed. I would define that check on the incoming population and report exclusions separately. Otherwise a very convincing success story can be built from a filter that simply removed the difficult cases.

A useful exercise before your next report

Take a small table you understand. Write one sentence explaining a row and another explaining the decision the result should support. Then identify the field whose error would most change your answer. If you cannot choose one, return to the business question before selecting arbitrary quality checks. It is easy to inspect the wrong thing very carefully.

Add three rules: one about completeness, one about agreement between fields, and one about time. For each rule, describe a valid case, a failure and a legitimate exception. The goal is not to inspect an entire organisation in an evening. It is to make the rule clear enough that another person would apply it the same way.

Finally, decide what you will do with a discovered problem. Who can answer the question? What will the report recipient see? Under which conditions is the result still useful? I discuss shared responsibility further in data problems in companies. Here, I would begin with one table and one decision. That is plenty of room to discover how much work hides inside the word obvious.