--- name: fabric-dataflows-perf-remediation-0ca02e143e24 description: Diagnose and resolve Microsoft Fabric Dataflow Gen2 performance issues including slow refresh times, Fast Copy optimization, query folding failures, staging bottlenecks, gateway latency, incremental refresh tuning, capacity throttling, and data destination write performance. Use when troubleshooting dataflow refresh failures, optimizing Dataflow Gen2 execution time, debugging Power Query mashup engine performance, resolving staging Lakehouse or Warehouse compute issues, configuring Fast Copy connectors, fixing insufficient permissions errors, monitoring dataflow refresh history, or automating dataflow health checks via REST API and PowerShell. license: Complete terms in LICENSE.txt --- # Dataflows Gen2 Performance Troubleshooting Systematic diagnostic workflows for identifying and resolving performance issues in Microsoft Fabric Dataflow Gen2 workloads covering data movement, transformation execution, staging configuration, and destination write optimization. ## When to Use This Skill - Dataflow Gen2 refresh takes longer than expected - Fast Copy is not being utilized or is failing - Query folding indicators show red or yellow steps - Staging-to-destination data movement is a bottleneck - Gateway-connected dataflows have high latency - Incremental refresh is slower than full refresh - Capacity throttling affects dataflow execution - Refresh failures with permission or connector errors - Need to automate dataflow monitoring and health checks - Migrating from Dataflow Gen1 and seeing performance regressions ## Prerequisites - Microsoft Fabric workspace with Data Factory enabled - Contributor or higher role on the workspace - PowerShell 7+ with Az.Accounts module for automation scripts - Access to Fabric Monitoring Hub for refresh history analysis - Fabric Capacity Metrics app access for CU consumption review --- ## Quick Diagnosis: Symptom-to-Solution Map | Symptom | Likely Cause | Jump To | |---------|-------------|---------| | Refresh takes 2x+ longer than Gen1 | Delta Parquet output overhead or staging misconfiguration | [Consideration 5 in Performance Guide](./references/performance-optimization-guide.md#gen1-vs-gen2-runtime) | | Fast Copy not activating | Unsupported connector or non-foldable transforms | [Fast Copy Diagnostics](#fast-copy-diagnostics) | | "Insufficient permissions for staging artifacts" | Creator token expired (90+ days) | [Common Errors](#common-refresh-errors) | | Slow design-time previews | Large dataset loaded in editor | [Design-Time Optimization](#design-time-optimization) | | Staging-to-Lakehouse write is slow | Extra data hop through staging Warehouse | [Staging Strategy](#staging-strategy-decision-tree) | | Gateway dataflows are slow | All transforms running on gateway host | [Gateway Optimization](#gateway-performance) | | Incremental refresh slower than full | Too many small buckets creating overhead | [Incremental Refresh Tuning](#incremental-refresh-tuning) | | HTTP 430 / capacity throttled | Concurrent Spark jobs exhausting CUs | [Capacity Management](#capacity-and-throttling) | --- ## Diagnostic Workflow ### Step 1: Check Refresh History 1. Open the Fabric workspace and locate the dataflow 2. Select the ellipsis (...) > **Recent runs** 3. Review the refresh history for status, duration, and type 4. Select a specific refresh **Start time** to drill into details 5. Examine the **Tables** section for per-entity timing 6. Examine the **Activities** section for destination write timing 7. Download **detailed logs** (bottom-left button) for deep analysis 8. Download **CSV** of refresh runs for trend analysis Key metrics to capture: total duration, per-table duration, bytes read/written, rows read/written, engine type (Mashup vs CopyActivity vs SQL DW). ### Step 2: Identify the Bottleneck Component Dataflow Gen2 has three performance-critical components: ``` [Data Source] --> [Dataflow Engine] --> [Data Destination] | | | Connectors Mashup / Fast Copy / Lakehouse / + Gateway SQL DW Compute Warehouse / SQL Database ``` - **Data Source**: Connector speed, network latency, gateway throughput - **Dataflow Engine**: Query folding, staging, Fast Copy, Mashup engine - **Data Destination**: Write performance, Delta Parquet conversion ### Step 3: Apply Targeted Optimization Based on the bottleneck identified, follow the relevant section below. --- ## Fast Copy Diagnostics Fast Copy provides up to 9x faster ingestion for supported scenarios. ### Supported Connectors ADLS Gen2, Azure Blob Storage, Azure SQL DB, Lakehouse, PostgreSQL, On-premises SQL Server, Warehouse, Oracle, Snowflake, SQL Database in Fabric. ### Fast Copy Indicators | Indicator | Meaning | Action | |-----------|---------|--------| | Green | Step supported by Fast Copy | No action needed | | Yellow | Some steps may support Fast Copy | Split query at boundary | | Red | Step NOT supported by Fast Copy | Move to referenced query | ### When Fast Copy Is Disabled - Transformations beyond: select columns, change types, rename, remove columns (for file sources) - Non-supported connector in use - "Require Fast Copy" set on incompatible query (causes failure) - Destination is not Lakehouse (stage first, then reference) ### Query Splitting Pattern for Fast Copy 1. Remove any red-indicator steps and the destination from the original query 2. Verify remaining steps show green indicators 3. Right-click the query > **Enable staging** 4. Right-click again > **Reference** to create a new query 5. Add back transformations and destination to the referenced query 6. Publish and refresh — first query uses Fast Copy, second uses SQL DW compute See [Performance Optimization Guide](./references/performance-optimization-guide.md#fast-copy-deep-dive) for detailed walkthrough. --- ## Staging Strategy Decision Tree ``` Is destination a Warehouse? ├── YES → Staging REQUIRED (enabled by default) │ Write goes directly via SQL DW compute │ This is the optimal path for Warehouse destinations └── NO → Is destination a Lakehouse? ├── YES with staging enabled → Data moves: │ Source → Staging LH → Staging WH → Lakehouse │ ⚠ CONSIDER: Disable staging to avoid extra hop │ OR: Switch destination to Warehouse instead └── YES with staging disabled → Data writes directly Source → Lakehouse (via Mashup engine) ✓ Fewer hops, but no SQL DW compute for transforms ``` **Key Decision**: If your transforms fold to the source, disable staging for Lakehouse destinations. If transforms are complex (joins, aggregations), either enable staging or switch to Warehouse destination. --- ## Design-Time Optimization When working with large datasets in the dataflow editor: 1. **Use parameters for date filtering**: Create a `DesignDateFilter` parameter to limit preview data during authoring, then adjust to full range before publish 2. **Switch to Schema view**: Select **Schema view** in the editor toolbar to see structure without loading data 3. **Limit preview rows**: Keep preview data small during development --- ## Gateway Performance When using on-premises data gateway: 1. **Split dataflows**: Separate data movement (gateway → cloud) from transformations 2. **First dataflow**: Use Fast Copy for high-throughput transfer from on-premises to Lakehouse/Warehouse staging 3. **Second dataflow**: Apply transformations using cloud compute on the staged data 4. **Gateway version**: Must be 3000.214.2+ for Fast Copy support; keep within last 6 supported versions 5. **Detailed logs**: Not yet supported for on-premises gateway refreshes (supported for cloud/VNet gateways) --- ## Incremental Refresh Tuning If incremental refresh is slower than full refresh: 1. **Increase bucket size**: Reduce total bucket count to lower partition management overhead 2. **Evaluate data volume**: For small datasets, full refresh may outperform incremental 3. **Concurrency control**: Adjust max concurrent requests in dataflow settings if source can't handle defaults 4. **Monitor bucket efficiency**: Check if most buckets process zero rows (indicates over-partitioning) --- ## Capacity and Throttling ### Dataflow Gen2 Compute Meters | Engine | When Used | Billing Basis | |--------|-----------|---------------| | Standard Compute (Mashup) | Staging disabled or non-foldable queries | Query evaluation time | | High Scale Compute (SQL DW) | Staging enabled | Lakehouse + Warehouse duration | | Fast Copy | Supported connectors with Fast Copy enabled | Copy job duration | All operations are **background operations** smoothed over 24 hours. ### CU Consumption Formula (Standard Compute) ``` If QueryDuration < 600s: CU_seconds = QueryDuration × 12 Else: CU_seconds = (QueryDuration - 600) × 1.5 + 600 × 12 ``` ### Reducing Capacity Impact 1. Avoid inefficient Power Query logic (expensive merges and sorts) 2. Maximize query folding to push work to source systems 3. Disable staging for small data volumes or simple transforms 4. Don't refresh more frequently than source data updates 5. Use data destinations instead of dataflow connectors for consumption --- ## Common Refresh Errors | Error | Cause | Resolution | |-------|-------|------------| | "Insufficient permissions for staging artifacts" | Creator inactive 90+ days or left org | Have creator log in to Fabric; if gone, open support ticket | | "Expression.Error: import matches no exports" | Unsupported connector in Premium workspace | Check connector compatibility list | | "Gateway version not supported" | On-premises gateway out of support | Update to latest gateway version | | "Staging lakehouse couldn't be found" | CI/CD branch workspace missing staging | Create a new Dataflow Gen2 in workspace to trigger staging creation | | Validation failure on save | Query schema can't be determined in 10 min | Simplify query or check source connectivity | --- ## Modern Evaluator (Preview) The Modern Evaluator engine can provide significant performance improvements: - **Large data volumes**: Shorter processing time, reduced memory usage - **Complex transformations**: Improved execution plans for joins across large tables - **Frequent schedules**: Cumulative time savings across multiple daily runs Enable via dataflow settings. Monitor results after enabling — some connectors may not yet be fully optimized. --- ## Automation and Monitoring Run the [Dataflow Health Check Script](./scripts/Get-DataflowHealthReport.ps1) to programmatically audit dataflow configurations and recent refresh performance across a workspace. Run the [Dataflow Refresh Monitor Script](./scripts/Watch-DataflowRefresh.ps1) to poll and track active refresh status in real-time. See [REST API Reference](./references/dataflow-rest-api-reference.md) for complete API documentation covering CRUD operations, scheduling, refresh triggering, and monitoring. See [Performance Optimization Guide](./references/performance-optimization-guide.md) for deep-dive scenarios including Fast Copy benchmarks, staging architecture patterns, and query folding analysis techniques. --- ## References - [Performance Optimization Guide](./references/performance-optimization-guide.md) — Deep-dive optimization scenarios - [Dataflow REST API Reference](./references/dataflow-rest-api-reference.md) — API operations for automation - [Get-DataflowHealthReport.ps1](./scripts/Get-DataflowHealthReport.ps1) — Workspace health audit - [Watch-DataflowRefresh.ps1](./scripts/Watch-DataflowRefresh.ps1) — Real-time refresh monitor - [Microsoft Learn: Best Practices for Dataflow Gen2 Performance](https://learn.microsoft.com/en-us/fabric/data-factory/dataflow-gen2-performance-best-practices) - [Microsoft Learn: Fast Copy in Dataflow Gen2](https://learn.microsoft.com/en-us/fabric/data-factory/dataflows-gen2-fast-copy) - [Microsoft Learn: View Refresh History and Monitor Dataflows](https://learn.microsoft.com/en-us/fabric/data-factory/dataflows-gen2-monitor) - [Microsoft Learn: Dataflow Gen2 Pricing](https://learn.microsoft.com/en-us/fabric/data-factory/pricing-dataflows-gen2) --- Source attribution: PatrickGallucci Original project: https://github.com/PatrickGallucci/fabric-skills License: Apache-2.0 License information: https://www.apache.org/licenses/LICENSE-2.0 Original upstream instructions; downloaded from Mega AI Hub. ## Upstream license notice Apache License Version 2.0, January 2004 http://www.apache.org/licenses/ TERMS AND CONDITIONS FOR USE, REPRODUCTION, AND DISTRIBUTION 1. Definitions. "License" shall mean the terms and conditions for use, reproduction, and distribution as defined by Sections 1 through 9 of this document. "Licensor" shall mean the copyright owner or entity authorized by the copyright owner that is granting the License. "Legal Entity" shall mean the union of the acting entity and all other entities that control, are controlled by, or are under common control with that entity. For the purposes of this definition, "control" means (i) the power, direct or indirect, to cause the direction or management of such entity, whether by contract or otherwise, or (ii) ownership of fifty percent (50%) or more of the outstanding shares, or (iii) beneficial ownership of such entity. "You" (or "Your") shall mean an individual or Legal Entity exercising permissions granted by this License. "Source" form shall mean the preferred form for making modifications, including but not limited to software source code, documentation source, and configuration files. "Object" form shall mean any form resulting from mechanical transformation or translation of a Source form, including but not limited to compiled object code, generated documentation, and conversions to other media types. "Work" shall mean the work of authorship, whether in Source or Object form, made available under the License, as indicated by a copyright notice that is included in or attached to the work (an example is provided in the Appendix below). "Derivative Works" shall mean any work, whether in Source or Object form, that is based on (or derived from) the Work and for which the editorial revisions, annotations, elaborations, or other modifications represent, as a whole, an original work of authorship. For the purposes of this License, Derivative Works shall not include works that remain separable from, or merely link (or bind by name) to the interfaces of, the Work and Derivative Works thereof. "Contribution" shall mean any work of authorship, including the original version of the Work and any modifications or additions to that Work or Derivative Works thereof, that is intentionally submitted to Licensor for inclusion in the Work by the copyright owner or by an individual or Legal Entity authorized to submit on behalf of the copyright owner. For the purposes of this definition, "submitted" means any form of electronic, verbal, or written communication sent to the Licensor or its representatives, including but not limited to communication on electronic mailing lists, source code control systems, and issue tracking systems that are managed by, or on behalf of, the Licensor for the purpose of discussing and improving the Work, but excluding communication that is conspicuously marked or otherwise designated in writing by the copyright owner as "Not a Contribution." "Contributor" shall mean Licensor and any individual or Legal Entity on behalf of whom a Contribution has been received by Licensor and subsequently incorporated within the Work. 2. Grant of Copyright License. Subject to the terms and conditions of this License, each Contributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable copyright license to reproduce, prepare Derivative Works of, publicly display, publicly perform, sublicense, and distribute the Work and such Derivative Works in Source or Object form. 3. Grant of Patent License. Subject to the terms and conditions of this License, each Contributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable (except as stated in this section) patent license to make, have made, use, offer to sell, sell, import, and otherwise transfer the Work, where such license applies only to those patent claims licensable by such Contributor that are necessarily infringed by their Contribution(s) alone or by combination of their Contribution(s) with the Work to which such Contribution(s) was submitted. If You institute patent litigation against any entity (including a cross-claim or counterclaim in a lawsuit) alleging that the Work or a Contribution incorporated within the Work constitutes direct or contributory patent infringement, then any patent licenses granted to You under this License for that Work shall terminate as of the date such litigation is filed. 4. Redistribution. You may reproduce and distribute copies of the Work or Derivative Works thereof in any medium, with or without modifications, and in Source or Object form, provided that You meet the following conditions: (a) You must give any other recipients of the Work or Derivative Works a copy of this License; and (b) You must cause any modified files to carry prominent notices stating that You changed the files; and (c) You must retain, in the Source form of any Derivative Works that You distribute, all copyright, patent, trademark, and attribution notices from the Source form of the Work, excluding those notices that do not pertain to any part of the Derivative Works; and (d) If the Work includes a "NOTICE" text file as part of its distribution, then any Derivative Works that You distribute must include a readable copy of the attribution notices contained within such NOTICE file, excluding those notices that do not pertain to any part of the Derivative Works, in at least one of the following places: within a NOTICE text file distributed as part of the Derivative Works; within the Source form or documentation, if provided along with the Derivative Works; or, within a display generated by the Derivative Works, if and wherever such third-party notices normally appear. The contents of the NOTICE file are for informational purposes only and do not modify the License. You may add Your own attribution notices within Derivative Works that You distribute, alongside or as an addendum to the NOTICE text from the Work, provided that such additional attribution notices cannot be construed as modifying the License. You may add Your own copyright statement to Your modifications and may provide additional or different license terms and conditions for use, reproduction, or distribution of Your modifications, or for any such Derivative Works as a whole, provided Your use, reproduction, and distribution of the Work otherwise complies with the conditions stated in this License. 5. Submission of Contributions. Unless You explicitly state otherwise, any Contribution intentionally submitted for inclusion in the Work by You to the Licensor shall be under the terms and conditions of this License, without any additional terms or conditions. Notwithstanding the above, nothing herein shall supersede or modify the terms of any separate license agreement you may have executed with Licensor regarding such Contributions. 6. Trademarks. This License does not grant permission to use the trade names, trademarks, service marks, or product names of the Licensor, except as required for reasonable and customary use in describing the origin of the Work and reproducing the content of the NOTICE file. 7. Disclaimer of Warranty. Unless required by applicable law or agreed to in writing, Licensor provides the Work (and each Contributor provides its Contributions) on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied, including, without limitation, any warranties or conditions of TITLE, NON-INFRINGEMENT, MERCHANTABILITY, or FITNESS FOR A PARTICULAR PURPOSE. You are solely responsible for determining the appropriateness of using or redistributing the Work and assume any risks associated with Your exercise of permissions under this License. 8. Limitation of Liability. In no event and under no legal theory, whether in tort (including negligence), contract, or otherwise, unless required by applicable law (such as deliberate and grossly negligent acts) or agreed to in writing, shall any Contributor be liable to You for damages, including any direct, indirect, special, incidental, or consequential damages of any character arising as a result of this License or out of the use or inability to use the Work (including but not limited to damages for loss of goodwill, work stoppage, computer failure or malfunction, or any and all other commercial damages or losses), even if such Contributor has been advised of the possibility of such damages. 9. Accepting Warranty or Additional Liability. While redistributing the Work or Derivative Works thereof, You may choose to offer, and charge a fee for, acceptance of support, warranty, indemnity, or other liability obligations and/or rights consistent with this License. However, in accepting such obligations, You may act only on Your own behalf and on Your sole responsibility, not on behalf of any other Contributor, and only if You agree to indemnify, defend, and hold each Contributor harmless for any liability incurred by, or claims asserted against, such Contributor by reason of your accepting any such warranty or additional liability. END OF TERMS AND CONDITIONS APPENDIX: How to apply the Apache License to your work. To apply the Apache License to your work, attach the following boilerplate notice, with the fields enclosed by brackets "[]" replaced with your own identifying information. (Don't include the brackets!) The text should be enclosed in the appropriate comment syntax for the file format. We also recommend that a file or class name and description of purpose be included on the same "printed page" as the copyright notice for easier identification within third-party archives. Copyright [yyyy] [name of copyright owner] Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License. # Mega AI Hub resource metadata Creator / publisher: PatrickGallucci Original project: https://github.com/PatrickGallucci/fabric-skills Attribution basis: Upstream repository explicitly recorded in package mirror path Fields of work: Data & Analytics, Development & Engineering, Operations, Product Management Roles: Business Process Analyst, Data Analyst, Data Scientist, ML / AI Engineer, Product Manager Specialties: API, Backend Tools: Powershell, Microsoft Fabric Country applicability: Country not specified Classification basis: Source catalog labels Instruction SHA-256: 343cfb44221d0ecf733a9f96cb0462132dd82b757cf6a2f102f0096a67f7de14