ETL and ELT Design Patterns Questions

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.

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 watermark is a marker that says "I've processed everything up to this point, and I'm not expecting anything older to still arrive." It's the mechanism that turns "read new data" from a vague instruction into something a query can actually execute: a boundary condition (WHERE updated_at > watermark).

Event-time vs. processing-time

• An event-time watermark tracks progress against when something actually happened in the real world (a created_at or event_time column on the record itself).
• A processing-time watermark tracks progress against when your pipeline saw or ingested the record (a load timestamp your job assigns).

The distinction matters because these two clocks disagree whenever data arrives late: an event that happened at 10:00 but only reaches your pipeline at 10:45 has an event-time of 10:00 and a processing-time of 10:45. If your watermark advances on processing-time, that event is correctly picked up whenever it shows up. If your watermark advances on event-time and you set it strictly (never accept anything before the current watermark), that late event gets silently dropped once the watermark has moved past 10:00.

Watermarks as incremental load windows
A monotonic column, most commonly updated_at on a mutable table or an auto-incrementing offset on an append-only log, gives you a cursor: each run reads WHERE watermark_column > last_recorded_watermark and, on success, advances the stored watermark to the maximum value it just processed. This is exactly the high-water-mark pattern behind most incremental extraction.

Handling late-arriving data without losing it
The honest fix is allowed lateness: instead of treating the watermark as a hard cutoff, you keep accepting records within a bounded grace window behind the watermark (say, up to 2 hours late) before you consider that window closed. This is a real trade-off, not a free lunch: a longer allowed-lateness window catches more genuinely late data but delays when you can consider any given window "final" and keeps more state open longer (memory in a streaming job, or a longer window before a daily aggregate can be marked complete).

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