Snowflake vs traditional data warehouses - CLI Comparison

Introduction

Traditional data warehouses store data on fixed hardware and can be slow to scale. Snowflake is a cloud data warehouse that separates storage and computing, making it faster and easier to scale without managing hardware.

When you want to quickly scale your data storage and processing without buying new servers.

When you need to run many users' queries at the same time without slowing down.

When you want to pay only for the computing power you use, not for idle servers.

When you want to store all your data in one place and access it easily from anywhere.

When you want automatic backups and security without manual setup.

Commands

Connect to Snowflake using the SnowSQL command line tool with your account, user, warehouse, database, and schema details.

Terminal

snowsql -a myaccount -u myuser -w mywarehouse -d mydatabase -s public

Expected Output

Welcome to Snowflake

You are now connected to Snowflake as user 'myuser'.

→

-a - Specifies the Snowflake account name

→

-u - Specifies the username

→

-w - Specifies the virtual warehouse to use

→

-d - Specifies the database to use

→

-s - Specifies the schema to use

Create a virtual warehouse in Snowflake that handles compute resources. It automatically suspends after 60 seconds of inactivity and resumes when needed.

Terminal

CREATE WAREHOUSE mywarehouse WITH WAREHOUSE_SIZE = 'SMALL' WAREHOUSE_TYPE = 'STANDARD' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE;

Expected Output

Statement executed successfully.

Create a simple table in Snowflake to store customer data.

Terminal

CREATE TABLE customers (id INT, name STRING, email STRING);

Expected Output

Statement executed successfully.

Insert a row of data into the customers table.

Terminal

INSERT INTO customers VALUES (1, 'Alice', '[e-mail]');

Expected Output

1 row inserted.

Query the customers table to see the data you inserted.

Terminal

SELECT * FROM customers;

Expected Output

ID | NAME  | EMAIL

1  | Alice | [e-mail]

Key Concept

Snowflake separates storage and compute so you can scale and pay for each independently, unlike traditional data warehouses.

Common Mistakes

Trying to scale compute and storage together as one unit.

Traditional warehouses tie storage and compute, so scaling one forces scaling the other, wasting resources.

Use Snowflake's separate scaling to adjust compute power without changing storage size.

Not setting AUTO_SUSPEND on warehouses.

Without auto suspend, warehouses keep running and cost money even when idle.

Set AUTO_SUSPEND to a low value like 60 seconds to save costs.

Using a single warehouse for all workloads causing slow queries.

One warehouse can get overloaded, slowing down all queries.

Create multiple warehouses for different workloads to isolate and speed up queries.

Summary

Snowflake uses virtual warehouses to separate compute from storage for flexible scaling.

You create warehouses and tables with simple SQL commands in Snowflake.

Auto suspend and resume features help save costs by stopping compute when not in use.