Microsoft Fabric Updates Blog

Table Clone in warehouse within Microsoft Fabric

In the rapidly changing landscape of artificial intelligence and data-driven advancements, data warehouses play a pivotal role. They serve as a backbone for efficient data management and serve as catalysts for enabling transformative capabilities. The challenges associated with data management multiples with growth in volume and complexity of data. Safeguarding data integrity, while also providing flexibility to execute operations such as development, testing, and reporting without compromising the production environment is a challenge that many organizations face.

To overcome these complexities, we are excited to announce the preview of table clones in Data warehouse within Microsoft Fabric! The ability to create clones helps achieve data isolation while maintaining the integrity of the production environment. The Warehouse in Microsoft Fabric allows the creation of zero-copy clones near-instantaneously at the granularity level of table.

What is a zero-copy Clone?

A zero-copy clone creates a replica of the table by copying the metadata, while referencing the same data files in One Lake. This avoids the need to store multiple copies of data, thereby saving on storage costs. During the creation of a clone, the metadata is copied while the underlying data of the table which is stored as parquet files remains referenced without being duplicated.

The scenarios where table clones prove beneficial are:

Development and Testing: Clones empower developers and testers with enhanced flexibility by spinning up near-instantaneous zero-copy replicas of tables which can be leveraged for development, debugging, testing and validation purposes. The ability to clone helps implement parallelized data operations which help accelerate and scale up the development and testing process.

Near-Instantaneous Data Recovery: Clones can be leveraged to recover the tables to their prior state almost near-instantaneously due to a failed release or corruption scenarios. Periodic creation of table clones enables quick and easy recovery of the tables. The metadata of the previous versions reference the same underlying data present in One Lake so that the tables can be recovered quickly ensuring business continuity.

Consistent Reporting and Analytics: Clones offer the advantage of enabling reports and dashboards to perform analytics without impacting on the ongoing ETL operations in the production environment. This parallel reporting process upholds data integrity while providing the opportunity to make informed decisions based on the most recent data.

Data exploration and Machine learning modelling: As the clones have an independent existence, they can be leveraged to perform various data exploration and analysis without the risk of modifying the original data. Table clones help create a platform for training and testing machine learning models, enabling data scientists and analysts to dive deeper into data and uncover patterns which help to make informed decisions while preserving the integrity of the production environment. Additionally, as the data volume grows, clones facilitate reproducing the same machine learning models, data and reports as of a specific point in time it was created, ensuring consistency and reproducibility.

Let’s dive deeper into the capabilities of Table clone in Warehouse in Microsoft Fabric.

Near-instantaneous zero-copy clones of tables of any volume of data can be created within and across schema within a Warehouse in Microsoft Fabric.

Any number of tables can be cloned, and tables can be cloned for multiple iterations.

New attributes or data added to the source of the table clone are not reflected in the clone; also, any new attributes or data added to the clone is not reflected at the source of the clone.

More details on table clones can be found here.

Tables can be cloned using a T-SQL statement.

Creating a table clone within the same schema.

CREATE TABLE dbo.dimension_city1 AS CLONE OF dbo.dimension_city;

Creating a table clone across schemas within the same warehouse.

CREATE TABLE abc.dimension_city1 AS CLONE OF dbo.dimension_city;

Who can Clone a table?

Administrator, Member and Contributor workspace roles having SELECT ALL permissions to all the rows and columns of a table can clone.


We are thrilled to present you with the capability of table clones, which not only expedite development and testing processes but also streamlines reporting and analytics on the most recent data, all while upholding data integrity. Get started with table clone by creating your first clone using the tutorial. We are waiting to hear from you.


Related blog posts

Table Clone in warehouse within Microsoft Fabric

July 17, 2024 by Ambika Jagadish

In the rapidly evolving world of generative artificial intelligence, historical data plays a significant role in influencing the decision-making process and shaping organizational strategies. Data retention within data warehouse refers to the practice of preserving and managing previous iterations of the data encompassing any inserts, updates or deletes made to the warehouse for a specified … Continue reading “Announcing the General availability of time travel and 30 days of data retention in Fabric Warehouse”

July 17, 2024 by Sowmya Sivaraman

In today’s rapidly evolving data management landscape, maintaining the resilience and continuity of your data infrastructure is essential. Unplanned system failures and scheduled maintenance alike demand the ability to restore data warehouses swiftly and seamlessly. This capability is no longer just a feature – it’s a critical necessity in modern analytics environments. A quick and … Continue reading “Seamless Data Recovery through Warehouse Restoration within Fabric Query Editor”