POST_START

Comparing Current and Historical Production Data After a Suspected Bad Write

Yesterday, I noticed some unusual behavior in our production data pipeline. A new batch of data was written to the production.sales.orders table, but when I checked the latest records, it looked like some entries were missing or had incorrect values. I suspected a bad write might have occurred, and I needed to compare the current data with historical versions to identify the issue.

First, I wanted to understand the history of the table to see what changes had been made recently. I ran the DESCRIBE HISTORY command to get a list of all the versions and the operations that had been performed on the table.

DESCRIBE HISTORY production.sales.orders;

From the output, I saw that version 12 was a WRITE operation by the analyst, and version 11 was a MERGE by the engineer. Version 10 was the initial CREATE TABLE. This suggested that the latest data might have been introduced by the analyst, which aligned with my suspicion of a bad write.

To investigate further, I decided to look at the data from version 10, which was the state of the table before the most recent changes. I used the VERSION AS OF clause to retrieve the data as it was at that time.

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

This gave me the historical data snapshot. I compared these records with the current data in the table to see if any discrepancies existed.

SELECT * FROM production.sales.orders;

Interestingly, the current data matched the historical version exactly. This meant that despite the recent WRITE operation, the data hadn’t changed. It was possible that the write didn’t actually modify any records, or the data was already in the correct state.

I then considered the possibility that the write operation was a no-op or that the data was already consistent. I verified the timestamps and user names to ensure that the operations were correctly attributed and that there were no overlapping or conflicting changes.

With this information, I concluded that the suspected bad write did not affect the data in the production.sales.orders table. The data remained consistent across versions, and no discrepancies were found. This helped me understand the situation better and avoid unnecessary reprocessing or data correction.