I have a Delta Table in Azure Databricks that stores history for every change that occurs. I also have a polybase external table for the users to read from in an Azure SQL Data Warehouse/Azure Synapse. But when an update or delete is necessary, I have to vacuum the table in order to update the polybase to the latest, or it has a copy from every previous version. That vacuum, by nature, deletes the history, so I can no longer roll back.
I'm thinking my only choice is to manually keep a row change table with colums = [schema_name, table_name, primary_key, old_value, new_value] so that we can reapply the changes in reverse if necessary. Is there anything more elegant I can do?