Why a Join Can Change the Data
A prediction data set has a defined meaning for each row: for example, one policy at a particular prediction date. Joining another table adds columns, but it can also change the number of rows. If a key appears more than once in the added table, a single prediction record can match multiple rows and be repeated. If a key is absent, an inner join can remove a prediction record entirely.
The central check is therefore not just whether two tables share a column. It is whether the key identifies the intended record in each table and whether the relationship between the tables matches the purpose of the analysis. A join that returns plausible-looking columns can still alter the population or give some records more influence than others.
For example, a prediction table might have one row per policy-month, while a payment table has one row per payment. The policy identifier alone may be unique in neither table. The correct matching fields might need to include both policy identifier and month—or the payment table might need to be summarized to policy-month before it can be joined safely.
Join Relationships and Row Counts
For a key value \(k\), let \(n_L(k)\) be the number of rows with that key in the left table, and \(n_R(k)\) the number in the right table. An equality join pairs every matching left row with every matching right row. Thus, for an inner join, key \(k\) contributes \(n_L(k)n_R(k)\) joined rows. This multiplication is the source of accidental duplication in many-to-many joins.
In a left join, each left row is retained. If it has no match, its right-hand fields are missing; if it has multiple matches, that left row appears once for each match. In particular, a left join preserves the left row count when the right-hand key is unique for every matched key. It does not guarantee preservation merely because the left table is named first.
| Relationship | Meaning for a left-table row | Typical row-count risk |
|---|---|---|
| One-to-one | Matches at most one row on the right | Usually preserves left row count in a left join; unmatched right fields are missing |
| One-to-many | Matches multiple right rows | Repeats left rows in a left join |
| Many-to-one | Multiple left rows may match one right row | Can preserve left row count in a left join if each left row has at most one match |
| Many-to-many | Multiple rows on each side share a key | Creates every matching pair for that key |
The multiplication formula follows directly from the matching rule: each of the \(n_L(k)\) left rows can be paired with each of the \(n_R(k)\) right rows. Counting those choices gives \(n_L(k)n_R(k)\). For example, two left rows and three right rows with the same key produce six pairs, not three or two. This explains why row counts should be checked before and after joining.
Worked Example: A Duplicate Customer Key Repeats Prediction Records
Method: Check whether the proposed right-hand key is unique, then trace each match and compare the row count and outcome summary before and after a left join. A hypothetical insurer has these prediction records:
| Prediction record | Customer key | Outcome |
|---|---|---|
| R1 | C-A | 1 |
| R2 | C-A | 0 |
| R3 | C-B | 0 |
A contact table has one row for each recorded contact type: C-A appears twice (Email and Phone), and C-B appears once (Email). Join the tables by customer key. Is the resulting table still one row per prediction record, and what is the positive-outcome proportion before and after the join?
Step 1: Check the key. C-A occurs twice in the contact table, so customer key is not unique there. R1 and R2 each match two contact rows. R3 matches one.
Step 2: Count joined rows. The left join gives \(2+2+1=5\) rows. R1 appears twice, R2 appears twice, and R3 appears once. The joined table no longer has one row per prediction record.
Step 3: Compare outcome proportions. Before joining, there is one positive outcome among three records, so the proportion is \(1/3 \approx 0.3333\). After joining, the positive outcome on R1 is repeated twice, while the zero outcomes occur three times in total. The proportion is \(2/5=0.4000\).
Check: The row count rose from 3 to 5, and the summary changed from \(0.3333\) to \(0.4000\). The changed proportion is not evidence that customer outcomes changed; it is a consequence of giving R1 and R2 two rows each. Common mistake avoided: treating the joined rows as independent prediction records without checking whether the customer key is unique.
Diagnose Before Joining
A reliable join starts with the intended row meaning, not with the software command. Write down what one row represents in each table, identify the proposed matching fields, and check the key counts on both sides. Then decide whether the relationship is expected: for example, one customer to many policies may be legitimate, while one policy-month to many unexplained policy-month records may indicate a data issue.
Describe each row precisely, such as “one prediction record per policy-month” and “one row per payment.”
Use every field needed to identify the intended match. A key may be composite, such as policy identifier plus month.
Count rows per key on each side. Identify duplicates and keys with no match before interpreting the joined table.
Use the match counts to anticipate whether rows will be preserved, repeated, or dropped. Compare actual row counts and distinct prediction-record counts after joining.
Correct the key or summarize the many-row table to the required grain before joining. Then repeat the checks.
A useful validation is to count distinct identifiers for the intended prediction records both before and after the join. Equal counts are reassuring but not sufficient by themselves: some records could be duplicated while others are lost, leaving the total unchanged. Also check per-key match counts, unmatched keys, and whether each intended record occurs exactly once.
Worked Example: Two Child Tables Create a Many-to-Many Join
Method: Calculate matches per key on both child tables, use the product rule for joined rows, and compare summed values with a summary made at the prediction grain. A prediction table has one row each for policy-months K1 and K2. A coverage table has two rows for K1, with units 1 and 2, and one row for K2, with units 1. A payment table has two K1 payments, $100 and $200, and one K2 payment of $50. Both tables are joined to the prediction table using the policy-month key.
Step 1: Count matches. For K1, the coverage table has 2 rows and the payment table has 2 rows. For K2, each has 1 row. The two K1 child tables therefore create \(2 \times 2=4\) combinations; K2 creates \(1 \times 1=1\).
Step 2: Count the combined result. Joining both child tables produces \(4+1=5\) rows from 2 prediction records. At K1, each coverage row is paired with each payment row. The K1 combinations contain payment values $100, $200, $100, and $200.
Step 3: Check the payment total. The source payment total is \(100+200+50=\$350\). Summing the payment column after the join gives \(100+200+100+200+50=\$650\). The K1 payments have each been counted twice because there are two coverage rows.
Step 4: Summarize to the required grain. If the prediction features require total coverage units and total payments per policy-month, first summarize each child table separately. K1 has coverage units \(1+2=3\), payment count 2, and payment total $300. K2 has coverage units 1, payment count 1, and payment total $50. These summaries each have one row per key, so joining them to the two prediction records yields two rows and the correct overall payment total of $350.
Check: The summarized amounts add to \(300+50=\$350\), matching the original payments, and the final table has one row per prediction record. Common mistake avoided: joining two detailed child tables directly and then treating repeated payment or coverage values as additional observations.
Composite Keys, Unmatched Records, and Join Type
A key that appears unique in a broad sense may still be too coarse for the intended match. Suppose a prediction table contains one record per policy-month, and a monthly score table also contains a row for each policy-month. Joining only on policy identifier ignores the month. If a policy has two monthly scores, each prediction month can match both scores. The join has matched the same policy, but not the correct prediction record.
A composite key combines multiple fields to identify a record. For a policy-month table, the pair (policy identifier, month) may be the required key. Check uniqueness on the pair rather than assuming that any single field is adequate. Also inspect unmatched keys: an unmatched record may be expected, or it may reveal an inconsistent identifier, an omitted key field, or a source-table coverage issue.
The choice of join type matters too. A left join retains every row from the prediction table, marking unmatched added fields as missing. An inner join retains only rows with a match and can therefore change the modeled population. A full join retains rows from both sides, including right-only records that may not correspond to prediction records. Select the join type based on the records that should remain, then verify the result rather than relying on the join name.
Worked Example: A Composite Key Prevents Cross-Month Matches
Method: Compare a coarse key with the composite key implied by the row grain, then verify both matched and unmatched prediction records. A hypothetical prediction table contains P7-January, P7-February, P8-January, and P9-January. A score table contains P7-January with score 10, P7-February with score 12, and P8-January with score 8. There is no score row for P9-January.
Step 1: Join on policy only. P7 has two prediction rows and two score rows. They produce \(2 \times 2=4\) matches, including the incorrect pairing of P7-January with score 12 and P7-February with score 10. P8 produces one match. P9 is retained with missing score fields in a left join. The result has \(4+1+1=6\) rows.
Step 2: Join on policy and month. P7-January matches only score 10, P7-February matches only score 12, and P8-January matches only score 8. P9-January has no match but remains in the left join with missing score fields. The result has 4 rows, the same number as the prediction table.
Check: The composite-key join preserves the four prediction records and assigns only month-matched scores. P9 remains visible as unmatched rather than being silently discarded. Common mistake avoided: using a familiar identifier alone even though the row meaning includes a time period.
Common Mistakes and Practical Checks
Joining tables is a data-design decision, not merely a mechanical step. A safe process makes the intended row meaning explicit and verifies it after every join. This discipline is aligned with the SOA Exam PA (Predictive Analytics) syllabus because a model can be technically fitted to a data set whose records no longer represent the intended prediction cases.
- Assuming a left join cannot change row count. It can repeat left rows whenever a matching right key occurs multiple times.
- Checking only the total row count. A total can conceal a duplicate in one key group and a missing record in another. Inspect match counts by key and distinct prediction identifiers.
- Joining on an incomplete key. If the grain is policy-month, policy alone may pair different months. Use the fields that identify the intended record.
- Summing repeated values after a many-to-many join. A payment or exposure value can appear in multiple combinations. Summarize each detailed table to the required grain before combining them when that is the intended analysis.
- Using an inner join without checking what it removes. Unmatched prediction records disappear. Confirm that dropping them is appropriate before using the result.
Check Your Understanding
For each problem, state the row-count or summary check that supports your answer.
- A left table has three records with keys A, B, and C. A right table has one row for A, two for B, and none for C. How many rows result from a left join, and how many from an inner join? (Answer: Left join 3 rows; inner join 2 rows.)
- A prediction table has four rows for key M. A detail table has three rows for M. If they are joined on M, how many joined rows are produced for M? What relationship does this represent? (Answer: 12 joined rows; many-to-many for key M.)
- Two policy-month prediction records, Q-June and Q-July, are joined to two score records for the same policy, one for each month. The join uses policy only. How many matches result, and what key should be used to match each month correctly? (Answer: 4 matches; use policy and month as a composite key.)
- A base table has outcomes 1 and 0 for records U and V. A lookup table has two rows for U and one for V. What is the positive-outcome proportion before the left join and after it? (Answer: Before, \(1/2=0.5000\); after, \(2/3=0.6667\).)
- A prediction table has one row each for keys J and K. A transaction table has two J transactions of $40 and $60, and one K transaction of $25. If the prediction table needs total transaction amount per key, how many rows should the summarized transaction table have, and what totals should it contain before joining? (Answer: 2 rows; J totals $100 and K totals $25.)