Snowflake vs traditional data warehouses - Hands-On Comparison

Snowflake vs Traditional Data Warehouses

📖 Scenario: You work as a data engineer for a retail company. Your team currently uses a traditional data warehouse but is considering moving to Snowflake for better performance and scalability. You want to understand the differences by setting up simple examples in Snowflake and comparing them with traditional data warehouse concepts.

🎯 Goal: Build a simple Snowflake schema and query setup to demonstrate how Snowflake handles data storage and querying differently from traditional data warehouses.

📋 What You'll Learn

Create a Snowflake database and schema

Create a table with sample sales data

Set a configuration variable for warehouse size

Write a query to select total sales grouped by product

Add a clustering key to optimize query performance

💡 Why This Matters

🌍 Real World

Retail companies use data warehouses to analyze sales data and make business decisions. Snowflake offers a cloud-native approach that scales easily and separates compute from storage.

💼 Career

Data engineers and analysts need to understand Snowflake's architecture and SQL commands to build efficient data pipelines and queries.

1

Create a Snowflake database and schema

Write SQL commands to create a database called RetailDB and a schema called SalesSchema inside it.

Snowflake

-- Your code here

Need a hint?

Use CREATE DATABASE and CREATE SCHEMA commands with the exact names.

2

Create a sales table with sample data

Create a table called Sales in RetailDB.SalesSchema with columns ProductID (integer), ProductName (string), and SalesAmount (number). Insert these exact rows: (1, 'Shoes', 100), (2, 'Hats', 50), (3, 'Shirts', 75).

Snowflake

CREATE DATABASE RetailDB;
CREATE SCHEMA RetailDB.SalesSchema;
-- Your code here

Need a hint?

Use CREATE TABLE with the exact column names and types, then INSERT INTO with the exact rows.

3

Set warehouse size configuration

Set a variable called warehouse_size to 'XSMALL' to configure the Snowflake warehouse size for this project.

Snowflake

CREATE DATABASE RetailDB;
CREATE SCHEMA RetailDB.SalesSchema;
CREATE TABLE RetailDB.SalesSchema.Sales (
  ProductID INT,
  ProductName STRING,
  SalesAmount NUMBER
);
INSERT INTO RetailDB.SalesSchema.Sales (ProductID, ProductName, SalesAmount) VALUES
  (1, 'Shoes', 100),
  (2, 'Hats', 50),
  (3, 'Shirts', 75);
-- Your code here

Need a hint?

Use SET warehouse_size = 'XSMALL'; exactly.

4

Query total sales and add clustering key

Write a query to select ProductName and the sum of SalesAmount as TotalSales grouped by ProductName. Then alter the Sales table to add a clustering key on ProductName.

Snowflake

CREATE DATABASE RetailDB;
CREATE SCHEMA RetailDB.SalesSchema;
CREATE TABLE RetailDB.SalesSchema.Sales (
  ProductID INT,
  ProductName STRING,
  SalesAmount NUMBER
);
INSERT INTO RetailDB.SalesSchema.Sales (ProductID, ProductName, SalesAmount) VALUES
  (1, 'Shoes', 100),
  (2, 'Hats', 50),
  (3, 'Shirts', 75);
SET warehouse_size = 'XSMALL';
-- Your code here

Need a hint?

Use SELECT with SUM and GROUP BY, then ALTER TABLE ... CLUSTER BY with ProductName.