Forum Discussion
Data Lakehouse Modelling Q
I don't know whether using copy activity with a delta table enabled for CDF will work, something to test for sure. And yes, if there is a big bulk movement of data, CDF isn't really going to be much use anyway.
IMHO you won't need to use Type 2 in the raw/cleansed layer (I assume you mean slowly changing dimension?) as you can handle this further down the pipeline in your dimensional model. You need to be able to identify the changed records to then apply appropriate transformation downstream.
I think the crux here is to be able to incrementally load from the MySQL box, if this isn't possible then I don't know any other way you could identify changed records other than to bulk load the data into Fabric Lakehouse, then work out what's changed by using date/time stamps. Possibly store the lastupdated timestamp in a custom metadata table in the lakehouse for each table, then when you bulk load into the raw layer, query each table using this timestamp and only load the changed rows into the silver layer.
A blog on CDF if any of it helps in this situation...
Delta Lake Change Data Feed in Fabric Lakehouses (serverlesssql.com)
Hi bw_chec so it certainly would be good to optimise that bronze/raw layer load, so my thoughts on that are:
- Any indexing implemented on relevant date columns in the MySQL db to allow only selecting data that has changed?
- Any way to identify only the tables that have changed data (therefore optimising what you're reading)?
- I assume there's no possiblity of increasing the power of the compute MySQL is on?
Ideally you don't want to drop/recreate the bronze layer for each load (it should be appended). How are you overwritting the bronze layer (lakehouse tables?)