How to Minimize Data Copying When Cloning a Table in Fabric?

Maintain the analytics development lifecycle

Answer Correct answer: C — Use CREATE TABLE AS CLONE OF to create a zero-copy table clone in the target schema, minimizing data duplication.

You have a Fabric tenant that contains a warehouse named Warehouse1. Warehouse1 contains two schemas name schema1 and schema2 and a table named schema1.city. You need to make a copy of schema1.city in schema2. The solution must minimize the copying of data. Which T-SQL statement should you run?

INSERT INTO schema2.city SELECT * FROM schema1.city;
SELECT * INTO schema2.city FROM schema1.city;
CREATE TABLE schema2.city AS CLONE OF schema1.city; Correct Answer
CREATE TABLE schema2.city AS SELECT * FROM schema1.city;

Community Votes

67%

33%

67% of anonymous learners picked answer C. Votes are pick records left by other test-takers — they are not the verified answer.

Community Insight

The question tests Fabric's zero-copy cloning feature; the trap is choosing standard T-SQL copy methods like CTAS or INSERT INTO which physically duplicate data.

Creating a zero-copy clone of a table in a Microsoft Fabric warehouse minimizes data movement and storage costs. The CREATE TABLE AS CLONE OF statement provides this near-instantaneous cloning capability across schemas.

Choosing option D (CREATE TABLE ... AS SELECT) because it creates a new table, but it physically copies the data instead of using a zero-copy clone.

Community Discussion (15 comments)

neoverma 👍 30

The “CREATE TABLE … AS CLONE OF” statement is the most efficient way to create a copy of a table in the same Fabric tenant. This statement creates a new table in the specified schema that is an exact clone of the source table, including the table structure, data, and indexes. This approach minimizes the amount of data copied, as it simply creates a reference to the underlying data rather than performing a full table scan and data copy. Options A and B, which use INSERT INTO or SELECT INTO, would require scanning the entire source table and copying the data row-by-row, which is less efficient than the CLONE method. Option D, CREATE TABLE … AS SELECT, would also work to create a copy of the table, but it would perform a full table scan and data copy, which is less efficient than the CLONE approach.

clux 👍 6 Selected: C

https://learn.microsoft.com/en-us/fabric/data-warehouse/clone-table Microsoft Fabric offers the capability to create near-instantaneous zero-copy clones with minimal storage costs.

Rataxe 👍 1 Selected: D

The schemas are existing so the correct answer is CREATE TABLE schema2.city AS SELECT * FROM schema1.city;

paffy 👍 1 Selected: C

should be C, since copying of data should be minimized

cafb698 👍 1 Selected: D

CLONE AS Creates a new table as a zero-copy clone of another table in Warehouse in Microsoft Fabric. Only the metadata of the table is copied. The underlying data of the table, stored as parquet files, is not copied. A zero-copy clone creates a replica of the table by copying the metadata, while still referencing the same data files in OneLake. The metadata is copied while the underlying data of the table stored as parquet files is not copied. The creation of a clone is similar to creating a table within a Warehouse in Microsoft Fabric.

sandy789 👍 2

go with D. C is a clone of table's metadata not copy data activity

vernillen 👍 3 Selected: C

AS CLONE basically minimizes the copying of data. That's the biggest requirement.

PiyushT 👍 2

C. CREATE TABLE schema2.city AS CLONE OF schema1.city; This statement creates a new table named city in schema2 that has the same structure as the city table in schema1 without copying any data. It essentially creates a metadata reference to the original table, which minimizes the data copying.

282b85d 👍 1

Option A: INSERT INTO schema2.city SELECT FROM schema1.city; This option assumes that schema2.city already exists. It will insert data into schema2.city but will not create the table. Option B: SELECT INTO schema2.city FROM schema1.city; This option copies data from schema1.city to schema2.city but is typically used in SQL Server to create a new table and copy data. However, it doesn't handle schema changes well and might not be the best for large datasets. Option C: CREATE TABLE schema2.city AS CLONE OF schema1.city; This is not a valid T-SQL syntax for creating tables. Option D: CREATE TABLE schema2.city AS SELECT * FROM schema1.city; This statement creates a new table schema2.city and copies all data from schema1.city into it (CTAS). This option is efficient for creating a new table with the same schema and data as the original table.

4fbcd40 👍 2 Selected: D

D is the correct answer. Only the metadata of the table is copied. The underlying data of the table, stored as parquet files, is not copied. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-as-clone-of-transact-sql?view=fabric

stilferx 👍 3

IMHO, C is good. Because: Within a warehouse, a clone of a table can be created near-instantaneously using simple T-SQL. A clone of a table can be created within or across schemas in a warehouse. Here: https://learn.microsoft.com/en-us/fabric/data-warehouse/clone-table#creation-of-a-table-clone

dp600 👍 1 Selected: C

I would go with C, is the fastest way to replicate the schema.

Nefirs 👍 2 Selected: C

C is correct - Cloning reduces data movement

Jasneet 👍 1 Selected: C

C is correct

AGTraining 👍 3 Selected: D

d is the write answer

Comments & Corrections

No comments yet — spotted an error or have a note? Share it below.

Expert Analysis

Why the Answer Is Correct

Option C uses the CREATE TABLE... AS CLONE OF T-SQL statement, which is a feature specific to Microsoft Fabric data warehouses. This statement creates a zero-copy clone of the source table, meaning it shares the existing data files rather than duplicating them. This directly satisfies the requirement to minimize the copying of data while making an exact copy of the table in a different schema.

Why the Other Options Are Wrong

Options A, B, and D all rely on traditional T-SQL data movement mechanisms. INSERT INTO... SELECT (Option A) and SELECT * INTO (Option B) physically read and write every row of data, incurring significant data copying and storage overhead. Option D (CREATE TABLE... AS SELECT) is Fabric's CTAS equivalent, which also physically copies the underlying data to create the new table, failing the requirement to minimize data copying.

Community Comment Notes

Several commenters correctly identified that the AS CLONE OF syntax creates a zero-copy clone that minimizes data movement, as neoverma noted when stating it "creates a reference to the underlying data rather than perf…" Others mistakenly favored Option D, with one commenter arguing "Only the metadata of the table is copied" for CTAS, confusing the metadata-only operation of cloning with the physical data copy performed by CTAS.

Official Reference

https://learn.microsoft.com/en-us/fabric/data-warehouse/clone-table

Exam Strategy

When a Fabric warehouse question asks to minimize data copying or storage costs for a table copy, look for the AS CLONE OF syntax. Standard T-SQL copy methods (CTAS, SELECT INTO) always physically duplicate data.

Related Analysis

Practice All DP-600 Questions

Access 115 questions with complete answers and detailed explanations.

View Full DP-600 Practice Test →

← Back to DP-600 Study Guide