ETL and ELT Design Patterns Questions

The MERGE, loading orders_stg(order_id, amount, last_modified, deleted) into orders(order_id PK, amount, last_modified, is_deleted):

MERGE INTO orders AS t
USING (
  SELECT order_id, amount, last_modified, deleted
  FROM (
    SELECT *, ROW_NUMBER() OVER (
      PARTITION BY order_id ORDER BY last_modified DESC
    ) AS rn
    FROM orders_stg
  )
  WHERE rn = 1
) AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s.last_modified > t.last_modified THEN
  UPDATE SET amount = s.amount, last_modified = s.last_modified, is_deleted = s.deleted
WHEN NOT MATCHED THEN
  INSERT (order_id, amount, last_modified, is_deleted)
  VALUES (s.order_id, s.amount, s.last_modified, s.deleted);

Verified end to end with a target row for order 1, and a staging batch containing an update for order 1, a stale duplicate row for order 1 with an older last_modified, a new order 2, and a new order 3 that's inserted and soft-deleted in the same batch:

before: orders = [(1, 100.00, 09:00, false)]

staging batch:
  (1, 150.00, 11:00, false)   -- real update
  (1,  90.00, 08:00, false)   -- STALE duplicate for the same key, older timestamp
  (2,  40.00, 11:05, false)   -- new
  (3,  10.00, 11:10, true)    -- new, arrives already deleted

after MERGE:
  order_id | amount | last_modified | is_deleted
  1        | 150.0  | 11:00         | False
  2        | 40.0   | 11:05         | False
  3        | 10.0   | 11:10         | True

re-running the SAME merge again: identical output (confirmed idempotent)

The inner ROW_NUMBER() window is what makes this correct rather than merely lucky: without it, the MERGE spec would try to match target row 1 against BOTH staging rows for order 1, and most engines either error on a duplicate match in the USING clause or apply them in an undefined order, which can silently leave the stale (90.00, 08:00) value as the final state instead of the correct 150.00. Deduplicating the staging batch down to one row per key, keeping the latest by last_modified, before it ever reaches the MERGE's ON clause is what guarantees a deterministic, correct result regardless of what order duplicate source rows happen to arrive in.

What makes this safe under retries: the WHEN MATCHED AND s.last_modified > t.last_modified guard means re-applying an already-applied batch is a no-op, since the incoming last_modified will no longer be greater than what's already in the target. Combined with the per-batch dedup, the MERGE produces the same end state whether it runs once or is retried after a partial failure and re-run from scratch.

What makes this safe under concurrent runs: a single MERGE statement is atomic in every warehouse that supports it (Snowflake, BigQuery, Postgres), so two concurrent MERGEs against the same target either serialize (one waits for the other's lock) or one fails with a concurrent-modification error that the caller retries, rather than interleaving in a way that corrupts a row. The dangerous version of "concurrent" is two SEPARATE INSERT/UPDATE statements instead of one MERGE, where a race between them can produce a duplicate insert; using one atomic MERGE statement is what removes that class of bug entirely.

Indexes/constraints: a unique constraint (or primary key) on order_id in the target table is what turns "the MERGE has a bug that lets two source rows both count as NOT MATCHED" into a loud constraint violation instead of a silent duplicate row, so it's not optional, it's the backstop for exactly the kind of duplicate-match scenario this dedup step exists to prevent.

In a pay-as-you-go warehouse, ELT's core promise (compute scales elastically, so push transformation down) is also exactly where its costs hide, because "elastic" means "you pay per query, and a bad query pattern compounds."

Where the costs actually show up

• Repeated full-table scans. A transform that re-scans a large source table on every run instead of filtering to what actually changed pays for the whole table's worth of compute, every single run.
• Wide, unnecessary joins inside transformation SQL. Joining more columns or tables than a given model actually needs (common when dbt models get copy-pasted and extended rather than redesigned) inflates the bytes scanned per query.
• Materializing too much, too often. Rebuilding a large table as a full table materialization every run when it could be incremental burns compute on rows that didn't change.
• Query concurrency at peak hours. Running heavy transformation jobs during the same window as interactive BI queries forces the warehouse to scale up compute (or queue queries), both of which cost money.

Concrete tactics to reduce spend without breaking service-level agreements (SLAs)

1. Materialized views and pre-aggregation for the heaviest, most-repeated aggregate queries, so you pay for the aggregation once instead of on every downstream query.
2. Clustering/partitioning keys aligned to how the data is actually filtered and joined, so the warehouse prunes what it scans instead of reading everything.
3. Right-sizing compute (warehouse size in Snowflake terms, slot reservations in BigQuery terms) to the actual workload rather than over-provisioning "to be safe," and scheduling the heaviest jobs off-peak when they don't compete with interactive queries.
4. Switching full-refresh models to incremental wherever the transformation logic allows it, since re-transforming only the delta is usually the single biggest lever.

A worked diagnostic: if a team notices costs rose after migrating pre-load ETL transforms into in-warehouse ELT SQL, the first thing to check isn't the warehouse bill line item, it's the query profile: how many bytes did the slow queries actually scan versus how many they needed to. A query that scans the full history of a table to compute yesterday's aggregate, because the model was written as a full refresh instead of incremental, is a much more common root cause than "the warehouse itself got more expensive."

Trade-off to be explicit about: every one of these tactics (materialized views, clustering, incremental models) adds engineering and maintenance complexity in exchange for lower compute spend. The right amount of optimization is proportional to how much that specific model actually costs, over-optimizing a rarely-run, cheap model is wasted effort that could go toward the genuinely expensive ones.

The design: a checkpoint table tracks which partitions are done, and each partition's update is its own transaction, so the backfill can crash and resume without re-doing completed work or leaving a partition half-updated.

CREATE TABLE backfill_checkpoint (
  event_date DATE PRIMARY KEY,
  status VARCHAR,
  rows_updated INT
);

Per-partition loop (pseudocode driving the SQL):

-- for each partition not already marked 'done':
BEGIN TRANSACTION;

UPDATE analytics_fact
SET amount_usd = round(amount * 1.10, 2)
WHERE event_date = :partition AND amount_usd IS NULL;

INSERT INTO backfill_checkpoint VALUES (:partition, 'done', :rows_updated)
ON CONFLICT (event_date) DO UPDATE SET status = 'done', rows_updated = excluded.rows_updated;

COMMIT;

Verified end to end, backfilling a derived amount_usd column across 5 date partitions:

Run 1 (crash simulated after partition 3 of 5):
  partition 2026-01-01: updated 20 rows
  partition 2026-01-02: updated 20 rows
  partition 2026-01-03: updated 20 rows
  !! simulated crash !!
  checkpoint after crash: 2026-01-01, 02, 03 all 'done'; 40 rows still NULL

Run 2 (resume, no changes to the driver code):
  partition 2026-01-04: updated 20 rows   <- correctly picked up where it left off
  partition 2026-01-05: updated 20 rows
  amount_usd remaining NULL: 0

Correctness check: 0 rows where amount_usd != round(amount * 1.10, 2)

Idempotency check: re-running the ENTIRE backfill again (checkpoint cleared to force
a full pass) updates 0 rows in every partition; sum(amount_usd) before and after
the re-run is identical.

Why this minimizes locking: each transaction's UPDATE is scoped by event_date, touching only rows in one partition, so it holds locks on a bounded slice of the table rather than the whole thing, letting concurrent reads (and writes to other partitions, if your workload has any) proceed unblocked. Batching by partition rather than backfilling row-by-row or in one giant transaction across the whole table is what turns a multi-hour table-locking operation into a sequence of short, low-contention ones.

Why it avoids duplicates: the WHERE amount_usd IS NULL guard on the UPDATE means a row that's already been backfilled (this run or a previous one) simply doesn't match the WHERE clause and isn't touched again, so re-running a partition, whether because you're resuming after a crash or deliberately re-running everything, is always a safe no-op on already-completed rows.

Why it resumes cleanly after failure: the checkpoint table is the source of truth for "what's done," checked before each partition starts, and only written after that partition's update has actually committed. A crash mid-partition leaves that partition's checkpoint row absent (not marked done), so a resume correctly retries it from scratch, while every already-committed, already-checkpointed partition is skipped.

Final cutover step: once every partition shows status = 'done' in the checkpoint table, verify the row-count and null-count invariants (as shown above: amount_usd IS NULL should be 0), then drop or repurpose the checkpoint table and treat the backfill as complete; downstream consumers reading amount_usd from this point forward see the fully-backfilled column with no special-casing needed.

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.

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.