Introduction to
Snowflake SQL
INTRODUCTION TO SNOWFLAKE SQL
George Boorman
Senior Curriculum Manager, DataCamp
Connecting to Snowflake
Snowsight: Snowflake Web Interface
INTRODUCTION TO SNOWFLAKE SQL
Worksheets
INTRODUCTION TO SNOWFLAKE SQL
Notebooks
INTRODUCTION TO SNOWFLAKE SQL
Connecting to Snowflake: Drivers
Drivers & Connectors
ODBC (Open Database Connectivity)
JDBC (Java Database Connectivity)
Connectors : Python, Spark, and more
1 https://docs.snowflake.com/en/developer-guide/drivers
INTRODUCTION TO SNOWFLAKE SQL
Connecting to Snowflake: Snowflake CLI
Snowflake CLI
Command-line client
Installed on Linux, Windows, or Mac
1 https://docs.snowflake.com/en/user-guide/snowsql
INTRODUCTION TO SNOWFLAKE SQL
SQL flavors
Snowflake uses Snowflake SQL
Other popular SQL flavors:
PostgreSQL
T-SQL
MySQL
Some differences in data types, functions, and general syntax
INTRODUCTION TO SNOWFLAKE SQL
Common syntax
SELECT
FROM
WHERE
GROUPBY
ORDER BY
AVG() , COUNT() , SUM() (etc)
DISTINCT
HAVING
JOIN *
*Some differences exist, more on this later!
INTRODUCTION TO SNOWFLAKE SQL
Let's practice!
INTRODUCTION TO SNOWFLAKE SQL
Snowflake SQL data
types
INTRODUCTION TO SNOWFLAKE SQL
George Boorman
Senior Curriculum Manager, DataCamp
Common data types
Category Data types
Text/string VARCHAR , CHAR , TEXT
INTRODUCTION TO SNOWFLAKE SQL
Common data types
Category Data types
Text/string VARCHAR , CHAR , TEXT
Numeric INTEGER
INTRODUCTION TO SNOWFLAKE SQL
Common data types
Category Data types
Text/string VARCHAR , CHAR , TEXT
Numeric INTEGER
Boolean BOOLEAN
INTRODUCTION TO SNOWFLAKE SQL
Common data types
Category Data types
Text/string VARCHAR , CHAR , TEXT
Numeric INTEGER
Boolean BOOLEAN
Date/time DATE , TIME , TIMESTAMP
1 https://docs.snowflake.com/en/sql-reference/intro-summary-data-types
INTRODUCTION TO SNOWFLAKE SQL
Snowflake SQL data types - NUMBER
NUMBER(p, s)
NUMERIC works in Snowflake as an alias for NUMBER
p = precision; s = scale
Max p and s values: 38
Exceeding will cause rounding!
INTRODUCTION TO SNOWFLAKE SQL
Snowflake SQL data types - TIMESTAMP_LTZ
TIMESTAMP_LTZ
Combines DATE and TIME with local
time zone
Format: YYYY-MM-DD HH:MI:SS
CREATE TABLE orders (
-- Timestamp with local time zone
order_timestamp TIMESTAMP_LTZ
)
INTRODUCTION TO SNOWFLAKE SQL
Data type conversion - What?
Converting data from one type to another
INTRODUCTION TO SNOWFLAKE SQL
Data type conversion - Why?
Improving performance
Data accuracy and consistency
Data quality
INTRODUCTION TO SNOWFLAKE SQL
Data type conversion - How?
1. CAST Syntax:
CAST( <source_data/column> AS <target_data_type> )
CAST('80' AS INT)
2. :: Syntax:
<source_data/column>::<target_data_type>
'80'::INT
INTRODUCTION TO SNOWFLAKE SQL
CAST
SELECT CAST(order_timestamp AS DATE)
AS order_date
FROM orders
INTRODUCTION TO SNOWFLAKE SQL
CAST results
INTRODUCTION TO SNOWFLAKE SQL
Conversion functions
Examples: TO_VARCHAR , TO_DATE , etc.
TO_VARCHAR
TO_VARCHAR( <expr> )
expr - numeric, timestamp, etc.
Result: VARCHAR
Example:
SELECT TO_VARCHAR(86)
Result:
86
INTRODUCTION TO SNOWFLAKE SQL
Checking data types
DESC TABLE orders
name type kind null? default primary key
ORDER_ID NUMBER(38,0) COLUMN N null Y
ORDER_DATE DATE COLUMN Y null N
ORDER_TIME TIME(9) COLUMN Y null N
INTRODUCTION TO SNOWFLAKE SQL
Let's practice!
INTRODUCTION TO SNOWFLAKE SQL
Functions, sorting,
and grouping
INTRODUCTION TO SNOWFLAKE SQL
George Boorman
Senior Curriculum Manager, DataCamp
String functions - INITCAP
Syntax: INITCAP( <expr> )
Capitalize each word in a string
SELECT INITCAP(category) AS capitalized_category
FROM pizza_type
INTRODUCTION TO SNOWFLAKE SQL
String functions - CONCAT
Combines the expressions Combining category with ' - Pizza'
Syntax: SELECT CONCAT(category, ' - Pizza')
AS pizza_category
CONCAT( <expr1> [ , <exprN> ... ] ) FROM pizza_type
Before Concat:
After Concat:
INTRODUCTION TO SNOWFLAKE SQL
DATE & TIME functions
CURRENT_DATE() or CURRENT_DATE
CURRENT_TIME() or CURRENT_TIME
SELECT CURRENT_DATE
SELECT CURRENT_TIME
INTRODUCTION TO SNOWFLAKE SQL
EXTRACT
Syntax
EXTRACT( <date_or_time_part> FROM <date_or_time_expr> )
<date_or_time_part> - year , month , day , weekday , etc.
SELECT EXTRACT(MONTH FROM order_date) AS order_month,
COUNT(*) AS num_orders
FROM orders
GROUP BY order_month
INTRODUCTION TO SNOWFLAKE SQL
SORTING and GROUPING
SORTING: ORDER BY
GROUPING: GROUP BY
Snowflake: GROUP BY ALL
INTRODUCTION TO SNOWFLAKE SQL
GROUP BY ALL
GROUP BY column1, column2 GROUP BY ALL
SELECT
pizza_type_id,
SELECT
size,
pizza_type_id,
AVG(price) AS average_price
size,
FROM
AVG(price) AS average_price
pizzas
FROM
GROUP BY
pizzas
pizza_type_id, -- explicit columns
GROUP BY ALL -- Don't specify columns
size
ORDER BY
ORDER BY
pizza_type_id, average_price DESC
pizza_type_id, average_price DESC
INTRODUCTION TO SNOWFLAKE SQL
Summary
Function/keyword Use
INITCAP() Capitalize each word in a string
CONCAT() Combine multiple strings
CURRENT_DATE Get the current date
CURRENT_TIME Get the current time
EXTRACT Pull a date/time element, e.g., month from a date
ORDER BY Sort query results
GROUP BY ALL Group query by all (non-aggregated) columns
INTRODUCTION TO SNOWFLAKE SQL
Let's practice!
INTRODUCTION TO SNOWFLAKE SQL
