Best approach(es) to refresh Dev Warehouse from Prod Warehouse (Read-Write Copy) in Microsoft Fabric
We are currently evaluating the best practices for refreshing a Development Data Warehouse using data from a Production Data Warehouse within the same Fabric tenant. We have evaluated a few approaches based on the use case: Read-Only Access (Zero-Copy): If Dev only needs read access to Prod data, using OneLake Shortcuts is the clear choice. It provides instantaneous access with zero data duplication, no storage overhead, and minimal setup effort. Read-Write Access (Full Schema & Data Copy): If Dev requires a Read-Write copy (allowing developers to run DML operations like UPDATE, INSERT, or DELETE without impacting Production), our current options seem to be: Fabric Data Pipelines (Copy Activity): Scheduled or trigger-based pipelines to pull data from Prod to Dev. Cross-Database Querying / CTAS: Running T-SQL (CREATE TABLE AS SELECT ...) across workspaces. The Challenge: Both Read-Write options (Pipelines and CTAS) can become cumbersome to set up and maintain, especially as datasets grow. Additionally, duplicating large volumes of data incurs extra storage and CU (Capacity Unit) processing costs. Was wondering if there are better, more efficient options available in Microsoft Fabric to maintain or periodically refresh a Read-Write copy of a Warehouse in a Dev workspace? Is Table Cloning / Zero-Copy Clone supported or planned for cross-workspace scenarios? How are others managing periodic Dev environment refreshes from Prod without creating heavy data copy pipelines?
how to set failure notification on the pipeline level
I have created a pipeline running multiple steps, if one step failed, i would like to move to next step till it finished the whole pipeline. I don't want to set a failure notification per step because that could end up sending out too many failure emails. I tried to create an activator for the pipeline job, the activator captured the failure and was able to send out a testing email, but it failed to send out the alert email in real environment. I wonder if there is another way to set up alert for the whole pipeline? Thnx.Auto-Magic Data Ingestion in Lakehouse using Fabric Data Activator & Pipelines
Microsoft Fabric Hands-On Connect — An Interactive Knowledge Sharing Event The NextGen Data Aspirants Community (NDAC) is back with another power-packed, hands-on session — this time diving deep into real-time data automation using Microsoft Fabric. Whether you're a beginner exploring the platform or an enthusiast ready to automate your data flows, this session will show you how to harness the full power of Lakehouse, Pipelines, and Data Activator — all in one seamless flow! Date: 26th July 2025 Time: 06:00 PM IST Duration: 1 Hour 15 Minutes Session Abstract: Imagine this — you drop a CSV file into your Lakehouse and, like magic, it transforms into a Delta Table automatically. No manual intervention. No waiting. Just pure automation. In this interactive session, we’ll walk through a real-world end-to-end solution built inside Microsoft Fabric, where: -Fabric Workspaces are configured -Pipelines handle dynamic metadata-driven data flow -Data Activator monitors events in real-time -CSV files get ingested and converted to Delta Tables effortlessly Session Takeaways: -Fabric Workspace & Lakehouse setup essentials -Pipeline design using Get Metadata, ForEach, and Copy Data activities -Setting up real-time triggers with Data Activator -Automating Delta Table creation on file drop -Best practices and tips for production-ready ingestion Whether you're building your first pipeline or scaling your data workflows — this session will empower you to automate like a pro using Microsoft Fabric. Join us at WhatsApp: https://chat.whatsapp.com/GHBzZ7rKqylLyXTGJqyTxz Follow us at LinkedIn: NextGen Data Aspirants Community: Overview | LinkedIn Join us at teams: https://teams.live.com/l/community/FEAVIPJvr_9CQiYFgI Join us at Meetup: NextGen Data Aspirants Community | Meetup Let’s Explore, Learn, and Innovate — Together! Thank you!
Fabric Workspace Architecture
I'm a Data Analytics Engineer at a logistics startup, and I'm the only person handling the data space. I'm looking for insights on how to effectively manage and monitor ETL processes using metadata-driven pipelines for a medallion architecture. I have 10 applications with about 2k tables total, though NetSuite accounts for 40% of the data. I'm considering 3 approaches for the Fabric workspace architecture: Approach 1: Create a dedicated NetSuite workspace with bronze, silver, and gold pipelines, plus 3 lakehouses for the medallion layers and schemas for individual departments. Then create similar workspaces for other applications. Approach 2: Organize by layer instead—separate workspaces for raw-to-bronze, bronze-to-silver, and silver-to-gold, with each workspace handling all applications at that layer. Approach 3: Use a single workspace to manage all medallion layers for all applications, but I'm not sure how to easily manage and monitor all the pipelines in this setup. Can you give me the pros and cons for each approach and recommend which one is best for a solo data engineer?
Automatic Background Token Refresh for Pipeline Connections
Problem Description Microsoft Fabric pipeline executions intermittently fail with authentication errors on connections that use Microsoft Entra ID (Azure AD) OAuth refresh tokens. Pipelines that previously executed successfully begin failing with authentication errors, and simply rerunning the pipeline does not resolve the issue. The connection must instead be manually re-authenticated before pipeline execution can resume. The following Azure AD Security Token Service (AADSTS) error codes have been observed: Error CodeDescription AADSTS700082 – Refresh token expired due to inactivity. AADSTS50173 – Refresh token revoked after password change/reset. AADSTS50057 – User account disabled. AADSTS500341 – User account deleted. AADSTS135010 – User principal missing key ID. Although these error codes represent different underlying causes, they all result in the same operational issue: the pipeline cannot authenticate until the connection credentials are manually refreshed. Root Cause Fabric currently does not automatically renew OAuth refresh tokens for pipeline connections in the background. When a connection is created, Fabric obtains an OAuth refresh token associated with the authenticated user account. This token is later used to acquire new access tokens as required. However, if: the connection remains unused for an extended period, the user's password is changed or reset, the user account is disabled or deleted, or the identity configuration changes, the refresh token becomes invalid. Unlike platforms that support service principals, managed identities, or silent token renewal, Fabric does not automatically re-establish the OAuth authorization when the refresh token becomes invalid. Instead, the connection enters a failed authentication state and requires manual user intervention to reauthenticate. As a result, unattended or scheduled pipelines can fail unexpectedly even though no pipeline logic has changed. Impact Pipeline Reliability Scheduled and unattended pipelines may fail unexpectedly after weeks or months of successful execution without any advance indication that the authentication token is nearing expiration. Operational Overhead Operations teams must monitor for AADSTS authentication failures and manually reauthenticate affected connections before pipelines can resume. This introduces ongoing maintenance effort that is inconsistent with unattended data integration workloads. Data Freshness and SLA Risk Failed pipeline executions delay downstream datasets, reports, and business processes until the authentication issue is resolved, increasing the risk of missed reporting deadlines and SLA violations. Longer Troubleshooting Multiple AADSTS error codes can produce similar pipeline failures while requiring different remediation actions. This increases investigation time and makes root-cause identification less straightforward. Current Resolution / Workarounds Option 1 – Re-authenticate the Connection Open the affected pipeline connection. Edit the connection credentials. Reauthenticate with Microsoft Entra ID. Save the connection. Rerun the pipeline. Option 2 – Trigger a Connection Refresh A temporary workaround is to make a minor, non-functional modification to the pipeline (for example, updating its description) and save the pipeline. This action has been observed to refresh the authentication token without requiring the user to manually re-enter credentials. Microsoft also provides a PowerShell script that automates this "dummy update" process across multiple pipelines, making it suitable for larger environments: https://github.com/microsoft/fabric-samples/tree/main/docs-samples/data-factory Additional Mitigations Where possible, we recommend the following operational practices: Use service principal or managed identity authentication instead of user credentials whenever the connector supports it. Schedule a lightweight keep-alive pipeline to execute at an interval shorter than the refresh token inactivity period to reduce the likelihood of AADSTS700082. Monitor pipeline execution failures for the relevant AADSTS error codes and automatically notify support teams so that reauthentication can be completed before business users are impacted. Maintain documentation identifying which pipelines depend on user-based authentication to simplify incident response. Product Enhancement Suggestions To improve reliability for unattended workloads, we recommend the following enhancements to Microsoft Fabric. 1. Automatic Background Token Refresh Support silent background renewal of OAuth refresh tokens for pipeline connections so that authentication remains valid without requiring manual credential re-entry. 2. Proactive Expiration Notifications Provide advance notifications (for example, email alerts or Fabric activity warnings) before a refresh token expires due to inactivity, allowing administrators to take corrective action before scheduled executions fail. 3. Expanded Support for Service Principals and Managed Identities Enable all first-party Fabric connectors to support service principal or managed identity authentication. Eliminating dependence on user accounts would significantly reduce authentication failures caused by password changes, disabled accounts, or deleted users. 4. Improved Error Messaging Present a unified, user-friendly authentication error that clearly distinguishes between: Token expired due to inactivity Password reset or credential revocation Disabled account Deleted account Identity configuration issues Providing targeted remediation guidance alongside each error would substantially reduce troubleshooting time. Conclusion Current Microsoft Fabric pipeline connections that rely on Microsoft Entra ID user authentication require manual intervention whenever OAuth refresh tokens become invalid. This behavior reduces the reliability of unattended pipelines, increases operational overhead, and introduces avoidable risks to data availability and service-level agreements. Implementing automatic background token refresh, proactive token expiry notifications, broader support for service principals and managed identities, and clearer authentication diagnostics would significantly improve the robustness and maintainability of Fabric pipeline authentication.
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.
