What are ACID transactions on a data lake?

Summary

• ACID transactions let a data lake table commit a change as one unit: a write either becomes part of the table or it doesn't, and readers see a consistent snapshot.
• Table formats supply this over files. Delta Lake adds a commit entry to a transaction log in _delta_log. Apache Iceberg commits by atomically swapping the table's metadata file.
• Both use optimistic concurrency. A conflicting Delta Lake write fails with an exception, and an Iceberg writer retries on the new current version.
• Isolation depends on the system: open-source Delta Lake supports Serializable, while Databricks defaults to write-serializable isolation on writes.
• Storage matters too. On Amazon S3, open-source Delta Lake needs writes from a single Spark driver unless you set up the experimental multi-cluster mode.

What are ACID transactions on a data lake?

ACID stands for atomicity, consistency, isolation and durability. The Databricks docs define them: atomicity means that "all transactions either succeed or fail completely", consistency guarantees "relate to how a given state of the data is observed by simultaneous operations", isolation "refers to how simultaneous operations potentially conflict with one another", and durability means that "committed changes are permanent". On a data lake, a table format such as Delta Lake or Apache Iceberg supplies these guarantees for files in object storage. The docs add that "specific guarantees vary by system".

Why do data lake tables need them?

The Delta Lake docs describe the problem: when writes conflict, the write fails "rather than corrupting the table as would happen with the write operation on a Parquet table". The Iceberg spec describes the alternative: its format "tracks individual data files in a table instead of directories", adds files to the table "in an explicit commit", and sets the goal that writes "are never partially visible. Readers will not acquire locks."

How do Delta Lake and Apache Iceberg provide ACID?

	Delta Lake	Apache Iceberg
Where a commit is recorded	A new entry in the transaction log, JSON files in the _delta_log directory	A new table metadata file, swapped in for the old one with an atomic swap
What readers see	"a consistent snapshot view of the table"	"a committed snapshot of a table's data"
Concurrent writers	Optimistic concurrency control: read, write, then validate and commit. A conflict fails with a concurrent modification exception	Optimistic concurrency. If the base snapshot is no longer current, the writer must retry the update on the new current version
When data files join the table	When the transaction log records a new version	In an explicit commit

What does a Delta Lake write look like?

import pyspark
from delta import *

builder = pyspark.sql.SparkSession.builder.appName("MyApp") \
    .config("spark.sql.extensions", "io.delta.sql.DeltaSparkSessionExtension") \
    .config("spark.sql.catalog.spark_catalog", "org.apache.spark.sql.delta.catalog.DeltaCatalog")

spark = configure_spark_with_delta_pip(builder).getOrCreate()

# Append rows to a Delta table
data = spark.range(0, 5)
data.write.format("delta").mode("append").save("/tmp/delta-table")

# Read the table: readers see a consistent snapshot
spark.read.format("delta").load("/tmp/delta-table").show()

# Read an older version (time travel)
spark.read.format("delta").option("versionAsOf", 0).load("/tmp/delta-table").show()

What happens when a write fails or two writers collide?

If a transaction fails after writing data files, the Databricks docs say these files "will not corrupt the table state, but the files will not become part of the table". VACUUM deletes untracked data files, including leftovers from failed transactions. In open-source Delta Lake, data files "are deleted only when you run VACUUM".
Delta Lake's conflict table says two concurrent INSERTs cannot conflict, while UPDATE, DELETE and MERGE INTO can conflict with each other and with INSERT. Whether they do depends on whether they touch the same files, so partitioning by the columns used in the operations' conditions reduces conflicts. Errors include ConcurrentAppendException.

Which isolation level applies?

The open-source Delta Lake docs say "Serializable isolation levels ensure that readers never see inconsistent data", and its configuration code says "Delta currently supports one isolation level: Serializable". On Databricks, "snapshot isolation on reads and write-serializable isolation on writes" is the default. WriteSerializable "ensures that only write operations (not reads) are serializable", and Serializable is the strongest level. On Databricks you can set it per table:

ALTER TABLE <table-name> SET TBLPROPERTIES ('delta.isolationLevel' = <level-name>)

In Iceberg, "the conditions required by a write to successfully commit determines the isolation level."

What do storage and catalogs have to provide?

Delta Lake's ACID guarantees depend on atomic visibility, mutual exclusion and consistent listing from the storage system. Azure, Google Cloud Storage and HDFS have built-in support with full transactional guarantees for concurrent reads and writes from multiple clusters. On S3, by default "concurrent writes to S3 must originate from a single Spark driver". The multi-cluster mode uses DynamoDB "to provide the mutual exclusion that S3 is lacking", and the docs call it "new and experimental".
Iceberg's atomic swap can be implemented by storing a pointer in a metastore or database that is updated with a check-and-put operation. Its file-system scheme is deprecated and "unsafe in object stores".

How does Databricks approach this?

"Databricks uses Delta Lake by default for all reads and writes", and each SQL statement "executes as its own atomic transaction against one table". Predictive optimization "automatically runs OPTIMIZE, VACUUM, and ANALYZE on Unity Catalog managed tables (Delta Lake and Iceberg)". Iceberg reads (UniForm) generate Iceberg metadata so Iceberg clients can read Delta Lake tables without rewriting files. Iceberg client support is read-only. The docs warn: "Other data formats and integrated systems might not provide transactional guarantees for reads and writes."

FAQs

Can I time travel to any old version?

No. "To time travel to a previous version, you must retain both the log and the data files for that version." By default you can time travel up to 30 days unless you have run VACUUM or changed retention. delta.logRetentionDuration (default interval 30 days) controls how long history is kept.

Who governs Delta Lake?

The Delta Lake site describes it as "an independent open-source project and not controlled by any single company". It joined the Delta Lake Project in 2019, "a sub-project of the Linux Foundation Projects".