Window functions — keep the grain, rank the rows

At a glance

Learning objectives

• Keep the result grain intact — a window computes over a set without collapsing rows
• Find duplicate keys with one GROUP BY query before deduplicating
• Deduplicate on the business key, keeping the latest by event time

What you need

• A table with a business key and an event-time column
• [object Object]
• The local DuckDB from lesson 2 works for all the practice queries

Steps

1. Review the failure modes first — the sql-review skill exists to catch functions that silently change grain, alongside fan-out joins, NULL-unsafe predicates, implicit casts, and non-deterministic ordering. (from skill-sql-review )
2. State the grain of a result set in one sentence — a window keeps the row grain while computing over its partition, a GROUP BY changes it. (from skill-sql-review )
3. Find the duplicate key first — identify the duplicate key and count duplicates per key. (from runbook-duplicate-records )
4. Count duplicates per key with one query: SELECT k, count(*) c FROM t GROUP BY k HAVING count(*) > 1 ORDER BY c DESC LIMIT 20; (from runbook-duplicate-records )
5. Deduplicate on the business key, keeping the latest by event time. (from runbook-duplicate-records )
6. Treat the group-by (and any window over the same key) as a shuffle — key skew on a join or group-by key is the first cause to check. (from tutorial-spark-aggregations-windows )
7. Spill to disk from an oversized partition — a window partition that gets too big is a known shuffle failure. (from runbook-spark-job-slow )

Check yourself

• The deduped table has one row per business key, and the GROUP BY duplicate count proves it.
• You can say in one sentence what the window preserved that a GROUP BY would have collapsed.
• The window's PARTITION BY key is the same key you checked for skew.

Worked example

SELECT k, count(*) c FROM t GROUP BY k HAVING count(*) > 1 ORDER BY c DESC LIMIT 20;
SELECT min(dt), max(dt) FROM (SELECT dt, k, count(*) FROM t GROUP BY dt, k HAVING count(*)>1);

Sourced from

• SQL Review
• Duplicate records
• Aggregations and windows without the skew
• Spark job is slow

Security scan

Last sweep 2026-10-24: 0 finding(s), 0 marked as examples.

Common gotchas

• A window keeps the grain, a GROUP BY changes it — pick by the grain you owe the consumer.
• Dedupe on the business key, keeping the latest by event time — not by insertion order.
• A window over a skewed key is a shuffle with a hot partition — it can spill to disk.

Production considerations

• Keep the GROUP BY duplicate-count query next to the dedupe job as its check.
• Check the write mode before dedupe — append vs merge/overwrite decides what the rewrite touches.

Diagram

window functions and grain
   |
   |- GROUP BY --> new grain (one row per key)
   |
   |- window --> same grain, value computed over the partition
   |
   |- dedupe --> business key --> keep latest by event time
   |
   |- at scale --> window = shuffle --> skew / spill to disk

Registry stats