From ADF Inventory to a Fabric Operating Model: A Practical Migration Playbook (Part 2)
A practical guide to building a portable, metadata-driven ingestion framework for Microsoft Fabric. Learn how JSON configuration, Pipelines or Airflow orchestration, watermarks, retries, and audit tables work together to make data ingestion scalable and safe.
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.An Overview of Lakehouses and Data Warehouses
Unlocking the Future of Data: Lakehouses vs. Data Warehouses In today’s data-driven world, choosing the right architecture is crucial for turning information into insight. Are traditional data warehouses still the gold standard, or are modern lakehouses rewriting the rules? Dive into our latest article as we explore how these two powerful approaches stack up — and discover which one could transform your data strategy.Microsoft Fabric Data Warehouse
Microsoft Fabric Data Warehouse (Azure Synapse) Microsoft Fabric introduces a new era in data management, and at its core lies a transformative capability—the Data Warehouse, also known as Azure Synapse within Fabric. While it builds on familiar concepts from traditional BI systems, Fabric’s Data Warehouse breaks new ground by offering a truly open, scalable, and fully integrated analytical environment. This article offers a high-level overview of Microsoft Fabric Data Warehouse, providing foundational insights for professionals ready to embrace modern data architecture.# Top 10 Anti-Patterns in Fabric Warehouse Production Code Part -2
You fixed the loops, batched the loads, and listed your columns explicitly. Good. But your Fabric Warehouse still has hidden cost leaks. In Part 2, we tackle the six anti-patterns that don't show up until your tables are large and your team is shipping fast: scalar UDFs that silently dodge Fabric's inlining engine, MERGE statements that join on the wrong column (and why case-sensitivity makes it worse), small-file fragmentation from over-partitioned lakehouse shortcuts — and the OPTIMIZE + VACUUM + V-Order trio that fixes it — plus three CI/CD habits (missing column lists, hardcoded workspace GUIDs, and DROP-recreate cycles) that quietly erase your statistics, your permissions, and your Monday morning peace of mind. Ten anti-patterns. Two posts. One rule: Fabric Warehouse is not SQL Server.Microsoft Fabric: A Data Engineer's Perspective. What I Learned Building Real Pipelines
After months of building production-grade data workflows on Microsoft Fabric, I share what genuinely works, what requires workarounds, and where the platform is heading from ingestion to transformation to serving.FabricSharepointCopy – An open-source utility to ingest Sharepoint Data into Microsoft Fabric
Organizations often rely on SharePoint as a lightweight data exchange layer—teams upload CSVs or Excel files that are later consumed for reporting and analytics. While convenient, this pattern frequently leads to manual ingestion, inconsistent schemas, delayed refreshes, and downstream data quality issues. To address this gap, we’re excited to opensource FabricSharePointCopy utility, a framework that provides a standardized, metadatadriven way to ingest files from SharePoint into Microsoft Fabric Lakehouse tables, with builtin validation and automation. The project is now available on GitHub: https://github.com/microsoft/FabricSharePointCopy What is FabricSharePointCopy? FabricSharePointCopy is a utility framework for seamlessly transferring files from SharePoint into Microsoft Fabric managed tables, enabling structured, Lakehouseready data for downstream analytics and reporting. The framework focuses on: Standardizing ingestion from SharePoint Enforcing data quality before publish Reducing manual intervention Making curated data quickly available to Fabric consumers It is designed to be generic, reusable, and extensible, rather than tied to any single business domain. Why We Built This While Microsoft Fabric provides powerful analytics capabilities, filebased ingestion from SharePoint often requires custom, oneoff solutions: Pipelines that only run on schedules Manual schema fixes after ingestion Silent failures when files change unexpectedly Inconsistent naming and table structures FabricSharePointCopy addresses these challenges by introducing a metadatadriven ingestion layer that reacts to file changes and enforces validation before data reaches curated tables. How the Framework Works At a high level, FabricSharePointCopy continuously watches configured SharePoint folders and triggers ingestion whenever a file is created or updated. Endtoend flow: Detect change – A new file upload or modification is detected for a configured SharePoint folder. Register the file – File metadata (name, path, modified time, size) is captured to drive processing. Validate (DQ gate) – Metadatadriven data quality checks run before publish (schema, required columns, thresholds, sheet rules). Ingest & transform – CSV or Excel files are read and processed based on configured load type. Publish to Fabric – Curated tables are updated in the Fabric Lakehouse and made available for consumption. Notify on failure – If validation fails, the framework sends a notification with the failure reason. This ensures only validated, structured data reaches downstream analytics. Supported File Formats and Load Types FabricSharePointCopy supports common business file formats and ingestion patterns out of the box: File formats CSV Excel (including multisheet files, skip rows, and skip columns) Load types Full load Delta load Custom load logic All behavior is driven through metadata rather than hardcoded logic. BuiltIn Data Quality (DQ) A key design principle of FabricSharePointCopy is fail fast on bad data. Before any data is published: Schema checks ensure expected columns exist Required fields can be enforced as nonnull Rowcount thresholds can be applied Sheet selection rules are validated When validation fails, the framework stops processing and notifies the relevant owner, preventing corrupted or incomplete data from flowing downstream. Standardized Naming with Flexibility To keep curated data easy to discover, the framework applies a consistent naming convention for Silverlayer tables: Silver_{Folder}_{FileName} This default can be overridden using metadata when needed, allowing teams to balance clarity, consistency, and customization. Designed for Microsoft Fabric FabricSharePointCopy is built specifically for Microsoft Fabric Lakehouse architectures: Works with OneLake paths Produces managed tables ready for Direct Lake and downstream analytics Aligns with Fabric notebooks and pipelinebased orchestration Prerequisites and setup details are documented in the GitHub README, including Fabric workspace requirements, SharePoint access, and Lakehouse shortcuts. Open Source and Extensible We’ve released FabricSharePointCopy as an opensource project under the MIT license, making it easy for teams to: Adopt the framework asis Extend validation logic Add custom transformations Integrate with their own notification or monitoring systems Who Is This For? FabricSharePointCopy is useful for: Data teams ingesting operational files from SharePoint Analytics engineers standardizing filebased ingestion Fabric users looking for near realtime availability of curated data Teams aiming to reduce manual data fixes and rework Contributors ghanchiasif, kranthimeda, swapnil09
