Delta Lake: What ACID Transactions Look Like When They're Not in SQL Server

Shannon Lowder

Shannon Lowder

— 3 min read

SQL Server has always had ACID transactions. You write BEGIN TRANSACTION, do your work, COMMIT or ROLLBACK. The database handles the rest: concurrent reads see consistent data, failed writes don't leave partial state, and committed data survives a crash. It's so fundamental to how SQL Server works that you stop thinking about it as a feature. It just is.

Then you move to a distributed file system and discover that Parquet files have exactly zero of this. Write 10 partitions to an S3 path, crash after partition 7, and you have 7 out of 10 partitions sitting there. No rollback. No recovery. Just partial data and a pipeline that has to figure out what to do about it. Delta Lake exists to solve this.

What Delta Lake Is

Delta Lake is an open-source storage layer that sits on top of Parquet files and adds a transaction log. When you write to a Delta table, two things happen: (1) the data files get written as Parquet, and (2) a JSON entry gets added to the _delta_log directory describing what changed. That log entry is the commit. If the job crashes before the log entry is written, the Parquet files exist but Delta ignores them — they're orphaned, not committed.

# Reading a Delta table
df = spark.read.format("delta").load("abfss://container@storage.dfs.core.windows.net/tables/orders/")

# Writing as Delta (overwrite mode)
df.write.format("delta").mode("overwrite").save("abfss://container@storage.dfs.core.windows.net/tables/orders/")

# Or if registered in the Hive Metastore
df.write.format("delta").mode("overwrite").saveAsTable("analytics.orders")

The Transaction Log Up Close

The _delta_log directory contains JSON files numbered sequentially: 00000000000000000000.json, 00000000000000000001.json, and so on. Each file represents one operation (a write, a MERGE, an OPTIMIZE, a schema change). Open one and you'll see it describes exactly which Parquet files were added or removed, what the schema is, and what operation produced it.

-- See the history of a Delta table
DESCRIBE HISTORY analytics.orders;

-- Returns: version, timestamp, operation, operationParameters, operationMetrics

Every 10 commits, Delta checkpoints the log by rolling up the full current state into a Parquet file (also in _delta_log). This prevents readers from having to scan every JSON file back to version 0 on every read.

ACID, Spelled Out

Atomicity: A write either commits fully (log entry written) or not at all. No partial table states.

Consistency: Delta enforces schema on write by default. Try to append a DataFrame with a column of the wrong type and it throws an error instead of silently corrupting the table. You can relax this with mergeSchema for intentional schema evolution.

Isolation: Concurrent readers always see a consistent snapshot. While a write is in progress, readers see the previous committed version. Snapshot isolation, same as SQL Server's READ COMMITTED SNAPSHOT.

Durability: Once the _delta_log entry is written to cloud storage, the commit is permanent. Cloud object stores (ADLS, S3) provide durability guarantees for their PUT operations.

Time Travel

Because every version is preserved in the log, you can query the table as it existed at any past version or timestamp:

# By version number
df_v3 = spark.read.format("delta")     .option("versionAsOf", 3)     .load("abfss://container@storage.dfs.core.windows.net/tables/orders/")

# By timestamp
df_july1 = spark.read.format("delta")     .option("timestampAsOf", "2020-07-01")     .load("abfss://container@storage.dfs.core.windows.net/tables/orders/")

# Or in SQL
spark.sql("SELECT * FROM analytics.orders VERSION AS OF 3")
spark.sql("SELECT * FROM analytics.orders TIMESTAMP AS OF '2020-07-01'")

This is what SQL Server's temporal tables give you, except Delta's time travel works on the full table including historical file versions. The retention window is controlled by delta.logRetentionDuration and delta.deletedFileRetentionDuration — the default is 30 days for the log and 7 days for deleted data files.

VACUUM: Cleaning Up Old Versions

Old Parquet files (from previous table versions) accumulate on disk. They're preserved for time travel but take up space. VACUUM removes files older than the retention threshold:

-- Remove files older than 7 days (default)
VACUUM analytics.orders;

-- Remove files older than 30 days
VACUUM analytics.orders RETAIN 720 HOURS;

After VACUUM runs, you can no longer time-travel past the retention window. This is the same tradeoff as SQL Server log truncation — you're trading history for reclaimed storage.

Delta Lake is the format I use for every production table in Databricks. The ACID guarantees alone are worth it over plain Parquet. The time travel capability has already saved me twice when a bad pipeline run corrupted data and I needed to see the pre-corruption state. Consider plain Parquet read-only input only — for anything you're writing to, use Delta.

Read more