ETL and ELT Design Patterns Questions

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 delete-then-insert scoped to exactly one partition, wrapped in a single transaction, so a reader never sees a half-loaded state and a retry never duplicates or corrupts other partitions.

BEGIN TRANSACTION;

DELETE FROM fact_events WHERE event_date = DATE '2026-01-01';
INSERT INTO fact_events
SELECT * FROM fact_events_stg_20260101;

COMMIT;

Verified: starting with fact_events holding rows for both 2026-01-01 and 2026-01-02, and a staging table fact_events_stg_20260101 with a corrected value for an existing row, an unchanged row, and one row that was previously missing:

before: 2026-01-01 has rows (1, amount=10), (2, amount=20)
        2026-01-02 has row  (3, amount=30)

staging: (1, amount=11 [corrected]), (2, amount=20 [unchanged]), (4, amount=40 [new])

after the transactional swap:
  event_id | event_date | amount
  1        | 2026-01-01 | 11.0
  2        | 2026-01-01 | 20.0
  4        | 2026-01-01 | 40.0
  3        | 2026-01-02 | 30.0   <-- untouched

re-running the SAME load again: identical result (confirmed idempotent)
2026-01-02's row count confirmed unchanged at every step: 1

Why this stays scoped to one partition: both the DELETE and the INSERT filter by event_date, and staging is itself built per-partition (its own table or a filtered subset), so nothing outside 2026-01-01 is ever touched, which is what lets you reprocess one day without a full-table operation.

Why the transaction matters, not just the DELETE+INSERT logic: without wrapping both statements in one transaction, a reader querying between the DELETE and the INSERT would see zero rows for that partition, a real (if brief) data-availability regression for anyone querying at the wrong moment, and a crash between the two statements would leave the partition empty rather than in either its old or new correct state. A single transaction makes the swap atomic from any reader's point of view: they see either the old partition contents or the new ones, never neither.

Supporting backfills and reprocessing without duplicates: because the DELETE always removes the full current contents of the target partition before the INSERT, this pattern is naturally idempotent, a backfill or a manual reprocess of a partition is exactly the same operation as its normal load, not a special code path. That's the core advantage over an append-only or a keyed-MERGE approach for this specific case: you don't need a unique constraint or a dedup step, because you're replacing a bounded, well-defined chunk wholesale rather than trying to reconcile individual rows.

Trade-off worth naming: this pattern requires staging to hold the FULL correct set of rows for the partition being reloaded, not just a delta; if your staging batch is itself incomplete (missing rows that should still be in that partition), the DELETE will correctly remove them but the INSERT won't bring them back, silently shrinking the partition. That's the failure mode to design guardrails against (a row-count sanity check on staging before running the swap), not a corner case you can ignore.

Once a company has more than a handful of self-serve analysts, letting each of them build transformation logic inside their own BI tool workbook (a Tableau Prep flow, a Power BI query) breaks in a specific, predictable order.

What breaks first

1. Duplication of logic. The same "active user" definition gets implemented slightly differently in three different dashboards, because each analyst wrote their own version instead of referencing a shared one, and nobody notices until an executive asks why two reports disagree.
2. No discoverability. Logic buried in a BI tool's proprietary transformation layer isn't searchable, versioned, or reviewable the way a warehouse table or a dbt model is; you can't grep it, and there's no lineage graph pointing back to it.
3. Query performance and governance drift out of anyone's control. Heavy transformations running inside the BI tool (rather than pre-materialized in the warehouse) mean every dashboard refresh re-does expensive work, and row-level security or masking rules applied in the warehouse don't automatically extend into whatever an analyst built locally.

How to decide what belongs where
Critical, widely-consumed metrics (revenue, active users, anything that ends up in an executive dashboard or a board deck) belong centrally in the warehouse as tested, documented ELT models, owned by whoever is accountable for that metric being right. Genuinely exploratory, single-use analysis (an analyst reshaping data for one ad-hoc investigation that will never be reused) is fine to leave in the BI tool, because the cost of formalizing it exceeds the benefit.

Who should own transformation logic for critical metrics
The team that's accountable for the metric being correct, which in practice means a data engineering or analytics engineering function that can enforce testing, review, and a single source of truth, not whichever analyst happened to need the number first. The self-serve BI layer should consume the already-correct warehouse table, not re-derive the definition.

The trade-off worth naming: centralizing everything into the warehouse is more governable but slower for an individual analyst to iterate on. The realistic policy most teams land on is a promotion path: an analyst prototypes a transformation in the BI tool, and once it's proven useful and gets reused by more than one person, it gets "promoted" into a tested dbt model in the warehouse, rather than either extreme (everything centralized from day one, or nothing ever formalized).

Architecting this pipeline means deliberately splitting transformation work by WHERE it needs to run, not defaulting to one pattern everywhere. The two workloads (sub-5-second personalized recommendations, and daily batch analytics) have genuinely different constraints, and the split should be visible in the design, not incidental to it.

The transform-placement decision, explicit

• Clickstream, feeding real-time recommendations (pre-load ETL, by necessity): light validation and enrichment (session stitching, basic feature computation) has to happen in the streaming path itself, before the recommendation service can use it, because ELT's "load raw, transform later in the warehouse" pattern is fundamentally too slow for a 5-second SLA (service-level agreement). This is ETL because it MUST be, not by preference.
• CDC (Change Data Capture) from transactional databases, feeding daily analytics (ELT): these changes get captured with minimal transformation (log-based CDC via Debezium, landed close to raw) and the heavy transformation, joins, aggregation, dimensional modeling, happens after landing in the warehouse, where it benefits from batch compute, is trivially reprocessable if a transform has a bug, and doesn't need to keep pace with a sub-5-second SLA.
• The clickstream ALSO feeds daily analytics, and for that path, the same raw (or lightly-enriched) events land in a data lake and get transformed in batch alongside everything else, meaning the same source data takes two different paths depending on which downstream consumer it's serving.

Architecture

flowchart LR
  CS[Clickstream 1B events/day] --> K[Kafka]
  DB[(Transactional DBs)] --> CDC[Debezium CDC] --> K
  K --> SP[Stream processor: light ETL - validate, enrich, feature-compute]
  SP --> RT[(Real-time feature store)]
  RT --> REC[Recommendation service <5s SLA]
  K --> LAKE[(Raw landing: data lake)]
  LAKE --> ELT[Batch ELT: dbt models in the warehouse]
  ELT --> DW[(Analytics warehouse)]
  DW --> BI[Daily analytics and dashboards]

Storage choices: the data lake (object storage, Parquet) is the durable raw landing zone for everything, cheap and replayable. The real-time feature store (a low-latency key-value store, not the warehouse) serves the recommendation path because a warehouse query, even a fast one, is the wrong latency class for a 5-second end-to-end budget that also has to include model inference. The analytics warehouse holds the ELT-transformed, dimensionally modeled data for BI.

Streaming vs. batch split: the recommendation path is pure streaming, no batch step in its critical path at all. The daily-analytics path is batch (scheduled dbt runs), consuming from the same raw landing zone the streaming path also reads from, so there is exactly one ingestion point (Kafka/Debezium into the lake) feeding two independently-paced downstream consumers.

Failure and recovery: the streaming path's failure mode is "recommendations get staler or fall back to a non-personalized default," recoverable by replaying from the last committed Kafka offset once the stream processor is healthy again. The batch ELT path's failure mode is "the daily analytics refresh is late," recoverable by simply rerunning the failed dbt run, since ELT models built on landed, durable raw data are naturally re-runnable without any data loss risk, which is exactly the property that justifies deferring most of the transformation work to that side of the split.

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.