Orchestration Tool Selection in Microsoft Fabric Data Factory
Why Tool Choice Is an Engineering Decision Picking the wrong orchestration primitive is a debt that compounds silently. A team that builds everything in notebooks because 'that's what we know' will eventually own fifty interdependent notebooks with hard-coded paths, bespoke retry loops, and monitoring gaps — maintained by two people. The failure mode is not technical; it is organisational. When the business analyst can't touch the transformation and the ops team can't centralise the logs, delivery velocity collapses. Microsoft Fabric Data Factory ships three distinct orchestration primitives — Pipelines, Dataflow Gen2, and Notebooks — because no single tool is optimal across all workloads. This article helps you map each tool to the scenario it was built for, with concrete code and configuration examples. Tool Selection Matrix Use the matrix below as your starting point. Real solutions usually combine more than one tool; treat each row as guidance for an individual task within a broader workflow. Decision Flowchart Step through the questions below in order, stopping at the first 'yes'. Most real pipelines combine several tools, so run the flowchart once per task — not once per project. Pipelines — Orchestration and Control Flow Pipelines are the scheduler and coordinator. They do not transform data; they determine when, in what order, and under what error conditions other activities run. When to choose a Pipeline Scheduling: Trigger-based execution (tumbling window, schedule, storage event). Fan-out: ForEach iterates over a dynamic list — e.g., 10 source schemas loaded in parallel. Error handling: Until activity with configurable back-off; conditional branching on activity outcome. Centralised monitoring: Run history, duration, and failure reason are surfaced in the Fabric monitoring hub — no custom logging code required. Dataflow Gen2 — No-Code Transformations Dataflow Gen2 exposes the Power Query engine behind a visual interface. Any engineer who has used Excel Power Query or Power BI can build and maintain transformations without writing a single line of Python or SQL. When to choose Dataflow Gen2 Analyst ownership: Transformations that business analysts or BI developers will modify post-deployment. Moderate complexity: Filters, joins, type coercions, aggregations, pivots — anything expressible in the Power Query formula language (M). Connector breadth: 300+ connectors out of the box; no custom connector code needed. Small-to-medium data: Power Query engine handles millions of rows comfortably; hand off to Spark Notebooks for billions. Notebooks — Programmatic Transformations Spark Notebooks are the right tool when the transformation logic exceeds what a visual canvas can express, when you need ML libraries, or when data volume demands distributed processing. When to choose a Notebook Complex logic: Nested conditionals, custom scoring functions, recursive lookups. Machine learning: scikit-learn, MLflow, Spark MLlib — all available in the Fabric Spark runtime. Big data: Billions of rows — Spark partitions the work across the cluster automatically. Developer ownership: Engineers maintain the code; version it in Git like any other source artefact. Common Integration Patterns Production-grade data platforms rarely use just one tool. The patterns below represent the most common compositions and the scenarios they address. Pattern 1 — Schedule → Dataflow → Warehouse A schedule trigger fires a Pipeline that runs a Dataflow Gen2 activity to clean and join source data, then writes the result directly to a Warehouse table. Operations monitors via Pipeline run history; analysts modify the Dataflow independently. Use case: Daily sales consolidation — analysts own the transformation, engineers own the schedule. Pattern 2 — Pipeline → Notebook → Email Notification A Pipeline runs a Notebook activity that executes complex PySpark logic (e.g., anomaly detection), then passes the output path to a Web activity that calls a Logic Apps endpoint to send an alert email. Use case: Nightly data quality checks that trigger ops alerts when anomaly thresholds are breached. Pattern 3 — Dataflow seeds Lakehouse, Notebook reads for ML A Dataflow cleans raw customer records and writes to Delta tables. A separate Notebook reads those tables to train a churn prediction model, logging the run to MLflow. The two artefacts are decoupled — analysts iterate on data prep, data scientists iterate on the model. Use case: Productionising an ML workflow without coupling data engineering to data science release cycles. Pattern 4 — End-to-End ETL Pipeline A single Pipeline chains four activities: (1) Copy Data extracts files from an external SFTP; (2) a Dataflow Gen2 activity standardises column names and types; (3) a Notebook applies business rules and ML scoring; (4) a second Copy Data loads results to the Warehouse; (5) a Web activity posts a completion notification to Teams. Performance Considerations Different tools have different performance envelopes. Matching data volume to the right engine avoids both over-engineering and under-provisioning. Pipeline — Copy Data activity The Copy Data activity is optimised for high-throughput bulk transfers. Increase Data Integration Units to scale throughput horizontally. Enable staging through Azure Blob Storage for transfers between incompatible source and destination connection types. This activity performs best on large file copies and full database extracts where no row-level transformation is required. Dataflow Gen2 The Power Query engine handles datasets comfortably in the millions-of-rows range. Enable staging in the Dataflow settings pane to off-load compute from the on-premises data gateway and execute transformations in the cloud. For datasets larger than this threshold, the latency and memory characteristics of the Power Query engine make a Spark Notebook the better choice. Notebooks — Spark Spark Notebooks distribute computation across a cluster and are the appropriate engine for datasets in the billions-of-rows range. Tune executor core and memory configuration in the Spark pool settings. Apply Delta Lake Z-ORDER clustering on frequently filtered columns to reduce the volume of data scanned per query. Use broadcast joins for small dimension tables to avoid expensive shuffle operations across the cluster. Key Takeaways for Data Engineers Getting tool selection right from the start avoids costly rewrites and knowledge silos later. Three principles guide sound architectural decisions in Data Factory: 1. Match the tool to the person who will maintain it — not just the person who builds it. A Dataflow maintained by an analyst and a Notebook maintained by an engineer are both valid choices; a Notebook that only two engineers understand is a liability. 2. Pipelines orchestrate; they do not transform — resist embedding transformation logic in pipeline expressions or Copy activity mapping columns. Keep orchestration and transformation concerns in separate artefacts. 3. Composition beats monoliths — a Pipeline that orchestrates a Dataflow and a Notebook is easier to debug, test, hand over, and evolve than a 500-line PySpark Notebook that handles extraction, transformation, loading, and notification in a single script. The architectural principle to internalise: Pipeline orchestrates, Dataflow transforms visually, Notebook transforms programmatically. Maintain this separation of concerns consistently and the architecture will scale with your team and your data volumes.Building Your First Pipeline in Microsoft Fabric
Data rarely lives where you need it. More often than not, the first real challenge of any analytics project is simply getting information from one place to another, reliably and on a schedule. This is exactly where Microsoft Fabric pipelines shine. If you have worked with Azure Data Factory before, much of this will feel familiar, but Fabric brings everything together inside a single, unified workspace and adds a few welcome conveniences along the way. In this article, we will walk through creating your very first Fabric pipeline from the ground up. We will start by setting up the pipeline and exploring the activities available to us, then move on to scheduling and monitoring. Finally, we will build a complete, end-to-end data movement that copies a file from an Azure Data Lake Storage (ADLS) account into a lakehouse. By the end, you will have a working pipeline and a solid mental model of how the pieces fit together. Creating the Pipeline Begin by opening your Newsletter_Pipelines workspace. To create a new pipeline, click on New Item. Give your pipeline a name — in this case, we will call it Ingestion Data. Once you click OK, you are taken to the pipeline authoring canvas shown below. From here, we can start building the pipeline from scratch. Click on the activity you want to add — we will choose Copy Data. Next, click on Activities. Exploring Activities At the top of the canvas you will find the activities bar. Clicking the Activities tab reveals the most commonly used activities right away. If you need something beyond those, the three dots open up a much longer list of options. From here you can, for instance, connect to Databricks or trigger a notification. One of the most useful additions in Fabric is the Outlook activity, which lets you send an email directly from the pipeline. You can even post a notification to Microsoft Teams. These options were not available in Azure Data Factory, so for data engineers they are a genuinely handy way to keep stakeholders informed. Scheduling and Running the Pipeline Adding activities is only half the story — at some point you will want the pipeline to run automatically. To handle that, click the Run button at the top. From there you can run the pipeline immediately, or set up a schedule. Clicking the Schedule button opens the scheduling options, where you can define how and when the pipeline runs. If you would rather use a different kind of trigger — a storage-based trigger, for example — you can configure that here as well. And whenever you want to check on past executions, View Run History gives you a complete record of every run. On the right-hand side, a slider lets you zoom in and out of the canvas as needed. A quick orientation tip for anyone new to Data Factory and pipelines: clicking anywhere outside the activity box surfaces the properties for the overall pipeline configuration in the bottom panel, while clicking inside the box shows the settings specific to that individual activity. Your First End-to-End Data Movement Now it is time for a complete Fabric data movement. Our goal is to build a pipeline that moves data from an Azure Data Lake Storage account into a lakehouse. To see what we are working with, head back to the ADLS account and open the landing container. Inside, you will find the source files. Of the three files here, suppose we only want to move orders.csv from ADLS to the lakehouse. To do that, we will use the Data Factory pipeline we just started building. We already have a pipeline with a single Copy activity in place. The first thing worth doing is renaming that activity to something meaningful — for example, Transfer data from ADLS to a Lakehouse. Naming the activity this way is not mandatory, but it is good practice. A descriptive name makes the pipeline far easier for other developers to understand at a glance; they can tell what the activity does without having to open it and inspect the source and destination. Configuring the Source To move the data, click on the Source tab and set up a connection to the source. Selecting Browse all reveals the wide range of data sources Fabric supports. We want to connect to an Azure Data Lake Storage account, but if you choose View more you will see that Fabric can also connect to SharePoint, Salesforce, Oracle, FTP, SFTP, and many others. Since we only need Data Lake Storage, type Data Lake at the top, find Azure Data Lake Storage, and click it to open the connector. With the connector open, we need to supply the URL, and Fabric will then ask for authentication. The credentials can be an organizational account, a SAS token, or an account key. In this example we will use a SAS token to connect to the Azure Data Lake Storage account. To generate one, go back to the Data Lake Storage account and search for SAS, then select Shared access signature. On the SAS configuration screen, allow all resource types, grant all permissions, and set the expiry far enough out so that you can still access it later. Then click Generate SAS and connection string. You will receive both a connection string and a SAS token. For now, copy the connection string and return to Fabric. When Fabric asks whether you are creating a new connection, choose yes, and paste in the URL. Paste the URL carefully. The example shows a format like https://<storage-account>.dfs.core.windows.net. When you copy and paste, however, the value often comes through as a blob type rather than dfs. Simply change blob to dfs manually and it will work correctly. We want the path to point as far as the landing container, so type landing here. Finally, give the connection a name — we will call it ADLS connection. Since this is neither a private nor an on-premises network, no data gateway is required, so leave that blank. Under authentication type, choose Shared access signature. Now return to the Azure portal, copy the SAS token, come back to Fabric, and paste it in. To recap: we supplied the URL, changed blob to dfs, named the connection, and provided the SAS token. With that done, click Connect. The connection takes a moment to establish and will eventually succeed. To confirm everything is working, click Test connection — you should see that the connection is successful. Next, choose which file or folder to bring in using the file path. The connection is made, but now we browse to a specific file — in this case, orders.csv. Click Browse, open the landing container, select orders.csv, and click OK. Because the file is a CSV, set the file format to DelimitedText. If you need finer control, the Settings tab lets you adjust details such as the column delimiter (useful when the file is not comma-separated) and whether the first line is a header. For now, click OK and move on to the destination. Before moving on, you can confirm the data looks right using the Preview tab. Clicking Preview Data shows the incoming records, and in this case everything reads perfectly. Configuring the Destination Now switch to the Destination tab. As before, there is no existing connection to our lakehouse, so click Browse All. We want to connect to the lakehouse, so type lakehouse. Rather than selecting the New Fabric item option, we will use the OneLake Catalog. Click View More, and under it you will find the e-commerce catalog — exactly where we want our data to land. Select it to make the connection to Orders_lakehouse. With the connection in place, we are almost ready to run. Fabric asks whether to copy the data into the Tables folder or the Files folder; we will save to Files for now. When prompted for a file path, you can browse the folders available in the lakehouse. We will create a new path called copied via pipeline and copy the data there. You can also set the output file format by clicking through the options — we will keep the data in Parquet format. Select Parquet, and we are done. All that remains is to execute the pipeline so it pulls the data from ADLS and pushes it into the lakehouse. Running and Verifying the Pipeline Click the Run button, then choose Save The pipeline now starts running, and full execution takes a little while. The Output tab shows live details. Initially the run sits in a queued state, waiting for resources. Once resources are assigned, it moves into the in-progress state and then completes. You can watch it progress from queued, to in progress, to succeeded. Our pipeline has executed successfully. Clicking into the run details, we can see it copied 3,000 records from the source file into the lakehouse, which now also holds 3,000 records. You will notice that the data read size and data written size differ. That is because Parquet is a compressed format, so the data footprint shrinks once written. To confirm the data actually landed, navigate to the lakehouse, where you will see the new copied via pipeline folder. Open it, and the Parquet files are right there. Wrapping Up And that is how you copy data from an ADLS account into a lakehouse using Fabric pipelines. In just a handful of steps, we created a pipeline, explored the activities Fabric offers, set up scheduling and monitoring, and built a complete data movement from source to destination — verifying along the way that every record arrived safely. The real takeaway is how approachable this process has become. What once required stitching together separate tools now happens inside a single, cohesive workspace, complete with conveniences like Outlook and Teams notifications that simply were not available before. With this foundation in place, you are well positioned to build richer pipelines: chaining multiple activities, adding transformations, and orchestrating sophisticated, scheduled workflows. Your first pipeline is rarely your last, but it is the one that makes everything that follows feel possible.Site to Insight: Fabric’s New SharePoint Picker
One of the greatest things about attending FABCON in Atlanta recently wasn’t just the incredible community or the deep-dive sessions – it was those “aha!” moments during the keynote announcements. Among the many massive reveals, there was one specific update that caught my attention as a massive “quality of life” win: the SharePoint Site Picker (Preview). For anyone who works between SharePoint and Fabric daily, this was easily one of my favourite announcements of the conference. Here’s why it’s a gamechanger.Creating Azure Data Lake Storage (ADLS) in Azure: A Step-by-Step Guide
In modern data platforms, building efficient and reliable data pipelines is at the heart of every data engineering workflow. This is where Microsoft Fabric Data Factory comes into play. It provides a powerful, intuitive interface to design, orchestrate, and automate data movement across different systems. With its visual pipeline designer, rich set of activities, and built-in scheduling and monitoring capabilities, Data Factory enables data engineers to create scalable workflows—from simple data ingestion to complex transformations—without heavy coding. As we move from understanding the interface to building real solutions, the next essential step is preparing a robust data source. In this article, we will start by creating an Azure Data Lake Storage (ADLS) account in Azure, upload sample data, and lay the foundation for our upcoming data pipelines. We’ll begin by setting up an Azure Storage account, where we’ll upload some sample data that will later be used in our data pipeline. I’ll assume you already have access to a Microsoft Azure account. Start by opening a new browser tab and navigating to the Azure portal (portal.azure.com). From there, we’ll proceed to create the storage account within your Azure environment. To set up the storage account, simply search for “Storage” in the Azure portal, where you’ll find the Create option—go ahead and click it. You’ll then be prompted to select your subscription and define a new resource group. Next, provide a unique name for your storage account. You can leave the region and other settings as default if they suit your needs and proceed by clicking Next. To configure it as an Azure Data Lake Storage account, make sure to enable the Hierarchical Namespace option. After that, continue clicking Next through the remaining steps. In the final stage, you’ll see a summary page displaying all your selected configurations. Take a moment to review everything, and once you’re satisfied, click Create to proceed. The deployment may take a few moments to complete. Once the Azure Storage account is successfully created, navigate to the resource to continue. Once the storage account is ready, the next step is to create a container. Navigate to Data Lake Storage, add a new container, give it a name like Building Container, and then click Create. Within this container, we can now upload our sample data. Simply select the required files—such as orders, products, and customers—and click Upload to add them. That’s it—your data has been successfully uploaded to the Azure Data Lake Storage account. In the next article, we’ll build a pipeline to read this data from ADLS and load it into a Fabric Lakehouse.Getting On-Premises SQL Server Data to Microsoft Fabric Lakehouse
For the last few days, I have been working on the Contoso Sales data to create a Power BI report as part of the learning. Currently, I am using the default ready to go Power BI data model provided by Microsoft which can be found here. As Microsoft Fabric is the new tech buzz, so I thought why don’t I get this data somehow in the Fabric environment.
