Atomic transactions for Delta Lake and Apache Iceberg tables in Databricks

Databricks has made multi-table SQL transactions generally available (GA) for Unity Catalog managed Delta Lake and Apache Iceberg tables. This feature groups commands across one or more managed tables into a single atomic transaction, ensuring all changes commit together or fail together.

Use cases

Imagine updating an inventory table and writing a sales record to another table within the same business event. If one statement fails, your data becomes inconsistent. With transactions, you can:

• Group multiple SQL statements across several tables into a single atomic commit.
• Maintain ACID guarantees across different tables managed by Unity Catalog.
• Work across Delta Lake and Apache Iceberg formats simultaneously.

How to use

Participating tables must have catalog commits enabled using the delta.feature.catalogManaged table property.

First, create a new table with the property or update an existing managed table:

-- Creating a new managed table
CREATE TABLE meu_catalogo.vendas.pedidos (
  pedido_id BIGINT,
  valor DECIMAL(10,2),
  data_pedido DATE
)
TBLPROPERTIES ('delta.feature.catalogManaged' = 'supported');

-- Enabling on an existing managed table
ALTER TABLE meu_catalogo.vendas.estoque 
SET TBLPROPERTIES ('delta.feature.catalogManaged' = 'supported');

What happens in this code:

1. The first statement creates the pedidos table with commits coordinated through Unity Catalog.
2. The second statement updates the existing estoque table to enable catalog-managed commits.

With catalog commits enabled on the participating tables, execute the transaction using BEGIN TRANSACTION or BEGIN ATOMIC:

BEGIN TRANSACTION;

INSERT INTO meu_catalogo.vendas.pedidos VALUES (101, 150.00, current_date());
UPDATE meu_catalogo.vendas.estoque SET quantidade = quantidade - 1 WHERE produto_id = 42;

COMMIT;

What happens in this code:

1. The BEGIN TRANSACTION statement initiates the transaction boundary.
2. The INSERT and UPDATE statements are staged.
3. The COMMIT statement applies changes to both tables atomically; if any statement fails, changes are aborted.

To check if a table has catalog commits enabled:

DESCRIBE DETAIL meu_catalogo.vendas.pedidos;

Limitations and requirements

• Multi-table transactions are GA, while the underlying Catalog Commits feature is listed in Beta.
• Tables must be Unity Catalog managed tables (Delta or Iceberg), streaming tables, or materialized views.
• Databricks Runtime 16.4 or above is required to read, write, or create managed tables with catalog commits.
• Databricks Runtime 18.0 or above is required to enable or disable catalog commits on existing managed tables.
• For streaming tables and materialized views, Databricks Runtime 17.3 or above (or serverless compute in Spark Declarative Pipelines) is required to read, write, or create objects, and Databricks Runtime 18 LTS or above is needed to disable the feature.
• You cannot turn catalog commits on or off on existing tables using CREATE OR REPLACE TABLE or REPLACE TABLE; use ALTER TABLE instead.
• Single-user clusters cannot access streaming tables that have catalog commits enabled.

Using it in a pipeline

In Gold or Silver data pipelines involving financial reconciliations or order fulfillment, wrap downstream dimension and fact updates in a transaction block to prevent reporting queries from seeing mismatched data states.