POST_START

Comparing Current and Historical Production Data After a Suspected Bad Write

Yesterday, I noticed some inconsistencies in our sales reports. A few orders were showing up as completed when they should have been pending. I suspected a bad write might have occurred, so I decided to investigate using Databricks Unity Catalog’s time travel capabilities.

Understanding the History of the Table

I started by checking the history of the production.sales.orders table to see what operations had been performed on it. This would help me understand the timeline of changes and identify the version that might have caused the issue.

DESCRIBE HISTORY production.sales.orders;

version	timestamp	userName	operation
12	2026-09-11 14:32:10	analyst@demo.com	WRITE
11	2026-09-11 13:18:42	engineer@demo.com	MERGE
10	2026-09-11 09:05:17	admin@demo.com	CREATE TABLE

From the output, I saw that version 12 was a WRITE operation, and version 11 was a MERGE operation. I suspected that the WRITE at version 12 might have introduced the issue. I decided to look at the data from that version to compare it with the current data.

Examining the Data from Version 10

To get a baseline, I ran a query to retrieve the data from version 10, which was the CREATE TABLE operation. This would give me the state of the table before any of the recent changes.

SELECT * FROM production.sales.orders VERSION AS OF 10;

order_id	customer_id	region	amount	status
50001	2001	EMEA	1250.00	COMPLETE
50002	2002	AMER	890.50	COMPLETE
50003	2003	EMEA	430.25	PENDING

The data from version 10 looked correct. Order 50003 was marked as PENDING, which matched our expectations. This was the state of the table before any recent operations.

Checking the Current Data

Next, I ran a query to see the current state of the production.sales.orders table. This would help me compare it with the historical data and identify any discrepancies.

SELECT * FROM production.sales.orders;

order_id	customer_id	region	amount	status
50001	2001	EMEA	1250.00	COMPLETE
50002	2002	AMER	890.50	COMPLETE
50003	2003	EMEA	430.25	PENDING

Surprisingly, the current data was identical to the data from version 10. That meant the WRITE operation at version 12 had not changed the data. But I still wanted to verify that the WRITE operation had actually been applied.

Confirming the Impact of the Write Operation

I reviewed the history again to ensure I had the correct version numbers. The WRITE at version 12 was the most recent operation. I had expected it to change some data, but the current data was the same as version 10. This raised a question: was the WRITE operation actually successful?

Despite the current data matching version 10, I decided to run a query against version 12 to see if any changes had been made. However, the output from version 12 was not shown in the sample. I would need to run the query with the correct version number to confirm if any changes were introduced.

Through this process, I learned how to use Databricks Unity Catalog’s time travel features to compare historical and current data. It’s a powerful tool for debugging and verifying data integrity in a production environment.