Stream Data Upsert and Replay

Stream Data Upsert and Replay in Azure (DP-203)

Stream Data Upsert and Replay is a critical concept for the Azure Data Engineer DP-203 exam. Understanding how to handle streaming data with upsert (merge) logic and replay capabilities ensures you can design resilient, accurate, and idempotent data pipelines.

Why Is Stream Data Upsert and Replay Important?

In real-world streaming scenarios, data arrives continuously and often includes updates to existing records, late-arriving data, or duplicates. Without proper mechanisms:
- Duplicate records can corrupt analytics and reporting.
- Lost updates can lead to stale or inaccurate data.
- System failures can result in data loss if messages cannot be reprocessed.

Upsert and replay patterns solve these problems by ensuring exactly-once semantics, data consistency, and fault tolerance in streaming architectures.

What Is Stream Data Upsert?

An upsert (a combination of update and insert) is an operation that:
- Inserts a new record if it does not already exist in the target store.
- Updates the existing record if a matching key is found.

In the context of streaming data, upsert is essential when the stream contains both new records and modifications to previously ingested records. For example, an IoT device sending sensor readings where corrections or updated readings may arrive after the initial data.

Key Azure Technologies for Stream Upsert:

1. Azure Databricks with Delta Lake (Structured Streaming)
Delta Lake's MERGE INTO statement is the primary mechanism for upserts in streaming pipelines. Using foreachBatch in Structured Streaming, you can apply merge logic on each micro-batch:

Example pattern:
- Define a streaming DataFrame from a source (e.g., Event Hubs, Kafka).
- Use foreachBatch to apply a Delta Lake MERGE operation.
- The MERGE statement matches on a key column and performs UPDATE when matched, INSERT when not matched.

This is the most commonly tested pattern on the DP-203 exam.

2. Azure Synapse Analytics with Spark Structured Streaming
Similar to Databricks, Synapse Spark pools support Delta Lake and the foreachBatch pattern for stream upserts.

3. Azure Stream Analytics
While Stream Analytics does not natively support upserts in the same way, it can output to SQL Database or Cosmos DB where upsert behavior is configured at the output sink level (e.g., using UPSERT mode in Cosmos DB output or MERGE in SQL).

4. Azure Cosmos DB Change Feed
Cosmos DB inherently supports upsert operations. When used as a sink for streaming data, documents are automatically updated if the same partition key and ID are provided.

What Is Stream Data Replay?

Stream replay is the ability to re-read and reprocess data from a streaming source from a specific point in time or offset. This is crucial for:

- Failure recovery: If a pipeline fails, you can restart from where it left off or from the beginning.
- Reprocessing: When business logic changes, you may need to reprocess historical stream data.
- Testing and debugging: Replaying data helps validate new pipeline logic.

Key Azure Technologies for Stream Replay:

1. Azure Event Hubs
- Supports message retention (1 to 90 days, depending on tier).
- Consumers can replay from a specific offset, sequence number, or enqueued time.
- Consumer groups allow multiple independent readers to process the same data at different offsets.
- The Capture feature stores raw events in Azure Blob Storage or Data Lake for long-term replay.

2. Apache Kafka on Azure (HDInsight or Event Hubs with Kafka protocol)
- Kafka retains messages based on configurable retention policies.
- Consumers can reset offsets to replay from the earliest available message or a specific timestamp.

3. Delta Lake (Change Data Feed / Time Travel)
- Delta Lake supports time travel, allowing you to query or reprocess data as it existed at a previous version or timestamp.
- The Change Data Feed (CDF) captures row-level changes (inserts, updates, deletes) which can be replayed downstream.

4. Checkpointing in Structured Streaming
- Spark Structured Streaming uses checkpoints to track processing progress.
- To replay, you can delete or modify the checkpoint location to reprocess from an earlier point.
- Checkpoints work with Event Hubs, Kafka, and file-based sources.

How Upsert and Replay Work Together

The combination of upsert and replay creates an idempotent pipeline:

1. A streaming source (Event Hubs/Kafka) retains messages for replay.
2. The processing engine (Spark Structured Streaming) reads from a checkpoint or replays from an earlier offset.
3. The sink (Delta Lake table) uses MERGE/upsert logic so that reprocessed records do not create duplicates — they simply update existing rows.

This pattern ensures exactly-once processing semantics even in the face of failures, restarts, or intentional replays.

Architecture Pattern Summary:

Source (Event Hubs with retention) → Processing (Structured Streaming with checkpointing + foreachBatch) → Sink (Delta Lake with MERGE) = Idempotent, replayable, upsert-capable pipeline

Key Concepts to Remember:

- foreachBatch: The Structured Streaming method that allows you to apply batch operations (like MERGE) to each micro-batch of streaming data.
- MERGE INTO: The Delta Lake SQL command for upserts — WHEN MATCHED THEN UPDATE, WHEN NOT MATCHED THEN INSERT.
- Checkpointing: Tracks offsets and state; essential for exactly-once guarantees and restart capability.
- Consumer groups: Enable multiple independent readers on Event Hubs for parallel or independent processing.
- Event Hubs Capture: Automatically archives stream data to storage for long-term replay and batch reprocessing.
- Delta Lake Time Travel: VERSION AS OF or TIMESTAMP AS OF queries to access historical data states.
- Idempotency: The property that reprocessing the same data produces the same result without side effects.

Common Exam Scenarios:

1. Scenario: You need to process IoT data in real-time and ensure no duplicate records in the target table.
Answer: Use Structured Streaming with foreachBatch and Delta Lake MERGE.

2. Scenario: A pipeline fails and you need to reprocess the last 24 hours of events.
Answer: Configure Event Hubs retention, reset consumer offset or adjust checkpoint, and use upsert logic to prevent duplicates.

3. Scenario: Business logic changes and all historical stream data must be reprocessed.
Answer: Use Event Hubs Capture (for raw data in storage) or Delta Lake time travel/change data feed to replay and reprocess.

4. Scenario: You need to ensure exactly-once delivery to a Delta Lake table from a Kafka source.
Answer: Combine Structured Streaming checkpointing with Delta Lake MERGE in foreachBatch.

---

Exam Tips: Answering Questions on Stream Data Upsert and Replay

1. Know the foreachBatch + MERGE pattern cold. This is the most frequently tested pattern for stream upserts. If a question asks about updating existing records in a Delta table from a stream, this is almost always the answer.

2. Distinguish between output modes. Remember that Append mode does not support updates, Complete mode rewrites the entire result, and Update mode only outputs changed rows. For true upsert logic, foreachBatch with MERGE is needed.

3. Understand checkpoint behavior. If asked about restarting a stream or replaying data, know that deleting a checkpoint forces reprocessing from the beginning, while keeping it resumes from the last committed offset.

4. Event Hubs retention vs. Capture: Retention (up to 90 days on Premium/Dedicated) allows consumer replay within the retention window. Capture provides permanent storage in AVRO format for long-term replay. Know when to recommend each.

5. Delta Lake is the preferred sink for upserts. If a question mentions preventing duplicates in a data lake, Delta Lake with MERGE is the answer — not Parquet or CSV.

6. Idempotency is the key concept. Many exam questions test whether you understand that combining replay + upsert = idempotent pipeline. Look for keywords like exactly-once, no duplicates, fault-tolerant, and reprocessing.

7. Watch for Cosmos DB upsert questions. If the output is Cosmos DB, remember that providing the same ID and partition key automatically performs an upsert. No special MERGE logic is needed at the application level.

8. Know watermarking for late data. While not strictly upsert/replay, watermarks in Structured Streaming handle late-arriving data. If a question combines late data with upsert scenarios, watermarking plus foreachBatch MERGE is the complete answer.

9. Read questions carefully for the sink type. The upsert mechanism differs by sink: Delta Lake uses MERGE, Cosmos DB uses native upsert, SQL Database uses stored procedures or MERGE, and Synapse dedicated pools may use staging tables.

10. Elimination strategy: If an answer option suggests using Append mode to handle updates, eliminate it immediately. If an option suggests using foreachBatch without MERGE (just inserts), eliminate it when the question mentions updates to existing records.