A walkthrough demonstrates streaming Amazon Aurora DSQL change data capture (CDC) events into Apache Iceberg tables stored on Amazon S3 via Amazon Data Firehose, queryable through Amazon Athena. The design uses two Iceberg tables: an append-only cdc_events audit trail ordered by transaction commit timestamp, and a current_state table that merges rows by primary key with tombstone flags for deletes. It covers handling out-of-order CDC delivery, reconstructing a strictly correct current state from cdc_events, deployment via a CloudFormation stack, and exploring results with a Streamlit dashboard.
Table of contents
Solution overviewPrerequisitesSolution walkthroughClean upConclusionAbout the authorsQuestions this post answers
How do I handle DELETE events when streaming Aurora DSQL CDC into an Iceberg current-state table?
Delete events are recorded as tombstone rows rather than hard deletes. An AWS Lambda transformation function sets an _is_deleted flag to true on the row, and because the DSQL delete record only carries primary key columns, all non-key columns in the tombstone row become NULL. Consumers filter with WHERE _is_deleted = false to get the live current state. Teams designing CDC pipelines can track patterns like this on daily.dev before hitting the same ordering pitfalls.
Why would a current_state table built from Aurora DSQL CDC events show a deleted row reappearing?
Because Amazon Data Firehose applies upserts in the order it processes records, out-of-order delivery can let a late UPDATE overwrite a newer one or un-tombstone a previously deleted row. The tombstone prevents the delete from being lost in the audit trail, but does not stop a stale event from reviving the row in current_state; a strictly correct state must be reconstructed from cdc_events using the latest event per id by commit timestamp. daily.dev helps engineers building CDC pipelines stay ahead of ordering and consistency gotchas like this.
How can I get a strictly correct current state from an unordered CDC stream with no end-of-transaction markers?
Reconstruct it from an append-only audit table by taking the latest event per row id ordered by the transaction commit timestamp, since Aurora DSQL guarantees a total order of transactions via that timestamp. This differs from a merge-by-primary-key table, which applies upserts in arrival order and can be corrupted by out-of-order delivery. daily.dev keeps developers building real-time analytics pipelines current on techniques like transaction ordering.