From ADF Inventory to a Fabric Operating Model: A Practical Migration Playbook
Migrating from Azure Data Factory to Microsoft Fabric is not a one-for-one conversion. This practical playbook helps you assess existing workloads, choose the right Fabric pattern—Mirroring, Copy jobs, Pipelines, or Notebooks—and validate the move safely through metadata-driven design, reconciliation, and phased cutover.
Understanding SHOWPLAN_ALL in Fabric SQL
As data engineers, we spend a significant amount of time writing SQL queries to ingest, transform, and analyze data. However, producing the correct result is only half the story. Equally important is understanding how the SQL Server Query Optimizer executes our queries. One of the most effective ways to inspect the optimizer's decisions is by using SHOWPLAN_ALL. In this article, I'll demonstrate how to use SHOWPLAN_ALL in SQL Server and Microsoft Fabric SQL Database, explain what it does, discuss a common pitfall, and show you how to resolve it. What is SHOWPLAN_ALL? SHOWPLAN_ALL is a session-level SQL Server setting that instructs the query optimizer to return the estimated execution plan instead of executing the query. Rather than returning data, SQL Server provides detailed information about the physical operators it intends to use, allowing us to understand how the query will be processed before it runs. This is particularly useful when: Investigating slow-running queries. Understanding optimizer decisions. Identifying expensive operations such as sorts and scans. Comparing different query implementations. Tuning SQL for better performance. Unlike PostgreSQL, MySQL, Oracle, or Databricks SQL, which support variations of the EXPLAIN command, SQL Server relies on SHOWPLAN_ALL and graphical execution plans. Orders Table For this walkthrough, I'll use anorders table in Fabric SQL CREATE TABLE orders ( order_id INT PRIMARY KEY NOT NULL, order_date DATE NOT NULL, customer VARCHAR(20) NOT NULL, amount INT NOT NULL ); After populating the table with data, I'll calculate a running total using a window function. Sample Query SELECT order_id, order_date, customer, amount, SUM(amount) OVER ( ORDER BY order_date, order_id ) AS running_total FROM orders ORDER BY customer, order_date, order_id; Without any execution plan settings enabled, SQL Server executes the query normally and returns the dataset. Viewing the Estimated Execution Plan To inspect how SQL Server intends to execute the query, enable SHOWPLAN_ALL. SET SHOWPLAN_ALL ON; GO SELECT order_id, order_date, customer, amount, SUM(amount) OVER ( ORDER BY order_date, order_id ) AS running_total FROM orders ORDER BY customer, order_date, order_id; GO Instead of returning rows from the orders table, SQL Server returns an estimated execution plan describing the physical operations that would be performed. Although the exact operators depend on the optimizer and available indexes, the execution plan typically resembles the following sequence: Read the data from the orders table. Perform any required sorting for the window function. Compute the running total using the Window Aggregate operator. Apply the final ORDER BY. Return the results. This visibility into the optimizer's decision-making process is invaluable when diagnosing performance issues. A Common Pitfall One of the most common mistakes developers make is assuming that SHOWPLAN_ALL only affects the next query. It doesn't. SHOWPLAN_ALL is a session-level setting. Once enabled, every subsequent query in the same session returns an execution plan instead of executing. For example, after running: SET SHOWPLAN_ALL ON; GO Even a simple query such as: SELECT * FROM orders; returns the execution plan rather than the table data as seen below If you're unaware that SHOWPLAN_ALL is still enabled, it can be quite confusing because every query appears to "stop working." The Solution The fix is straightforward. Disable the session setting. SET SHOWPLAN_ALL OFF; GO After turning it off, SQL Server immediately resumes normal execution. Running the same query again returns the expected dataset. Why This Happens Many SQL Server settings persist for the duration of the current session. SHOWPLAN_ALL is one of them. Other commonly used session-level settings include: SHOWPLAN_XML STATISTICS IO STATISTICS TIME NOCOUNT Understanding session scope is important when troubleshooting unexpected SQL Server behavior, particularly during performance tuning. As data volumes continue to grow, query performance becomes increasingly important. Execution plans provide insights that cannot be obtained simply by reading the SQL statement. They help answer questions such as: Is SQL Server performing a Table Scan or an Index Seek? Is an unnecessary Sort operation occurring? Which operator consumes the highest estimated cost? Is the optimizer using a Window Aggregate efficiently? Can the query be rewritten to reduce resource consumption? These are exactly the questions that distinguish writing SQL from engineering performant SQL solutions. Key Takeaways If you regularly work with SQL Server or Microsoft Fabric SQL Database, keep the following in mind: SHOWPLAN_ALL returns the estimated execution plan without executing the query. It is a session-level setting, not a one-time command. Every query continues returning execution plans until the setting is explicitly disabled. Use SET SHOWPLAN_ALL OFF to restore normal query execution. Learning to interpret execution plans is an essential performance tuning skill for data engineers and database professionals. Final Thoughts Window functions, Common Table Expressions (CTEs), and complex analytical queries are becoming increasingly common in modern data platforms. While writing these queries correctly is important, understanding how the SQL Server Query Optimizer executes them is what enables us to build scalable and efficient data solutions. SHOWPLAN_ALL offers a simple yet powerful way to inspect the optimizer's strategy before a query is executed. Combined with graphical execution plans and tools such as STATISTICS IO and STATISTICS TIME, it forms an essential part of every data engineer's SQL performance tuning toolkit. The next time you're optimizing a query, don't just verify that it returns the correct result—take a few minutes to examine how SQL Server plans to execute it. The insights you gain can often reveal opportunities for significant performance improvements.OneLake Security + Shortcuts: The RLS Architecture That Actually Holds Up in Production
You configure row-level security on your gold Lakehouse. You test it rows filter correctly. You ship it. Two weeks later, another team creates a shortcut from their workspace and discovers they see every row. This isn't a Fabric bug. It's the consequence of conflating Power BI RLS, SQL endpoint security policies, and OneLake Security and assuming they propagate through shortcuts the same way. They don't. This article is the reference I wish I'd had.Choosing the Right Way to Run Python in Microsoft Fabric
Fabric gives us several ways to run Python, and at first they can look overlapping. In this post, I share the practical decision model I use to choose the right option based on execution mode, compute engine, and data access path. If you are code-first and want fewer wrong turns when moving from exploration to production, this guide is for you.Direct Lake Is Changing the Lakehouse vs Warehouse Debate in Microsoft Fabric
Most Fabric discussions still focus on Lakehouse versus Warehouse. I believe that's increasingly the wrong question. Thanks to Direct Lake, many organizations can now go directly from Lakehouse to Power BI without introducing a Warehouse layer. But there are important trade-offs and hidden performance considerations that every Fabric architect should understand before making that choice. Let's dive into what really drives the decision.Medallion to Magic — Manufacturing Intelligence Platform on Microsoft Fabric
Microsoft Fabric brings Data Engineers, Data Analysts, and Business Users onto a single platform. Data Engineers build the ingestion, Lakehouse, Warehouse, and dbt transformation layers that move raw factory data through the Bronze → Silver → Gold Medallion layers. Data Analysts design the DirectLake Semantic Model, author the DAX measure library, and build the Power BI reports that surface production readiness intelligence. Business Users (manufacturing operations, supply chain managers, and executives) consume those insights through Power BI, the Inventory Insights data agent, and M365 Copilot, asking questions in natural language without ever opening Fabric. Inspired by the Data Factory & Data Integration Community Challenge. I built and end-to-end analytical solution on Microsoft Fabric, integrating batch-exported operational data from four U.S. factories, transforming it through the Medallion pattern, and surfacing the results through Power BI and an AI data agent.The Fabric Admin Trap: Scaling Your Cleanup
We’ve all been there. It’s Friday afternoon, and you’re looking at your Microsoft Fabric tenant. It’s cluttered with dozens of abandoned test workspaces, half-finished projects, and “oops, I forgot to delete this” environments. You open the portal. You click. You wait for the page to refresh. You click again. You feel the rage slowly building. As admins, we are supposed to be power users, but we often spend more time navigating UI menus than actually managing our data. I decided enough was enough and turned to the Microsoft Fabric CLI (fab) to take back control. But the path to automation wasn’t a straight line.
