Apache Spark / Intermediate: distribution and execution evidence / Lesson 7
Windows and deterministic deduplication
Retain reading detail while choosing authoritative revisions and computing ordered energy measures.
On this page
Lesson goals
- Define window partitions, ordering and frames separately.
- Choose a reproducible winner rather than an arbitrary duplicate.
A window keeps the current row visible
GROUP BY produces one row per group, whereas a window calculation adds a value to each input detail row. PARTITION BY site_id defines an independent analytical population, not a promise about existing physical partitions. Ordering defines sequence within that population, and a frame determines which rows contribute to an aggregate. These are separate decisions. If a join already duplicated a reading, a window does not repair that multiplication. Establish the input grain and eligible revision before adding cumulative totals or comparisons with previous observations. An orderly-looking result can still contain the wrong facts.
Choose a winner with a business ordering
A repeated reading identifier can represent duplicate delivery or a legitimate corrected revision. dropDuplicates on the identifier alone does not express which revision should win. The SQL fixture chooses the highest source_version, then a stable source_record_id for reproducibility. That tie-breaker is appropriate only when equal-version payloads are equivalent under the source contract; contradictory equal versions should be quarantined instead of silently selected. The example returns r1 with three kWh and r2 with four. It does not prove that a real feed has monotonic versions or that every source record has been received.
WITH incoming AS (SELECT * FROM VALUES
('r1',1,'a',2),('r1',2,'b',3),('r2',1,'c',4)
AS t(reading_id,source_version,source_record_id,kwh)),
ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY reading_id
ORDER BY source_version DESC, source_record_id ASC) AS rn FROM incoming)
SELECT reading_id,kwh FROM ranked WHERE rn=1 ORDER BY reading_id;reading_id | kwh
r1 | 3
r2 | 4The winning logical rows are r1=3 and r2=4. ROW_NUMBER requires an explicit complete ordering for reproducible selection.
State whether peers or sequential rows accumulate
For a sequential energy ledger, use an explicit ROWS frame and a total ordering. A default logical RANGE frame can give equal ordering peers the same accumulated total, which answers a different question. The fixture orders unique reading identifiers to isolate frame arithmetic: two, then five, then nine. Real production ordering normally follows event time with a stable identity tie-breaker. The ORDER BY inside OVER controls calculation, not final presentation, so the outer query also orders its result. A running total over the wrong revision population remains wrong even when every frame is syntactically valid.
SELECT reading_id,kwh,SUM(kwh) OVER (ORDER BY reading_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_kwh
FROM VALUES ('r1',2),('r2',3),('r3',4) AS r(reading_id,kwh)
ORDER BY reading_id;reading_id | kwh | cumulative_kwh
r1 | 2 | 2
r2 | 3 | 5
r3 | 4 | 9The reasoned cumulative values are2,5,9. No Spark window or sort is executed.
Previous depends on a declared history
LAG and LEAD compare adjacent ordered observations without a full self-join. Arrival order and event order can differ when meters reconnect, so name which history the analysis reconstructs. A missing preceding row differs from a preceding row with unknown energy, even when both yield NULL under an expression. Rank functions also differ: RANK preserves ties with gaps, while DENSE_RANK removes those gaps. ROW_NUMBER supplies unique positions but not a meaningful winner unless ordering resolves ties. Keep timestamp duplicates, late corrections and missing values in the test fixture rather than relying on perfectly increasing sample times.
Window scope is also a resource decision
A window can require repartitioning and sorting rows by its partition and order keys. A global window with no partition clause can concentrate work and become a bottleneck; a site with most readings can still be oversized even with site partitioning. Project necessary columns and filter the intended population before the window when semantics allow it. Inspect authorized plans and task metrics before choosing a physical remedy. This course validates only tiny revision and cumulative arithmetic models in standard Python; it starts no Spark engine, shuffle or executor and reports no window benchmark.
Common errors and fixes
A corrected reading loses to an older duplicate
- Why it happens
- Deduplication named the key but no authoritative revision ordering.
- How to fix it
- Rank source revisions with a documented deterministic conflict policy.
- Prevent it next time
- Test out-of-order revisions and equal-version contradictions.
Running totals jump over equal timestamps
- Why it happens
- A logical peer frame was mistaken for sequential row accumulation.
- How to fix it
- Choose a ROWS frame and total order when a ledger is intended.
- Prevent it next time
- Include tied ordering values in acceptance tests.
A global window becomes one slow task
- Why it happens
- The analytical population concentrated work without suitable partitioning.
- How to fix it
- Review whether an independent business partition is valid before tuning.
- Prevent it next time
- Inspect window distribution and projected data width.
Check your understanding
1. Does dropDuplicates(["reading_id"]) guarantee the greatest source_version survives?
Show worked solution
No. It does not encode that winner policy. Use a reviewed ordering and retain conflict evidence for equal versions with different payloads. Verify the chosen records and source accounting before summing energy; a correct count can still hide the wrong revision.
2. What are sequential totals for readings2,3,4?
Show worked solution
They are2,5,9 under a ROWS frame in that declared order. Equal event times need a stable tie-breaker, and the final query needs its own ordering for presentation. This arithmetic does not establish the physical Spark execution plan.
Key takeaways
- Deduplication needs a business winner and conflict policy.
- Analytical partition, ordering, frame and presentation are independent choices.