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.