User Profile
twcyril
New Member
Joined 3 months ago
User Widgets
Contributions
Warehouse Snapshots in Microsoft Fabric (Preview)
Maintaining data consistency during ETL (Extract, Transform, Load) processes has long been a critical challenge for data engineers. Whether it’s a nightly pipeline overwriting key records or a mid-day transformation introducing schema drift, the risk of disrupting downstream analytics is both real and costly. In today’s fast-paced, data-driven world, even brief inconsistencies can break dashboards, distort reports, and lead to poor business decisions. We are excited to announce the preview of Warehouse Snapshots, Microsoft Fabric’s new capability to provide a stable, read-only view of your data warehouse at a specific point in time. With Warehouse Snapshots, you can confidently support analytics, reporting, and historical analysis without worrying about the volatility of live data updates. As read-only child items of the parent warehouse, warehouse snapshots offer a consistent and stable view of your data, ensuring that analytical workloads remain unaffected by ongoing changes or ETL operations. What Are Warehouse Snapshots? A warehouse snapshot is a read-only representation of your warehouse at a specific point in time. Warehouse snapshots can reference data from the past 30 days. Snapshots can be seamlessly rolled forward on demand to reflect the latest state of the warehouse. This allows consumers to access the same snapshot using a consistent connection string, even from third-party tools. When the snapshot timestamp is rolled forward, updates are applied immediately, as if in a single, atomic transaction. This ensures data engineers can provide analysts with a consistent dataset, even as real-time updates occur behind the scenes. Analysts can confidently run SELECT queries against the snapshot, knowing the data remains unchanged and free from ETL interference. Benefits of Warehouse Snapshots: Guarantees Data Consistency: Users can access a stable dataset that remains unaffected by ongoing ETL processes. Immediate Roll-Forward Updates: Snapshots can be instantly updated with the latest data through atomic operation. Facilitates Historical Analysis: Users have the flexibility to create snapshots on an hourly, daily, or weekly basis to suit their business requirements. Enhanced Reporting: Offers a reliable dataset for precise reporting, free from disruptions caused by data modifications. How Warehouse Snapshots compare to SQL Analytics Endpoints Feature SQL Analytics Endpoint (Lakehouse) Warehouse Snapshot Primary capabilities System generated SQL analytics endpoint for Lakehouse for T-SQL querying and serving, Querying Delta tables in Lakehouse, and the Delta Lake folders referenced via shortcuts. User-created child item of parent warehouse, providing a consistent view of a Warehouse Data Modification Read-only Read-only Data loading Spark, Pipelines, Dataflows, Shortcuts Load data into the parent warehouse Storage Format Delta No separate storage, relies on source Warehouse, no parquet files T-SQL Support Full DQL (Data Querying Language), no DML (Data Manipulation Language), limited DDL (Data Definition Language) such as support for views, table valued functions Full DQL, no DML, no DDL (except to updated snapshot timestamp by admin, member or contributor), no creation of views, stored procedures or Table valued functions Use Cases Exploring and querying delta tables from the Lakehouse, staging data, medallion lakehouse architecture with zones for bronze, silver and gold analysis Access stable version of a warehouse, ETL consistency, historical analysis, reporting accuracy, meet specific business needs by creating hourly, daily or weekly warehouse snapshots Permission Security permissions must be set in the source database. A user with workspace roles of administrator, member, or contributor can create and manage a warehouse snapshot. A user with administrator, member, or contributor role can modify the snapshot timestamp via the TSQL and Fabric portal. A user with administrator, member, contributor, viewer role on the workspace or a shared recipient on parent warehouse access can query the child snapshot. Getting started with Warehouse Snapshots Create and manage Warehouse Snapshots Create and manage warehouse snapshots using the Fabric portal, T-SQL, or the Fabric API: Create a Warehouse Snapshot Warehouse Snapshots can be created using the REST API or Fabric portal For more information on the public APIs, see Microsoft Fabric REST API references If snapshot datetime is not provided, it will take the current time. Snapshot must have a unique name compared to warehouse and SQL Analytics Endpoint. Sample JSON request for creating a snapshot via the REST API: POST https://api.fabric.microsoft.com/v1/workspaces/{workspaceId}/items Authorization: Bearer <bearer token> Content-Type: application/json { "type": "WarehouseSnapshot", "displayName": "DailySnapshot", "description": "Snapshot for daily reporting", "creationPayload": { "parentWarehouseId": "00000000-0000-0000-0000-000000000000", "snapshotDateTime": "YYYY-MM-DDTHH:SS:SSZ" //Enter UTC time for when snapshot needs to be created } } Use the following steps to generate your <bearer token> Open your Microsoft Fabric workspace in a browser (Microsoft Edge). Press F12 to open Developer Tools. Select the Console tab. If necessary, select Expand Quick View to reveal the console prompt > . Type the command powerBIAccessToken and press Enter. Right-click on the large unique string returned in the console and select Copy string contents. Paste it in place of <bearer token> . Create Warehouse Snapshot via Fabric portal Warehouse snapshots can also be created via the Fabric portal. In the ribbon, under Management, select New warehouse snapshot. Warehouse_Snapshots_in_Microsoft_Fabric_Preview Choose to create a warehouse snapshot from the current warehouse state or a previous point in time (within the last 30 days). Warehouse_Snapshots_in_Microsoft_Fabric_Preview Warehouse_Snapshots_in_Microsoft_Fabric_Preview Name the snapshot and select Create. Multiple snapshots can be created for the same parent warehouse. Once warehouse snapshots are created, they will appear as child items of the parent warehouse in the workspace view. Warehouse_Snapshots_in_Microsoft_Fabric_Preview Read To return the properties of the specified snapshot: GET https://api.fabric.microsoft.com/v1/workspaces/{workspaceId}/items/{warehousesnapshotId} Authorization: Bearer <bearer token> When a T-SQL query is run, information about the current version of the data being accessed is included. For example, you can see the timestamp in the Messages of the Fabric portal query editor: Warehouse_Snapshots_in_Microsoft_Fabric_Preview Update Snapshot timestamp Updating the Warehouse Snapshot timestamp allows users to roll forward data instantly, ensuring consistency. This can be done via T-SQL commands and Fabric portal. To update the snapshot to the current state of the warehouse, use CURRENT_TIMESTAMP . ALTER DATABASE [<snapshot name>] SET TIMESTAMP = CURRENT_TIMESTAMP; The above SQL statement will use the system time of the warehouse as the new point in time in which the source warehouse data will be reflected in the snapshot. The timestamp can also be set to any point within the retention period (within the last 30 days). ALTER DATABASE snapshot SET TIMESTAMP = 'YYYY-MM-DDTHH:MM:SS.SS'//Enter UTC time Setting an explicit timestamp can be useful in scenarios that an ETL process may have created data corruption. This ensures analytical consumers always work with the latest stable data version. 'In progress' queries will always complete against the version of data that they were started against. If a snapshot timestamp is rolled forward while a consumer is executing a query, that consumer session would not observe the new data until they execute the next query. Update a snapshot timestamp via Fabric portal In the Fabric portal, select Capture new state from the context menu, then select a timestamp for the snapshot. You can select Current or any point within the retention period (within the last 30 days). Warehouse_Snapshots_in_Microsoft_Fabric_Preview Warehouse_Snapshots_in_Microsoft_Fabric_Preview Rename a Warehouse Snapshot You can rename a warehouse snapshot item via REST API and in the Fabric portal. Sample JSON request to rename a warehouse snapshot via the REST API: PATCH https://api.fabric.microsoft.com/v1/workspaces/{worspaceId}/items/{warehousesnapshotId} Authorization: Bearer <bearer token> Content-Type: application/json { "type": "WarehouseSnapshot", "displayName": "Warehouse Snapshot’s new name", "description": "Snapshot for daily reporting", "creationPayload": { "parentWarehouseId": "00000000-0000-0000-0000-000000000000", "snapshotDateTime": "YYYY-MM-DDTHH:SS:SSZ" //Enter UTC time } } To rename warehouse snapshots via the Fabric portal, open your warehouse snapshot. Select the settings button, provide a new Name. Warehouse_Snapshots_in_Microsoft_Fabric_Preview Delete a Warehouse Snapshot Warehouse snapshots can be deleted via the Fabric portal. In the workspace item list, select the context menu for the warehouse snapshot item, and select Delete. When the parent warehouse gets deleted, the snapshot is also deleted. Sample REST API request for deleting a snapshot: DELETE https://api.fabric.microsoft.com/v1/workspaces/{workspaceId}/items/{warehousesnapshotId} Authorization: Bearer <bearer token> Accessing a Snapshot Once created, snapshots appear as child items in the workspace. Users with appropriate permissions (Admin, Member, Contributor, Viewer) can query them just like a standard warehouse. To see a warehouse's snapshots and their current timestamps, use the following T-SQL query on sys.databases and the extended property of TIMESTAMP to render attributes: SELECT snapshot_name = v.name , source_warehouse_name = s.name , snapshot_timestamp = DATABASEPROPERTYEX(v.name,'TIMESTAMP') FROM sys.databases AS v INNER JOIN sys.databases AS s ON v.source_database_id=s.database_id; Security & Governance Snapshots inherit permissions from the source warehouse. Warehouse snapshots are read-only for all consumers regardless of their permission level in the source warehouse. The only exception is that the administrator/member/contributor can update the timestamp of the snapshot via TSQL or the Fabric portal. Any permission changes in the source warehouse applies instantly to the snapshot. User access such as GRANT, DENY, REVOKE, and UNMASK all reflect the state of the source warehouse regardless of the snapshot timestamp. As an example, if a consumer’s permissions should be denied from accessing data at 12:00pm, that should apply regardless of if they are accessing the source warehouse or the snapshot. If the snapshot timestamp is set to 11:00am, the denied privileges will be reflected in the snapshot at 12:00pm even if the snapshot timestamp is not rolled forward until 1:00pm. Users are restricted from querying the snapshot if they lose access later. Design pointers Roll forward of the snapshot timestamp (ALTER DATABASE) cannot be part of an explicit transaction (standard SQL server behavior) and should be instantaneous. Queries that are in progress during point in time update will complete against the version of data they were started against. Security updates on source database will be rendered immediately to the snapshot databases. DDL changes to source will only impact queries in the snapshot against tables affected. Multiple snapshots can be joined, and the respective snapshot date will be applied to each warehouse connection. It's important to note that the stable reporting promise in Fabric Data Warehouse applies to the data, not the schema. For example, if a report references a table, view or column from a Warehouse Snapshot, and that object is later dropped, renamed or altered from the parent Warehouse, the snapshot will reflect that change. As a result, the report could break. This behavior is expected at this point, as the snapshot mechanism is designed to preserve data consistency, not schema stability. Limitations Warehouse Snapshots can only be created against new warehouses created after March 2025. Warehouse Snapshots do not appear in SSMS Object Explorer but will show up in the database selection dropdown. Snapshot datetime can be set to any date in the past up to 30 days or database creation time (whichever is later). Modified tables, views, and stored procedures after the snapshot timestamp become invalid in the snapshot. Warehouse Snapshots must be recreated if the data warehouse is restored. Warehouse snapshots require Direct Query or Import mode in Power BI, and don't support Direct Lake. Warehouse snapshots aren't supported on the SQL analytics endpoint of the Lakehouse. Warehouse snapshots aren't supported as a source for OneLake shortcuts. Conclusion Warehouse Snapshots in Microsoft Fabric provide a tailored solution for ensuring data consistency, reliable reporting, and seamless analytics without the disruptions caused by ongoing ETL processes. By offering a stable, read-only view of your data at any point in time, snapshots empower data engineers and analysts to work confidently with accurate, unaltered datasets. Whether you're rolling forward updates, analyzing historical trends, or ensuring reporting accuracy, Warehouse Snapshots deliver the reliability you need. Start leveraging them today! Thanks to Steve Howard, Sowmya Sivaraman, and Priyanka Langade for their contributions.Warehouse Snapshots in Microsoft Fabric (Generally Available)
Managing data consistency during ETL has always been a challenge for our customers. Dashboards break, KPIs fluctuate, and compliance audits become painful when reporting hits 'half-loaded' data. With Warehouse Snapshots, Microsoft Fabric solves this by giving you a stable, read-only view of your warehouse at a specific point in time and now, this capability is Generally Available! Think of this as a true time travel database, an industry-first capability that sets us apart. Why Warehouse Snapshots Matter Guarantee reporting consistency during ETL or data changes. Enable audit and compliance workflows. Support reproducible analytics and ML training. Roll forward snapshots atomically without breaking BI connections. Key Scenarios Enabled using Warehouse Snapshots Stable Reporting During ETL: Keep dashboards consistent while pipelines run, free from disruptions caused by data modifications. Historical Analysis: Users can schedule snapshots hourly, daily, or weekly to align with their business needs. This is especially valuable when the source dataset does not maintain historical changes. You can compare and analyze differences between two specific points in time. For instance, you can compare the product inventory quantity from 20 days ago with that from 5 days ago. Financial Close: Lock KPIs for month/quarter-end without blocking operations. Data Audit: Auditing data changes is crucial for data compliance and understanding how data has evolved over time. Warehouse Snapshots empowers you to track changes, access different versions of updates, and perform data analysis at any desired point in time Day 0 (Close Date): Finance triggers a snapshot named MonthEnd_Sept2025 . Day 1–30: Operations continue adding new sales and expenses. Audit: Finance queries SELECT * FROM MonthEnd_Sept2025.Sales to validate KPIs without worrying about post-close changes. The data retention is for 30 days from the current date. Repairing Accidental Data Changes & Incident Recovery: Warehouse Snapshots feature is invaluable for rectifying individual records to their last known good state, making it efficient to perform repairs without resorting to backups and restores. Once you access the desired data as it existed in the last known good state, you can either update the source warehouse with that data or ingest the records into a new object. Data Science & ML: Train models on reproducible datasets tied to snapshots. Snapshots capture the warehouse state at a specific point in time. Data scientists can train models on this frozen dataset, ensuring reproducibility during that window. While the live warehouse continues to ingest new data, snapshots allow ML teams to work on stable datasets without blocking operational pipelines or risking data changes. Experiments can be reproduced within the 30-day retention period. For longer-term reproducibility, teams can export snapshot data to external storage. See Warehouse Snapshots in Action What’s New in GA vs Preview Area Preview General Availability Portal Improvements – Update Snapshot ONLY possible using TSQL and from context menu for the warehouse snapshot item in the workspace Capability available through portal - 'Manage Warehouse Snapshot' tab SSMS Object Explorer Warehouse Snapshots do not appear in SSMS Object Explorer although it is visible in the database selection dropdown SSMS 22 Preview 3 provides direct visibility and query access to snapshots CREATE Warehouse Snapshots can only be created against new warehouses created after March 2025 Warehouse Snapshots can be created against any existing warehouses Call to Action Start using Warehouse Snapshots today to deliver consistent, compliant, and disruption-free analytics. Your insights matter to us. Please visit the feedback form to share your experience with Warehouse Snapshots. Learn More Warehouse Snapshots Create and Manage a Warehouse Snapshot Warehouse Snapshots in Microsoft Fabric Preview blog postALTER TABLE inside explicit transactions in Fabric Data Warehouse (Generally Available)
Schema evolution is a fact of life for modern analytics platforms. As data models grow, teams need to add columns, drop unused fields, and evolve constraints—often as part of tightly controlled deployment pipelines. Fabric DW supported transactional execution for key table‑focused DDLs like CREATE TABLE, DROP TABLE, TRUNCATE TABLE, CTAS and sp_rename—with this release, ALTER TABLE now joins that set, enabling truly atomic and safe schema evolution. A new approach to schema changes Previously, supported ALTER TABLE statements in Fabric Data Warehouse ran outside of explicit user transactions. Attempting to include ALTER TABLE within a BEGIN TRAN—COMMIT block caused the operation to fail with the error: Transaction failed because this DDL statement is not allowed inside a snapshot isolation transaction. This presented significant challenges for enterprise clients: No atomicity for schema evolution—multi-step migrations could fail halfway through, leaving warehouses in partially updated states. Brittle CI/CD pipelines—because ALTER TABLE couldn’t participate in explicit user transactions while other DDLs and DMLs could, schema migrations that combined ALTER TABLE with other operations couldn’t be executed atomically in a single transaction—adding deployment complexity and increasing failure risk. Manual recovery on failure—when a deployment failed mid-flight, teams often had to manually diagnose and fix schema drift. What’s new: ALTER TABLE inside BEGIN TRAN Supported ALTER TABLE operations can now execute inside an explicit user-defined transaction in Fabric Data Warehouse Now, you’re able to write: -- Sample Syntax--- BEGIN TRAN; ALTER TABLE <table_name> ADD <column_name> <type>; ALTER TABLE <table_name> DROP COLUMN <column_name>; COMMIT; Additionally, Fabric will process these schema changes as a single atomic operation. If any statement in the transaction fails, all schema changes are automatically rolled back - leaving your warehouse in a consistent, predictable state Learn more: ALTER TABLE syntax for Warehouse in Fabric. Why this matters Atomic, All-or-Nothing Schema Changes Multiple ALTER TABLE statements can now be grouped and committed together. No more partial updates. Stronger Correctness Guarantees Schema evolution now follows the same transactional semantics as data changes, aligned with Fabric DW’s snapshot isolation and rollback model. Safer Enterprise Deployments CI/CD pipelines become simpler and more reliable. Failures automatically roll back without manual intervention. Supported scenarios Fabric Data Warehouse supports executing the following inside an explicit transaction: ALTER TABLE Add nullable columns ALTER TABLE Drop columns ALTER TABLE Add or drop NOT ENFORCED PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints Execute multiple ALTER TABLE statements atomically ALTER distributed temporary tables If any operation fails, the entire transaction is rolled back automatically. Note: Certain operations—such as adding non-nullable columns / ALTER COLUMN / ALTER on non-distributed temp tables—remain unsupported at this time and will continue to surface clear, actionable error messages. Refer to the Transactions in Fabric Data Warehouse documentation for the most up-to-date list of supported features, as Fabric Data Warehouse continues to evolve and add new capabilities. A foundational step forward Transactional DDL is a core expectation for enterprise data platforms. By bringing ALTER TABLE into explicit user transactions, Fabric Data Warehouse unlocks safer, more predictable schema evolution workflows. This release is more than a syntax change. It’s a step toward stronger guarantees of correctness and operational trust for customers building mission-critical analytics on Fabric.Understanding Locking and DDL Blocking in Microsoft Fabric Data Warehouse
As adoption of Microsoft Fabric Data Warehouse accelerates, understanding its concurrency model becomes essential for developers, architects, and data engineers. In this post, we’ll explore how Fabric DW handles locking, the nuances of DDL blocking, and how these behaviors impact your workloads. Locking Behavior in Fabric Data Warehouse Fabric DW supports ACID-compliant transactions using standard T-SQL (BEGIN TRANSACTION, COMMIT, ROLLBACK) and enforces snapshot isolation across all operations. Locks in Fabric Data Warehouse are used to manage concurrent access to metadata and data, especially during DDL operations. Here’s how locking works: Table-Level Lock Modes by Operation Statement type Lock taken SELECT Schema-Stability (Sch-S) INSERT Intent Exclusive (IX) DELETE Intent Exclusive (IX) UPDATE Intent Exclusive (IX) MERGE Intent Exclusive (IX) COPY INTO Intent Exclusive (IX) CREATE TABLE Schema-Modification (Sch-M) ALTER TABLE Schema-Modification (Sch-M) DROP TABLE Schema-Modification (Sch-M) TRUNCATE Schema-Modification (Sch-M) CTAS Schema-Modification (Sch-M) CREATE TABLE AS CLONE Schema-Modification (Sch-M) Fabric DW uses table-level locking, regardless of whether a query touches one row or many. This simplifies the engine’s concurrency model but introduces some trade-offs: Pros: Predictable behavior, easier to reason about. Cons: Can lead to blocking in high-concurrency scenarios, specifically when DDLs are included. DDL Blocking Behavior Long-running transactions that have included at least one DDL operation can block concurrent transactions. This is the current design of Fabric DW. This locking behavior aligns with SqlDb behavior. DDL operations acquire Sch-M locks on target table for the duration of the transaction. These locks prevent concurrent DML operations (e.g., SELECT, INSERT, UPDATE, DELETE ) targeting the same table from proceeding. DDL operations also acquire an X lock on rows in sys.tables and sys.objects associated with the target table of the DDL. This will block concurrent SELECT statements on sys.tables and sys.objects. Since Sch-M locks on tables and X locks on sys.tables rows are held for the duration of the transaction, they can be especially long-lived if the DDL is done in an explicit user transaction (i.e. inside BEGIN TRAN ). This behavior is particularly impactful in scenarios involving: Schema evolution during active workloads. Automated schema updates or migrations. Blocking and Conflicts can impact performance, reliability, and user experience, especially in high-concurrency environments. Understanding how they work and how to mitigate them is essential for building resilient data pipelines and applications in Fabric DW. Best Practices for Developers Avoid long-running transactions that hold locks for extended periods. Schedule DDL operations during maintenance windows to minimize blocking. Avoid placing DDL statements inside explicit user transactions (e.g., BEGIN TRAN ) as this can cause blocking issues for concurrent DML operations and SELECT queries, both on user tables and system catalog views like sys.tables . To monitor and troubleshoot potential lock conflicts, use sys.dm_tran_locks . What’s Next READPAST hint to mitigate DDL blocking on metadata queries - This enhancement will introduce support for the READPAST table hint when querying sys.tables This allows metadata queries to skip over rows that are currently locked with an exclusive (X) lock—typically those associated with tables undergoing DDL operations within active transactions. As a result, queries against sys.tables will no longer be blocked entirely by DDL activity and sys.tables will instead return all other available rows, improving system responsiveness and observability during schema changes.Resolving Write Conflicts in Microsoft Fabric Data Warehouse
Fabric Data Warehouse (DW) supports ACID-compliant transactions using standard T-SQL (BEGIN TRANSACTION, COMMIT, ROLLBACK) and uses Snapshot Isolation (SI) as its exclusive concurrency control model. All operations within a transaction are treated atomically—either all succeed or all fail. This ensures that each transaction operates on a consistent snapshot of the data as it existed at the start of the transaction, which means. Read Consistency: All reads within a transaction reflect the state of all attached databases as of the first read or write operation following the BEGIN TRANSACTION statement, aligning with SQL Server’s snapshot isolation behavior. Optimistic Concurrency: Conflicts are detected at commit time, not during execution. This avoids locking overhead but introduces rollback risks. While this model enables high concurrency for reads, it introduces the possibility of write-write conflicts which are common under SI when multiple transactions attempt to modify the same data concurrently. Write-write conflicts in Fabric Data Warehouse are fundamentally different from lock-based conflicts. For more information on lock conflicts, refer to Understanding Locking and DDL Blocking in Microsoft Fabric Data Warehouse blog post. Write-Write Conflict A write-write conflict (aka update conflict) occurs when two or more concurrent transactions attempt to perform conflicting operations such as UPDATE, DELETE, MERGE or TRUNCATE on the same table. Under SI, only the first transaction to commit will succeed; others will be aborted with a conflict error. Common Error Messages surfaced: Error 24556: Snapshot isolation transaction aborted due to update conflict. Using snapshot isolation to access table '%.*ls' directly or indirectly in database '%.*ls' can cause update conflicts if rows in that table have been deleted or updated by another concurrent transaction. Retry the transaction. Error 24706: Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table '%.*ls' directly or indirectly in database '%.*ls' to update, delete, or insert the row that has been modified or deleted by another transaction. Please retry the transaction. Users should treat the two error messages the same - retry the operation. Operations Participating in Write-Write Conflict UPDATE DELETE MERGE TRUNCATE MERGE Behavior: Even when MERGE transactions only result in append-only changes, they are still subject to write-write conflict detection. When MERGE transaction affects different rows than other concurrent DML transactions, it may encounter this error if MERGE is not the first transaction to commit: 'Snapshot isolation transaction aborted due to update conflict.' This granularity means that even if two transactions touch different rows, they will conflict if they target the same table. Only the first transaction to commit will succeed. Others will be rolled back with errors such as: 'Snapshot isolation transaction aborted due to update conflict...' This behavior is by design and ensures consistency, but it can be disruptive in high-throughput pipelines. What causes Write – Write conflicts Fabric DW currently tracks write-write conflicts at the table level, meaning even if two transactions modify different rows in the same table, they can still conflict. Conflicts arise from two primary sources: User-Induced Workload Conflicts Multiple users or processes concurrently modifying the same table. Common in ETL pipelines, batch updates, or overlapping transactions. System-Induced Conflicts (Compaction) Background system tasks like automatic data compaction rewrites files with poor quality. These can conflict with user transactions such as UPDATE, DELETE, MERGE even if the user is modifying different rows. Example: A user updates a table while compaction rewrites it. If compaction commits first, the user’s transaction fails due to a write-write conflict. Refer to Data Compaction documentation to learn more about compaction. Conflicts can impact performance, reliability, and user experience, especially in high-concurrency environments. Understanding how they work and how to mitigate them is essential for building resilient data pipelines and applications in Fabric DW. Best Practices to Avoid Conflicts Avoid concurrent UPDATE, DELETE, MERGE on the same table. Use Retry Logic for DML Statements for UPDATE , DELETE, MERGE Fabric DW uses Snapshot Isolation (SI), which detects conflicts at commit time. If a transaction fails due to a conflict, retrying the statement is often successful. Recommended for UPDATE , DELETE , MERGE , and TRUNCATE statements. Especially effective for single-statement transactions. Customers are advised to implement retry logic in stored procedures or ETL pipelines or tools like Qlik replicate. This is the most effective short-term workaround. In-Product Capabilities As part of our ongoing investments to improve concurrency and reliability in Fabric Data Warehouse, we’re introducing Compaction Preemption, a new capability that intelligently avoids write-write conflicts between background compaction tasks and user operations. As of October 2025, Compaction preemption is enabled and is handled entirely by the system to reduce write-write conflicts between user workloads and system compaction tasks in Fabric DW, which uses snapshot isolation for concurrency control. Problem Compaction Preemption Solves Fabric DW uses Snapshot Isolation (SI), where write-write conflicts occur if two transactions modify the same table concurrently. Background compaction, which rewrites table files to optimize storage can silently conflict with user operations like UPDATE , DELETE , or MERGE , causing failures. How Compaction Preemption Works Compaction Preemption introduces a shared lock mechanism: User transactions take a shared lock on a preemption-specific sub resource. Compaction checks for this lock: Before execution: If the lock is held, compaction aborts immediately. Before commit: If the lock is held, compaction aborts again to avoid conflict. Compaction does not take a lock, ensuring it never blocks user workloads. Users can still create write-write conflicts through their own workload; this feature only addresses conflicts involving compaction, not user-initiated conflicts. If users use explicit transactions and perform non-conflicting work (like inserts) before a conflicting operation (update, delete, merge), compaction may still commit successfully, and a subsequent user operation could still fail due to a conflict. What’s Next We are actively investing in improvements to improve concurrency – Currently, conflicts are detected at the table level. Introducing more granularity such as file-level conflict detection / row- level conflict detection to improve concurrency.Session-scoped distributed #temp tables in Fabric Data Warehouse (Generally Available)
Introducing distributed session-scoped temporary (#temp) tables in Fabric Data Warehouse and Fabric Lakehouse SQL Endpoints. #temp tables have been a feature of Microsoft SQL Server (and other database systems) for many years. In the current implementation of Fabric data warehouse, #temp tables are session scoped or local temp tables. Global temp tables are not included in this release. Session-scoped #temp tables exist only within the session in which they are created and last only for the duration of that session. They are not visible to other users or sessions and are automatically dropped from the system once the session ends or the user decides to drop the temp table. These tables are accessible to all users without requiring specific artifact-level permission. Benefits of Session-Scoped #temp Tables They can be used as a staging area for data before inserting it into user tables, helping to streamline the data insertion process. They are also ideal for storing intermediate results during complex data transformations, which can significantly optimize query performance. Developers find them useful for testing and debugging purposes, making them a versatile tool in various scenarios. The ease of use and flexibility of #temp tables allow for dynamic creation and dropping within a session, providing a convenient solution for temporary data storage and calculations. Types of Session-Scoped #temp Tables Two types of #temp tables can be created based on specific use cases: Non-distributed #temp Table (mdf-backed) is the default type. The syntax for creating and using non-distributed #temp tables in Fabric Data Warehouse is similar to user tables, but you need to prefix the temp table name with #. CREATE TABLE #table_name ( Col1 data_type1, Col2 data_type2 ); Distributed #temp Tables (Parquet-Backed) can be created with the distribution equals round-robin keyword. CREATE TABLE #table_name ( Col1 data_type1, Col2 data_type2 ) WITH (DISTRIBUTION=ROUND_ROBIN); *data_type1 and data_type2 are placeholders for the supported data types in Fabric Data Warehouse Data types - Microsoft Fabric | Microsoft Learn. Due to session context limitations in the Fabric query editor, it is recommended to use SSMS or execute #temp table queries within the same query execution context. Using distributed #temp table is recommended as they fully align with warehouse user tables in terms of unlimited storage, data types supported, operations we can perform on them. The syntax for the rest of the operations is like user tables in Fabric Data warehouse, with prefix '#' added to the table name to indicate that the table is a session-scoped #temp table. For distributed #temp tables, if a collation is not specified for a string-type column, it defaults to the "DATABASE_DEFAULT" collation type. This means it inherits the collation of the current database context. If a transaction involving a #temp table fails or when a #temp table is dropped using the DROP TABLE command, the table becomes immediately eligible for Garbage Collection. In the event of a front-end failover, all #temp tables from the previous session are also eligible for GC, ensuring that temp tables do not persist across failovers. Why two types of #temp Tables? SSMS uses #temp tables to populate the object explorer pane, displaying databases and tables. Non-distributed #temp tables adhere to SQL DB behavior and are the default type to ensure compatibility with existing tools like SSMS and other Azure tools. SSMS uses #temp tables for operations such as inserting from DMVs and catalog views. Non-distributed #temp tables support all column types, whereas distributed #temp tables have limitations due to unsupported column types (such as using column types sysname and nvarchar) in some DMVs, which is why they cannot be the default type of temp tables in Fabric Data Warehouse. Distributed #temp tables address the limitations of non-distributed #temp tables, such as the ability to integrate with distributed user tables and support for creating temp tables of unlimited size. Common scenarios supported by each type of #temp tables Scenario Is supported by non-distributed #temp table Is supported by distributed #temp table Create session scoped local #temp tables Yes Yes Insert into the #temp table VALUES (<value>’),(‘value’) Yes Yes Create a global ##temp table No No SELECT INTO from one temp table to another *In Fabric DW, if a target of a SELECT INTO/CTAS is a temp table, a distributed temp table is created always (regardless of source table's type). So, operations like Select-Into with source as non-distributed temp tables will not be supported No Yes Insert into #temp table SELECT from parquet backed user table No Yes Join two #temp tables *Joining non distributed temp tables with distributed temp tables is not a supported scenario Yes Yes Join #temp with sys.tables *for non-distributed #temp table, use collate clause to resolve the collation conflict Yes No Insert into #temp table exec sp No No Insert into user table select * from #temp table No Yes Insert into #temp table from DMVs and Catalog views (sys.tables) Yes No Create unlimited sized #temp table No Yes Drop #temp table Yes Yes Create and use distributed temp tables in nested statement execution such as stored proc, TSQL while loop, sp_executesql Yes Yes Conclusion We are actively working on enabling additional scenarios for distributed #temp table. Stay tuned for more updates. Submit your feedback on Fabric Ideas and join the conversation on the Fabric Community.
