TT Lab
Get started
Learn Learning paths Courses

In Front of an Unfamiliar System

One word, three systems, three different meanings

Continue in TT Lab

In one line

To the question "how many active customers are there?", three people can give three numbers and all three can be right. If you do not tie a term to an identifier, every number after that goes quietly out of line.

Why this was needed

Around the second day on site, you get called into a meeting. Someone asks, "How many active customers were there last month?", the sales rep says 34,000, the billing rep says 31,000, and the operations rep says 26,000. The three look at each other as if the others were strange.

Nobody is wrong. The sales system counted the rows whose status character in the customer table is A, the billing system counted the accounts that can be billed, and the operations system counted the people who logged in within the last 30 days. All three definitions are correct for their own work. What is wrong is believing that a single word holds all three definitions.

The reason this mismatch is frightening is that it does not show up as an error. The query succeeds, the dashboard draws the number, and the report is printed. The moment it surfaces is usually after something has been decided with that number.

How it works

The fix is not to ban the word. People will keep saying "customer", and they should. Instead, build a table that links words to identifiers. If you write, one line at a time, which column of which table each system picked, under what condition, for one term, then from that point the numbers can be explained even when they do not match.

A line of the mapping table needs four things — the system, the table, the identifier column, and the condition. If the condition is missing, the table is only half useful. This is because the difference in "active" lies not in the table but in the condition.

After building the table, you confirm with numbers. You measure how large the sets of the same name are in each system, and how much two sets overlap. You look at the overlap as the intersection and the difference, and if you want to reduce it to a single number, you use the Jaccard coefficient.

자카드 계수 = |A 교집합 B| / |A 합집합 B|

1.0  두 집합이 완전히 같다        — 이름이 같아도 괜찮다
0.8  대체로 같고 한쪽이 조금 넓다  — 경계 조건을 확인한다
0.0  겹치는 것이 없다             — 같은 이름을 쓰면 안 되는 관계다

There is one more place where most people stumble here. To compare two sets, you first have to decide what counts as "the same thing". Suppose you link people by email. The sales system holds User07@Example.com, the billing system holds user07@EXAMPLE.COM, and the operations system holds user07@example.com with a trailing space. Do you consider these three the same?

Section 2.4 of RFC 5321 gives a precise answer to this question. The local part of a mail address (before the @) is case sensitive (MUST BE treated as case sensitive). The domain, on the other hand, follows the DNS rules and is not case sensitive. By the specification, User07@Example.com and user07@example.com are different addresses. The same section goes on to say that actually relying on the case of the local part harms interoperability and is not recommended.

So the normalization used in the field is one of two.

Which one is right is decided not by the document but by that customer's system. What matters is not the choosing itself but writing down the rule you chose and using the same rule in every aggregation. If you change the rule, the overlap changes entirely — seen by the specification, the three systems above have not one person in common, and if you lowercase everything, almost all of them overlap.

What it looks like in the field

First, different names for the same thing are more common than the same name for different things. Sales calls it email, billing calls it billing_email, and operations calls it login_email, but all three point to the same person. Because the names differ, nobody reconciles them, and so nobody knows that the same person is in the three systems three times.

Second, sometimes the same name is attached to completely different things. The warehouse operations team uses the word "order" to mean a warehouse work instruction. Sales' 300 orders and operations' 180 orders have nothing in common. A relationship like this shows up immediately as a Jaccard of 0 — with the table, you could find out in 5 minutes what you instead wander for 2 weeks not knowing.

Third, if you write the mapping table in the meeting minutes, it disappears. Keep it as a file and make the aggregation tool read that file. Then when the definition of a term changes, the numbers change with it, and if they do not change, it means someone has not fixed the mapping table.

Fourth, do not try to fix it right away just because you found a conflict. A proposal to unify the definition of "active" across the three systems is a proposal to change the work of three teams at the same time. The job in the first week is not unification but writing down which definition each report used.

What really matters in practice

What you will do in the next lab

You hold a snapshot that puts the three systems of (fictional) Hana Distribution in one file. You write in a mapping table which column of which table, under what condition, each of the three systems means by "customer", "active", and "order", measure the set sizes, and compare two systems at a time to get the overlap. Then you apply identifier normalization once by the specification and once by the system's actual behavior, and see the answer change entirely. The grader actually runs your aggregator on a database made with different tables and values each time and checks the numbers. At the end you split the same-name-different-set cases from the different-name-same-set cases and deliver them as a report.