Lesson 8.2: Case study: a data integration that went wrong
A silent data fault ran for eleven days before a store manager noticed. Trace the causes, the response and the fixes, and learn what to build in on day one.
By the end of this lesson you will have followed a failed data integration from the small decision that made it possible to the incident review that learned from it, and be able to say which checks, alerts and habits would have prevented or shortened it. You will see how a silent fault differs from a loud one, read a blameless account of the causes in three layers, watch an engineer handle the first hours of an incident and the harder weeks of rebuilding trust, and use a simulator to put a price on every day a fault goes undetected. This is the second case study of the last module. Like the first, it is a composite written for teaching: the retailer, the people and every number are invented and meant to be realistic. It is a story of things going wrong, and it is meant to be uncomfortable in places, because every forward deployed engineer eventually has a week like this and the best preparation is to have lived one on paper. You should have read Modules 3, 5 and 6, since the case uses them heavily.
Most of what we learn about reliability we learn from failures, and most useful failures share a feature that makes them dangerous: they are quiet. A system that stops and shouts is easy. A system that carries on, producing output that looks plausible and is wrong, can run for days. The case here is of the second kind, and it is told in the order the engineer lived it, with the reasoning at each stage, so that you can see where the choices that mattered were made.
The setting
Rajan Retail, an invented name, is a chain of about 400 supermarkets. A vendor has deployed a service that combines a demand forecast with each store's stock level and suggests the weekly order for every product. The forward deployed engineer, whom we will call Imran, built the integration that feeds the service with daily sales: each night, a pipeline pulls the previous day's sales from the chain's point-of-sale partner and loads about 120,000 rows into the forecasting system. The forecasts and order suggestions are produced each Sunday for the week.
The project had been live for three months and was seen as a success. Stock-outs were down, store managers liked the suggestions and the sponsor, the head of replenishment, had mentioned the project in a board update. Imran had reduced his time on it to a few hours a week, as Lesson 5.4 advises, and had handed daily operation to a small team at Rajan. It is in this calm that the fault arrives.
The decisions that made it possible
A failure like this seldom comes from one mistake. It comes from several small choices, each defensible when made, that line up. It helps to name them before the story, so that you can watch for them.
Choice 1: a shortcut left unrecorded. In the first weeks, Imran wrote the loader to accept whatever the partner sent, as long as the columns were present. He meant to add range checks and had a note, "add reasonableness checks on quantities", in his own file. It never reached the shortcuts list that Lesson 4.1 recommends keeping where the customer can see it, and it was never scheduled.
Choice 2: alerts only on errors. The monitoring raised an alert if the pipeline failed, as Lesson 5.3 describes. It did not alert on silence or on strange values. A night when the pipeline ran successfully but loaded odd data raised no alert at all.
Choice 3: no reconciliation. The pipeline recorded how many rows it loaded, but nothing compared the quantities with an independent total, such as the partner's own daily count of units sold. Lesson 3.3 lists reconciliation among the door checks.
Choice 4: an unwritten assumption about the partner. The loader assumed that the quantity field always meant single units. That was true on the day it was written, and nobody had asked the partner how it would announce changes, a question Lesson 3.2 recommends.
Choice 5: the owner was a team, not a person. The handover named the replenishment systems team as the operator. In practice, nobody was looking at the data each morning, because nobody was specifically responsible for it.
None of these is outrageous. All are common. Together they leave a pipeline that cannot notice when it is wrong.
Day 0: the change
On a Tuesday, the point-of-sale partner releases an update. Without announcement to the retailer, the partner changes how it reports the quantity for products sold in packs. For items that customers buy by the case, about 3 per cent of all rows, the quantity now means the number of cases and not the number of single units. A case of twelve that used to appear as twelve now appears as one.
The partner's release notes mention the change under a heading about "improved quantity semantics". Nobody at Rajan or the vendor reads them. The nightly pipeline, which accepts any number in that column, loads the new data without a murmur. The row count is normal, the run is marked successful and the dashboard is green.
Days 1 to 10: the quiet damage
The effect begins at once, and is invisible. For the case-packed items, the forecast sees a twelfth of the real sales. Fast-selling items such as bottled water, cooking oil and packaged snacks look as though demand has collapsed. The forecasting service does what it is designed to do: it suggests smaller orders. On Sunday, the suggestions for those items are lower than normal across all 400 stores.
Most store managers accept the suggestions, as they usually do, since they have been good for three months. Some notice that the order for water looks small and increase it by hand. Most do not. By Thursday of the first full week, shelves of the fastest case-packed items are thinner than usual. By the weekend they are empty in a number of stores.
Meanwhile, Imran sees nothing. His weekly check of the dashboard shows green. The operations team sees no alerts. The sponsor sees the headline measure of stock-outs, which is reported weekly and lags. The sales data that would reveal the problem, a sudden drop in recorded sales for fast-selling items, is exactly what is corrupted.
Day 11: the call
On the eleventh day, a store manager in a mid-sized city phones the regional manager: for the second weekend, there is no bottled water on the shelf and the order suggestion for next week is smaller still. The regional manager mentions it to the head of replenishment, who phones the vendor's support line. By the afternoon, the message reaches Imran.
Predict first: you are Imran. At four in the afternoon, you learn that stores have been out of stock for some fast-selling items for a week and that the order suggestions for those items look wrong. You do not yet know why. What is the first thing you do? Think of the order of your first three actions before reading on.
The first action is to stop the damage, not to find the cause. The next Sunday's suggestions are still two days away, but store managers are placing top-up orders daily based on the suggestions on screen. Imran asks the head of replenishment to put a notice to all stores: until further notice, for the affected categories, ignore the suggestions and order by hand, and he asks the operations team to switch the suggestions for case-packed items to "hold" in the system. This takes about thirty minutes. Only then does he start to diagnose. The second action is to tell the sponsor what is known and not known, in plain words, with a time for the next update. The third is to look for the cause. Reversing the order, diagnosing first and informing later, is the most common mistake in an incident, and the one that turns a bad day into a bad week.
Day 11, evening: finding the cause
Imran follows the approach of Lesson 5.3: symptoms first, then causes. The symptom is that forecasts for a group of fast-selling items dropped sharply on one date. He charts the loaded quantity for a bottled-water product by day and sees a drop to roughly a twelfth on day 0. He opens the raw files, which the pipeline kept, as Lesson 3.3 advises, and compares the quantity column before and after: the same product, the same stores, with figures that are a twelfth as large. A call to the partner's technical contact, answered within the hour, confirms the change in meaning and points to the release note.
By evening, he has the cause, the date and the scale. About 3 per cent of rows, about 3,600 a day, have been affected for eleven days, about 39,600 rows. He writes his first full update to the sponsor, in the format of Lesson 6.3: the headline, the impact, the cause in a sentence, the immediate actions and the plan to fix and to recover. He does not hide the length of time. He says, early in the note, that his monitoring did not detect it, and that this is his responsibility to correct.
Days 12 to 14: fix and replay
The fix has three parts.
Stop the bleeding, properly. A rule in the loader converts quantities for the case-packed items back to single units, using the pack size from the product table. It is tested on the raw files from before and after the change, and reviewed by a colleague. It is released as a patch version with a rollback plan, as Lesson 5.2 describes, on the second day, to the test system first and then to production.
Repair the data. Imran replays the eleven days of raw files through the corrected loader, replacing the faulty rows. Because the pipeline had been written to be repeatable, as Lesson 3.3 advises, with each day's data replaced and not added, this takes hours and not days. He reconciles the replayed totals against the partner's own daily units-sold report, which he asks for in order to confirm that they match.
Re-run the forecast. The forecasts for the affected items are recalculated with the correct history, and new suggestions are issued. A short note goes to store managers explaining that the suggestions are trustworthy again, and which items to check by eye in the first week.
The recovery of the shelves takes longer. Deliveries need time, and for some items the supply chain's lead time is a week. For ten days, the affected items are short in a number of stores. Rajan's team estimates the lost sales later.
The harm built up in a straight line until the fault was found. The chart shows the running total of bad rows from the day the partner changed the field.
The communication
The technical fix was straightforward. The harder part was the relationship. The head of replenishment had told the board that the project was a success. Now there was a week of empty shelves, a number of store managers who had lost confidence, and a question she must answer: how did the vendor's system fail without anyone noticing?
Imran follows the approach of Lesson 6.2. He tells her early, directly and in person, before the news reaches her manager from another source. He gives her the facts without cushioning, the cause without blaming the partner or her team, the impact in terms she cares about and the plan with dates. He owns the part that was his: the monitoring that could not see this kind of failure. He offers a short list of changes, and asks for her view on them. She is angry, and says so. She also says, at the end, "Thank you for phoning me yourself."
He sends a written update every day for a week, then twice a week for a month. The head of replenishment forwards the first one to her own manager, unchanged. A frank, early account allows her to defend the project, and she is not blindsided.
The blameless review
Two weeks after the incident, Imran leads a review with Rajan's operations team, the partner's technical contact and a colleague from his own company. The rules are those from Lesson 5.3: it is blameless, the aim is to improve the system and the routine, and it ends in actions with owners and dates. A blameless review does not mean nobody is responsible. It means that the question is "how did the system allow this?" and not "who is to blame?"
The review finds causes in three layers.
The trigger. The partner changed the meaning of a field without telling the retailer. This is the proximate cause, and the one nobody at Rajan or the vendor controlled.
The technical gaps. The pipeline accepted any number in the quantity column and so could not notice a change. There was no reconciliation against the partner's own totals. There was no alert on unusual values or on an unexpected drop in recorded sales. The monitoring watched whether the job ran, and not whether the data made sense. Lesson 3.3's door checks and Lesson 5.3's rule to alert on symptoms would have caught it in a day.
The process gaps. The shortcut, "add reasonableness checks", lived in one engineer's notes and not in a shared list. The handover named a team as the operator and no person as the owner of the morning data check. The agreement with the partner said nothing about change notices. The first user of the data, the store manager, was the first to detect the fault.
Three layers, none of which was a single person's failure. Fixing only the trigger, by asking the partner to give notice next time, would leave the other two layers in place, ready for the next surprise.
A reconciliation check would have caught this in a day. Compare the units loaded with the partner's own daily total, and alert when they differ by more than a tolerance. Set the share of units sold in case-packed rows, the pack size and the tolerance, and see whether the check would have fired.
At the defaults, case-packed items are 8 per cent of units, and reporting each case as one unit loses 11 of every 12 of them, a shortfall of about 7.3 per cent against a 2 per cent tolerance. The check would have alerted on the first night.
The fixes
The review ends with a list of actions, each with an owner and a date.
Door checks on quantities. Reasonableness ranges for each product, and an alert on a sudden fall in total recorded units. Owner: Imran, one week.
Reconciliation. A daily comparison of the loaded unit totals with the partner's own report, with an alert on a difference above a small percentage. Owner: Imran, two weeks.
An alert on silence and anomaly. An alert if the number of rows for any product group falls sharply from its normal range. Owner: Imran, one week.
A named owner. One person at Rajan, the senior replenishment analyst, owns the morning check of the data dashboard, with a deputy. Owner: the head of replenishment, immediately.
A visible shortcuts list. All known gaps are added to a shared list, reviewed monthly. Owner: Imran, one week.
A change notice agreement. The partner agrees to give fifteen days' notice of changes to field meanings, and to provide a test environment. Owner: the head of replenishment with the partner, a month.
A runbook for "suggestions look wrong". The first checks, the hold switch and who to tell. Owner: Imran, two weeks.
A hold switch. A quick way to freeze suggestions for a category. Owner: Imran, one week.
Each action addresses a layer. Together they turn the pipeline from one that cannot notice a fault into one that notices within a day.
What the fault cost
Rajan's team estimates the cost, with Imran's help, by counting. About 3,600 rows a day were wrong for eleven days, about 39,600 in all. A small share of the bad rows led to an order that was too small, and a smaller share of those to a lost sale. The team also counted the cost of repairing and replaying the data.
You can rebuild the estimate yourself, and the main lesson is the way the cost grows with every day of delay.
The simulator below does the sum. Set the number of bad rows a day, the days the fault runs undetected, the chance that a bad row causes a lost sale, the value of a lost sale and the cleanup cost per bad row. It shows the lost sales, the cleanup cost and the total, and plots the total as the days undetected grow.
The probabilities and values are the case's invented figures and an estimate, since in a real incident they are hard to know. The shape is the point: the cost grows in direct proportion to the days the fault runs, which is why the cheapest improvement in a pipeline is to shorten the time to detection.
Try three experiments. First, keep the defaults and read the total. Second, set the days undetected to one, which is what the new alerts would give, and see the saving. Third, double the bad rows and see that a large fault with a one-day detection time can cost less than a small one that runs for two weeks.
At the defaults, 3,600 bad rows a day for eleven days is 39,600 rows. At a 0.5 per cent chance of a lost sale of ₹2,000 each, lost sales are about ₹3.96 lakh, and cleanup at ₹3 a row is about ₹1.19 lakh, so the total is about ₹5.15 lakh. Detected in one day, the same fault would have cost about ₹0.47 lakh. The checks added after the incident cost a few days of Imran's time. The case for them can be read directly from the plot.
What Imran took from it
Imran writes a one-page account for his own company's engineers, in the blameless form, and adds an entry to the pattern log of Lesson 7.1: three other customers have the same integration with the same partner. He sends a field brief to the product team, using Lesson 7.2, asking for door checks and reconciliation to be built into the standard connector, with the incident as evidence and the cost estimate as the impact. The product team puts it on the roadmap for the next quarter.
He also changes his own practice. He puts every shortcut on a shared list on the first day. He asks, in every integration's first week, how the source will announce changes and writes the answer in the one-page plan. He builds the reconciliation check before the second week of any project, and he never again describes a pipeline as "monitored" if all that is watched is whether the job ran.
Common mistakes
Alerting only on errors. A pipeline can run perfectly and load nonsense. Alert on values, volumes and silence.
No reconciliation. Compare loaded totals with an independent total from the source.
Trusting unwritten assumptions about a source. Ask how changes are announced, and write the answer down.
Shortcuts in one head. Put every known gap on a shared, dated list.
A team as the owner. Name a person responsible for the morning check, with a deputy.
Diagnosing before stopping the harm. Freeze the affected output first, tell the sponsor, then find the cause.
Hiding the time it ran. Say how long the fault lasted, own what was yours and give the plan.
Blaming the partner or a person. Review in layers: trigger, technical gaps, process gaps.
Fixing only the trigger. A change notice agreement does not replace checks that notice the next change.
Key takeaways
The most dangerous failures are quiet: the system runs, the dashboard is green and the output is wrong. A system that cannot notice a fault will not.
Failures like this come from several small choices lining up: an unrecorded shortcut, alerts only on errors, no reconciliation, an unwritten assumption about a source and an owner that is a team.
In an incident, stop the harm first, tell the sponsor early and plainly, and then diagnose. Own what was yours.
Keep raw data and make pipelines repeatable, so that replaying and repairing takes hours, not days.
Review blamelessly in layers, the trigger, the technical gaps and the process gaps, and end with actions that have owners and dates.
The cost of a fault grows with every day it runs, so the cheapest improvement is to shorten the time to detection with door checks, reconciliation and alerts on silence and anomaly.
Check yourself
1. Why did the fault run for eleven days without an alert?
The monitoring watched only whether the job ran. The pipeline accepted any number in the quantity column, so a run with wrong data looked like a normal successful run.
2. At the moment you learn of a fault affecting stores, what is the first action, and why?
Stop the damage, for example by freezing the affected suggestions and telling stores to order by hand. Diagnosis comes after, because the fault keeps harming while you look for the cause.
3. Name the three layers of causes in the review.
The trigger, the technical gaps and the process gaps.
4. 2,000 bad rows a day for 8 days, a 1 per cent chance each causes a ₹1,500 lost sale, and ₹2 cleanup per row. What is the total cost?
16,000 bad rows. Lost sales: 16,000 × 0.01 × 1,500 = ₹2.4 lakh. Cleanup: 16,000 × 2 = ₹0.32 lakh. Total about ₹2.7 lakh.
5. Why does fixing only the trigger fail to protect the system?
The partner may change something else, or another source may change. Checks, reconciliation and alerts catch faults whatever caused them, while a change notice agreement only prevents one cause.
Try this
This exercise takes about forty minutes and audits a pipeline for the five choices in this case. Use a pipeline or data feed you work with, or an imagined one.
1. Draw the pipeline in a line of boxes: source, load, check, store and use. Mark where a person first sees the result.
2. For each of the five choices, answer yes or no for your pipeline: Are all known shortcuts on a shared list? Does it alert on values and silence as well as errors? Is there a reconciliation against an independent total? Do you know how the source announces changes? Is a named person responsible for checking it each morning?
3. For every no, write the first fix, who owns it and a date.
4. Write the worst quiet failure you can imagine for the pipeline, and how long it would take to notice today.
5. Use the simulator with your own numbers to estimate its cost, and what shortening the detection time to one day would save.
6. Write three door checks you would add, with the alert each would raise.
7. Write the first three steps of the runbook for "the output looks wrong".
A good result is a five-question audit, a list of fixes with owners and dates, an estimate of the cost of a quiet failure and a short runbook. Doing this before an incident is far cheaper than doing it after.
Sources
This is a composite case written for teaching. The retailer, the people, the partner's change and every figure are invented and meant to be realistic. It applies the methods of Lessons 3.2, 3.3, 4.1, 5.2, 5.3, 6.2 and 6.3, and the sources cited in them, and none of it reports a real company's incident. The simulator's defaults are the case's invented figures.
Tags: Forward Deployed Engineer Case study Data integration Incident review Postmortem