In this continuation (part 3) of the previous blog, you will create a simple metadata driven pipeline to ingest data into the Data Warehouse and populate the dimension tables.

Task4: Create a metadata driven pipeline to ingest data into the Data Warehouse and populate the dimension tables.

We will use the table mtd.TablesToCopy created in Task3 of above while building a metadata driven pipeline, to ingest data from the Azure SQL Database.

1. Click on the Workspace icon on the left side navigationand click “+ New Item” > search “Data pipeline” to create a new pipeline and provide a name (eg., PL_Dim_FullLoad_Levelup)
2. Once the pipeline editor opens, click on the Activities tab and add a Stored Procedure activity and name it DeleteAllRowsfromStgTbls in the General tab

3. On the settings tab, use the below snip to populate the details. This stored procedure deletes all the records of the staging dimension tables to facilitate a full load (except stg.DimCustomer)

Note: We'll utilize stg.DimCustomer for incremental changes, won't be deleting all rows from the table.

4. Click on the whitespace in the pipeline editor/canvas to add a parameter called “LookupTable” like the below.

5. Add a lookup activity into the canvas. Connect the “On success” port of the “Stored Procedure” activity to the Lookup activity. From the general tab of the lookup activity, rename it to TablesToCopyLookup. In the settings tab, use the below snip to populate the details.

Important: Remember to uncheck the First row only

Note: In the above snip the Query field is set dynamically. To do so, click on the Add dynamic content as shown below and paste the following code: SELECT * FROM @{pipeline().parameters.LookupTable}

The lookup activity will get the table name passed by the pipeline parameter at runtime and run the select statement against the table – mtd.TablesToCopy.

6. Add a ForEach activity to the canvas and connect the “On success” port of the “Lookup” activity to the “ForEach” activity. The ForEach activity will iterate once for each value returned by the Lookup activity. Rename the activity name to ForEach_Row_In_TablesToCopy
7. Set the settings for the ForEach activity as below. The “items” field should be set similar to note section of step 5. Paste the following into the items field - @activity('TablesToCopyLookup').output.value.

8. Double click or edit the ForEach activity and within the ForEach activity, add a Copy Activity, set its name as “Copy_Data_From_SQL_To_Fabric_DW” in the General tab to:

9. Set the source tab settings based on the snip below. In the connection field, setup a new connection to the Azure SQL DB that you created in the pre-requisites section.

In the Query field click Add dynamic content and type - SELECT * FROM @{item().tableName}. This will pass the name of the table based on the iteration we are in.

10. Set the destination tab settings based on the snip below. Notice that we will copy the source tables from the Azure SQL database to the staging schema and dynamically pass table names based on the source tables. The schema field is statically set to stg. To create the table names in the data warehouse dynamically, in the table name field, click Add dynamic content and paste - @concat('Dim',split(item().tableName,'.')[1]). This expression, concatenates the string “Dim” to the second part of the two part source table.

11. In the canvas showing the Copy activity, click on Main canvas on the top left corner to exit out of the ForEach activity.

12. Add a Stored procedure activity to the canvas and connect the Success port of the ForEach activity to the stored procedure. Rename the stored procedure activity from the General tab to - CTAS_For_PrdDimTbls.
13. In the settings tab, fill out the details using the snip below

14. Click on the Home tab > Save to save the pipeline and the Play button to execute the pipeline. The pipeline should look like the below.

After a successful run, it would have copied all the tables from the Azure SQL Database to the stg schema of the Data Warehouse and would have populated all the Dimensions tables in the production schema.