Source files within source systems are not synchronized with raw tables in Azure database

Hi @Stanislav Slovik - Administrator ,

Thank you for reaching out to the Microsoft Q&A forum. 

Your concern is valid. Once data corrections are made directly in the database and are not reflected back to the source files, the database effectively becomes the most accurate version of the data.

My recommendation would be:

• Keep a dedicated audit/correction table that stores all manual changes (old value, new value, timestamp, ticket reference, user, and reason for change).
• Enable Temporal Tables or Change Data Capture (CDC) in Azure SQL Database to maintain historical versions of the data.
• Periodically export critical tables or snapshots to Azure Storage for long-term retention beyond the point-in-time restore window.
• If possible, keep the raw layer immutable and apply corrections in a separate curated/business layer rather than modifying raw data directly.

A common architecture is:

Source Files
 ↓
Raw Layer (Original Data)
 ↓
Correction/Audit Layer
 ↓
Curated Layer
 ↓
Reporting

This approach provides better auditability, recovery options, and data lineage. If a table is accidentally deleted or corrupted months later, you can rebuild it using the original source data together with the recorded corrections, rather than relying solely on source files that no longer reflect the current state.

Overall, your idea of maintaining a correction register combined with periodic exports/snapshots would be the approach I would recommend for a production DWH environment.

Store corrections as replayable data (table, key, column, old_value, new_value, applied_date, ticket_ref). It lets you deterministically re-apply corrections after any reload from source.

Add a recovery backstop for the late-detection gap beyond your 7-day PITR: Azure SQL long-term retention and/or system-versioned temporal tables for queryable row history.

A strategic option worth evaluating - keep raw tables as a faithful copy of source and apply corrections through a separate overlay (a corrections table plus a view). Reloads then can't lose corrections. It's powerful but disruptive to retrofit, so it's most attractive if corrections are concentrated in a few tables.

Restrict DDL/DELETE on raw tables, route corrections through a controlled procedure or pipeline that also writes to some table, and set up alerts on table drops.

Do I understand this correctly, that the files have data errors of various sorts that you correct in the target tables? And sometimes that these corrects are applied incorrectly?

It is difficult to explain a complex problem in a short and concise post, so there is a risk that important details get lost, and therefore I may be misunderstanding you. But from what you seem to say, my gut reaction is that you should have two sets of tables. The first set should mirror the source files exactly. Then you have your next set of tables which is your actual DWH which you query and make analyses from. To this you can add tracking table to track the manual changes.

I'm not much into the analytics space myself, but it's popular to talk about a medallion architecture with a bronze, silver and gold layer, where, as I understand it, bronze is the raw data, and gold is what you actually expose to the end users. Silver is then an intermediate layer to permit for stepwise refine ment.