Zero-copy cloning in Snowflake - Mini Project: Build & Apply

Zero-copy Cloning in Snowflake

📖 Scenario: You work as a data engineer at a company that uses Snowflake for data warehousing. Your team wants to create a copy of an existing database to test new features without using extra storage space.Snowflake's zero-copy cloning allows you to create a clone of a database instantly without duplicating the data physically.

🎯 Goal: Create a zero-copy clone of an existing database in Snowflake to enable safe testing without extra storage costs.

📋 What You'll Learn

Create a database named sales_db with a sample table

Set a variable for the clone database name sales_db_clone

Use zero-copy cloning to create sales_db_clone from sales_db

Verify the clone database exists

💡 Why This Matters

🌍 Real World

Zero-copy cloning helps data teams create test or development copies of large datasets instantly without extra storage costs.

💼 Career

Data engineers and cloud architects use zero-copy cloning to manage data environments efficiently and safely.

1

Create the original database and table

Write SQL commands to create a database called sales_db and a table called orders inside it with columns order_id (integer) and amount (decimal). Insert one row with order_id = 1 and amount = 100.00.

Snowflake

-- Create database sales_db and table orders with one row
-- Your code here

Need a hint?

Use CREATE DATABASE to make the database, then CREATE TABLE inside it, and INSERT INTO to add data.

2

Set the clone database name variable

Declare a variable called clone_db_name and set it to the string 'sales_db_clone'.

Snowflake

CREATE DATABASE sales_db;
USE DATABASE sales_db;
CREATE TABLE orders (order_id INT, amount DECIMAL(10,2));
INSERT INTO orders VALUES (1, 100.00);

-- Declare clone_db_name variable
-- Your code here

Need a hint?

Use SET to declare a variable in Snowflake SQL.

3

Create the zero-copy clone database

Write a SQL command to create a clone database named using the variable clone_db_name from the existing sales_db database using zero-copy cloning.

Snowflake

CREATE DATABASE sales_db;
USE DATABASE sales_db;
CREATE TABLE orders (order_id INT, amount DECIMAL(10,2));
INSERT INTO orders VALUES (1, 100.00);

SET clone_db_name = 'sales_db_clone';

-- Create zero-copy clone database
-- Your code here

Need a hint?

Use CREATE DATABASE IDENTIFIER($clone_db_name) CLONE sales_db; to create the clone using the variable.

4

Verify the clone database exists

Write a SQL query to list all databases and check that sales_db_clone is present.

Snowflake

CREATE DATABASE sales_db;
USE DATABASE sales_db;
CREATE TABLE orders (order_id INT, amount DECIMAL(10,2));
INSERT INTO orders VALUES (1, 100.00);

SET clone_db_name = 'sales_db_clone';

CREATE DATABASE IDENTIFIER($clone_db_name) CLONE sales_db;

-- Query to list databases
-- Your code here

Need a hint?

Use SHOW DATABASES LIKE 'sales_db_clone'; to verify the clone exists.