Batch vs Streaming vs Micro-Batch

You often need to copy a table from an app database into the warehouse. This chapter uses one interview prompt: get orders from Postgres into the warehouse. The warehouse may be at most five minutes behind.

The big choice is how often to move the data. You can move it in one big run, in small runs, or one event at a time.

What you already know

• A watermark load reads only new rows. A MERGE writes them. Running it twice gives the same result.
• An orchestrator runs a job on a schedule. A daily run at 02:00 loads the day before.
• CDC turns each change into an event with a position. A stream is a feed that sends each event as it happens.
• Kafka is a tool that stores events in order. Flink handles one event at a time.
• Lag is how far the reader is behind the newest change.

What you will learn

1. Batch, micro-batch and streaming, and when each order becomes visible.
2. How to compare them by latency, cost and effort.
3. Event time, windows and watermarks.
4. What exactly-once really means.
5. How to answer the five-minute prompt.
6. What it costs to switch from batch to streaming.

Latency is the time from a change in the source until the warehouse can show it.

We use five new orders on a made-up day, 2026-01-22. All the times here are new.

Orders 110 and 111 have the same customer and amount as in the CDC chapter. Orders 112 to 114 are new too.

Here are the five orders:

Batch means one run handles a whole slice of data, then stops. The slice is a time range. A run at 11:00 can load the hour from 10:00 to 11:00.

This query is one hourly run. It reads the orders saved from 10:00 up to, but not including, 11:00:

SELECT order_id, customer_id, amount
FROM   orders
WHERE  updated_at >= '2026-01-22 10:00'
  AND  updated_at <  '2026-01-22 11:00'
ORDER  BY order_id;

All five orders become visible at 11:00. Order 110 waited 58 minutes. Order 114 waited 52 minutes.

A batch run is easy to retry. If it fails, you run the same slice again. This is the safe re-run you already know.

Micro-batch is batch with a very small slice. The job runs again and again, for example every minute. Each run is a normal batch run.

The interval is the time between two runs. Here it is five minutes, to keep the example short.

This is the run at 10:10. It reads the slice from 10:05 up to, but not including, 10:10:

SELECT order_id, customer_id, amount
FROM   orders
WHERE  updated_at >= '2026-01-22 10:05'
  AND  updated_at <  '2026-01-22 10:10'
ORDER  BY order_id;

Each run remembers where its slice ended. That is the watermark from the incremental-load chapter. Here is what each run does, ignoring run time:

• The 10:05 run loads orders 110 and 111. They wait 3 and 1 minutes.
• The 10:10 run loads orders 112, 113 and 114. They wait 4, 3 and 2 minutes.

If you use CDC, each run reads the new events from Kafka and applies them with MERGE. Some streaming engines work this way inside. Spark Structured Streaming runs micro-batches by default.

Streaming means a job that never stops. It handles each event as it arrives. It does not wait for a slice to fill.

Here is the same shop. Each order is written when its event arrives:

Each order is visible a short moment after it arrives. Most jobs write to a warehouse in small groups, every few seconds. One row at a time is too slow and costly.

The job runs all day. It keeps its position in the feed, as in CDC. If it stops, lag grows until you restart it.

A stream job can also keep state. State is a fact the job remembers between events, such as a running count. You use state in the next sections.

Lower latency costs more money and more work. This table compares the three shapes:

The effort is work for people, not only for machines. A stream job needs more watching.

Latency has several parts: the source, the feed, the wait for a run, the run, and the write. Lag is one part of latency: the part inside the CDC reader.

Event time is when the thing happened in the real world. Processing time is when your pipeline sees it.

Often they are almost the same. Sometimes they are not.

Order 113 was placed at 10:04. A slow service saved it only at 10:07. Here are the five orders with both times:

A late event is an event that arrives after events that happened later than it. Order 113 is late: order 112, placed at 10:06, arrived first.

Late events are normal. A phone can be offline, a service can be slow, or a queue can be behind. Your design must expect them.

A tumbling window is a fixed block of time with no overlap. With five-minute windows, the blocks are 10:00 up to 10:05, then 10:05 up to 10:10. An event at exactly 10:05 goes in the second block.

The table order_events has one row for each order: when it was placed (event_time) and when it arrived.

This query counts the orders in each window by event time. It uses date_bin, which Postgres 14 and later has:

SELECT date_bin('5 minutes', event_time,
                TIMESTAMP '2026-01-22 10:00') AS window_start,
       COUNT(*) AS orders
FROM   order_events
GROUP  BY 1
ORDER  BY 1;

A stream job must answer before every event has arrived. This table shows its count for each window as the events come in:

At 10:07, the late order 113 arrives. It belongs to the 10:00 window, so that old count changes from 2 to 3. The job must be able to update a window it already showed.

Now count by processing time instead. You get a different answer:

Event time answers: how many orders were placed in each window? Processing time answers: how many arrived? Use event time for business numbers.

The job cannot know if a late event is still coming. So it makes a guess. The guess is called a watermark.

A streaming watermark is the newest event time seen so far, minus a wait you choose. A window closes when the watermark reaches its end.

Here the wait is 3 minutes. Look at the 10:00 window:

Order 113 arrived at 10:07. The watermark was only 10:03, so the window was still open and counted it. At 10:08 the watermark reached 10:05, and the window closed.

The watermark moves only when a new event arrives. If no order comes, the window stays open.

This is a different thing from the watermark in an incremental load. They share a name. This table shows the difference:

A pipeline can fail and retry. So you must decide what happens to an event when something breaks. There are three promises:

• At-most-once: an event is never sent twice, but it can be lost.
• At-least-once: an event is never lost, but it can arrive twice.
• Exactly-once: each event changes the result one time.

A common choice is at-least-once, because losing an order is worse than seeing it twice. Then you make the write idempotent. That means writing the same event twice gives the same result.

Say a retry sends order 112 again. This MERGE adds an order only if its order_id is not there yet:

MERGE INTO warehouse.orders_live AS t
USING delivered AS d
  ON t.order_id = d.order_id
WHEN NOT MATCHED THEN
  INSERT (order_id, customer_id, amount)
  VALUES (d.order_id, d.customer_id, d.amount);

So "exactly-once" usually means at-least-once delivery plus an idempotent write. Some tools offer exactly-once inside their own system, if you set them up for it. Ask what the claim covers.

The prompt says: get orders from Postgres into the warehouse, at most five minutes behind. First, split the five minutes into parts. This plan is called a budget.

This budget is a plan, not a measurement:

Then answer in five steps:

1. Ask how fresh the data must be, and who reads it. Five minutes is not "real time".
2. Ask if deletes matter. If they do, use CDC. If not, a watermark query may be enough.
3. Run micro-batch every minute. It fits the budget, and a failed run is easy to run again. Write each run with MERGE on order_id, so a retry is safe.
4. Choose streaming only if you need seconds, or you must act on each event, such as to block a card payment.
5. Say how you will check it: watch the lag and the time of the newest row in the warehouse.

Streaming can also meet five minutes. But it costs more effort for a need that a one-minute run already meets.

Moving a working batch pipeline to streaming changes many parts, not one setting. This table shows what changes:

Running batch more often is a smaller step. You must check that each run still ends before the next one starts.

Build each scenario on the pipeline canvas with the chapter closed. The review tells you which requirement your design missed.