A supplier phones to say a gasket lot is out of specification. Which finished lots used it, and which have already shipped? Manufacturing data integration is the work of joining production records, quality results and supplier receipts on shared keys, so that question is answered from joined data rather than by searching three systems by hand. Those records usually sit in the MES, the quality system and the ERP, and they don't share one reliable key for the supplier lot.
We tested that on illustrative synthetic data: 900 finished lots, 120 supplier lots and one planted bad gasket lot used in 37 finished lots. An exact lot-code join found 31 of the 37. Normalising formats, mapping internal receiving IDs and a date-window fallback for missing scans found all 37, in a hold of 43 lots.
Figures checked 17 November 2026. This guide is for quality, operations and data teams in discrete and batch manufacturing who answer supplier and customer traceability questions. For the general case of two systems disagreeing on one value, read DataFuseAI's post on multi-source data conflicts.
Key Takeaways
- Quality results alone miss affected lots. In our test, inspection failed 19 of the 37 affected lots; the other 18 passed and would have shipped.
- Exact lot-code joins leave gaps. They traced 379 of 900 finished lots end to end and found 31 of the 37 affected lots.
- Internal receiving IDs are the hidden key. Mapping them back to supplier lots found 4 more affected lots.
- Missing scans need a fallback, labelled as such. A date window found the last 2 affected lots, plus 6 that weren't affected.
- A blunt hold is large. Holding everything made with any gasket in the window meant 114 lots, of which 77 were fine.
Why is production, quality and supplier data hard to combine?
Each system records a different event and keys it differently. The ERP records the goods receipt against the supplier's lot code. The MES records which material went into which finished lot, keyed by whatever an operator scanned. The quality system records inspections against the finished lot.
| System | What it records | Key it holds | What goes wrong in our data |
|---|---|---|---|
| ERP | Goods receipt from a supplier | Supplier lot code and an internal receiving ID | Nothing; it is the reference list |
| MES | Material consumed into each finished lot | Whatever label was scanned | 306 scans in a different format, 219 scans of the receiving ID, 135 missing |
| Quality system | Inspection result per finished lot | Finished lot code | Results don't name the supplier lot |
Source: DataFuseAI test on illustrative synthetic plant data, 17 November 2026. Counts are out of 2,676 consumption rows.
The problem is common outside our test data too. In a Manufacturing Leadership Council survey published in July 2026, integrating data from different sources was the second most cited obstacle to data-driven decisions, at 37% [1]. Only 42% of respondents said their company had a process to verify manufacturing data accuracy and quality [1].
NIST describes a related gap across supply chains, from a security angle. Its final IR 8536, dated 9 September 2026, says organisations struggle to obtain the pedigree and provenance they need [2]. Its answer is an industry-neutral framework that lets companies keep using existing industry standards [2].
How we tested
We generated one plant's data in Python with a fixed seed. Six materials came from 29 suppliers in 120 receipts. Each of 900 finished lots consumed two or three materials, drawn from the three most recently received lots of each. One gasket lot, 125-G30-8271, was planted as bad.
Scans were planted the way they often go wrong. Of 2,676 consumption rows, 2,016 (75%) carried the exact supplier lot code. The rest carried a format variant, the internal receiving ID or nothing. Affected lots failed inspection with a 55% chance; other lots with a 3% chance.
We then traced at three levels, each keeping the one before. Exact joins came first, then normalised codes, then a receiving-ID crosswalk. A date-window fallback came last. Because the data is synthetic, we know which lots really contain the bad gasket, so every hold can be scored.
How many finished lots can you trace back to a supplier lot?
With exact text joins, 379 of 900 finished lots traced back to a supplier lot for every material they used. Normalising formats raised that to 582. Mapping receiving IDs raised it to 769, or 85% of finished lots, using only data the plant already holds.
The two counts moved differently. Normalising formats added 203 fully traced lots but no affected ones, because every recorded scan of the bad gasket was either exact or a receiving ID; the other 2 were never scanned. The crosswalk added 4 affected lots. Which fix matters on the day depends on how the bad lot happened to be scanned.
The remaining 131 finished lots each have at least one material with no lot scanned. No join can recover a scan that was never made. That is a shop-floor fix, not a data fix.
How big does the hold need to be?
The trace with a date-window fallback put 43 lots on hold and caught all 37 affected lots. Quality results alone held 34 lots and missed 18. Holding everything that used any gasket during the window caught all 37 but held 114.
| Approach | Lots held | Affected lots caught | Unaffected lots held | Affected lots missed |
|---|---|---|---|---|
| Quality failures only | 34 | 19 | 15 | 18 |
| Exact lot-code trace | 31 | 31 | 0 | 6 |
| Full trace plus date-window fallback | 43 | 37 | 6 | 0 |
| Any gasket used in the window | 114 | 37 | 77 | 0 |
Source: DataFuseAI test on illustrative synthetic plant data, 17 November 2026.
The window runs from the first to the last traced use of the bad lot: 7 to 28 September 2026 in our data. The fallback added 8 finished lots whose gasket scan was missing in that window. Two contained the bad lot; six did not. They are candidates for inspection, not proven matches.
Quality data still earns its place in the join. Ranked by inspection failure rate, the bad gasket lot came first among supplier lots with at least five linked finished lots, at every trace level. Its linked lots failed at 48.4% with exact joins and 51.4% with the crosswalk. That fail rate was planted, so the ranking shows the method can surface a planted signal, not how strong the signal is in a real plant.
What does one missed lot look like?
Finished lot FG-50195 shows how an affected lot slips through every check. It was made on line L1 on 13 September 2026. Its resin and pigment scans carry supplier lot codes. Its gasket scan reads RCV-20055, the ERP's internal receiving ID for the bad lot, received on 7 September.
An exact join on supplier lot codes never matches RCV-20055, so FG-50195 drops out of the trace. It also passed inspection. Without the crosswalk from receiving ID to supplier lot, only a blunt hold on every gasket used in the window would catch it.
Four steps to join production, quality and supplier data
Joining production, quality and supplier data takes four steps, applied in order on every run. They are a DataFuseAI editorial framework, not an industry standard. Steps 1 and 2 produce exact matches. Step 3 produces candidates that someone must check. Step 4 tells you where the trace will fail next time. Quality results then join to the traced lots on the finished lot code.
- Normalise formats. Upper-case lot codes and strip hyphens, spaces and slashes on both sides before joining. Keep the original code beside the normalised one, and check that no two supplier lots collide after normalising.
- Crosswalk internal IDs. Build a table from the ERP that maps each receiving ID to its supplier lot, and resolve MES scans through it.
- Use a date window only as a fallback. For consumption rows with no usable scan, list finished lots of the same material made while the bad lot was in use. Mark them as candidates.
- Report what is still untraced. Count untraced finished lots by line and material on every run, and fix scanning where the count is highest.
Keep fuzzy matching away from lot codes. DataFuseAI's post on fuzzy matching in ETL pipelines recommends exact matching for identifiers and system-generated keys. Two lot codes one character apart are usually two different lots.
What does FSMA 204 ask food manufacturers for?
For foods on the FDA's Food Traceability List, the rule asks firms to assign a traceability lot code when they transform a food [3]. All key data elements, including that code, must be linked to the relevant traceability lot [3]. Unless a firm is exempt, an electronic sortable spreadsheet is due within 24 hours of a request, or a longer time FDA agrees [3].
The FDA has proposed moving the compliance date by 30 months to 20 July 2028 [3]. The same page says FDA will follow a congressional directive not to enforce the rule before then [3]. Check the FDA page for your own obligations; this guide is not legal advice.
Mistakes to avoid
- Treating inspection failures as the hold list. In our test, 18 affected lots passed inspection.
- Joining on lot-code text as typed. Case, hyphens and spaces split one lot into several codes.
- Ignoring receiving IDs in MES scans. Operators scan the label in front of them, which may be the plant's own.
- Presenting window matches as proof. Six of the eight fallback lots in our data were not affected.
- Building the trace only when a supplier calls. A scheduled run keeps the joined tables ready and shows the scanning gaps early.
Where DataFuseAI fits (and where it doesn't)
The joins in this guide are standard pipeline steps: derive a cleaned code, join, and filter by date. We did not run this test in DataFuseAI, but it schedules steps like these over the databases you already run. Its manufacturing industry page describes connecting the databases behind MES, ERP, quality and SCADA systems [4]. It also describes a supplier-health walkthrough that joins supplier, receipt and quality data on a no-code canvas [4].
The page describes scheduled batch runs at shift-end, nightly or on your reporting cycle [4]. It says these are not a live feed off the line, and that DataFuseAI is not a streaming or condition-monitoring layer [4]. The pipeline automation page says each run's duration, status and step log are kept [5].
What it does not do matters here. The manufacturing page doesn't describe lot or batch genealogy features, and says nothing in your plant stack is replaced [4]. DataFuseAI holds no sector certification, and ISO 9001 stays your own plant's certification [4]. For the trace in this guide, it would build the joined tables; your MES, ERP and quality system stay the records.
To try the four steps on one recurring trace report, DataFuseAI's free 30-day POC asks for one recurring workflow and two or three data sources [6]. Applications are reviewed for fit, there is no POC fee or obligation, and external cloud or compute costs, if any, stay with you [6].
Frequently Asked Questions
What is lot genealogy in manufacturing?
Lot genealogy is the record of which input lots went into which output lots, so a trace can run forward from a supplier lot or backward from a finished lot. It depends on consumption records that name the input lot. In our test, joining MES scans to ERP receipts made 769 of 900 finished lots traceable end to end.
Do I need real-time data for lot traceability?
Usually not. A trace answers questions about lots already made, so a batch run at shift-end or nightly keeps the joined tables current enough. Real-time data matters for controlling the line itself, which belongs in your control systems. DataFuseAI's own manufacturing page draws the same line [4].
Can fuzzy matching link mismatched lot codes?
It is risky. Lot codes that differ by one character are often different lots, so a similarity score can join the wrong ones. Normalise formats with fixed rules and map internal IDs through a lookup table. Send what still doesn't match to a person instead of guessing.
What is a traceability lot code?
Under the FDA's food traceability rule, a traceability lot code is a descriptor, often alphanumeric, used to uniquely identify a traceability lot [3]. Firms assign one when they transform a food on the Food Traceability List [3]. Outside food, plants use their own lot or batch numbers for the same purpose.
Methodology
Data. Synthetic and illustrative, generated in Python with seed 20261214. Six materials, 29 suppliers (drawn from a pool of 30), 120 receipts between 1 July and 12 September 2026, and 900 finished lots made on three lines between 11 July and 28 September. Each finished lot consumed three materials, or two where one had no receipt yet (24 lots). Each was drawn from the three most recently received lots of that material.
Planted problems. Each consumption row had a 74% chance of the exact supplier lot code and 12% of a format variant. It had 9% of the internal receiving ID and 5% of no scan. In total, 2,016 rows were exact, 306 variants, 219 receiving IDs and 135 missing. One gasket lot was set as bad.
Quality. Finished lots containing the bad lot failed inspection with a 55% chance; others with 3%. 34 lots failed in total, 19 of them affected. These rates are invented to test the method and say nothing about real failure rates.
Measures. "Fully traced" means every consumption row of a finished lot resolves to a supplier lot. The date window spans the first and last traced use of the bad lot. Scripts ran on 17 November 2026 between 03:50 and 03:53 UTC.
Limits. Real plants have multi-level genealogy, rework, split and merged lots, and partial consumption, which this test leaves out. The counts show how each step behaves, not how often each problem occurs in any plant. The test measures a method, not any product.
References
- Manufacturing Leadership Council (Penelope Brown). "Survey: In Manufacturing Data We Trust—Mostly." 30 July 2026. Accessed 17 November 2026. https://manufacturingleadershipcouncil.com/?p=65474
- NIST Computer Security Resource Center. "NIST's NCCoE Releases Final Version of Internal Report (IR) 8536: Supply Chain Traceability Principles: A Manufacturing Meta-Framework." 9 September 2026. Accessed 17 November 2026. https://csrc.nist.gov/News/2026/nccoe-releases-nist-ir-8536
- U.S. Food and Drug Administration. "FSMA Final Rule on Requirements for Additional Traceability Records for Certain Foods." Accessed 17 November 2026. https://www.fda.gov/food/food-safety-modernization-act-fsma/fsma-final-rule-requirements-additional-traceability-records-certain-foods
- DataFuseAI. "Reconcile the Manufacturing Data Behind Your MES, ERP, and Quality Systems." Accessed 17 November 2026. https://www.datafuseai.com/industries/manufacturing
- DataFuseAI. "Data Pipeline Automation, Scheduling & Monitoring." Accessed 17 November 2026. https://www.datafuseai.com/solutions/data-pipeline-automation
- DataFuseAI. "Free 30-Day Data Integration POC." Accessed 17 November 2026. https://www.datafuseai.com/free-poc