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.
Conclusion:
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.
