23,000 rows of background data, from 3.10 to 3.12

Twenty-three thousand rows of background data, to move from ecoinvent 3.10 to 3.12.

It sounds like a version bump — click import, done. It isn’t. This data had been re-coded inside the project: every row hangs off the project’s own UUID, not ecoinvent’s native Activity UUID. So you can’t overwrite the old rows with the official 3.12 UUIDs. Overwrite them directly and the audit chain breaks — at verification time nobody can say where a row came from or why it turned into that new entry.

This is the post-mortem of one real mapping run: why the step is unavoidable, and how we got it through.

Why you can’t just swap UUIDs

First, where the block is.

Every ecoinvent process has an official Activity UUID. Between versions — 3.10 to 3.12, say — ecoinvent publishes a mapping table telling you which old UUID corresponds to which new one. If your project uses the official UUIDs, you follow the table and you’re done.

But this data didn’t. When the project exported its own library, it regenerated project-side UUIDs and IDs as business primary keys. A 3.10 export table, twenty-three thousand-odd rows, each carrying a UUID the project minted itself — none of them matching the Activity UUIDs in that official table.

Hence the deadlock: the official table can tell you 3.10 official → 3.12 official, but it knows nothing about your project-side UUID. Swap directly and you’re turning a key that doesn’t fit the lock.

The four-hop

The fix is to split “project” and “official” into two layers, with the business key as the bridge. The full path is four hops:

1. Project 3.10 data, aligned to official 3.10 data by business key.
2. Official 3.10 → official 3.12, through the official mapping table.
3. Official 3.12 data, aligned back to project 3.12 data by business key.
4. What falls out at the end is a table of project-side old UUID → project-side new UUID — which is the thing a database upgrade actually needs.

The business key is three parts: Activity Name, Reference Product, Geography. The same process, across two versions, counts as one row as long as those three match.

What you deliver isn’t the official UUID. It’s the project-UUID-to-project-UUID correspondence. The official table is only the bridge in the middle.

The geography hurdle

The nastiest part of the business key is geography.

The project’s exported geography field isn’t clean codes — it’s annotated with Chinese, e.g. China, Hebei (河北). The official table writes CN-HB. The strings don’t match, and the join silently drops a large slice.

So before matching you have to normalize. This step uses ecoinvent’s own geography reference tables — geo3.10.xlsx for the 3.10 source, geography.xlsx for the 3.12 target — reading the Name → Shortname column to wash annotated names down to short codes. China, Hebei (河北) first loses the parenthetical to become China, Hebei, then maps to CN-HB.

You can’t fuzzy-match a province code by guessing. Guess one wrong and the whole chain silently attaches to the wrong data, while the report still reads “matched.” This hurdle has to be forced through the reference table.

What to do with one-to-many

After the four hops you get a “source UUID → target UUID” table. Now classify it, by cardinality, into four kinds:

• One-to-one: one old UUID to one new UUID. The cleanest.
• Many-to-one: several old UUIDs point at the same new UUID, usually because 3.12 merged some entries. Keep them.
• One-to-many: one old UUID maps to several new UUIDs. You have to pick one.
• No target: the old UUID has no counterpart in 3.12 — the 3.12 side of that mapping-table row is empty.

One-to-many was the main source of conflict this time. A 3.10 process may have split into several region-specific entries in 3.12. You can’t pick at random. The rule we used is a priority order: China (CN or CN-*) first, then RoW, then GLO, and if none of those, the first of the candidates.

The key is that one-to-many must not collapse to a single row at match time. You have to keep every candidate first, tag each with its geography code, sort, then pick. Otherwise you have no idea which options you dropped, and no way to leave a “why this one” audit record in the result.

The numbers after the run

For the EN15804 tier, the final counts:

• 23,603 source rows, all entering the flow.
• Source to official 3.10: 23,603 matched, zero loss on geography normalization.
• Official 3.12 to project 3.12: 23,575 matched; the remaining 28 are rows where the 3.12 side of the official table is itself empty — not a project-side miss, the official row gave no target.
• UUID relations: one-to-one 23,115, many-to-one 169, one-to-many 291, no target 28.

Under the “keep one-to-one and many-to-one, collapse one-to-many by China-first” strategy, the final usable mapping is 23,575 rows. Of the one-to-many collapses, 45 picked China, 179 picked RoW, 0 picked GLO, and 67 fell back to the first candidate.

Every pick carries a selection_reason column spelling out which rule chose it. In the output table, the old-version UUID column and the new-version UUID column are highlighted yellow — handed to the project team, the first thing you see is those two columns’ correspondence, with Activity Name, product, and geography beside them for spot checks.

The one thing worth writing down

The single most error-prone thing in the whole process, and the one most worth putting in the runbook, is this: don’t use PROCESS_UUID as the primary key.

The project export has two kinds of UUID — UUID and PROCESS_UUID. PROCESS_UUID looks like the native official one, which tempts you to treat it as unique and use it directly as the swap key. It isn’t: in the project export, PROCESS_UUID repeats. Join on it as the primary key and you silently get many-to-many crosswiring — the report numbers all look right while the underlying correspondence is already scrambled.

The primary key is always the project-side UUID. PROCESS_UUID can only be an auxiliary audit field, never the migration key. If you don’t lock this down in the script, every match statistic afterward is a building raised on a tilted foundation.

---

Upgrading a database version isn’t hard because of the version number. It’s hard because you have to prove every row moved correctly. The four-hop, the geography normalization, the cardinality classification, the selection trail — none of it is there to make the process look tidy. It’s there to make the final table survive a spot check. Twenty-three thousand rows moved, every one traceable to its source: that’s when it counts as delivered.