Lesson 3.3: Data quality and pipelines for customer data

Customer data is never as clean as promised. Learn six quality checks, how to build a pipeline that catches bad rows at the door, and how to keep it running.

By the end of this lesson you will be able to describe the six ways data goes wrong, run simple checks on a customer's file, design a data pipeline that catches bad records at the door and runs safely every night, and explain data problems to a non-technical customer in terms of money and decisions. You will see three realistic quality failures traced to their causes and use a simulator to see how much a good set of checks saves. This lesson follows 3.1, where you audited where the data lives, and 3.2, where you learned to reach it through interfaces and files. Here you learn what to do once the data arrives. You do not need to be a programmer to follow the ideas, though the exercise at the end is easier if you can read a little code or a spreadsheet formula.

Every customer's data has problems. This is not a criticism. Data is created by people typing under pressure, by systems that were configured years ago for a different purpose and by processes that changed without anyone updating the records. The question is never whether the data is clean. It is whether you know how it is unclean, whether you have decided what to do about it and whether your solution will notice when it gets worse. Engineers who skip this work discover the problems in front of the customer, usually when a number on a screen looks wrong.

Why data quality is your problem

It is tempting to treat quality as the customer's responsibility: they own the data, so its flaws are theirs. In a narrow sense that is true. In practice, the customer will judge your solution by its outputs, and an output built on bad data looks like a bad solution. A reminder sent to the wrong borrower, a dashboard that shows a bed as free when it is not, a forecast driven by duplicated sales: each of these is the customer's data problem, and each will be remembered as your failure.

So you take responsibility for what you can control. You find the problems early, you design around them, you tell the customer what you found and what you did about it, and you build checks that keep working after you leave. You do not need to fix the customer's whole data estate. You need your slice to be trustworthy.

Six ways data goes wrong

It helps to have names for the kinds of problem, because each has a different check. These six are widely used, and they cover most of what you will meet.

1. Completeness. Required values are missing. A borrower record with no phone number, a payment with no date. A blank is not always an error, since some fields are legitimately optional, but you need to know how many blanks to expect. 2. Validity. Values are in the wrong form or outside allowed ranges. A date written as "31-02-2026", a negative loan amount, a phone number with letters, a status code that does not exist. 3. Uniqueness. The same real-world thing appears more than once. The same customer entered twice with slightly different spellings, the same payment loaded twice from two files. 4. Consistency. Different places disagree. The loan system says the balance is ₹40,000, and the collections system says ₹42,500. Or a field means different things in different tables. 5. Timeliness. The data is older than you need. A file that arrives at 9 a.m. for a decision made at 6 a.m., or an account status that is updated weekly when you need it daily. 6. Accuracy. The data does not match reality. The record says a bed is free and it is occupied. Accuracy is the hardest to check, because you need an independent source of truth, such as a physical count or a sample verified by a person.

The first five can be tested by a program. The sixth needs a person looking at the real world, which is why you should sometimes sit with the users and compare a sample of records with what they see.

Profile before you build

The first thing to do with a new data source is to look at it. Profiling means producing a quick summary of each field. For each column, count how many values are blank, how many are different, what the smallest and largest are, how many fit the expected format and what the most common values are. Even a spreadsheet's filtering and counting tools will show a great deal.

A profile often turns up the problem within minutes. A date column in which a third of the values are in a different format. An amount column with a handful of values a thousand times larger than the rest, because someone entered rupees in a field meant for thousands. A status field with twenty-three different values where the documentation lists six. None of these is hard to find, and all of them are expensive to find late.

Write down what you find, and take it to the steward from Lesson 3.1. Ask, "what does this mean, and is it expected?" Sometimes the answer is that the odd value is a legitimate special case, and sometimes it is a mistake that nobody had noticed.

Checks at the door

The most important design decision for a pipeline is where the checks go. The right place is at the door: as data enters your system, before it is mixed with anything else and before anyone relies on it. A problem caught at the door is cheap to explain and to fix. A problem caught after it has fed a report is expensive, and one caught after a customer has acted on it is very expensive.

A practical set of door checks, for each file or batch, includes:

Arrival. Did the file arrive when expected? Is it complete, or still being written? Size. Is the number of rows within the normal range? A file with a tenth of the usual rows probably means something failed upstream. Structure. Are all the expected columns present, in the expected order, with the expected names? Format. Do dates, numbers and codes fit their expected forms? Required values. Are the key fields filled in? What share of rows is missing each? Duplicates. Are any identifiers repeated within the file or against yesterday's? Reasonableness. Are values within plausible ranges, such as amounts, dates in the future or ages above 120? Reconciliation. Do totals match? If the sender says it sent 12,431 records with a total balance of ₹18.4 crore, do you have the same?

Each check should produce a clear result, not just a pass or fail. "412 of 12,431 rows have a due date in the old format" is useful. "Validation failed" is not.

What to do with a bad row

Finding a problem is half the job. The other half is deciding what to do, and there are four choices. Agree the choice with the customer in advance, for each kind of problem.

Fix it automatically. If the rule is clear, such as converting a date from one known format to another, the pipeline can correct it and note that it did so. Quarantine it. Set the row aside in a separate place, with the reason, and carry on with the rest. A person reviews the quarantined rows and either fixes them or confirms they should be ignored. This is the most common choice for problems that need judgement. Reject the whole batch. If the problem is serious, such as a missing column or a file that is a tenth of its normal size, do not process anything. Alert someone and wait for a corrected file. Processing a broken batch is worse than processing nothing. Accept it and flag it. If the problem is minor and the use is tolerant, load the row and mark it. Make sure the flag travels with the data, so that later users know to be careful.

What you must never do is quietly drop or change data without recording it. A pipeline that silently discards 3 per cent of rows produces results that are wrong in a way nobody can see. Always count what came in, what went through, what was fixed and what was set aside, and show those counts.

Predict first: your pipeline loads the customer's nightly file of 12,000 accounts. Tonight the file arrives with only 1,100 rows, and no error message. What should the pipeline do, and what should it not do? Decide before reading on.

The pipeline should stop, not load the file, and alert a person: a file with a tenth of its usual size is almost certainly incomplete, and loading it would make the reports appear to show that most accounts vanished. It should keep yesterday's data in place, say clearly what it found ("1,100 rows against an expected range of 11,000 to 13,000") and wait. What it should not do is carry on silently, or replace yesterday's data with the broken file. The size check is among the cheapest and most valuable checks you can write, and many serious incidents are an unchecked small file.

Building a pipeline that runs every night

A pipeline is the sequence of steps that moves data from the source to where it is used: collect, check, clean, combine, load and publish. A few design principles keep it reliable.

Make each step repeatable. If you run the pipeline twice on the same input, the result should be the same as running it once. This property is the same idea as idempotency in Lesson 3.2. It means that after a failure you can simply run it again without fear of duplicates. A common way is to replace a whole day's data when it is loaded, and not to add to it.

Keep the raw data. Store exactly what arrived, unchanged, before you clean or transform anything. If you later discover a mistake in your cleaning, you can rebuild from the original. If the customer disputes a number, you can show what they sent.

Separate steps. Collecting, checking, cleaning and loading are different jobs. Keeping them separate makes failures easier to find: if the check step fails, you know the problem is in the incoming data, not in your calculations.

Record what happened. For each run, keep the time, the counts at each stage, the checks that failed and the decisions taken. This record is your evidence when someone asks "why did the number change on Tuesday?"

Schedule with slack. If the file normally arrives at 2 a.m. and the pipeline starts at 2:05, one late file breaks the morning. Allow time, and make the pipeline wait and retry up to a limit.

Handle late and corrected data. Sources often send corrections, such as a payment that was recorded yesterday but dated the day before. Decide how you will treat records that arrive later than their date and records that change after you have loaded them.

Alert on the right things. A failed run, a failed check, a file that did not arrive and a count outside its normal range are all worth an alert. Too many alerts and people ignore them, so start with a few important ones.

Tracing a number back

One of the most valuable properties of a good pipeline is that any number on a screen can be traced to its source. This property is called lineage. When the head of collections asks "why does this account show ₹42,500 overdue when the loan system says ₹40,000?", you should be able to answer in minutes: this value came from this file on this date, this transformation added a fee, and here is the row.

Lineage need not be elaborate. Keep an identifier for the source file and row on each record, record the version of the rules you applied and keep the raw data. These habits cost little and save hours of argument when a number is questioned. They are also what makes a customer trust a system: they can see where things come from.

Worked example 1: two date formats

A lender's nightly file contains a due date column. The engineer's first profile shows that about 70 per cent of dates are in the form day-month-year and about 30 per cent in month-day-year. For dates such as 03-04-2026, the two readings differ by a month, and the data does not say which applies.

The engineer traces the cause with the steward. Two branches use different versions of the collection software, and one of them exports dates in the other order. The file does not say which branch a row came from, so the engineer asks the system owner to add a branch code to the export. In the meantime, a rule handles the unambiguous cases: if the day is above 12, the format is obvious. Ambiguous dates are sent to quarantine, about 4 per cent of rows, for the steward to confirm. Once the branch code arrives, the rule becomes exact.

The lesson is that the fix was to find the cause and add information at the source, not to guess. A guess would have turned some due dates into the wrong month, and borrowers would have been told they were overdue when they were not, or told nothing when they were.

The date problem is a split of one column into two meanings. The chart shows the shares in the lender's file.

Worked example 2: the duplicate customers

A retailer's loyalty data contains customers entered more than once, with variations such as "Rohit Sharma" and "Rohit Sharma " (with a trailing space) or with a different mobile number. The engineer measures it by counting records that share a mobile number or an email but have different identifiers: about 6 per cent.

The business cost shows up quickly. A campaign that counts customers counts these people twice, and the reported number of active customers is inflated. The engineer builds a matching step. It treats two records as the same person when they share a verified mobile number, or when name and postcode match closely, and it flags uncertain matches for review instead of merging them. The first rule is strict, because wrongly merging two people is worse than missing a match.

Two points stand out. First, uniqueness problems are often invisible in totals and obvious in counts of customers. Second, merging records is a decision with consequences, so the cautious setting is usually the right one at the start, and the pipeline should show how many records were merged and why.

Duplicates inflate every count made from the data. Set the number of customers a report shows and the share of records that are duplicates, and see how many real customers are behind them.

At the defaults, a report of 100,000 customers with 6 per cent duplicates is really 94,000 people. Anything divided by customers is too small by about 6 per cent, and the headline count is too large by about 6.4 per cent.

Worked example 3: the file that shrank

A hospital group's bed screen reads a nightly file from each hospital. One morning the screen shows the largest hospital with almost no beds in use, which is plainly wrong. The cause is found in the log: the file that night held 900 rows instead of the usual 9,000, because the export had been interrupted. The pipeline loaded it anyway, since it checked only that the columns were present.

The fix is a size check against a rolling range, a rule to refuse any file outside it and an alert to the hospital's IT contact, plus the habit of keeping the last good file in place. The engineer also adds a check on the share of beds with a status, so that a file with plausible size but a missing column of values would also be caught. After this change, the next interrupted export produces an alert at 3 a.m. and a screen that still shows yesterday's state, clearly labelled as such. The screen is slightly out of date and not wrong, which in a hospital is a far better failure.

What a good set of checks is worth

Checks cost effort, and a customer may ask whether they are worth it. The arithmetic is simple and surprisingly convincing. Each bad row that gets through has a cost: a wrong message, a wrong report, an hour of someone's time finding out why. Checks catch a share of bad rows, and what they miss costs money.

The simulator below turns this into numbers. Set the number of rows loaded each day, the share that are bad, the share that your checks catch and the cost of one bad row that gets through. It shows how many bad rows are missed each day and the monthly cost, and plots how the missed rows fall as the checks improve.

The cost figure is your estimate of what one missed error costs, so treat the result as an order of magnitude. The picture it gives is the real point: a modest bad-row rate on a large daily volume adds up, and the first checks you add are the ones that save the most.

Try three experiments. First, keep the defaults and see how the cost falls when the catch rate rises from 60 to 90 per cent. Second, lower the bad-row rate to 0.5 per cent and see how much a tidy source reduces the value of checks. Third, increase the cost per missed row to ₹500 and see how quickly the monthly figure grows when each error reaches a customer.

At the defaults, 12,000 rows with 3 per cent bad give 360 bad rows a day. Checks that catch 60 per cent leave 144 a day getting through, which at ₹150 each is about ₹6.5 lakh a month. Raise the catch rate to 90 and the leak falls to 36 rows a day, about ₹1.6 lakh a month. The saving, nearly ₹5 lakh a month, is the argument for building the checks first.

Telling the customer

Data problems are a sensitive subject, because they can sound like blame. A few habits keep the conversation useful.

Show, do not accuse. "Here are 310 rows where the date reads two ways" is a fact. "Your data is a mess" is a judgement. Talk in money and decisions. "If we leave this, 4 per cent of borrowers get a reminder in the wrong month" matters more to a sponsor than "there is a format inconsistency". Offer the options. Fix at the source, correct in the pipeline, set aside for review or accept. Let the customer choose, with your recommendation. Share the counts regularly. A short weekly note saying how many rows came in, how many were fixed and how many were set aside builds trust and shows whether the source is improving. Help fix the source. Where the same problem keeps recurring, the best cure is upstream: a required field, a drop-down list instead of free text, a check in the system where people enter data. Be generous with suggestions, and credit the people who make the change.

Common mistakes

Assuming the data is clean. Profile it first. Surprises are cheaper in week one. Checking late. Put checks at the door, before data mixes with other data and before anyone relies on it. Silently dropping or changing rows. Count and record everything that is fixed, set aside or rejected. Loading a broken file. A file far smaller than usual should stop the run and raise an alert. Guessing. Where the data is ambiguous, ask for more information at the source. Merging too eagerly. Wrongly merged records are worse than missed matches. Start strict and flag uncertain cases. No raw copy. Keep what arrived, so you can rebuild and so you can show the customer. A pipeline that cannot be rerun. Make each step safe to repeat. Too many alerts. Start with the important few, or people stop reading them.

Key takeaways

Every customer's data has problems. The question is whether you know them, have decided what to do and will notice if they get worse. Six kinds of problem cover most cases: completeness, validity, uniqueness, consistency, timeliness and accuracy. Only accuracy needs a person to check against reality. Profile a source before you build, and put checks at the door: arrival, size, structure, format, required values, duplicates, reasonableness and reconciliation. Decide in advance what to do with a bad row: fix it, set it aside, reject the batch or accept and flag it. Never drop or change data silently. A reliable pipeline is repeatable, keeps the raw data, separates its steps, records every run and can trace a number to its source. Explain problems in money and decisions, offer options and push fixes upstream where the same problem recurs.

Check yourself

1. Name the six ways data can go wrong. Completeness, validity, uniqueness, consistency, timeliness and accuracy. 2. Tonight's file has a tenth of the usual rows and no error message. What should the pipeline do? Stop without loading it, keep yesterday's data in place and alert a person with the counts. Never load it silently. 3. Why should a pipeline keep the raw data exactly as it arrived? So you can rebuild if your cleaning turns out to be wrong, and show the customer what they sent if a number is disputed. 4. With 20,000 rows a day, 2 per cent bad, 75 per cent caught and ₹100 per missed row, what is the monthly cost of missed rows? 20,000 × 0.02 = 400 bad rows. 25 per cent get through, so 100 a day. At ₹100 that is ₹10,000 a day, or ₹3 lakh over 30 days. 5. Why is wrongly merging two customer records usually worse than missing a match? Merging mixes two people's histories and is hard to undo, while a missed match only leaves a duplicate that can be reviewed later.

Try this

This exercise takes about forty minutes and gives you a first data-quality report. You need a table of real data with at least a few hundred rows: a spreadsheet from your work, a public dataset or an export from a tool you use.

1. Write one line on what the data is for and which fields matter most. 2. Profile each important column: count the blanks, count the different values, find the smallest and largest, and look at the most common values. A spreadsheet's filter and pivot tools are enough. 3. Check each of the six kinds of problem and write what you found. Completeness: how many blanks. Validity: values in the wrong form or range. Uniqueness: repeated identifiers. Consistency: any two columns that should agree. Timeliness: the date of the latest record. Accuracy: pick five rows and check them against another source or a person. 4. Choose three door checks you would run every day, and write what the pipeline should do when each fails. 5. Use the simulator with your row count and your bad-row rate to estimate what missed errors cost. 6. Write a short note to the owner of the data, in plain words, with the three most important findings, the counts and one suggestion for fixing the source.

A good result is a one-page quality report with counts for each of the six kinds of problem, three door checks and a note for the data owner. It is the first section of the document you would hand to a customer, and it shows them you have looked at their data with care.

Sources

The three customers, the check results, the figures and the simulator's defaults are the author's illustrations for teaching, not data from real companies. The six kinds of data problem are a widely used way of describing quality and are explained here in the author's own words. The door checks and pipeline principles are a practical summary written for this lesson.

Tags: Forward Deployed Engineer Data quality Data pipelines Integration Beginner