Azure Data Factory's Mapping Data Flows feature enables graphical ETL designs that are generic and parameterized. In this example, I'll show you how to create a reusable SCD Type 1 pattern that could be applied to multiple dimension tables by minimizing the number of common columns required, leveraging parameters and ADF's built-in schema drift capability.
The completed version of this data flow is shown above and is also available as a pipeline template that you can add to your factory by downloading it here and then importing it into your factory. Go to New > "Pipeline from template" and then point to this local file.
Typically, when building ETL for a data warehouse solution, you will need to manage many different dimension tables. With ADF Mapping Data Flows, you can minimize the number of different flows that you need to create by creating generic, reusable data flows. The key is to identify the commonality between each dimension table and then create parameters for the elements that change between executions and tables.
For this demo data flow, I created string parameters for these elements:
1. dimname: can be used to create intermediate files with the name of the dimension
2. keycol: the name of the business key column in the target dimension table. In this case, it is "ProductID"
3. SurrogateKey: the column name for the dimension table's surrogate key (non-business key)
4. tablename: the target dimension table name
Inside of the logic in the example, you will see that I am assuming 4 fields will be common in every dimension table in your stars schema model:
These are all very common attributes used in star schemas. But you can modify these columns and parameters to fit your model. The idea here is to provide a pattern that you can mimic in your business. I explain the steps below, but the best way to get comfortable with this technique is to download this sample and play around with it in your factory.
Here are the high-level steps to make this work for more than just one dimension:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.