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

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

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

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

Hint

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

Practice

1. What is a key advantage of Snowflake compared to traditional data warehouses?

easy

A. It is cloud-based and easy to scale on demand

B. It requires physical hardware setup

C. It has fixed resource limits that cannot be changed

D. It needs manual software installation on servers

Solution

1. Step 1: Understand Snowflake's deployment model

   Snowflake is built on the cloud, so it does not require physical hardware or manual installations.

2. Step 2: Compare with traditional warehouses

   Traditional warehouses often need physical setup and fixed resources, limiting scalability.

3. Final Answer:

   It is cloud-based and easy to scale on demand -> Option A

4. Quick Check:

   Cloud-based and scalable [OK]

Hint: Cloud means easy scaling and no physical setup [OK]

Common Mistakes:

• Thinking Snowflake needs physical hardware
• Assuming resources are fixed in Snowflake
• Confusing manual installation with cloud services

2. Which of the following is the correct way to describe Snowflake's resource usage?

easy

A. Snowflake uses only on-premises servers for compute

B. Snowflake requires upfront purchase of fixed compute resources

C. Snowflake does not support scaling compute resources

D. Snowflake charges based on actual usage, scaling compute as needed

Solution

1. Step 1: Review Snowflake's billing model

   Snowflake charges customers based on the compute and storage they actually use, allowing flexible scaling.

2. Step 2: Contrast with fixed resource models

   Traditional warehouses often require buying fixed compute capacity upfront, unlike Snowflake.

3. Final Answer:

   Snowflake charges based on actual usage, scaling compute as needed -> Option D

4. Quick Check:

   Pay for what you use [OK]

Hint: Snowflake bills by usage, not fixed resources [OK]

Common Mistakes:

• Thinking Snowflake requires upfront fixed resource purchase
• Believing Snowflake cannot scale compute
• Assuming Snowflake uses only on-premises servers

3. Given the following scenario: A company runs a traditional data warehouse with fixed compute resources. They experience a sudden spike in data queries. What is the likely outcome compared to using Snowflake?

medium

A. The traditional warehouse will automatically scale compute to handle the spike

B. Snowflake can scale compute instantly to handle the spike, traditional cannot

C. Both systems will fail to handle the spike due to fixed resources

D. Traditional warehouses handle spikes better because of fixed resources

Solution

1. Step 1: Understand traditional warehouse limitations

   Traditional warehouses have fixed compute capacity and cannot scale instantly to spikes.

2. Step 2: Understand Snowflake's scaling ability

   Snowflake can quickly add compute resources on demand to handle spikes in queries.

3. Final Answer:

   Snowflake can scale compute instantly to handle the spike, traditional cannot -> Option B

4. Quick Check:

   Instant scaling = Snowflake advantage [OK]

Hint: Only Snowflake scales instantly for spikes [OK]

Common Mistakes:

• Assuming traditional warehouses auto-scale
• Thinking fixed resources handle spikes better
• Believing both systems fail equally

4. A company tries to reduce costs by running their traditional data warehouse 24/7 at full capacity. What is a key problem with this approach compared to Snowflake?

medium

A. They pay for unused compute during low demand times

B. Snowflake requires running 24/7 at full capacity too

C. Traditional warehouses automatically pause when idle

D. Snowflake cannot pause compute resources

Solution

1. Step 1: Analyze traditional warehouse cost model

   Traditional warehouses have fixed compute running constantly, so costs remain high even when idle.

2. Step 2: Compare with Snowflake's cost efficiency

   Snowflake can pause compute when not in use, saving costs during low demand.

3. Final Answer:

   They pay for unused compute during low demand times -> Option A

4. Quick Check:

   Fixed compute costs even when idle [OK]

Hint: Traditional pays always; Snowflake pauses to save [OK]

Common Mistakes:

• Thinking Snowflake must run 24/7
• Believing traditional warehouses pause automatically
• Assuming Snowflake cannot pause compute

5. A company wants to migrate from a traditional data warehouse to Snowflake. Which of the following best describes a benefit they will gain in terms of management and cost?

hard

A. They will need to manage physical hardware but save on software licenses

B. They must buy fixed compute capacity upfront but get better performance

C. They reduce management effort and pay only for the compute and storage they use

D. They lose flexibility but gain better control over physical resources

Solution

1. Step 1: Understand Snowflake's cloud benefits

   Snowflake removes the need to manage physical hardware and automates many management tasks.

2. Step 2: Understand Snowflake's cost model

   Snowflake charges based on actual compute and storage usage, avoiding upfront fixed costs.

3. Final Answer:

   They reduce management effort and pay only for the compute and storage they use -> Option C

4. Quick Check:

   Less management + pay-as-you-go [OK]

Hint: Cloud means less management and pay for usage [OK]

Common Mistakes:

• Thinking physical hardware management is still needed
• Assuming fixed upfront compute purchase
• Believing flexibility is lost after migration