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
powerBIAccessTokenand 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.
- Choose to create a warehouse snapshot from the current warehouse state or a previous point in time (within the last 30 days).
- 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.
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:
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).
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.
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.