Spark SQL and DataFrame engineering · Lesson 3 of 3

Spark SQL joins, aggregations, and windows

Combine datasets and compute grouped or row-relative measures.

Lesson

Joins match rows using a condition; aggregations summarize groups; window functions calculate values across related rows while retaining row-level detail. Select the join type based on whether unmatched rows must be preserved, and qualify columns after joining tables with overlapping names. Before aggregating, determine the intended grain so a one-to-many join does not multiply measures unexpectedly. Window partitions define groups and ordering defines the sequence used by functions such as row_number or lag. If order is not deterministic, tied rows can produce unstable results unless an additional tie-breaker is supplied. Inspect row counts and key uniqueness before and after a join. Practice task: calculate the latest event per account and explain how ties are resolved. Applying the pattern: State the row grain, validate join keys, choose a join type, aggregate or window over an explicit order, and compare row counts. Checks and failure modes: A join that duplicates a dimension key may silently inflate sums; a window order without a tie-breaker can be nondeterministic. Before production, test both a representative input and a boundary case. Confirm the expected row grain, permissions, recovery behavior, and observable output for the task. Record the configuration needed to reproduce the run and compare its result with the agreed data contract. Practice task: What is a key safeguard against inflated sums after a join?

Visual guide

Reason about a join and window

Step 01 Define grain
Decide what one output row represents.
Step 02 Join
Match rows using validated keys.
Step 03 Order
Set deterministic window ordering.
Step 04 Check
Verify counts and measures.

Code examples

Select the latest event per account

SQL

SELECT account_id, event_type, event_ts
FROM (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY account_id ORDER BY event_ts DESC, event_id DESC
  ) AS rn
  FROM main.events.silver_activity
)
WHERE rn = 1;

Expected output

A stable tie-breaker selects one latest event for each account.

Key points

State the row grain, validate join keys, choose a join type, aggregate or window over an explicit order, and compare row counts.
A join that duplicates a dimension key may silently inflate sums; a window order without a tie-breaker can be nondeterministic.
Re-run the included practice scenario after changing source data or transformation logic.

Check your understanding

Practice

What is a key safeguard against inflated sums after a join? Check key cardinality and the resulting row grain Add DISTINCT to every query Use a larger cluster Remove all unmatched rows

Your learning progress