What is Snowflake - CLI Guide

Introduction

Snowflake is a cloud data platform that helps you store and analyze large amounts of data easily. It solves the problem of managing data by separating storage and computing, so you can work faster and pay only for what you use.

When you want to store big data without managing hardware or software.

When you need to run fast queries on your data for reports or analysis.

When you want to share data securely with other teams or partners.

When you want to scale your data storage and computing power independently.

When you want to avoid complex setup and maintenance of traditional databases.

Commands

This command connects you to your Snowflake account using the SnowSQL command-line tool. You need to log in before running any queries or commands.

Terminal

snowsql -a myaccount -u myuser

Expected Output

Welcome to SnowSQL!

You are now connected to Snowflake as user 'myuser' on account 'myaccount'.

→

-a - Specifies the Snowflake account to connect to.

→

-u - Specifies the username for login.

This command lists all the compute warehouses available in your Snowflake account. Warehouses are where your queries run.

Terminal

SHOW WAREHOUSES;

Expected Output

name       | state  | size   | type     | ...

-----------|--------|--------|----------|---

COMPUTE_WH | ACTIVE | XSMALL | STANDARD | ...

This command creates a new warehouse named 'my_warehouse' with a small size. It will automatically pause after 60 seconds of inactivity and resume when needed, saving costs.

Terminal

CREATE WAREHOUSE my_warehouse WITH WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE;

Expected Output

Warehouse 'my_warehouse' created successfully.

This command sets 'my_warehouse' as the active warehouse for running queries.

Terminal

USE WAREHOUSE my_warehouse;

Expected Output

Using warehouse 'my_warehouse'.

This command shows the current version of Snowflake you are connected to.

Terminal

SELECT CURRENT_VERSION();

Expected Output

CURRENT_VERSION()

-----------------

6.40.0

Key Concept

If you remember nothing else from Snowflake, remember: it separates storage and computing so you can scale and pay for each independently.

Common Mistakes

Trying to run queries without selecting a warehouse first.

Queries need a warehouse to run; without selecting one, Snowflake will return an error.

Always run 'USE WAREHOUSE warehouse_name;' before running queries.

Creating warehouses without auto-suspend enabled.

Warehouses keep running and cost money even when not in use.

Set AUTO_SUSPEND to a reasonable time to save costs when idle.

Summary

Connect to Snowflake using SnowSQL with your account and username.

List and create warehouses to run your queries.

Set an active warehouse before running queries.

Snowflake separates storage and compute for flexible scaling and cost control.

Practice

1. What is Snowflake primarily used for?

easy

A. Managing network security

B. Creating mobile applications

C. Storing and analyzing data in the cloud

D. Designing websites

Solution

1. Step 1: Understand Snowflake's main purpose

   Snowflake is a cloud service designed to store and analyze data easily.

2. Step 2: Compare options with Snowflake's use

   Options B, C, and D relate to other fields like app development, security, and web design, not Snowflake.

3. Final Answer:

   Storing and analyzing data in the cloud -> Option C

4. Quick Check:

   Snowflake = Data storage and analysis [OK]

Hint: Snowflake = cloud data storage and analysis [OK]

Common Mistakes:

• Confusing Snowflake with app or web development tools
• Thinking Snowflake manages network security
• Assuming Snowflake is for designing websites

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

easy

A. Snowflake uses databases to hold data and warehouses to run queries

B. Snowflake uses virtual machines to host websites

C. Snowflake is a peer-to-peer file sharing system

D. Snowflake is a blockchain platform

Solution

1. Step 1: Identify Snowflake's architecture components

   Snowflake separates storage (databases) and compute (warehouses) for queries.

2. Step 2: Eliminate unrelated options

   Options B, C, and D describe unrelated technologies like web hosting, file sharing, and blockchain.

3. Final Answer:

   Snowflake uses databases to hold data and warehouses to run queries -> Option A

4. Quick Check:

   Architecture = Databases + Warehouses [OK]

Hint: Snowflake splits storage and compute: databases and warehouses [OK]

Common Mistakes:

• Mixing Snowflake with web hosting or blockchain
• Confusing compute with storage roles
• Thinking Snowflake is a file sharing system

3. Consider this Snowflake feature: You can increase or decrease resources anytime and pay only for what you use. What is this feature called?

medium

A. Fixed provisioning

B. Static allocation

C. Manual backup

D. Auto-scaling

Solution

1. Step 1: Understand resource scaling in Snowflake

   Snowflake allows dynamic adjustment of compute resources based on demand.

2. Step 2: Match feature to correct term

   Auto-scaling means resources adjust automatically; fixed provisioning and static allocation do not allow this flexibility.

3. Final Answer:

   Auto-scaling -> Option D

4. Quick Check:

   Dynamic resource adjustment = Auto-scaling [OK]

Hint: Dynamic resource changes = Auto-scaling [OK]

Common Mistakes:

• Confusing auto-scaling with manual backup
• Thinking fixed provisioning allows dynamic scaling
• Mixing static allocation with pay-as-you-go

4. You wrote a query in Snowflake but it runs very slowly. Which of these is a likely cause?

medium

A. The warehouse size is too small for the query workload

B. Snowflake does not support SQL queries

C. Data is stored on your local computer

D. Snowflake requires manual server restarts before queries

Solution

1. Step 1: Analyze query performance factors

   Warehouse size controls compute power; too small means slower queries.

2. Step 2: Check incorrect statements

   Snowflake supports SQL, stores data in cloud, and does not need manual restarts.

3. Final Answer:

   The warehouse size is too small for the query workload -> Option A

4. Quick Check:

   Small warehouse = slow queries [OK]

Hint: Small warehouse slows queries; increase size [OK]

Common Mistakes:

• Believing Snowflake lacks SQL support
• Thinking data is stored locally
• Assuming manual restarts are needed

5. You want to analyze a large dataset quickly but only pay for the time you use. How should you configure Snowflake to achieve this?

hard

A. Store data locally and run queries on your computer

B. Use a large warehouse and pause it when not running queries

C. Use a small warehouse and keep it running 24/7

D. Manually copy data to multiple warehouses

Solution

1. Step 1: Choose warehouse size for fast analysis

   A large warehouse provides more compute power for quick queries.

2. Step 2: Manage cost by pausing warehouse

   Pausing warehouse when idle stops billing, so you pay only for usage time.

3. Final Answer:

   Use a large warehouse and pause it when not running queries -> Option B

4. Quick Check:

   Large + pause = fast and cost-efficient [OK]

Hint: Big warehouse + pause = speed and pay only when used [OK]

Common Mistakes:

• Keeping small warehouse always running wastes time
• Storing data locally defeats cloud benefits
• Copying data manually is inefficient and costly