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

Question

Hello,

I would like to ask for suggestions regarding the following problem:

• Our DWH architecture was initially configured in the way, that source files from various source systems should be aligned with actual raw tables in our Azure database [we are on daily basis simply loading data from those source files into our Azure database].
• But over the time, we had to adapt some data corrections into various database tables, while respective source files in the source platform were not updated
• We keep tracking of those changes within our ticketing system, but I am afraid of the scenario in the future, that some table from database is deleted by mistake or corrections are removed by mistake.
• Point in time restore [snapshots for seven days of data] can help us, if we detect such a scenario in couple of days.
• But if it is detected after the longer time, then it will be very tricky to reload the data into database table, because source files are not containing corrections.

And I though about the following solutions:

1. Tracking all the data corrections in some register [e.g.: dedicated tables or platform]
2. Simply exporting all the raw database tables on regular basis to the Azure cold storage [or irregular basis, when corrections are performed]
3. Exporting from database tables delta source files, which will contain only corrections

But it would be really great, if you could suggest also some other solutions or suggest how would you approach it.

Many thanks in advance

Answer 1

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.

Answer 2

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.