From raw extract to a row you can trust
The pipeline's job is not to clean data. It is to make every repair, assumption and failure visible — typed values where a type could be established, flags where one could not, and a review queue holding everything a machine should not decide alone.
The eleven normalization steps
What happens to a row between the CSV and the clean output?
Dotted edges into the stages are reference data; dotted edges out are review queues. A queue is an output, not an error — it is where a human decides.
Decisions worth knowing
Exact duplicates are retained and flagged by default rather than removed — --deduplicate exact is opt-in. A row that cannot be parsed keeps its raw values alongside the nulls, so the original is always recoverable from the clean output.
HS2, HS4 and HS6 are derived by slicing the string, never by arithmetic. Converting an HS code to a number loses its leading zero, and 030616 becoming 30616 is a silent reclassification into a different chapter of the tariff. Extraction itself is one shared rule in pipeline/hs_codes.py, used by the normalizer and the product catalog alike. How both sides reach six digits, and what that join loses →
Extracts arrive with either 20 columns or 12. The normalizer captures the raw column list before adding its own derived fields and works from that, rather than from a positional slice — an earlier version used iloc[:, :20] and produced duplicate line IDs the moment a 12-column file appeared.
manage_knowledge_base.py generates candidate aliases into entity_alias_candidates.csv. Only entity_aliases.csv is read during processing, and nothing enters it automatically. An unreviewed name stays unmatched, which shows up as coverage rather than as a wrong answer.
Corporate families
Three levels, each narrower than the last. entity_key is one normalized name. family_key unions names that fuzzy-match above threshold, via union-find over rapidfuzz token-sort ratio. group_key reduces a family to its distinctive root token, so “PPG Industries Inc” and “PPG Cieszyn” land on PPG and a purchase between them is recognised as intra-group rather than counted as third-party spend.
The root must be at least three characters. It cannot be lowered to two, because MS and PT are legal-form prefixes rather than brand roots and would collapse every Indonesian and Indian company onto one group. The cost of that floor is that two-character brands slip through — which is why a second, independent check exists in the app. The engine page explains what it caught →
The knowledge base
| File | Holds | Approval |
|---|---|---|
normalization_rules.json | Date formats, missing-value sentinels, numeric parsing | Edited by hand |
unit_aliases.csv | Unit spellings → canonical unit and conversion factor | Edited by hand |
countries.csv, country_aliases.csv | Country names, ISO codes, coordinates | Generated, then reviewed |
entity_aliases.csv | Approved company name → canonical entity | Approved only |
entity_alias_candidates.csv | Suggestions awaiting review — never read during processing | Pending |
hs_aliases.csv | Observed HS hierarchy | source_only — no official nomenclature is bundled |
Because no official HS table ships with the repository, every observed code is marked source_only. Adding an approved official code table promotes them to official matches; until then, an HS description is what the declaring party wrote, not what the tariff says.
Edge-case policies
| Case | Policy |
|---|---|
| Duplicate rows | Retained and flagged exact_duplicate, because a repeated source row may be genuine repeated declaration rather than an error. --deduplicate exact removes them from the clean output; raw files are never altered. |
| Repeated declaration IDs | Not duplicates. A declaration can carry several product lines, so Declaration ID is a grouping key, never a primary key. |
| Empty and sentinel values | Empty strings, N/A, NOT AVAILABLE, NOT DECLARED, UNKNOWN and their Spanish and Portuguese equivalents become explicit quality flags. Never replaced with a fabricated value. |
| Schema anomalies | One known anomaly — a shifted Unit/Value pair — repaired narrowly and auditably via schema_shift_repaired. Anything else malformed goes to the review queue rather than being guessed at. |
| Units | Normalized to canonical labels and dimensions. Quantities may only be summed within one dimension and base unit — kilograms, counts, area, volume and packages must never be added together. |
| Countries | Display text is kept even when ISO enrichment fails. Placeholders like OTHERS and EUROPEAN UNION get no coordinates, so they cannot appear as a location on a map. |
| Entities | Deterministic for approved aliases only. The pipeline never converts an uncertain match into a canonical entity. The opportunity layer relaxes this deliberately — clustering happens downstream and always carries a confidence tier, leaving the pipeline output as the strict ledger. |
What to check after a run
- Input row count equals clean row count, unless
--deduplicate exactwas used. - Value subtotals reconcile across direction, transport, trade type and customer.
- No negative quantities or values.
- Missing dates and values appear in the review queue rather than as zeros.
- Unknown units and country placeholders are still explicit.
- Duplicates and repeated declarations are flagged, not silently discarded.
A review queue that jumps from a couple of percent to nearly everything means a format changed, not that the data got worse. That is exactly how the ISO-date regression was caught: adding 22 converted files sent Akzo's queue to 100% until "%Y-%m-%d" was added to date_formats, after which it settled at 2.2%. The runbook has the full sequence →