ETL and ELT Design Patterns Questions

The pattern is: assign a rank to each row within its duplicate group ordered so the row you want to keep comes first, then filter to rank 1.

The query

SELECT event_id, user_id, event_type, event_time
FROM (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY user_id, event_type
           ORDER BY event_time DESC, event_id DESC
         ) AS rn
  FROM events
)
WHERE rn = 1
ORDER BY user_id, event_type;

Verified with a small fixture (events(event_id, user_id, event_type, event_time)):

(1, 100, 'click', '2026-01-01 10:00:00')
(2, 100, 'click', '2026-01-01 10:05:00')   -- later duplicate for the same (user, type)
(3, 100, 'view',  '2026-01-01 09:00:00')
(4, 200, 'click', '2026-01-01 10:00:00')
(5, 100, 'click', '2026-01-01 10:05:00')   -- EXACT TIE with row 2 (same user, type, time)

Running the query above returns:

event_id | user_id | event_type | event_time
5        | 100     | click      | 2026-01-01 10:05:00
3        | 100     | view       | 2026-01-01 09:00:00
4        | 200     | click      | 2026-01-01 10:00:00

Row 1 (the older click for user 100) is correctly dropped, and between the two exact ties (rows 2 and 5, identical user, type, and event_time), the query deterministically keeps row 5, because the query's ORDER BY has a second tiebreak clause (event_id DESC) after event_time DESC.

How it behaves on ties: ROW_NUMBER() requires a strict, deterministic ordering to be reproducible. If you order by event_time DESC alone and two rows tie exactly on event_time, the database is free to break the tie however it wants internally, meaning two different runs of the identical query over identical data could keep a different row each time. That's why the query above appends event_id DESC as an explicit, deterministic tiebreak.

Why it's safe to re-run: this is a pure read (a SELECT), so running it twice against unchanged data returns the identical result set every time, by construction (it's not writing anything). When this pattern feeds a MERGE (as it typically would for loading a target table), the resulting write is idempotent for the same reason: the dedup step always resolves to the same winning row given the same input and the same explicit tiebreak.

The honest answer is "it depends on where the transform has to live to do its job well," not a blanket rule. Four factors actually drive the decision in practice.

The factors

1. Compute cost and billing model. Modern cloud warehouses bill compute separately from storage and scale near-instantly, which is the single biggest reason ELT has become the default: pushing transformation into the warehouse means you're not paying for a separate always-on compute layer, and you scale transformation compute the same way you scale query compute.
2. Governance and access control. If a transformation needs to touch PII (personally identifiable information) before it's ever visible in the warehouse (masking, tokenization, filtering out fields a downstream consumer legally can't see), it has to run upstream, before load, because "load raw then mask" means the unmasked data existed in the warehouse even briefly.
3. Iteration speed and ownership. SQL-in-the-warehouse (ELT via dbt) is something analysts and analytics engineers can write and iterate on themselves. Upstream ETL usually lives in a codebase only data engineering touches, which is slower to change but keeps transformation logic close to the source system's quirks, which sometimes only the team that owns that source truly understands.
4. Coupling to the source schema. Transformations that depend heavily on source-system internals (a microservice's internal event shape, a legacy system's undocumented status codes) are easier to keep close to that source, so the person who understands the source owns the translation, rather than propagating source-specific knowledge into a shared warehouse layer everyone else has to learn.

How the answer changes by source type
For a small relational CRM table, ELT is almost always right: the data is small, the warehouse can trivially absorb the compute, and keeping the raw table around costs nothing meaningful. For a high-volume event stream (billions of rows/day), some transformation often still has to happen upstream regardless: basic validation, PII stripping, and possibly enrichment that would be prohibitively expensive to redo on every downstream query if left entirely raw. The realistic answer for a high-volume stream is usually a hybrid: light, cheap-to-skip transforms upstream (schema validation, PII masking) and everything else pushed down into ELT, where it can be reprocessed and iterated on freely.

The trap: treating this as a one-time architectural decision instead of a per-transformation one. A real pipeline typically runs BOTH patterns simultaneously, some transforms pre-load out of necessity (governance, cost at extreme volume), most transforms post-load because that's cheaper and easier to iterate on, and conflating "our pipeline uses ELT" with "every transform lives in the warehouse" is where teams get surprised by a governance or cost problem they didn't design for.

Architecting this pipeline means deliberately splitting transformation work by WHERE it needs to run, not defaulting to one pattern everywhere. The two workloads (sub-5-second personalized recommendations, and daily batch analytics) have genuinely different constraints, and the split should be visible in the design, not incidental to it.

The transform-placement decision, explicit

• Clickstream, feeding real-time recommendations (pre-load ETL, by necessity): light validation and enrichment (session stitching, basic feature computation) has to happen in the streaming path itself, before the recommendation service can use it, because ELT's "load raw, transform later in the warehouse" pattern is fundamentally too slow for a 5-second SLA (service-level agreement). This is ETL because it MUST be, not by preference.
• CDC (Change Data Capture) from transactional databases, feeding daily analytics (ELT): these changes get captured with minimal transformation (log-based CDC via Debezium, landed close to raw) and the heavy transformation, joins, aggregation, dimensional modeling, happens after landing in the warehouse, where it benefits from batch compute, is trivially reprocessable if a transform has a bug, and doesn't need to keep pace with a sub-5-second SLA.
• The clickstream ALSO feeds daily analytics, and for that path, the same raw (or lightly-enriched) events land in a data lake and get transformed in batch alongside everything else, meaning the same source data takes two different paths depending on which downstream consumer it's serving.

Architecture

flowchart LR
  CS[Clickstream 1B events/day] --> K[Kafka]
  DB[(Transactional DBs)] --> CDC[Debezium CDC] --> K
  K --> SP[Stream processor: light ETL - validate, enrich, feature-compute]
  SP --> RT[(Real-time feature store)]
  RT --> REC[Recommendation service <5s SLA]
  K --> LAKE[(Raw landing: data lake)]
  LAKE --> ELT[Batch ELT: dbt models in the warehouse]
  ELT --> DW[(Analytics warehouse)]
  DW --> BI[Daily analytics and dashboards]

Storage choices: the data lake (object storage, Parquet) is the durable raw landing zone for everything, cheap and replayable. The real-time feature store (a low-latency key-value store, not the warehouse) serves the recommendation path because a warehouse query, even a fast one, is the wrong latency class for a 5-second end-to-end budget that also has to include model inference. The analytics warehouse holds the ELT-transformed, dimensionally modeled data for BI.

Streaming vs. batch split: the recommendation path is pure streaming, no batch step in its critical path at all. The daily-analytics path is batch (scheduled dbt runs), consuming from the same raw landing zone the streaming path also reads from, so there is exactly one ingestion point (Kafka/Debezium into the lake) feeding two independently-paced downstream consumers.

Failure and recovery: the streaming path's failure mode is "recommendations get staler or fall back to a non-personalized default," recoverable by replaying from the last committed Kafka offset once the stream processor is healthy again. The batch ELT path's failure mode is "the daily analytics refresh is late," recoverable by simply rerunning the failed dbt run, since ELT models built on landed, durable raw data are naturally re-runnable without any data loss risk, which is exactly the property that justifies deferring most of the transformation work to that side of the split.

A pipeline run is idempotent if running it twice with the same input produces the same result as running it once. That matters because retries are not an edge case, they are the normal failure-recovery mechanism: a job times out and gets re-triggered, a message gets redelivered because the consumer crashed before acking it, an operator reruns a failed DAG (directed acyclic graph) task. If reprocessing the same batch can double-count rows or corrupt state, every one of those ordinary recovery paths becomes a data-corruption risk.

Two concrete techniques

1. Unique dedup key plus upsert/merge at the sink. Give every record a stable identity (a natural key, or a deterministic hash of its contents) and write with MERGE/UPSERT semantics instead of a bare INSERT. Re-applying the same record just overwrites itself with identical values; nothing accumulates.

2. A run identifier plus an idempotency check before writing. Tag each pipeline run with a unique run id (or, for append-only writes, embed a deterministic idempotency key derived from the source event), and before writing check whether that key has already been applied (a "processed batches" ledger table, or a unique constraint on the sink that makes a duplicate write a no-op or a safely-caught conflict rather than a silent double-insert).

A worked scenario: a streaming consumer reads an event, writes it to the warehouse, then crashes before it can commit its Kafka offset. On restart, the consumer re-reads the same event (at-least-once delivery is the norm for most streaming systems) and would double-write it under naive append-only INSERT logic. With an idempotency key (say, the event's own event_id) and a MERGE keyed on that id instead of INSERT, the redelivered event just re-writes the identical row, producing the exact same end state as if it had been delivered once.

Trade-offs: append-only + dedup-on-read (keep every write, filter duplicates at query time) is cheaper to write but pushes cost onto every downstream reader and doesn't bound storage growth. Merge/upsert-at-write is more expensive per write (it has to look up the existing row) but keeps the table itself clean and correct, which is usually worth it once more than one consumer reads the table.

A full load re-reads and rewrites the entire target table on every run. An incremental load reads and applies only what changed since the last run. Incremental is the default because it is almost always cheaper on both source load and warehouse compute, but a full refresh is still the right call in a few specific situations.

When a full refresh beats incremental

• The table is small enough that reading it entirely costs nothing meaningful (a few thousand rows), and the extra engineering to track a cursor isn't worth it.
• The source can't reliably tell you what changed: no reliable updated_at, no CDC (Change Data Capture) feed, and rows can be deleted without leaving any trace an incremental scan would catch.
• Historical corrections are common and hard to bound: if upstream data gets silently rewritten in ways you can't detect with a watermark, a full refresh is the only way to guarantee correctness.
• The table is genuinely small relative to the operational complexity incremental logic adds (deletion handling, backfill logic, drift between the target and source over time).

Signals that confirm an incremental load actually worked

1. Row-count delta sanity check. Compare the number of rows the incremental load touched against a rough expectation (source's own change rate, a moving average of daily deltas). A load that touched zero rows on a day you know had activity is a red flag, not a quiet success.
2. Watermark/cursor advancement. Confirm the stored high-water-mark actually moved forward after the run. A load that silently fails to update its cursor will look "successful" (job exit code 0) while re-processing the same window forever, or worse, never picking up new data if the failure is downstream of the cursor update.
3. Reconciliation against source counts. Periodically (daily or weekly), compare a full count (or checksum) from the source against the target for a bounded window, to catch drift that accumulates silently across many "successful" incremental runs, e.g. missed deletes or an off-by-one in the cursor comparison.

Pitfall worth naming: teams often treat "full load" and "incremental load" as a permanent architectural choice, when the honest answer is usually "incremental with a scheduled full-refresh reconciliation" (nightly incremental, weekly full rebuild) precisely because incremental logic silently accumulates drift that a full comparison catches and a purely incremental pipeline never will on its own.