Incremental Ingestion for a Subscription Streaming Service
Fabrikam Streaming ingests a 40-million-row subscriber table nightly. A full refresh currently takes six hours and sometimes fails near completion, forcing a restart from zero. The source system can emit a modified-timestamp column, but roughly 2 percent of rows have null timestamps because of a legacy migration. The analytics team needs profiles refreshed by 6 a.m. local time, and the ingestion window overlaps with peak evening streaming traffic. The engineering lead proposes switching to incremental ingestion immediately for all rows.
1. Evaluate the proposal to move the entire table to incremental ingestion. What risks arise from the null timestamps, and how should the team handle them?
Read the worked answer
Incremental ingestion is the right direction but not as an all-rows switch, because null timestamps break the core assumption that a watermark captures every changed row.
A null modified timestamp means the row can never satisfy a 'greater than last watermark' filter. Those rows would silently stop updating after the initial load - a data correctness failure that is harder to detect than a slow refresh. Worse, if the legacy rows are updated in place without populating the timestamp, the changes are invisible.
Handle it in layers: (1) backfill or derive a timestamp for the null rows at the source or in a staging transformation, using a documented fallback such as last-known update date; (2) keep a periodic full reconciliation - for example weekly - to catch rows the watermark missed; (3) monitor row counts and a checksum or sampled comparison between source and ingested data so drift surfaces quickly.
Only after the null population is resolved or bounded should the team cut over. The 2 percent is small enough to remediate but large enough to cause visible reporting errors if ignored.
2. The six-hour window collides with peak traffic and the 6 a.m. deadline. What ingestion design choices reduce both duration and blast radius?
Read the worked answer
Separate the two problems: total duration and failure recovery.
For duration, incremental ingestion after the initial load is the primary lever - nightly changes are a fraction of 40 million rows, so the steady-state run should be far shorter than six hours. Keep the initial full load as a one-time scheduled activity outside the peak window.
For blast radius, make the load restartable rather than all-or-nothing. Structure ingestion in partitions or batches so a failure resumes from the last completed batch instead of from zero. Schedule the run so the heavy phase starts after peak streaming traffic subsides, and set the refresh schedule so the pipeline completes before 6 a.m. with buffer for retries.
Also add observability: record per-batch timing and row counts so the team can see whether duration is trending up, and alert on a run exceeding a threshold rather than discovering a failure at the deadline. If the source system throttles reads during peak hours, cap concurrency during that period and raise it afterward.
3. A stakeholder asks why the team cannot simply shorten the full-refresh schedule to run twice nightly to improve freshness. How do you respond?
Read the worked answer
Explain that doubling a six-hour full refresh is not feasible within a 24-hour cycle once failures and peak-traffic constraints are considered: two six-hour runs consume half the day and leave little room for retries, and each run competes with peak streaming load.
More importantly, full refresh does not improve the failure mode. A run that fails near completion still restarts from zero, so doubling the frequency doubles the exposure to that risk without improving recovery.
Reframe the goal: freshness comes from incremental ingestion plus a reliable schedule, not from repetition. Once incremental loads are in place, the nightly delta should complete well within the window, and if near-real-time freshness is later required, the team can evaluate a more frequent incremental cadence rather than more full refreshes.
Offer a concrete commitment: incremental cutover with partitioned restart, weekly reconciliation, and monitoring on run duration and row counts - which addresses both the 6 a.m. deadline and the reliability concern the stakeholder actually raised.