Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)!
Last updated: 22-Jan-2025
[Microsoft Fabric] Zero-copy Clone
- {def} a replica of an existing OneLake table created by copying existing table's metadata and referencing its data files [1]
- the metadata is copied while the underlying data of the table stored as parquet files is not copied [1]
- its creation is like creating a delta table [1]
- DML/DDL changes on the source
- are not reflected in the clone table [1]
- are not reflected on the source [1]
- can be created within or across schemas in a warehouse [1]
- created based on either:
- current point-in-time
- based on the present state of the table [1]
- previous point-in-time
- based on a point-in-time up to seven days in the past
- the table clone contains the data as it appeared at a desired past point in time
- all CRUD operations are retained for seven calendar days
- created with a timestamp based on UTC
- {characteristic} autonomous existence
- the original source and the clones can be deleted without any constraints [1]
- once a clone is created, it remains in existence until deleted by the user [1]
- {characteristic} inherits
- object-level SQL security from the source table of the clone [1]
- DENY permission can be set on the table clone if desired [1]
- the workspace roles provide read access by default [1]
- all attributes that exist at the source table, whether the clone was created within the same schema or across different schemas in a warehouse [1]
- the primary and unique key constraints defined in the source table [1]
- a read-only delta log is created for every table clone that is created within the Warehouse [1]
- {benefit} facilitates development and testing processes
- by creating copies of tables in lower environments [1]
- {benefit} provides consistent reporting and zero-copy duplication of data for analytical workloads and ML modeling and testing [1]
- {benefit} provides the capability of data recovery in the event of a failed release or data corruption by retaining the previous state of data [1]
- {benefit} helps create historical reports that reflect the state of data as it existed as of a specific point-in-time in the past [1]
- {limitation} table clones across warehouses in a workspace are not currently supported [1]
- {limitation} table clones across workspaces are not currently supported [1]
- {limitation} clone table is not supported on the SQL analytics endpoint of the Lakehouse [1]
- {limitation} clone of a warehouse or schema is currently not supported [1]
- {limitation} table clones submitted before the retention period of seven days cannot be created [1]
- {limitation} cloned tables do not currently inherit row-level security or dynamic data masking [1]
- {limitation} changes to the table schema prevent a clone from being created prior to the table schema change [1]
- {best practice} create the clone tables in dedicated schema(s)
- [syntax] CREATE TABLE <schema.clone_table_name> AS CLONE OF <schema.table_name>
Previous Post
<<||>>
Next Post
References:
[1] Microsoft Learn (2023) Clone table in Microsoft Fabric [link]
[2] Microsoft Learn (2024) Tutorial: Clone tables in the
Fabric portal [link]
[3] Microsoft Learn (2024) Tutorial: Clone a table with T-SQL in a Warehouse [link]
[4] Microsoft Learn (2024) SQL: CREATE TABLE AS CLONE OF [link]
