Six names for one field

Why does the same piece of information end up with six different names and six slightly different meanings?

Trace one date across an insurance estate and you find EFF-DT in the policy copybook, EFFECTIVE_DATE in the warehouse, startDate in the API, BILL_FROM_DT in billing, covStart in the pricing service, and DT_EFF_POL in a reporting extract.

The tidy reading is that this is sloppiness — six teams who could not agree, and a cleanup waiting to happen. But it is worth asking whether the sloppiness story explains what we see. Because if these were merely six labels for one value, reconciling them would be a rename. It almost never is. The values disagree, in small numbers of rows, in ways that turn out to matter.

Ask first where a name actually comes from. Not from the concept — from the circumstances of the copy. A field entering a system is named to fit that system's constraints and conventions: the eight-character limit of a job control name, the thirty-character COBOL data name, a database's column-length rule, a house style that abbreviates or that spells out, a language community that prefers camel case. The name records where and when it landed, not what it means. That is path dependence in its plainest form: the outcome is explained by the history of the process rather than by the properties of the thing.

Now the harder half. Why do the meanings drift too?

Consider what happens at each hop. A system receives the date and must do something with it that its own model requires. Billing needs a value it can put on an invoice line, so it moves the date to the first of the billing month. Pricing needs a moment, not a day, so it adds a time and a timezone — and picks one. The warehouse loads nightly, so its copy is the value as of last night's snapshot, which for recently backdated policies is not the current value. Reporting handles a null by substituting a low date so its joins behave.

Each of those transformations was locally correct. Each was made for a reason that made sense inside one system's model. And none of them was a decision about the shared concept, because at no point was there a place where the shared concept lived. So the drift is not carelessness — it is the accumulated residue of many small, individually justified local rules.

Which suggests the reconciliation instinct is dangerous. If you declare one of the six canonical and align the rest to it, you are not removing duplication; you are silently overwriting five local rules, at least some of which encode something real. The right first question is not "which name wins" but "in which rows do these six disagree, and why".

How would you even find that they are the same field, given the names are no help? Not by matching names, which is what most tooling reaches for first and which produces both misses and false hits. By profiling the values: the shape and range of the data, how many distinct values, the overlap of value sets between two columns sampled over the same entities, whether one determines the other. Two columns whose value sets agree on ninety-nine per cent of matched rows are almost certainly the same concept — and the one per cent is the interesting part, because that is exactly where a local rule is hiding.

And the hazard that runs the other way deserves naming: homonyms are worse than synonyms. Six names for one thing is a discoverability problem. One name for six things — customer meaning the billed party in one system, the insured party in another, the broker in a third — is a correctness problem, and it is far harder to detect, because the name reassures everyone that the conversation is going fine.

Three refinements.

First, "the same field" is doing too much work. There are at least three relations worth keeping apart: identical, meaning every row agrees; derived, meaning one is a deterministic function of the other; and related, meaning they are about the same real-world property but neither derives from the other. A glossary that flattens all three into "synonym" loses the only information a migration needs.

Second, the surviving evidence for which relation holds is usually in the transformation code, not in either schema. The job that loaded the warehouse column contains the rule that made it differ. Schemas record the endpoints; the mapping records the meaning, and mappings are the artefacts most often lost.

Third, a caution about what a canonical name achieves. Agreeing one term for the concept genuinely helps people talk to each other and is worth doing. It does not make the values agree, and treating it as though it does converts a visible problem — six names, obviously needing care — into an invisible one, where a single name implies an equivalence nobody verified.

• How value-based profiling decides that two columns are the same concept, and where it misleads.
• Whether a canonical model can hold local variants explicitly rather than forcing them out.
• How to detect homonyms, given that no name mismatch signals their presence.

Every term the collection defines is gathered in the glossary.