ETL and ELT Design Patterns Questions

Streaming dedup at scale comes down to a trade-off between memory, latency, and how much false-negative/false-positive risk you can tolerate, and the right answer usually blends more than one technique.

The options

1. Exact dedup with a durable keyed state store: for every event id you've seen, keep a record in state (RocksDB-backed state in Flink/Spark Structured Streaming, or an external store like Redis). Guaranteed correct (zero false positives or negatives) but state grows unbounded unless you bound the window you're willing to remember.
2. Hash-based approaches: hash the event's content (not just its id) to dedup near-identical payloads even without a shared id field. Useful when upstream systems don't guarantee a stable id, but adds hashing compute and doesn't help if two legitimately different events happen to hash-collide (rare, but non-zero for weak hashes).
3. Bloom filters for approximate dedup: a probabilistic structure that can say "definitely not seen before" or "possibly seen before" using far less memory than exact state. The trade-off is explicit: false positives are possible (an event gets wrongly treated as a duplicate and dropped), false negatives are not (a genuine duplicate is never wrongly let through).
4. Time-windowed dedup: bound the durable state to a rolling window (say, the last 24 hours of ids), on the assumption that duplicates arriving further apart than that are rare enough to accept. This bounds memory growth for options 1 to 3 rather than being a separate technique on its own.
5. Dedup across restarts: whichever mechanism you pick, its state has to survive a job restart, either via a state backend with checkpointing (Flink/Spark's own fault-tolerant state) or by re-deriving state from a durable external store on startup, otherwise a restart silently resets your dedup window and lets through duplicates you'd already filtered.

When approximate dedup (Bloom filters) is actually acceptable
When the cost of a rare false-positive drop is genuinely low relative to the memory savings, most commonly for high-volume telemetry or engagement events feeding an aggregate metric, where losing an occasional event has negligible impact on the aggregate, versus a payments or billing pipeline, where a false-positive drop means a real transaction silently vanishes and Bloom filters are the wrong tool regardless of memory pressure.

Memory vs. latency, concretely: exact state stores cost more memory per unique key but impose essentially no extra latency per event (a state lookup). Bloom filters cost far less memory but need periodic maintenance (rotating or resizing as the filter fills up and its false-positive rate climbs), which is an operational latency/complexity cost rather than a per-event one. The realistic production pattern for high-volume, loss-tolerant streams is Bloom filter as a cheap first-pass filter, with an exact check as a fallback for anything the Bloom filter flags as "possibly seen," which gets you most of the memory savings with none of the false-positive risk.

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

Migrating nightly ETL jobs off an on-prem tool onto a cloud-native ELT stack (a modern warehouse plus dbt) is a data-correctness migration wearing an infrastructure-migration costume: the real risk isn't "can we stand up Snowflake and dbt," it's "will the new pipeline produce the exact same numbers the old one did, and if not, can we tell before a stakeholder does."

The plan

1. Inventory. List every existing job: what it reads, what business logic it applies, what it writes, who consumes the output, and how often it runs. Rank by consumer impact and complexity, not build order.
2. Map transformations. For each job, translate the transformation logic into dbt models (staging, then business logic). Do this job by job, not all at once, so each migrated piece can be validated independently.
3. Build parity tests. For every migrated job, write a reconciliation check that runs both the old and new pipeline over the same input window and diffs the outputs: row counts, key aggregates, and a sample of individual rows compared field by field. This is the single highest-leverage step; skipping it is how migrations ship silent regressions.
4. Staged validation. Run the new pipeline in shadow mode (writing to a parallel table, not the production one) for a real window of days, comparing continuously against the legacy output, before anyone downstream depends on it.
5. Dry-run backfills. Before cutover, backfill historical data through the new pipeline and reconcile it against the legacy historical output, since a pipeline that matches going forward can still disagree on how it handles old edge cases (a schema that changed shape a year ago, a historical data-quality issue the legacy job special-cased).
6. Cutover plan. Flip consumers over job by job once parity is proven, not all at once, so a problem is isolated to one job instead of the whole migration.
7. Rollback criteria. Define upfront what triggers a rollback (a reconciliation mismatch above some tolerance, a downstream consumer reporting wrong numbers) and keep the legacy job runnable in parallel until the new one has proven itself over a real operating window, not just the initial validation period.

The trap worth naming: teams treat "the new pipeline runs successfully" as the finish line. A pipeline that runs cleanly and silently produces slightly different numbers (a different NULL-handling default, a rounding difference, a JOIN that behaves differently because ELT does it in the warehouse instead of in application code) is worse than one that visibly fails, because nobody notices until a stakeholder does. Parity testing against the legacy pipeline, not just "does the new one look reasonable," is what catches that class of defect.

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.

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.