A Plausible Wrong Number Is Worse Than No Number

A few years ago I was working on a SAP integration, and the first stretch of every conversation went sideways over a single word: Product.

The SAP consultant kept saying “Product.” In SAP’s vocabulary, a Product is the concrete thing moving through production: what an MES calls a Material, or a Lot. In MES vocabulary, a Product is the definition, the abstract type that sits one level above any physical instance. So every time he said “Product,” I heard the type; he meant the lot on the line. We were both fluent and both precise, and we were talking straight past each other. It took stopping, putting the two glossaries side by side, and mapping the words to each other.

Two people in a room can do that. They feel the confusion, back up, and realign. A data pipeline cannot. When the exact same mismatch is a string value inside a Kafka event, no one is in the room to notice. That is the point where a naming problem stops being a slow conversation and becomes a silently wrong number.

Picture a machine whose states are tagged SEMIE10 instead of SEMI-E10. Nothing errors. The events flow through Kafka, land in the warehouse, and the source tables look fully populated. But the transformation layer looks up SEMI-E10, finds nothing, and that machine now has no state duration: no Availability, no Performance Efficiency, no OEE. The line dashboard fills the gap with the machines that did report, so the number doesn’t look broken. It looks a little high. An operator trusts it, a cross-plant comparison inherits it, and a model trains on it.

That failure mode is a believable wrong number rather than a crash, and it is the hardest, most common problem in connecting a new MES to a shared analytics pipeline. It almost never shows up in the source system, where the data looks right, and it almost never shows up as a pipeline failure, because the pipeline runs clean. It surfaces as a gap, a zero, or a plausible-but-wrong metric on a dashboard, with a root cause buried in a string value inside an event. This post is about why that happens and how to stop it happening to you.

The hard part is the values#

A factory running Critical Manufacturing’s MES represents a material state transition differently than one running SAP ME, AVEVA, or Camstar. Field names, value vocabularies, and hierarchy structures all differ. So does event granularity: one system emits a single event per step transition, another emits separate Dispatch, TrackIn, TrackOut, and MoveToNextStep events. My “Product” versus his “Lot” was one small instance of this, settled by two people over a whiteboard. A pipeline spanning many systems has to settle thousands of them, with no whiteboard and no one to ask.

For analytics that work across factories and MES platforms, that is fatal. You cannot write OEE logic once when “Productive” is spelled six different ways. You cannot compare two plants when each names its steps, states, and shifts on its own terms. Every new system multiplies the integration surface, and every difference is a place where a metric can quietly come out wrong.

A Canonical Data Model (CDM) is the answer to that: a fixed set of event types (Material_MaterialOperations, Resource_ResourceStateChange, Calendar_CalendarDay, and others), each with a well-defined schema. Every event carries the same organizational hierarchy, the same temporal keys, and the same domain fields, regardless of the platform underneath. When a factory runs CM’s platform, CDM events are emitted natively. When it runs a different MES, an integration layer translates that system’s events into CDM before they hit the pipeline. From that point on, the pipeline neither knows nor cares which MES produced the data.

The payoff is simple to state: write the analytics once, connect any MES. CDM events flow through Kafka into ClickHouse, where a dbt pipeline turns them into material-movement metrics, WIP snapshots, OEE, yield and loss, maintenance reliability (MTBF/MTTR/MTTA), and plan adherence. Because every metric derives from the same canonical event stream, the pipeline can also relate events that point tools would investigate separately. A single production event that lowers OEE, adds scrap, and opens a quality deviation becomes one situation instead of three investigations chasing three causes.

All of this works only as long as the input conforms to the contract, and that contract covers more than shape.

The contract is behavioral#

The CDM schema doesn’t only define which fields exist. The pipeline interprets specific field values to drive calculations. Get the shape right and the values wrong, and everything ingests cleanly and computes garbage. Three examples cover the majority of real failures.

Processing states must be exact. Material_ProcessingState must contain one of Queued, Dispatched, InProcess, or Processed, and it is case-sensitive. These strings decide which duration bucket an interval falls into and which quantity snapshot is captured. An event carrying IN_PROCESS or in process ingests without error, because the platform validates that mandatory fields are present, not that their content is correct. The result is zero InProcess duration and zero InProcess quantity in the output. Silently.

Resource states use parallel arrays. A Resource State Change event carries two arrays: Resource_StateNames and Resource_StateValues. The pipeline finds SEMI-E10 in the names array and reads the value at the same index (Productive, Unscheduled Down, and so on) from the values array. If the name is Semi-E10, the lookup fails and that resource has no SEMI-E10 state duration, and therefore no Availability, no Performance Efficiency, no OEE.

Join keys must match character-for-character. The Calendar_Name in a Resource State Change event must exactly equal a Calendar_Name in the Calendar Day events. Production Calendar on one side and ProductionCalendar on the other, and the join returns nothing: no shift-level analytics for that resource. A trailing space does the same thing.

This is the dominant failure mode in CDM integrations. The data looks right in the source. The pipeline runs without errors. The dashboards show gaps, zeroes, or missing entities, and tracing the cause back to a mismatched string in a Kafka event is anything but obvious.

Silent failure is a deliberate tradeoff with a sharp edge#

The obvious question: why not validate field content at ingestion and reject bad events? Two reasons.

First, the CDM is extensible. Many fields are descriptive. They are carried through for context, filtering, and drill-down, but never touch a calculation. Rejecting events against a fixed value whitelist would make the schema rigid and block legitimate data from reaching the warehouse.

Second, “correct” is context-dependent. A Material_ProcessingState of Setup is not wrong in the MES; it is simply not a value the current pipeline interprets. The event should still be stored, since a future revision might use it. The CDM is a transport layer, not a gatekeeper.

So the design is flexible at ingestion and strict at transformation. That is the right call, but it has a sharp edge: a wrong value produces a plausible, wrong number, and a plausible wrong number is the most dangerous output a manufacturing system can generate, because everything downstream treats it as truth. The operator, the cross-plant comparison, and the model in training all inherit it. The contract document exists to close that gap: it tells integration teams which values the transformation layer depends on, so they get them right before an event is ever emitted.

One language, but whose?#

A fair objection from anyone who has read a standards spec: why a vendor-defined canonical model instead of an open, neutral standard? OPC UA, MQTT Sparkplug, the 2025 ISA-95 revision, and the Manufacturing-X / Catena-X data-space efforts all stake a claim to shared cross-vendor vocabulary.

None of them ships the value vocabulary our pipeline needs. Several define real domain semantics (OPC UA’s companion specifications and Catena-X’s aspect models both do), but none tells your pipeline that InProcess, and only InProcess, fills the in-process duration bucket. That last mile (the behavioral contract on field values) is where analytics correctness actually lives, and it is what a general-purpose interoperability standard leaves open. A canonical model for analytics complements those standards: it pins down the value vocabulary they deliberately leave flexible. Where an open standard already fixes a value (units, timestamps, hierarchy encoding), the CDM should adopt it rather than reinvent it. Say that out loud, and the “one language, but yours” objection loses its teeth.

That last-mile precision is also what makes multi-site comparison honest. Consider several plants making the same product. If each labels its states, steps, and calendars its own way, even after the data lands in one warehouse, the numbers can never be compared on equal terms, and enterprise decisions get made on information quietly shaped by how one plant happens to work. Same event types, same value vocabulary, same keys: that is what lets a “productive hour” or a “queue time” mean the same thing in every plant it is reported from. It is also the foundation for anything more ambitious. A digital twin is only as current as its runtime feed, and a model is only as trustworthy as the labels underneath it.

Practical guidance for integration teams#

If you are building a CDM integration for a non-CMF MES, in priority order:

1. Start with the value-dependent fields. Only a handful of fields carry string values that drive analytics: Material_ProcessingState, Header_Operation, Resource_StateNames / Resource_StateValues, and Change_Type. Get these exactly right first. Everything else is structural.

2. Validate join-key consistency early. Pick one material, one resource, one calendar. Emit events and verify that the organizational hierarchy (Enterprise_Name, Site_Name, Facility_Name, Area_Name) and Calendar_Name match character-for-character across every event type. (These follow ISA-95’s Enterprise → Site → Area levels. ISA-95 names no level between Site and Area, so for large or complex sites CM adds a Facility archetype there rather than nesting Areas within Areas.) A trailing space will break a join.

3. Test against the pipeline, not just the database. Events landing in ClickHouse does not mean they are correct. Run the dbt pipeline and inspect the output tables. If a material shows zero cycle time, or a resource shows no SEMI-E10 state, the field values are wrong, even when the source table looks fully populated.

4. Emit events ordered and prompt; size your lookback deliberately. The pipeline tolerates out-of-order and late events: window functions re-sort each entity by Header_DateTime at query time, incremental runs re-scan a lookback window (OPERATION_ENDTIME_LOOKBACK_MINUTES, default 20) so late facts fold back in, and ReplacingMergeTree deduplicates the re-ingested rows. But tolerance is not free: a wider lookback re-scans more data every run (CPU and memory), and anything beyond the horizon is missed until a full refresh. Size the lookback to cover your real latency (roughly 2× observed P99) and no wider.

The bottom line#

The contract is strict because the analytics are precise. OEE is Availability × Performance × Quality, each derived from specific state durations and quantity snapshots. If the input is off by a single string value, the output is off by a full metric, and it will not tell you.

Get the CDM integration right and the analytics, the digital twins, and the AI on top of them come for free. Get it wrong and the error compounds silently, at exactly the moment the organization starts to rely on it. The examples above are the difference between the two.

Author#

Hi! My name is Ricardo Magalhães. 🤘#

You can check me on LinkedIn