The CSVTrax Blog

Key:Value Dictionaries are Good – But Constrained Vocabularies RULE!

A data export might represent the same business category with NY, N.Y., New York and NEW_YORK. A supplier might call a fulfilled order F, while another writes Shipped. Manually editing a few values is easy. Maintaining hundreds of such substitutions across recurring CSV deliveries is not.

A lookup table turns those scattered cleanup decisions into an explicit mapping that can be reviewed and reused.

PRO TIPS

• Constrained Vocabulary: A bounded set of values used in a specific context or purpose. If I ask “Give me all possible 2-letter uppercase permutations”, you start at AA,AB,AC,… It’s a lot. But “All uppercase 2-letter US State Abbreviations” – that’s SMALLER: AL,AK,AR,AZ,… Both are FINITE SETS. If we see a CSV Column Header 'us_state_abbr' – we do NOT have to accept all possible letter pairs. Its very useful that the ‘permutation space’ of US State Abbreviations is a CONSTRAINED VOCABULARY. Constrained Vocabularies ARE YOUR FRIEND during Data Cleansing & Normalizing. Put’em in a table and look’em up.

Start with a small code dictionary

Suppose downstream reporting expects a canonical RegionCode:

Incoming value	Canonical value
NY	NY
N.Y.	NY
New York	NY
NEW_YORK	NY
NJ	NJ
New Jersey	NJ

The operational rule is simple: read a source value, look up an exact defined mapping, and produce the corresponding canonical value. With a table, the rule is data that someone can inspect, rather than a growing chain of hidden “find and replace” actions.

But lookup tables are not magic. Their correctness depends on scope, matching rules, collision handling and unmatched-value behavior.

PRO TIPS

• Normalize to Constrained Vocabulary EARLY. If you are able to ‘compress’ an inbound value space to a Constrained Vocabulary – you typically want to do this EARLY in your Data Canonicalization. You seek to express chaotic inputs to CANONICAL FORM at first point of contact in pretty much all cases. CANONICAL FORM includes normalization to Constrained Vocabularies. The SOONER you Lock Down incoming column values to an applicable Constrained Vocabulary, the sooner you enforce DETERMINACY on that column.

Do not use global text replacement for exact codes

So, there are STRATEGIES to compressing an inbound column value-space to a Constrained Vocabulary. Here’s a mental ‘short-hand’ for it: Imagine you have to sort a bunch of Glass Marbles by Size, Color, and interior Paint Pattern (‘star-burst’,’waves’,’swirls’, blah…). Now imagine you have a wood tray with holes of different sizes and a bunch of little plastic cups. If you gotta sort a 1000 marbles – that’s a good place to start: BY SIZE (Most DETERMINISTIC). After that, it seems COLOR is perhaps the ‘next easiest’ criteria. You see what we’re doing: EASIEST to HARDEST (Deterministic first, Ambiguous Later). By the time you handle all the easy ones – there are fewer left to deal with. Ok, you’re set. Let’s do this.

Imagine a file containing Region=NY and Notes=Returned from Albany. A broad replacement of NY everywhere might accidentally alter words inside the Notes column, or other fields. Otherwise stated: ‘Start swapping blindly in strings with N and/or Y‘. Just NO – Rethink it. You throw a net that wide and you’ll catch more Hell than fish. More formally, think of the “Intake Diameter” here – that’s way too WIDE (permissive). You want your identification strategy to start at its Most FOCUSED, then carefully incrementally widen the intake.

A safer design restricts the lookup to the Region column and matches the whole field value, not arbitrary substrings. The more contiguous characters that must exactly match, the tighter and more focused that criteria is. It might also distinguish uppercase and lowercase if the source contract requires that distinction.

Ask three questions before adding a mapping:

• Which source column does it apply to?
• Is the match exact, case-insensitive, trimmed, or pattern-based?
• Is the replacement a code, a label, or a new business classification?

For identifier-like values, avoid removing leading zeros as a preliminary “cleanup.” 0017 and 17 can be different business keys even when spreadsheet software displays them similarly.

Beware of unintended replacement chains

Let’s talk about ‘chaining’ and ‘side-effects’. When those two start breeding – you gotta put’em in a special box or they’ll burn the house down. Suppose a naive sequence of replacement rules says:

A -> B
B -> C

Basically – an Input Value comes in as A which is identified and transformed to B. But then a subsequent transform operation on that same column value identifies any occurrence of B and then transforms it to C. Is that actually what you wanted? Because HERE’s what you actually GOT: For Input Values in [A,B] -> C. A sequential (chained) find-and-replace operation might transform it twice. Otherwise stated: The First Operation’s Side-Effect produced a matching condition to trigger a subsequent Transform Operation. That what you wanted? A true one-pass dictionary lookup should transform the original A to B and stop—unless the business specification explicitly requires chained interpretation.

The distinction is not academic. Hundreds of mapping entries are difficult to audit if an earlier replacement changes which later rule matches. Prefer a defined one-pass lookup against the original source value, or clearly document intentional staged transformations.

Also check whether two source keys become the same key after normalizing whitespace or case. If the duplicate normalized key points to conflicting outputs, do not arbitrarily choose one.

PRO TIPS

• A Data Transformation Pipeline (for normalizing, restructuring, cleaning, …) is a multi-step process of incremental transformations upon Column Values. That’s a fancy way to say: CHAINING of SIDE-EFFECTS. There is a REAL benefit to decomposing mutative ‘Field Operations’ to a fundamental vocabulary of simple operations because that powerfully amplifies the COMPOSABILITY when you chain them. But that POWER demands your full attention: You see, its the same composability that introduces a drift to longer chains of sequential side-effects. Learn To See It. Keep it Clear and Intentional.

Decide what happens when the lookup misses

A real source eventually introduces an unfamiliar value. Consider an order-status dictionary that recognizes P, S and C, but a new file contains H.

Possible responses include preserving H and flagging it, sending the record to a review queue, or refusing the file if an unrecognized status is unacceptable. Don’t just automatically convert it to one of the other familiar category types ‘just to keep going’. You’re CLOBBERING DATA by ‘Magically Conjuring’ FALSE INFORMATION when you do that. Just DON’T, or there will be a Gathering and then a Reckoning, followed promptly with a BEATING.

The right policy depends on consequences. An unknown marketing segment might be tolerable as “Unclassified.” An unknown payment or fulfillment state could require investigation before the data is used.

A sound validation report counts matched values and unmatched values separately. Avoid celebrating a 100% “successful” output if unknown codes were quietly dropped, invented, or mis-handled.

Maintain the table as a business asset

A long-lived mapping should record more than input and output text. Useful extra fields include the source system, owner, effective date, reason for the mapping and review status. Not every table needs every field, but some change history becomes important as the business grows.

For example, a supplier could repurpose R from “Returned” to “Ready.” The old rule may have been correct for last year’s exports and wrong for tomorrow’s. If mappings change over time, choose whether to version the dictionary by export date, source version or another explicit authority.

Apply ordinary quality checks:

1. Ensure each permitted source key maps to one unambiguous target value within its scope.
2. Test a representative matched value, an unknown value and a blank value.
3. Confirm that a mapping alters only the intended column.
4. Decide whether the original code should be retained for reconciliation.
5. Measure how many rows were transformed and how many require review.
6. Require an owner to approve changes that alter financial or operational meaning.

PRO TIPS

• Data Dictionary – say it with me: “Da.. Da.. Day… Day – Taaaaa. Dataaaa Di…Dic…Dick”. That’s right: PUT IT IN YOUR HEAD. If you don’t have one of these… Up your game. T’sall I’m sayin’.

Where a reusable transformation workflow helps

The conceptual pattern is the same whether you use a spreadsheet lookup, SQL join, maintained Python dictionary or an appropriate configurable transformation operation. For recurring CSV/TSV processing, a reusable Job-oriented tool such as CSVTRAX may help operationalize approved rules. Whatever tool you choose, evaluate it against the exact mapping scope, ordering, and unknown-code policy that your workflow requires.

The business benefit comes from moving the code dictionary out of someone’s memory and into an inspectable, maintained contract.

The practical takeaway

Replacing hundreds of codes is not fundamentally a typing problem. It is a controlled classification problem. A good lookup table makes accepted meanings explicit, confines changes to the right data, reports unknowns and prevents apparently harmless substitutions from silently changing business truth.