Customer, order, active - three systems count three different things

Goal

You harden into a mapping table what the customer's "customer", "active", and "order" each are in the three systems, and measure from the data the size and overlap of the sets the same name points to. You see for yourself that changing the identifier normalization changes the answer entirely, and deliver a report that separates the same-name-different-set cases from the different-name-same-set cases.

Why it matters

If you ask in a meeting "how many active customers are there?", three people give three numbers and all three can be right. Sales counted the rows whose status character is A, billing counted the accounts that can be billed, and operations counted the people who logged in recently. The three definitions are each correct for their own work, and what is wrong is believing that a single word holds all three definitions. This mismatch does not show up as an error. The query succeeds and the report is printed. By the time it surfaces, a decision has already been made with that number. So in the first week you build a term-to-identifier mapping table as a file and make the aggregation tool read that file. To compare sets, you first have to decide what counts as "the same thing". Section 2.4 of RFC 5321 says to treat the local part of a mail address as case sensitive and says the domain is not case sensitive. Seen by the specification, there is not one person in common, and if you lowercase everything, almost all of them overlap — two answers come out of the same data. The grader does not trust the wording you write down. It actually runs your aggregator on a database made with different table names and values each time, and checks the set sizes and overlap against the values it computes itself.

Steps

1. Create and run /root/glossary/gen_systems.py to produce /root/glossary/systems.db. It holds six tables from three systems: sales (crm), billing (bill), and operations (ops).
2. In /root/glossary/terms.json, write the term-to-identifier mapping table. It is three terms (customer, active, order) × three systems = nine lines, and for each line you write system, table, id_column, and predicate.
3. Create /root/glossary/census.py so that it measures the set size for each line of the mapping table and writes /root/glossary/census.json.
4. Add --overlap <경로> (the placeholder is the path) so that it writes the result of comparing two systems at a time within one term. It outputs the number of overlapping identifiers and the number present on only one side.
5. Add --normalize none|strict|loose. strict strips whitespace and lowercases only the domain, and loose strips whitespace and lowercases everything.
6. Create /root/glossary/conflicts.json from the values measured with loose, and for each term write the most mismatched pair of systems and its Jaccard coefficient.
7. In /root/glossary/naming.json, separate and write the same-name-different-set cases (Jaccard 0.1 or less) and the different-name-same-set cases (column pairs of the customer term with a Jaccard of 0.5 or more).
8. Report in four sections in /root/glossary/glossary_report.md.

Notes

• Execution contract: python3 /root/glossary/census.py --db <DB> --terms <대조표> --out <집계 JSON> [--overlap <겹침 JSON>] [--normalize none|strict|loose] (the placeholders are the DB, the mapping table, the aggregation JSON, and the overlap JSON) writes a one-line summary to standard output and ends with exit code 0. If it cannot read the input, the exit code is 3.
• Mapping table: {"entries": [{"term": …, "system": …, "table": …, "id_column": …, "predicate": "SQL 조건", "note": …}]} (the placeholder is the SQL condition). predicate is a condition string that is attached as is after WHERE, and if there is no condition it is 1=1.
• Aggregation JSON: {"normalize": …, "counts": [{"term", "system", "table", "rows", "distinct_ids"}]}. counts is sorted by term, then system, ascending. rows is the number of rows that match the condition, and distinct_ids is the number of distinct normalized identifiers.
• Overlap JSON: {"normalize": …, "pairs": [{"term", "left", "right", "both", "left_only", "right_only"}]}. Within one term, pair up the systems two at a time in ascending order of system name, and left comes before right.
• Normalization: none leaves the value as is, strict is 공백 제거 + 마지막 @ 뒤만 소문자 (the Korean text means "strip whitespace + lowercase only what follows the last @"), and loose is 공백 제거 + 전부 소문자 (the Korean text means "strip whitespace + lowercase everything").
• conflicts.json: {"normalize": "loose", "terms": [{"term", "sizes": {시스템: 크기}, "min_pair": [시스템, 시스템], "min_jaccard": 소수점 셋째 자리, "conflict": true|false}]} (the placeholders are the system, the size, and the third decimal place). Jaccard is the intersection size divided by the union size, and if the union is empty it is treated as 1.0. conflict is true when min_jaccard is less than 1.0.
• naming.json: {"same_name_different_thing": [{"term", "min_pair", "jaccard"}], "different_name_same_thing": [{"a", "b", "jaccard"}]}. a and b are in the form 시스템.표.칼럼 (the placeholders are the system, the table, and the column), and a comes before b.
• Common mistakes: leaving the condition out of the mapping table (the difference in active lies not in the table but in the condition), using a different normalization rule for each aggregation, and not removing duplicates before counting the overlap.
• The Jaccard thresholds 0.1 and 0.5, and the rounding to the third decimal place, are assumptions of this lab. They are not values a standard decides but values you write in the report and use.
• Reference documents: Section 2.4 of RFC 5321 gives the case rules for mail addresses, RFC 4949 is a model of a glossary that deals with the problem of using the same word with several meanings, and the SQLite SELECT documentation explains INTERSECT and EXCEPT.

Get the three systems in hand

Create and run /root/glossary/gen_systems.py to produce /root/glossary/systems.db. It holds six tables: crm_customer, crm_order, bill_account, bill_invoice, ops_user, and ops_workorder.

On site you receive an extract from each of the three systems. Here we put the three into one file. The crm, bill, and ops at the start of each table name tell you which system it came from. After you make it, first look at just the list of tables and a few rows of each with sqlite3.

Build the term-to-identifier mapping table

In /root/glossary/terms.json, write nine lines. The terms are the three customer, active, and order, and the systems are the three crm, bill, and ops. For each line write term, system, table, id_column, and predicate; for active, use the same table as the customer of the same system but narrow it with the condition. Order is a different table from customer.

If the condition is missing, the mapping table is only half useful. This is because the difference in active lies not in the table but in the condition. What each system uses to separate active becomes visible when you open the tables — the status character, whether it can be billed, and the last login date. For a line with no condition, set predicate to 1=1.

Measure the set sizes of the same name

Create /root/glossary/census.py so that for each line of the mapping table it measures the number of rows that match the condition (rows) and the number of distinct identifiers (distinct_ids), and writes /root/glossary/census.json. counts is sorted by term, then system, ascending.

predicate is a condition string that is attached as is after WHERE. The reason to count the rows and the distinct identifiers separately is that one person can have several rows. In this step you compare the values as they are without touching them — normalization is added in step 5.

Compare two systems at a time

Add --overlap <경로> (the placeholder is the path) so that it writes the result of comparing two systems at a time within one term. both is the number of identifiers present on both sides, and left_only and right_only are the numbers present on only one side. In this step too, the values are compared as they are.

For each term, sort the system names in ascending order and pair them up two at a time. With three systems there are three pairs. If you compare the values as they are, the overlap will come out near 0, and that result itself is the starting point of the next step — check by eye why it is 0.

What to treat as the same

Add --normalize none|strict|loose. strict strips whitespace and lowercases only what follows the last @, and loose strips whitespace and lowercases everything. Write the value you used in the normalize field of the aggregation JSON and the overlap JSON.

Section 2.4 of RFC 5321 says to treat the local part of a mail address as case sensitive and says the domain is not case sensitive. strict is the side that follows that specification, and loose is what this customer's systems actually do. See for yourself two answers come out of the same data.

The same name points to different sets

Using the values measured with loose, create /root/glossary/conflicts.json. For each term, write the set size per system (sizes), the pair of systems with the lowest Jaccard (min_pair), its value (min_jaccard, to the third decimal place), and whether there is a conflict (conflict).

Jaccard is the intersection size divided by the union size. If the union is empty, it is treated as 1.0. If several pairs are tied for the lowest, choose the pair that comes first in ascending order of system name. conflict is true when min_jaccard is less than 1.0 — it means that if they differ even slightly, using the same name is risky.

Different names point to the same thing

In /root/glossary/naming.json, write two lists. same_name_different_thing holds the terms whose min_jaccard is 0.1 or less, and different_name_same_thing holds the pairs among the three column pairs of the customer term whose Jaccard is 0.5 or more. Write a column in the form 시스템.표.칼럼 (the placeholders are the system, the table, and the column), with a before b.

Different names for the same thing are more common and more dangerous than the same name for different things. Because the names differ, nobody reconciles them, and so nobody knows that the same person is in the three systems three times. Jaccard has the same definition as in step 6, and the normalization is the same loose.

Write down what to agree on

In /root/glossary/glossary_report.md, write four sections: ## 세 시스템이 같은 말을 쓴다 ## 용어-식별자 대조표 ## 같은 이름 다른 집합 ## 합의할 것 (the Korean headings mean "The three systems use the same word", "The term-to-identifier mapping table", "Same name, different set", and "What to agree on"). The names of the three terms and the set size per system must all appear, and write down the normalization rule you used too.

Do not write the report by hand; generate it from the three files census, conflicts, and naming. That way, when the mapping table changes, the report changes along with it. In the last section, write not a proposal to unify but the agreement to 'write down which definition each report used'.