Implementing Delta Lake Time Travel in a Fabric Lakehouse: Querying Historical Snapshots, Rolling Back Bad Writes, and Auditing Table Changes with PySpark and T-SQL | Wicked Smart Data
Implementing Delta Lake Time Travel in a Fabric Lakehouse: Querying Historical Snapshots, Rolling Back Bad Writes, and Auditing Table Changes with PySpark and T-SQL
Delta Lake time travel turns catastrophic data overwrites into two-minute recoveries. Learn how to query historical snapshots by version and timestamp, execute safe rollbacks with RESTORE TABLE, build a persistent audit trail, and manage retention windows so time travel is available when you actually need it.
🔥 Expert31 min readSep 22, 2026Updated Sep 22, 2026
Implementing Delta Lake Time Travel in a Fabric Lakehouse: Querying Historical Snapshots, Rolling Back Bad Writes, and Auditing Table Changes with PySpark and T-SQL
Introduction
Picture this: it's 9:47 AM on a Tuesday, and your phone is ringing. The sales team is screaming because the revenue numbers in the morning dashboard are wrong — way wrong. Someone ran a transformation job overnight that overwrote the gold_sales_orders table with data that had a bad join condition, effectively multiplying unit prices by a factor of ten. The Power BI report connected in Direct Lake mode picked up the corrupt data immediately, and now every executive with a mobile app is staring at numbers that suggest the company had its best quarter in history. Except it didn't.
In a traditional data warehouse, your options at this point are uncomfortable: restore from last night's backup (losing everything ingested since), manually reconstruct the correct state from source systems (hours of work), or issue a correction factor and apologize profusely. But if your tables live in a Fabric Lakehouse as Delta Lake format — and they should — you have a fourth option that takes about two minutes. Delta Lake's time travel feature gives every table a complete, queryable transaction history. You can read the table as it existed at any prior version or timestamp, compare what changed between versions, roll back to a known-good state, and audit exactly what operations touched the data and when.
By the end of this lesson, you'll know how to exploit that capability fully. We'll go deep on the Delta transaction log mechanics so you understand why time travel works, not just the commands that invoke it. We'll cover querying historical snapshots in both PySpark and T-SQL via the SQL Analytics Endpoint, implement a systematic rollback workflow for production incidents, build an audit query that surfaces every structural change to a table over its lifetime, and tackle the operational concerns — retention windows, VACUUM, and the tension between storage cost and recovery capability — that determine whether time travel is actually available when you need it.
What you'll learn:
How the Delta Lake transaction log (_delta_log) enables time travel at a mechanical level, and what that means for your Fabric Lakehouse storage in OneLake
Querying historical table snapshots by version number and timestamp using PySpark spark.read and the @v{n} / FOR SYSTEM_TIME AS OF T-SQL syntax
Implementing a safe, auditable rollback pattern using RESTORE TABLE and INSERT OVERWRITE strategies with proper verification steps
Reading and interpreting the Delta transaction log directly to produce a human-readable audit trail of all DML and DDL operations
Configuring and managing data retention windows so time travel remains available without bankrupting your OneLake storage budget
Before you can use time travel confidently, you need a mental model of the machinery underneath. Cargo-cult usage of VERSION AS OF will eventually burn you when you hit an edge case and don't understand why.
Every Delta table is a directory in OneLake. Inside that directory you'll find Parquet data files (the actual row data) and a subdirectory called _delta_log. The _delta_log is the heart of Delta Lake. It contains a sequential series of JSON files — 00000000000000000000.json, 00000000000000000001.json, and so on — where each file represents one committed transaction (one version of the table).
Each JSON commit file records:
The operation that was performed (WRITE, MERGE, DELETE, UPDATE, OPTIMIZE, RESTORE, etc.)
The add actions: which Parquet files were added to the table by this transaction
The remove actions: which Parquet files are no longer part of the logical table after this transaction (but are still physically present on disk for time travel purposes)
Operation parameters: the mode (overwrite vs. append), the predicate for a delete, the merge condition, etc.
A timestamp for when the commit completed
This is the critical insight: Delta Lake never immediately deletes old Parquet files. When you run UPDATE or DELETE or INSERT OVERWRITE, Delta rewrites the affected files and adds new ones. The old files are "removed" logically (via a remove action in the commit log) but remain physically present in OneLake storage. Time travel works by reading a specific version of the commit log and identifying which set of Parquet files constituted the table at that point in time — then reading only those files.
Key insight
Time travel doesn't store full table snapshots at each version. It stores a log of file additions and removals. Reading a historical version means reconstructing "which files were live" by replaying the log up to that version. This is extremely space-efficient for tables with low churn, but for tables with frequent full overwrites, you'll accumulate many orphaned files quickly.
After 10 commits, Delta writes a checkpoint file (a Parquet file that summarizes the cumulative state of the log). Checkpoints exist for performance — replaying 10,000 individual JSON files to open a table would be unacceptably slow. For your time travel queries, checkpoints are mostly transparent, but they matter for the lower bound of the retention window.
The physical Parquet files that were "removed" by later transactions are the ones that make time travel possible. They are also the ones that VACUUM will delete when they age out of your retention window. This is why VACUUM and time travel are in direct tension, and we'll spend significant time on that later.
Setting Up a Realistic Demo Table
Let's create a table that has a real history to explore. We'll work with a gold_sales_orders table representing aggregated daily order data — the kind of table that lives in your gold layer and feeds Power BI reports directly.
Open a Spark Notebook in your Fabric Lakehouse and attach it. Run the following cells to build the table and simulate several realistic operations against it:
# ---- Version 2: Correct Electronics AMER revenue for Jan 1 (was understated) ----
dt = DeltaTable.forPath(spark, "Tables/gold_sales_orders")
dt.update(
condition="order_date = '2024-01-01' AND category = 'Electronics' AND region = 'AMER'",
set={"revenue": lit(1_312_750.00)}
)
print("Version 2 written: revenue correction for Electronics AMER Jan 1")
# ---- Version 3: THE BAD WRITE ----
# Simulate the overnight job that accidentally multiplied all revenues by 10
df_corrupted = spark.read.format("delta").load("Tables/gold_sales_orders")
df_corrupted = df_corrupted.withColumn("revenue", col("revenue") * 10)
df_corrupted.write.format("delta").mode("overwrite").save(
"Tables/gold_sales_orders"
)
print("Version 3 written: CORRUPTED DATA — revenue multiplied by 10!")
Now you have a four-version history (versions 0 through 3) with a clear bad write at version 3. Let's verify what we're working with:
# Confirm current state is corrupted
spark.read.format("delta").load("Tables/gold_sales_orders").show()
You'll see revenue figures that are an order of magnitude too high. Perfect — now let's learn how to deal with it.
Reading the Transaction History: DESCRIBE HISTORY
The first tool you reach for in any time travel scenario is DESCRIBE HISTORY. It reads the commit log and returns a structured summary of every operation ever performed on the table, along with timestamps, user information, operation parameters, and operational metrics.
Note several things about this output that matter for real incident response:
The history is newest-first. Version 3 is at the top. In a production table with hundreds of versions, you'll want to filter by timestamp range rather than scanning everything.
operationMetrics tells you the blast radius. For the bad write at version 3, numOutputRows -> 9 tells you it touched all 9 rows. A partial corruption (say, only AMER rows were affected) would show fewer updated rows.
userName is recorded automatically. In Fabric, this reflects the identity of the Entra ID principal that executed the notebook or pipeline. This is your built-in audit trail for "who did this."
operationParameters for overwrites records the mode. This is how you distinguish an accidental overwrite from an intentional one in your audit log.
Tip
For production audit purposes, persist the history to a separate audit lakehouse table on a scheduled basis. The DESCRIBE HISTORY data only goes as far back as your retention window allows — once old commits are vacuumed, that history data is gone. Use a data pipeline to capture it nightly.
Querying Historical Snapshots with PySpark
Once you know which version you want, reading it is straightforward. PySpark's Delta reader supports two modes of time travel: by version number and by timestamp.
Querying by Version Number
# Read the table as it existed after version 2 (pre-corruption, post-correction)
df_v2 = (
spark.read
.format("delta")
.option("versionAsOf", 2)
.load("Tables/gold_sales_orders")
)
print(f"Row count at version 2: {df_v2.count()}")
df_v2.show()
This gives you the clean state: 9 rows, with the corrected Electronics AMER Jan 1 revenue of 1,312,750.00.
# You can also query version 0 to see the original load
df_v0 = (
spark.read
.format("delta")
.option("versionAsOf", 0)
.load("Tables/gold_sales_orders")
)
# Compare revenue totals between v0 and v2 to verify the correction landed
from pyspark.sql.functions import sum as _sum
v0_total = df_v0.agg(_sum("revenue").alias("total_revenue")).collect()[0]["total_revenue"]
v2_total = df_v2.agg(_sum("revenue").alias("total_revenue")).collect()[0]["total_revenue"]
print(f"Revenue total at v0: ${v0_total:,.2f}")
print(f"Revenue total at v2: ${v2_total:,.2f}")
print(f"Difference (the correction): ${v2_total - v0_total:,.2f}")
Querying by Timestamp
Version numbers are precise but require you to know them. In practice, you often know when the data was good, not which version number that corresponds to. Timestamp-based time travel handles this:
# Read the table as it was at a specific point in time
# Use a timestamp just before the bad write occurred
clean_timestamp = "2024-06-15 09:32:00" # adjust to your actual timestamps
df_clean = (
spark.read
.format("delta")
.option("timestampAsOf", clean_timestamp)
.load("Tables/gold_sales_orders")
)
df_clean.show()
Warning
When using timestampAsOf, Delta reads the most recent version that was committed at or before the specified timestamp. This is not the same as "the version that was live at that time." If a transaction committed at exactly your timestamp, you'll get that version. Use a timestamp a few seconds before the bad operation to be safe, and always verify the result against your history output before taking any rollback action.
Comparing Two Versions Side by Side
A very useful pattern for incident investigation is computing the diff between two versions. This tells you exactly what changed:
This is the smoking gun you show stakeholders. A change_factor of exactly 10.0 for every row confirms the multiplication bug and removes any ambiguity about the scope of impact.
Querying Historical Snapshots with T-SQL
Everything above works in PySpark, but many data teams use the SQL Analytics Endpoint to query Lakehouse tables with T-SQL — especially analysts who aren't Spark practitioners. The good news is that the SQL Analytics Endpoint exposes Delta time travel through standard SQL syntax that will feel familiar if you've ever used temporal tables in SQL Server.
Open the SQL Analytics Endpoint for your Lakehouse (accessible from the Lakehouse explorer by switching to the "SQL analytics endpoint" mode), then run:
-- Query table at a specific version number
SELECT *
FROM gold_sales_orders
FOR SYSTEM_VERSION AS OF 2;
-- Query table at a specific timestamp
SELECT *
FROM gold_sales_orders
FOR SYSTEM_TIME AS OF '2024-06-15T09:32:00.000';
-- Compute revenue totals at version 2 vs version 3 to confirm the corruption
SELECT
'Version 2 (clean)' AS snapshot,
SUM(revenue) AS total_revenue,
COUNT(*) AS row_count
FROM gold_sales_orders
FOR SYSTEM_VERSION AS OF 2
UNION ALL
SELECT
'Version 3 (corrupt)' AS snapshot,
SUM(revenue) AS total_revenue,
COUNT(*) AS row_count
FROM gold_sales_orders
FOR SYSTEM_VERSION AS OF 3;
-- Find rows that changed between versions
-- (available in T-SQL via CTEs)
WITH clean AS (
SELECT order_date, category, region, revenue AS revenue_clean
FROM gold_sales_orders FOR SYSTEM_VERSION AS OF 2
),
corrupted AS (
SELECT order_date, category, region, revenue AS revenue_corrupt
FROM gold_sales_orders FOR SYSTEM_VERSION AS OF 3
)
SELECT
c.order_date,
c.category,
c.region,
c.revenue_clean,
d.revenue_corrupt,
d.revenue_corrupt - c.revenue_clean AS overstatement
FROM clean c
JOIN corrupted d
ON c.order_date = d.order_date
AND c.category = d.category
AND c.region = d.region
WHERE c.revenue_clean <> d.revenue_corrupt
ORDER BY overstatement DESC;
Note
The T-SQL time travel syntax in Fabric uses FOR SYSTEM_VERSION AS OF {n} for version-based queries and FOR SYSTEM_TIME AS OF '{timestamp}' for timestamp-based queries. This is not the same syntax as SQL Server's system-versioned temporal tables — FOR SYSTEM_TIME AS OF in SQL Server queries a history table, but in Fabric's SQL Analytics Endpoint it queries the Delta transaction log. Don't conflate the two; the semantics are Delta-based even though the SQL surface looks similar.
If you're regularly querying historical data via T-SQL for reporting or audit purposes, you can wrap these in views — but be aware that the SQL Analytics Endpoint for Lakehouses does not support creating views with parameterized time travel syntax. You'll need to hardcode version numbers in views, which makes them ephemeral artifacts rather than permanent structures. For audit dashboards, query directly from T-SQL with dynamic versions rather than pre-building views. For a deeper treatment of what you can and can't do with T-SQL against a Lakehouse, see Querying Lakehouse Data with the SQL Analytics Endpoint: Writing T-SQL Against Delta Tables Without a Warehouse.
Rolling Back a Bad Write: The Right Way
Understanding how to roll back is important, but equally important is when to commit to a rollback versus just querying the historical data. Here's a framework for thinking about it:
Verify the corruption scope using the version diff approach above before touching anything
Confirm the target clean version — make sure v2 is genuinely clean and not itself a product of an earlier bad operation
Notify stakeholders that the rollback is happening and approximately when the table will be in a consistent state
Execute the rollback using the appropriate method
Verify the result against known-good metrics before declaring success
Document the incident including the version range affected and the remediation action taken
Method 1: RESTORE TABLE (Preferred)
Delta Lake provides a first-class RESTORE TABLE command that rewinds the table to a prior version. This is the cleanest approach because it creates a new commit in the transaction log (so the rollback itself is auditable) and handles all the file pointer bookkeeping correctly.
from delta.tables import DeltaTable
dt = DeltaTable.forPath(spark, "Tables/gold_sales_orders")
# Restore to version 2 — the last known-good state
restore_result = dt.restoreToVersion(2)
restore_result.show()
You can also restore by timestamp:
# Restore to the state the table was in at a specific time
dt.restoreToTimestamp("2024-06-15 09:32:00")
After the restore, verify:
# Confirm version 4 now exists (the restore is its own commit)
dt.history().select("version", "timestamp", "operation").show()
# Verify row counts and revenue totals match what we expected from v2
df_restored = spark.read.format("delta").load("Tables/gold_sales_orders")
df_restored.agg(_sum("revenue").alias("total"), _sum("order_count").alias("orders")).show()
The history output will now show a RESTORE operation at version 4, with operationParameters recording that the restore targeted version 2. This is your audit trail — anyone querying the history will see that a rollback occurred, when it happened, and to which version it rolled back.
Key insight
RESTORE TABLE does not delete the corrupt data permanently. Version 3 remains accessible via versionAsOf=3 until it ages out of the retention window. This is both a safety net (you can still query the corrupt state to understand it) and a compliance consideration (if the corrupt data contained PII that was accidentally exposed, it's still readable via time travel until VACUUM removes it).
Method 2: INSERT OVERWRITE from a Historical Read
For cases where RESTORE TABLE isn't available (older Delta versions, or when you want finer-grained control — say, restoring only certain partitions), you can manually write the historical state back:
# Read the clean state
df_clean = (
spark.read
.format("delta")
.option("versionAsOf", 2)
.load("Tables/gold_sales_orders")
)
# Overwrite the table with the clean data
# This creates a new version (not a restore — the operation will show as WRITE)
df_clean.write.format("delta").mode("overwrite").save("Tables/gold_sales_orders")
This approach is subtly different from RESTORE TABLE: the new commit shows as WRITE rather than RESTORE in the history, which is less informative for auditors. It also doesn't preserve Delta table properties or partition layout the way RESTORE TABLE does. Prefer RESTORE TABLE unless you have a specific reason not to.
Handling Partial Rollbacks
Sometimes you don't want to roll back the entire table — you want to fix specific rows. Perhaps the bad job only corrupted the AMER region while EMEA and APAC data is correct in the current version. A surgical fix avoids losing valid new data:
from delta.tables import DeltaTable
# Read the correct AMER data from version 2
df_correct_amer = (
spark.read
.format("delta")
.option("versionAsOf", 2)
.load("Tables/gold_sales_orders")
.filter("region = 'AMER'")
)
# Use MERGE to update only the affected rows
dt = DeltaTable.forPath(spark, "Tables/gold_sales_orders")
dt.alias("target").merge(
df_correct_amer.alias("source"),
"""
target.order_date = source.order_date AND
target.category = source.category AND
target.region = source.region
"""
).whenMatchedUpdateAll().execute()
print("Surgical fix applied — only AMER rows updated from historical version")
This is the most precise rollback pattern, but it requires you to have correctly identified which subset of data is affected. If you're uncertain, do the full restore and then re-apply any valid incremental updates that arrived after the bad version.
For tables that are part of a larger medallion architecture, also consider whether the corruption propagated downstream — if a gold-layer overwrite was bad, any silver-to-gold transformations need to be re-run against the restored gold table. The Implementing the Medallion Architecture in Microsoft Fabric lesson covers this dependency chain in detail.
Building an Audit Trail from the Transaction Log
The DESCRIBE HISTORY output is useful for interactive investigation, but production data governance usually requires something more structured: a queryable, persistent audit log that records what happened to each table over time, who did it, and what the operational impact was.
Reading DESCRIBE HISTORY Programmatically
from delta.tables import DeltaTable
from pyspark.sql.functions import col, to_json, current_timestamp
def capture_table_audit_snapshot(table_path: str, table_name: str, audit_table_path: str):
"""
Reads the full history of a Delta table and appends new entries
to a persistent audit log Delta table.
"""
dt = DeltaTable.forPath(spark, table_path)
history_df = dt.history().select(
lit(table_name).alias("table_name"),
col("version"),
col("timestamp").alias("operation_timestamp"),
col("operation"),
col("operationParameters").cast("string").alias("operation_parameters"),
col("operationMetrics").cast("string").alias("operation_metrics"),
col("userName").alias("executed_by"),
col("clusterId").alias("cluster_id"),
col("readVersion").alias("read_version"),
col("isolationLevel").alias("isolation_level"),
col("isBlindAppend").alias("is_blind_append"),
current_timestamp().alias("audit_captured_at")
)
# Append to audit log (upsert logic would be better in production)
history_df.write.format("delta").mode("append").save(audit_table_path)
print(f"Captured {history_df.count()} history entries for {table_name}")
# Run against your table
capture_table_audit_snapshot(
"Tables/gold_sales_orders",
"gold_sales_orders",
"Tables/delta_audit_log"
)
# Query the audit log
audit_df = spark.read.format("delta").load("Tables/delta_audit_log")
# Which operations touched gold_sales_orders and what was their impact?
audit_df.filter("table_name = 'gold_sales_orders'") \
.orderBy("operation_timestamp", ascending=False) \
.show(truncate=False)
Reading the Raw Transaction Log Files
For deep-dive investigation, you can read the raw JSON commit files directly. This is more complex but gives you access to the file-level add/remove actions that DESCRIBE HISTORY abstracts away:
import json
from pyspark.sql.functions import explode
# Read all commit log files from the _delta_log directory
log_path = "Tables/gold_sales_orders/_delta_log/*.json"
raw_log = spark.read.text(log_path)
raw_log.show(5, truncate=False)
Each row in raw_log is a single JSON action from a commit file. You can parse these to understand exactly which Parquet files were added or removed by each transaction:
# Parse commit log to extract file-level add/remove actions
from pyspark.sql.types import StructType, StructField, StringType, LongType, BooleanType, MapType
from pyspark.sql.functions import from_json, col, input_file_name
# Read with file source information to know which commit version each action belongs to
raw_log_with_source = (
spark.read
.text("Tables/gold_sales_orders/_delta_log/*.json")
.withColumn("source_file", input_file_name())
# Extract version number from filename (e.g., 00000000000000000003.json -> 3)
.withColumn(
"commit_version",
regexp_extract(col("source_file"), r"(\d+)\.json$", 1).cast("long")
)
)
# Parse the "add" actions to see which files were written
add_schema = StructType([
StructField("path", StringType()),
StructField("size", LongType()),
StructField("modificationTime", LongType()),
StructField("dataChange", BooleanType()),
StructField("stats", StringType())
])
adds_df = (
raw_log_with_source
.filter(col("value").contains('"add"'))
.withColumn("add_action", from_json(
get_json_object(col("value"), "$.add"), add_schema
))
.filter(col("add_action").isNotNull())
.select(
"commit_version",
col("add_action.path").alias("file_path"),
col("add_action.size").alias("file_size_bytes"),
col("add_action.dataChange").alias("is_data_change")
)
)
adds_df.orderBy("commit_version").show(truncate=False)
Note
Reading the raw transaction log directly is primarily useful for forensic investigation and building custom audit tooling. For day-to-day operations, dt.history() is far more ergonomic. Reserve raw log parsing for cases where you need file-level provenance — for example, proving that a specific Parquet file containing sensitive data was removed and by which transaction.
A Production-Grade Audit Query
Here's a T-SQL query you can run against the SQL Analytics Endpoint that produces a clean audit narrative for a table:
-- Audit summary for gold_sales_orders
-- Returns a human-readable changelog with impact assessment
WITH history AS (
-- Use the audit log table we built, or query history directly
SELECT
version,
operation_timestamp,
operation,
executed_by,
operation_parameters,
operation_metrics,
LAG(version) OVER (ORDER BY version) AS prior_version
FROM delta_audit_log
WHERE table_name = 'gold_sales_orders'
),
annotated AS (
SELECT
version,
operation_timestamp,
operation,
executed_by,
CASE operation
WHEN 'WRITE' THEN
CASE WHEN operation_parameters LIKE '%Overwrite%'
THEN 'Full table overwrite'
ELSE 'Incremental append' END
WHEN 'UPDATE' THEN 'Row-level update'
WHEN 'DELETE' THEN 'Row deletion'
WHEN 'MERGE' THEN 'Merge / upsert'
WHEN 'OPTIMIZE' THEN 'File compaction (no data change)'
WHEN 'RESTORE' THEN 'Rollback to prior version'
WHEN 'VACUUM' THEN 'Old file cleanup'
ELSE operation
END AS operation_description,
prior_version
FROM history
)
SELECT
version,
FORMAT(operation_timestamp, 'yyyy-MM-dd HH:mm:ss') AS [when],
operation_description,
executed_by,
CASE WHEN prior_version IS NULL
THEN 'Initial version'
ELSE CONCAT('Changed from v', prior_version, ' → v', version)
END AS version_narrative
FROM annotated
ORDER BY version DESC;
This kind of audit view becomes especially valuable when you need to demonstrate data lineage for regulatory purposes, or when you're debugging a discrepancy between what a report shows and what the underlying data should contain.
Managing Retention Windows: The VACUUM Tension
Here's the operational reality that often bites teams who implement time travel in production: the ability to query historical versions doesn't last forever. It lasts as long as the underlying Parquet files haven't been deleted by VACUUM.
VACUUM is the Delta Lake command that physically deletes files that are no longer part of the current table (i.e., files that were "removed" by later transactions). By default, Delta Lake won't delete files that are younger than 7 days (168 hours). This is the delta.deletedFileRetentionDuration property.
Warning
Microsoft Fabric runs automatic maintenance on Delta tables, which includes OPTIMIZE and sometimes VACUUM. As of current Fabric behavior, automatic VACUUM respects the default 7-day retention window. If you need longer retention for time travel, you must explicitly configure delta.deletedFileRetentionDuration on your table — otherwise Fabric's automatic maintenance may vacuum away your historical data before you realize you need it.
Checking and Configuring Retention
# Check current retention settings on your table
dt = DeltaTable.forPath(spark, "Tables/gold_sales_orders")
# View all table properties
spark.sql("DESCRIBE DETAIL delta.`Tables/gold_sales_orders`").select(
"name", "location", "createdAt", "lastModified", "numFiles", "sizeInBytes",
"properties"
).show(truncate=False)
# Set a longer retention window — e.g., 30 days for audit compliance
spark.sql("""
ALTER TABLE delta.`Tables/gold_sales_orders`
SET TBLPROPERTIES (
'delta.deletedFileRetentionDuration' = 'interval 30 days',
'delta.logRetentionDuration' = 'interval 30 days'
)
""")
Note two separate properties here:
delta.deletedFileRetentionDuration: Controls how long physically-removed Parquet files are kept. This is what makes historical data readable.
delta.logRetentionDuration: Controls how long the JSON commit log files are kept. This determines how far back DESCRIBE HISTORY can see.
Both should be set together, and both should be at least as long as your required audit window.
Running VACUUM Safely
When you run VACUUM manually, always specify a retention period and consider adding a dry run first:
# Dry run: shows which files WOULD be deleted without actually deleting them
spark.sql("""
VACUUM delta.`Tables/gold_sales_orders` DRY RUN
""").show(truncate=False)
# Actually run VACUUM with an explicit retention period
# This removes files older than 30 days that are no longer part of the current table
spark.sql("""
VACUUM delta.`Tables/gold_sales_orders` RETAIN 720 HOURS
""")
Warning
Delta Lake has a hard-coded safety check that prevents you from running VACUUM with a retention period shorter than 7 days (168 hours) without explicitly disabling the check. Do not disable this check in production. If you vacuum with zero retention, you permanently destroy all time travel history and any concurrent readers accessing older file versions will get errors. This is irreversible. The safety check exists for good reason.
The Storage Cost Calculation
For tables with high write frequency, long retention windows have real cost implications. Let's think through this concretely.
Suppose your gold_sales_orders table is 10 GB in its current state and you run a full overwrite every day (a common pattern with partitioned gold tables). With a 30-day retention window, you could be storing up to 30 × 10 GB = 300 GB of historical files in addition to the current 10 GB. In OneLake, storage costs roughly $0.023/GB/month on Fabric capacity — that's about $6.90/month for the current table but $69/month with 30-day retention.
For most tables this is trivial. For very large tables (multiple TB) with full overwrites, it's worth optimizing: use partition-level writes instead of full table overwrites (so only changed partitions generate orphaned files), use OPTIMIZE regularly to compact small files before they become orphaned historical files, and be realistic about how far back you actually need to travel.
One scenario worth calling out specifically: if your Lakehouse tables feed a Power BI semantic model in Direct Lake mode, a bad write at the Lakehouse layer surfaces in reports almost immediately — there's no import refresh buffer protecting you. This is both a strength and a weakness of Direct Lake.
After you restore a table using RESTORE TABLE, Direct Lake picks up the corrected data automatically because it reads Parquet files directly from OneLake rather than maintaining a cached copy. You typically don't need to trigger a manual refresh after a rollback — the next query from Power BI will reflect the restored state.
Now that you've seen all the mechanics, let's put them together in a realistic incident response drill. Complete the following sequence in your own Fabric Lakehouse:
Step 1: Build a multi-version history
Create a silver_customer_metrics table with at least 5 write operations across several versions. Include a mix of appends, updates, and at least one full overwrite. Use a domain you know well — customer lifetime value, inventory levels, transaction summaries, whatever fits your context.
Step 2: Simulate a bad write
In a new cell, write a clearly corrupted version of the table — multiply a numeric column by an incorrect factor, or filter to only a subset of rows and overwrite the full table. Note the timestamp when you did this.
Step 3: Run the investigation workflow
Without looking at the version number (pretend you don't know it), use DESCRIBE HISTORY and the timestamp you noted to identify the last clean version. Write the version comparison query to compute the diff and confirm your hypothesis about what changed.
Step 4: Execute a T-SQL verification
Switch to the SQL Analytics Endpoint for your Lakehouse. Write a T-SQL query using FOR SYSTEM_VERSION AS OF to retrieve the clean state and a second query using FOR SYSTEM_VERSION AS OF against the corrupt state. Compute the delta between them in a single query using a CTE.
Step 5: Perform a partial rollback
Suppose only one category/segment of data was corrupted. Use the MERGE-based partial rollback pattern to fix only the affected rows without restoring the entire table.
Step 6: Verify and document
Read the final table state and verify it matches your expected clean metrics. Run DESCRIBE HISTORY again and confirm that the merge operation appears in the log with appropriate operationMetrics. Write a brief audit note recording what happened, which versions were affected, and what remediation was applied.
Step 7: Configure retention
Set the delta.deletedFileRetentionDuration on your table to 14 days. Compute the approximate storage overhead this implies given your table's current size and write frequency. Run a VACUUM dry run and review which files would be removed.
Common Mistakes & Troubleshooting
"Version X not found" errors when querying history
This happens when you try to query a version that has already been vacuumed. The fix is not to panic: check your current DESCRIBE HISTORY output to confirm the oldest available version, then adjust your query target. Going forward, increase your delta.deletedFileRetentionDuration and make sure VACUUM isn't being run with too short a retention period.
If you receive this error immediately after creating a table (no VACUUM has run), check whether you're using the correct table path. A common mistake in Fabric is confusing the Lakehouse-relative path (Tables/gold_sales_orders) with the full ABFS path. When in doubt, use DESCRIBE DETAIL to get the canonical location.
RESTORE TABLE fails with permission errors
In Fabric, RESTORE TABLE requires write access to the underlying OneLake location. If you're running the notebook as a service principal or under a different identity than the one that owns the Lakehouse, you may hit permission failures. Check your workspace role assignments — you need at minimum Contributor access to the workspace to write to Lakehouse tables. See Securing and Governing Microsoft Fabric: Workspace Roles, Item Permissions, and OneLake Data Access for the full permission matrix.
timestampAsOf returns a version that's still corrupted
This usually means your timestamp is too close to — or after — the bad write. Remember that timestampAsOf returns the most recent version at or before the timestamp. If the bad write committed at 09:32:05 and you query with timestampAsOf = '09:32:10', you'll get the corrupt version. Back your timestamp up further, or use versionAsOf with the explicit clean version number for precision.
DESCRIBE HISTORY shows "null" for userName
This can happen when operations were performed by automated jobs running under a managed identity that isn't mapped to a display name, or when the Spark session's credential context isn't fully propagated. It's not a time travel failure — the version is still queryable. For production jobs, configure explicit credential passing in your pipeline activities so that userName is always populated for audit purposes.
Time travel queries are very slow on large tables
Reading a historical version requires Delta to reconstruct the file set by replaying the commit log from the nearest checkpoint up to the target version. On a table with thousands of versions and no recent checkpoint, this log replay can take seconds to minutes. The fix: ensure Delta is writing checkpoints regularly (the default is every 10 commits, controlled by delta.checkpointInterval). You can also force a checkpoint manually:
dt = DeltaTable.forPath(spark, "Tables/gold_sales_orders")
dt.createOrReplaceTempView("my_table")
spark.sql("GENERATE symlink_format_manifest FOR TABLE delta.`Tables/gold_sales_orders`")
# Force checkpoint directly
dt._jdt.checkpoint(False) # False = synchronous checkpoint
Schema evolution broke time travel
If you added columns between versions, reading an old version that predates the column addition will return nulls for those columns — this is expected behavior. However, if you changed a column's data type (which Delta disallows without special flags), attempting to read across that schema change boundary will fail. See Handling Schema Evolution in Fabric Lakehouse Delta Tables for strategies to handle this gracefully.
RESTORE TABLE succeeds but Direct Lake still shows old data
Direct Lake maintains a "framing" of which Parquet files constitute the current table version. After a restore, you may need to trigger a semantic model refresh to force a reframing. In most cases this happens automatically within seconds, but under high load or if the semantic model is in an error state, a manual refresh from the workspace UI may be needed.
Summary & Next Steps
Delta Lake time travel is one of the genuinely transformative capabilities that comes with running your data in a Lakehouse. The ability to query any prior version of a table, roll back a bad write in two minutes instead of two hours, and produce a complete audit trail of every operation — all without any additional infrastructure or tooling — is what separates a mature data platform from a fragile one.
Let's recap the core skills you now have:
The mechanics: You understand that time travel is powered by the _delta_log transaction log, which records file additions and removals for every commit. Old files are retained on disk until VACUUM removes them, and your retention window is the window during which historical queries work.
PySpark time travel: You can read any historical version using .option("versionAsOf", n) or .option("timestampAsOf", t), compute version diffs, and investigate corruption scope before taking any action.
T-SQL time travel: You can use FOR SYSTEM_VERSION AS OF and FOR SYSTEM_TIME AS OF in the SQL Analytics Endpoint, write CTE-based diff queries, and build audit-friendly reports from T-SQL.
Rollback patterns: You know to prefer RESTORE TABLE over manual overwrites for auditability, how to execute partial rollbacks with MERGE for surgical fixes, and the five-step verification process that separates confident rollbacks from risky ones.
Audit trail construction: You can read DESCRIBE HISTORY, parse raw transaction log files for file-level provenance, and persist audit snapshots to a dedicated log table for long-term governance.
Retention management: You know how to set delta.deletedFileRetentionDuration and delta.logRetentionDuration, estimate storage costs, and run VACUUM safely with explicit retention periods.
The 9:47 AM phone call doesn't have to be a disaster anymore. With time travel fully configured and understood, it's a fifteen-minute incident with a clear audit trail and a satisfying resolution.
