Skip to content

MODULE 8 · LESSON 47 · Data tools and workflows

Combining datasets

Join a lookup table without multiplying records or hiding unmatched keys.

By Laurence Svekis · Self-paced · All worked data is synthetic unless you supply your own source.

Try the practice ↓

PredictInspectCalculateCheckExplainTransfer

Your starting point

Prerequisites: Use the ideas and vocabulary from lesson 46; revisit its worked examples if needed.

Definition: A join combines fields from tables using a matching key. A left join retains all records from the left table and adds matching fields from the right. Key uniqueness and table grain determine whether a join preserves or multiplies rows.

Inspect both grains before joining

A customer table may have one row per customer while an order table has many rows per customer. Adding customer attributes to orders can be a many-to-one lookup if the customer key is unique. Joining two tables that both contain repeated keys can produce multiple matches per row and inflate totals. A successful software operation is not proof of a valid analytic relationship.

Check unmatched and missing keys

Different formatting, leading zeros or incomplete coverage can prevent matches. A blank lookup result may mean no matching key, not a true missing attribute in a matched record. Count unmatched rows and inspect their importance. Do not automatically drop them, because that changes the population represented. Keep key transformations explicit and verify that they do not merge distinct identifiers.

Reconcile after the join

For a many-to-one left lookup, row count should remain the same and original numerical totals should reconcile. The academy refuses duplicate right-side keys to prevent accidental row multiplication. It prefixes added fields with lookup_ and reports unmatched records. This guarded join supports a clear beginner workflow while leaving more complex many-to-many designs to explicitly documented methods.

Two worked examples

A safe lookup

Orders contain customer IDs A, A and B. A customer lookup contains one row each for A and B with region labels. Joining region to the three orders should produce three rows. The two A orders remain separate events and inherit the same region; they are not duplicates simply because the attribute repeats.

A multiplication risk

If the lookup contains two rows for A, each A order could match twice. Two A orders would become four joined rows, potentially doubling their revenue in a later sum. The academy rejects that lookup until you resolve whether the repeated key represents revisions, multiple attributes or an inappropriate grain.

Where this becomes useful

A project team wants to add team names to task records. A unique team-code lookup is suitable, while a table containing many weekly team summaries is not automatically a safe lookup for every task.

Make an attempt

Load Retail sales in the workbench. In the lookup section, use region,owner with one North and one South row. Join on region and reconcile the original row count. Then deliberately duplicate a lookup key and inspect the rejection before restoring a valid lookup.

Open the accompanying interactive lab →

Misconception check

Claim: If a join runs without a software error, the resulting totals are trustworthy.

Inspect the correction

Check grain, key uniqueness, unmatched records and row-count reconciliation. Technically valid joins can still answer the wrong question.

Independent application

Create a small left table and lookup with one unmatched key. Perform the join, reconcile row count and an original total, and document the unmatched record. Explain how duplicate lookup keys would change the result and why they need a separate decision.

Completion records practice and self-review. It is not certification of statistical competence.