A visual glossary of data terms

Every data team has a set of words that everyone uses and half the room could not define under pressure. I picked eight. Each one gets a tiny interactive instead of a definition, because the way I actually learned them was by watching the rows move.

Why these eight

I did not pick the hardest terms. I picked the ones that cause the most damage when two people think they agree and do not. Grain and cardinality are the reason most wrong numbers exist. Idempotency is the reason most pipeline retries are safe or catastrophic. SCD type 2 and late-arriving facts are the two ways history quietly changes under a report. Coalesce is the small one that hides in every fallback chain. Edit distance and Soundex are the two matching algorithms that show up the moment you have to join names or addresses from two systems, and they are also the only two things on this page that are real code rather than a picture.

Say this in the interview

For each term, the sentence I would want to hear from a candidate. Not the textbook line, the line that proves you have been burned by it.

Edit distance. “It is the minimum number of inserts, deletes, and substitutions between two strings. I use it with a threshold for fuzzy joins, and I cap it by string length so short names do not match everything.”

Soundex. “It keeps the first letter and codes the next three consonant sounds, so spelling variants collapse to one key. It is a blocking key, not a match rule. I use it to shrink candidate pairs before a real comparison.”

Coalesce. “It returns the first non-null argument. I read it as a priority list: the order of the arguments is a business decision about which source you trust most.”

Grain. “The grain is what one row means. I state it in the model name or the first comment, and every measure in the table has to be additive at that grain or it does not belong there.”

Idempotent. “Running the job twice gives the same state as running it once. In practice that means delete-and-insert by partition or merge on a key, never append, never increment.”

SCD type 2. “You keep every version of a dimension row with valid_from and valid_to, and the fact table joins on the version that was current when the event happened. The cost is that the natural key is no longer unique, so every join needs the date.”

Late-arriving facts. “Events can land after the period they belong to has closed. I reprocess a trailing window, usually a few days, and I track a watermark so I know which periods are final.”

Cardinality. “Before I join, I check rows per key on both sides. If the dimension has more than one row per key and I did not expect it, the fact table is about to multiply.”

The two that matter most

If you only take two away, take grain and cardinality. They are the same problem seen from two sides. Grain is a promise about what one row is. Cardinality is what happens to that promise when you join. Almost every wrong dashboard number I have debugged came down to one of these: someone summed a table at a finer grain than they thought, or joined to a dimension that had two rows per key. The join widget above is the whole story in three columns. Four orders, a dimension with three versions per customer, twelve rows out.

The fix is boring and works. State the grain in the model. Test uniqueness on the key. Do that on every dimension, every time, and the row explosion stops being a surprise.

Where the two algorithms live

Edit distance and Soundex are older than most of the stack. Soundex dates to a 1918 patent and is still a built-in function in most warehouses. Levenshtein was published in 1965 and is also usually built in. I keep them in the glossary because people treat them as magic. They are not. The matrix widget is the entire algorithm: each cell is the minimum of three neighbours plus a cost. If you can read the matrix you can debug a fuzzy match.

What to do

• Write the grain of every model in one sentence at the top of the file. If you cannot, the model is not done.
• Add a uniqueness test on every dimension key and a rows-per-key check before every join you do not fully trust.
• Make every load step idempotent. Replace by partition or merge on key. Remove every balance + 1 style update from your pipelines.
• Decide a late-data window per fact table and reprocess it every run. Record the watermark somewhere a reader can see.
• When you need to match names across systems, block with Soundex, score with edit distance, and review the borderline.

Then send this page to the newest person on the team. It will save you a meeting.