An Idempotency Pattern for Achieving "Exactly-Once for the DB, At-Least-Once for Notifications" in SQS → Lambda → DB / SNS

1. Introduction

Hello, I'm Simon from the Consulting Department.

When processing messages from SQS with Lambda, have you ever wanted to cleanly satisfy the requirement: "DB writes must never be duplicated, but the downstream notification must be delivered at least once (duplicates are acceptable)"? I have.

For example, in an order ingestion pipeline, order records must be unique, but for Slack notifications, duplicates are more acceptable than missing notifications.

Standard SQS provides at-least-once delivery. There are mainly two cases in which duplicate delivery occurs.
One is when Lambda does not finish normally, in which case the message is redelivered after the visibility timeout elapses (caused by timeouts, out-of-memory errors, errors in the AWS infrastructure layer, and so on).
The other is due to SQS's distributed storage architecture: even after Lambda has successfully deleted a message, the same message may rarely be redelivered.

If you do not design your processing with this duplicate delivery in mind, it leads to double writes of business data or lost notifications.

The goal of this article is to satisfy this requirement with a single idempotency pattern. Specifically, it covers the following three points.

• Combine the state management table (processed_events) and the business table into a single atomic write with TransactWriteItems
• Perform the SNS publish after the write succeeds, and update the status in the state management table once it completes
• On redelivery, branch three ways based on the status, so that the data layer behaves as exactly-once and the notification layer behaves as at-least-once

2. Architecture diagram and prerequisites

The architecture covered in this article is SQS → Lambda → DynamoDB / SNS.

The source of messages sent to SQS is not specified. Any producer is assumed, such as S3 event notifications, EventBridge rules, or direct SendMessage calls from an application.

The implementation examples use DynamoDB, but the pattern itself is DB-independent.

There are three assumed requirements.

• Losing the processing of any item that flows into SQS is not acceptable
• Double writes to the DB are also not acceptable (the same item must not become two rows)
• Duplicate notifications are acceptable (a human can check and skim past them)

3. DynamoDB table design

The tables are split into two: processed_events (state management) and order_records (business data). This time I decided to separate the tables in order to separate the concerns of state management and business data. This way, even if the schema of the business table changes, the state management logic is unaffected. Writes to both tables are combined into a single atomic operation with TransactWriteItems.

processed_events (state management table)

For the partition key, use the event ID (event_id) assigned on the producer side. For example, this is a value that is unique on the business side, such as an S3 object key, an order ID, or a request ID. Do not use the SQS message ID. When the same event is resent by the producer, SQS issues a new message ID, so the message ID cannot identify the same event.

The status attribute takes only two values: pending and sent.

• pending: TransactWriteItems has succeeded, SNS publish not yet completed
• sent: SNS publish has completed

ProcessedEventsTable:
  Type: AWS::DynamoDB::Table
  Properties:
    TableName: processed_events
    BillingMode: PAY_PER_REQUEST
    AttributeDefinitions:
      - AttributeName: event_id
        AttributeType: S
    KeySchema:
      - AttributeName: event_id
        KeyType: HASH
    TimeToLiveSpecification:
      AttributeName: ttl
      Enabled: true

order_records (business table)

The business table varies depending on the reader's domain, so since this is a concept article, I will skip its implementation. In this article, we assume an order table order_records exists.

4. Processing flow

First, let's check the flow in the normal case.

Lambda first atomically writes the two items to processed_events and order_records with TransactWriteItems. Attach ConditionExpression: attribute_not_exists(event_id) to the Put for processed_events. If this key already exists, the entire transaction is canceled, so double writes to the business table are prevented at the same time.

try:
    dynamodb.transact_write_items(
        TransactItems=[
            {
                "Put": {
                    "TableName": "processed_events",
                    "Item": {
                        "event_id": {"S": event_id},
                        "status": {"S": "pending"},
                    },
                    "ConditionExpression": "attribute_not_exists(event_id)",
                }
            },
            {
                "Put": {
                    "TableName": "order_records",
                    "Item": {
                        "order_id": {"S": order_data["order_id"]},
                        # Add business fields
                    },
                }
            },
        ]
    )
except ClientError as e:
    if e.response["Error"]["Code"] != "TransactionCanceledException":
        raise
    # event_id already exists → check status with GetItem and branch on pending/sent (detailed in the next section)
    ...

If TransactWriteItems succeeds, publish to SNS. SNS cannot participate in a DynamoDB transaction, so the publish must always be done outside the transaction.

sns.publish(TopicArn=sns_topic_arn, Message=event_id)

Once the publish completes, update status to sent with UpdateItem, and finally delete the message from SQS.

dynamodb.update_item(
    TableName="processed_events",
    Key={"event_id": {"S": event_id}},
    UpdateExpression="SET #s = :sent",
    ExpressionAttributeNames={"#s": "status"},
    ExpressionAttributeValues={":sent": {"S": "sent"}},
)

5. Three branches on redelivery

If the cause of the TransactWriteItems failure is TransactionCanceledException, the event_id already exists.

Next, read status with GetItem and branch the processing depending on whether it is pending or sent.

item = dynamodb.get_item(
    TableName="processed_events",
    Key={"event_id": {"S": event_id}},
)["Item"]
if item["status"]["S"] == "sent":
    return  # Go to pattern B
# status == "pending": proceed to publish

Pattern A: pending — run publish only

This pattern occurs when Lambda times out, runs out of memory, or crashes after TransactWriteItems succeeds but before the SNS publish. The write to the business table was already completed the first time, so it is skipped, and only the SNS publish is executed.

Pattern B: sent — skip everything

This is the case where a message that has already completed normally is redelivered. Skip both the write and the publish, and simply delete the message.

6. Doesn't FIFO SQS solve this?

FIFO SQS has a feature called exactly-once processing, and the AWS documentation states that it can deduplicate messages. The question "Wouldn't FIFO SQS do the job?" is a natural one.

However, this is a deduplication feature for the case where the producer side mistakenly calls SendMessage twice with the same message.

The behavior on the consumer side (the Lambda in this case) is no different from Standard SQS. If Lambda times out or crashes while processing a message, the message is redelivered after the visibility timeout elapses. This redelivery occurs with FIFO as well, so idempotent processing on the consumer side (Lambda) is still necessary. The pattern in this article can be applied to FIFO SQS as is.

FIFO-specific trade-offs also need to be considered.

• You need to design MessageGroupId (ordering is guaranteed within the same group, but different groups are processed in parallel)
• Throughput limits (there is a limit even with high-throughput FIFO mode)
• Some event sources cannot target a FIFO queue directly as the destination (e.g., S3 event notifications)

Cases where you should choose FIFO are when message ordering must be guaranteed, or when duplicate sends from the producer side occur frequently. If the requirement is simply "ensure idempotency on the consumer side," Standard SQS is sufficient.

7. Summary

This article introduced a pattern that achieves exactly-once in the data layer and at-least-once in the notification layer at the same time, using an SQS → Lambda → DynamoDB / SNS configuration.

There are three key points of the implementation.

• Write to the business table at the same time using TransactWriteItems with an attribute_not_exists condition on the event_id of the processed_events table, atomically preventing double writes
• Perform the SNS publish outside the transaction, and update status to sent after it completes
• On redelivery, branch on status, and retry the publish only when it is pending

Note that the line drawn at "allowing duplicate notifications" presupposes agreement with the business side. In cases where duplicate notifications are a problem, a separate deduplication mechanism is also needed on the SNS subscriber side.