Keys, IDs, and joining in plain English

Two lists sit in two tabs. Both have a customer named “Acme.” One has a space. One has “ACME Inc.” A VLOOKUP fails, a person “fixes” it by hardcoding a number, and three months later nobody knows why revenue for Acme is a special case. The missing idea is not a better lookup formula. It is a key.

This is Part 3 of From spreadsheets to real data. You will learn what keys and IDs are for, how joining works without SQL jargon overload, and how name-matching culture creates silent wrong answers.

What you’ll learn

Example:

Join key example

Primary keys and foreign keys in plain English
Why names are terrible join keys
One-to-many joins and fan-out (why sums explode)
Sheet habits that keep IDs stable
How this sets up SQL joins later

A key is a stable nickname for a real-world thing

Example:

customer_id join example

A primary key uniquely identifies a row in a table: one customer_id per customer, one order_id per order. A foreign key is a primary key stored in another table to point back: orders.customer_id points at customers.customer_id. Together they let you combine facts without hoping labels match.

Join on the ID, not on the name string.

Names lie; IDs bore you on purpose

Join attempt	What goes wrong
Match on customer name	Typos, renames, legal suffixes, duplicates
Match on email	Shared inboxes, changes, personal vs work
Match on customer_id	Stable if your system issues IDs carefully

Humans read names. Systems should join on IDs. Display the name after the join for readability.

One-to-many and the exploding sum

One customer can have many orders. If you join customers to orders and then SUM a customer-level field (like credit_limit) without care, you multiply that field by the number of orders. The sheet will happily lie with confidence.

-- Smell test after any join
-- row_count should match expectations for the grain you want
-- If sums of parent fields jump, you probably fanned out

Rule of thumb: after a join, restate grain. “One row is now one order, not one customer.” Aggregations must respect that.

Sheet practices that keep keys healthy

Put IDs in their own columns; never hide them “to make it pretty.”
Store IDs as text if leading zeros matter (employee 00058).
Do not retype IDs from memory; copy from the source system export.
When you create manual rows, reserve an ID scheme (TMP-001) and mark status=manual.
Prohibit “fixups” that overwrite IDs because a name changed.

VLOOKUP culture vs key culture

VLOOKUP is not evil. Fragile keyless matching is. If your weekly process is “match on name and hope,” you are building a museum of exceptions. Prefer a proper ID from the CRM/ERP export, even if the sheet stays the workspace for a while.

Worked scene: support tickets and accounts

Support exports tickets with account_name typed by agents. Finance exports accounts with account_id and legal_name. Leadership wants tickets per account. Name matching recovers 80% and silently drops the rest into “Other.” An ID on the ticket at create time would have made the join boring, which is the goal.

How SQL will feel later

SQL joins are the same idea with stricter syntax. If you already think in keys, SQL series join lessons click faster. If you think in names, SQL will feel like the computer is pedantic. The computer is protecting you.

SELECT c.name, o.order_id, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

Read it as: start from customers, attach each matching order where the IDs equal.

Practice this week

On one multi-tab workbook, highlight every column that is actually an ID.
Find one VLOOKUP on names; write down failure modes.
Add a foreign key column to a child tab even if you still display names.
Document grain after your most common join in one sentence.

Common mistakes

Using display names as join keys
Reusing IDs after deleting rows
Joining then summing parent measures without re-aggregating
Hiding ID columns so “it looks clean”

Surrogate keys without fooling yourself

When a system will not give you an ID, people invent name-based “keys” that look unique until the real world changes. A safer temporary approach is an explicit mapping table: source_name, resolved_id, confidence, owner, as_of. Daily joins use resolved_id. Humans maintain the map when collisions appear. That is more work than a hopeful VLOOKUP and far less work than a public incident.

Never silently overwrite an ID because a display name changed. Names are attributes. IDs are identity. If legal renames Acme to Acme Holdings, the ID should survive. If two companies merge, that is a modeled event, not a casual cell edit at 5 p.m.

In sheets, protect key columns or clearly mark them. Hide them only if you also provide a visible stable code. “Looks clean” is not a data quality strategy. Clean is when a stranger can rebuild the join from documentation.

Fan-out stories you will meet

Orders to order_lines fans out. Customers to subscriptions fans out. Tickets to ticket_events fans out. Each is fine until someone averages a parent metric on the child grain. Teach your team to say the grain after every join out loud. “We are on order lines now; order-level shipping fee must not be summed naively.”

A simple sheet check: count rows before and after the join. If row count multiplies, ask whether that is expected. If yes, document it. If no, stop before the board deck freezes a wrong total.

Keys also help privacy and support. Tickets should reference account_id so renames and typos in free text do not strand history. The same discipline that helps analytics helps operations.

Practice beyond the happy path

Find one broken name match from last month and write the ID-based alternative. Estimate how many rows the name match dropped. Share that number with the stakeholder who asked for speed over keys. Speed without keys is often speed into a ditch.

Putting it into daily practice

Think about the last time two dashboards disagreed. The argument was rarely about chart colors. It was about definitions, grain, filters, and who changed a file without telling anyone. Spreadsheet discipline exists to make those arguments shorter and rarer. When you invest in keys, cleaning, and contracts, you are investing in fewer emergency meetings and more decisions that stay decided.

A useful test for any process is vacation resilience. If the owner disappears for two weeks, can someone else reproduce the number from written rules and a canonical file? If the answer depends on a private brain, you have a single point of failure wearing a friendly grid. Document the boring parts while they are still small enough to remember.

Tools will keep changing. Sheets today, a warehouse tomorrow, a notebook the week after. Habits transfer. Grain statements transfer. Ownership transfers. Hopeful name matching does not transfer; it just fails in a new syntax. Build habits that survive the next platform pitch from a vendor or a well-meaning executive.

Stakeholders often reward speed visibly and quality invisibly. Your job includes making quality visible: row counts, as-of times, exclusion lists, and links to definitions. When quality is visible, it can be valued. When it is invisible, it is treated as optional polish and then blamed when something breaks in public.

One more pass on judgment calls

Quick recap

Keys uniquely identify things; foreign keys point across tables. Join on IDs, show names for humans. Watch one-to-many fan-out so sums stay honest. Sheet habits that preserve IDs today make SQL and warehouses easier tomorrow.

Next: Cleaning before you analyze (Sheets edition). Series: From spreadsheets to real data.

Sources

Database key fundamentals (any standard intro to primary/foreign keys; e.g. PostgreSQL docs on primary keys): https://www.postgresql.org/docs/current/ddl-constraints.html
Analytics Made Simple SQL series on joins; Part 2 of this series on tidy tables

When you cannot get a system ID yet

Sometimes the upstream tool will not give you a key. Build a temporary surrogate carefully: a stable hash of fields that should not change, or a manually assigned code list with an owner. Record that it is temporary in the table contract later. Do not pretend a fuzzy name match is “good enough” forever if money or customers depend on it.

Also separate entity resolution (figuring out two names are one company) from daily joins. Resolution is a project. Daily joins should use the result of that project as an ID map, not rediscover Acme every Monday.