Exactly-Once Delivery from Azure Stream Analytics to Azure Synapse Analytics

Quick Answer

Exactly-once delivery is fundamentally about idempotency, making sure that if the same event gets sent to Synapse more than once, perhaps because of a retry after a transient failure, the end result in the table is the same as if it had only been delivered once. A primary key is what makes that possible at the destination: with a primary key defined, writing the same row twice becomes an upsert rather than a duplicate insert, so the second attempt simply overwrites or matches the existing row instead of creating a second copy. This only works alongside a unique identifier carried on each event, since the primary key needs something stable and consistent to key off of; without it, there would be nothing for the upsert logic to match against. A watermark strategy handles a related but distinct concern: accounting for events that arrive later than expected relative to their timestamps, which affects correctness of windowed results rather than duplicate delivery. Writing in batch mode is not what enables exactly-once semantics here, since the guarantee comes from how individual rows are matched and upserted, not from how many rows are sent per write. Whenever a Stream Analytics question involves guaranteeing exactly-once output to a relational destination, expect the answer to center on unique identifiers paired with a primary key that turns duplicate writes into harmless upserts.