Following one failed unit through a factory's data

This is Matthew Perry. What you are about to hear is a full spoken walk through of a business case built around one line on the page: one unit fails final inspection, and everything after that is the same problem. I will take you through what was actually built, what was modelled to keep the engineering realistic, and what each number means, moving in the order the page presents it, from the first failed inspection out through the field and back to the people behind it.

Every figure you will hear is synthetic. It was generated for this exercise, and none of it describes any real company's operations. This narration runs about thirty five minutes. Here is the page.

So here is the top of it, starting with the headline: one unit fails final inspection. Everything after that is the same problem. This is part two of two, the business case, built from what you told me, using synthetic data throughout.

Here's the setup. A binocular fails final inspection on the line, and the question is simple: why, and what does it cost us. You can't answer that on the day it happens, because the answer for that one unit is split across the machine stations, three hand stations, two generations of an E R P, the dealer, the owner's own registration, and the service desk. No two of those systems share a key. Everything on the page follows that single failure downstream, in the order a real unit actually travels, built on synthetic data shaped to match.

Right under that sits the synthetic data banner. Every figure here is synthetic. I generated four datasets for this exercise, shaped like an E R P, a C R M, a quality control station log, and a web analytics feed. No real customer, dealer, serial number, campaign, or inspection is used, reproduced, or implied anywhere, and nothing describes any company's actual operations. The volumes are order of magnitude estimates, chosen to make the engineering realistic.

Then comes the section called what you told me: the pieces of one problem. It takes six things you said, in your own words, and maps each to what it actually is underneath, and where on the page I answer it. Nobody can say why products fail quality control, that's failure reasons captured three ways, some by hand, none coded. Needing two systems to answer one question, that's the E R P and the C R M disagreeing on the key that should join them.

Production data being a live issue right now, that's the two E R P generations, a firehose of station events, late arrivals, replays, and the renamed column. How much is done by hand versus by a system becomes a capture method column on every record, then it's a number.

Then there's ranking what's highest value for the least effort, and formalizing what done actually means. I used generic names on purpose, the E R P, the C R M, the legacy system, because the engineering is identical whichever vendor sits behind them. These aren't six separate problems. They're one failure, seen from six different desks.

Next, the section called one dataset, one thread.

Everything here comes from eleven synthetic tables I built, grouped into three slices: production, product, and people. The idea: one serial number threads a single unit from its work order out to the field, and where there is no serial, an email address threads a person from a web form into the C R M, the customer relationship management system. Nothing actually forces that thread to hold. Every system just writes its own spelling of the same key.

Take one example serial from the synthetic set. Manufacturing writes it as S W dash E L four two one zero dash zero zero zero zero one two three. The dealer shortens it to E L four two one zero dash one two three. The customer's own registration types it lowercase with spaces. The service desk drops the punctuation entirely. Four systems, four spellings, one unit.

On top of that, I baked in more traps on purpose: stripped leading zeros, typos, duplicate registrations, timestamps with no time zone, dealers who report late or never, a file format that changed overnight, failure reasons written in three languages, duplicate event batches from replays, a column renamed mid year, and five hundred and sixteen registrations dated before their own unit was even built.

The figure next to this section is a plain screenshot from BigQuery, the data warehouse: one day of raw station events, pulled straight off the table, with eight and a half million rows behind it, all of it synthetic. Already, on the very first row, the ingested at timestamp is two hours behind when the event actually happened. That is the starting mess. Everything after this is what it takes to clean it up.

Next, the section called the unit. This is the one row I follow all the way through, because everything else on this page is downstream of what happens to it. It comes from the synthetic dataset, and every table it moves through is synthetic too, but the kind of gap it exposes is real. The serial is S W dash E L thirty two ten dash zero two nine seven seven zero seven. I will just call it our serial from here on. It is an E L Range binocular, for the birding segment, list price two thousand eight hundred and ninety euros.

Before that serial exists there are two lots, a lens lot and a housing lot. The lens lot is recorded three times, once each by the grinding machine, the interferometer, and the coating line. The housing lot is recorded three times too, two of those by machines and one by a person hand finishing it. Neither lot knows a serial yet, because there is not one.

The serial gets written for the first time on the sixth of February twenty twenty one, at clean room assembly. That is the moment the two lots become one physical unit, and a person writes the serial by hand into the unit inspection log and the lot to serial table that ties it back to where it came from.

That same afternoon the unit fails final inspection. The inspector types a misspelling of the word scratch, no catalogue code, and logs the row an hour and a half late. It goes back to assembly, comes forward, fails again three days later for a focus problem, goes back a second time for glue residue, and passes on its third attempt. Six hand written rows, five operator initials, three free text reasons matching nothing in the catalogue as typed. The station clock through all of this reads New York time.

Then the unit leaves the factory. A dealer in Germany sells it in October twenty twenty one and reports the sale seven days late, spelling the serial one way. The owner registers it on the web a month later, from the Canadian subsidiary, spelling it a second way. The service desk in Canada logs a warranty repair, and later a paid cleaning, spelling it a third way. Four systems, four spellings, one unit, one border crossed, and a warranty case on a unit already reworked twice before it left the building. Every section after this one is another place that same gap shows up.

Next, the section called at the station: why it failed.

This is where our unit failed, and the plain problem is that nobody could say why, because the reason for a failure gets written down three different ways.

I built this on six hundred and eighty three thousand two hundred and ninety two inspection rows across eight stations, from lens grinding through to final inspection. Five are machines: grinding, the interferometer check, coating, housing machining, and anodizing. Three are hand stations: housing hand finishing, clean room assembly, and final inspection, where our unit actually failed. The station roles here are synthetic too, sized to keep the engineering realistic.

Across all eight, the overall fail rate is four percent. But ninety two point six percent of the rows were typed by a person. That's not because most of the line is manual, it's because a machine station logs one row per lot of lenses or housings, while a hand station logs one row per unit, and units far outnumber lots. That share is a function of record grain, not of how many stations are automated, and once that column exists, how much is done by hand is a query, not a debate.

Now the reason itself. A machine station writes a code. A hand station writes what the inspector saw, in whichever language came first: dust, or Staub, or Fingerabdruck. Group by the raw string and one failure splinters into a dozen spellings, and the dashboard just says other.

Four steps fix that. First, a reason catalogue: one code per known cause, per station, forty eight phrasings collapsing into thirty two codes. Second, map, don't guess: a phrase that matches becomes a code, one that doesn't stays flagged, and four thousand five hundred and ninety three of twenty seven thousand two hundred and seventy nine fails landed flagged. Third, remove the double entries: the same serial, station and minute logged twice by hand is one failure, not two, twelve thousand four hundred and sixty six duplicates removed. Fourth, carry a capture method column, hand or system, on every row.

The station table shows it clearly. On the five machine stations, empty reasons, duplicates and unmatched reasons all sit at zero. On the three hand stations, unmatched reasons range from sixteen point seven percent at final inspection to eighteen point one percent at clean room assembly, duplicates hold close to two percent, and three to four percent of rows carry no reason at all, station by station rather than blended away.

Then the part that actually costs money: the field link. A reworked unit turns up in service afterward at two point four six times the rate of a unit that passed first time, ten percent of ten thousand five hundred and twenty nine reworked units, against four point one percent of two hundred eighty two thousand two hundred and twenty nine that passed first time. That comparison only exists because the inspection log joins to service history on the serial number, and repairing that join is exactly the work waiting in the section called out the door, further along.

Without it, this number is zero. All four figures in this section, the fail rate, the hand written share, the unmatched reason share, and the field service ratio, are synthetic.

Three mistakes, all mine, none of which threw an error. A join checking a hand typed serial against the manufacturing table forgot to strip the brand prefix first, so it matched nothing, caught by counting matches per side. The catalogue itself listed spellings that collapse to the same key once lower cased, so the join fanned rows out and inflated every hand station's count by three to four percent, caught by checking the view's row count against the sum of its tables. And the routine simulating a mistyped zero could pick a position that was never actually a zero, which nothing can undo afterward: one point one percent of serials were unrecoverable until it was restricted to real zeros.

Prevention is two changes. Give the inspector a coded picklist at the station, with a free text note still there for the engineer to read. And put a capture method column on every record, everywhere, so this answer is always one query away.

Next, the section called downstream zero two: upstream, which lot, which order, and two E R P systems that disagree. To blame a lot for a fail, you need the work order behind it, and that order lives in two systems that don't even agree on what the order is.

Here's what's real about the setup, modeled on purpose. These figures are synthetic, measured on the loaded dataset. The old E R P writes a work order as a ten digit string with leading zeros, item numbers the same way, dates as plain year month day text, and it sends a full dump once a month. The new one writes the same order as a change feed of inserts, updates, and deletes, with a dash in the key instead of padded zeros. Same order, two shapes.

Matching them on the raw key catches nothing, zero matched against fifty three thousand, five hundred seventy one left over. Strip the zeros, add the prefix back, run the item numbers through a crosswalk, and twenty five thousand five hundred match, twenty eight thousand seventy one still don't.

Those last ones are orders that closed before the new system ever existed, so I kept them as legacy only in the view instead of dropping them. Of the twenty five thousand five hundred both systems know about, two thousand four hundred sixty four disagree on quantity or status. The view carries a source of truth column with three values, legacy only, new, and conflict, so a disagreement gets shown rather than silently resolved by picking whichever system I trust more.

Then there's the event stream underneath all of it, eight and a half million station and machine readings loaded, partitioned by day and clustered by line. Of the eight point two million events left after duplicates are merged out, twelve point nine percent arrive one to three days after they actually happened, delayed by the generator on purpose.

On the worst single day, a count taken at midnight only caught eleven thousand five hundred seventy two of the thirteen thousand four hundred eleven events that eventually showed up for that day, fifteen point eight percent short. Close the count at midnight and it's wrong. Close it with a three day watermark and it's right, three days later. The dashboard has to say which one it's showing.

Those events land in a staging table and get merged into the main one on their event I D, and I built the flow to run that merge twice on purpose, checking that the second pass inserts nothing. The first run inserted eight million, two hundred forty five thousand, eight hundred thirty five rows. The second inserted zero. That check is what makes a retry safe at three in the morning.

On the first of October twenty twenty five, the station software renamed its reading column from value to measure, mid year, exactly the kind of thing that breaks a naive pipeline. Three million, eight hundred sixteen thousand, five hundred eighty two rows before that date only have value; four million, six hundred eighty three thousand, four hundred eighteen after only have measure. The view reads whichever column is populated, and flags any row where both are empty instead of quietly reporting it as zero.

Then the cost table. Counting one day by converting every timestamp to a date scans one hundred seventy eight point five megabytes. Filtering on the partition column instead scans eleven hundredths of a megabyte, one thousand six hundred times less. Add a cluster filter for one line and it's twenty two hundredths of a megabyte, because the engine also has to read the cluster column. The warehouse bills a ten megabyte minimum per query, so the actual billed gap is seventeen times, not one thousand six hundred, and partitioning gets decided when the table is created, not later.

Two mistakes here, both caught before anything loaded. First, the generator writing the monthly legacy dump crashed on its second pass because I'd handed a date object to a function expecting a string, a one line fix, but it would have shipped a half written dump. Second was a definition problem, not a bug: the generator delays five percent of events on purpose, yet the measured late share came out at twelve point nine percent, because a calendar day comparison also counts a two hour lag that happens to cross midnight as late. I left the number as measured and wrote the definition next to it, because any real dashboard comparing dates instead of timestamps will hit the same thing.

Next, the section called out the door: the serial that four systems write four ways.

Once a unit leaves the factory, the dealer, the owner, and the service desk each retype that same serial number, and none of them types it the same way. Join on the raw string as each system wrote it, and you match zero units. Nothing. Run it through the catalogue resolved version instead, and it is one hundred and eighteen thousand and eighty two matches, twenty eight point two percent of units registered, plus two thousand five hundred and twenty one duplicate registrations caught along the way. All of it synthetic data, built to make the pattern visible.

Here's the trap that caught my own first attempt. My instinct was to split the model code from the serial with a regular expression. That fails, and fails silently, because model codes themselves contain digits, so once you strip the separators there's no boundary left to find. Take a serial like S W N L zero eight four two zero one zero zero one zero zero. A greedy match slices it in the wrong place and joins to nothing. The only way to find the real boundary is to look it up.

So the fix is five steps. Normalize: strip everything down to letters and digits, uppercase, so a stray space or lowercase letter doesn't matter. Look up the longest matching model code against the actual product catalogue, something like E L forty two ten. Split there, letting the catalogue decide where the model ends and the serial tail begins, never a pattern guessing at it. Clean the remainder: strip leading zeros, correct the classic transcription slips, an O standing in for a zero, an I for a one. Rebuild one standard form every downstream system can agree on.

Every resolved serial then carries a confidence tier, not a flat yes or no. Exact means a downstream system already wrote the standard form itself. Corrected means it needed padding or one character fixed. Partial means it's flagged, and a flagged serial never feeds into a financial number.

There's a folded section underneath on why the data looks like this. No conspiracy, just accidents: the engraved characters are small, printing is inconsistent across product lines, nothing validates a serial as it's typed, older units predate the current format, and dealers sometimes register on a customer's behalf from records already reformatted once. Some things resolution genuinely cannot recover: transposed digits, a serial from a different product entirely, a box barcode typed in instead of the serial itself. Those get flagged, not guessed, because a confident wrong join is worse than a gap.

Two ways to stop it recurring. Validate at entry: check the serial against the catalogue the moment it's typed, capture it by camera in the existing mobile app, and confirm the model back to the user. And for connected products that already pair over Bluetooth, let the device assert its own serial, so nobody types anything at all.

Next, the section called in the field: the return, the warranty clock and the dealer's shelf.

The reworked unit comes back at two point four six times the rate of one that passed first time, and whether that counts as a warranty case depends on a timestamp nobody agreed on.

This section covers three more places downstream where a hidden data problem changes what a number means. First, the warranty clock. Warranty eligibility is judged on when the system heard about a registration, not when the owner acted. I modelled this because registration timestamps carry no time zone at all. Seventy eight thousand, seven hundred and twenty units are eligible on our clock, seventy nine thousand and eighty six on theirs. One hundred and ninety nine decisions flip on time zone alone, and three hundred and sixty six units would be wrongly refused a claim they are actually entitled to.

Second, the dealer's shelf. A dealer changed their file format overnight. Nothing errored. A parser built for the old format silently dropped whatever it could no longer read. Of two hundred forty thousand, nine hundred and fifty six sell through rows, only one hundred seventy two thousand, three hundred and ten survive that silent drop, and sixty eight thousand, six hundred and forty six rows land in the wrong month.

Third, seasonality. Hunting spikes for the season opener. Birding never spikes. Averaging hides both. Blend the two segments together and the peak reads as one point five two times average demand. Split them apart and the hunting segment's real peak is two point five five times average. Averaging the two together does not soften the picture, it hides it.

All of these figures are synthetic, and the dashboard page behind each one still exists, one click away in the underlying report.

Next, the section called the people around it: the owner nobody can reach and the lead counted twice.

This whole page has followed one serial number through the factory. This section asks the same question about people. If this unit gets recalled, can you reach the owner? And on the marketing side, when someone converts, does the system know that's the same person twice, or does it quietly double them? The same identity gap that scrambles a serial number scrambles an email address in the C R M just as easily.

Here's the concrete number. Count rows the way both dashboards do it today, every web form submission plus every C R M lead row added together, and you get two hundred nine thousand and forty seven leads. That is not two hundred nine thousand people. Resolve every row down to one key per actual human, and the number drops to seventy seven thousand, six hundred and fifty one. All synthetic figures, built for this exercise, but the same disease as the serial, just on an email address instead of a serial number.

Underneath that number is a short, ordered set of rules, with a tier written onto every row forever. Lowercase and trim the address, that alone fixes most of it. Collapse a provider's alias trick, only where the rule is documented. Do not repair a typo. A domain one character off gets flagged, not silently guessed at. Of those two hundred nine thousand rows, one hundred thirty six thousand, eight hundred fifty six matched exactly, seventy thousand, five hundred nineteen needed normalizing, and one thousand, six hundred seventy two stayed flagged rather than get force merged into someone else's identity.

Campaign names carry the same disease. Three hundred eighty real campaigns show up on the web as three thousand and forty six distinct spellings. Join the cost report on that raw string, the way it's joined today, and search advertising looks like it costs nine hundred sixty six euros a lead. Resolve the names first, and it's fifty six euros. The spend never moved, the join did. Email shows the widest gap, two thousand and sixty nine euros raw against fifty seven point nine resolved, and the same shape repeats across social, dealer events, and print and Q R codes.

One mistake I made building this is worth saying out loud, because it's the honest version of this whole page. My generator built those campaign names by combining a subsidiary, a theme, and sometimes a model, picked at random, and only two hundred fifty five of the three hundred eighty came out distinct. The campaign view joins on the normalized name, so a session landing on a name two campaigns happened to share silently fanned into two rows and doubled that campaign's numbers in the cost table.

Nothing threw an error. I caught it by counting distinct names before trusting the join. The fix was a suffix on the repeats and a test that now fails the moment two names collide again.

Next, two sections: what done means for this one problem, and where I would start, and why.

I said the real question, why a unit failed and what it cost, only gets answered once a pipeline can prove six things about itself. First, it runs on a schedule and checks its own output, refusing to report success against an empty table or a number that didn't move the way it should. Second, the number on the dashboard is the number in the query, and that query has an owner, version controlled. Third, every row carries where it came from, who or what captured it, and how confident the fix is, so the dashboard can show you the share that's guessed, which is zero, and the share that's flagged, which is not.

Fourth, what can't be recovered is written down beside the number, because a stated limit is a decision and a hidden one is a future incident. Fifth, it's safe to run twice, so a retry at three in the morning can't double anything. Sixth, the mistakes get logged, with what actually caught each one.

Done is a state the pipeline can prove, not a status somebody sets. The same test covers a move to a new cloud setup: it is done when the old system is empty and the new one passes the same six checks.

Then I ranked everything on this page by value to production and sales against the effort to build it. The top four are all high value, and none of them is a dashboard. Put a capture method column on every record. Build the quality control reason catalogue. Resolve the serial key across the systems that spell it differently. And make the event loads idempotent and partitioned. The first two are low effort: capture method just needs one column the loader can populate, and the reason catalogue needs coded reasons, not a new system, since that dashboard already exists.

Below those, lead identity resolution and a campaign name registry are medium value and low effort, and that registry alone collapses three thousand and forty six campaign spellings down to the real three hundred and eighty. Reconciling the two E R P systems is high value but high effort, since it needs that key work done first and an owner for the conflicts that follow. Warranty timing on the customer's clock and late data watermarks are medium value, medium and low effort. That pattern, keys and columns before dashboards, is where the easy wins sit in most warehouses I've seen, including my own.

Next, the section on where automated tools fit, and where they do not.

Every messy input on this page is something today's models read well, and four examples show it. A free text failure note, in any language, becomes a proposed reason code with a confidence score, and once an inspector approves it, it becomes a rule. A photo of a receipt becomes a proposed purchase date, entered as a claim with its own confidence score, not as fact. The app's camera reads an engraved serial and checks it against the same catalogue used to resolve serials on this page. And for an odd dealer file, a model proposes the mapping, a person approves it once, and it becomes a rule too.

In every case, I treat that output as one more source with a confidence score. It never writes a financial number, and it never writes a quality verdict, on its own.

That is the same rule I use through the rest of this page. Resolve what you can, flag what you cannot, never guess. The models are already good enough for all four of these jobs. The question is no longer whether it can read the input. It is what it is allowed to touch, and that is a governance question.

Next, the section on four flows, one shape, and every one checks its own work, where I show you how this whole page actually runs.

Underneath it are four separate pipelines: product, people, the production scale event stream, and quality control. I built all four the same shape on purpose. Each one generates its own synthetic data, loads it into the warehouse in parallel, builds views on top, then runs a verify step that queries what it just built and refuses to report success if a table comes back empty, or if the headline number didn't move the way it's supposed to. A pipeline that says done without checking anything is the exact failure this whole page argues against.

The proof is screenshots of those runs actually finishing. The product slice: eleven tasks, four loads in parallel, landing as four tables and eight views. The people slice: five loads in parallel, same verify step. The production scale slice is the big one: eighteen tasks, most of it one long load of eight point five million station events across nine chunks, the merge running twice on purpose with the second run inserting nothing, fourteen minutes end to end.

The quality control slice: fourteen tasks, five loads in parallel, six views, landing as five tables holding component inspections at lot grain, unit inspections at serial grain, the reason catalogue, and the lot to serial genealogy table. Its verify step also checks that the naive serial join matches fewer rows than the resolved one before it calls the run good, ninety seconds end to end. I pulled the raw rows too: final inspection fails from the unit inspection table, hand written columns visible, the free text reason, the operator's initials, logged time against actual time.

The stack behind it: Python for the pipeline's own code, Prefect as the workflow scheduler that runs it, BigQuery as the cloud data warehouse with partitioned and clustered tables, views kept in version control, Terraform for everything around it, and Looker Studio as the dashboard layer on top. Synthetic data throughout.

Next, the section on sixteen more consequences of the same problem, sketched.

These sixteen aren't built out the way the earlier sections are. Each one is just the trap and the shape of the fix, using the same synthetic data.

Ten sit on the marketing and C R M side. One example: eighty point six percent of won deals close after their campaign has already ended, so judging the campaign at its end date makes a win look like a loss. Other sketches in that group cover a lead that goes silent after being handed to a dealer, consent that needs its own timestamp, bot traffic that gets rejected and kept rather than deleted, and a form field that quietly drops out and takes a lead score with it.

The other six sit on the product side, on the same serial and registration tables. Duplicate registrations that are really families, not errors, two thousand five hundred and twenty one units of them. A unit crossing a border between the dealer, the registration and the service desk. A returned unit that gets resold and counted twice. And a recall trace, where seventy one point eight percent of owners have no contact path at all.

Same rule as everywhere else on this page: resolve what you can, flag what you can't, never guess.

That's the page: one failed binocular, followed all the way through six systems, with the mistakes and the limits written down next to the numbers instead of hidden underneath them.