Data Pipeline Architecture and Design Questions

Direct answer

Treat this as a data-quality incident, not a join bug: scope the blast radius first (every table, feature, and join that references the identifier), stop the bad data from continuing to propagate, and only then build a compatibility fix. Patching the join logic in place while the source keeps sending the new format just moves the failure somewhere else instead of resolving it.

Structured elaboration

Contain (minutes to hours):

• Scope affected downstream tables and pipelines using lineage (whatever tracks table-to-table dependencies in your stack), not just the one join that paged someone.
• Pause or reroute the source connector so new records land in a quarantine area instead of flowing straight into production joins.
• Where correctness matters more than freshness, roll consumers back to last-known-good cached data or safe feature defaults rather than serving nulls.

Restore (hours to a day):

• Build a translation layer between the old and new identifier formats: a deterministic parse-back where one exists, or a lookup table populated by a one-time sync against the source system where it does not.
• Backfill the affected window through that mapping layer, using idempotent writes so a retried backfill cannot double-write.
• Add a validation gate at ingestion that rejects type-mismatched identifiers going forward instead of letting them silently coerce to null.

Prevent (following days/weeks):

• Put a schema or contract registry in front of the external source so a future format change requires a version bump and advance notice instead of arriving unannounced.
• Add an adapter or facade layer between the external source and your canonical internal model, so the next format change is absorbed at the boundary instead of rippling into every downstream consumer.
• Define an explicit change-notification SLA (service-level agreement) with the source's owning team for anything that would break the join key's shape.

Worked example

Say customer_id moves from an integer to a UUID-shaped string. Joins keyed on customer_id do not error, they silently produce nulls on type mismatch, which is exactly why this tends to surface as "features are null" rather than a job failure. The detection signal is a join match-rate monitor: if this join historically matched roughly 98% of rows and, starting the day of the source's change, the match rate for newly arriving records drops toward near zero, that is visible immediately in a daily row-count or match-rate check. For a source sending 500,000 records/day, a drop from a 98% match rate to a 2% match rate is the difference between about 490,000 and 10,000 successfully joined rows in a single day:

$0.98 \times 500{,}000 = 490{,}000 \quad \text{versus} \quad 0.02 \times 500{,}000 = 10{,}000$

a change large enough that a basic daily monitor catches it well before a consumer has to report it.

Trade-offs & pitfalls

Patching the join to "just accept both formats" without an explicit mapping table hides the symptom instead of fixing it, and produces inconsistent keys once both formats coexist in the same table long-term. Racing to restore joins before scoping the blast radius risks joining the wrong entities entirely if the format change also changed what the identifier represents, not just its shape. Relying on informal, person-to-person coordination with the source team as the only prevention measure does not survive that person changing roles; the registry and facade layer are what make prevention durable.

Direct answer

A backfill is running a pipeline's transformation logic against a range of already-elapsed time (or already-landed data) to produce the output that should exist for that range, because it never ran, ran incorrectly, or needs to reflect a rule that did not exist when the data was first processed. It targets the past on demand, unlike the pipeline's normal incremental run, which only ever moves forward onto newly arriving data.

Structured elaboration

A backfill is forced by one of a small number of situations, and naming which one you're in changes how you'd run it:

1. Outage / gap: the pipeline simply did not run for some window (a scheduler failure, an upstream source down), so there is no output for that period at all.
2. Bug in the transform: the pipeline ran, but the logic was wrong, so a past window's output needs replacing, not filling in.
3. Definition change: a metric or feature's meaning changes (a new business rule, a corrected calculation), and history needs to reflect the new definition for comparability.
4. Late-arriving source data: upstream systems deliver records after the window that consumed them already closed and published.
5. New pipeline / dataset onboarding: a pipeline is switched on today but needs history from before it existed.
6. Upstream schema or identifier change: already-landed raw data needs reprocessing once a mapping or fix is available.
7. Disaster recovery: a downstream table was lost or corrupted and needs to be rebuilt from immutable raw sources.

The first three are the ones that actually change what the output should say (correction, redefinition); the rest are about filling in output that should already exist. That distinction matters because a correction or redefinition backfill has to reconcile with whatever already consumed the old, wrong output, while a gap-fill or historical load usually does not.

Worked example

A daily active users pipeline retains 400 days of partitioned output. A dedupe bug is found that undercounted the metric for the last 40 days only; the other 360 days were computed before the bug was introduced. The backfill only needs to touch the 40 affected date partitions, not the full 400:

$\frac{40}{400} = 0.10$

so the reprocessing cost is proportional to 10% of the retained table, not 100%, because the affected range is scoped by partition. Compare that to onboarding this same pipeline fresh today with no prior output: that backfill has to cover all 400 days, a full historical load, because there is no "already correct" portion to leave alone. The cost driver in both cases is the same: how many partitions actually need to change, not the size of the whole table.

Trade-offs & pitfalls

Scoping a backfill to only the affected partitions (using deterministic, idempotent writes keyed by partition) is what keeps the cost proportional to the actual damage; reprocessing the whole table "to be safe" trades a real, avoidable compute cost for a false sense of thoroughness. Blindly appending backfilled rows instead of overwriting the affected partitions atomically produces duplicates. The most common wrong turn is forgetting that anything materialized on top of the reprocessed table (rollups, dashboards, downstream models) also has stale output for that same window and needs its own rerun once the backfill lands, a backfill that stops at the first table it touches leaves the corruption one layer further downstream.

Direct answer

Decide by weighing the cost of a false block (delaying or dropping a probably-fine record) against the cost of a false pass (letting a genuinely bad record reach every consumer of a shared dataset), not by a single fixed rule. Checks on a hard, structural guarantee that downstream logic actually depends on, like a required key being present or a value violating a constraint the rest of the pipeline assumes, should block. Checks that are really statistical health signals, like a metric drifting outside a normal range, should let the record through and raise an alert instead. Placement follows the same logic: put blocking checks as early as possible, before the data reaches anything shared, and put statistical checks wherever there's enough context to compute a meaningful baseline, which is often necessarily later.

Structured elaboration

Decision criteria, block versus alert-and-pass:

Placement follows the same reasoning, not convenience. Hard, structural, cheap-to-evaluate checks belong as early as possible, ideally at ingestion, before a record can be joined, aggregated, or shared with anyone, because catching a violation before it's mixed into downstream state is strictly cheaper than unwinding it afterward. Statistical checks (drift, anomaly detection against a baseline) often need enough accumulated data to be meaningful, which can genuinely push them later in the pipeline; their outcome should still go to an alert rather than a block, since blocking on a probabilistic signal risks stalling the pipeline on a false positive.

A useful middle option: quarantine-and-continue. For a check that's borderline, confident enough to worry about but not confident enough to trust blindly, setting the record aside (not passed downstream, not silently dropped) while the rest of the batch proceeds avoids both a hasty block and a silent pass.

What kinds of checks tend to fall where. A null check on a required key or a duplicate-key check is usually the hard, deterministic, blocking kind. A range or distributional check on a numeric field is usually the softer, alert-worthy kind, precisely because it's inferring "unusual" rather than checking a fact the schema guarantees.

Worked example

A shared orders table feeds 12 downstream consumers. A schema check finds 0.4 percent of a day's 1,000,000 incoming records missing the required customer identifier, a value every downstream join assumes is present:

Even at a small fraction of the batch, this is treated as blocking, because those 4,000 rows would otherwise silently break joins for all 12 downstream consumers at once, and the check itself is unambiguous (the key is either present or it isn't).

Separately, a statistical check on average order value shows a shift from a baseline mean of $85 to $102:

This could be a genuine promotion-driven spike or a data problem; it goes to alert-and-pass rather than blocking, because the check is probabilistic (not every deviation is a defect) and the cost of being wrong in the blocking direction (halting real data from a legitimate business event) is worse here than the cost of a few hours where a chart looks slightly off before a human confirms it either way.

flowchart LR
    A[Raw ingestion] --> B[Hard structural checks: block on failure]
    B -->|pass| C[Transform and aggregate]
    C --> D[Statistical or drift checks: alert, let pass]
    D --> E[Shared dataset]
    E --> F[Downstream consumers]
    B -->|fail| G[Quarantine, do not propagate]

Trade-offs & pitfalls

• Blocking on every check, including soft statistical ones, trades away data availability for a false sense of safety, and will eventually stall the pipeline on a legitimate anomaly like a real traffic spike.
• Letting every check through as alert-only, including hard structural violations, means a genuinely broken record (a missing key that breaks every downstream join) propagates to all 12 consumers before anyone even reads the alert.
• Placing every check right before a report renders catches problems far too late, after the data has already been joined and aggregated into shared state that's expensive to unwind.
• A common wrong turn is picking one block-or-alert policy for the whole pipeline instead of deciding per check, based on that specific check's own confidence and blast radius.

Direct answer

Bootstrap the target with a consistent point-in-time snapshot of each source table, and hand off to the change data capture (CDC) stream at the exact log position the snapshot was taken from, so the union of "snapshotted rows" and "streamed changes from that position onward" covers every row exactly once, with no gap and no duplicate window. Keep the target schema evolvable by treating additive changes (a new nullable column) as automatic, and gating anything that changes existing meaning, renames, type changes, drops, behind an explicit migration step instead of silently applying whatever the source does next.

Structured elaboration

Snapshot-to-stream handoff. Take the snapshot inside a transaction, or using the database's consistent-snapshot mechanism, and record the exact log position (an LSN, binlog offset, or global transaction identifier) at that instant. Start the CDC stream from that same recorded position. This is the one design decision that determines correctness: pin it wrong and you get either duplicated rows (stream restarts before the snapshot's true cutoff) or silently missing rows (stream starts after it).

Ongoing incremental changes. Log-based CDC (the same mechanism used for capture generally) streams inserts, updates, and deletes keyed by the source's primary key; apply them to the target as upserts, or soft-deletes for deletions, keyed by that same primary key, so replaying or reordering events within one key stays safe.

Schema evolution policy. Additive changes (a new nullable column) propagate automatically because they cannot break anything already relying on the existing shape. Anything that changes existing meaning, a rename, a type narrowing, a column drop, is not auto-applied: the pipeline diffs the schema on each batch, halts propagation for that table when it detects an incompatible change, and requires an explicit versioned migration mapping old to new before resuming. Silently applying a rename as if it were a new column would quietly corrupt every downstream query relying on the old name.

Consistency during migration. The target should never expose a half-migrated schema to readers. A staged, versioned table swap, build the new shape alongside the old, validate it, then atomically point consumers at it, keeps every query against a single, fully-consistent version at any moment.

flowchart LR
  A[Source OLTP tables] --> B[Snapshot extractor]
  A --> C[Log tailer from pinned position]
  B --> D[Target: bootstrap load]
  C --> E[Change stream: upsert/soft-delete]
  D --> E
  E --> F[Target: steady state]
  A --> G[Schema diff check]
  G -->|additive| E
  G -->|breaking change| H[Halt + versioned migration]
  H --> E

Worked example

Forty OLTP tables need replication; the largest, orders, has 200 million rows averaging 300 bytes each pre-compression:

If the snapshot pull sustains 200 MB/s:

CDC starts buffering changes from the recorded log position the moment the snapshot begins. If the source sustains 50 writes/sec against orders during that 5-minute window:

Those 15,000 buffered events are applied, in log order, once the snapshot lands, closing the gap with nothing missed and nothing duplicated. If orders is being replicated on its own as a narrower, near-real-time single-table copy rather than as one of forty tables, the same arithmetic scopes down directly: a 60 GB bootstrap followed by roughly 15,000 buffered upserts to reach steady state, with the target consistent as soon as that buffered replay finishes.

Trade-offs & pitfalls

The single most common corruption source is taking the snapshot without pinning the exact log position, or using an isolation level that doesn't guarantee one consistent read point across the whole table, either duplicating rows if the stream restarts before the true cutoff, or dropping rows if it starts after. Auto-applying every schema change, including renames, is a shortcut that eventually corrupts a column's meaning silently, by the time it's noticed, downstream queries have been reading the wrong thing for a while. Very large tables make a single-transaction snapshot expensive and lock-heavy on the source; a chunked, parallel snapshot by primary-key range trades a slightly more complex bootstrap for not holding a long-lived lock.

Direct answer

The speed layer owns the window of data the batch layer has not yet finished reprocessing, typically the last partial day or the last few hours, while everything older than the batch layer's most recently completed run is served exclusively from batch output, because batch has already recomputed it correctly. The handoff is a watermark: once batch advances that watermark forward, the speed layer's corresponding window is dropped and superseded, not merged value-by-value, because the entire reason the speed layer existed for that window was that it had not yet been through the authoritative computation.

Structured elaboration

The speed layer's job. Give an approximate, low-latency view of data that has not yet gone through the accurate batch recompute. It tolerates being wrong in small ways, duplicate counts, out-of-order updates, because it will be superseded once batch catches up.

The batch layer's job. Perform a full, authoritative recompute over a complete time window on a schedule, correcting for late-arriving and out-of-order events and enforcing deduplication. Its output for a given period becomes authoritative the moment the run completes.

The boundary, concretely. Define a watermark $W$ equal to the timestamp through which the most recent batch run is complete. Any query for time before $W$ reads only from batch output. Any query for time at or after $W$ reads from the speed layer. When the next batch run finishes and advances $W$ forward, the speed layer's now-covered range is simply discarded and replaced by batch's output for that range, not reconciled row by row, because batch is strictly more correct for anything it has already covered.

Why no per-row reconciliation is needed. The speed layer's numbers for a given window are never "corrected" against batch; they are superseded wholesale once batch's watermark passes that window. This is what keeps the two layers from needing complex agreement logic between them.

Delivery guarantees differ by layer. The speed layer can tolerate at-least-once or approximate counting because its output is provisional by design. The batch layer needs deduplication and exactly-once-equivalent accounting because its output is what actually persists.

flowchart LR
  E[Event stream] --> Sp[Speed layer: stream processor]
  Sp --> Fv[Fast view: low-latency store]
  E --> Rs[Raw storage: append-only]
  Rs --> Bl[Batch layer: full recompute]
  Bl --> Av[Authoritative view]
  Fv --> Serve[Serving layer]
  Av --> Serve
  Bl -->|advances watermark W| Sp

Worked example

Batch runs nightly starting at 02:00, covering the prior 24 hours, and takes 3 hours to complete due to full reprocessing and joins, so its watermark $W$ only advances to "yesterday 00:00" once the run finishes around 05:00:

If a dashboard is queried at 15:00 the same day, the speed layer is the only source for everything from $W$ to now:

Everything before that 39-hour window comes from last night's batch output. Once tonight's 02:00 to 05:00 run completes, $W$ advances to "today 00:00," and the speed layer's live window shrinks back down to just the hours elapsed so far today.

Trade-offs & pitfalls

If a batch run fails or runs long, the speed layer's provisional window grows and stays uncorrected for longer, since nothing has caught up to supersede it; this is why a maximum-staleness service-level agreement (SLA) on the batch run, with alerting if it's missed, matters as much as the batch logic itself. A common wrong turn is trying to reconcile or merge speed and batch numbers cell by cell instead of a clean cutover at the watermark; that doubles the reconciliation logic for no benefit, since batch is always strictly more correct for anything it has already covered. The other well-known cost of this architecture is maintaining two independent codepaths, a streaming job and a batch job, that must implement the same business logic and never drift apart; this exact duplication is what kappa architecture removes by reprocessing history through the same streaming code instead of a separate batch pipeline.