EU delta replay — design

Why

EuImporter opens the largest zip in the source directory — the monthly CIRCABC extraction — and stops. The eu-2026-07 release also carries ~51 TARIC_YYYYMMDD_HHMM.zip daily deltas (rolling ~10-week window) that are downloaded every sync and never opened.

EU is authoritative for all EU-27 tariffs, so a measure created or end-dated mid-month is invisible until the next monthly extraction. The source also rebuilds daily — each new delta asset moves the sync:imported:eu marker — reproducing the same stale data every time.

BE and NL already replay their deltas (BeImporter.ApplyDeltasAsync, NL incrementals). EU is the only source with unapplied deltas; PL has none published (annual base file only), and every other source ships fulls refreshed in place.

Operation vocabulary

Scanned across all 51 deltas of eu-2026-07. Every entity file has a PUBLISH column with exactly three values:

An unrecognised value must abort the import rather than be skipped — a silently ignored operation leaves the database subtly wrong, which is worse than a failed run.

REC_SEQ_NUM orders operations within a file. Files must be applied in delta-timestamp order, and rows within a file in REC_SEQ_NUM order.

Boundary: which deltas apply

The window spans deltas both older and newer than the monthly full (e.g. 21 May → 30 July against a full dated 1 July). Only deltas newer than the full may be replayed. Applying an older UPDATE overwrites current values with stale ones — the full already reflects every change up to its own generation.

Rule: derive the boundary from the full's filename, eu-taric-YYYY-MM.zip → YYYYMM01, and replay deltas whose filename timestamp is >= YYYYMM01.

Operations are applied idempotently (INSERT as upsert, DELETE as delete-if-exists) so that a boundary off by a day or two self-corrects rather than failing. That asymmetry is deliberate: replaying one extra day is harmless, missing one is not.

Column layouts

Note the delta layouts differ from the monthly full's — the full has END_DATE at index 4 and DUTY at 9, the delta has DESCR_START_DATE at 4 and DUTY at 8. Delta parsing needs its own column map; it cannot reuse the full's.

Identity

Measures, Measure_Conditions and Measure_Footnotes all identify a measure by the same six fields the full already keys on:

MeasureKey(gnCode, addCode, orderNo, startDate, originCode, measType)

(ORIGIN / ORIGIN_CODE / GEOGR_AREA are the same field under three names, likewise ORD_NUMB / ORDER_NUM and MEAS_TYP_ID / MEAS_TYPE.) So measureKeyToId, which ImportMeasuresAsync already returns and the child importers already consume, is the right lookup for deltas too — but it must be kept current as the replay runs: a delta INSERT of a measure followed by a delta INSERT of one of its conditions needs the new id in the map.

Description rows are language-scoped via LANG_COD, matching the EN/FR/DE merge the full does.

Phasing

Order by what duty lookups actually read, so each phase is independently useful and verifiable:

1. ~~Measures — duty rates, validity windows, origins~~ done (1.5.0, fixed in 1.5.1)
2. ~~Measure_Conditions, Measure_Footnotes — the measure family~~ done (1.5.6)
3. ~~Goods_Nomenclature — codes and descriptions~~ done (1.6.3)
4. ~~Certificates, Footnotes, Add_Codes — reference descriptions~~ done (1.6.3)

Children are replayed after Measures within the same delta file, so a condition or footnote can reference a measure that same file inserted.

Watch the child row(s) with no matching measure counter: children whose parent is outside the full's scope are expected, but a sudden jump means the measure key stopped matching.

Verification

This code mutates duty data, so a wrong result is silent rather than loud. Before it ships:

• Row counts per table before/after replay against the count of applied operations
• Spot-check a handful of measures changed mid-month against the DDS2 web view
• A delta whose boundary excludes it must leave the database byte-identical (assert no-op)
• An unrecognised PUBLISH value must fail the import loudly

Phase 3-4 notes (added when they shipped)

Layouts above were re-verified against TARIC_20260724_1924 before writing the code, not taken on trust. All four matched.

Identity is the whole game here. Both new appliers delete-then-insert, so the delete predicate must match the identity the full import uses, or it silently destroys rows the full created:

• Goods_Nomenclature: the full keys on (CnCode, Suffix, StartDate) and splits the 12-char GOODS CODE into a 10-digit CN code plus a 2-digit product-line suffix via SplitGnCode. A first version of the delta treated the whole string as CnCode and deleted on that alone. 3,498 of EU's 25,763 nomenclature rows share a CnCode with another row, so that would have collapsed every one of them into whichever version was applied last — while verification still passed. Caught by comparing COUNT(*) against COUNT(DISTINCT CnCode), which is the cheap check worth repeating for any delete-then-insert delta.
• Certificates / Footnotes / Add_Codes: the full keys on (code, start), so ValidityStart belongs in the delete predicate for the same reason.

Descriptions are merged EN/FR/DE only, matching the full. The deltas also carry the other ~20 EU languages; the schema has no columns for them.

Verified locally against the real eu-2026-07 release (52 assets, 22 deltas at/after the cutoff):

measures      3,158 inserted, 245 updated, 215 deleted   (identical to dv)
conditions    2,259 written
footnotes       210 written
nomenclature    699 written  -> +638 rows, +638 distinct codes,
                                multi-version codes still 3,498 (unchanged: nothing collapsed)
references      106 written  -> certificates 890 -> 899