TT Lab
Get started
Learn Learning paths Courses

Working With Customer Data

Revenue Hit Zero in Week 13: Put the Intake Contract in Code

Continue in TT Lab

Goal

You build schema.py, a tool that extracts the schema from the files that come every week and freezes it as a fingerprint, classifies column additions, removals, renames, and type changes, and judges them automatically by compatibility rules. In the end you catch, by value distribution, a column whose meaning changed while its name and type stayed the same.

Why it matters

The schema of a file someone else gives you changes without notice. The sender has tidied up the fields of their own system, and the receiver was merely using it as a contract. Since we cannot control the side that makes the change, all we can do is make the pipeline notice before anyone else that it has changed. What comes after noticing is more important. If you stop the load because one unknown column was added, nobody will trust that warning, and if a required column has disappeared and you just let it pass, the numbers go quietly wrong. So you pin down with rules what breaks and what does not. The default is two lines — an unknown column passes, and a required column that has disappeared stops the load. And there is one thing a schema check can never see. If the unit of the amount changes from won to thousand won, the column name and the type stay the same. All the checks pass and only the sales become one thousandth. Such a change is visible only in the distribution of values, and a distribution is not evidence but a lead. The grader does not trust your wording. It sets up weekly files it made in a temporary directory, actually runs your tool, and checks the classification and the judgment. The company names and amounts change on every run.

Steps

  1. Create and run /root/drift/gen_weeks.py to produce w01.csv through w07.csv under /root/drift/weeks. The 30 orders stay the same throughout and only the schema changes.
  2. Build fingerprint in /root/drift/schema.py so that it outputs the column names, types, value samples, and the fingerprint, and save the fingerprint of w01.csv in /root/drift/baseline.json.
  3. Add diff <기준 JSON> <파일> (the placeholders are the baseline JSON and the file) so that it classifies column additions and removals.
  4. Make diff find pairs whose value samples overlap heavily and classify them as renames. A column caught as a rename drops out of the added and removed lists.
  5. Make diff classify type changes of the same column. Also look at the case where the name changed and the type changed along with it.
  6. Declare the required and optional columns and the rules in /root/drift/contract.json, and have gate <기준 JSON> <파일> judge pass, warn, and stop.
  7. Add watch <기준 CSV> <파일> so that it measures the change in the median of numeric columns, and write the week in which the unit changed in /root/drift/meaning.json.
  8. Judge all seven weeks in one go to produce /root/drift/drift_report.json and /root/drift/drift_report.md.

Notes

Build seven weeks of received files

Create and run /root/drift/gen_weeks.py to produce w01.csv through w07.csv under /root/drift/weeks. The 30 orders stay the same throughout and only the columns change from week to week.

Make the rows of the baseline week into a list of dictionaries, and for each week adjust only the column list and values and write it again. The values must stay the same throughout so that you can later find renames by value samples. Change the values only in the week where the unit changes.

Freeze the schema into a fingerprint

Build fingerprint <파일> (the placeholder is the file) in /root/drift/schema.py so that it outputs rows, columns, and digest, and save the fingerprint of w01.csv in /root/drift/baseline.json.

For each column, collect the values and infer the type, and put the first 20 distinct values, sorted, in as the sample. The fingerprint hashes only the names and types joined together — if you put the sample in too, the fingerprint differs whenever the data changes and becomes useless.

Separate added columns from removed columns

Add diff <기준 JSON> <파일> (the placeholders are the baseline JSON and the file) so that it outputs four lists: added, removed, renamed, and retyped. In this step it is fine to fill in only additions and removals.

If you take the name sets from the baseline fingerprint and the new file's fingerprint and compute the set differences, you get additions and removals. Output the lists sorted — if the order differs from run to run, you cannot compare them.

Find the column whose only change is its name

Make diff classify a pair as a rename if the Jaccard similarity of the value samples of a vanished column and a new column is 0.8 or more. A column caught as a rename drops out of added and removed.

By names alone it is one that vanished and one that appeared. By values it is the same column. For each vanished column, divide the intersection size of its sample set with each new column's by the union size, pick the pair with the highest value, and treat it as a rename only when it exceeds the threshold.

Pick out the columns whose type changed

Make diff put the type change of a same-named column into retyped as [이름, 옛타입, 새타입] (the placeholders are the name, the old type, and the new type). If the name changed and the type changed along with it, put it in under the new name.

The type is an inferred value, so it can change with only a small change in the data. So put the old type and the new type together so that you can tell an integer becoming decimal notation from a number becoming a string. In the next step you handle these two at different levels.

Pin the compatibility rules down in code

Declare required, optional, and rules in /root/drift/contract.json, and have gate <기준 JSON> <파일> (the placeholders are the baseline JSON and the file) output {"verdict": …, "reasons": [...]}. An unknown column passes, and a removed required column is stop.

The list of required columns is decided by the business. Without order_id, shop_id, qty, amount_krw, and ordered_at you cannot build the aggregation, while without shop_name and weight the aggregation still comes out. Output reasons sorted, and attach to each reason the name of the column responsible.

Catch the change the schema cannot see

Add watch <기준 CSV> <파일> (the placeholders are the baseline CSV and the file) so that it measures the median and the ratio of the numeric columns present in both files. And write the week in which the unit changed in /root/drift/meaning.json as column, median_before, median_after, ratio, flag, and schema_verdict.

In this change the column name and the type stay the same, so all the judgments of the earlier steps pass. Only the value distribution drops. If the ratio is 3 or more or one third or less, mark that a person has to look — it is a lead, not evidence. In schema_verdict, write the gate judgment for the same week together, to record the fact that the schema check does not see this.

Report the seven weeks on one sheet

Compare the seven weeks against the baseline week, write baseline, weeks, pass, warn, and stop in /root/drift/drift_report.json, and write /root/drift/drift_report.md in four sections: ## 무엇이 바뀌었나 ## 무엇이 깨지고 무엇이 안 깨지나 ## 자동으로 잡히지 않는 것 ## 보내는 쪽과 맞출 것 (the Korean headings mean "What changed", "What breaks and what does not", "What is not caught automatically", and "What to agree on with the sender").

weeks is an object keyed by file name that holds verdict, added, removed, renamed, and retyped. If you include the baseline week itself, its verdict comes out as pass, which makes the comparison easier. In the report, write the weeks that got stop and the reasons, together with the numbers.