Keeping a Stored Top N Current

The next probe assumes the query is settled. "The dashboard reads top 3 per department from a table you maintain, top_earners. HR deletes a terminated employee. What happens to your table?" The answer that costs the round is "delete the matching row." Department 10 then shows 2 names, because the 4th-highest earner should move up and a top 3 table discarded everything below the cutoff when it was written.

State the asymmetry as a rule: a stored top N absorbs any change that adds a competitor, and any change that removes one forces that group to be rebuilt from source. An insert needs 1 comparison against the floor, the group's current Nth row. Deleting a stored row needs the group's N+1th row, which the store never held. Updates split the same way: a raise outside the store is an insert, a pay cut inside it is a delete, and a transfer is a delete in one group and an insert in the other.

3 ways to keep the stored table current

Option 1 rebuilds everything on a cadence. A nightly job runs the window query over the 3.2 million employee rows, writes the 5,400 survivors (3 per department across 1,800 departments) and swaps the table in; Postgres REFRESH MATERIALIZED VIEW CONCURRENTLY does this with readers unblocked. It reads nearly 600 rows per row kept and runs up to 24 hours stale. Name both numbers, then say that a 40-second nightly rebuild is the right design for most reports, because the interviewer is waiting to hear it.

Option 2 rebuilds only the groups that changed. CDC from employees yields the touched department_ids, both sides of a transfer. In a typical hour that is around 40 of 1,800, so the refresh reads around 2% of the source and rewrites about 120 rows. The rebuild runs the same window query per group from source, so it is idempotent, and it doesn't care whether a delete arrived before its insert, the ordering that corrupts applied deltas.

-- Group-scoped refresh: rebuild only departments touched since the last run
DELETE FROM top_earners
WHERE department_id IN (SELECT department_id FROM changed_departments);

INSERT INTO top_earners (department_id, employee_id, salary, hire_date)
SELECT department_id, employee_id, salary, hire_date
FROM (
  SELECT e.*,
         ROW_NUMBER() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC, hire_date ASC, employee_id ASC
         ) AS rn
  FROM employees e
  WHERE e.department_id IN (SELECT department_id FROM changed_departments)
) ranked
WHERE rn <= 3;

Option 3 stores a margin: keep the top 10 per department and serve WHERE rn <= 3. A deleted stored row promotes the 4th from rows already held, and the group needs a source rebuild only when its margin falls below 3. The cost is a table 3.3 times larger and a filter on every read; it pays when deletes are frequent and the source is expensive, such as a top N over a 2 billion row event table.

Warehouse materialized views will not maintain it

Asked why the warehouse should not maintain it, give facts: Snowflake materialized views reject window functions, BigQuery's incremental materialized views reject analytic functions, and a Postgres materialized view has no incremental refresh at all, so every refresh is the full rebuild. Then the check, since these rules change by version: "I would confirm the engine maintains a ranking view incrementally, and if it cannot, I own the refresh."

The closing follow-up is "how do you know the stored table is right?" Recompute the top 3 for 50 random departments from employees, EXCEPT it against the store in both directions, and alert on any row. Drift runs both ways: a missed delete or pay cut leaves a stale row in the store, and a missed insert leaves a row missing, as does a raise or inbound transfer you never saw. Report which side of the EXCEPT the rows came from, because that names the kind of CDC event you lost.

• N+1

About this lesson

This section is part of the Top N Per Group: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.