Apache Spark / Basic: typed relational foundations / Lesson 5

Joins, grain and reference data

Enrich readings without multiplying energy or losing sites whose reference data is incomplete.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Predict cardinality from actual key uniqueness.
Choose outer, semi and anti joins from the required population.

A join changes what one row means

A reading row represents one observation, a site row describes one site and an inspection row represents one visit. Joining a reading to three inspections creates three result rows unless the relationship is reduced first. Summing kwh after that join triples the reading’s contribution. Spark does not automatically enforce a primary-key declaration on every file-backed DataFrame, so “the dimension should be unique” is not evidence that it is. Validate uniqueness at the intended join key and effective time. A reference table with historical versions can be legitimately plural and needs an explicit current or temporal selection rule.

Count a duplicate-reference fixture by hand

The first SQL example intentionally gives north two reference labels. Its two-kWh reading appears twice after an inner join, so the joined sum would be four. Both rows are valid under the query’s written semantics, even though the business result is wrong. DISTINCT on the final sum is not a principled repair. Choose the authoritative dimension row before joining or change the output grain if the report truly needs every version. A tiny adversarial fixture reveals the defect more clearly than a large total that happens to look plausible.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

WITH readings AS (SELECT * FROM VALUES ('north',2) AS r(site_id,kwh)),
 sites AS (SELECT * FROM VALUES ('north','A'),('north','B') AS s(site_id,label))
SELECT r.site_id, r.kwh, s.label
FROM readings r JOIN sites s ON r.site_id=s.site_id
ORDER BY s.label;

Expected output

site_id | kwh | label
north   | 2   | A
north   | 2   | B

Two rows result, each carrying kwh=2. This is a logical cardinality example, not measured Spark output.

Keep optional-side filters in the intended stage

A left join retains readings with no matching site and supplies NULL reference fields. A later WHERE on the right-side active flag can remove those unmatched rows and change the query into an effective match requirement. Decide whether unknown sites should remain in a completeness report, be quarantined or be excluded deliberately. Do not let a performance rewrite decide that policy accidentally. Qualify shared column names and project the intended key once to avoid ambiguity. A join using a list of common key names has different output-column convenience from a general expression join, even when row eligibility is equivalent.

Use a semi or anti join for a presence question

If the report only asks which readings lack a site reference, a left anti join states that population directly and does not return dimension columns. A left semi join selects readings with at least one match without multiplying them for each matching reference row. These forms can avoid a join-plus-DISTINCT pattern that hides data defects. The PySpark fragment assumes existing lab DataFrames and preserves reading columns only. It is non-runnable. NULL keys need a separate rule, since ordinary equality does not match two missing site identifiers.

python Illustrative technical example; logical results are reasoned, not engine-tested.

unmatched = readings.join(sites.select('site_id'), ['site_id'], 'left_anti')
matched_once = readings.join(sites.select('site_id'), ['site_id'], 'left_semi')

Each output preserves the left reading grain. Semi matching does not establish that the site dimension is unique.

Choose physical strategies after semantic review

Spark may use broadcast, shuffle hash or sort-merge strategies depending on data, statistics, configuration and adaptive execution. A broadcast hint does not repair duplicate dimension keys and can cause memory pressure if the relation is too large. First prove the row contract, then inspect an authorized plan and representative runtime metrics. Reference-data updates also need a batch-consistency policy so different partitions do not see uncoordinated external lookups. Prefer a documented snapshot of the dimension over a network call per reading. This course runs no join engine, broadcast, network request or distributed benchmark.

Common errors and fixes

Energy increases after enrichment

Why it happens: The reference key is nonunique at the joining grain.
How to fix it: Select or aggregate reference rows under a documented rule before joining.
Prevent it next time: Assert dimension uniqueness and reconcile energy before and after enrichment.

Unknown sites disappear from a completeness report

Why it happens: A right-side predicate rejected outer-join NULL rows.
How to fix it: Place optional-side conditions in the intended join or classification stage.
Prevent it next time: Test one reading without any site match.

AMBIGUOUS_REFERENCE

Why it happens: A shared column name is unresolved after a join.
How to fix it: Qualify aliases and project a deliberate output schema.
Prevent it next time: Review output column ownership with join conditions.

Check your understanding

1. A two-kWh reading matches three site-history rows. What is its contribution after summing the raw join?

Show worked solution

Six kWh, which is a multiplication artifact rather than new energy. Resolve the historical row selection or aggregate at the correct grain before joining. Reconcile reading identities as well as total energy so offsetting errors cannot hide the defect.

2. Does a left semi join prove reference-key uniqueness?