Data Pipeline Architecture and Design Questions

Direct answer

A dead-letter queue (DLQ) is a separate holding queue where a pipeline puts a record it couldn't successfully process, instead of blocking the whole run on it, silently dropping it, or crashing. It solves the problem of a pipeline needing to keep moving for the overwhelming majority of records that are fine, without losing the small number that aren't.

Structured elaboration

Why this is needed at all. Without somewhere to put a bad record, a processing stage has only two options when it hits one: stop everything until a human intervenes, which halts every good record queued behind it too, or silently skip and drop the bad one, losing it without a trace. Both are worse than isolating it.

What actually goes into it. Not just the failed record itself, but enough context to act on it later: the original payload, the reason it failed, when it failed, and how many times it was tried before landing here.

What a dead-letter queue is not. It is not a fix, and it is not the same as an error log. A queue nobody ever reads or reprocesses is functionally the same as silently dropping records, just with extra storage cost attached.

What has to exist alongside it for it to actually work. A way to notice it's growing, so a genuine upstream break doesn't go unnoticed for days, and a way to get corrected records back into the main flow. Without both, isolating the failure doesn't actually solve anything, it just relocates the problem.

Worked example

A pipeline processes 10,000 records in a run, and 7 of them fail parsing because of a malformed field:

Without a dead-letter queue, the pipeline either stops the entire run of 10,000 records for those 7, or silently proceeds with the other 9,993 and nobody ever learns the 7 existed. With a dead-letter queue, the 9,993 good records finish normally, and the 7 are isolated along with their failure reasons, so someone can look at exactly those 7 without slowing down the other 99.93 percent of the batch.

Trade-offs & pitfalls

• A dead-letter queue with no monitoring on its growth rate can hide a real, ongoing upstream problem for a long time; a slow trickle and a growing crisis look identical if nobody's watching the rate.
• A dead-letter queue with no path back into the main flow is a graveyard, not a safety valve; isolating failures only pays off if corrected records can actually rejoin the pipeline.
• Using it as a catch-all for any exception, including genuine bugs in the pipeline's own logic, can mask defects that should fail loudly during development instead of quietly accumulating in production.
• It's built for isolating a minority of individually bad records; a systemic failure hitting most or all records calls for stopping the pipeline entirely, not queuing everything that comes through.

Direct answer

For a table loaded daily where most queries filter by time, partition by date; only add a secondary key (a hybrid scheme) once a large share of the expensive queries also filter or join on that key at high selectivity. Partitioning by a high-cardinality key like a user identifier alone, instead of date, breaks the thing a daily load needs most: a clean, cheap way to append "yesterday's data" as one new partition.

Structured elaboration

The decision criteria are what the daily load pattern actually needs (a clean new unit to append) and what the majority of expensive downstream queries filter or join on. If the workload is overwhelmingly time-range, do not add the secondary dimension speculatively. If user-level joins are frequent and expensive, add clustering or bucketing on that key inside the date partition rather than replacing the date partition outright.

Worked example

A fact table ingests 20 million rows/day, retained for 2 years:

$730 \times 20{,}000{,}000 = 14{,}600{,}000{,}000 \text{ rows total}$

Partitioned by date, a 30-day trend query scans only the partitions in that range:

$30 \times 20{,}000{,}000 = 600{,}000{,}000 \text{ rows}$

which is about:

$600{,}000{,}000 / 14{,}600{,}000{,}000 \approx 4.1\% \text{ of the table}$

If the same table were partitioned only by a user identifier (say, 500 buckets) instead, that same 30-day trend query has no time-based pruning at all, and has to scan across all 500 buckets' full history, effectively the entire 14.6 billion rows, because "the last 30 days" is not a property that partitioning scheme exposes to the query planner.

Trade-offs & pitfalls

Picking a high-cardinality key as the sole partition scheme breaks daily-append ergonomics and date-based retention, the two things a daily-loaded table usually needs most, in exchange for a join benefit that clustering could have delivered without that cost. Picking date alone when the workload is genuinely dominated by expensive user-level joins leaves real, measurable query cost on the table. Adding a hybrid scheme "for safety" without evidence of the join workload adds ongoing maintenance cost for no measured benefit; the decision should follow from which queries are actually expensive, not from habit or precaution.

Direct answer

Records that fail parsing or validation go to a dead-letter queue (DLQ), a separate durable holding area, tagged with the original payload, the specific error, and a stable identifier tying them back to their place in the source. Correction happens outside the main flow (automated repair for known-fixable patterns, manual remediation for the rest), and replay pushes the corrected record back through the exact same validation gate it failed under a new attempt identifier, so it either passes cleanly or fails again visibly. Nothing is ever silently patched and slipped back in.

Structured elaboration

Put the gate as early as possible. Validation belongs at the ingestion boundary, before a record reaches any join, transform, or aggregation stage. Catching a bad record before it's mixed into shared state is cheap; unwinding an aggregate it already corrupted is not.

Parsing failures and validation failures are different problems. A parsing failure (malformed structure, broken encoding) usually points at a producer bug and is rarely safe to auto-fix. A validation failure (a missing required field, a value out of range, a reference that doesn't resolve) is often fixable given a bit more context, which is why it's worth routing these two differently rather than treating every failure the same way.

What has to travel with a record into the DLQ. The raw original payload, the specific rule or field that failed, a timestamp, a stable identifier that preserves its position in the source stream, and a retry or attempt counter. Without this, remediation is guesswork.

Two correction paths, not one. A known, safe failure pattern (for example, a schema version the pipeline hasn't rolled out support for yet) can go through an automated repair-and-resubmit step with no human involved. Everything else goes to a remediation surface where a person can see the failure reason and the payload, fix or annotate it, and mark it ready to go back through.

Replay through the same gate, under a new identity. A corrected record is resubmitted through the identical validation logic it originally failed, not around it, so a bad fix still gets caught. It carries a new attempt identifier rather than overwriting the original entry, preserving an audit trail of what failed, what changed, and when it was retried.

Watch the queue itself, not just individual failures. A DLQ that only ever grows is itself a signal. The right response to a small, steady trickle (normal background noise) is different from the right response to a sudden spike (usually a systemic break, like an upstream schema change), and the pipeline needs a way to tell those apart rather than routing an ever-growing stream to a queue nobody is watching.

Worked example

Say the pipeline ingests 2,400,000 records a day, and historically 0.25 percent fail validation:

Spread evenly across 24 hours, that's a baseline arrival rate of:

Now suppose an upstream schema change causes 8 percent of one hour's arriving records (100,000 that hour) to fail validation:

Compared to the 250-per-hour baseline, that's:

If the alerting rule fires whenever the DLQ arrival rate exceeds 5 times the trailing baseline, this spike (32 times baseline) triggers it clearly, which is exactly the signal that should prompt someone to check for a systemic cause, like a schema change, instead of assuming it's more of the same background trickle.

flowchart LR
    A[Incoming record] --> B[Validation gate]
    B -->|passes| C[Continue through pipeline]
    B -->|fails| D[Dead-letter queue]
    D --> E[Automated repair, known patterns]
    D --> F[Manual remediation]
    E --> G[Replay, new attempt id]
    F --> G
    G --> B

Trade-offs & pitfalls

• A DLQ with no remediation and replay loop is a write-only graveyard; it's no better than silently dropping the records, just more expensive to store.
• Reprocessing a corrected record under its original identity, instead of a new attempt identifier, erases the audit trail of "this failed once, here was the original data."
• Auto-retrying validation failures that are genuinely permanent (a business-rule violation, not a transient glitch) wastes effort on records that will fail identically every time until the rule or the data changes.
• Placing the validation gate after joins or aggregation means a bad record has already corrupted shared downstream state before anyone catches it; moving the gate earlier is nearly free, unwinding a corrupted aggregate is not.
• Treating every spike in DLQ volume as "more of the same" instead of a signal worth investigating is how a schema break goes unnoticed for hours.

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.

Direct answer

True end-to-end exactly-once isn't available when the sink can't join the stream processor in a distributed transaction. What you can actually get is "effectively-once": every write becomes a deterministic upsert keyed on the aggregation's logical identity (its group key plus window boundaries) combined with a monotonic version or offset check, so a redelivered update converges to the same final state instead of double-applying. What you give up is atomicity between consuming an event and writing its result, and any guarantee that covers side effects beyond that one row.

Structured elaboration

Why a distributed transaction isn't on the table. A two-phase commit across a message broker and an arbitrary external database requires the database to participate as a transactional resource with the stream processor. Most operational databases don't expose that to an outside system, so "I consumed this event" and "I wrote this result" are two separate operations on two separate systems that cannot fail or succeed together atomically.

What idempotent (safe to apply the same operation more than once without changing the result beyond the first application) writes actually require:

• A deterministic key derived from the aggregation's logical group and window boundaries, not an auto-generated identifier, so reprocessing the same window always targets the same row instead of creating a new one.
• A conditional apply: the write only takes effect if the incoming update is newer than what's already stored, checked against a monotonic marker like the source offset or event sequence number, never a wall-clock timestamp, since redelivery can legitimately arrive out of order.
• At-least-once delivery (the normal guarantee from most stream processors) combined with that conditional, idempotent apply is what produces an effectively-once result in practice; the guarantee isn't native to the transport, it's built at the sink.

What you give up, explicitly:

• No atomicity between the consume step and the write step, so a crash between them means the same batch gets reprocessed; the design has to tolerate that by construction, not treat it as an edge case.
• Any side effect that isn't itself an idempotent write to this same row (an outbound notification triggered per update, for example) can still be duplicated, because the guarantee only covers the sink's own state, not everything the pipeline does per event.
• No claim to a globally consistent snapshot across keys at a single point in time, only per-key convergence once each key's events have all been applied.
• If the sink can't do a conditional or compare-and-set write at all, the fallback is a staging-then-reconciliation pattern, where updates land in a holding area and a single-writer step decides what to apply, trading throughput for correctness.

Worked example

Suppose a running aggregate for one key should total 1,245 units. A stored value of 830 receives an update contributing a delta of 415:

A producer failure causes that same update to be redelivered a second time. Applied naively (blindly adding the delta again):

With an idempotent conditional upsert, the sink stores the offset of the last applied update alongside the aggregate. The second delivery carries the same offset that's already recorded as applied, so the conditional check (incoming offset greater than stored offset) is false, the write is skipped, and the stored value stays:

Trade-offs & pitfalls

• Assuming the stream processor's own internal exactly-once checkpointing extends automatically to an external database is the most common wrong turn; that guarantee stops at the processor's own state, and the sink write needs its own independent idempotency.
• Using a wall-clock timestamp instead of a monotonic offset or sequence number for the "is this newer" check breaks under clock skew and legitimately out-of-order redelivery.
• Auto-generated keys instead of deterministic ones mean every retry creates a new row rather than converging on the same one, silently duplicating data instead of correcting it.
• Trying to solve this by wrapping more of the pipeline in a database transaction misdiagnoses the problem; the gap is between two separate systems that no single database transaction can span, not a matter of transaction scope.