Two dashboards, two figures, one plant. Neither is wrong in the arithmetic sense; both are wrong in every sense that matters. Quality pulls from the inspection database, production from the MES, maintenance from the historian, and each system holds its own view of what happened on line three last Tuesday. I have sat in more meetings than I care to count where this exact scene plays out, and it never ends in insight. It ends in a spreadsheet.
This is data everywhere and answers nowhere. Every sensor logs, every tester records, every operator keys something into a terminal, yet the basic question — which parts, made under which conditions, measured by which gauge, turned out nonconforming — takes a data engineer three days to answer, if it can be answered at all. The problem is rarely analytical capability. It is architecture. Nobody designed the joins, so nobody can make them.
The failure is structural, not cultural. Each system was justified on its own merits: the historian for process monitoring, the MES for traceability, the LIMS for lab results, the QMS for nonconformances. Each vendor delivered what was asked. What nobody asked for was the ability to connect a torque curve on a fastening station to the leak test result on the same serialized unit three stations later. That connective tissue must be designed deliberately, and it is engineering work, not an IT procurement exercise.
Contextualisation: A Measurement Without Its Context Is Noise
A dimension of 12.07 millimetres means nothing on its own. It means something when you know the part serial, the cavity in the injection mould, the machine number, the shift, the ambient temperature, the tool's shot count, and the gauge that produced the reading. Contextualisation is the discipline of binding every measurement to the conditions of its creation — at the moment of creation, not retroactively.
Retroactive contextualisation is where plants go to die. Someone tries to reconstruct, six months later, which cavity produced a suspect batch by matching timestamps across three systems with different clocks and different batch-naming conventions. The honest answer is usually that it cannot be done with confidence. Across two decades in automotive and aerospace, I have seen containment decisions — scrap versus rework versus ship — hinge on exactly this reconstruction, and I have seen the reconstruction fail.
The architectural answer is a context record that travels with every data point: part identifier, serial if you serialize, station, tool or cavity, batch, shift, operator (pseudonymised if works council rules require it), and gauge identifier. These fields must be captured at source by the equipment or the application, never inferred later from logs. Test this deliberately: pick a hundred random measurements from your historian and determine, from the data alone, which cavity made them. If you cannot, your data has volume but no value.
Time Alignment: Three Clocks, No Agreement
Every plant I have worked in runs on at least three clocks that disagree: the PLC clock, the server clock, and the database timestamp — and the operator's wristwatch often matters more, because handwritten records still exist. Drift of a few seconds between a process system and a test system is normal; drift of minutes is common. Once you join events by timestamp alone, that drift becomes indistinguishable from real process causality.
Consider the sequencing problem concretely. A part leaves the press at 10:42:15 per the press controller, arrives at the vision station where the image is stamped 10:42:09 because the vision PC ran six seconds behind, and the result lands in the quality database at 10:42:51 with the server's own offset. Which timestamp is the truth? None of them. The truth is the sequence, and timestamps are a poor proxy for sequence unless you engineer them to be.
NTP synchronisation across all data-producing systems is the baseline, and it is astonishing how often it is missing on shop-floor equipment commissioned years ago and never touched since. But synchronisation alone is insufficient, because even synchronised clocks leave millisecond ambiguity on fast lines. The robust method is event sequencing with explicit unit tracking: the carrier, RFID tag, or serial number defines the order of events, and timestamps only corroborate.
Audit the architecture with a deliberately injected fault — a part routed out of sequence — and see whether the joined data tells the story correctly. Most systems I have audited fail this test the first time. That failure is cheap to find with a test part and ruinous to find with a customer complaint.
The Join Nobody Can Make
Here is the join that defeats almost everyone: connecting process parameters to final quality outcomes on a per-unit basis across multiple operations. The MES knows a serial passed through stations A, B, and C in a certain order. The historian knows what pressures, temperatures, and torques occurred in time windows at each station. The tester knows which serials passed and failed. To answer whether the fluke failures correlate with cold-start press conditions, you must join serial to time window to parameter, three times, with no errors.
The failure mode is subtle. A naive join by timestamp window will occasionally grab the wrong cycle — the carrier that backed up, the rework loop that sent a part through twice, the sensor glitch that split one cycle into two records. These edge cases are rare on paper and devastating in aggregate, because they poison the correlation analysis. You conclude a parameter is irrelevant when in fact your join silently mismatched the interesting units.
A dashboard on a broken join is not a tool; it is a confident lie with a nice interface.
Station-centric versus unit-centric data models
Station-centric (typical)
- Each station writes its own table keyed by its own timestamps
- Cross-station history requires timestamp-window joins
- Rework loops and backups corrupt the join silently
- Full unit history assembled manually by a specialist
Unit-centric (what works)
- Every event writes against a unit identifier as primary key
- Station and sequence position are attributes, not keys
- Out-of-sequence routing stays visible instead of ambiguous
- One serial in, one complete ordered history out
What makes this join tractable is a unit-centric data model rather than a station-centric one. Instead of each station writing its own table keyed by its own timestamps, every event writes against a unit identifier as the primary key, with the station and sequence position as attributes. This is not exotic technology; it is a modelling decision made early or never.
When I evaluate a plant's data architecture, I ask for one serial number and request the complete, ordered history of every process value, measurement, and disposition attached to it. If producing that takes manual work, the join is broken, no matter what the vendor slide deck claims.
Architecture Metrics That Matter
Quality departments measure processes relentlessly and their own data systems almost never. Three concrete metrics change that. First, join completeness: of all units produced in a period, what fraction have a full chain of records with no gaps from press to final test? Track it weekly by line. Gaps concentrate where the interesting problems live, because the units that cause trouble are the ones that get reworked, re-routed, and manually handled — precisely when records go missing.
Second, timestamp coherence: sample pairs of systems that must be sequenced and measure their clock offset and the rate of sequence violations — records where the data claims a test happened before the operation that produced the part. A sequence violation rate above a handful per week points at clock drift or broken event association, and both need fixing before any analytics built on that data deserve trust.
The three numbers to publish per line
Third, attribution latency: how long from a nonconformance being detected to an engineer being able to pull the full contextual history of the affected units? If the honest answer is measured in days because a specialist has to assemble it, your escalation and containment are being throttled by your architecture. Measure the latency, publish it, and treat reductions in it as genuine quality improvements, because faster attribution means smaller containment lots and less scrap.
Practical Steps, in Order of Leverage
Start with the identifier, because everything else is downstream of it. Decide what the unit of traceability is — serial, carrier, lot, or a hierarchy of them — and enforce that every system records it at source. Retrofitting identifiers onto systems that never captured them is painful; choosing the identifier model for new equipment is nearly free. Write it into purchase specifications: any machine producing quality-relevant data must emit records keyed to the unit identifier, with a defined interface contract, or it does not pass commissioning.
Sequence of leverage
- 01Fix the identifierDefine the unit of traceability and write it into equipment purchase specifications
- 02Fix timeAudit NTP coverage on every data-producing device; log clock offsets centrally
- 03Build the join layerEvent store keyed by serial; validate with deliberately mis-routed test parts
- 04Then dashboardsOnly once the join passes the injected-fault test
- 05Govern itNamed owner, written data dictionary, meaning of fields enforced per station
Then fix time. Audit NTP coverage on every data-producing device, including the old ones, and log clock offsets centrally so drift becomes visible rather than latent. After that, build the unit-centric join layer — often a simple event store keyed by serial — and validate it against known ground truth: deliberate test parts, routed deliberately oddly, must emerge from the join with a correct, complete history. Do this before building dashboards.
Governance: Preventing the Quiet Decay
Finally, govern it. Someone must own the meaning of fields — what "cycle start" means at each station, which timestamp is authoritative, how rework loops are recorded. Without a named owner and a written data dictionary, the architecture decays quietly with every new machine that arrives with its own conventions and its own clock.
The decay follows a predictable path. The first machine integrated without the interface contract introduces a new event format. A workaround join is written to accommodate it. Six months later the workaround is load-bearing, undocumented, and wrong on edge cases nobody remembers. Within two years you are back to data everywhere and answers nowhere, except now with the added confidence of dashboards that everyone trusts.
I have watched that decay happen more than once. It is entirely preventable, and almost never prevented — because prevention looks like bureaucracy until the day a containment decision has to be made in hours instead of weeks, and the serial number queried against a join layer that actually works. That is the whole point of the architecture: not dashboards, but answers a quality decision can stand on.