Why should a batch task derive its target partition from the run's interval rather than the current date?
answer
- the clock moves, the interval does not
- what happens if the retry starts at 00:05
- a backfill of 400 runs, one target partition
- derive the filter and the path from run parameters
basics
~20 sBecause the wall clock moves and the interval does not. A task scoped by the current date reads and writes whatever today happens to be, so retries after midnight, replays and backfills all land in the wrong slice and collide with each other.
solid answer
~50 sA run should be a pure function of the window it was created for. If the task computes its filter and its output partition from `now()` or `CURRENT_DATE`, the result depends on **when the process happened to execute** rather than on which window it represents. Three concrete failures follow: a retry that starts after midnight writes yesterday's data into today's partition; a replay of a 90-day-old window reads the last 24 hours of source data instead of that window's; and a backfill of 400 windows has every run resolve to the same target partition, so they overwrite each other and you end up with one day of data. The fix is to take interval start and end as explicit run parameters, use them in both the source filter and the destination path, and treat `now()` inside transformation logic as a code smell. Half-open bounds — `>= start AND < end` — keep windows from overlapping or dropping rows on boundaries.
code
sql · 14 lines-- clock-scoped: result depends on when the process ran
INSERT INTO events_daily
SELECT user_id, count(*) AS n, CURRENT_DATE - 1 AS dt
FROM raw_events
WHERE event_ts >= now() - interval '1 day'
GROUP BY user_id;
-- interval-scoped: result depends only on the window
INSERT INTO events_daily
SELECT user_id, count(*) AS n, :interval_start::date AS dt
FROM raw_events
WHERE event_ts >= :interval_start
AND event_ts < :interval_end -- half-open, no boundary double-count
GROUP BY user_id;go deeper
Recognise that now() and CURRENT_DATE inside pipeline logic make a run depend on when it happened to execute. Know that the window should be passed in as a parameter instead.
Explain the concrete breakages — the retry that crosses midnight, the replay that reads the last 24 hours, the backfill where every run targets one partition — and why interval bounds are half-open.
Show judgment about which timestamp defines the window: event time, arrival time, or a mutable updated_at, and what each costs in reproducibility. Be ready to size a rolling reprocessing window from measured lateness.
Set the standard: interval parameters passed into every task, clock functions banned from transformation logic, and stateful bookmarks confined to the single extract hop so the rest of the platform stays replayable.
## The rule A run is created for a window of data. Everything the task does should be derived from that window: which source rows it reads, which output slice it replaces, which files it writes, what it names them. Nothing should be derived from the moment the process happens to start. The interval is an input; the clock is an accident of scheduling. ## What goes wrong when the clock leaks in **Retries that cross a boundary.** A daily task starts at 23:50, fails, and the retry begins at 00:05. If the destination is `CURRENT_DATE - 1`, the first attempt targeted one day and the retry targets another. You now have a missing day and a doubled day, produced by a task that reported success. **Replays read the wrong data.** A source filter like `WHERE event_ts >= now() - interval '1 day'` describes a sliding window relative to execution. Re-running the window for a date three months ago reads the last 24 hours of the source and writes it under the old date's label. The pipeline turns green and the data is nonsense. **Backfills collapse.** Queue 400 runs of a task whose output partition is `CURRENT_DATE`. All 400 resolve to the same partition and stampede over each other. When it finishes, you have one partition containing whichever run committed last, and 399 wasted runs' worth of compute spend. **Late starts create gaps.** A schedule that fires hourly but computes `now() - 1 hour` will, on a run delayed by queueing, skip the minutes between the intended window and the actual one. Nothing errors; rows just vanish. This is the hardest variant to detect because the loss is proportional to scheduling jitter. ## Half-open bounds Use `>= interval_start AND < interval_end`. Closed-closed bounds (`BETWEEN start AND end`) double-count any row sitting exactly on the boundary — midnight, or the top of the hour — because it belongs to two adjacent windows. Closed-open is the convention that makes adjacent windows tile the timeline exactly once, and it is worth being explicit about in code review because `BETWEEN` reads more naturally and is therefore the default mistake. ## Which timestamp defines the window Interval-scoped reads assume the source can be filtered by something meaningful. There are three candidates and they behave differently: - **Event time** — when the thing happened. Best for reproducibility, but late-arriving records land in a window whose run already completed, so you need a reprocessing policy. - **Arrival/ingestion time** — when the record landed in your raw zone. A window is complete the moment it closes, so replays are exact, and late events simply appear in the arrival window they actually arrived in. This is the friendliest choice for replayable pipelines. - **`updated_at` on a mutable source table** — the most common in practice and the least replayable. If a row's `updated_at` moves after you processed its window, replaying that window no longer returns it, and replaying a later window does. The task is idempotent but no longer reproducible. ## The bookmark alternative and what it costs The opposite design is a **high-water mark**: the task stores the maximum `updated_at` it has seen and next time reads everything above it. This is stateful. It has real advantages — it copes with sources that have no clean event-time column, and it never re-reads rows unnecessarily — but it gives up the property this question is about. There is no such thing as "re-run the window for 3 May": the task's scope is defined by a mutable cursor, not by a parameter. Backfilling means manually resetting the cursor, which is a destructive edit to shared state, and two concurrent runs racing on that cursor can skip data outright. A practical compromise: bookmark the *extract* from the operational source into an immutable, arrival-time-partitioned raw zone, and make everything downstream of that zone interval-scoped and freely replayable. The stateful, hard-to-replay part is then one hop wide instead of running through the whole pipeline. ## Late-arriving data Even with perfect interval scoping, records show up after their window closed — a mobile client that was offline, a source system that corrects yesterday's row. Interval scoping does not solve this; it makes solving it possible. The standard answer is a **rolling reprocessing window**: every night, re-run not just the last window but the last N windows, where N covers the lateness you actually observe. That is affordable precisely because the task is idempotent and interval-scoped — reprocessing seven days is seven replaces, not seven doubled partitions. Size N from measured lateness (the distribution of arrival time minus event time), and treat anything later than N as requiring a manual backfill rather than silently disappearing. ## Making it enforceable The habit is easy to state and easy to violate under time pressure, so back it with mechanics: pass interval start and end explicitly into every task, ban `now()`/`CURRENT_DATE` in transformation SQL through review or a lint rule, and make the destination path a function of the interval parameter. A useful test is to run a task twice with the same interval a day apart and assert the output is identical — any clock leakage fails it immediately.
- Why use half-open bounds for the interval filter rather than BETWEEN?Closed-closed bounds include both endpoints, so a row timestamped exactly at midnight belongs to two adjacent windows and gets counted twice. Half-open — greater-or-equal start, strictly-less-than end — makes adjacent windows tile the timeline exactly once. BETWEEN reads more naturally, which is why it is the default mistake in review.
- How does a stored high-water mark differ from interval-scoped reads, and what does it cost?A high-water mark is mutable state: the task reads everything above the last cursor it saved. It copes with sources that lack a clean event-time column, but there is no way to ask for one specific past window — replay means destructively resetting shared state, and concurrent runs racing the cursor can skip rows. Interval scoping keeps scope in the run parameters, where it is reproducible.
- With interval-scoped tasks, how do you handle rows that arrive after their window has already run?Use a rolling reprocessing window: every night re-run the last N windows rather than only the newest, sizing N from the observed distribution of arrival time minus event time. That is affordable only because the write replaces the slice. Anything later than N should raise an alert and get an explicit backfill, not vanish.
saying these in an interview costs you the question
- Filters the source with now() minus an interval
- Names the output partition from CURRENT_DATE
- Uses BETWEEN for interval bounds and double-counts boundary rows
- Assumes a run always executes inside the window it represents
- Treats a mutable updated_at column as a reproducible window definition