Amazon Redshift will no longer support the use of Python UDFs after June 30, 2026. We will start enforcing it in phases. For more information on the details of Python end of life and migration options, see the blog post that was published on June 30, 2025.

Transaction semantics

Amazon Redshift Iceberg write queries support ACID transactions. Write transactions are atomic: when a query or transaction fails, Amazon Redshift doesn't publish a partial update to the table.

By default, each Iceberg write query runs as its own auto-commit transaction, and Amazon Redshift publishes its changes as one Iceberg snapshot when the query finishes. You can also group multiple data manipulation language (DML) statements that write to one Iceberg table into a multi-statement transaction. Amazon Redshift then publishes all of the transaction's changes as one Iceberg snapshot when the transaction commits.

The following example creates a table with a single auto-commit statement:

CREATE TABLE sales_schema.orders (
    order_id int,
    customer_id int,
    order_date date,
    total_amount decimal(10,2)
) USING ICEBERG LOCATION 's3://my-data-lake/sales/orders/';

Multi-statement transactions

You can run a multi-statement transaction in any of the following ways:

The following example applies a nightly batch of point-of-sale (POS) changes to a sales table. The MERGE statement adds new sales and corrects existing ones from the pos_sales Iceberg staging table. The DELETE statement removes the voided sales listed in the pos_voids Iceberg staging table. The SELECT statement checks the day's total before the transaction commits.

BEGIN;

-- Add new sales and correct existing ones
MERGE INTO iceberg_schema.sales USING iceberg_schema.pos_sales p
  ON sales.sale_id = p.sale_id
  WHEN MATCHED THEN UPDATE SET amount = p.amount
  WHEN NOT MATCHED THEN INSERT VALUES (p.sale_id, p.store_id, p.sale_date, p.amount);

-- Remove voided sales
DELETE FROM iceberg_schema.sales
  WHERE sale_id IN (SELECT sale_id FROM iceberg_schema.pos_voids);

-- Check the day's total, which includes both changes
SELECT sum(amount) FROM iceberg_schema.sales
  WHERE sale_date = '2026-10-06';

-- COMMIT if the total is correct; otherwise ROLLBACK
COMMIT;

The SELECT statement sees the changes from the MERGE and DELETE statements, because reads of the table that a transaction writes include the transaction's uncommitted changes. Other sessions and other engines don't see any of the changes until COMMIT. They see the table as it was either before the batch or after it, but never in between.

If any statement in the transaction fails, or if you end the transaction with ROLLBACK, Amazon Redshift doesn't publish any of the transaction's changes. The following example inserts an order, updates it, and then rolls back both changes:

BEGIN;
INSERT INTO "analytics-bucket@s3tablescatalog".sales_db.orders VALUES (12346, 5432, '2026-10-06', 150.75);
UPDATE "analytics-bucket@s3tablescatalog".sales_db.orders SET total_amount = 160.75 WHERE order_id = 12346;
ROLLBACK;  -- discards both the INSERT and the UPDATE

Statements allowed in a multi-statement transaction

A transaction that writes to an Iceberg table can read from any tables, but it can write to only that one Iceberg table. The following table shows which statements you can run in a transaction that contains an Iceberg write.

When you run a statement that isn't allowed, the statement fails and Amazon Redshift aborts the transaction. None of the changes made earlier in the transaction are published. In the following example, the second DELETE statement fails because it writes to a second Iceberg table:

BEGIN;
DELETE FROM iceberg_schema.sales
  WHERE sale_id IN (SELECT sale_id FROM iceberg_schema.pos_voids);
DELETE FROM iceberg_schema.pos_voids;  -- fails: a second Iceberg table
COMMIT;                                -- rolls back

To write to another table, run that write in its own transaction, before or after the Iceberg transaction. Run Iceberg DDL statements, such as CREATE TABLE and ALTER TABLE, as standalone statements outside a transaction block. If your client turns off autocommit, turn it on before you run Iceberg DDL statements.

Referencing the table in a transaction

Amazon Redshift identifies an Iceberg table in a transaction by the name that you use to reference it. Use the same name for the table throughout the transaction. If you reference one table through two names, for example through two external schemas, or through an external schema and the awsdatacatalog catalog, Amazon Redshift treats the two names as two different tables. A write through the second name fails, and a read through the second name doesn't include the transaction's uncommitted changes. For more information, see Referencing Iceberg tables in Amazon Redshift.

Isolation guarantees

A transaction on Iceberg tables gives you the following guarantees:

Amazon Redshift fixes the version of each Iceberg table when the transaction first reads or writes that table, not when the transaction begins. If a transaction first accesses an Iceberg table later in the transaction, that read can include changes that other sessions committed after the transaction started.

Reads of RMS tables in the same transaction use the Amazon Redshift transaction snapshot, so they might not reflect the same point in time as reads of Iceberg tables. For more information, see Isolation levels in Amazon Redshift.

Files from transactions that don't commit

As each statement in a multi-statement transaction runs, Amazon Redshift writes data files and intermediate metadata files to the table location. If the transaction doesn't commit, no snapshot references these files, and they become orphan files. To remove them, set up orphan file removal for the table. For more information, see Best practices.

Troubleshooting multi-statement transactions

If a statement in a multi-statement transaction fails, Amazon Redshift aborts the transaction and doesn't publish any of its changes. The following are common errors and how to resolve them:

Resolving concurrent write conflicts

Multiple sessions and engines can write to the same Iceberg table at the same time. If another writer commits to the table while your write is running, and the two sets of changes don't overlap, Amazon Redshift can rebase your changes onto the latest version of the table and commit them without running the write again. If Amazon Redshift can't rebase your changes, for example because both writes changed the same data, the commit fails. When this happens, run the write again. For more information, see Resolving concurrent write conflicts for Apache Iceberg tables in Amazon Redshift.