Configuring Data Destinations in Bulk with MS Fabric Dataflow Gen2

In this blog post, we'll dive into how you can hook up Dataflows to different data sources, use Dataflow Gen2 for data transformations, and manage data destinations across various Microsoft Fabric services.

What is Dataflow Gen2?

Dataflow Gen2 is the next version of dataflows from Microsoft, made to make data integration and transformation even better within the Microsoft Fabric ecosystem. It takes what Data Flow Gen1 did and adds new features and improvements to handle more data processing needs using the same Power Query environment.

Configuring Data Destinations: Challenges and Solutions

One of the great features of Gen2 is that you can now choose where to store your transformed data by setting up a data destination for each table. Here are some supported data destinations in DFG2:

Azure SQL databases
Azure Data Explorer (Kusto)
Fabric Lakehouse
Fabric Warehouse
Fabric KQL database

Every data query in your Dataflow Gen2 can have its own data destination. You can set the data destination for each query separately and use different destinations within the same dataflow.

Here's how you can set up a data destination:

Challenge

Let's say you have two workspaces: one for development and one for production. You've set your data queries to be stored in the Lakehouse in the development workspace. Now, you're ready to update your data destination to the production Lakehouse. If you created your Dataflow Gen2 directly from the list below, you'll need to set up the data destination for each query manually.

Solution

The solution is more straightforward than you might think. We can also create Dataflow Gen2 from storage artifacts. For example, in Lakehouse, we have the option to "Get Data". By using this option and if we setup our data queries, the data destination will be pre-configured to the same Lakehouse from which we created the Dataflow Gen2.

Here's a screenshot that shows the same Lakehouse is pre-configured for the query I created. This configuration will apply to any number of queries you create.

Configure Data Destination In Bulk

Now, you might be wondering how to move your existing transformations from another Dataflow Gen2 or update the development Lakehouse to the production Lakehouse. You probably already know these simple steps. We can simply copy and paste all data queries from the development Dataflow Gen2 to the production Dataflow Gen2.

Now, paste these queries into the production Dataflow Gen2 using the keyboard shortcut Ctrl + V.

As you can see, these queries did not have any data destination configured in Dataflow 1. After pasting them into Dataflow 2, all queries' data destinations were automatically configured to the same Lakehouse from which we commissioned this Dataflow Gen2.

This solution is also applicable to other storage artifacts such as Warehouse, KQL Database, etc.

Summary

In this article, we looked at how Dataflow Gen2 makes data integration and transformation easier in the Microsoft Fabric ecosystem. We talked about the hassle of setting up data destinations for each query and shared a simple way to update data destinations in bulk, especially when moving from development to production.