Redshift and Iceberg: transactions, conflicts and time travel in practice
Anyone who has run a nightly load on a data lake knows the incident: the MERGE finished, the DELETE for voided sales failed, and for forty minutes the revenue dashboard showed an inflated number someone had already sent to leadership.
The question this Redshift announcement answers isn't "does Iceberg support ACID?". The format has always had atomic commits per snapshot. The real question is who guarantees that three steps of a load become a single snapshot, and who resolves things when streaming and a scheduled job write to the same table in the same minute. Until 11/16/2026 the answer was your orchestration code. Now part of it is the engine.
The numbers AWS published
- 12x: more commits with 16 writers. Autocommit
INSERTbenchmark on an unpartitioned table: without resolution, 94% of commits failed and throughput fell to 0.17x of a single writer. - 1 em 5: write failures were conflicts. Weekly share of failed Iceberg write queries caused by concurrent conflicts, before the operation-level check reached the whole fleet in early September 2026.
- 1: Iceberg table per transaction. The transaction reads from anywhere but writes to one table only. Writing to a second one aborts everything.
What actually changed
There are three capabilities, and they apply to RG provisioned or Serverless Redshift writing to or reading Iceberg, in every commercial Region. Note what was left out: the announcement doesn't mention RA3. If your cluster is still RA3, the conversation starts with migrating to RG, not with BEGIN.
- Multi-statement transactions: you group
INSERT,UPDATE,DELETEandMERGEin aBEGIN…COMMITblock and Redshift publishes a single snapshot with a single catalog update. Each statement builds on the previous one's uncommitted metadata. If one fails, nothing is published and there is nothing to undo. It also works in a multi-statement string and over JDBC with autoCommit=false. It requires patch 206 or later. - Automatic conflict resolution: when another writer commits while yours is running, the engine tries to rebase your changes onto the newest version using metadata only, without rereading data or replanning the query. It fails only when both writes touched the same data.
- Time travel:
AS OF TIMESTAMPafter the table name, inFROM,JOINorUSING, reads the snapshot that was current at that instant. Read-only: it can't be the target of DML or DDL.
None of this is a new Iceberg feature. It is Redshift stopping being a naive writer of the format and getting the semantics you expected from a warehouse.
Two writers, one snapshot, one read into the past
It answers one question: where atomicity and conflicts are resolved. Batch and streaming write to the same table; Redshift rebases at commit time and publishes one snapshot per transaction to the Glue Data Catalog. The auditor reads an earlier snapshot through the same engine. The dashed line is asynchronous maintenance, which decides how far back time travel reaches and cleans up files from aborted transactions.
Clients Nightly batch MERGE + DELETE (compute) Streaming ingest INSERT autocommit (messaging) Auditor or agent AS OF TIMESTAMP (user) Redshift Redshift RG / Serverless rebase at commit (compute) Lake Glue Data Catalog metadata pointer (data) S3 Iceberg files data + snapshots (storage) Glue optimizers retention + orphans (ci) Flows batch -> rs: BEGIN…COMMIT stream -> rs: concurrent INSERT rs -> s3: writes files rs -> glue: one snapshot per commit glue -> s3: points to metadata auditor -> rs: reads old snapshot opt -> s3: expires and cleans
Conflicts: what the engine resolves and what it hands back to you
Resolution climbs in granularity and stops as soon as it proves independence. First, by operation: if either side only appended data (INSERT), rebasing is always possible. Then, by partition: if both removed data, it checks whether they touched disjoint partitions. Finally, by file: same partition, different files, it still passes.
There is no row-level check. Two UPDATEs on different rows that land in the same data file are a conflict, and the second writer fails. That has a direct consequence for table design. An unpartitioned table with streaming plus a scheduled DELETE coexists fine, because streaming only inserts. A balances table with two jobs running concurrent MERGE only coexists if partitioning separates what each job touches. If both write to dt = current_date, you still need retry with backoff in the orchestration.
Two details I've seen catch people out. Behavior is governed by the table property write.*.isolation-level: a table set to serializable surfaces every conflict and resolves none. And Redshift's ALTER DATABASE … ISOLATION LEVEL has no effect at all on Iceberg commit conflicts, so changing it thinking it helps is a lost afternoon. Concurrent ALTER TABLE always requires a rerun.
The fleet data is what convinces me: conflicts accounted for roughly one in five failed Iceberg writes per week, and dropped to near zero while write volume more than doubled.
What resolves on its own under concurrency Your write Against concurrent INSERT Against removal or compaction What you keep in code INSERT Resolves Resolves - DELETE, UPDATE, MERGE Resolves Only if partition or file differ Retry with backoff and idempotency ALTER TABLE Requires rerun Requires rerun DDL window outside loads Serializable table Surfaces conflict Surfaces conflict All of the retry
Where it shines
- No readable intermediate state: Spark, Athena or another Redshift see the table before or after the batch, never in between. The check
SELECTinside the transaction sees its own changes beforeCOMMIT. - Fewer snapshots, less metadata: one snapshot per load instead of one per statement. With 25 small statements per commit, AWS measured 14.77% less time for
INSERTand 11.29% forDELETE. - Real concurrent throughput: with 32 writers, four-
INSERTtransactions reached 10.37x the throughput of a single autocommit writer. - Reproducible reporting: a
MERGEreadingAS OF TIMESTAMP '2026-11-01 02:00:00 UTC'returns the same total every time it runs, even as orders arrive after the cutoff. - Recovery without copies: a
DELETEplus anINSERTfrom the snapshot of one hour ago, in a single transaction, restore a whole customer without keeping a backup table.
The limits you'll hit in the first sprint: One table per transaction: you can't write the fact table and the load-control table in the same commit. COPY, UNLOAD, GRANT, ANALYZE, DML on an RMS or temp table and any Iceberg DDL abort the whole transaction. Stored procedures: Iceberg writes don't run inside a procedure, so legacy PL/pgSQL ETL gets none of this. Same name: reaching the same table through two external schemas, or through a schema and awsdatacatalog, counts as two tables, and the write through the second name fails. Behavior change: a single Iceberg DDL inside BEGIN…COMMIT used to work and is now rejected. Clients with autocommit off break on deploy.
Time travel is not a regulatory audit trail
The temptation in a financial environment is to read "auditors query the table at any point in time" and cross the traceability requirement off the list. I wouldn't, for four reasons that are in the documentation.
Reach is retention, not intent: time travel only reads snapshots that still exist. If the Glue retention optimizer expired the snapshot, the query returns an error. How far back you can see is a maintenance setting, not a guarantee.
Lake Formation is excluded: a table governed by Lake Formation, including data filters or column-level permissions, doesn't accept AS OF TIMESTAMP. That includes the s3tablescatalog catalog. In banking, the sensitive data is exactly what sits under Lake Formation. And the mismatch is odd: multi-statement transactions work on S3 Tables, time travel doesn't.
Permissions are today's: Redshift checks access as it is now, not as it was at the queried instant. For an audit of "who could see what in March", that answers nothing.
Nested schemas block it: if the snapshot being read has any struct, array or map column, the query fails, even without selecting that column. No data sharing, no AS OF VERSION by snapshot ID, no AS OF TIMESTAMP in a view definition.
AWS itself recommends keeping months or years of history in the table, with timestamp and operation columns. I agree: time travel is an operations tool for days, not a compliance archive for years.
The cost that shows up later: orphan files, snapshots and compaction
Building the transaction means writing BEGIN and COMMIT. The real cost is not that, it's keeping the lake clean and predictable for years.
Aborted transactions leave garbage: each statement writes data files and intermediate metadata to the table location before COMMIT. If the transaction fails, no snapshot references those files and they become orphans. On S3 Tables, unreferenced file removal is on by default. On the Glue Data Catalog you must enable the orphan file deletion optimizer, and it only removes files created after it was enabled. Turn it on before the first deploy, not after you notice the S3 bill.
Retention is a three-way trade-off: each retained snapshot makes time travel planning slower and holds in storage the files only it references. Retention too short breaks recovery; too long charges you in S3 and in planning latency. Set it from the real requirement: if your window to detect a bad load is 48 hours, retain a bit more than that.
Old snapshots read slowly: a snapshot from before compaction carries the small files it was written with. Querying the table as of seven days ago can cost more than querying the current one. Regular compaction through the Glue or S3 Tables optimizer is part of the bill.
Recovery also undoes other people's work: restoring from one hour ago erases what other writers wrote in that hour. Pick the instant right before the bad load and filter by its scope.
How I would adopt it
- Confirm the ground: RG or Serverless cluster, patch 206 or later, table in the Glue Data Catalog or S3 Tables. RA3 is outside the announcement.
- Turn on maintenance first: On Glue, enable orphan deletion, snapshot retention with a window derived from the recovery requirement, and compaction. Without it, the first aborted transaction already leaves permanent garbage.
- Standardize one name per table: Pick one external schema per Iceberg table and forbid access through awsdatacatalog in write jobs. Review with grep before turning it on.
- Rewrite the load as one gated transaction:
MERGE,DELETE, a checkSELECTagainst the control total, thenCOMMITorROLLBACK. The load-control table is written afterwards in its own transaction, with an idempotent key per run. - Design partitioning for conflicts: If two jobs remove data from the same table, partitioning must separate what each one touches. Keep exponential backoff retry for the remaining conflicts and move DDL to a window outside loads.
- Pin the instant in reports: Use a timestamp literal with a time zone, such as
'2026-11-01 02:00:00 UTC'.CURRENT_TIMESTAMPmoves on each run,GETDATEisn't accepted, and a prepared statement with a moving instant returns an error.
Anti-patterns
- Treating time travel as backup: an expired snapshot doesn't come back. For data that must survive, persist it with
CREATE TABLE ASfrom the snapshot, or keep history in the table itself. - Assuming cross-table atomicity: fact and dimension don't commit together. Consumers can see the new fact with the old dimension, so the read contract must tolerate it.
- Removing retries because it resolves itself now: concurrent
UPDATEs on the same file still fail. Without idempotent retries, the 2 AM load dies silently on the first collision. - Trusting database isolation: changing
ALTER DATABASE … ISOLATION LEVELchanges nothing for Iceberg. What governs it iswrite.*.isolation-levelon the table.
Curator's note: I would adopt it now for the single-table loads that today use staging plus a swap just to hide intermediate state: all that engineering becomes a BEGIN and a COMMIT. What I wouldn't do is present time travel to the risk team as an audit trail, because the most sensitive table usually sits under Lake Formation and it doesn't work there. The lesson I carry from financial platforms is that the cost of a consistency feature isn't on the day you turn it on, it's in the orphan file, the retained snapshot and the retry someone removed months later. Turn on Glue maintenance before the first transaction, not after the first bill.
References
AWS What's New: Amazon Redshift now supports multi-statement transactions, conflict resolution, and time travel for Apac Amazon Redshift Developer Guide: Transaction semantics for Iceberg writes Amazon Redshift Developer Guide: Time travel queries on Iceberg tables AWS Big Data Blog: Multi-statement transactions for Apache Iceberg tables in Amazon Redshift AWS Big Data Blog: Resolving concurrent write conflicts for Apache Iceberg tables in Amazon Redshift AWS Glue Developer Guide: Snapshot retention optimization
Verdict
Adopt when the load writes to one Iceberg table, the cluster is RG or Serverless, and today's problem is visible intermediate state or failing concurrent commits: that's the most common case and the gain is immediate. Wait or complement when you need atomicity across fact and dimension, when ETL lives in stored procedures, or when the table sits under Lake Formation and the requirement is long-term audit. There the design stays yours: one transaction per table with an idempotent key, and regulatory history as data in the table.
AWS closed the biggest semantic gap between Redshift-on-Iceberg and a real warehouse; the remaining ones are documented and can be worked around.
Rating: 8/10 Originally published at fernando.moretes.com. By Fernando F. Azevedo: Senior Solutions Architect.
Anyone who has run a nightly load on a data lake knows the incident: the MERGE finished, the DELETE for voided sales failed, and for forty minutes the revenue dashboard showed
Comments
No comments yet. Start the discussion.