DEA-C02 Storage and Data Protection Practice Question

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

• ✗

  CREATE TABLE dev_db.public.fact_sales_clone AS SELECT * FROM prod_db.public.fact_sales;

  Why it's wrong here

  CTAS physically copies all rows into new micro-partitions, immediately consuming roughly 5 TB of additional storage. It does not share partitions with the source. While it produces an independent copy, it defeats the goal of avoiding extra storage at creation, and it also incurs compute cost to read and rewrite the data.

• ✓

  CREATE TABLE dev_db.public.fact_sales_clone CLONE prod_db.public.fact_sales;

  Why this is correct

  Zero-copy cloning creates a new table that initially references the same micro-partitions as the source, so no data is physically duplicated at creation. This satisfies the requirement of no additional storage up front. Storage diverges only when either table is modified, at which point new micro-partitions are written and the clone stops sharing those changed partitions.

• ✗

  ALTER TABLE prod_db.public.fact_sales SET CLONE dev_db.public.fact_sales_clone;

  Why it's wrong here

  There is no ALTER TABLE ... SET CLONE syntax in Snowflake. Cloning is performed with the CLONE keyword inside a CREATE statement, as in CREATE TABLE ... CLONE. This invented command would fail to parse, so it cannot achieve the zero-copy goal. The engineer must use the supported CREATE ... CLONE form.

• ✗

  CREATE TABLE dev_db.public.fact_sales_clone LIKE prod_db.public.fact_sales;

  Why it's wrong here

  CREATE TABLE ... LIKE copies only the column definitions and table properties; it creates an empty table with no rows and no shared micro-partitions. It does not produce a data copy at all, so it fails the requirement to have the same data available for query testing. It is used for schema replication, not data duplication.