ETL and ELT Design Patterns Questions

Change Data Capture is the practice of identifying exactly which rows changed (and how) in a source system, so a downstream consumer only has to process the delta instead of re-reading everything. There are three common ways to implement it, and they trade off latency, load on the source, and correctness very differently.

When each is the right call

• Log-based is the right default for a high-write OLTP database feeding near-real-time analytics: it doesn't add load to the source and it's the only one of the three that captures deletes and preserves transaction ordering. Debezium is the standard open-source tool here (into Kafka, then a sink connector or stream processor).
• Trigger-based shows up when you don't have log access (a managed database that doesn't expose its replication log, or a source you don't control at the infrastructure level) but do have DDL permission to add triggers.
• Timestamp polling is the pragmatic choice when the source has a reliable updated_at, deletes are rare or handled another way (a soft-delete flag), and near-real-time latency isn't required: it's the cheapest to build and operate, at the cost of missing hard deletes and adding poll-interval latency.

The trap: teams often reach for log-based CDC (Change Data Capture) by default because it's "the correct one," without checking whether they actually need deletes or sub-minute latency. Trigger-based and timestamp-based CDC are legitimate production patterns, not just fallbacks, when their specific correctness gaps don't matter for the use case.

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 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.

SQL-in-the-warehouse (dbt) is the right default until the transformation needs something SQL genuinely can't express well or needs compute the warehouse can't give you economically. Both of those limits are concrete, not vibes.

Where dbt/SQL wins

• Anything expressible as joins, aggregations, and window functions, which covers the large majority of analytics transformations.
• Fast iteration: an analytics engineer edits a model, runs dbt run against a small sample, and sees results in seconds, with tests and documentation attached to the same file.
• Cost efficiency for warehouse-native compute: you're not paying for a separate cluster, you're using compute you're already paying the warehouse for.

Where Spark (or another general-purpose engine) becomes necessary

• Complex, imperative logic that SQL expresses awkwardly: iterative algorithms, custom state machines, or business logic that's naturally a sequence of conditional steps rather than a set-based query.
• Feature engineering for ML at scale: many feature transformations (sliding-window computations with complex conditional logic, joins against very large reference datasets with custom matching logic) are more naturally expressed and more efficiently executed in a DataFrame API with fine-grained control over partitioning and caching.
• Custom UDFs with non-SQL logic: calling out to a Python library (a geocoding library, a specialized parser, a model inference call) inside a transformation is straightforward in Spark and either impossible or a serious performance anti-pattern in most warehouse SQL engines.
• Data volumes or shapes SQL engines handle poorly: deeply nested, irregular semi-structured data at large scale, or workloads that need fine-grained control over partitioning and shuffle behavior a SQL optimizer won't give you.

The trade-off: Spark buys you expressiveness and low-level control at the cost of developer productivity (writing and testing a Spark job is slower iteration than writing and testing a dbt model) and operational complexity (you're now managing a cluster, not just SQL against a warehouse you already operate). The decision isn't "SQL vs Spark" as a permanent architectural choice, it's per-transformation: a data platform commonly runs both, dbt for the bulk of standard analytics transformations, Spark for the specific jobs that genuinely need it (typically ML feature pipelines and complex semi-structured processing).

A concrete rule of thumb: if you find yourself trying to express something in SQL that would be a five-line Python function with a for-loop and conditionals, that's usually the signal you've hit dbt's ceiling for that specific transformation, not a sign you're writing bad SQL.

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.