Snowflake Database Cheatsheet

Snowflake is a cloud-based data warehousing platform that allows organizations to store and analyze large volumes of data. It is known for its scalability, performance, and ease of use. To help you navigate and make the most out of Snowflake, here’s a cheatsheet with essential commands and tips.

Connecting to Snowflake

Connect to Snowflake Account

-- Using Snowflake CLI
snowsql -a <account> -u <username> -r <role>

-- Using Snowflake JDBC
jdbc:snowflake://<account>.snowflakecomputing.com/?db=<database>&warehouse=<warehouse>&role=<role>&schema=<schema>

Switch Role

USE ROLE <role_name>;

Database and Schema Operations

Create Database

CREATE DATABASE IF NOT EXISTS <database_name>;

Use Database

USE DATABASE <database_name>;

Create Schema

CREATE SCHEMA IF NOT EXISTS <schema_name>;

Use Schema

USE SCHEMA <schema_name>;

Table Operations

Create Table

CREATE TABLE <table_name> (
  column1 datatype,
  column2 datatype,
  ...
);

Show Tables

SHOW TABLES;

Describe Table

DESCRIBE TABLE <table_name>;

Drop Table

DROP TABLE IF EXISTS <table_name>;

Data Loading and Unloading

Load Data from External Stage

COPY INTO <table_name> FROM @<external_stage>/<file>;

Unload Data to External Stage

COPY INTO @<external_stage>/<file> FROM <table_name>;

Querying Data

Select Statement

SELECT column1, column2 FROM <table_name> WHERE condition;

Join Tables

SELECT t1.column, t2.column
FROM <table1> t1
JOIN <table2> t2 ON t1.id = t2.id;

Aggregation

SELECT AVG(column), COUNT(*), MAX(column) FROM <table_name>;

Security

Grant Privileges

GRANT SELECT ON <table_name> TO ROLE <role_name>;

Revoke Privileges

REVOKE SELECT ON <table_name> FROM ROLE <role_name>;

Miscellaneous

Show Warehouses

SHOW WAREHOUSES;

Show Query History

SHOW QUERY_HISTORY;

Set Session Parameters

ALTER SESSION SET <parameter_name> = <value>;

This cheatsheet provides a quick reference for common Snowflake database operations. Remember to replace placeholders such as <database_name>, <table_name>, etc., with your actual database and table names. Keep this handy to streamline your Snowflake experience and enhance your productivity.

FAQ