When Batch Outgrows Itself

Batch pipelines outgrow themselves in 2 ways, and the fix depends on which. The data grows until the run no longer finishes inside its schedule, a throughput problem. Or the consumer's freshness need shrinks until the schedule itself is too slow, a latency problem. Both show up as the same complaint, the dashboard is behind, and both tempt a team to rebuild as streaming. Name the problem first, then climb the smallest number of rungs that meets the consumer's tier.

A worked diagnosis

A nightly job rebuilds a 2 year curated.orders table from the full raw history every night at 01:00. 2 years ago it took 90 minutes. It now takes 5 hours 40 minutes and must finish before 07:00, when the finance team opens its dashboard. Meanwhile the operations team has asked for the same table within 1 hour so it can react to regional delivery delays. Run time growing in step with history is the throughput signal; the 1 hour request is the latency signal. The job has both problems, and the same rung fixes both.

Most of the job's work is reprocessing 730 days of orders to add 1 day's worth. Switch it to process only rows that arrived since the previous run, and a nightly run reads 1 day of rows instead of 730. Once runs are small, the schedule can tighten to hourly without touching streaming. Each hourly run handles around 170,000 rows in about 3 minutes, so worst-case latency falls from 30 hours to around 63 minutes, which the operations team accepts as its hourly tier.

INSERT INTO curated.orders
SELECT
  order_id,
  customer_id,
  city,
  amount,
  event_ts,
  ingested_ts
FROM raw.order_events
WHERE ingested_ts > (
  SELECT
    coalesce(max(ingested_ts), TIMESTAMP '1970-01-01 00:00:00')
  FROM curated.orders
)

Moving one path to micro-batch or streaming

Stop climbing when the tier is met. If the operations team later needs delays within 2 minutes, the next step is a micro-batch job on the order queue with a 60 second trigger, and only the path feeding the delay board moves. Finance keeps its hourly load: a fresher table gains it nothing and would cost 3 to 10 times more. If a fraud consumer later needs sub-second decisions, that single stateless check becomes a streaming job, while the stateful aggregates stay in micro-batch where state and recovery cost less.

Do

• Name the constraint first: a growing backlog is throughput, a waiting consumer with idle machines is latency
• Process only new rows before shortening the schedule
• Move one consumer's path up a rung, never the whole table

Avoid

• Shorten a schedule on a throughput-bound job; extra runs add overhead, not speed
• Rebuild as streaming because the dashboard is behind
• Put a stateful aggregate into a stream before pricing its state and checkpoints

A daily batch loads 20M click rows in 50 minutes for a marketing dashboard viewed at 09:00. Product wants a live board of clicks per page over the last 5 minutes, refreshed within 1 minute. Marketing's need is unchanged. Which design is the smallest that meets both?

• Replace the nightly batch with a streaming job that writes every click within 1 second, and point both dashboards at it.
• Run the existing nightly batch every 5 minutes so the product board is fresh enough.
• Keep the nightly batch for marketing; add a micro-batch job on the click queue with a 30 second trigger computing the 5 minute per-page counts, and check its processing time stays under 30 seconds.
• Keep the nightly batch and add a second nightly batch that computes per-page counts, since 2 batches are cheaper than a stream.

About this lesson

This section is part of the Batch vs Streaming: Intermediate 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.