Zero-copy cloning in Snowflake - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a zero-copy clone of a table named 'sales_data'.

Snowflake

CREATE TABLE sales_data_clone CLONE [1];

Drag options to blanks, or click blank then click option'

Asales_data

Bsales_data_clone

Csales

Dclone_table

Attempts:

3 left

💡 Hint

Common Mistakes

Using the clone table name instead of the original table name.

Misspelling the source table name.

The CLONE keyword is used to create a zero-copy clone of the existing table sales_data.

2fill in blank

medium

Complete the code to clone a database named 'analytics_db' using zero-copy cloning.

Snowflake

CREATE DATABASE analytics_db_clone CLONE [1];

Drag options to blanks, or click blank then click option'

Aanalytics

Banalytics_db

Canalytics_db_clone

Dclone_db

Attempts:

3 left

💡 Hint

Common Mistakes

Using the clone database name as the source.

Using an incorrect database name.

The CLONE keyword clones the original database analytics_db without copying data.

3fill in blank

hard

Fix the error in the code to clone a schema named 'public' from database 'sales_db'.

Snowflake

CREATE SCHEMA sales_db_clone.public CLONE [1];

Drag options to blanks, or click blank then click option'

Asales_db_clone.public

Bsales_db_clone

Csales_db.public

Dpublic

Attempts:

3 left

💡 Hint

Common Mistakes

Using the clone schema name as the source.

Using only the schema name without database.

The source schema must be fully qualified with the original database and schema name: sales_db.public.

4fill in blank

hard

Fill both blanks to clone a table named 'orders' from schema 'sales' in database 'retail_db' into a new schema 'sales_clone'.

Snowflake

CREATE TABLE sales_clone.orders CLONE [1].[2].orders;

Drag options to blanks, or click blank then click option'

Aretail_db

Bsales

Csales_clone

Dorders

Attempts:

3 left

💡 Hint

Common Mistakes

Using the clone schema name instead of the original schema.

Swapping database and schema names.

The clone source must specify the original database and schema: retail_db.sales.orders.

5fill in blank

hard

Fill all three blanks to clone a zero-copy clone of a stage named 'data_stage' from database 'prod_db' and schema 'public' into a new stage in schema 'dev_schema'.

Snowflake

CREATE STAGE dev_schema.data_stage_clone CLONE [1].[2].[3];

Drag options to blanks, or click blank then click option'

Aprod_db

Bpublic

Cdata_stage

Ddev_schema

Attempts:

3 left

💡 Hint

Common Mistakes

Using the clone schema or stage name as source.

Missing one of the three parts in the source.

The clone source must be fully qualified with database, schema, and stage name: prod_db.public.data_stage.

Practice

1. What is the main benefit of zero-copy cloning in Snowflake?

easy

A. It deletes the original data after cloning.

B. It compresses data to save storage space.

C. It encrypts data automatically during cloning.

D. It creates instant copies without duplicating the original data.

Solution

1. Step 1: Understand zero-copy cloning concept

   Zero-copy cloning creates a copy of data instantly without physically duplicating the data.

2. Step 2: Identify the main benefit

   This saves storage and time because the clone shares the original data until changes are made.

3. Final Answer:

   It creates instant copies without duplicating the original data. -> Option D

4. Quick Check:

   Zero-copy cloning = instant copy without duplication [OK]

Hint: Zero-copy cloning shares data, no extra storage used [OK]

Common Mistakes:

• Thinking cloning duplicates all data immediately
• Confusing cloning with data encryption
• Assuming original data is deleted after cloning

2. Which of the following is the correct syntax to create a zero-copy clone of a table named sales_data in Snowflake?

easy

A. CREATE TABLE sales_data_clone AS CLONE sales_data;

B. CREATE CLONE sales_data_clone FROM sales_data;

C. CREATE TABLE sales_data_clone CLONE sales_data;

D. CLONE TABLE sales_data TO sales_data_clone;

Solution

1. Step 1: Recall Snowflake cloning syntax

   The correct syntax to clone a table is: CREATE TABLE new_table CLONE original_table;

2. Step 2: Match syntax with options

   CREATE TABLE sales_data_clone CLONE sales_data; matches the correct syntax exactly.

3. Final Answer:

   CREATE TABLE sales_data_clone CLONE sales_data; -> Option C

4. Quick Check:

   CREATE TABLE ... CLONE ... is correct syntax [OK]

Hint: Use CREATE TABLE new_table CLONE original_table [OK]

Common Mistakes:

• Using CREATE CLONE instead of CREATE TABLE ... CLONE
• Adding AS before CLONE incorrectly
• Using CLONE TABLE ... TO syntax which is invalid

3. Consider this Snowflake SQL snippet:

CREATE TABLE original (id INT, value STRING);
INSERT INTO original VALUES (1, 'A'), (2, 'B');
CREATE TABLE clone_table CLONE original;
INSERT INTO clone_table VALUES (3, 'C');
SELECT COUNT(*) FROM original;

What will be the output of the SELECT query?

medium

A. 2

B. Error

C. 0

D. 3

Solution

1. Step 1: Understand cloning and data insertion

   The clone_table is a zero-copy clone of original, sharing data initially. Inserting into clone_table does not affect original.

2. Step 2: Count rows in original table

   Original has 2 rows inserted initially and remains unchanged after clone_table insert.

3. Final Answer:

   2 -> Option A

4. Quick Check:

   Original rows unchanged after clone insert [OK]

Hint: Inserts to clone don't affect original table [OK]

Common Mistakes:

• Assuming insert in clone adds rows to original
• Thinking clone is a full independent copy immediately
• Expecting error due to cloning

4. You tried to create a zero-copy clone of a database using:

CREATE DATABASE clone_db CLONE original_db;

But received an error. What is the most likely cause?

medium

A. The original database does not exist.

B. You cannot clone a database, only tables.

C. The clone syntax requires the keyword TABLE.

D. You need to specify a schema when cloning a database.

Solution

1. Step 1: Check cloning rules for databases

   Snowflake supports cloning databases if the original database exists.

2. Step 2: Identify error cause

   If the original_db does not exist, cloning fails with an error.

3. Final Answer:

   The original database does not exist. -> Option A

4. Quick Check:

   Cloning requires existing source database [OK]

Hint: Ensure original database exists before cloning [OK]

Common Mistakes:

• Thinking databases cannot be cloned
• Adding TABLE keyword incorrectly
• Assuming schema must be specified

5. You want to create a zero-copy clone of a large table for testing, but only want to clone data as it was at a specific past time. Which Snowflake feature should you use with cloning?

hard

A. Use a full data export and import instead of cloning.

B. Use Time Travel to specify the past timestamp during cloning.

C. Clone the table and then manually delete unwanted rows.

D. Use a materialized view to clone the data at that time.

Solution

1. Step 1: Understand cloning with Time Travel

   Snowflake allows cloning data as it existed at a past time using Time Travel feature.

2. Step 2: Apply Time Travel in cloning

   You specify the timestamp or offset in the clone statement to get data snapshot at that time.

3. Final Answer:

   Use Time Travel to specify the past timestamp during cloning. -> Option B

4. Quick Check:

   Time Travel + cloning = snapshot at past time [OK]

Hint: Combine Time Travel with clone for past data snapshot [OK]

Common Mistakes:

• Thinking cloning alone can get past data
• Using export/import which is slower
• Using materialized views which don't clone data