Build a Cloud Data Warehouse

Load and query food truck data in Snowflake, then recover deleted data with Time Travel.

Introduction

30 Second Summary

Every time you browse a restaurant menu or order from a food truck, that data gets stored somewhere for analysis. Companies use cloud data warehouses to organize massive amounts of information and answer questions like "what's our most profitable item?" in seconds.

In this project, you will build a cloud data warehouse on Snowflake, load real food truck menu data, write analytical queries, and use Time Travel to recover data after a simulated disaster.

What You'll Build

A fully functional cloud data warehouse that loads food truck menu data from an external stage, runs analytical SQL queries against it, and demonstrates Snowflake's Time Travel capability by recovering accidentally deleted data.

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

  • A Snowflake trial account configured with a virtual warehouse for compute power.
  • A database and schema containing loaded Tasty Bytes food truck menu data.
  • Analytical SQL queries that calculate profit margins, average prices, and menu breakdowns.
  • A Time Travel recovery proving Snowflake's built-in data protection.
  • Secret Mission: Use zero-copy cloning to create a development copy of your production database instantly.

Are there any prerequisites?

Basic familiarity with SQL (SELECT, INSERT, CREATE TABLE) is helpful but not required. You do not need a Snowflake account beforehand. The project walks you through creating a free trial.

Sign Up for Snowflake and Explore Snowsight

Snowflake is entirely browser-based. There is nothing to install on your computer. You will create a free trial account, sign in to Snowflake's web interface, and verify everything is working.

By the end of this step, you will have a live Snowflake environment with a SQL worksheet open and ready for queries.

In this step, get ready to:
  • Create a free Snowflake trial account.
  • Sign in to Snowsight and explore the interface.
  • Open a SQL worksheet and verify your setup.
Create your free trial account

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

  • Open your browser and navigate to https://signup.snowflake.com.
  • Enter your first name, last name, email address, and company name (you can enter anything for company).
  • Select AWS as your cloud provider.
  • Choose the region closest to your physical location.
  • Select Enterprise as your Snowflake Edition.
  • Click SIGN UP.
  • Check your email inbox for the activation link from Snowflake.
  • Click the activation link and set your password.

Why Enterprise Edition?

Enterprise Edition gives you access to advanced features like Time Travel with up to 90 days of retention. You will use Time Travel later in this project to recover accidentally deleted data.

Your edition selection cannot be changed after signup, so choosing Enterprise now ensures you have everything you need.

Don't see the activation email?

Check your spam or junk folder. The email comes from Snowflake and may take a minute or two to arrive. If it still does not appear, try signing up again with a different email address.

help me find my Snowflake activation email

Sign in to Snowsight

Snowsight is Snowflake's browser-based interface where you write SQL, manage databases, and monitor your account.

  • Navigate to https://app.snowflake.com.
  • Enter your account identifier (from your activation email) and sign in with the password you just set.
  • After signing in, you will see the Snowsight homepage with your recently updated worksheets.

What about the MFA prompt?

Snowflake strongly encourages multi-factor authentication. You may see a prompt to configure MFA on your first sign-in. Dismiss it for now by clicking the close button. You can set it up later if you choose.

Open a SQL worksheet

A SQL worksheet is where you write and run all your SQL commands in Snowflake.

  • In the left navigation menu, select Projects.
  • Select Worksheets.
  • Click the + button in the top right to create a new SQL worksheet.

A blank worksheet opens. This is where you will write and run all your SQL for the rest of this project.

  • Verify your setup by typing the following SQL and clicking the Run button (or pressing Cmd+Enter on macOS, Ctrl+Enter on Windows):
SELECT CURRENT_VERSION();

What does this command do?

SELECT CURRENT_VERSION(); asks Snowflake to return its current software version. If you see a version number in the results panel below your worksheet, your account is working correctly.

You should see a version number (like 9.x.x) returned in the results panel below the worksheet.

Not seeing a result?

Make sure you clicked the Run button or pressed Cmd+Enter / Ctrl+Enter. Check that your cursor is on the same line as the SQL statement. If you see an error about no warehouse, that is okay for now. You will create one in the next step.

help me run my first SQL command in Snowsight

✔️ Awesome, I've got everything!

Great. Make sure your worksheet stays open. You will use it for all SQL commands in the next steps.

ⓧ I'd like to double check the full code

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

SELECT CURRENT_VERSION();

Your Snowflake account is live and your worksheet is ready. Next up, you will create a database and a virtual warehouse to store and process data.

Create Your Database and Virtual Warehouse

Your worksheet is verified and ready for action. Now it's time to build the two foundational objects every Snowflake project needs.

A database stores your tables and data. A virtual warehouse provides the compute power to load data and run queries.

In this step, get ready to:
  • Create a database and schema for your project data.
  • Spin up a virtual warehouse for compute power.
  • Verify both objects exist in your Snowflake account.
Create a database and schema

A database is the top-level container for all your tables. Inside it, a schema groups related tables logically.

  • In your worksheet, clear any existing code from Step 1 and create your database by running this SQL:
CREATE OR REPLACE DATABASE tasty_bytes_db;
USE DATABASE tasty_bytes_db;
USE SCHEMA public;

What does this code do?

  • CREATE OR REPLACE DATABASE tasty_bytes_db creates a new database called tasty_bytes_db. If one already exists with that name, it replaces it.
  • USE DATABASE tasty_bytes_db sets your session to use this database for all following commands.
  • USE SCHEMA public selects the default public schema inside your new database.
  • Select all three lines, then click the **Run** button (or press **Cmd+Return** on macOS, **Ctrl+Enter** on Windows).
  • You should see a success confirmation in the results panel below your worksheet for each statement.

Seeing an error?

  • Make sure each statement ends with a semicolon.
  • Check that you selected all three lines before clicking **Run**. If only one line is selected, only that statement executes.
  • If you see a permissions error, confirm you are using the ACCOUNTADMIN role (visible in the top-left of your worksheet).

Help me fix a permissions error when creating a database in Snowflake.

Create a virtual warehouse

A virtual warehouse is the compute engine that runs your queries. It only consumes credits while actively processing work.

  • Create an X-Small warehouse that auto-suspends after 60 seconds of inactivity by running this SQL:
CREATE OR REPLACE WAREHOUSE tasty_wh WITH
  WAREHOUSE_SIZE = 'X-SMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

USE WAREHOUSE tasty_wh;

What do these settings mean?

  • WAREHOUSE_SIZE = 'X-SMALL' is the smallest (and cheapest) compute size available.
  • AUTO_SUSPEND = 60 shuts the warehouse down after 60 seconds of inactivity. The default is 600 seconds, so this saves your free trial credits.
  • AUTO_RESUME = TRUE automatically starts the warehouse when you run a query, so you never have to manually wake it up.
  • INITIALLY_SUSPENDED = TRUE means the warehouse starts in a paused state and only consumes credits once you actually use it.

This combination ensures you only burn credits when actively running queries. Your trial includes $400 in credits, so this setup keeps costs near zero for this project.

  • Select all the warehouse SQL and click **Run** (or press **Cmd+Return** on macOS, **Ctrl+Enter** on Windows).
  • You should see a success confirmation for both the CREATE WAREHOUSE and USE WAREHOUSE statements.

Warehouse not created?

  • Double-check that WAREHOUSE_SIZE uses the value 'X-SMALL' (with quotes and a hyphen, not a space or underscore).
  • If you see a message about insufficient privileges, switch to the ACCOUNTADMIN role using the role selector in the top-left corner of your worksheet.

Help me troubleshoot why my warehouse creation failed in Snowflake.

Verify your objects exist
  • Confirm your database and warehouse were created by running these two commands:
SHOW DATABASES;
SHOW WAREHOUSES;
  • Select both lines and click **Run**.
  • In the results panel, you should see TASTY_BYTES_DB listed in the databases output and TASTY_WH listed in the warehouses output.

Why are the names uppercase?

Snowflake automatically converts unquoted identifiers to uppercase. You typed tasty_bytes_db but Snowflake stores it as TASTY_BYTES_DB. Both forms work in your SQL queries.

Don't see your objects?

  • Run each SHOW command individually by placing your cursor on the line and clicking **Run**. Snowflake only displays results for the last statement when running multiple at once.
  • If you see many databases (Snowflake creates some defaults), scroll or search the results table for TASTY_BYTES_DB.

Help me find my database and warehouse in Snowflake's SHOW command results.

✔️ Awesome, I've got everything!

Great. Double check that your worksheet contains all the SQL from this step, then move on.

ⓧ I'd like to double check the full code

Here is the complete SQL you should have in your worksheet at this point:

----------------------------------------------
-- Step 2: Create Your Database and Virtual Warehouse
----------------------------------------------

CREATE OR REPLACE DATABASE tasty_bytes_db;
USE DATABASE tasty_bytes_db;
USE SCHEMA public;

CREATE OR REPLACE WAREHOUSE tasty_wh WITH
  WAREHOUSE_SIZE = 'X-SMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

USE WAREHOUSE tasty_wh;

Your database and warehouse are ready. Next up, you'll load real data into your warehouse from an external stage.

Load Data from an External Stage

You have a database and a virtual warehouse ready to go. Now it is time to fill your warehouse with real data.

In this step, you will create a table, point Snowflake at an external data source using a stage, and load CSV data into your table using the COPY INTO command.

In this step, get ready to:
  • Create a table to hold food truck menu data.
  • Create a stage pointing to an external S3 bucket.
  • Load and verify the data using COPY INTO.
Create a table for the menu data

Before you can load data, you need a table to receive it. This table defines the exact columns and data types that match the incoming CSV file.

  • Create the menu table by placing your cursor on the following SQL in your worksheet and clicking Run:
CREATE OR REPLACE TABLE menu (
  menu_id NUMBER,
  menu_type_id NUMBER,
  menu_type VARCHAR,
  truck_brand_name VARCHAR,
  menu_item_id NUMBER,
  menu_item_name VARCHAR,
  item_category VARCHAR,
  item_subcategory VARCHAR,
  cost_of_goods_usd NUMBER(38,2),
  sale_price_usd NUMBER(38,2),
  menu_item_health_metrics_obj VARIANT
);

What does this SQL do?

This creates an empty table called menu with 11 columns matching the Tasty Bytes food truck dataset.

  • NUMBER columns store IDs and numeric values like prices.
  • VARCHAR columns store text like menu item names and categories.
  • VARIANT stores semi-structured JSON data (health metrics for each menu item).
  • After running, you should see a success message in the results panel confirming the table was created.

Not seeing the success message?

  • Make sure you placed your cursor inside the CREATE OR REPLACE TABLE statement before clicking Run. Snowsight runs only the statement your cursor is on.
  • Check that you are using the TASTY_BYTES_DB database and PUBLIC schema from the previous step. If not, re-run your USE DATABASE and USE SCHEMA statements.

Help me debug my CREATE TABLE statement in Snowflake.

Create a stage and load the data

A stage in Snowflake is a pointer to where your data files live. Instead of uploading files manually, you will point Snowflake at a public S3 bucket that already contains the Tasty Bytes dataset.

  • Create the stage and load data into your table by running the following SQL:
CREATE OR REPLACE STAGE blob_stage
  URL = 's3://sfquickstarts/tastybytes/'
  FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1);

COPY INTO menu
FROM @blob_stage/raw_pos/menu/;

What does this SQL do?

CREATE STAGE creates a named reference called blob_stage pointing to Snowflake's public tutorial bucket on S3. The FILE_FORMAT option tells Snowflake the files are CSV and to skip the first header row.

COPY INTO then reads the CSV files from the @blob_stage/raw_pos/menu/ path and loads every row into your menu table. This is the primary command data engineers use to bulk-load data in Snowflake.

Why use a stage instead of uploading files?

Stages decouple storage from compute. Your data lives in cloud storage (S3, Azure Blob, or GCS), and Snowflake reads it on demand. This is how production data pipelines work. You never need to copy files onto Snowflake's servers manually.

  • Select both statements (the CREATE STAGE and the COPY INTO), then click Run.
  • The COPY INTO result will show a table with columns including file, status, rows_parsed, and rows_loaded. The status should show LOADED.

Seeing an error instead of LOADED?

  • If you see a warehouse-related error, confirm your warehouse is active by running USE WAREHOUSE tasty_wh; first. Your warehouse auto-suspends after 60 seconds of inactivity, but AUTO_RESUME = TRUE should restart it automatically.
  • If you see a stage-not-found error, make sure you ran the CREATE STAGE statement before the COPY INTO. You can select both and run them together.

Help me fix my COPY INTO data load in Snowflake.

Verify the load

Always confirm your data loaded correctly before moving on. A quick row count and preview will confirm everything landed in your table.

  • Verify your data by running these queries:
SELECT COUNT(*) AS row_count FROM menu;
SELECT TOP 10 * FROM menu;

What should I see?

The first query returns a single number showing the total rows loaded into your menu table. The second query shows the first 10 rows of data, including menu item names, prices, truck brands, and categories.

If you see rows of real food truck data (items like burgers, tacos, and ice cream with prices), your load was successful.

  • Run the SELECT COUNT(*) query first. You should see a row count greater than zero.
  • Run the SELECT TOP 10 query. You should see 10 rows of menu data with columns like menu_item_name and sale_price_usd populated.

Seeing 0 rows?

  • Go back and re-run your COPY INTO statement. If the result shows a failure status, check the first_error column for details on what went wrong.
  • Confirm you are querying the correct database. Run USE DATABASE tasty_bytes_db; and try the count again.

Help me figure out why my Snowflake table has no data.

✔️ Awesome, I've got everything!

Great. Double check you have run all the statements in this step before moving on.

ⓧ I'd like to double check the full code

Here is the complete SQL you should have in your worksheet so far:

----------------------------------------------
-- Step 2: Create Your Database and Virtual Warehouse
----------------------------------------------

CREATE OR REPLACE DATABASE tasty_bytes_db;
USE DATABASE tasty_bytes_db;
USE SCHEMA public;

CREATE OR REPLACE WAREHOUSE tasty_wh WITH
  WAREHOUSE_SIZE = 'X-SMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

USE WAREHOUSE tasty_wh;

----------------------------------------------
-- Step 3: Load Data from an External Stage
----------------------------------------------

CREATE OR REPLACE TABLE menu (
  menu_id NUMBER,
  menu_type_id NUMBER,
  menu_type VARCHAR,
  truck_brand_name VARCHAR,
  menu_item_id NUMBER,
  menu_item_name VARCHAR,
  item_category VARCHAR,
  item_subcategory VARCHAR,
  cost_of_goods_usd NUMBER(38,2),
  sale_price_usd NUMBER(38,2),
  menu_item_health_metrics_obj VARIANT
);

CREATE OR REPLACE STAGE blob_stage
  URL = 's3://sfquickstarts/tastybytes/'
  FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1);

COPY INTO menu
FROM @blob_stage/raw_pos/menu/;

SELECT COUNT(*) AS row_count FROM menu;
SELECT TOP 10 * FROM menu;

Your data warehouse now has real data loaded and ready for analysis. Next up, you will write analytical queries to explore this dataset and uncover insights about the Tasty Bytes menu.

Query Your Data Warehouse

Your data warehouse is loaded and ready for analysis. Now comes the fun part: asking questions of your data using SQL.

In this step, you will write analytical queries that answer real business questions about the Tasty Bytes food truck menu. You will find the most expensive items, calculate averages by category, and identify which items earn the highest profit margins.

In this step, get ready to:
  • Run queries to find the most expensive items and average prices.
  • Calculate profit margins to identify the most profitable menu items.
  • Explore what different food truck brands offer.
Find the most expensive menu items

Your first analytical query answers a simple question: which food truck menu items cost the most? This uses ORDER BY to sort results and LIMIT to return only the top results.

  • In your worksheet, place your cursor on a new line below your previous queries and paste the following SQL:
-- Top 5 most expensive menu items
SELECT menu_item_name, sale_price_usd
FROM menu
ORDER BY sale_price_usd DESC
LIMIT 5;

What does this query do?

  • SELECT menu_item_name, sale_price_usd pulls just the item name and price columns from the menu table.
  • ORDER BY sale_price_usd DESC sorts the results from highest price to lowest.
  • LIMIT 5 caps the output at five rows so you only see the top results.
  • Place your cursor inside this query and click Run in the top-right corner of the worksheet.
  • You should see a results table showing 5 menu items with their prices, sorted from highest to lowest.

Running individual queries

When your worksheet has multiple queries, place your cursor inside the specific query you want to run and click Run. Snowsight executes only the query your cursor is positioned in.

Not seeing results?

  • Make sure your cursor is placed inside the query you want to run, not on a blank line between queries.
  • Check that your warehouse is active. Run SELECT CURRENT_WAREHOUSE(); to confirm TASTY_WH is selected.

Help me figure out why my Snowflake query isn't returning results.

Calculate average prices by category

Averages give you a broader view of the data than individual rows. This query groups menu items by their category and calculates the average sale price for each.

  • Paste this query below your previous one and run it:
-- Average sale price by item category
SELECT item_category, ROUND(AVG(sale_price_usd), 2) AS avg_price
FROM menu
GROUP BY item_category
ORDER BY avg_price DESC;

What does this query do?

  • GROUP BY item_category groups all rows that share the same category together.
  • AVG(sale_price_usd) calculates the average price within each group.
  • ROUND(..., 2) rounds the result to 2 decimal places so prices look clean.
  • You should see a list of item categories with their average prices, sorted from most expensive to least expensive.
Calculate profit margins

Knowing the sale price is useful, but the real business insight is profit margin. This query calculates how much profit each item generates and expresses it as a percentage.

  • Paste this query below your previous ones and run it:
-- Profit margin analysis
SELECT
  menu_item_name,
  sale_price_usd,
  cost_of_goods_usd,
  ROUND(sale_price_usd - cost_of_goods_usd, 2) AS profit,
  ROUND((sale_price_usd - cost_of_goods_usd) / sale_price_usd * 100, 1) AS margin_pct
FROM menu
WHERE cost_of_goods_usd > 0
ORDER BY margin_pct DESC
LIMIT 10;

What does this query do?

  • sale_price_usd - cost_of_goods_usd calculates the raw profit in dollars for each item.
  • (sale_price_usd - cost_of_goods_usd) / sale_price_usd * 100 converts that profit into a percentage of the sale price.
  • WHERE cost_of_goods_usd > 0 filters out items with no cost data, which would cause division errors.
  • LIMIT 10 returns only the top 10 most profitable items.
  • You should see 10 menu items with their sale price, cost, dollar profit, and margin percentage, sorted from highest margin to lowest.

Seeing a division by zero error?

Make sure you included the WHERE cost_of_goods_usd > 0 clause. Without it, items with a zero cost value cause a division-by-zero error in the margin calculation.

Help me fix a division by zero error in my Snowflake profit margin query.

Filter by truck brand

The Tasty Bytes dataset covers multiple food truck brands. This final query shows how many menu items each brand offers.

  • Paste this query below your previous ones and run it:
-- Count of menu items per truck brand
SELECT truck_brand_name, COUNT(*) AS item_count
FROM menu
GROUP BY truck_brand_name
ORDER BY item_count DESC;

What does this query do?

  • COUNT(*) counts the total number of rows in each group.
  • GROUP BY truck_brand_name groups the rows so each truck brand gets its own count.
  • ORDER BY item_count DESC shows the brands with the most items first.
  • You should see a list of truck brand names with the number of menu items each one offers, sorted from most to fewest.

Getting an 'invalid identifier' error?

Double check that you typed truck_brand_name exactly as shown. Column names are case-insensitive in Snowflake, but typos like missing underscores will cause this error.

Help me debug an invalid identifier error in my Snowflake SQL query.

✔️ Awesome, I've got everything!

Great. Make sure you have saved your worksheet so all four queries are preserved.

ⓧ I'd like to double check the full code

Here is the full cumulative SQL you should have in your worksheet up to this point:

----------------------------------------------
-- Step 2: Create Your Database and Virtual Warehouse
----------------------------------------------

CREATE OR REPLACE DATABASE tasty_bytes_db;
USE DATABASE tasty_bytes_db;
USE SCHEMA public;

CREATE OR REPLACE WAREHOUSE tasty_wh WITH
  WAREHOUSE_SIZE = 'X-SMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

USE WAREHOUSE tasty_wh;

----------------------------------------------
-- Step 3: Load Data from an External Stage
----------------------------------------------

CREATE OR REPLACE TABLE menu (
  menu_id NUMBER,
  menu_type_id NUMBER,
  menu_type VARCHAR,
  truck_brand_name VARCHAR,
  menu_item_id NUMBER,
  menu_item_name VARCHAR,
  item_category VARCHAR,
  item_subcategory VARCHAR,
  cost_of_goods_usd NUMBER(38,2),
  sale_price_usd NUMBER(38,2),
  menu_item_health_metrics_obj VARIANT
);

CREATE OR REPLACE STAGE blob_stage
  URL = 's3://sfquickstarts/tastybytes/'
  FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1);

COPY INTO menu
FROM @blob_stage/raw_pos/menu/;

SELECT COUNT(*) AS row_count FROM menu;
SELECT TOP 10 * FROM menu;

----------------------------------------------
-- Step 4: Query Your Data Warehouse
----------------------------------------------

-- Top 5 most expensive menu items
SELECT menu_item_name, sale_price_usd
FROM menu
ORDER BY sale_price_usd DESC
LIMIT 5;

-- Average sale price by item category
SELECT item_category, ROUND(AVG(sale_price_usd), 2) AS avg_price
FROM menu
GROUP BY item_category
ORDER BY avg_price DESC;

-- Profit margin analysis
SELECT
  menu_item_name,
  sale_price_usd,
  cost_of_goods_usd,
  ROUND(sale_price_usd - cost_of_goods_usd, 2) AS profit,
  ROUND((sale_price_usd - cost_of_goods_usd) / sale_price_usd * 100, 1) AS margin_pct
FROM menu
WHERE cost_of_goods_usd > 0
ORDER BY margin_pct DESC
LIMIT 10;

-- Count of menu items per truck brand
SELECT truck_brand_name, COUNT(*) AS item_count
FROM menu
GROUP BY truck_brand_name
ORDER BY item_count DESC;

You have turned raw data into real business insights. Next up, you will test one of Snowflake's most powerful safety features: Time Travel, which lets you recover data even after deleting it.

Use Time Travel to Recover Data

In the previous step, you ran analytical queries against your loaded data. You proved your warehouse works for real analytics.

Now imagine you accidentally delete all your data. In a traditional database, that would be catastrophic. Snowflake's Time Travel feature lets you query and restore data as it existed at any point within a configurable retention window. In this step, you will simulate a disaster and recover from it in seconds.

In this step, get ready to:
  • Simulate an accidental data deletion.
  • Recover your data using Time Travel.
  • Verify the recovery with a final query.
Simulate a mistake

You are going to intentionally delete all rows from your menu table. In a real scenario, this kind of accidental deletion could take down an analytics pipeline. Here, you will see how Snowflake protects you.

  • In your SQL worksheet, run the following statements to check your row count, delete all data, and confirm the table is empty:
-- Check current row count
SELECT COUNT(*) FROM menu;

-- "Accidentally" delete everything
DELETE FROM menu;

-- Confirm the table is empty
SELECT COUNT(*) FROM menu;

What does this code do?

The first SELECT COUNT(*) confirms how many rows exist before the delete. This gives you a baseline to compare against after recovery.

DELETE FROM menu removes every row from the table without dropping the table itself. The second SELECT COUNT(*) proves the table is now empty.

  • Select all three statements and click the Run button (or press Ctrl+Enter on Windows / Cmd+Enter on macOS).
  • You should see a row count (your original data), then a deletion confirmation, then a count of 0.

Still seeing rows after the DELETE?

Make sure you ran all three statements in order. If you only ran the first SELECT COUNT(*), you are seeing the original count. Highlight all three statements and click Run again.

help me figure out why my DELETE statement did not execute

Recover with Time Travel

Your table is empty, but Snowflake still remembers what the data looked like before the delete. Time Travel lets you access historical data within a retention window of up to 90 days on Enterprise edition.

  • Recover your data by running these Time Travel statements:
-- Query the table as it was 5 minutes ago
SELECT COUNT(*) FROM menu AT (OFFSET => -300);

-- Restore the data
CREATE OR REPLACE TABLE menu AS
SELECT * FROM menu AT (OFFSET => -300);

-- Verify recovery
SELECT COUNT(*) FROM menu;

What does this code do?

AT (OFFSET => -300) tells Snowflake to look at the table as it existed 300 seconds (5 minutes) ago. The first query peeks at the historical row count to confirm the data is still there in Snowflake's history.

CREATE OR REPLACE TABLE menu AS SELECT * FROM menu AT (OFFSET => -300) recreates the table with all the data from that historical snapshot. The final SELECT COUNT(*) confirms the row count matches what you had before the delete.

  • Select all three statements and click Run.
  • The first result should show your original row count (proving the data still exists in history). The final result should show the same row count, confirming recovery is complete.

Run recovery within 5 minutes

The offset of -300 looks back 5 minutes. If more than 5 minutes passed between your DELETE and this recovery step, increase the offset value (for example, -600 for 10 minutes). As long as you are within the retention window, your data is safe.

Seeing zero rows from the Time Travel query?

If the AT (OFFSET => -300) query returns 0, too much time may have passed. Try increasing the offset to -600 or -900 to look further back.

help me recover my deleted Snowflake data with Time Travel

Celebrate your recovery

Your data is fully restored. Run one final query to confirm everything looks exactly as it did before the deletion.

  • Verify your recovery by running this query:
-- Final verification
SELECT TOP 5 menu_item_name, sale_price_usd FROM menu ORDER BY sale_price_usd DESC;

What does this code do?

This query returns the 5 most expensive menu items, sorted by price. If you see the same results as your earlier analytical queries from Step 4, your data is fully intact.

  • Click Run. You should see 5 rows with menu item names and their sale prices, confirming your data is back to normal.

Why is Time Travel a game-changer?

In a traditional database, recovering from an accidental DELETE would require restoring from a backup. That process can take hours and often loses recent changes.

Snowflake's Time Travel works because data is stored as immutable micro-partitions. When you delete rows, the old partitions are not physically removed. They remain accessible for the duration of your retention window.

✔️ Awesome, I've got everything!

Great. Double check you have all the Step 5 statements in your worksheet.

ⓧ I'd like to double check the full code

Here is the full SQL worksheet including everything from Steps 2 through 5:

----------------------------------------------
-- Step 2: Create Your Database and Virtual Warehouse
----------------------------------------------

CREATE OR REPLACE DATABASE tasty_bytes_db;
USE DATABASE tasty_bytes_db;
USE SCHEMA public;

CREATE OR REPLACE WAREHOUSE tasty_wh WITH
  WAREHOUSE_SIZE = 'X-SMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

USE WAREHOUSE tasty_wh;

----------------------------------------------
-- Step 3: Load Data from an External Stage
----------------------------------------------

CREATE OR REPLACE TABLE menu (
  menu_id NUMBER,
  menu_type_id NUMBER,
  menu_type VARCHAR,
  truck_brand_name VARCHAR,
  menu_item_id NUMBER,
  menu_item_name VARCHAR,
  item_category VARCHAR,
  item_subcategory VARCHAR,
  cost_of_goods_usd NUMBER(38,2),
  sale_price_usd NUMBER(38,2),
  menu_item_health_metrics_obj VARIANT
);

CREATE OR REPLACE STAGE blob_stage
  URL = 's3://sfquickstarts/tastybytes/'
  FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1);

COPY INTO menu
FROM @blob_stage/raw_pos/menu/;

SELECT COUNT(*) AS row_count FROM menu;
SELECT TOP 10 * FROM menu;

----------------------------------------------
-- Step 4: Query Your Data Warehouse
----------------------------------------------

-- Top 5 most expensive menu items
SELECT menu_item_name, sale_price_usd
FROM menu
ORDER BY sale_price_usd DESC
LIMIT 5;

-- Average sale price by item category
SELECT item_category, ROUND(AVG(sale_price_usd), 2) AS avg_price
FROM menu
GROUP BY item_category
ORDER BY avg_price DESC;

-- Profit margin analysis
SELECT
  menu_item_name,
  sale_price_usd,
  cost_of_goods_usd,
  ROUND(sale_price_usd - cost_of_goods_usd, 2) AS profit,
  ROUND((sale_price_usd - cost_of_goods_usd) / sale_price_usd * 100, 1) AS margin_pct
FROM menu
WHERE cost_of_goods_usd > 0
ORDER BY margin_pct DESC
LIMIT 10;

-- Count of menu items per truck brand
SELECT truck_brand_name, COUNT(*) AS item_count
FROM menu
GROUP BY truck_brand_name
ORDER BY item_count DESC;

----------------------------------------------
-- Step 5: Use Time Travel to Recover Data
----------------------------------------------

-- Check current row count
SELECT COUNT(*) FROM menu;

-- "Accidentally" delete everything
DELETE FROM menu;

-- Confirm the table is empty
SELECT COUNT(*) FROM menu;

-- Query the table as it was 5 minutes ago
SELECT COUNT(*) FROM menu AT (OFFSET => -300);

-- Restore the data
CREATE OR REPLACE TABLE menu AS
SELECT * FROM menu AT (OFFSET => -300);

-- Verify recovery
SELECT COUNT(*) FROM menu;

-- Final verification
SELECT TOP 5 menu_item_name, sale_price_usd FROM menu ORDER BY sale_price_usd DESC;

You just recovered from a total data wipeout in seconds. That is the power of a cloud-native data warehouse. Next up, try pushing your skills further with a Secret Mission that creates instant database copies using zero-copy cloning.

Secret mission

Zero-Copy Cloning

In production environments, teams need copies of their databases for development and testing. Traditional database copies take hours and double your storage costs. Snowflake's zero-copy cloning creates an instant copy without physically duplicating any data. Try it yourself.

Clean Up Your Resources

Clean Up Your Resources

Your Snowflake trial is free for 30 days (or until your $400 in credits run out). Your virtual warehouse auto-suspends after 60 seconds of inactivity, so it will not burn credits while idle.

Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you created:

  • Database: tasty_bytes_db (stores the menu table and stage).
  • Virtual Warehouse: tasty_wh (consumes credits only when running queries).

Keep everything running

No action needed. Choose this if you are still actively experimenting with Snowflake.

  • Your warehouse auto-suspends after 60 seconds of inactivity. No credits are consumed while it is suspended.
  • Your trial lasts 30 days from sign-up or until your $400 in credits are depleted, whichever comes first.
  • If you do nothing, your trial account will be automatically suspended after 30 days. You can log in but cannot run queries or load data after that point.

Pause - I'll come back to this later

Manually suspend your warehouse to guarantee zero credit consumption. Your data stays intact.

  • Return to your SQL worksheet in Snowsight and run the following command:
ALTER WAREHOUSE tasty_wh SUSPEND;
  • Your database and table remain available. When you come back, run ALTER WAREHOUSE tasty_wh RESUME; to start querying again.

Delete - I don't want to use this again

Remove all project resources to stop all consumption. Run these commands in your SQL worksheet.

DROP DATABASE IF EXISTS tasty_bytes_db;
DROP DATABASE IF EXISTS tasty_bytes_dev;
DROP WAREHOUSE IF EXISTS tasty_wh;
  • These commands permanently delete your database, any cloned databases, and the virtual warehouse.
  • Your Snowflake trial account will expire on its own after 30 days. Trial accounts cannot be cancelled through the web interface. If you want to cancel earlier, contact Snowflake Support.

Nice Work!

Nice Work!

You just built a cloud data warehouse from scratch on Snowflake. You loaded real data, ran analytical queries, and recovered from a simulated disaster using Time Travel.

You've learned how to:

  • Created a database, schema, and virtual warehouse on Snowflake to organize and power your analytics.
  • Loaded external data from an S3 stage into Snowflake using the COPY INTO command.
  • Wrote analytical SQL queries including aggregations, filters, and profit margin calculations against real food truck data.
  • Used Time Travel to recover accidentally deleted data, and created a zero-copy clone for a development environment.

Ready to quiz yourself?