Clock Drift, Duplicate Serials, and ERP Mismatch on a Device Line
A medical device assembly line uses MES to capture serialized device test results from 12 test stands over OPC UA. MES also receives work-order status from ERP every 15 minutes by flat file. The line produces 4,800 units per shift. Recent data shows 1.8% of test records have a timestamp earlier than the preceding operation, 37 duplicate serial numbers in the last week, and ERP reports 120 units completed that MES shows as in process. The OPC UA server on test stand 7 has a 45-second clock drift; the others use NTP. A new integration developer changed the queue retry logic to retry indefinitely last month. Production wants real-time dashboards, but quality wants to lock the batch record at end of shift. Diagnose the likely causes and propose controls.
1. Diagnose the likely causes of the timestamp, duplicate serial, and ERP mismatch symptoms.
Read the worked answer
The timestamp problem is most likely clock skew: stand 7 is 45 seconds off, and MES may be ordering events by ingestion time rather than source time or a trusted sequence. The 1.8% out-of-order rate is too high for normal processing variance. The 37 duplicate serials are consistent with indefinite retries: if the MES write is not idempotent, a timeout followed by retry can insert the same serial twice. The ERP mismatch is partly a latency issue: the 15-minute flat file means ERP completed status lags MES, and MES in-process status may not be updated when the file is delayed. It can also reflect status-model differences between ERP and MES. The developer's change removed a bounded retry and dead-letter path, so failed messages may be stuck or duplicated. These are data-integrity symptoms, not three separate operator errors.
2. What integration and architecture controls would you put in place?
Read the worked answer
Use UTC timestamps from a trusted source and require NTP or PTP on all test stands, with drift alarms. Store both source time and ingestion time, and use a monotonic sequence number per stand for ordering. Make the MES write idempotent by using the serial number plus test-stand event ID as a unique key, and reject duplicates at ingestion. Replace indefinite retry with bounded retry, exponential backoff, and a dead-letter queue that raises an alert. Add message acknowledgements and a reconciliation report comparing MES and ERP counts by work order. For ERP, use an API or event-based interface if possible; if flat files remain, define a cut-off and a reconciliation window. Separate the real-time dashboard from the system of record so dashboards can be eventual-consistency views while the batch record is controlled. Log integration failures and review them daily.
3. How do you reconcile quality's end-of-shift batch record lock with production's real-time dashboard requirement?
Read the worked answer
Do not let the dashboard and the batch record share the same uncontrolled data path. Let production view a near-real-time operational dashboard fed from the integration stream, labeled as operational and not the official record. Keep the batch record controlled by a cut-off at end of shift, but define a reconciliation window before final lock: late or corrected test results must go through an approved correction with reason and audit trail. Run an automated pre-lock check for duplicate serials, out-of-sequence timestamps, and missing tests. If any exception exists, hold the record for QA review. This gives production visibility without weakening data integrity. Document the cut-off, the correction procedure, and the dashboard's status so users do not treat operational data as released batch data.