Incremental Refresh with Deleting Duplicate Records before Appending Staging Data

Hello Everyone, thanks for the ideas on this. I've been able to successfully build out an Incremental refresh structure without using a notebook. Some notes on my use case:

1. Existing rows in my tables can be updated so I’ll need to delete the old data before appending the new data when pulling in incremental data
2. I can’t risk using a notebook for this. I'm using notebooks to push data to an API and can't risk a conflict where the API fails because I'm already running too many notebooks for incremental refreshes.
3. Our data sources are mostly on-prem.
4. We've subscribed to Fabric capacity (F64)
5. I'm a citizen developer and we don't have many data analytics folks who have experience with Data Factory so I'm going to rely more on the citizen dev tools where possible - although learning Data Factory wasn't as hard as I thought it was going to be when they first launched Fabric into Public Preview.
6. I'm following the medallion model/architecture though I use names that my users who aren't familiar with bronze/silver/gold would understand - specifically Raw/Cleaned/Refined.

So here's some details on how I've set this up. I'll post additional details.

What I set up:

1. Staging Lakehouse - this is needed to copy data from an external source into Fabric (I tried going direct to a Data Warehouse but you can’t – the data needs to be staged)
2. Data Warehouse for Raw Data - Only data warehouses support deleting rows in a table using a script so you can’t use a Lakehouse for incremental refresh (unless you use a notebook).
3. Data Warehouse for Cleaned Data – For the tables that get cleaned.
4. Data Warehouse for the Refined Data - For the tables that are refined.
5. Lakehouse - I'm not sure what to call this but really the purpose here is just a repository of links to the tables in the data warehouses. It gives me a layer of abstraction. So when I have someone using a lakehouse in another workspace that needs access to this data, I link to this Lakehouse and if I ever change a table - like if a table needs cleaned that didn't before, I can just update the link in this Lakehouse and I don't need to remember to update any other lakehouses that my users are building against.

Dataflow Gen 2: Admin Table – I start with a Dataflow Gen2 which is just a manual set of tables that I use to iterate over for pulling in data. So that includes these tables and the columns listed:

• Tables to import raw data: Table_Name, Date_Column, Unique_Identifier_Column, Dataflow_Needed*
• Tables to clean: Table_Name, Date_Column, Unique_Identifier_Column
• Tables to refine: Source_Table_Name, Source_Data_Warehouse, Schema,  Refined_Table_Name, Date_Column, Unique_Identifier_Column

*This is 1 if the table needs to use a dataflow to copy into the data warehouse instead of a straight copy in Data Factory. I’ve found that long text fields that exceed varchar(8000) need to be copied using a dataflow or custom scripting. Since I’m trying to stay low-code, I’m going with the dataflow gen2 for that. But you could add a column to pull in a script for that specific table if preferred.

A data pipeline manages everything. Here’s what’s in it:

1. Lookup Activity: Looks up table in staging lakehouse with the list of tables to import
2. ForEach Activity: Iterates over the rows in the list of tables to import
   1. Script: Grab the max date (value in Date_Column) for the table (value in Table_Name)
   2. Copy data Activity: Copies the data from the on-prem data source after the max date for the table and copies it to the staging lakehouse. I have this add “staging__” to the front of the table name.
   3. Script: Delete matching IDs (from the Unique_Identifier_Column) for the table in the Data Warehouse for Raw Data that match the staging table in the staging lakehouse.
   4. If Condition: Checks to see if the table requires a Dataflow to copy
      1.      If the table does NOT need a dataflow
         1. Script: Insert data from the staging table in the staging lakehouse into the table in the Data Warehouse for Raw Data
      2.      If the table does need a dataflow then nothing happens – that’s covered in step 3
   5. Dataflow Gen2: Append staging data from staging lakehouse to the tables in the data warehouse for raw data – this doesn’t do any processing of the data
   6. Lookup Activity: Looks up table in staging lakehouse with list of tables that need cleaned.
   7. ForEach Activity: Iterates over the rows in the list of tables to clean
      1. Script: Grab the max date (value in Date_Column) for the table (value in Table_Name)
      2. Copy data Activity: Copies the data from the table in the Data Warehouse for raw data after the max date for the table and copies it to the staging lakehouse. I have this add “cleanstaging__” to the front of the table name.
      3. Script: Delete matching IDs (from the Unique_Identifier_Column) for the table in the Data Warehouse for Cleaned Data that match the staging table in the staging lakehouse.
   8. Dataflow Gen2: Cleans and Appends data from staging lakehouse (from the version starting with “cleanstaging__” to the tables in the data warehouse for cleaned data – this dataflow does take steps to clean the tables before appending them
   9. Lookup Activity: Looks up the table in the staging lakehouse with the list of tables that need refined.
   10. ForEach Activity: Iterates over the rows in the list of tables to clean
       1. Script: Grab the max date (value in Date_Column) for the table (value in Refined_Table_Name)
       2. Copy data Activity: Copies the data from the table in the Data Warehouse for cleaned data after the max date for the table and copies it to the staging lakehouse. I have this add “refinestaging__” to the front of the table name.
       3. Script: Delete matching IDs (from the Unique_Identifier_Column) for the table in the data warehouse for refined data that match the staging table in the staging lakehouse.
   11. Dataflow Gen2: Refines and Appends data from staging lakehouse (from the version starting with “refinestaging __” to the tables in the data warehouse for refined data – this dataflow does take steps to refine the tables before appending them

There are many nuances so let me know if you have questions. The pattern is pretty simple and basically repeated for each step in the medallion architecture. It would be easy to simplify clean and refining the data. And most of the dataflow gen2 entries could be replaced by SQL scripts if needed/preferred.

Notebooks are easier than all of this. But the limitation on number of notebooks running at one time could cause conflicts with other scheduled notebooks/pipelines (with notebooks) or even with your users who are running ad hoc notebooks.