Data Pipeline Architecture and Design Questions

Direct answer

Prefer extract-load-transform (ELT), loading raw and transforming afterward, when the target warehouse's compute is elastic and cheap relative to standing up separate transform infrastructure and you want fast iteration on business logic without redeploying a job. Prefer extract-transform-load (ETL), transforming before landing, when sensitive fields must be redacted or minimized before ever reaching the cloud, the transformation is too stateful or procedural for set-based SQL, or warehouse compute is actually the scarcer, more expensive resource in this environment. The right choice is about where compute is cheapest and where governance has to happen, not a universal best practice.

Structured elaboration

ELT's case. Raw data is preserved for reprocessing, the same decoupling benefit that motivates a bronze layer. Transformation logic lives as versioned SQL close to the consumers who read it, and warehouse compute scales elastically with demand rather than requiring a separately sized, separately operated cluster.

ETL's case. Sensitive fields can be filtered or masked before they ever leave the source environment, which matters when data residency or compliance rules constrain what can land in the cloud at all. Complex, stateful transformation logic that doesn't map cleanly to set-based SQL is often easier to express and test outside the warehouse. And if warehouse compute is priced or provisioned as the expensive resource here, keeping heavy transforms off it can be the cheaper path.

Dual-write during migration. During the transition, the ingestion path writes to both the legacy on-prem target and the new cloud warehouse for a defined window, so both systems stay populated and comparable before anything downstream has cut over. The risk is doubled write cost, and the two paths silently drifting apart if one write fails and isn't retried with identical semantics to the other.

Dual-read during migration. Consumers, or a dedicated validation job, query both the old and new systems for the same request and diff the results before trusting the new path fully. This validates correctness without dual-write's ongoing cost duplication, but it only catches divergence at query time, not continuously between queries.

Typical sequencing. Dual-write while validating that loads land correctly in both places, dual-read while validating that transformed, queryable results match, then cut consumers over to the new path alone and decommission the old system after a retention window.

Worked example

The legacy on-prem batch job transforms 200 GB nightly on already-paid-for dedicated hardware, effectively near-zero marginal cost per run. The ELT alternative lands the same 200 GB raw, then runs a warehouse transform query. Using an illustrative unit price of $5 per terabyte (TB) scanned to show the arithmetic, and assuming the transform's star-schema joins against reference tables cause roughly a 3x scan fan-out over the base 200 GB:

That ninety dollars a month is the concrete number ELT has to be compared against for whatever the dedicated ETL infrastructure would actually cost to provision and operate for equivalent throughput. If that alternative is cheaper than ninety dollars a month, ETL wins on pure compute-cost grounds; if not, ELT does, independent of any other factor like a redaction requirement, which would settle the choice regardless of the cost comparison.

Trade-offs & pitfalls

Because warehouse pricing is scan-based, ELT's cost is sensitive to query design, an unpartitioned scan or an unnecessarily wide join can blow past what an equivalent ETL job would have cost, in a way a dedicated ETL cluster's roughly fixed cost is not. Dual-write's biggest pitfall is the two paths silently diverging because they don't share identical retry and idempotency semantics, one path retries a failed write and the other doesn't, and now they disagree. A governance pitfall specific to ELT: pushing everything through ELT without column-level masking inside the warehouse means sensitive data sits raw in cloud storage even before anyone has queried it, which widens the blast radius of any breach compared to redacting before load.

Direct answer

A data lake stores raw and semi-structured data cheaply with schema-on-read, optimized for ingest scale and flexible reprocessing; a data warehouse stores curated, modeled data with schema-on-write, optimized for fast, governed, repeated SQL access. In a source-to-sink pipeline, the lake is typically where raw data lands first and stays as the reprocessing archive, while the warehouse (or a warehouse-modeled zone) is the layer BI tools and dashboards actually query.

Structured elaboration

Core distinction

How a pipeline threads them together

Ingestion lands raw events in the lake first, immutable and partitioned by arrival time, regardless of downstream shape, because that is the cheapest point to keep full fidelity and reprocess later if logic changes. A transformation stage then cleans, conforms, and models that data into the warehouse (or a warehouse-modeled zone), which is what BI and reporting query directly. The lake stays the archive and reprocessing source; the warehouse stays the fast, governed read path. Ad-hoc exploration of raw signals goes to the lake; repeated, latency-sensitive dashboard queries go to the warehouse.

The lakehouse alternative for regulated, audit-heavy settings

In a regulated environment where every read must be auditable and data cannot be duplicated across stores without tight control, a lakehouse pattern collapses the two zones into one: transactional table formats built directly on lake storage add warehouse-like properties (atomicity, consistency, isolation, durability, plus versioning and schema enforcement) to the lake itself, so there is a single lineage instead of two copies to reconcile for an audit. The trade is fewer warehouse-specific conveniences (workload isolation, some query-engine optimizations) in exchange for one authoritative copy.

Worked example

Consider a pipeline handling clickstream and change-data-capture (CDC) feeds from an operational database. Both land in the lake first as immutable, partitioned files, preserving full history even though the operational source only keeps a live snapshot. A daily transform job conforms and models that data into warehouse tables for a daily-active-user dashboard. If a support ticket needs a raw event from three weeks ago that never made it into any warehouse table, the lake still has it; the warehouse never needed to carry that raw volume at all. In a regulated variant of the same pipeline, the raw and modeled tables would instead live as transactional tables on the same lake storage, so the audit trail for "who read what, when" does not have to be reconciled across two separate systems.

Trade-offs & pitfalls

• Treating the lake as if it were queryable at BI speed is a common mistake; a lake needs a compute engine layered on top, and querying raw files directly for dashboards gets slow and expensive fast.
• Skipping the lake and loading straight into the warehouse loses reprocessing ability, forcing a re-pull from the original source if transformation logic ever needs to change.
• Warehouse compute and storage coupling can get expensive at high ingest volume; many pipelines route only aggregated, curated data to the warehouse and leave raw exploration to the lake.
• A lakehouse is not a universal upgrade. It earns its keep specifically when audit simplicity or a hard constraint against duplicating regulated data outweighs the workload-isolation and tuning conveniences a separate warehouse provides.

Direct answer

A pipeline stage is idempotent if running it again on the same input leaves the system in the same end state as running it once, with no extra side effects like duplicate rows or double-counted totals. This matters because retries and reprocessing are a normal part of running a pipeline (a job fails partway, a message gets redelivered, a backfill re-runs old data), and without idempotency each of those ordinary events risks silently corrupting downstream data.

Structured elaboration

What makes an operation idempotent

A write is idempotent when it is expressed as "set this to X" (an upsert or merge keyed by a stable identity) rather than "add X to whatever is already there" (a blind append or increment). The second form is only safe to run exactly once; the first is safe to run any number of times. Idempotency is a property of the write itself, not of the retry logic wrapped around it: retry logic decides whether to attempt again, idempotency decides whether attempting again is safe.

Where the stable identity comes from

The common pattern in distributed ingestion is a unique key assigned once, close to the source, whether a generated identifier, a source-provided event identifier, or a deterministic combination of the event's natural business identity and a time bucket, carried through every downstream stage. Every write then keys off that same identity, so a redelivered or replayed event lands on the same row instead of creating a new one.

Why retries and reprocessing make this non-optional

Retries happen because any network call in a distributed system will occasionally redeliver a message the consumer already processed; that is ordinary operation, not a bug, so every retry is a chance to double-count if the write is not idempotent. Reprocessing and backfills are standard practice (fixing a bug, adding a derived column); without idempotency, re-running a job over a period that already has data corrupts it instead of correcting it. Without this property, operators end up avoiding retries and backfills out of fear, which is worse: it blocks the very bug fixes and failure recovery the pipeline needs.

Worked example

A nightly job loads sales records keyed by order identifier and event date. If the job fails after writing half the day's records and is simply re-run, a blind append would create duplicate rows for that half; an upsert keyed by order identifier and event date instead overwrites those same rows with identical values, so running the job twice looks exactly like running it once.

Trade-offs & pitfalls

• Assuming retries are rare enough to skip idempotent design is risky precisely because the failure only shows up once, silently, and bad data is already downstream by the time anyone notices.
• Deduplicating on a payload hash instead of a stable business identity fails when a payload legitimately changes between attempts (a corrected field): the same conceptual event gets treated as new.
• Idempotent writes (upserts, keyed merges) usually cost a little more per write than blind appends, since the store has to check for an existing key first; that overhead is the price of retry-safety and is almost always worth paying compared to the cost of finding and fixing silent duplication later.
• Making the retry policy careful (backoff, limited attempts) while leaving the underlying write non-idempotent is a common wrong turn; the retry policy is not what makes reprocessing safe, the shape of the write is.

Direct answer

Detect skew by measuring the actual size, row count or bytes, distribution across partitions, rather than assuming the key is evenly distributed. Fix it without a full re-architecture by decoupling the logical key from the physical partition, most commonly by salting the hot keys (appending a bounded hash-based suffix so a single hot key's rows spread across several physical partitions) and recombining in a second pass.

Structured elaboration

Detection: track per-partition row count or byte size over time, a simple metadata query or a scrape of the processing engine's own job metrics, not a new system. A healthy key distribution looks roughly even; skew shows up as a small number of partitions holding a disproportionate share of the total, and as a proxy, uneven shuffle size or task duration per partition in the engine's own metrics, a few tasks doing far more work than the median task, which is a relative-size signal, not a wall-clock claim.

Fix options without a full re-architecture, roughly in order of how invasive they are:

1. Salting: append a bounded suffix, for example hash(key) modulo K, to the hot keys only, splitting one hot partition into K sub-partitions. Requires a second aggregation pass to recombine results across the salted sub-keys; adds a small fixed overhead to the keys you salt to fix the few that were actually hot.
2. Isolate the hot keys: route known hot keys to their own dedicated overflow partition(s) while everything else keeps the original scheme. Cheaper than salting every key, but requires knowing which keys are hot ahead of time, or detecting them online.
3. Pre-aggregate before the skewed operation: combine or reduce data locally before the shuffle that groups by the hot key, so the skewed stage moves less data even though the key distribution itself is unchanged.
4. Increase parallelism or resources for the affected stage only: a stopgap that buys time without touching the key at all, useful while deciding among the options above.

Streaming-state variant: the same skew shows up differently in a stateful stream processor. Instead of an uneven batch partition, a single hot key's keyed state (one very active entity's running aggregation) can overwhelm the single task instance responsible for that key, since keyed state pins a key to one task for correctness. The fix follows the same idea as salting: split the hot key into N sub-keys for the stateful operator, maintain N partial-state instances, and merge them in a downstream combining step, rather than giving the one overloaded task more resources, which does not rebalance state ownership at all.

Worked example

A table has 1,000 partitions and 200 million total rows. If the key were uniform, each partition would hold roughly:

$200{,}000{,}000 / 1{,}000 = 200{,}000 \text{ rows}$

Observed reality: the single largest partition holds 40 million rows, while most others sit near the 200,000 baseline. That is:

$40{,}000{,}000 / 200{,}000 = 200\times \text{ the expected size}$

a clear skew signal without needing a formal statistical test. Salting that one hot key into 200 sub-keys would bring each resulting sub-partition down to roughly:

$40{,}000{,}000 / 200 = 200{,}000 \text{ rows}$

back in line with the rest of the table.

Trade-offs & pitfalls

Salting every key uniformly, instead of just the hot ones, adds a recombination step to every query even though only a few keys actually needed it. Picking the salt factor too small still leaves meaningful skew; too large adds unnecessary recombination overhead, so the factor should be sized to the measured skew ratio, not chosen as a round number. Isolating hot keys requires ongoing detection, since which keys are hot can shift over time, a one-time fix can silently stop working as the workload changes. None of these options fix a genuinely adversarial or naturally power-law-distributed key long-term; they buy headroom. If skew keeps recurring on new keys despite these fixes, a real re-architecture, a different key entirely, is still the right call.

Direct answer

Bound the retries with a capped exponential backoff and jitter (for example, 5 attempts, delay doubling each time up to a ceiling), retry only failures classified as transient, and once retries are exhausted route the record to a dead-letter queue (DLQ, a separate holding queue for records a pipeline could not process) with full failure context rather than dropping it or blocking the whole run.

Structured elaboration

Classify before retrying. Not every failure deserves a retry. Split errors into transient (timeouts, connection resets, rate-limit responses, 5xx-class server errors) and permanent (malformed request, authentication failure, a business-rule rejection from the API itself). Only the transient class should ever be retried; retrying a permanent failure wastes time and delays the record's arrival in the DLQ for no benefit.

Shape the backoff deliberately.

• Exponential growth (each retry waits roughly twice as long as the last) spreads retries out instead of hammering an already-struggling dependency at a fixed interval.
• Jitter (randomizing the delay slightly) prevents every failed record from retrying in lockstep and re-creating the same burst of load that caused the failure.
• A ceiling on the maximum delay keeps a single record's retry sequence from silently blowing past whatever latency the pipeline's service-level agreement (SLA, the commitment on how fresh downstream data must be) allows.
• A hard cap on attempt count guarantees every record eventually resolves one way or the other, rather than retrying forever.

Make retries safe, not just present. A retry is only safe if repeating the call cannot cause harm beyond the first successful application, meaning the call needs to be idempotent (applying it more than once has the same effect as applying it once), either because the operation is naturally read-only or because a stable idempotency key lets the receiving side recognize and ignore a duplicate write.

Protect the dependency, not just the record. If a meaningful fraction of calls to the same API start failing within a short window, that's a signal the dependency itself is down, not that this one record is unlucky. A circuit breaker (a mechanism that stops sending calls to a dependency once its failure rate crosses a threshold, and periodically tests whether it has recovered) avoids retrying every in-flight record individually into a dependency that isn't coming back soon.

Decide where the retry actually lives. Retrying should happen at the queue or consumer boundary (re-delivering the message after a delay) rather than as a tight loop inside the worker that's handling the record, so the worker stays free to process other records while one waits out its backoff.

What happens after the last retry. The record is never silently dropped. It goes to the DLQ carrying the original payload, the specific error, a retry count, and timestamps of the first and last attempts, so a human or an automated remediation step has everything needed to fix and safely replay it later.

Worked example

Take a policy of base delay 1 second, backoff factor 2, a 60 second ceiling, and a maximum of 5 attempts. The delay before attempt $n$ is:

None of these hit the 60 second ceiling yet, so the worst-case wall time from first failure to landing in the DLQ (ignoring jitter and the API's own response time) is the sum of the delays before each retry:

With jitter of plus-or-minus 20 percent applied to each delay, the same worst case falls somewhere between:

So a record that is genuinely unrecoverable still takes roughly 25 to 37 seconds to reach the DLQ under this policy, a number the team can check against the pipeline's actual freshness requirement to decide if 5 attempts and a 60 second ceiling are the right choice, or too slow, or too aggressive.

flowchart LR
    A[Pipeline stage] --> B[Call third-party API]
    B -->|success| C[Continue downstream]
    B -->|transient error| D[Backoff retry queue]
    D -->|attempts remain| B
    D -->|attempts exhausted| E[Dead-letter queue]
    B -->|permanent error| E
    E --> F[Remediation]
    F --> G[Replay, new attempt]
    G --> B

Trade-offs & pitfalls

• Aggressive retry counts or a flat, non-exponential interval can amplify load on a dependency that is already struggling; jitter and a circuit breaker exist specifically to avoid turning a retry policy into a self-inflicted denial-of-service.
• Retrying failures indiscriminately, without classifying transient versus permanent, burns the entire retry budget on errors that will never succeed and only delays the record's arrival at the DLQ.
• Idempotency is the load-bearing assumption of the whole design: skip it, and retries turn into duplicate side effects (a record processed twice, a write applied twice) instead of a safety mechanism.
• A DLQ with no remediation and replay path is just a slower, more expensive way to drop data; the retry design isn't complete until there's a way to get a corrected record back into the flow.
• A common wrong turn is implementing the retry as a blocking loop inside the worker handling the record, which stalls that worker (and everything queued behind it) for the full backoff duration instead of freeing it to process other records.