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

DP-600 Last updated

Maintain the analytics development lifecycle

Short answer

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

Question

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?

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

Key Takeaways

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.

Common mistake: 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.

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.

Community Votes

C
67%
D
33%

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

Discussion 15 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded.

From public community threads 15

  1. 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.
  2. clux 👍 6 Answered 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.
  3. 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
  4. vernillen 👍 3 Answered C
    AS CLONE basically minimizes the copying of data. That's the biggest requirement.
  5. 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.
Show 10 more comments
  1. 4fbcd40 👍 2 Answered 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
  2. sandy789 👍 2
    go with D. C is a clone of table's metadata not copy data activity
  3. Nefirs 👍 2 Answered C
    C is correct - Cloning reduces data movement
  4. 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.
  5. cafb698 👍 1 Answered 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.
  6. Rataxe 👍 1 Answered D
    The schemas are existing so the correct answer is CREATE TABLE schema2.city AS SELECT * FROM schema1.city;
  7. dp600 👍 1 Answered C
    I would go with C, is the fastest way to replicate the schema.
  8. paffy 👍 1 Answered C
    should be C, since copying of data should be minimized
  9. AGTraining 👍 3 Answered D
    d is the write answer
  10. Jasneet 👍 1 Answered C
    C is correct

Reader comments & corrections

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

Log in to comment, report an error, or add a note about this question.

Submitted for moderation before publishing. Keep it helpful and respectful.

Fabric Deployment Pipeline Folder Structure Behavior

S-Grade

Accessing OneLake Shortcuts in Fabric Notebooks

S-Grade

Direct Lake Fallback to DirectQuery with Row-Level Security

S-Grade

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