Reasoning Like the System-Design Round: Pruning, Consumer Lag, Windows, DAG Deps

Level 5: Level 5: Advanced & Company-Specific SQL for DE Interviewshard30 minstreamingpartition pruning / sargabilityEXPLAIN QUERY PLANtumbling event-time windowsKafka consumer lagDAG dependency eligibility

Make the four un-runnable system-design mechanisms concrete: sargable pruning, Kafka consumer lag, event-time tumbling windows, and DAG eligibility.

The system-design round, made runnable

The DE system-design round is a conversation: you are handed a vague prompt ("design a pipeline for clickstream analytics") and judged on how you reason through requirements, latency SLA, batch versus streaming, backfill, observability, and lineage. Four mechanisms come up in almost every one of these rounds, and none of them literally runs in SQLite. This lesson makes each one a real query so the reasoning is concrete rather than hand-waved.

Four mechanisms, each reduced to SQL

Partition pruning (does my filter scan the whole table). A filter on the partition or clustering column lets the engine skip files. The SQL analog is a sargable predicate that can use an index: WHERE ts >= '2026-07-03' AND ts < '2026-07-04' uses the index, while WHERE date(ts) = '2026-07-03' wraps the column in a function and forces a full scan. EXPLAIN QUERY PLAN shows the difference (SEARCH USING INDEX versus SCAN), the same reason a function on a partition key defeats pruning and bills terabytes in BigQuery. Level 6 teaches the distributed machinery behind these terms (partitioning and file pruning).
Kafka consumer lag (is my consumer keeping up). Lag is latest_offset - committed_offset per partition. That one subtraction over an offsets table is exactly what kafka-consumer-groups --describe reports, and a partition whose lag keeps climbing is a consumer falling behind.
Event-time tumbling windows (count per fixed interval, flag late data). Floor the event timestamp into fixed buckets with strftime and group. An event whose ingest time lands more than a window past its bucket is late data, the thing a streaming watermark decides whether to admit.
DAG dependency eligibility (which task can run now). A task is eligible when every upstream dependency has succeeded, a join over a dependency edge table, the same check an orchestrator makes before it schedules a task.

Why this matters

Naming the mechanism and writing its one-line query is what separates a candidate who has operated a pipeline from one who has only read about them. When the interviewer asks "how would you know the consumer is behind", the answer is not "monitoring", it is "lag is latest minus committed offset per partition, alert when it crosses a threshold".

Interview nuance: the sargability point is the most transferable. A function wrapped around a partition or indexed column (date(ts), UPPER(name), CAST(id AS TEXT)) defeats the index or the partition prune every time. Filter the raw column against computed bounds instead.

In the warehouse this differs. Filtering on the partition or clustering column drops BigQuery bytes billed from terabytes to gigabytes and prunes Snowflake micro-partitions, exactly as a sargable predicate uses a B-tree here. Flink TUMBLE(event_time, INTERVAL '1' HOUR) with watermarks is the streaming form of the strftime bucketing. The offsets table stands in for Kafka internals, and the lag arithmetic is what you monitor in production.

Sample data for this example

CREATE TABLE offsets (topic TEXT, partition INTEGER, committed_offset INTEGER, latest_offset INTEGER, consumer_group TEXT);
INSERT INTO offsets VALUES
  ('orders', 0, 1000, 1500, 'g1'), ('orders', 1, 5000, 200000, 'g1'), ('clicks', 0, 100, 100, 'g2');

Worked example (SQL)

-- Total and worst consumer lag per topic: the kafka-consumer-groups --describe view.
SELECT topic,
       SUM(latest_offset - committed_offset) AS total_lag,
       MAX(latest_offset - committed_offset) AS worst_partition_lag
FROM offsets
GROUP BY topic
ORDER BY topic;

Apply

Your turn

The task this lesson builds to.

Write a query that returns the consumer lag per partition as (topic, partition, lag, lag_alert), over offsets(topic, partition, committed_offset, latest_offset, consumer_group).

lag is latest_offset - committed_offset, and lag_alert is 1 when lag exceeds 100000 and 0 otherwise. Order by topic then partition. Alias the columns exactly as named.

3 hints and 1 automated check are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, so it survives past today.

Write a query that returns, per 1-hour tumbling window, the event count and the count of late-arriving events, as (window_start, event_count, late_count), over events(event_id, event_time, ingest_time).

Bucket event_time into 1-hour windows (the hour floor), count events per window, and count an event as late when its ingest_time is more than one hour past the end of its window (more than two hours after the window start). Order by window_start. Alias the columns exactly as named.

3 hints and 1 automated check are waiting in the workspace.