Can I use Azure Data Lake in place of Data Warehouse to separate Compute and Storage of RDBMS analytics [closed]

Question

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.

Answer 1

Your approach sounds reasonable. Many of the analytics-oriented DBMS allow access to data lake files in situ. You're just reproducing a similar feature in user-space.

IIRC ADF's data flow uses a DataBricks cluster under the covers. This may be a simpler implementation than writing dataframes in a notebook and getting it to run in 'Bricks.

I think most opt for a load then process approach because SQL makes such manipulation easy and Synapse is optimised to perform it.

If your source database is SQL Server you may want to consider Change Data Capture for watermarks / delta tracking.