{"id":102060,"date":"2024-05-06T16:07:53","date_gmt":"2024-05-06T16:07:53","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=102060"},"modified":"2024-09-03T20:04:45","modified_gmt":"2024-09-03T20:04:45","slug":"how-to-build-metadata-driven-pipelines-in-microsoft-fabric","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/how-to-build-metadata-driven-pipelines-in-microsoft-fabric\/","title":{"rendered":"How to Build Metadata Driven Pipelines in Microsoft Fabric"},"content":{"rendered":"

Over the past years, \u201ctraditional\u201d ETL development has morphed into data engineering<\/strong>, which has a more disciplined software engineering approach. One of the benefits of having a more code-based approach in data pipelines is that it has become easier to build metadata driven pipelines<\/strong>. What does this mean exactly?<\/p>\n

Say for example you need to copy a set of tables from a source database into data lake storage. If you have 50 source tables, you don\u2019t want to create 50 distinct pipelines manually. It\u2019s not only very time-consuming (and boring), but it\u2019s also hard to maintain. Suppose you need to add an audit column to each result set. This means you need to update 50 pipelines!<\/p>\n

In the \u201cold days\u201d of SSIS, you could use BIML<\/a> to generate your SSIS packages (you still ended up with 50 different packages, but at least you didn\u2019t have to create them yourself). In code-based pipelines like notebooks in Spark, you can write Python to extract metadata of the source tables and use that data to dynamically extract all the source data into different data frames and dump those into data lake storage. In SQL Server, you might use dynamic SQL<\/em> to generate SQL statements on-the-fly to do the transformations dynamically.<\/p>\n

The goal of metadata driven code is that you build something only once<\/a>. You need to extract from relational databases? You build one pipeline that can connect to a relational source, and you parameterize everything<\/strong> (server name, database name, source schema, source table, destination server name, destination table et cetera). Once this parameterized piece of code is ready, all you must do is enter metadata about the sources you want to extract. If at a later point an additional relational source needs to be extracted, you don\u2019t need to create a brand-new pipeline. All you need to do is enter a new line of data in your metadata repository.<\/p>\n

Aside from speeding up development \u2013 after you\u2019ve made the initial effort of creating your metadata driven pipeline \u2013 is that everything is consistent. You tackle a certain pattern always in the same way. If there\u2019s a bug, you need to fix it in one single location.<\/p>\n

In this article, we will guide you through creating metadata driven pipelines in Microsoft Fabric<\/a>. Pipelines are a low-code to no-code environment, but we can still implement metadata driven patterns in them using parameterization. Pipelines in Fabric are similar to pipelines in Azure Data Factory<\/a> or Azure Synapse Analytics Pipelines<\/a>, but there are differences.<\/p>\n

If you want to follow along with the examples, you can get a free trial of Fabric<\/a> (as long as the offer stands) and a free Azure trial<\/a>. You can also create a cheap pay-as-you-go Fabric capacity<\/a> in Azure, just don\u2019t forget to pause it<\/a> once you\u2019re done to save on costs.<\/p>\n

Copying One Single Table<\/h2>\n

When you want to create a metadata driven framework, it\u2019s always a good idea to start easily. You create a pipeline that copies one single table and everything\u2019s hardcoded. If you try to jump directly into the parameterization and fetching of metadata, the solution might get too complex too fast and it\u2019s harder to debug. With a simple case, you can first validate if certain items work, like the connections to source and destination.<\/p>\n

First, make sure you have a Fabric-enabled workspace (either by having a capacity assigned to it or by using a trial). Next, we need some sample data to read from. In the article How to Install the AdventureWorks Sample Database in Azure SQL Database<\/a> it is explained how to get a small sample database in Azure. Of course, you can use any other source to experiment yourself. In this article, we\u2019re going to write the data to a warehouse. If you don\u2019t have one yet, you can familiarize yourself with this compute type in the article What are Warehouses in Microsoft Fabric?<\/a>.<\/p>\n

Let\u2019s login into Fabric<\/a> and switch to the Data Factory<\/em> persona (bottom left corner of the screen):<\/p>\n

\"A<\/p>\n

This will take you to the Data Factory experience where you can choose to create a new pipeline:<\/p>\n

\"A<\/p>\n

Give the new pipeline a name and hit Create<\/strong>.<\/p>\n

\"A<\/p>\n

A new pipeline canvas will be opened, and four options will be presented to you: add a new pipeline activity, the Copy data wizard\/assistant, the option to load sample data or using a pre-defined template.<\/p>\n

\"A<\/p>\n

There\u2019s a template to bulk copy from a database (or to import files), which seems promising for our metadata driven pipeline:<\/p>\n

\"A<\/p>\n

It seems to be using the pattern we\u2019re going to implement in this article:<\/p>\n

\"A<\/p>\n

However, only the following destination are supported by the template:<\/p>\n

\"A<\/p>\n

Since we want to write to a Fabric warehouse, we can\u2019t use it. Instead, let\u2019s start from scratch by using the first option (Pipeline Activity). We get a blank canvas where we can add a new pipeline activity to the pipeline: the Copy data activity. In the Source<\/em> pane, we need to configure the source connection by either selecting an existing one, or creating a new one:<\/p>\n

\"A<\/p>\n

Create a new connection and choose Azure SQL DB as the connection type.<\/p>\n

\"A<\/p>\n

Once you\u2019ve verified the connection, you can select a table from the dropdown list. Later, we will parameterize this. In the Destination<\/em> pane (which is called Sink<\/em> in ADF), choose Workspace<\/strong> as the data store type and select your warehouse from the list:<\/p>\n

\"A<\/p>\n

In the table option, choose the Auto create table<\/strong> option. To make sure we can run the pipeline multiple times, go to Advanced<\/strong> and enter the following SQL statement in the pre-copy script:<\/p>\n

IF OBJECT_ID(N'dbo.AW_Address', N'U') IS NOT NULL \u00a0\nBEGIN;\n\u00a0 \u00a0 DELETE FROM dbo.AW_Address;\nEND;<\/pre>\n
We\u2019re using a DELETE<\/code> statement since TRUNCATE TABLE<\/code> is not supported for the moment. If it at some point in time it is available in the Fabric Warehouse, TRUNCATE<\/code> will be the preferred option.<\/p>\n
The pipeline will run the pre-copy script before it actually creates the table. If we don\u2019t check for table existence, we get the following error:<\/p>\n
\"A<\/p>\n
The Mapping<\/em> pane stays empty, which means columns will be mapped using their name (and if that fails, by ordinal). A best practice that I\u2019d like to introduce is that the source and destination have the exact same column names<\/strong>, to ensure a smooth mapping process.<\/p>\n
\"A<\/p>\n
In the Settings<\/em> pane, make sure Enable staging<\/strong> is selected (otherwise the pipeline fails because it doesn\u2019t support writing directly to the warehouse):<\/p>\n
\"A<\/p>\n
Once everything is configured, you can hit Run<\/strong> to test the pipeline. While the pipeline running, you can click on the Copy data activity name in the output<\/em> pane to get a more detailed view of the performance:<\/p>\n
\"A<\/p>\n
We can verify the data has landed in the warehouse by running a simple SELECT *<\/code> statement:<\/p>\n
\"A<\/p>\n
Getting Metadata and Parameterizing the Pipeline<\/h2>\n
Once the pipeline works for a single table, it\u2019s time to put the metadata in action and to parameterize everything. The first step is to get the metadata of which tables we want to load. You can store this in a table, but most relational databases have the INFORMATION_SCHEMA tables, which allow you to query the metadata of the database. In the source database, we can run the following query:<\/p>\n
SELECT\n     SourceSchema       = TABLE_SCHEMA\n    ,SourceTable        = TABLE_NAME\n    ,DestinationTable   = TABLE_NAME\n    ,DeleteStatement      = \n    'IF OBJECT_ID(N''dbo.' + TABLE_NAME + ''', N''U'') IS ' \n        + 'NOT NULL'\n        + CHAR(13) + CHAR(10) \/* new line*\/\n        + 'BEGIN;'\n        + CHAR(13) + CHAR(10) \/* new line*\/\n        + '    DELETE FROM dbo.' + TABLE_NAME + ';'\n        + CHAR(13) + CHAR(10) \/* new line*\/\n        + 'END;'\nFROM INFORMATION_SCHEMA.TABLES\nWHERE   TABLE_TYPE = 'BASE TABLE'\n    AND TABLE_SCHEMA <> 'dbo';<\/pre>\n
This gives us everything we need to parameterize the Copy data activity. In the pipeline, add a Lookup activity<\/strong> to the canvas.<\/p>\n
<\/p>\n
In the General pane, give the activity a decent name and set a proper timeout<\/strong> (we don\u2019t want an activity hanging for hours, time is literally money with Fabric capacities).<\/p>\n
\"A<\/p>\n
I also typically set the retry<\/strong> to at least one, because network failures do happen. In the Settings<\/em> pane, set the data store type<\/strong> to External<\/em> and reuse the source connection we created earlier.<\/p>\n
\"A<\/p>\n
Set the type to Query<\/strong> and copy paste the metadata query. Make sure First row only is not selected<\/strong>, otherwise the metadata driven pipeline is not going to do a lot of work. Hit Preview data<\/strong> to ensure everything works.<\/p>\n
\"A<\/p>\n
Once we have the metadata, we need to iterate over it. This means that for every row returned by this result set, we will execute the Copy activity. We can achieve this by using the ForEach activity<\/strong>, which can be found on the Activities<\/strong> tab.<\/p>\n
\"A<\/p>\n
Add a ForEach<\/code> activity to the canvas, connect the Lookup to this activity using an On success<\/strong> constraint (the green arrow) and move the Copy activity to the inside of the ForEach. You can do this by cutting the activity, go to the edit pane of the ForEach<\/code> and then paste it onto the new canvas of the ForEach<\/code>. Finally, your canvas should look like this:<\/p>\n
\"A<\/p>\n
We need to tell the ForEach<\/code> over which items to iterate over, which would be the output of the Lookup. We can view what this output is like by running the pipeline. Deactivate the ForEach<\/a> (right-click and choose Deactivate<\/strong>) and hit Run<\/strong>. Once the pipeline is finished, you can click on the output to view the returned JSON.<\/p>\n
\"A<\/p>\n
The output should look like this:<\/p>\n
\"A<\/p>\n
We\u2019ve got a JSON containing an array named value<\/em> of 10 items. In the settings pane of the ForEach<\/code>, we\u2019ll reference this array in the dynamic content<\/em> of the Items property<\/strong>.<\/p>\n
\"A<\/p>\n
You can use the expression builder to get the output of the Lookup. Just add .value<\/code> at the end to retrieve the array. By default, the ForEach<\/code> will run everything in parallel. You can control the number of parallel items by setting the Batch count<\/strong> property. If you don\u2019t want any parallelism, you can check Sequential<\/strong>. Make sure you have an ordered array to have predictable results.<\/p>\n
All that is left is to parameterize the Copy<\/code> activity itself. In the Source<\/em> pane, select Enter manually<\/strong> in the Table settings and then use the dynamic content to set the source schema and source table name.<\/p>\n
\"A<\/p>\n
We can reference the current item of the iteration by using the @item()<\/code> function, followed by the desired column name of the metadata result set. In the Destination<\/em> pane, parameterize the destination table name<\/strong> and the Pre-copy script<\/strong> properties.<\/p>\n
\"A<\/p>\n
I\u2019m prefixing the table name with \u2018AW_\u2019<\/em>, which can be done with the @concat<\/code> function. The entire expression:<\/p>\n
@concat('AW_',item().DestinationTable)<\/pre>\n
Like before, the column mapping stays empty. The configuration is now finished and we can finally test our metadata driven pipeline (don\u2019t forget to activate the ForEach<\/code>). In the Output pane you can observe that 10 parallel copies have been started:<\/p>\n
\"A<\/p>\n
In the Fabric warehouse, the 10 tables are now created and populated with data, all with one single pipeline.<\/p>\n
\"A<\/p>\n
There\u2019s a downside to the approach we used though. More specifically, the auto-create table option is sometimes a bit \u201cgenerous\u201d when it comes to sizing string data types. We can view this by again querying the INFORMATION_SCHEMA<\/code> tables:<\/p>\n
SELECT\n\u00a0 \u00a0 \u00a0TABLE_NAME\n\u00a0 \u00a0 ,COLUMN_NAME\n\u00a0 \u00a0 ,DATA_TYPE\n\u00a0 \u00a0 ,CHARACTER_MAXIMUM_LENGTH\nFROM INFORMATION_SCHEMA.COLUMNS\nWHERE TABLE_NAME = 'AW_Address'\nORDER BY ORDINAL_POSITION;<\/pre>\n
\"A<\/p>\n
If you don\u2019t mind that every string column is 8000 characters in length, you can leave everything as-is. If not, you can construct a CREATE TABLE<\/code> statement dynamically by using metadata from the source system. This is left as an exercise for the reader.<\/p>\n
Conclusion<\/h2>\n
In this article, we\u2019ve shown you how you can easily set up a metadata driven pipeline to import sources external to Microsoft Fabric. All you need is metadata which you can either enter manually in some repository, or you can retrieve it from the source system if possible (like using the INFORMATION_SCHEMA<\/code> tables in this example).<\/p>\n
You can apply this pattern on most sources, such as relational databases, but also on files and REST APIs for example.<\/p>\n
By using the metadata driven approach, you can significantly save on development time, but it also improves consistency and maintainability of your data pipelines.<\/p>\n","protected":false},"excerpt":{"rendered":"
Over the past years, \u201ctraditional\u201d ETL development has morphed into data engineering, which has a more disciplined software engineering approach. One of the benefits of having a more code-based approach in data pipelines is that it has become easier to build metadata driven pipelines. What does this mean exactly? Say for example you need to……<\/p>\n","protected":false},"author":110905,"featured_media":103103,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528,53,159164],"tags":[158997],"coauthors":[159081],"class_list":["post-102060","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-bi-sql-server","category-featured","category-microsoft-fabric","tag-microsoft-fabric"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/102060","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/110905"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=102060"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/102060\/revisions"}],"predecessor-version":[{"id":103104,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/102060\/revisions\/103104"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/103103"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=102060"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=102060"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=102060"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=102060"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}