ETL and ELT Design Patterns Questions

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 model (models/stg_orders.sql), pulling only changed rows via updated_at, deduplicating on order_id, and staying idempotent under retries:

{{ config(materialized='incremental', unique_key='order_id', incremental_strategy='merge') }}

with source as (
    select order_id, amount, updated_at
    from {{ source('raw', 'orders_raw') }}
    {% if is_incremental() %}
    where updated_at > (select coalesce(max(updated_at), '1900-01-01'::timestamp) from {{ this }})
    {% endif %}
),

deduped as (
    select *,
           row_number() over (partition by order_id order by updated_at desc) as rn
    from source
)

select order_id, amount, updated_at
from deduped
where rn = 1

Actually run (real dbt-core 1.12 against DuckDB, not simulated) across three runs:

Run 1 (initial build, orders_raw has orders 1 and 2):
  stg_orders -> (1, 100.0, 09:00), (2, 50.0, 09:05)

Run 2 (source gets an updated order 1 and a new order 3):
  new source rows: (1, 150.0, 11:00), (3, 30.0, 11:05)
  dbt run: "1 of 1 OK created sql incremental model"
  stg_orders -> (1, 150.0, 11:00), (2, 50.0, 09:05), (3, 30.0, 11:05)
  -- order 1 correctly UPDATED via merge, order 3 correctly INSERTED, order 2 untouched

Run 3 (source gets a duplicate batch for a new order 4: two rows, same order_id, different amounts):
  new source rows: (4, 5.0, 12:00), (4, 9.0, 12:01)
  stg_orders -> ..., (4, 9.0, 12:01)
  -- the later of the two duplicate rows for order 4 won, per the row_number dedup

How is_incremental() works: on the first run, the target table ({{ this }}) doesn't exist yet, so dbt compiles the model without the is_incremental() block and does a full initial build. On every subsequent run, dbt detects the table already exists and compiles WITH the block, so the source CTE is filtered down to only rows newer than the current max updated_at already in the target, which is exactly the high-water-mark pattern applied inside a dbt model.

How the model stays idempotent on retry: incremental_strategy='merge' compiles down to a MERGE statement keyed on unique_key='order_id', so re-running the model against the same unchanged source data is a no-op (nothing newer than the current watermark to pull), and even a retry that somehow re-reads an already-applied row resolves to the same MERGE semantics as the hand-written SQL MERGE pattern used elsewhere in this topic: matching rows get updated to the same values, not duplicated.

Why the dedup step matters even inside dbt: without the row_number()/deduped CTE, a source batch containing two rows for the same order_id (a legitimate scenario if the source's CDC (Change Data Capture) or extraction occasionally redelivers or the upstream table briefly had two versions in flight) would hand dbt's compiled MERGE two source rows matching the same target key, which most warehouses either reject or apply nondeterministically. The dedup CTE is what makes the model's output deterministic regardless of duplicate rows in a single incremental batch, confirmed above where run 3's duplicate order-4 rows resolved to a single, deterministic winner.

The honest framing is that CDC (Change Data Capture) is a real engineering investment (a tool, an operational surface, schema-change handling) that only pays for itself once you actually need what it uniquely gives you: near-real-time latency, and correctness for deletes. Absent both of those needs, periodic batch polling is simpler to build, operate, and reason about.

What each costs and gives you

When CDC is worth it: high-write OLTP sources feeding analytics that genuinely need sub-minute freshness (fraud detection, live operational dashboards), or any case where deletes are business-meaningful and must be reflected downstream (a customer deletion for compliance, an order cancellation that must disappear from a revenue dashboard, not just stop updating).

When periodic batch polling is the better trade: reporting and BI use cases where hourly or daily freshness is genuinely fine, deletes are rare or don't need to propagate downstream, and the team doesn't want to operate a CDC tool and a message bus for the benefit.

The hash-based fallback, concretely: when a source has neither a reliable last_updated column nor CDC access (a third-party system you don't control), computing a deterministic row-level hash and diffing it against the previously stored hash on each poll detects any changed column even without a timestamp, at the cost of a full-table scan every run, which puts real, unavoidable load on the source proportional to the table's size, not the size of the actual delta.

The decision in one sentence: reach for CDC when the business genuinely needs sub-minute freshness or delete correctness, and accept that you're taking on real operational surface area to get it; reach for polling (timestamp-based if you have a reliable watermark column, hash-based if you don't) when neither of those is actually a requirement, because the added correctness and latency of CDC has no payoff if nobody's going to notice the difference.

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.

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.