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

A metric-definition change is a fundamentally different kind of backfill from "fix a bug that corrupted data": the old numbers weren't wrong, they were computed under a different, equally valid definition, and the plan has to preserve BOTH versions rather than simply overwriting history.

The design

1. Version the metric definition itself, not just the data. Store a metric_version (or a definition id) alongside every computed value, so "revenue under definition v1" and "revenue under definition v2" are distinguishable rows, never the same cell silently overwritten.
2. Compute the new definition over the full historical range as a genuinely separate backfill job, writing to versioned output (a new column, a new table, or the same table tagged with the new version), not in place over the old values.
3. Support side-by-side comparison before cutover. Build a reconciliation view or dashboard that shows both versions over the same historical window, so stakeholders can see exactly where and by how much the numbers diverge before the new definition becomes the default anyone sees.
4. Cut the dashboard over deliberately, with an explicit transition point, rather than silently swapping which definition a dashboard reads once the backfill finishes. A visible annotation on the dashboard ("metric definition changed on this date, see docs for what changed and why") is what prevents someone from misreading a definitional shift as a real business change.
5. Document what changed and when, including the old and new definitions in plain language and the exact date/version boundary, in a place discoverable from the dashboard itself, not just in a commit message or a Slack thread that won't exist when someone asks about this in eight months.

Why this differs from correcting a bug: fixing a genuinely wrong number (a computation error, corrupted data) has one correct target state, and the backfill's job is to converge everyone on it. A definitional change has TWO correct states depending on which definition you're asking about, and collapsing history down to only the new definition, even though it's "more correct going forward," destroys the ability to explain historical decisions that were made using the old definition, which is a real cost, not a hypothetical one.

Reprocessing considerations shared with a bug-fix backfill: minimize dashboard disruption by backfilling in the background and only flipping the default view once fully validated; verify the backfill actually reproduces the old definition's numbers exactly over the overlap period where nothing should have changed (a self-check that your new pipeline logic didn't accidentally also alter something it wasn't supposed to); and keep the backfill resumable/idempotent per the same partition-level patterns used for any large reprocessing job, since "recompute five years of history" is exactly the kind of job that can and will fail partway through.

The late-arriving-data twin of this problem: the same reprocessing mechanics (recompute a bounded historical range, verify against a baseline, communicate the correction) apply when a daily aggregate needs correcting because data for that day arrived late, not because a definition changed. The difference is scale and cadence, a late-arriving correction is usually small and routine (a few hours to a couple of days of data, handled automatically via an allowed-lateness window), while a definitional backfill is a large, deliberate, one-time reprocessing of full history, but the underlying discipline (versioning, validation, controlled cutover, communication) is the same.

An in-warehouse transformation tool like dbt gives you three things a scheduled cron script does not: version-controlled, testable SQL; a dependency graph the tool builds and executes for you; and documentation that stays attached to the code instead of living in a separate wiki.

Concretely, what you get

1. Modularity and dependency management. Each model is a .sql file that references other models with ref(). dbt parses those references into a DAG (directed acyclic graph) and runs models in the right order automatically, so you never hand-maintain "run script B after script A finished."
2. Testing as a first-class citizen. You declare assertions in YAML next to the model (uniqueness, not-null, accepted values, referential integrity to another model) and dbt runs them as part of the pipeline, not as an afterthought someone has to remember to write.
3. Documentation and lineage for free. dbt generates a browsable docs site from your model definitions and column descriptions, and a lineage graph showing exactly what feeds what. A cron-scheduled SQL script has none of this unless someone builds it by hand and keeps it in sync.
4. Git-native change management. Because everything is plain text SQL and YAML, you get code review, branching, and CI the same way you would for application code, which a stored procedure buried in a scheduler typically does not.

Where this actually helps day to day
A change to an upstream source column shows up as a broken test or a documented lineage edge before it silently corrupts three downstream marts. Onboarding a new analyst is "clone the repo and read the docs site" instead of "ask around for who owns which script." A bad model can be reverted with git revert instead of hand-reconstructing what the SQL used to say.

What it does not replace
dbt only transforms; it does not extract or load data (you still need a connector or CDC (Change Data Capture) tool to get data into the warehouse), and it does not orchestrate anything outside the warehouse (you still need something like Airflow to trigger dbt run on a schedule and coordinate it with non-dbt tasks).