Pipelines Advanced

Delivery semantics

The vocabulary that anchors every streaming discussion:

• At-most-once: may drop messages, never duplicates. Rarely acceptable for billing/usage.
• At-least-once: never drops, may duplicate (the common default). You handle duplicates downstream via dedup on an idempotency key.
• Exactly-once: each message reflected once in the result. Real but expensive — achieved through idempotent writes + transactional offset commits, not magic.

Windowing & watermarks

Stream processors aggregate over windows of event time, and must decide when a window is "done" despite late data — that's the watermark.

• Tumbling (fixed, non-overlapping), hopping/sliding (overlapping), session (gap-defined) windows.
• Event time vs processing time: aggregate by when the event happened, not when it arrived, or your hourly metrics are wrong under lag.
• Watermark: "I believe I've seen all events up to time T." It triggers window emission. A larger allowed-lateness = more correctness, more latency/state. This tradeoff is the heart of streaming correctness.
• Late data after the watermark: drop, side-output, or update — pick deliberately.

Exactly-once in practice

How systems actually get there, so you can speak to it:

• Idempotent sink: upsert/merge on a deterministic key so reprocessing overwrites rather than appends.
• Transactional offsets: commit the consumer offset in the same transaction as the output write (Kafka transactions, Flink checkpoints) so a crash can't double-count.
• Checkpoint/replay: on failure, restore state from the last checkpoint and replay from the committed offset.

Connect it back: this is the streaming version of chapter 04's idempotency — same principle, harder setting.

CDC, deeply

• Log-based CDC (Debezium, Fivetran log mode) reads the DB's write-ahead/binlog. Captures inserts, updates, and deletes with low source load — the gold standard.
• Query-based CDC polls updated_at > last_seen. Simple but misses hard deletes and rows updated without bumping the timestamp.
• Soft vs hard deletes: log CDC emits delete events; decide whether your warehouse hard-deletes or marks is_deleted (usually the latter — you want history).
• Ordering & idempotency: CDC events carry a log sequence number (LSN/SCN); apply with a MERGE keyed on PK, keeping the highest LSN, so out-of-order or replayed events converge correctly.
• Snapshot + stream: initial full snapshot, then incremental log tailing — know that handoff is where bugs live (gaps/overlaps at the boundary).

File & table formats

Even warehouse-first shops hit these via external/iceberg tables and lakes.

• Columnar files — Parquet/ORC: column pruning + compression + predicate pushdown. Default for analytics-at-rest. Avoid row formats (CSV/JSON) for big data.
• Open table formats — Iceberg / Delta / Hudi: add ACID transactions, schema evolution, time travel, and hidden partitioning on top of Parquet files. They turn a file dump into a real table.
• The small-files problem: streaming writes create many tiny files that wreck scan performance; you run compaction to coalesce them. Know this exists.
• Partitioning vs clustering: partition by a low-cardinality, frequently-filtered column (date); cluster/Z-order within for secondary keys. Over-partitioning (e.g. by customer) creates millions of tiny partitions — a classic mistake.

Lakehouse vs warehouse

First-hire take: "Warehouse-first for a small team — it's lower ops and SQL-native. I'd reach for lakehouse/Iceberg when data volume, ML workloads, or open-format/cost concerns justify it. The two are converging anyway (warehouses now read Iceberg)."

Orchestration patterns that matter

• Idempotency key per run (the logical date/partition) so retries and backfills target a specific slice — the orchestration twin of delete-insert.
• Sensors / triggers: wait for upstream readiness (a file lands, a source table is fresh) instead of guessing with fixed schedules.
• Dynamic task mapping / fan-out: generate one task per partition/customer at runtime; bounds blast radius and enables parallel backfills.
• Retries with exponential backoff + jitter for transient failures; cap attempts so you fail loudly rather than retry forever.
• Asset/data-aware orchestration (Dagster): model the data assets and their lineage, not just tasks — failures and staleness are expressed in terms of data.
• Concurrency & pools: limit simultaneous hits on a source DB / warehouse to avoid self-inflicted outages.

Errors, retries, dead-letter queues

• Distinguish transient vs poison: retry transient (network, rate limit); route permanently-bad records to a dead-letter queue so one malformed row doesn't halt the whole batch.
• Quarantine, don't drop: bad rows go to a quarantine table with the reason — you can inspect, fix, and reprocess. Silent dropping destroys trust.
• Partial-failure semantics: design so a failed batch leaves the target unchanged (transaction or staging-then-swap), never half-written.
• Alert on the DLQ growing, not just on job failure — a job can "succeed" while quietly quarantining everything.

Write-audit-publish (WAP)

The pattern that prevents bad data from ever reaching consumers — and the cleanest answer to "how do you deploy data changes safely?"

1. Write the new data to a staging/branch location (or an Iceberg branch).
2. Audit — run quality tests against the staged data.
3. Publish — atomically swap it into production only if audits pass; otherwise hold and alert.

Blue-green for data. Combine with idempotent partitions so a failed publish is a no-op. This is also how you do zero-downtime backfills of a live table.

Testing pipelines (not just data)

• Unit tests on transform logic: dbt unit tests / seeds with fixed inputs → assert exact outputs. Catches logic regressions before they touch real data.
• Schema/contract tests at ingest (chapter 05) as the upstream tripwire.
• Integration tests on a sample warehouse: build the DAG end-to-end on seeded data in CI.
• Idempotency test: run the load twice, assert the output is identical — directly proves the property interviewers care about.
• Reconciliation as a test: row-count and sum checks vs source (the diff/EXCEPT pattern from 02b).

Cost & performance optimization

• Incremental over full-refresh — the biggest lever; reprocess only new partitions.
• Partition pruning & clustering so queries scan less (bytes = dollars on BigQuery; warehouse-seconds on Snowflake).
• Materialize hot rollups consumed by many dashboards instead of recomputing per view.
• Right-size warehouses / auto-suspend idle compute; separate ELT and BI warehouses so one doesn't starve the other.
• Prune ingestion: only sync columns/tables you use; high-volume connectors priced per row add up fast.
• Watch backfills — estimate bytes before replaying history; chunk by partition.

Config, secrets, environments

• Separate dev/staging/prod targets (separate schemas/databases) so you never test on prod tables.
• Secrets in a manager (cloud secret manager / env), never in code or dbt profiles committed to git.
• Config as code & version control everything — dbt project, orchestration DAGs, infra. Reproducibility is part of trust.
• CI on pull requests: build changed models + run tests on a sample before merge (slim CI / state:modified). Covered more in 05b.

✓ Check yourself

Answer from memory before opening each one.

1. A consumer reads from Kafka, writes to the warehouse, then commits its offset. It crashes between the write and the commit. What does the warehouse hold after restart, and what makes this safe?

   Show answer

   The messages since the last commit are written again, so you get duplicates. Make the write idempotent with a MERGE keyed on a stable event id, or commit offsets in the same transaction as the output. That yields effectively-once results from at-least-once delivery. See exactly-once in practice.

2. Hourly counts are built by event time. Phones that were offline upload a backlog 40 minutes late. You raise allowed lateness from 5 minutes to 2 hours. What do you gain and what do you pay?

   Show answer

   You gain completeness, because late events land in the correct hour. You pay in latency, since a window stays open longer before it emits, and in state, because the engine holds every open window. That tradeoff sits at the centre of streaming correctness. See windowing and watermarks.

3. A nightly model passes in dev, but a bad source batch reaches production and consumers see wrong numbers for hours. Which pattern prevents this, and what are its three steps?

   Show answer

   Write-audit-publish. Write the new data to a staging location, run the quality tests against it, and swap it into production only if they pass. A failed audit leaves production untouched and raises an alert. See write-audit-publish.

4. CDC delivers two updates for the same primary key out of order: log position 200 arrives before log position 150. How do you apply them so the final row is right?

   Show answer

   MERGE on the primary key and apply a change only if its log position is higher than the stored one. Then replays and reordering converge on the same row. Watch the snapshot-to-stream handoff, where gaps and overlaps hide. See CDC, deeply.

5. A streaming job has written one small file per micro-batch for six months, and scans have slowed badly. What is the cause, what is the fix, and what mistake do teams make when partitioning to help?

   Show answer

   Thousands of tiny files make planning and reading slow. Run compaction to merge them into larger files. Partition on a low-cardinality column such as date; partitioning by customer creates millions of tiny partitions and makes it worse. See file and table formats.

6. A four-person team with 2 TB of data debates Iceberg on object storage versus a managed warehouse. What decides it?

   Show answer

   Lean on the warehouse: lower operating load and SQL-native access suit a small team. Reach for open table formats when ML workloads, very large unstructured data, or lock-in and cost pressure justify the extra operations. Warehouses now read Iceberg, so the choice is less final than it looks. See lakehouse versus warehouse.

Takeaway

• The practical streaming pattern is at-least-once delivery plus idempotent writes keyed on a stable event id.
• Aggregate by event time; the watermark trades completeness against latency and state.
• Exactly-once is built from idempotent sinks, offsets committed with the output, and checkpoint replay.
• Log-based CDC captures deletes and carries a log position; apply changes with a merge that keeps the highest position.
• Streaming writes create small files; compact them and partition on a low-cardinality column such as date.
• Write-audit-publish keeps failed audits from reaching consumers, and doubles as a safe backfill method.
• Quarantine bad rows with the reason attached, and alert when the quarantine grows, not only when a job fails.

Moving data well only counts if people can trust it, which is the job of testing and monitoring. → Data Quality & Trust

Further reading

• Message delivery semantics — Apache Kafka docs, 2026. At-most-once, at-least-once and exactly-once, and what Kafka transactions add.
• Timely Stream Processing — Apache Flink docs, 2026. Event time, processing time and watermarks explained for one engine.
• The Dataflow Model — Akidau et al., VLDB 2015. The paper behind windows, watermarks and triggers.
• Debezium reference — Debezium project, 2026. Log-based CDC, snapshots and event ordering.
• Branching and tagging — Apache Iceberg docs, 2026. Branches as the mechanism for write-audit-publish.
• Apache Parquet documentation — Apache Parquet project, 2026. The columnar file format under most open tables.