ETL and ELT Design Patterns Questions

The query, given orders(order_id INT PRIMARY KEY, updated_at TIMESTAMP, status VARCHAR, amount DECIMAL) and a stored cursor last_max_ts:

SELECT order_id, updated_at, status, amount
FROM orders
WHERE updated_at > TIMESTAMP '2026-01-01 10:00:00'   -- last_max_ts
   OR (updated_at = TIMESTAMP '2026-01-01 10:00:00' AND order_id > 1)  -- last_max_id, the tiebreak
ORDER BY updated_at, order_id;

Verified against a fixture with a row exactly on the cursor timestamp, a row just before it, and a row just after:

orders:
  (1, '2026-01-01 10:00:00', 'paid',    100.00)  -- already processed (this run's cursor)
  (2, '2026-01-01 10:00:00', 'paid',     50.00)  -- exact tie on updated_at with order 1
  (3, '2026-01-01 09:59:59', 'paid',     20.00)  -- just before the cursor
  (4, '2026-01-01 10:00:01', 'shipped',  75.00)  -- just after the cursor

With last_max_ts = '2026-01-01 10:00:00' and last_max_id = 1 (the id the previous run stopped at), the query returns exactly rows 2 and 4: order 3 is correctly excluded (before the cursor) and order 1 is correctly excluded (already processed last run), while order 2, which shares order 1's exact timestamp but was inserted after it, is correctly picked up.

Handling rows with identical timestamps: a naive WHERE updated_at > last_max_ts silently drops any row that shares the exact timestamp of the last row you processed, if that row was written to the source in the same instant but after the row that set your cursor. The fix above stores a compound cursor (timestamp, tiebreak id) and uses an OR condition so ties strictly after the recorded tiebreak id are still picked up. The tiebreak column needs to be something monotonically increasing within a tied timestamp, typically an auto-increment id or a sequence, not something arbitrary.

Handling clock skew: if the ETL job computes its own "now" to derive updated_at values, or if the source database's clock drifts relative to the machine running the extraction job, a row can be committed with a timestamp that's technically "in the past" relative to your already-advanced cursor, and it will be silently skipped forever. The standard mitigation is a safety lag: instead of setting the cursor to the maximum timestamp you just saw, set it to max(updated_at) - lag_buffer (a few minutes, sized to your worst observed clock drift), so you deliberately re-read a small overlapping window every run. This means the incremental extraction is not itself idempotent (you'll re-see a few rows every run), which is exactly why this pattern must feed into a MERGE/upsert on the load side rather than an append-only INSERT: reprocessing the same row from the overlap window has to be a safe no-op downstream.

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.

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.

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.

Streaming dedup at scale comes down to a trade-off between memory, latency, and how much false-negative/false-positive risk you can tolerate, and the right answer usually blends more than one technique.

The options

1. Exact dedup with a durable keyed state store: for every event id you've seen, keep a record in state (RocksDB-backed state in Flink/Spark Structured Streaming, or an external store like Redis). Guaranteed correct (zero false positives or negatives) but state grows unbounded unless you bound the window you're willing to remember.
2. Hash-based approaches: hash the event's content (not just its id) to dedup near-identical payloads even without a shared id field. Useful when upstream systems don't guarantee a stable id, but adds hashing compute and doesn't help if two legitimately different events happen to hash-collide (rare, but non-zero for weak hashes).
3. Bloom filters for approximate dedup: a probabilistic structure that can say "definitely not seen before" or "possibly seen before" using far less memory than exact state. The trade-off is explicit: false positives are possible (an event gets wrongly treated as a duplicate and dropped), false negatives are not (a genuine duplicate is never wrongly let through).
4. Time-windowed dedup: bound the durable state to a rolling window (say, the last 24 hours of ids), on the assumption that duplicates arriving further apart than that are rare enough to accept. This bounds memory growth for options 1 to 3 rather than being a separate technique on its own.
5. Dedup across restarts: whichever mechanism you pick, its state has to survive a job restart, either via a state backend with checkpointing (Flink/Spark's own fault-tolerant state) or by re-deriving state from a durable external store on startup, otherwise a restart silently resets your dedup window and lets through duplicates you'd already filtered.

When approximate dedup (Bloom filters) is actually acceptable
When the cost of a rare false-positive drop is genuinely low relative to the memory savings, most commonly for high-volume telemetry or engagement events feeding an aggregate metric, where losing an occasional event has negligible impact on the aggregate, versus a payments or billing pipeline, where a false-positive drop means a real transaction silently vanishes and Bloom filters are the wrong tool regardless of memory pressure.

Memory vs. latency, concretely: exact state stores cost more memory per unique key but impose essentially no extra latency per event (a state lookup). Bloom filters cost far less memory but need periodic maintenance (rotating or resizing as the filter fills up and its false-positive rate climbs), which is an operational latency/complexity cost rather than a per-event one. The realistic production pattern for high-volume, loss-tolerant streams is Bloom filter as a cheap first-pass filter, with an exact check as a fallback for anything the Bloom filter flags as "possibly seen," which gets you most of the memory savings with none of the false-positive risk.