Clone use cases (dev, testing, backups) in Snowflake - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a clone of a database for development use.

Snowflake

CREATE DATABASE dev_clone CLONE [1];

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

Atest_db

Bproduction_db

Cbackup_db

Darchive_db

Attempts:

3 left

💡 Hint

Common Mistakes

Cloning from a test or backup database instead of production.

Using incorrect database names.

The production_db is cloned to create a development environment without affecting the original data.

2fill in blank

medium

Complete the code to clone a schema for testing purposes.

Snowflake

CREATE SCHEMA test_schema_clone CLONE [1].sales_schema;

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

Aprod_db

Bbackup_db

Cdev_db

Darchive_db

Attempts:

3 left

💡 Hint

Common Mistakes

Cloning from development or backup databases.

Using the wrong database name.

The prod_db is cloned to create a test schema, allowing safe testing without impacting production.

3fill in blank

hard

Fix the error in the clone creation command for backup purposes.

Snowflake

CREATE DATABASE backup_clone [1] backup_db;

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

ADUPLICATE

BCOPY

CCLONE

DREPLICATE

Attempts:

3 left

💡 Hint

Common Mistakes

Using COPY instead of CLONE.

Using unsupported keywords like DUPLICATE or REPLICATE.

The correct keyword to create a clone in Snowflake is CLONE. Other keywords like COPY or DUPLICATE are invalid here.

4fill in blank

hard

Fill both blanks to clone a table for testing and rename it.

Snowflake

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

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

Atest_orders

Bprod_db

Cdev_db

Dbackup_orders

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong source database.

Naming the clone with a confusing or incorrect name.

The table orders from prod_db is cloned and renamed test_orders for testing.

5fill in blank

hard

Fill all three blanks to clone a database for backup, set a comment, and specify a clone time.

Snowflake

CREATE DATABASE [1] CLONE [2] AT (TIMESTAMP => '[3]');

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

Abackup_db_clone

Bproduction_db

C2024-06-01 00:00:00

Dtest_db

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong database name for cloning.

Incorrect timestamp format.

Confusing test and production databases.

This command clones the production_db as backup_db_clone at the specified timestamp for backup purposes.

Practice

1. What is the main benefit of using CLONE in Snowflake for development?

easy

A. It moves data to a different cloud provider automatically.

B. It permanently deletes the original data after copying.

C. It creates a quick, space-saving copy of data without affecting the original.

D. It encrypts the data for security during development.

Solution

1. Step 1: Understand cloning purpose in development

   Cloning creates a copy of data instantly without changing the original, allowing safe development.

2. Step 2: Identify benefits of cloning

   It saves storage by sharing unchanged data and avoids risks to original data.

3. Final Answer:

   It creates a quick, space-saving copy of data without affecting the original. -> Option C

4. Quick Check:

   Clone = quick, safe copy [OK]

Hint: Clones copy data fast without changing original [OK]

Common Mistakes:

• Thinking clone deletes original data
• Believing clone moves data between clouds
• Confusing clone with encryption

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

easy

A. CREATE TABLE sales_data_clone CLONE sales_data;

B. CREATE TABLE sales_data_clone AS CLONE sales_data;

C. CLONE TABLE sales_data TO sales_data_clone;

D. COPY TABLE sales_data TO sales_data_clone;

Solution

1. Step 1: Recall Snowflake clone syntax

   The correct syntax is CREATE TABLE new_table CLONE source_table;.

2. Step 2: Match syntax to 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 A

4. Quick Check:

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

Hint: Use CREATE TABLE new_table CLONE source_table syntax [OK]

Common Mistakes:

• Using AS CLONE instead of CLONE
• Using COPY instead of CLONE
• Incorrect order of keywords

3. Given the following commands:

CREATE TABLE orders (id INT, amount FLOAT);
INSERT INTO orders VALUES (1, 100.0), (2, 200.0);
CREATE TABLE orders_clone CLONE orders;
INSERT INTO orders_clone VALUES (3, 300.0);
SELECT COUNT(*) FROM orders;

What will be the result of the SELECT COUNT(*) FROM orders; query?

medium

A. 2

B. 3

C. 0

D. Error

Solution

1. Step 1: Understand cloning effect on data

   Cloning creates a copy at that moment. Changes to clone do not affect original.

2. Step 2: Analyze inserts and counts

   Original table has 2 rows inserted. The clone gets a new row inserted separately.

3. Step 3: Query original table count

   Original table still has 2 rows; clone changes do not affect it.

4. Final Answer:

   2 -> Option A

5. Quick Check:

   Original rows unchanged after clone insert [OK]

Hint: Clone changes don't affect original data count [OK]

Common Mistakes:

• Assuming clone insert adds to original
• Thinking clone shares insert changes
• Expecting error due to cloning

4. You cloned a database for testing but later found that some data changes in the clone are missing. Which is the most likely cause?

medium

A. You forgot to commit the changes in the clone before querying.

B. Cloning only copies metadata, not data.

C. Changes were made in the original, not the clone.

D. You cloned the database before the data was inserted.

Solution

1. Step 1: Understand cloning timing

   Cloning copies data as it exists at clone time; later changes in source are not included.

2. Step 2: Analyze missing data cause

   If clone was made before data insertion, clone won't have that data.

3. Final Answer:

   You cloned the database before the data was inserted. -> Option D

4. Quick Check:

   Clone snapshot time matters for data presence [OK]

Hint: Clone captures data state at clone time only [OK]

Common Mistakes:

• Assuming clone updates with source changes
• Confusing metadata-only clone
• Forgetting to commit changes

5. You want to create a backup of a large production schema without using extra storage. Which Snowflake feature should you use and why?

hard

A. Use CREATE DATABASE AS COPY which compresses data automatically.

B. Use CLONE to create a schema copy because it shares unchanged data and saves storage.

C. Create a new schema and manually copy all tables to save storage.

D. Export data to CSV files and store externally for backup.

Solution

1. Step 1: Identify backup needs

   Backup should be space-efficient and quick for large data.

2. Step 2: Evaluate Snowflake features

   Cloning creates instant copies sharing unchanged data, saving storage space.

3. Step 3: Compare options

   Exporting or manual copying uses more storage and time; no CREATE DATABASE AS COPY command exists.

4. Final Answer:

   Use CLONE to create a schema copy because it shares unchanged data and saves storage. -> Option B

5. Quick Check:

   Clone = instant, space-saving backup [OK]

Hint: Clone shares data, saves space for backups [OK]

Common Mistakes:

• Thinking manual copy saves storage
• Assuming export is space-efficient
• Using non-existent commands