Build a Sales Analytics Warehouse

Load millions of rows into Snowflake and query real business data with SQL.

Introduction

30 Second Summary

Every company sits on mountains of sales data, but raw numbers don't answer business questions on their own. Data warehouses organize that information so teams can query it instantly and make real decisions.

In this project, you will build a sales analytics warehouse in Snowflake, load millions of rows of business data, and write SQL queries that answer questions like "which country generates the most revenue?" and "who are our top customers?"

What You'll Build

A cloud data warehouse in Snowflake powered by the TPC-H benchmark dataset, with tables for customers, orders, and line items.

By the end of this project, you'll have:

  • A Snowflake trial account with a configured virtual warehouse for compute.
  • A custom SALES_ANALYTICS database populated with millions of rows from the TPC-H dataset.
  • A library of SQL queries answering real business questions like revenue by nation, top customers by spend, and monthly order trends.
  • Hands-on experience with Time Travel and zero-copy cloning to recover from mistakes and create instant database copies.
  • Secret Mission: Connect to your Snowflake warehouse using Python to run analytics programmatically.

Are there any prerequisites?

None. This project includes full Snowflake account setup from scratch. All you need is a web browser and an email address to sign up for a free trial.

Set Up Your Snowflake Account

Every data warehouse needs two things: a place to store data, and compute power to run queries against it. Snowflake separates these completely, which means you can scale each one independently.

In this step, you will create a free Snowflake trial account, spin up a virtual warehouse for compute, and set your session context so every query runs with the right permissions.

In this step, get ready to:
  • Create a Snowflake trial account and log into Snowsight.
  • Create a virtual warehouse for compute.
  • Set your session context (role and warehouse).
Create your Snowflake trial account

Snowflake offers a 30-day free trial with $400 in credits. No payment information is required upfront.

  • Open your browser and navigate to https://signup.snowflake.com/.
  • Fill in your first name, last name, and email address.
  • Enter Learning in the **Company** field.
  • Enter Student in the **Job title** field.
  • Select **Enterprise** as your Snowflake edition.
  • Choose a cloud provider and region close to you (for example, **AWS** and **US East (Ohio)**).
  • Click **Get Started** to submit your registration.
  • Check your email for a verification link from Snowflake and click it to activate your account.
  • Set your password when prompted, then log into Snowsight at app.snowflake.com.

You should see the Snowsight home screen with a navigation menu on the left side.

What is Snowsight?

Snowsight is Snowflake's web-based interface. You will use it to write SQL, manage databases, and monitor your warehouse. Everything in this project happens inside Snowsight.

Not receiving the verification email?

Check your spam or junk folder. The email comes from no-reply@snowflake.com and can take a few minutes to arrive. If it still does not appear, try re-submitting the signup form with the same email.

Help me troubleshoot my Snowflake signup

Create a virtual warehouse for compute

In Snowflake, a virtual warehouse provides the compute power that runs your queries. It is not where data is stored. Think of it like renting a machine that turns on when you need it and shuts off when you do not.

  • In the left navigation menu, select **Projects**, then **Worksheets**.
  • Click the **+** button and select **SQL Worksheet** to open a new worksheet.

Seeing Workspaces instead of Worksheets?

Snowflake is transitioning to a newer editor called Workspaces. If you see **Workspaces** in your navigation instead of **Worksheets**, select **Projects**, then **Workspaces**, click the **+** button, and select **SQL File**. The editor works the same way for running SQL.

  • Create your virtual warehouse by pasting and running this SQL in your worksheet:
-- Create a virtual warehouse for compute
CREATE OR REPLACE WAREHOUSE ANALYTICS_WH
  WITH WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 120
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

What does this SQL do?

WAREHOUSE_SIZE = 'XSMALL' creates the smallest (cheapest) compute cluster. This is plenty for learning.

AUTO_SUSPEND = 120 automatically pauses the warehouse after 120 seconds of inactivity, so you stop burning credits when idle. AUTO_RESUME = TRUE restarts it automatically when you run your next query.

INITIALLY_SUSPENDED = TRUE means the warehouse starts paused. It will only consume credits once you run a query that needs compute.

  • Place your cursor anywhere in the SQL statement and click the **Run** button (top-right of the worksheet) to execute it.
  • Confirm the warehouse was created by running this command:
SHOW WAREHOUSES;
  • You should see ANALYTICS_WH in the results table with size XSMALL.

Don't see ANALYTICS_WH in the results?

Make sure you clicked **Run** after pasting the CREATE WAREHOUSE statement. If you pasted both statements together, only the last one executes by default. Highlight the CREATE statement first, then click **Run**, then highlight the SHOW statement and run it separately.

Help me troubleshoot my warehouse creation

Set your context for the session

Snowflake uses roles to control permissions and warehouses to provide compute. Setting your context tells Snowflake which role and warehouse to use for every query you run in this worksheet.

  • Set your role and warehouse for this session by running the following SQL:
USE ROLE ACCOUNTADMIN;
USE WAREHOUSE ANALYTICS_WH;

What does this do?

USE ROLE ACCOUNTADMIN sets your active role to ACCOUNTADMIN, which has full permissions on your trial account. This is fine for learning.

USE WAREHOUSE ANALYTICS_WH tells Snowflake to use the warehouse you just created for any compute-intensive queries.

  • Check the context selector at the top-right of your worksheet. It should display **ACCOUNTADMIN** as the role and **ANALYTICS_WH** as the warehouse.

Context selector shows a different role or warehouse?

Highlight both USE statements, then click **Run**. Both must execute successfully for the context to update. If the context selector still shows COMPUTE_WH or SYSADMIN, try clicking the selector directly and choosing ACCOUNTADMIN and ANALYTICS_WH from the dropdowns.

Help me fix my session context

✔️ Awesome, I've got everything!

Great. Make sure your worksheet is saved (Snowsight auto-saves, so you should be all set).

ⓧ I'd like to double check the full code

Here is everything you should have run in your SQL worksheet so far:

-- Step 1: Create warehouse
CREATE OR REPLACE WAREHOUSE ANALYTICS_WH
  WITH WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 120
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

USE ROLE ACCOUNTADMIN;
USE WAREHOUSE ANALYTICS_WH;

Your Snowflake account is ready and your compute warehouse is standing by. Next up, you will create your own database and load millions of rows of sales data into it.

Create Your Sales Database and Load Data

Your warehouse is ready to process queries. Now you need somewhere to store your data.

In Snowflake, data lives inside databases, and databases contain schemas that group related tables together. In this step, you will create a SALES_ANALYTICS database, add a SALES schema, and populate it with millions of rows from the TPC-H sample dataset.

In this step, get ready to:
  • Create a database and schema to organize your tables.
  • Load five tables from the TPC-H sample dataset.
  • Explore your new tables to confirm the data is ready.
Create the database and schema

Every Snowflake object (tables, views, stages) belongs to a database and a schema. Think of the database as a filing cabinet and the schema as a drawer inside it.

  • In your SQL Worksheet from the previous step, paste the following SQL and select all four lines.
  • Click the Run button at the top of the worksheet to execute each statement. Run them one at a time by placing your cursor in each statement and clicking Run.
-- Create your sales analytics database
CREATE OR REPLACE DATABASE SALES_ANALYTICS;
USE DATABASE SALES_ANALYTICS;

-- Create a schema to group your tables
CREATE OR REPLACE SCHEMA SALES;
USE SCHEMA SALES;

What does this SQL do?

CREATE OR REPLACE DATABASE SALES_ANALYTICS creates a brand new database. If one already exists with the same name, it replaces it.

USE DATABASE and USE SCHEMA set your session context so that all following SQL commands run inside that database and schema. You do not need to type the full path for every table you create.

  • Confirm your context is set correctly by running this query:
SELECT CURRENT_DATABASE(), CURRENT_SCHEMA();
  • You should see SALES_ANALYTICS and SALES in the results row.

Seeing NULL in the results?

Make sure you ran the USE DATABASE SALES_ANALYTICS; and USE SCHEMA SALES; statements before running the SELECT query. Each statement must end with a semicolon.

Also check that the context selector at the top of the worksheet shows ACCOUNTADMIN and ANALYTICS_WH. If not, run the USE ROLE ACCOUNTADMIN; and USE WAREHOUSE ANALYTICS_WH; statements from Step 1 again.

Still stuck? Help me troubleshoot my database context in Snowflake

Create tables and load data from the sample dataset

Snowflake trial accounts come with a pre-loaded read-only database called SNOWFLAKE_SAMPLE_DATA. It contains the TPC-H benchmark dataset, which simulates a real business with customers, orders, and line items.

Why not query the sample data directly?

The SNOWFLAKE_SAMPLE_DATA database is read-only. You cannot modify it, clone it, or use Time Travel on it. By copying the data into your own database, you own the tables and can use all of Snowflake's features on them.

  • Create five tables in your SALES schema by running the following SQL. Place your cursor in each CREATE OR REPLACE TABLE statement and click Run to execute them one at a time:
-- Copy customer data into your database
CREATE OR REPLACE TABLE CUSTOMERS AS
  SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER;

-- Copy order data
CREATE OR REPLACE TABLE ORDERS AS
  SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS;

-- Copy line item details (largest table)
CREATE OR REPLACE TABLE LINEITEM AS
  SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.LINEITEM;

-- Copy nation reference data
CREATE OR REPLACE TABLE NATION AS
  SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION;

-- Copy region reference data
CREATE OR REPLACE TABLE REGION AS
  SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.REGION;

What does this SQL do?

Each CREATE OR REPLACE TABLE ... AS SELECT * statement is called a CTAS (Create Table As Select). It creates a new table and populates it with every row from the source table in one operation.

The LINEITEM table is the largest with over 6 million rows. This might take 10-15 seconds to run. The smaller reference tables (NATION and REGION) finish almost instantly.

  • Confirm the data loaded successfully by running this count query on the largest table:
SELECT COUNT(*) FROM LINEITEM;
  • You should see approximately 6,001,215 rows in the results.

Seeing an error about SNOWFLAKE_SAMPLE_DATA?

If you see an error like "database does not exist", the sample data share might not be loaded in your account. This is rare for new trial accounts but can happen.

Try running SHOW DATABASES; and look for SNOWFLAKE_SAMPLE_DATA in the list. If it is missing, contact Snowflake support through the help menu in Snowsight.

Need more help? Help me fix the SNOWFLAKE_SAMPLE_DATA not found error

Explore your data structure
  • View all five tables in your schema by running:
SHOW TABLES IN SCHEMA SALES;
  • You should see all five tables listed: CUSTOMERS, ORDERS, LINEITEM, NATION, and REGION.
  • Now inspect the column structure of the ORDERS table by running:
DESCRIBE TABLE ORDERS;
  • You should see column definitions including O_ORDERKEY, O_CUSTKEY, O_ORDERSTATUS, O_TOTALPRICE, and O_ORDERDATE.

What do these columns mean?

O_ORDERKEY is the unique order ID. O_CUSTKEY links each order to a customer. O_TOTALPRICE is the dollar amount of the order.

These columns are what you will use in the next step to write queries that answer real business questions like "which nation generates the most revenue?"

✔️ Awesome, I've got everything!

Great. Make sure all five CTAS statements ran successfully before moving on.

ⓧ I'd like to double check the full code

Here is the complete SQL you should have run across Steps 1 and 2:

-- Step 1: Create warehouse
CREATE OR REPLACE WAREHOUSE ANALYTICS_WH
  WITH WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 120
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

USE ROLE ACCOUNTADMIN;
USE WAREHOUSE ANALYTICS_WH;

-- Step 2: Create database and load data
CREATE OR REPLACE DATABASE SALES_ANALYTICS;
USE DATABASE SALES_ANALYTICS;
CREATE OR REPLACE SCHEMA SALES;
USE SCHEMA SALES;

CREATE OR REPLACE TABLE CUSTOMERS AS
  SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER;

CREATE OR REPLACE TABLE ORDERS AS
  SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS;

CREATE OR REPLACE TABLE LINEITEM AS
  SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.LINEITEM;

CREATE OR REPLACE TABLE NATION AS
  SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION;

CREATE OR REPLACE TABLE REGION AS
  SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.REGION;

Your database is loaded with millions of rows of business data. Next up, you will write SQL queries that turn this raw data into answers to real business questions.

Query Your Data to Answer Business Questions

Your database is loaded with millions of rows of business data. Now comes the real value: turning raw data into answers.

In this step, you will write SQL queries that join multiple tables, aggregate results, and reveal patterns hidden in the data. These are the same types of queries data analysts run every day to drive business decisions.

In this step, get ready to:
  • Write a multi-table JOIN query to calculate total revenue by nation.
  • Filter and rank customers to find the top 10 by total spend.
  • Use date functions to spot monthly order trends over time.
Calculate total revenue by nation

The first business question is simple: which countries generate the most revenue? To answer it, you need data from three tables. The ORDERS table has order totals, the CUSTOMERS table links each order to a customer, and the NATION table maps each customer to a country.

  • In your SQL Worksheet in Snowsight, paste the following query:
-- Revenue by nation
SELECT
  n.N_NAME AS nation,
  SUM(o.O_TOTALPRICE) AS total_revenue,
  COUNT(o.O_ORDERKEY) AS total_orders
FROM ORDERS o
JOIN CUSTOMERS c ON o.O_CUSTKEY = c.C_CUSTKEY
JOIN NATION n ON c.C_NATIONKEY = n.N_NATIONKEY
GROUP BY n.N_NAME
ORDER BY total_revenue DESC;

What does this query do?

This query chains two JOINs together to connect order revenue to country names. ORDERS joins to CUSTOMERS via O_CUSTKEY, and CUSTOMERS joins to NATION via C_NATIONKEY.

The GROUP BY clause groups all rows by nation name, then SUM() and COUNT() calculate the total revenue and number of orders for each country.

  • Place your cursor inside the query and click the Run button at the top of the worksheet. You can also press Cmd+Return (macOS) or Ctrl+Enter (Windows).
  • You should see 25 rows in the results panel, one for each nation, sorted by total revenue from highest to lowest.

Seeing zero rows or an error?

  • Make sure your session context is set to the SALES_ANALYTICS database and SALES schema. Check the context selector at the top of the worksheet.
  • Confirm you highlighted only this query before clicking Run. If your worksheet has multiple queries, Snowsight runs only the one your cursor is inside.

Help me debug my revenue by nation query in Snowflake.

Find the top 10 customers by spend

Now that you can see revenue by country, the next question is: who are the biggest individual customers? This query ranks customers by their total order spend and limits results to the top 10.

  • Paste the following query into your worksheet:
-- Top 10 customers by spend
SELECT
  c.C_NAME AS customer_name,
  c.C_MKTSEGMENT AS market_segment,
  SUM(o.O_TOTALPRICE) AS total_spend,
  COUNT(o.O_ORDERKEY) AS order_count
FROM CUSTOMERS c
JOIN ORDERS o ON c.C_CUSTKEY = o.O_CUSTKEY
GROUP BY c.C_NAME, c.C_MKTSEGMENT
ORDER BY total_spend DESC
LIMIT 10;

What does this query do?

This query joins CUSTOMERS to ORDERS and groups the results by customer name and market segment. ORDER BY total_spend DESC sorts from highest spend to lowest.

The LIMIT 10 clause cuts the results to only the top 10 rows. Without it, you would see all 150,000 customers.

  • Place your cursor inside this query and click Run.
  • You should see exactly 10 rows showing the highest-spending customers along with their market segment and order count.

Seeing more than 10 rows?

  • Make sure you placed your cursor inside the top-10 query, not the previous revenue-by-nation query. Snowsight runs the query your cursor is currently inside.
  • Check that your query ends with LIMIT 10; on its own line.

Help me debug my top 10 customers query.

Spot monthly order trends with date functions

The final query answers a time-based question: how does order volume change month to month? Snowflake's DATE_TRUNC function truncates each order date to the first day of its month, letting you group orders into monthly buckets.

  • Paste the following query into your worksheet:
-- Monthly order volume
SELECT
  DATE_TRUNC('MONTH', O_ORDERDATE) AS order_month,
  COUNT(*) AS order_count,
  SUM(O_TOTALPRICE) AS monthly_revenue
FROM ORDERS
GROUP BY order_month
ORDER BY order_month;

What does this query do?

The DATE_TRUNC('MONTH', O_ORDERDATE) expression converts each order date (like 1995-03-15) into the first day of that month (1995-03-01). This creates a consistent grouping key.

Grouping by order_month then lets COUNT(*) and SUM(O_TOTALPRICE) give you the total order count and revenue for each calendar month. The ORDER BY order_month sorts results chronologically so you can see trends over time.

  • Place your cursor inside this query and click Run.
  • You should see monthly rows showing order volume trends across multiple years. Each row has a month, the number of orders placed that month, and the total revenue.

Query returns an error about order_month?

  • Check that the GROUP BY clause references the alias order_month, not the full expression. Snowflake allows grouping by column alias.
  • Make sure there are no typos in O_ORDERDATE. Column names in the TPC-H dataset use uppercase with underscores.

Help me fix my monthly order volume query.

✔️ Awesome, I've got everything!

Great. You now have three analytical queries that answer real business questions using JOINs, aggregation, and date functions.

ⓧ I'd like to double check the full code

Here is the cumulative SQL from this project so far, including all three business queries:

-- Step 1: Create warehouse
CREATE OR REPLACE WAREHOUSE ANALYTICS_WH
  WITH WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 120
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

USE ROLE ACCOUNTADMIN;
USE WAREHOUSE ANALYTICS_WH;

-- Step 2: Create database and load data
CREATE OR REPLACE DATABASE SALES_ANALYTICS;
USE DATABASE SALES_ANALYTICS;
CREATE OR REPLACE SCHEMA SALES;
USE SCHEMA SALES;

CREATE OR REPLACE TABLE CUSTOMERS AS
  SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER;

CREATE OR REPLACE TABLE ORDERS AS
  SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS;

CREATE OR REPLACE TABLE LINEITEM AS
  SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.LINEITEM;

CREATE OR REPLACE TABLE NATION AS
  SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION;

CREATE OR REPLACE TABLE REGION AS
  SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.REGION;

-- Step 3: Business queries

-- Revenue by nation
SELECT
  n.N_NAME AS nation,
  SUM(o.O_TOTALPRICE) AS total_revenue,
  COUNT(o.O_ORDERKEY) AS total_orders
FROM ORDERS o
JOIN CUSTOMERS c ON o.O_CUSTKEY = c.C_CUSTKEY
JOIN NATION n ON c.C_NATIONKEY = n.N_NATIONKEY
GROUP BY n.N_NAME
ORDER BY total_revenue DESC;

-- Top 10 customers by spend
SELECT
  c.C_NAME AS customer_name,
  c.C_MKTSEGMENT AS market_segment,
  SUM(o.O_TOTALPRICE) AS total_spend,
  COUNT(o.O_ORDERKEY) AS order_count
FROM CUSTOMERS c
JOIN ORDERS o ON c.C_CUSTKEY = o.O_CUSTKEY
GROUP BY c.C_NAME, c.C_MKTSEGMENT
ORDER BY total_spend DESC
LIMIT 10;

-- Monthly order volume
SELECT
  DATE_TRUNC('MONTH', O_ORDERDATE) AS order_month,
  COUNT(*) AS order_count,
  SUM(O_TOTALPRICE) AS monthly_revenue
FROM ORDERS
GROUP BY order_month
ORDER BY order_month;

Your analytical queries are working. Next up, you will explore Snowflake's Time Travel and zero-copy cloning features to recover from mistakes and create instant database copies.

Use Time Travel and Zero-Copy Cloning

Your SQL queries from the last step proved that your warehouse can answer real business questions. But what happens when someone accidentally deletes critical data?

In production, mistakes happen. Snowflake has two features that make recovery painless: Time Travel (query or restore historical data within a retention period) and zero-copy cloning (create instant copies of entire databases without duplicating storage).

In this step, get ready to:
  • Simulate an accidental data loss and recover it using Time Travel.
  • Use UNDROP to bring back a dropped table.
  • Create a zero-copy clone of your entire database in seconds.
Simulate a mistake and recover with Time Travel

Time Travel lets you query data as it existed at any point within the retention period (default 1 day). You will deliberately delete rows, then use the AT(OFFSET => -60) clause to look back 60 seconds and restore the table.

  • First, check your current CUSTOMERS row count by running this query in your SQL Worksheet:
-- Check count before delete
SELECT COUNT(*) FROM CUSTOMERS;

What does this query do?

This establishes a baseline. You will compare this number after the delete and after the recovery to confirm everything worked.

You should see approximately 150,000 rows in the results panel.

  • Now simulate an accidental delete and verify the damage by running these two statements:
-- Simulate accidental delete
DELETE FROM CUSTOMERS WHERE C_MKTSEGMENT = 'BUILDING';

-- Verify rows removed
SELECT COUNT(*) FROM CUSTOMERS;

What does this code do?

The DELETE statement removes all customers in the BUILDING market segment. The follow-up SELECT COUNT(*) confirms the row count dropped significantly.

In a real scenario, this kind of unfiltered delete is exactly how teams lose data. The row count should now be noticeably lower than your baseline.

  • Use Time Travel to peek at the data as it existed 60 seconds ago, then restore the table by running these statements:
-- Time Travel: see data 60 seconds ago
SELECT COUNT(*) FROM CUSTOMERS AT(OFFSET => -60);

-- Restore table
CREATE OR REPLACE TABLE CUSTOMERS AS
  SELECT * FROM CUSTOMERS AT(OFFSET => -60);

-- Verify restoration
SELECT COUNT(*) FROM CUSTOMERS;

What does this code do?

The first query uses AT(OFFSET => -60) to read the table as it was 60 seconds before the current time. The OFFSET parameter accepts a negative number of seconds.

The CREATE OR REPLACE TABLE ... AS SELECT statement rebuilds the entire CUSTOMERS table from that historical snapshot. The final count confirms the deleted rows are back.

The final SELECT COUNT(*) should match your original baseline of approximately 150,000 rows.

Count doesn't match your original?

If the OFFSET query returns fewer rows than expected, you may have waited longer than 60 seconds between running the DELETE and the Time Travel query. Try increasing the offset (for example, -120 for 2 minutes or -300 for 5 minutes) to reach a point before your DELETE ran.

Help me fix my Time Travel recovery. The row count after restoration doesn't match the original count.

Use UNDROP to recover a dropped table

Time Travel handles deleted rows. But what if someone drops an entire table? Snowflake keeps dropped objects available for recovery within the retention period using the UNDROP command.

  • Drop the NATION table and immediately recover it by running these statements:
-- UNDROP demo
DROP TABLE NATION;
UNDROP TABLE NATION;
SELECT COUNT(*) FROM NATION;

What does this code do?

DROP TABLE removes the NATION table from your schema. Snowflake does not physically delete it right away. It retains the table for the data retention period (default 24 hours).

UNDROP TABLE restores the most recent version of the dropped table in place. The final SELECT COUNT(*) confirms all 25 nation rows are back.

You should see 25 rows in the result, confirming the table is fully restored.

Seeing an error on UNDROP?

If you see an error that a table with the same name already exists, it means the DROP TABLE statement did not execute. Run DROP TABLE NATION; on its own first, then run UNDROP TABLE NATION; separately.

Help me fix my UNDROP TABLE command. I'm getting an error when trying to recover the NATION table.

Create a zero-copy clone of your database

Cloning in Snowflake creates an instant copy of a database, schema, or table without physically duplicating the underlying data. This is called a zero-copy clone because it uses metadata pointers instead of copying bytes.

  • Clone your entire SALES_ANALYTICS database and verify the clone by running these statements:
-- Zero-copy clone
CREATE DATABASE SALES_ANALYTICS_DEV CLONE SALES_ANALYTICS;
USE DATABASE SALES_ANALYTICS_DEV;
SELECT COUNT(*) FROM SALES.LINEITEM;

What does this code do?

CREATE DATABASE ... CLONE creates a full copy of SALES_ANALYTICS (all schemas, tables, and data) as a new database called SALES_ANALYTICS_DEV. The operation completes in seconds regardless of data size because no data is physically copied.

The USE DATABASE switches your session to the new clone. The final query checks that LINEITEM in the clone has the same row count as the original (approximately 6,001,215 rows).

You should see approximately 6,001,215 rows returned. The clone was created in seconds, not minutes, because no data was physically copied.

Why use zero-copy clones?

Data teams use clones to create safe development environments. You can experiment freely in SALES_ANALYTICS_DEV without any risk to your production data in SALES_ANALYTICS.

Because clones share the underlying storage until data diverges, they cost almost nothing to create. This makes them ideal for testing, prototyping, and CI/CD pipelines.

Clone taking a long time?

If the clone seems slow, your warehouse may have been suspended. Snowflake auto-resumes it, but the first query after resumption takes a few extra seconds. Wait for the result to appear.

Help me troubleshoot my zero-copy clone creation in Snowflake.

✔️ Awesome, I've got everything!

Great. Double check that your final SELECT COUNT(*) FROM SALES.LINEITEM; returned approximately 6,001,215 rows in the SALES_ANALYTICS_DEV database.

ⓧ I'd like to double check the full code

Here is the complete SQL for all steps up to and including this one:

-- Step 1: Create warehouse
CREATE OR REPLACE WAREHOUSE ANALYTICS_WH
  WITH WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 120
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

USE ROLE ACCOUNTADMIN;
USE WAREHOUSE ANALYTICS_WH;

-- Step 2: Create database and load data
CREATE OR REPLACE DATABASE SALES_ANALYTICS;
USE DATABASE SALES_ANALYTICS;
CREATE OR REPLACE SCHEMA SALES;
USE SCHEMA SALES;

CREATE OR REPLACE TABLE CUSTOMERS AS
  SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER;

CREATE OR REPLACE TABLE ORDERS AS
  SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS;

CREATE OR REPLACE TABLE LINEITEM AS
  SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.LINEITEM;

CREATE OR REPLACE TABLE NATION AS
  SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION;

CREATE OR REPLACE TABLE REGION AS
  SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.REGION;

-- Step 3: Business queries

-- Revenue by nation
SELECT
  n.N_NAME AS nation,
  SUM(o.O_TOTALPRICE) AS total_revenue,
  COUNT(o.O_ORDERKEY) AS total_orders
FROM ORDERS o
JOIN CUSTOMERS c ON o.O_CUSTKEY = c.C_CUSTKEY
JOIN NATION n ON c.C_NATIONKEY = n.N_NATIONKEY
GROUP BY n.N_NAME
ORDER BY total_revenue DESC;

-- Top 10 customers by spend
SELECT
  c.C_NAME AS customer_name,
  c.C_MKTSEGMENT AS market_segment,
  SUM(o.O_TOTALPRICE) AS total_spend,
  COUNT(o.O_ORDERKEY) AS order_count
FROM CUSTOMERS c
JOIN ORDERS o ON c.C_CUSTKEY = o.O_CUSTKEY
GROUP BY c.C_NAME, c.C_MKTSEGMENT
ORDER BY total_spend DESC
LIMIT 10;

-- Monthly order volume
SELECT
  DATE_TRUNC('MONTH', O_ORDERDATE) AS order_month,
  COUNT(*) AS order_count,
  SUM(O_TOTALPRICE) AS monthly_revenue
FROM ORDERS
GROUP BY order_month
ORDER BY order_month;

-- Step 4: Time Travel and Cloning

-- Check count before delete
SELECT COUNT(*) FROM CUSTOMERS;

-- Simulate accidental delete
DELETE FROM CUSTOMERS WHERE C_MKTSEGMENT = 'BUILDING';

-- Verify rows removed
SELECT COUNT(*) FROM CUSTOMERS;

-- Time Travel: see data 60 seconds ago
SELECT COUNT(*) FROM CUSTOMERS AT(OFFSET => -60);

-- Restore table
CREATE OR REPLACE TABLE CUSTOMERS AS
  SELECT * FROM CUSTOMERS AT(OFFSET => -60);

-- Verify restoration
SELECT COUNT(*) FROM CUSTOMERS;

-- UNDROP demo
DROP TABLE NATION;
UNDROP TABLE NATION;
SELECT COUNT(*) FROM NATION;

-- Zero-copy clone
CREATE DATABASE SALES_ANALYTICS_DEV CLONE SALES_ANALYTICS;
USE DATABASE SALES_ANALYTICS_DEV;
SELECT COUNT(*) FROM SALES.LINEITEM;

You have just recovered from data loss and created an instant database copy. These are features that come up frequently in data engineering interviews and real-world incidents. Next up, the Secret Mission challenges you to connect to this warehouse programmatically using Python.

Secret mission

Connect with Python and Snowpark

So far you have been running SQL directly in Snowsight. But in production, data engineers connect to Snowflake programmatically from Python scripts, pipelines, and notebooks. In this secret mission, you will install the Snowflake Python Connector, write a script that connects to your warehouse, and run analytics queries from your terminal.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. Your Snowflake trial has $400 in credits that expire after 30 days, so there is no ongoing cost after the trial ends.

Cost warning

Your XSMALL warehouse costs approximately 1 credit per hour when running. With AUTO_SUSPEND set to 120 seconds, it stops automatically when idle. You are unlikely to use more than a few credits during this project.

If you leave the warehouse running continuously (for example, by disabling auto-suspend), it could consume credits faster. The safest option is to leave AUTO_SUSPEND enabled.

Resources you used:

  • ANALYTICS_WH virtual warehouse in Snowflake (consumes credits when running).
  • SALES_ANALYTICS database with 5 tables (CUSTOMERS, ORDERS, LINEITEM, NATION, REGION).
  • SALES_ANALYTICS_DEV cloned database.
  • snowflake_analytics.py local Python file (Secret Mission).
  • snowflake-connector-python and pandas pip packages (Secret Mission).

Keep everything running

No action needed. Choose this if you are still actively exploring Snowflake or plan to build on this project soon.

  • Your trial remains active for 30 days from signup. After 30 days, the account automatically suspends and no further credits are consumed.
  • The ANALYTICS_WH warehouse auto-suspends after 2 minutes of inactivity, so it only uses credits while queries are running.
  • Your local snowflake_analytics.py file and pip packages remain installed on your machine.

Pause - I'll come back to this later

Shut down running processes to free up credits. Your data stays intact.

  • No Snowflake action needed. The warehouse suspends automatically after 2 minutes of inactivity. If no queries are running, it is already suspended.
  • To manually confirm the warehouse is suspended, run this in a SQL Worksheet:
  • Your databases, tables, and clones remain intact and consume storage credits (minimal for this dataset size).
ALTER WAREHOUSE ANALYTICS_WH SUSPEND;
  • Drop the cloned database to free storage if you do not need it:
DROP DATABASE SALES_ANALYTICS_DEV;

Delete - I don't want to use this again

Remove all project resources. Your Snowflake trial account will expire automatically after 30 days.

  • Drop the cloned database by running this in a SQL Worksheet:
DROP DATABASE SALES_ANALYTICS_DEV;
  • Drop the main project database:
DROP DATABASE SALES_ANALYTICS;
  • Drop the virtual warehouse:
DROP WAREHOUSE ANALYTICS_WH;
  • Delete your local Python file:
rm snowflake_analytics.py
  • Uninstall the pip packages:
pip uninstall snowflake-connector-python pandas -y
  • Your Snowflake trial account will automatically suspend after 30 days with no further action required. There is no self-service account deletion. If you want to close it earlier, contact Snowflake Support.

Nice Work!

Nice Work!

Nice work! You just built a fully functional sales analytics warehouse in Snowflake, loaded millions of rows of business data, and proved you can query it like a data professional.

You've learned how to:

  • Build a cloud data warehouse in Snowflake with a custom database and schema containing millions of rows of sales data from the TPC-H benchmark dataset.
  • Write SQL queries using JOINs, GROUP BY, aggregate functions, and date functions to answer real business questions like revenue by nation and top customers by spend.
  • Use Time Travel to recover from an accidental delete, restore a dropped table with UNDROP, and create a zero-copy clone of an entire database in seconds.
  • Secret Mission: Connected to Snowflake from Python using the Snowflake Connector and ran analytics programmatically.

Ready to quiz yourself?