Overwriting the existing table in Databricks; Mechanism and History?

Question

Hi,

Assume that I have a delta table stored on an Azure storage account. When new records arrive, I repeat the transformation and overwrite the existing table.

    (DF.write
         .format("delta")
         .mode("overwrite")
         .option("path", save_path)
         .save()

I have 2 questions in this regard:

1.What is the mechanism of overwriting?

Does it truncate the table and insert new records?

2.If any overwriting operation fails, how can I know that?

Assume that dataset is large and overwriting cannot be identified by looking at the table records.

Is there any log or history that shows whether the latest overwrite was successful?

Answer 1

@Mohammad Saber

Thanks for using Microsoft Q&A forum and posting your query.

1. What is the mechanism of overwriting?

When you use the mode("overwrite") option to write data to a Delta table in Azure Databricks, the existing data in the table is truncated (nothing but table is replaced with new/latest data, but the old data still available in the log history based on the versioning) and replaced with the new data from the DataFrame being written. The overwrite operation first deletes all the files in the Delta table's directory (But the old data still available in the log history based on the versioning) and then writes the new data to the table. This ensures that the table reflects only the latest data.

1. If any overwriting operation fails, how can I know that?

If the overwrite operation fails for any reason, an exception will be raised, and the operation will be rolled back. You can catch the exception using a try-except block and log the error message for debugging purposes.

By default, Delta tables in Azure Databricks store transaction logs that track changes made to the table. These logs can be used to recover the table in case of a failure or to revert to a previous version of the table. You can use the DESCRIBE HISTORY command in Databricks to view the transaction history of a Delta table.

The logs and history available in Delta Lake tables can help you identify whether an overwrite operation was successful or not.

Below are the steps to check those logs:

1. Open your Azure Databricks workspace and navigate to the Delta Lake table that you want to check.
2. Click on the 'Data' tab and select the 'Table History' option.

You can see operation parameters details like Overwrite , Append , etc in operationParameters column.

You can also verify the job details and notebook details in job and Notebook columns.

By following these steps, you can check the logs and history of a Delta Lake table in Azure Databricks to identify whether an overwrite operation was successful or not.

Please note that, as I mentioned earlier, if the overwriting operation fails, the commit will not happen for half of the data, and it will be rolled back. But you can catch the exception and implement alerting system to get notification for failure scenarios. If you are doing an on-demand run (not a job), then spark will show you the error right away.

In case if the overwrite operation is successful and you can see that logged in the history table as mentioned above. To double confirm if all the news records were inserted you can check the number of latest records available in the delta table versus the number of the records available in the previous version of the table. This will confirm that the last overwrite operation was successful or not.

Hope this helps.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.