I am from a telemetry data engineering background and am looking for some help on a use-case I am not familiar with as to whether my proposed architecture is practically feasible in Azure. The use-case is that I am trying to build some intelligence from a relational database that is updated regularly.
Currently, a data warehouse is in place which uses ADF to copy database tables in 3NF to some staging tables. The staging tables are then used to upsert to their respective tables in the 'Data Warehouse'. No denormalisation has been done. This has been pushed to the end-user to do in PowerBI. I inherited this.
The solution is expensive and places high demand on the end-user analysts. I wanted to use a Kimball style approach and have a fact table with some dimension tables. In order to reduce costs, I want to do this in Azure Data Lake and then use DataBricks to carry out the denormalisation and store the resulting outputs in a separate zone in Data Lake. I would then take the daily snapshots of the database tables and update the fact and dimension tables where data has changed/been added. (I still need to determine how to determine the daily changes without a watermark in the source tables).
I do not see any reference architectures where this is done. It seems the data is always put into Synapse or SQLDB, even if it does make a stop in Data Lake. In order to reduce the costs would my approach be ok where I use Data Lake and DataBricks for everything (Staging, denormalisation, and serving the fact/dim tables)? The analysts would then have a much simpler model to connect up to PowerBI and use Dataflows to carry out their aggregations.
Any advice would be greatly appreciated.