IngestionGENERALTOOL-SPECIFICSCALE-SPECIFIC

Batch Ingestion

Every hour, select what is new, write files, load the warehouse. The simplest thing that works — and the specific ways it stops working.

What actually happens How to build it Can I trust it?

Who needs this, what one row is, and why the obvious build breaks

Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.

The question

When is a scheduled extract the right answer, and what exactly does a batch boundary do to the data that sits on either side of it?

Who needs this

A staging model that expects to find one complete window's worth of files at a known path, and an analyst who will compare today's number to yesterday's and needs both days to have been assembled the same way. Neither of them can tolerate a half-loaded window, and both would rather have a late complete window than a punctual partial one.

What one row is

One batch window, containing one file or set of files, containing rows as the source presented them during that window. The window — not the row — is the unit of everything that matters here: it is the unit of failure, of retry, of publication, of reconciliation and of deletion. If the window is not present in the path, none of those operations can be bounded (Partitioning).

The obvious build

A cron entry and a script. Every hour: connect, SELECT * FROM orders WHERE updated_at >= now() - interval '1 hour', write a file, COPY it into the warehouse. It is understandable at a glance, it requires no framework, and for a long time it is genuinely correct.

Why it breaks

The window is computed from now() at run time, so a run that starts three minutes late reads a window shifted three minutes forward and the first three minutes belong to no run at all. The gap is invisible because both runs succeeded (Incremental Extraction).

How it breaks with real data

The window is computed from now() at run time, so a run that starts three minutes late reads a window shifted three minutes forward and the first three minutes belong to no run at all. The gap is invisible because both runs succeeded (Incremental Extraction).
A run fails. The next run's window is computed from the clock, not from the last successful window, so the failed hour is skipped permanently (Ingestion Failure & Recovery).
The extract takes longer than its interval. Two runs overlap, both read overlapping ranges, and the warehouse gains duplicates — or the source gains two concurrent scans and gets slower, which makes the overlap worse (Retry Storms: The Load You Generated Yourself).
Hourly files are small. After a year there are thousands of them, and every downstream query pays listing and per-file overhead that has nothing to do with the data volume (File Size and the Small-Files Problem).
The COPY into the warehouse is not atomic. A consumer querying during the load sees a fraction of the hour and reports a dip that never happened (Atomic Publish).
Daylight-saving arrives. One hour runs twice and one never runs, and both are correct behaviour for a schedule expressed in local time.

Source→Ingestion→Raw→Transformation→Validation→Storage Model→Serving→Consumer→Observability

What is actually happening

Batch ingestion converts a continuous stream of changes into discrete, named, bounded units of work. That conversion is the whole idea: a window can be retried, reconciled, published atomically and deleted, and a continuous stream can do none of those things easily.
The window has two clocks in it and they are not the same. The *processing window* is when the job ran; the *data window* is which records the predicate selected. Confusing them is the source of almost every batch bug — a job that says "yesterday" and means "the twenty-four hours before I started" is at the mercy of when it started (Processing Time, Event Time).
Because the window is discrete, records that arrive after their window closed have nowhere natural to go. Batch does not eliminate late data; it makes late data into a class of record that requires a decision — drop, place in the current window, or reprocess the old one (Late-Arriving Data).
The load into the warehouse is a second, separate transaction from the extract. Anything that treats extract-plus-load as one operation is assuming an atomicity that does not exist unless a manifest or a staging-then-swap makes it exist.
Simplicity is not an accident of batch; it is structural. There is no long-lived state to manage, no consumer group to rebalance, no watermark to advance, no backpressure to handle. That is a real engineering property and it is the strongest argument for batch, not a consolation prize (Batch vs Streaming Ingestion).

The window is the unit of everything

GENERALThe SQL is illustrative of the predicate shape only; the important structure is in the comments — parameterised window, window-derived path, conditional bookmark advance after durability. All three hold whether the extract is SQL, an API walk or a file listing.

Batch ingestion is often described as "reading the new rows", which puts the emphasis in the wrong place. What batch actually produces is a sequence of named, bounded windows, and every operation you will ever want to perform on ingested data is expressed in terms of one: retry this window, reconcile this window, delete this window, reprocess everything after this window.

That is why the window must appear in three places at once: as a parameter to the job, as a component of the path the data lands at, and as a row in whatever tracks completion. When all three agree, a re-run is idempotent for free. When the window lives only in the scheduler's head, every repair becomes a bespoke operation performed under pressure.

The code below is deliberately tool-free. Its properties come from the shape of the arguments, not from a framework: nothing reads the clock, the output path is a pure function of the window, and the bookmark is written after the data is durable.

A window extract that is safe to re-run

1-- Called with an explicit half-open window. Nothing here reads now().
2-- [win_start, win_end) — half-open, so a row on the boundary belongs to
3-- exactly one window and can never land in both or in neither.
4 
5SELECT *
6FROM   orders
7WHERE  updated_at >= :win_start
8  AND  updated_at <  :win_end;
9 
10-- Landed at a path that is a pure function of the window, so a re-run
11-- replaces its own output instead of appending beside it:
12--   raw/orders/ingest_date=2026-03-14/ingest_hour=09/
13--
14-- Only after those files are durable:
15--   UPDATE ingest_bookmarks
16--   SET    last_complete_window_end = :win_end
17--   WHERE  source = 'orders'
18--     AND  last_complete_window_end = :win_start;  -- optimistic: refuses
19--                                                  -- to skip a window
20 
21-- And the next window is derived from the bookmark, never from the clock:
22--   SELECT last_complete_window_end                        AS win_start,
23--          last_complete_window_end + interval '1 hour'    AS win_end
24--   FROM   ingest_bookmarks
25--   WHERE  source = 'orders';

Three properties do the work. Half-open bounds make window membership total and disjoint. The path is derived from the window, so replacement is idempotency. The bookmark update is conditional on the previous value, so two concurrent runs cannot both advance it and a run cannot skip a window that was never completed.

Where the boundary cuts, and who falls in the gap

A window boundary is an arbitrary line drawn through a continuous process, and records near it behave badly. A row updated at 08:59:59.9 and committed at 09:00:00.2 has a timestamp in the 08:00 window and became visible during the 09:00 one. Whether it is ingested at all depends on details nobody wrote down.

The timeline below makes this concrete. Read eventTime as the moment the source assigned updated_at, and arrivalTime as the moment the row became visible to a reader — commit time. Batch selects on the first column and observes only the second, and the gap between them is where records disappear.

The remedy is not a better clock. It is either an overlapping window — reading a little further back than strictly needed and deduplicating on the source key — or reading from a source whose ordering *is* commit ordering, at which point the problem does not exist rather than being mitigated.

Four rows around a batch boundary

08:00 window 08:00–09:0009:00 window 09:00–10:00watermark A batch pipeline with no lookback behaves as if its watermark jumps instantly to the window end — it declares 08:00–09:00 complete the moment the run starts, which is a claim about the source it has no way to justify.

Event	Happened	Arrived	Lands in
row-A	08:12	08:12	08:00 window The ordinary case: assigned and committed well inside the window. Nothing interesting happens and this is most rows.
row-B	08:59	09:02	nothing The failure. Its `updated_at` is 08:59, so the 09:00 run's predicate excludes it — and the 08:00 run had already finished before it committed. No window will ever ask for it again.
row-C	09:04	09:04	09:00 window Normal, and shown to make the point that B and C differ only in commit lag — nothing about B looks unusual in the source.
row-D	08:57	09:06	08:00 window (recovered) The same shape as B, but recovered because this pipeline runs the 09:00 window with a ten-minute lookback and deduplicates on the primary key. The lookback is the entire difference.

Clock labels on a teaching timeline, not measurements. The lesson is the relationship between the two columns: batch filters on the left and can only observe the right, so any row whose columns straddle a boundary is at risk.

Publishing a window without letting anyone see it half-built

The extract is the visible half of batch ingestion and the load is where the consumer-facing failures are. A warehouse load that appends rows one file at a time is observable at every intermediate state, and every one of those states is a real-looking period that is genuinely incomplete.

The fix is the same shape everywhere it appears: write somewhere invisible, validate, then make the whole window visible in a single operation. In a warehouse that is a partition swap or a transaction. On object storage it is a manifest or table-format commit rather than a directory that readers list (Open Table Formats).

Validating between the write and the swap is the part most often skipped, and it is what turns atomic publish from a concurrency nicety into a quality gate: the window that fails its row-count assertion is simply never published, and the consumer sees the previous state rather than a wrong one.

Window-level assertions, and their blind spots

Check	Expresses	Catches	Still misses
Window continuity — every expected window between the first and the newest exists	No period was skipped.	A failed run the schedule moved past, a DST-skipped hour, an orchestrator that dropped a missed interval.	A window that exists but was shifted — present, plausible, and covering the wrong range. Only source reconciliation sees that.
Row count for the window versus the same window on prior days	This period looks like a normal period.	Partial loads, a predicate that broke, an upstream outage, an extract truncated by a timeout.	Genuinely unusual business days, which produce false alarms; and any failure that preserves volume, which is most value-level errors.
Uniqueness on the source key within the window	The window contains each source record once.	An appended retry, two overlapping runs, a lookback window landed without deduplication.	Duplicates across adjacent windows — a row appearing once in the 08:00 window and once in the 09:00 one is unique in both.
Extract duration versus the interval	The schedule is still viable.	The approach of overlapping runs, weeks before they happen.	Nothing about the data itself; a fast extract that read the wrong range passes comfortably.

The first and third are structural — they check the shape of the batch. The second is statistical and will produce false positives, which is the price of it being the only one that can see a partial load with correct-looking metadata.

Append as you go

Each extracted file is loaded into the target table as soon as it is written. The window is complete when the last file lands. A consumer querying at any point during the load sees whatever fraction has arrived.

Stage, validate, swap

All files for the window are written to a staging location. Row count and key uniqueness are asserted against the window's expectation. Only then is the window made visible — a partition swap, a single transaction, or a manifest commit — in one operation that a reader either sees entirely or not at all.

Appending makes every intermediate state of the load queryable, and an incomplete window is indistinguishable from a quiet period — there is no signal in the data that says "still loading". Staging costs one extra write of the window and converts an unbounded number of observable wrong states into two correct ones: the window is there, or it is not. It also gives validation somewhere to stand, because a failed assertion can simply decline to publish rather than having to undo a partial load.

How to build it

Most important first.

Make the window an explicit parameter of the job, never derived from now() inside it. A job invoked as --window-start=... --window-end=... is re-runnable, backfillable and testable; a job that reads the clock is none of those. Express those parameters in UTC and reason about local time only at the presentation layer, or a daylight-saving transition will run one hour twice and skip another entirely (Idempotent Data Pipelines).
Drive the next window from the last *successfully completed* window, not from the schedule. Then a failed run leaves a window that the next run naturally picks up, and catching up is the default behaviour rather than a manual operation (The High-Water Mark).
Write to a path containing the window, and make a re-run replace that path rather than append to it. This single convention makes the job idempotent without any deduplication logic (The Raw Landing Zone).
Publish atomically: load into a staging location, validate, then make the window visible in one operation — a partition swap, a manifest append, a directory rename (Atomic Publish).
Guard against overlap. One run per source at a time, with a lock, so a slow run cannot be joined by its successor. Concurrency here creates duplicates and source load simultaneously.
Right-size the window against file size rather than against a desire for freshness. An interval that produces files far below the format's efficient read size is buying freshness nobody asked for and paying for it in every downstream query (File Compaction).

What this actually promises

Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.

Completeness per window: only what the predicate captured, which for a timestamp predicate is an approximation of what happened. The window boundary is exact; the mapping from records to windows is not (Incremental Extraction).
Delivery: at-least-once. A retried window re-reads the whole range, so a design that appends produces duplicates and a design that replaces the window's path does not.
Ordering: none within a window, and only window-level ordering between them. Downstream code that needs per-entity ordering must get it from a source-provided column (CDC Ordering and Transaction Boundaries).
Atomicity: per window if you publish atomically, otherwise per file, which is not a meaningful unit to any consumer.
Freshness: bounded below by the interval. A consumer reading immediately before a run sees data one full interval old, and no amount of pipeline reliability changes that.

Can I trust it?

A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.

The check that would catch this

Per window: assert the file or row count is non-zero where non-zero is expected, assert uniqueness on the source key within the window, and reconcile the window's count against the source for the same closed range.
Assert window continuity — that every expected window between the first and the newest exists. A missing window in the middle of a sequence is the failure this check exists for, and nothing else in a batch pipeline looks for it (Freshness Checks).
The continuity check misses a window that exists and is empty for the wrong reason, which is why it should be paired with a volume comparison against the same window on previous days rather than against a fixed threshold (Volume Anomalies).
None of these can tell you the window boundary itself was correct. A run that shifted its window by three minutes produces present, unique, plausibly-sized files with a hole between them, and only source reconciliation finds it.

Freshness

The shape is a sawtooth with amplitude equal to the interval. Staleness climbs from near zero to a full interval and drops. Any single number quoted to a consumer — average, median — describes a moment they may not be reading at.
The end-to-end staleness a dashboard shows is the ingestion interval plus the transformation interval plus the BI cache, and each of those adds rather than overlaps. A "fifteen-minute" ingest feeding a nightly model gives a nightly dashboard.
Shortening the interval buys freshness sub-linearly and costs file count linearly. Past a point the extra queries against the source and the extra small files cost more than the freshness is worth (Cost vs Freshness).
What batch cannot give at any interval is *per-record* latency. Every record waits for its window to close, so the newest record in a window is fresh and the oldest is a full interval old, regardless of how urgent it was.

When the schema or meaning changes

A source schema change lands inside a window, so history is heterogeneous by window. Storing raw in a format that carries its own schema makes that recoverable; storing it as headerless text makes it a forensic exercise (Avro).
Changing the interval changes what one window contains, and any downstream logic that assumed windows were comparable units — "rows per window" as a health metric, for instance — silently changes meaning at the cutover.
Changing the predicate column, from created_at to updated_at say, changes which records appear in which window and makes periods before and after the change non-comparable without a note (Semantic Changes).
Adding a lookback overlap to fix late commits changes the duplicate profile of every window. Downstream must already be deduplicating on the source key before that change is safe (Deduplication).

How to re-run this safely

Recovery is re-running a window, which is safe exactly when the window is a parameter and the write replaces the window's output. Both are cheap to build on day one and expensive to retrofit under incident pressure.
Catching up after an outage means running many windows, which multiplies load on the source at the moment it may still be fragile. Cap concurrency during catch-up and prefer slower recovery to a second outage (Without Jitter, Every Client That Failed Together Retries Together).
Backfilling a range older than the source retains is not recovery, it is a different operation with different semantics — you are writing today's view of old rows into a historical window. It may be the best available answer; it is never the same answer (What Backfills Break).
Recovering a window that has already been transformed downstream requires reprocessing everything derived from it. Landing raw by window makes that a bounded operation rather than a full rebuild (Reprocessing vs Retrying).

What can go wrong

Failure modes

Window derived from now() at run time, so late starts create permanent gaps that no run ever revisits.
Overlapping runs, when an extract exceeds its interval, producing duplicates and doubling source load at exactly the wrong moment.
Non-atomic load, letting consumers observe a partially loaded window as a genuine dip.
Small-file accumulation, where the ingestion is fine and every query downstream slowly becomes worse (File Size and the Small-Files Problem).
The mitigation fails: a lock intended to prevent overlap is held by a process that died, so ingestion stops entirely and quietly instead of overlapping — a different failure, equally silent.
Schedule expressed in local time, so a DST transition skips or repeats an hour and both behaviours are the scheduler working correctly.

Misreads

"Batch is the legacy option." Batch is the correct option whenever the consumer's decision cadence is slower than the interval, which covers most analytics. It is chosen for the operational properties, not settled for (Batch vs Streaming Ingestion).
"Running more often makes the data fresher." It makes the *newest* record fresher and does nothing for the freshness floor a consumer experiences if the transformation downstream still runs nightly. Freshness is end-to-end or it is marketing.
"The window is yesterday." Say which clock. Yesterday by event time, by the source's updated_at, by arrival, and by the scheduler's local time are four different sets of rows, and they differ most at exactly the boundary people check.
"We re-ran the job, so the gap is filled." Only if the job's window is a parameter. Re-running a job that computes its window from now() re-processes the present and leaves the gap exactly where it was.
"Duplicates from a retry will wash out." They will not. They persist in every aggregate built on that window until someone deduplicates, and the sum is wrong in a direction nobody questions (Duplicate Rows).

Operating it

How you see it in production

Window completion status as a grid — one cell per expected window — rather than a task success rate. A hole in a grid is visible instantly; an aggregate success rate is not (Pipeline Observability).
Extract duration against the interval, on the same axis. The moment duration approaches interval, overlap is coming, and that is a leading indicator with weeks of warning (Saturation: The Reading Utilization Cannot Give You).
Rows per window compared with the same window on previous days, which normalises out daily and weekly seasonality that a fixed threshold cannot.
Files written and average file size per window, because the file-size problem is invisible in ingestion metrics and appears months later as query cost (Scan Cost).

What changes at 10x and 100x

At 10x volume the extract duration approaches the interval and the design must change: narrow the predicate, add an index on the predicate column at the source, split by key range, or move to log-based capture.
At 100x, a single-threaded extract is not viable and the window must be partitioned — by key range or by sub-interval — which introduces per-chunk completion tracking, because a window is now complete only when all its chunks are.
Interval shortening scales file count and orchestration overhead linearly while scaling data volume not at all. A platform ingesting every minute from two hundred tables is running nearly three hundred thousand tasks a day to move a modest amount of data (When a Task Fails Mid-DAG).
What does not change with scale is the correctness story: explicit windows, bookmark-driven scheduling and atomic publish are as right for two hundred rows as for two hundred million. That is genuinely the point of batch.

What drives cost here

Source query cost per run, multiplied by run frequency. Halving the interval doubles this whether or not the data volume changed.
File count, which grows with run frequency and never shrinks without compaction. It costs listing time, metadata size and per-file read overhead on every downstream query, forever (File Compaction).
Idle compute: a job that starts a cluster or a warehouse to move a small window pays startup cost per run rather than per byte, and at short intervals that dominates (Compute Waste).
Re-reading history. An extract with a generous lookback re-reads and re-writes the same rows every run; the cost scales with lookback rather than with change.

What this approach costs

Batch buys operational simplicity — no long-lived state, no rebalancing, no watermarks, restartable from a parameter — and costs a freshness floor equal to its interval and a late-data problem at every window boundary.
Explicit window parameters buy re-runnability and cost an orchestrator that tracks which windows are done, which is a real component with its own failure modes (Orchestration).
Atomic publish buys consumers who never see partial data and costs a staging copy plus a swap, which for large windows is a meaningful amount of extra write.
Longer intervals buy bigger files, lower source load and fewer tasks, and cost freshness. Shorter intervals invert all four. There is no interval that is right in general and the correct one is set by the consumer's decision latency, not by ambition.

Where this applies

Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.

GENERALExplicit windows, bookmark-driven scheduling, idempotent writes keyed on the window and atomic publish are properties of the design rather than of any tool, and apply equally to a shell script, an orchestrated DAG and a managed connector.
TOOL-SPECIFICOrchestrators differ in whether a schedule interval is passed to the task as a parameter or left for the task to compute, and in whether a missed interval is automatically backfilled or dropped. Airflow-style logical-date semantics and a plain cron entry produce different behaviour after an outage even with identical task code.
SCALE-SPECIFICBelow the point where an extract takes a meaningful fraction of its interval, none of the overlap, chunking or file-size advice applies and adding it is pure complexity. It becomes mandatory the moment extract duration and interval are within a factor of a few of each other.

Where the depth lives

This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.

Databasetransactions-and-acid isolation-levels index-types should-i-add-an-index

Backendscheduled-jobs job-idempotency backoff-and-jitter retries

Architecturebackground-jobs idempotency

Observabilitysaturation queue-backlog

Cloudobject-storage storage-lifecycle

DSAmerge-intervals

Domains that do not exist yet

— DevOps / Production Engineering owns the scheduler itself — how a missed interval is handled, how a catch-up storm is rate-limited, and how a schedule change is deployed and reverted.
— Distributed Systems owns why two concurrent runs advancing the same bookmark need a compare-and-set rather than a read-then-write, and what happens when the bookmark store and the data store are different systems.