Global Aircraft✈️ Live Tracking with Microsoft Fabric Real-Time Intelligence
In this blog, I’ll walk you through, build a real-time global flight tracking system. We will ingest live flight data from the public OpenSky Network API using a Python polling script, stream it through Microsoft Fabric Eventstream into an Eventhouse (KQL Database), transform the dense raw arrays using KQL update policies and visualize the results on a Real-Time Dashboard complete with maps, KPIs and analytical charts. Prerequisites: Valid Fabric Capacity / Trail License Knowledge on Python Knowledge on KQL Step1: Setup Workspace & Eventhouse (KQL Database) Created a workspace “FlightTracking-[WS]” Created an eventhouse “FLightTracking-EH” Create a raw ingestion table “RawFlightbatc” in KQL Databse Step 2: Setup a Fabric Eventstream Created a Eventstream “GlobalFlightStream” and select ‘Use custom endpoint’ Click ‘Add’ Click on ‘Publish’ Copy the ‘Event hub name’ and ‘connection string-primary key’ into notepad Step 3: Notebook Creation and Setup Python script Created a notebook. Make sure select Python Install azure eventhub package Let’s go back to eventstream and add destination by selecting ‘Eventhouse’ Configure all details and click on save Comeback to Notebook and insert the Python script which is having all the connection strings / passwords etc. Run the notebook Now notebook started running and sending the data to eventstream Data is loaded into eventstream and Click on Publish Now eventstream is ‘Live’ Data is loading into KQL Database Step 4: Regularizing Data with KQL & Update Policies Create a cleaned table “FlightStates” Create the parsing function and update policy Alter table with updated policy Step 5: Building Real-Time Dashboard Click on Realtime dashboard and give a name “FlightOperationsDashboard” Now click on edit Run the below code to get total active flights count Change the chart to Stat and rename, click on Apply KPI added and click on Add visual and take new Stat visual Insert the code and run to get India Origin Flights and Format it and click Apply In the same way, I built other KPIs Select Map Chart Run the below code and Fill all details and Click on Apply. Here we’re calculating Flights trend We can see chart added to Dashboard Select a Bar chart Run the below code, fill all details and click on apply to add Bar chart to Dashboard. Here we’re getting the top 10 countries by aircrafts Run the below code, fill all details and click on apply to add Column chart to Dashboard. Here we’re categorizing the baro-altitude which is critical for airport delay predection Run the below code, fill all details and click on apply to add Pie chart to Dashboard. It splits the aircraft parked versus those actively flying Together, these visuals transform raw flight telemetry into an operational monitoring experience. Key takeaways: Fabric Eventstream provides a streamlined way to ingest external real-time feeds. Eventhouse/KQL Database provides a real-time analytical environment for flight telemetry. KQL mv-expand simplifies the processing of nested flight-state arrays. Update Policies automate transformation from raw streaming data into structured analytical data. KQL geospatial functions enable location-based flight analysis. Real-Time Dashboards transform streaming telemetry into actionable operational insights. Conclusion: This project demonstrates how Microsoft Fabric Real-Time Intelligence can be used to build an end-to-end real-time aircraft tracking solution. we can transform continuously arriving aircraft telemetry into meaningful real-time insights. Do you want to replicate? Get Code file from my GitHub link You can find all the KQL queries, update policy functions, and the complete Python polling script in the official GitHub repository below: [Download from here] Acknowledgements I would like to express my sincere gratitude to @SuryaTejaJosyul , @minniwalia and @rajendraongole1 for their continuous guidance and support throughout this Real-Time Intelligence (RTI) implementation. Their insights and encouragement played a key role in helping me complete this solution successfull Happy learning! — Inturi Suparna Babu [LinkedIn]Sequence Modeling for Data Science in Microsoft Fabric
In this edition, we're exploring how sequence modeling changes the way one thinks about data, why treating rows as independent often leads to shallow insights, and how RNNs and GRUs step in to model memory. By the end, you should have a clear intuition for when simple recurrence is enough, when gating really matters, and how to start applying this mindset in Microsoft Fabric (without overengineering anything!)Dataflow Gen2 vs Copy Job vs Pipeline: Choosing by Workload, Not by Habit
Every Fabric team has a default tool. People who came from Azure Data Factory build a pipeline for everything. Power BI people open Dataflow Gen2 for everything. Newer teams might put every table into a Copy Job because the wizard is quick. Each tool does its own job very well. Problems start when it gets used for another tool's job: pipelines full of hand-built watermark logic, dataflows used only to copy tables, and Copy Jobs expected to handle transformations they were never built for. This post gives you a simple way to pick the right tool based on what the workload needs. One sentence each Copy Job moves data from A to B, including incremental loads, with as little setup as possible. Dataflow Gen2 shapes data. It cleans, merges, reshapes and applies business rules using Power Query. Pipeline coordinates work. It runs steps in order, handles dependencies and failures, and ties everything together. If you remember only one thing, remember this: Copy Job moves, Dataflow shapes, Pipeline orchestrates. Start with three questions about the workload Ask these before you open any editor. Am I moving data or changing it? If the data arrives at the destination looking mostly like the source, with only column mapping or type changes, you are moving data. If you are joining, deduplicating, deriving columns or applying business logic, you are transforming it. How does the source change? A one-time or full reload is a different problem from an ongoing incremental sync. Incremental loads based on change data capture (CDC) are different again. How many steps depend on each other? A single load on a schedule is one thing. "Load these, then validate, then transform, then run a stored procedure, then alert someone if it fails" is a workflow. Your answers usually point clearly to one tool. Copy Job: when the job is data movement Copy Job is the newest of the three and the one most often overlooked by teams stuck in old habits. Microsoft's decision guide lists its main scenarios as incremental copy and replication (both watermark-based and native CDC), data lake and storage migration, medallion ingestion, and out-of-the-box multi-table copy. Microsoft Learn The incremental support is the main reason to use it. In a pipeline, the Copy activity handles incremental copy through pipeline expressions and control tables, and only with watermarks. That means you build and maintain the control table, the lookup, the parameterized query and the watermark update yourself. Copy Job does this for you. Microsoft Learn Choose Copy Job when: You need to ingest many tables from a database into a lakehouse or warehouse. You want an initial full load followed by incremental updates, and you don't want to build watermark logic. The source supports CDC and you want inserts and updates merged into the destination automatically. The destination is outside Fabric. Copy Job supports 40+ destination connectors. Microsoft Learn Microsoft's own example fits this well: an analyst who needs multi-table selection across regional SQL Server instances, a bulk initial load, and then CDC-based incremental merges picks Copy Job because it supports both watermark-based and native CDC incremental copying through a wizard, and automatically detects CDC-enabled tables. Microsoft Learn Don't choose Copy Job when: You need real transformation. Its transformation support is rated low, and that is intentional. Microsoft Learn The load is one step in a larger workflow with conditions, retries and downstream dependencies. That belongs in a pipeline. Dataflow Gen2: when the job is shaping data Dataflow Gen2 is Power Query running at Fabric scale. It is the right tool when the value lies in the transformation logic. It offers 170+ built-in connectors, 300+ transformation functions in a visual interface, and data profiling tools for checking data quality. Microsoft Learn A common complaint is that dataflows are slow for large volumes. That used to be a fair criticism, but Fabric has added several performance features aimed at specific workloads: Fast Copy is for direct, high-throughput copies from a supported source with no transformations. It uses the same backend as the pipeline Copy activity. microsoftmicrosoft Modern Evaluator helps when you are shaping data from connectors that don't fold, or only partly fold. microsoft Partitioned Compute is for large, partitioned or multi-file datasets that can be processed in parallel. microsoft Staging lets you land raw data first and transform it afterwards (ELT), so ingestion and transformation don't compete in one pass. microsoft Choose Dataflow Gen2 when: Business logic is the main work: cleansing, standardizing codes, merging sources, calculated columns. The people who own the logic know Power Query and would struggle to maintain Spark or SQL. You are combining files, APIs, SharePoint lists and databases into one clean dataset. You want visual data profiling while you build. Don't choose Dataflow Gen2 when: You are only copying tables. Fast Copy makes this workable, but Copy Job is simpler, gives you incremental loads without extra work, and has less to maintain. Your destination isn't supported. Dataflow Gen2 lists around 7+ destination connectors, compared with 40+ for the copy tools. Microsoft Learn The transformations are very complex or code-heavy. At that point a notebook is usually the better choice. Pipeline: when the job is coordination A pipeline is not mainly a data movement tool. It is the orchestrator. Microsoft describes it as low-code orchestration that groups several activities together to complete a task. The Copy activity inside it is powerful, and it remains a strong option for very large migrations. The guide describes it as the best low-code choice for moving petabytes of data into lakehouses and warehouses, either ad hoc or on a schedule. Microsoft LearnMicrosoft Learn The real reason to use a pipeline is the control flow: dependencies, branching, retries, failure handling, parameters, and calling other items. Choose a pipeline when: Several steps must run in a set order, where step B only runs if step A succeeds. You need logic such as If/Else, ForEach over a metadata list, or waiting on an external event. You are combining different item types, such as a Copy Job, a dataflow, a notebook, a stored procedure and a web call. You need error handling and notifications around the whole process. You are doing a large, custom migration where you want detailed control over the Copy activity. Microsoft's scenario for pipelines describes a workflow that runs stored procedures, calls web APIs, moves files and executes other pipelines. That is orchestration, not simple ingestion. Microsoft Learn Don't choose a pipeline when: It would contain a single activity that just runs one dataflow on a schedule. Dataflows can be scheduled on their own. You would be rebuilding incremental loading by hand when Copy Job already supports your source. Side-by-side Copy Job Dataflow Gen2 Pipeline Main job Move and replicate Transform and shape Orchestrate Incremental loads Built in (watermark and CDC) Possible, but not its strength Manual (watermark and control tables) Transformation Low High None itself (calls other items) Destinations 40+ ~7+ Depends on activities Authoring Wizard Power Query Visual canvas and expressions Best owner Data integrator, analyst Analyst, data engineer Data engineer Warning sign you picked wrong Adding transformation workarounds Dataflow has no transformation steps Pipeline has one activity Common mistakes that come from habit The "everything is a pipeline" team. Every source table gets a Lookup, a ForEach, a parameterized Copy activity and a stored procedure to update the watermark. It works, but you now maintain a small custom framework that Copy Job gives you ready-made. Keep the pipeline and let it call the simpler pieces. The "everything is a dataflow" team. Dataflows with forty queries that just select a table and load it. Move the raw ingestion to Copy Job and keep dataflows for the layer where logic actually happens. The "Copy Job does it all" team. Trying to handle business rules with column mappings, then adding SQL views downstream to fix what should have been transformed properly. Once logic appears, add a Dataflow Gen2 or a notebook. The single-activity pipeline. A pipeline whose only purpose is to run one item on a schedule adds a layer to monitor without adding control. Use the item's own schedule until you really need dependencies. The pattern that usually wins: use all three, each for its own job For a typical medallion architecture, the tools fit together naturally: Bronze with Copy Job. Ingest source tables with built-in incremental or CDC loads. No custom watermark logic. Silver and Gold with Dataflow Gen2 (or notebooks for heavy, code-first logic). Apply cleansing, conformance and business rules where they are visible and easy to maintain. Pipeline around everything. Run ingestion, then transformation only if ingestion succeeded, then refresh or post-processing steps, and send an alert if anything fails. Each tool does what it is best at, and each is simpler because it isn't doing another tool's job. A 30-second decision checklist No transformation, and data must stay in sync over time? → Copy Job One-off or very large custom migration needing fine control? → Copy activity in a pipeline Transformation logic is the main work, and owners know Power Query? → Dataflow Gen2 Complex, code-first transformation at scale? → Notebook Multiple steps, dependencies, branching or error handling? → Pipeline, calling the tools above Closing thought The right question isn't "which tool do we use?" It is "what does this workload need?" Movement, shaping and coordination are three different problems, and Fabric gives you a dedicated tool for each. Teams that choose by workload build less custom plumbing, find problems faster, and hand solutions over more easily, because each piece does one clear job. Next time you start a new load, answer the three questions first, then pick the tool.Forecasting with Autoregression, ARIMA & AIC / BIC for Data Science in Microsoft Fabric
In this edition, we’re exploring forecasting through Autoregression, ARIMA, and the model selection tools AIC and BIC. By the time you’re done reading, you’ll understand how data can actually learn from its own patterns, how ARIMA helps bring structure to unpredictable trends, and how AIC and BIC keep your models grounded by balancing accuracy with simplicity.Model Explainability That Goes Beyond the Notebook for Data Science in Microsoft Fabric
A machine learning model can be remarkably accurate and still leave people completely unconvinced. That is one of the strange realities of working in data science. You can spend weeks cleaning data, testing algorithms, tuning parameters, and validating performance, only to reach the moment when somebody looks at a prediction and asks a very reasonable question: “Why?” Suddenly, accuracy alone does not feel like enough. A probability score might tell you what the model believes, but it does not necessarily help the person receiving that prediction understand how the model arrived there. That gap between prediction and understanding is exactly where model explainability becomes important. What you will learn: In this edition, we’re exploring model explainability in production and how SHAP, FastAPI, and Plotly can work together to make predictions easier to understand. By the time you’re done with this, you’ll have a clear view of how SHAP can explain individual model decisions, how those explanations can be served dynamically through an API, and how interactive visualizations can turn technical attribution values into something far more approachable. Source: Sahir Maharaj (https://sahirmaharaj.com) It is tempting to think of explainability as a chart you generate after training a model, but production explainability is really an ongoing capability. Every new observation can produce a different prediction, and every prediction may have a different explanation. A customer predicted to leave a service might receive a high risk score because of declining engagement, while another customer receives the same score because of repeated service issues. The prediction may look identical at the surface, but the reasoning behind it can be completely different. That difference matters when somebody needs to decide what to do next. This is where SHAP becomes particularly valuable because it gives you a structured way to describe feature contributions. Imagine a model predicting whether a machine is likely to fail. The prediction itself might tell you that failure risk is 82 percent. Useful, certainly, but incomplete. SHAP can help reveal that rising operating temperature, vibration intensity, and unusually long operating hours are pushing the prediction upward, while another factor such as recent maintenance is reducing the predicted risk. Suddenly the prediction becomes much easier to reason about. You still have a probabilistic model, but you also have something resembling an explanation of its behavior. As a data scientist, I find that this distinction between global and local understanding is especially important. During model development, I often want to understand the model globally. Which variables matter most overall? Is the model relying heavily on variables I expected it to use? Are there surprising relationships hiding in the data? In production, however, people often care about something much more specific. They want to know why this customer, this transaction, this machine, or this observation received the prediction it did. That is a local explanation, and SHAP is particularly useful for helping bridge that gap. Source: Sahir Maharaj (https://sahirmaharaj.com) You can think about the difference through a credit-risk example. Globally, a model might rely heavily on payment history, debt ratios, and income stability. That tells you something useful about the overall model. But suppose one applicant receives a higher risk prediction than expected. A general feature importance chart does not really answer the person's question. You need to understand what happened for that particular prediction. Perhaps the debt ratio increased the risk score significantly, while long-term employment reduced it. That level of explanation provides much more context than simply showing which variables matter across thousands of predictions. Explainability can also become an important debugging tool for you as the person building the model. Suppose a model performs well according to the metrics you are monitoring, but its explanations repeatedly show that one unexpected variable dominates many predictions. That should make you curious. import numpy as np import pandas as pd import shap import matplotlib.pyplot as plt from sklearn.ensemble import RandomForestClassifier from sklearn.model_selection import train_test_split np.random.seed(42) n = 800 X = pd.DataFrame({ "usage_drop": np.random.normal(20, 10, n), "support_calls": np.random.poisson(3, n), "login_days": np.random.randint(1, 30, n), "tenure_months": np.random.randint(1, 72, n), "monthly_cost": np.random.normal(80, 20, n) }) score = ( 0.08 * X["usage_drop"] + 0.45 * X["support_calls"] - 0.05 * X["login_days"] - 0.02 * X["tenure_months"] + 0.015 * X["monthly_cost"] ) y = (score + np.random.normal(0, 1, n) > np.median(score)).astype(int) X_train, X_test, y_train, y_test = train_test_split( X, y, test_size=0.25, random_state=42 ) model = RandomForestClassifier(n_estimators=150, random_state=42) model.fit(X_train, y_train) explainer = shap.TreeExplainer(model) values = explainer(X_test) class_values = values[:, :, 1] if values.values.ndim == 3 else values shap.plots.beeswarm(class_values, max_display=5) plt.show() shap.plots.waterfall(class_values[0], max_display=5) plt.show() Imagine that somebody submits new information to a prediction service. The model receives those features, processes them, and generates a result. If explainability is part of the design, the same interaction can also produce SHAP information describing the important factors behind that specific prediction. Instead of storing explanations for every imaginable case beforehand, you generate them when they are needed. FastAPI fits naturally into this kind of workflow because it gives the prediction and explanation process a consistent interface. Rather than another application needing to understand your model directly, it can send the required information to an API. The API can coordinate the prediction process, calculate the explanation, and return the relevant output. The application consuming the result does not need to know the mathematical details of SHAP or how the model itself works internally. It simply receives information in an agreed structure. That separation can make a production system much easier to manage. From what I observe when building data science workflows, this separation becomes valuable surprisingly quickly. During early experimentation, everything often exists together. Data preparation, prediction, explanation, and visualization may all happen inside one environment. That is perfectly reasonable when you are exploring an idea. Production introduces more responsibilities. The application requesting a prediction should not need access to the entire modeling environment, and your model should not depend on somebody manually generating explanations. Creating a clear service boundary helps each part of the system do its job without becoming unnecessarily tangled with everything else. Dynamic explanations also give you flexibility over how much information you return. A technical monitoring application may want detailed SHAP values for every available feature. A decision-support interface may only need the five strongest contributors. Another application may need a short summary explaining the strongest positive and negative influences. The explanation engine can be the same while the presentation changes according to the audience. That matters because explainability is not only a modeling problem. It is also a communication problem. Source: Sahir Maharaj (https://sahirmaharaj.com) Performance deserves careful attention here as well. Calculating an explanation adds work beyond producing the prediction itself, and depending on the model, the amount of additional work may not be trivial. That does not mean explanations should be avoided. It means you should think about when they are required. Some systems may need an explanation for every single prediction. Others may generate explanations only when a user requests additional detail. You might also decide that certain high-impact decisions always receive explanations while low-impact automated predictions do not. Production design is often about finding that balance between completeness and responsiveness. There is also an important consistency issue. An explanation is only useful if it corresponds to the exact model and transformation process that generated the prediction. Imagine updating your model but accidentally continuing to generate explanations using an older version. The results could look perfectly reasonable while describing behavior that no longer matches the prediction being shown. import numpy as np import pandas as pd import shap import plotly.graph_objects as go from sklearn.ensemble import GradientBoostingClassifier np.random.seed(7) n = 700 X = pd.DataFrame({ "temperature": np.random.normal(70, 12, n), "vibration": np.random.normal(4, 1.5, n), "operating_hours": np.random.normal(900, 250, n), "maintenance_days": np.random.randint(1, 120, n) }) risk = ( 0.06 * X["temperature"] + 0.7 * X["vibration"] + 0.003 * X["operating_hours"] - 0.025 * X["maintenance_days"] ) y = (risk + np.random.normal(0, 1, n) > np.median(risk)).astype(int) model = GradientBoostingClassifier(random_state=7) model.fit(X, y) explainer = shap.Explainer(model, X) row = pd.DataFrame([{ "temperature": 92, "vibration": 6.8, "operating_hours": 1250, "maintenance_days": 75 }]) prediction = model.predict_proba(row)[0, 1] explanation = explainer(row) names = row.columns.tolist() impacts = explanation.values[0] waterfall = go.Figure(go.Waterfall( orientation="h", y=names, x=impacts, measure=["relative"] * len(names), text=[f"{x:+.2f}" for x in impacts], textposition="outside" )) waterfall.update_layout( title="SHAP Explanation: What Drove This Prediction?", xaxis_title="SHAP Impact on Model Output", yaxis_title="Feature", height=450 ) waterfall.show() gauge = go.Figure(go.Indicator( mode="gauge+number+delta", value=prediction * 100, number={"suffix": "%"}, delta={"reference": 50}, title={"text": "Predicted Machine Failure Risk"}, gauge={ "axis": {"range": [0, 100]}, "steps": [ {"range": [0, 40]}, {"range": [40, 70]}, {"range": [70, 100]} ], "threshold": { "line": {"width": 4}, "thickness": 0.8, "value": prediction * 100 } } )) gauge.update_layout(height=400) gauge.show() pd.DataFrame({ "Feature": names, "Value": row.iloc[0].values, "SHAP Impact": impacts }).sort_values("SHAP Impact", key=abs, ascending=False) The third objective is turning those explanation values into something people can interpret without needing to understand every detail of SHAP. This is where visualization becomes more than decoration. Raw values can tell you exactly how features influenced a prediction, but they require effort to interpret. A Plotly visualization can organise those contributions so the strongest influences immediately stand out. Features pushing a prediction in one direction can be separated visually from those pushing it in the opposite direction, allowing somebody to understand the basic story of the prediction within a few moments. Imagine a customer churn model that predicts an unusually high probability of cancellation. A dynamic explanation might show that a recent drop in product usage is the strongest contributor, followed by repeated support contacts and a reduction in login frequency. At the same time, a long customer relationship might slightly reduce the churn prediction. That is much more meaningful than presenting a table containing feature names and decimal values. The visualization helps the reader understand not only which features mattered but also how they worked together. Source: Sahir Maharaj (https://sahirmaharaj.com) When I look at explanations intended for other people, I try not to assume that more information automatically creates more transparency. It is very easy to generate a detailed visualization containing dozens of variables simply because the values are available. The result may technically be comprehensive while being practically overwhelming. If somebody has to study a chart for several minutes before understanding the main message, the explanation probably needs refinement. In many situations, highlighting the strongest contributors and allowing additional detail to be explored when needed creates a much better experience. Interactivity can make this even more useful. Plotly allows explanations to become something people can investigate instead of merely observe. A user might hover over a feature to inspect the contribution more closely or compare several predictions to understand why similar observations received different results. Imagine comparing two loan applications that received different risk scores. An interactive explanation can make it easier to see that one prediction was strongly influenced by debt levels while another was affected primarily by inconsistent payment history. That comparison helps reveal how the model responds to different combinations of information. At the same time, visual polish should never become a substitute for clarity. A sophisticated interactive chart can create an impression of authority, and that means you have a responsibility to provide enough context around what the visualization represents. The user should understand that the chart explains the model's behavior, not necessarily the underlying truth of the real-world situation. If a feature makes a large contribution, that means the model relied heavily on that feature for the prediction. It does not automatically prove that the feature caused the outcome. Keeping that distinction clear is a major part of responsible model communication. Source: Sahir Maharaj (https://sahirmaharaj.com) So give it a try. Do not wait until you have a huge production system or a complicated machine learning platform before thinking about transparency. Start with one prediction, one explanation, and one clear visualization. Build from there as your understanding grows. The more comfortable you become explaining what your model is doing, the easier it becomes to recognise when its behaviour deserves a closer look. And that ability is valuable whether you are just entering data science or have been building models for years. If you ever need anything while exploring it, just reach out to me. I’ll be more than happy to help. Thanks for taking the time to read my post! I’d love to hear what you think and connect with you! 🙂 LinkedIn Kaggle Topmate (Free Power BI / Data Science Sessions and Resources) Website The Tech Journal (Blog) About the author Sahir Maharaj is a Lead Data Scientist who leads the design and deployment of end-to-end AI solutions that drive strategic decisions at scale. As a Microsoft MVP, he has been featured internationally, including in The Indian Express and on New York's Times Square billboards, and is a prolific content creator on LinkedIn.Mastering Advanced Regression for Data Science in Microsoft Fabric
In this edition, we’re exploring two regression techniques that every data professional eventually bumps into when the simple models stop telling the full story. You’ll get a clear sense of what quantile regression actually solves, especially when your data behaves in unpredictable or uneven ways. By the time you’re done, you’ll feel more confident choosing the regression approach that truly fits the question you’re trying to answer, instead of defaulting to whatever is familiar.Microsoft Fabric, RAG, and the Conversations That Follow You Home
In this edition, we will look at why RAG is quickly becoming a much bigger conversation than simply retrieving a few documents before asking an AI model a question. From there, I want to explore something I think deserves far more attention: what happens when retrieval becomes messy, contextual, relational, and dependent on human feedback. And finally, I want to look at what all of this means for data professionals working around Microsoft Fabric and trying to understand where they fit as AI systems become more capable.Easy and Fast Reporting Analysis with PowerBI Copilot
Tired of digging through reports to find what you want? Did you know you can get the answer you need with just a single prompt? With a well-designed Semantic Model and reporting, getting the answers you want is now possible. In this article, we examine step-by-step how Copilot works through the chat panel and how it responds to different questions.
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.
