Azure Stream Analytics guarantees exactly-once processing within the processing pipeline. However, it currently doesn’t ensure end to end exactly-once delivery to output sink. Instead, it guarantees at-least-once delivery to output sink. When using SQL output, we can achieve exactly-once semantics if the following requirements are met:
- all output streaming events have a natural key, i.e. are uniquely identifiable either by a field or a combination of fields.
- the output SQL table has a unique constraint (or primary key) created using the natural key of the output events.
This is sufficient to avoid duplicates because the SQL output honors any constraints placed on the table by skipping any events that cause a unique constraint violation.