Snowflake Architecture and SQL

Download Snowflake Class Database Script

Download Flat File for Data Load Exercise

For pricing and availability

Contact: Tom Coffing

Phone: 513 300-0341

Reference Guide: The book is an excellent book for teaching and makes for an excellent reference guide to help on the job.

Description: Students will learn Snowflake architecture and SQL starting at the most basic level and going to the most advanced level in this course.

Objectives: After this course, students will have detailed knowledge and understanding of the Snowflake architecture, creating and managing warehouses, tables, and objects, utilizing time travel and restoration features, performance tuning expertise, data ingestion, stored procedures, user-defined functions (UDFs), advanced analytics, and essential, intermediate, and advanced SQL hands-on training. Students will be ready to pass certification exams.

Audience: This course is for anyone who desires to learn Snowflake architecture and SQL, from beginners to experienced users.

Prerequisites: None

Duration: 3-5-Days

Topics:

Snowflake Virtual Warehouse Sizes
Create a Snowflake Virtual Data Warehouse
Scaling Up or Down the Virtual Data Warehouse
Creating a Table with a Clustering Key
Account Usage
Time Travel
Snowflake Caching
Zero-Copy Cloning
Creating a Transient Schema
Fail-Safe
Data Sharing
Creating a Reader Account
AUTOINCREMENT
Creating a Task
Aggregation
Joins
Date and Time Functions
Format Functions
Analytics and Window Functions
Temporary Tables
Subqueries
Strings
Interrogating the Data
Views
Set Operators
Creating Tables
Data Manipulation Language (DML)
User-Defined Functions (UDF)
Stored Procedures
Unstructured Data – JSON and XML
Statistical Aggregate Functions
Data Ingestion
Account Monitoring

Snowflake Architecture and SQL Course Outline

Chapter 1 - Getting Started with a Snowflake Trial Account

How to Create a Starter Account on Snowflake

Click on the START FOR FREE Menu Item

Start Your 30-Day Free Trial

Choose Your Edition of Snowflake and Cloud Provider

You're Now Signed Up – An Email Will Arrive Soon

Take the Snowflake Email and CLICK TO ACTIVATE

Click the Snowflake Link to Enter a Username and Password

Welcome to Snowflake Message Comes Up

Snowflake Browser and Query Tool

Snowflake Worksheet to Create and Run Queries

Script to Create the Database, Schema, Tables, and Views

Choose the COURSE OUTLINE Button for Snowflake

Press on Download Snowflake Class Database Script Button

Copy All of the Text From Your SnowflakeClass.Txt Script

Run Your Script on the Snowflake Website – First Way

Copy Your Script to the Snowflake Website – Alternative Way

Run Your Script on the Snowflake Website – Alternative Way

Your Systems Tree will Contain the Nexus Database

The SQL_CLASS Schema Contains Your Tables

Set Your Database to NEXUS and Schema to SQL_CLASS

Download Nexus at CoffingDW.com

Nexus Server Migrates All Data Warehouses to Snowflake

Nexus Joins Data Across Platforms with the Super Join Builder

YouTube Video of Nexus on Snowflake Look-and-Feel

Chapter 2 – What is Snowflake?

Snowflake IPO Largest in History

Advertising in the Computer Industry Pays off Each Year

Scaling Compute and Storage on Public Clouds

Customer Retention is Gold in the Computer Industry

High-powered Investors are Critical

Who are the Biggest Losers in the IPO?

Sharing Data Between Customers and Providers is Amazing

Standard SQL Makes Application Migration Easy

Hiring a Proven CEO can make the Difference

Snowflake Architecture

Snowflake Architecture – Cloud Services

Snowflake Architecture – Virtual Warehouse

Snowflake Architecture – Data Storage

Snowflake Tables are Immutable

Elasticity

Worker Nodes and Worker Processes

Min-Max Based Pruning

Snowflake Execution Engine

Fault Tolerance

Semi-Structured Data

Snowflake Editions

Snowflake Pricing

Snowflake Virtual Warehouse Sizes

Snowflake Storage Pricing

Chapter 3 – Configuring and Managing the Snowflake Warehouse

Five System Defined Snowflake Roles

Create a Snowflake Virtual Data Warehouse

Create a Role

Create a User and Grant a Role

Scaling Up or Down the Virtual Data Warehouse

Scaling Up the Virtual Warehouse

Scaling Out the Virtual Data Warehouse

Configuring a Warehouse

The Life of a Query

Resource Monitor

Create Resource Monitor Screen

Creating a Resource Monitor with SQL

Chapter 4 – Brilliant Features of Snowflake

Snowflake Caching

The Life of a Query With Caching (Option 1)

The Life of a Query With Caching (Option 2)

Time Travel Using OFFSET

Time Travel Using TIMESTAMP Part 1

Time Travel Using TIMESTAMP Part 2 - Troubleshooting

Time Travel Using TIMESTAMP Timezone CST

Time Travel Using TIMESTAMP With UTC Time

Time Travel Using QUERY_ID Part 1

Time Travel Using Query_ID Part 2

Restoring Data Using Time Travel Feature (Best Option)

Restoring Data Using Time Travel Feature (Bad Option)

CREATE TABLE with Time Travel Days

Account Usage

Drop and Undrop a Table

Drop and Undrop a Schema

Drop and Undrop a Database

Rename a Table

SHOW TABLES With LIKE

Table Types

TABLE STORAGE METRICS QUERY

Creating a Transient Schema

Zero-Copy Cloning Fundamentals

Zero-Copy Cloning

Zero-Copy Cloning with Time Travel

Zero-Copy Cloning Rules

Zero-Copy Cloning a Database and Schema

Cloning a Temporary Table

Creating Temporary and Transient Tables

Fail-Safe

Fail-Safe Begins when Time Travel Ends

Two Fail Safe Storage Metrics Query

Chapter 5 – Performance Tuning with Cluster Keys

What is Parallel Processing?

The Basics of a Single Computer

Data in Memory is Fast as Lightning

Parallel Processing Of Data

Snowflake has Linear Scalability

Snowflake Clustering For Performance Tuning

Creating a Table with a Cluster Key

Creating a Table with a Cluster Key Function

Creating a Table with a Multi-Cluster Key

Altering a Table to Create or Drop a Cluster Key

Joining Tables Can Have the Same Cluster Keys for Speed

The Emp_Tbl CREATE Statement with Eight Rows

Snowflake Quiz –Put the Rows on the Proper Micro-Partition

The First Row is Now on the Proper Micro-Partition

Place the Remaining Rows on the Proper Micro-Partition

The Rows Are Now on the Proper Micro-Partition

Snowflake Slices will use Columnar Storage

Snowflake Micro-Partitions Create Metadata for each Block

Quiz – How Many Blocks Move into Memory?

Answer –How Many Blocks Move into Memory?

Quiz – How Many Blocks Move into Memory?

Answer – How Many Blocks Move into Memory?

Quiz – How Many Blocks Move into Memory?

Answer – How Many Blocks Move into Memory?

Emp_Tbl CREATE Statement with a Cluster Key of DeptNo

Snowflake Quiz –Place the Rows on the Micro-Partition

The First Row is on the Proper Micro-Partition

Place the Remaining Rows on the Proper Micro-Partitions

The Rows are on the Proper Micro-Partitions

Snowflake will use Columnar Storage

Quiz – Can you Place the Metadata Correctly?

Answer – Can you Place the Metadata Correctly?

Quiz – How Many Blocks Move Into Memory?

Answer – How Many Blocks Move into Memory?

Quiz – How Many Blocks Move into Memory?

Answer – How Many Blocks Move into Memory?

Quiz – How Many Blocks Move into Memory?

Answer – How Many Blocks Move into Memory?

Each Block Comes With Metadata

Snowflake Architecture – Data Storage

Min-Max Based Pruning

Chapter 6 – Data Sharing

The Nexus Cache Tree Shows Objects Across All Systems

Data Sharing

Implementing Data Sharing

Investigating Data Sharing – SHOW SHARES

SEE SHARE DETAILS with the DESC Command

CREATE a Database for the SHARE

Sharing All Tables in a Database and Schema

Investigating Data Sharing – SHOW SHARES

SEE SHARE DETAILS with the DESC Command

Data Sharing for Non-Snowflake Account Users

Steps We Need to Take to Share with Non-Snowflake Users

Creating a Reader Account

Viewing Managed Accounts

Sharing Data with the Managed Account

Troubleshooting Sharing Data with the Managed Account

Gathering the URL for Logging Into a Reader Account

Using the URL to Login into your Snowflake Reader Account

SHOW SHARES for Reader Account

SEE SHARE DETAILS with the DESC Command

CREATE a Database and Warehouse for the SHARE

Create a User and Grant a Role for Reader Account Users

Chapter 7 – Snowflake Tasks

Creating a Task

Creating a Task Using CRON

Creating a Task Using CRON Using Minute and Hour

Tasks Using CRON Using Minute, Hour, and Day of Week

CRON Using Minute, Hour Range, and Day of Week Range

CRON with Minute, Multiple Hours, and Day of Week Range

CRON with Minute, Multiple Hours, and Last Day of Month

CRON with Minute, Multiple Hours, and Month

CRON Last Friday of the Month

CRON With America/Los Angeles Time

Creating a Tree of Tasks

Chapter 8 – Roles and Access Rights

Access Control Framework

Access Control Objects Hierarchy

Roles

Five System Defined Snowflake Roles

Roles Can Inherit Privileges

Current Role

Primary and Secondary Roles

Using the ACCOUNTADMIN Role

Precautions for ACCOUNTADMIN Role

Accessing Database Objects

Role Examples

CREATE ROLE Examples

Create Database and Schema Examples

Granting Rights to Roles Examples

Creating Users

Granting Rights to Roles Examples

Simplifying Grant Management Using Future Grants

Chapter 9 – Data Ingestion

Migrate Data to Snowflake at High Speeds with Nexus

Loading Data

What is a Staging Area?

Snowflake has Four Types of Stages

Snowflake Internal Stages

Data File Details

User Stage

Loading User Stage Data with the Snowflake Web Interface

Go to the Databases Tab to See Your Databases

Click on the Table You Want To Load

The Table Columns Appear – Choose Load Table

Load Data Screen Appears

Choose Your Source Files

Choose Your File Format

Create the File Format

Create the File Format – Press Load

Load Results

Table Stage

Download a Flat File From Our Website for Load Exercise

Choose the COURSE OUTLINE Button for Snowflake

Press on Download Snowflake Class Database Script Button

Copying Data Into a Table Using the Table Stage

The PUT Command will not work on Snowflake Browser

Query a Table Residing in the Table Stage

Using the Copy Command with File Format Options

Named Stages

Create Three Internal Named Stages Using SQL

Creating Three Internal Named Stages with Nexus

Using an Internal Named Stage to Load Data with PUT

Create Named Stage Using the Snowflake Browser Tool - 1

Create Named Stage Using the Snowflake Browser Tool - 2

Create Named Stage Using the Snowflake Browser Tool - 3

Create Named Stage Using the Snowflake Browser Tool - 4

Create Named Stage Using the Snowflake Browser Tool - 5

Create Named Stage Using the Snowflake Browser Tool - 6

Importing Data From External Stages

Example of Copy Command from an External Stage

Example of Copy Command Using Pattern

Pattern Results from Loading Multiple Files

Copying Only Some Columns Into a Table

Functions for Transforming Data During a Load

Transformation Example Using CASE and Implicit CAST

Transformation Example To Populate Only Two Columns

Using the ON_ERROR Options

ON_ERROR Defaults to ABORT_STATEMENT

ON_ERROR SKIP_FILE Option

ON_ERROR_<NUMBER> SKIP_FILE Option

ON_ERROR_<PERCENTAGE> SKIP_FILE Option

Creating and Describing a File Format (1 of 3)

Creating and Describing a File Format (2 of 3)

Creating and Describing a File Format (3 of 3)

Altering or Changing Attributes in a File Format

DESCRIBING our File Format to Confirm Attribute Changes

Important Copy Options – Validation Mode

Validation Mode RETURN_ERRORS Example

Validation Mode RETURN_n_ROWS Example of an Error

Validation Mode RETURN_n_ROWS Example of Success

Saving Load Error Rows in a Table

Another Technique to Save Load Error Rows in a Table

SPLIT_PART Function for Easy Reading of Errors

Important Copy Options – Size Limit

Example of Using the Size_Limit Option (1 of 3)

Example of Using the Size_Limit Option (2 of 3)

Example of Using the Size_Limit Option (3 of 3)

Important Copy Options – RETURNED_FAILED_ONLY

RETURNED_FAILED_ONLY Example

Important Copy Options – TRUNCATECOLUMNS

TRUNCATECOLUMNS Example (1 of 3)

TRUNCATECOLUMNS Example (2 of 3)

TRUNCATECOLUMNS Example (3 of 3)

Important Copy Options – FORCE

FORCE Example

An Example of a JSON File

Creating a JSON Stage and File Format

Querying the JSON Table

Tricks and Tips to Query the JSON Table

Tricks and Tips to Query Nested Data in a JSON Table

Tricks and Tips to Query Arrays in a JSON Table

Tricks and Tips to Flatten Arrays in a JSON Table

Creating a Table with Flattened Data

LOAD_HISTORY

LOAD_HISTORY Results

LOAD_HISTORY For Loads Happening Before Today

STAGES View in Information_Schema

Nexus Server Migrates All Data Warehouses to Snowflake

Chapter 10 – Information_Schema System Catalog

APPLICABLE_ROLES

COLUMNS

COLUMNS EXAMPLE

DATABASES

ENABLED_ROLES

EXTERNAL_TABLES

FILE_FORMATS

FUNCTIONS

INFORMATION_SCHEMA_CATALOG_NAME

LOAD_HISTORY

OBJECT_PRIVILEGES

PACKAGES

PIPES

PROCEDURES

REFERENTIAL_CONSTRAINTS

REPLICATION_DATABASES

SCHEMATA

SEQUENCES

STAGES

TABLE_CONSTRAINTS

TABLE_PRIVILEGES

TABLE_STORAGE_METRICS

TABLES

USAGE_PRIVILEGES

VIEWS

Chapter 11 – Systems Functions

ALL_USER_NAMES and CURRENT_IP_ADDRESS

CURRENT_ACCOUNT, CURRENT_AVAILABLE_ROLES

CURRENT_CLIENT and CURRENT_DATE

CURRENT_DATABASE and CURRENT_SCHEMA

CURRENT_SESSION and CURRENT_STATEMENT

CURRENT_TIME and CURRENT_TIMESTAMP

CURRENT_VERSION and CURRENT_WAREHOUSE

CURRENT_TRANSACTION and CURRENT_USER

CURRENT_REGION and CURRENT_ROLE

Chapter 12 – Introduction to SQL

Introduction

SELECTING Current Information

Setting Your Default DATABASE and SCHEMA

SELECT * (All Columns) in a Table

SELECT Specific Columns in a Table

Commas in the Front or Back?

Place your Commas in front for better Debugging Capabilities

Sort the Data with the ORDER BY Keyword

Use a Column Name or Number in an ORDER BY Statement

Two Examples of ORDER BY using Different Techniques

Changing the ORDER BY to Descending Order

NULL Values Sort Last in Ascending Mode (Default)

Using the Nulls First Command

NULL Values Sort First in Descending Mode (DESC)

Using the Nulls Last Command

Major Sort vs. Minor Sort

Multiple Sort Keys using Names vs. Numbers

An Order By That Uses an Expression

Sorts are Alphabetical, NOT Logical

Using A Valued CASE Statement to Sort Logically

Using A Searched CASE Statement to Sort Logically

Quiz – Can you Add a Minor Sort?

Answer – Can you Add a Minor Sort?

Order By Decode

Quiz – Can you Add Two Minor Sorts Using Decode?

Answer – Can you Add Two Minor Sorts Using Decode?

How to ALIAS a Column Name

Using an Alias in the WHERE and ORDER BY Clause

Using an Alias in the ORDER BY Clause with Decode

A Missing Comma Can Become an Alias by Mistake

Comments using Double Dashes are Single Line Comments

Comments for Multi-Lines

Comments for Multi-Lines As Double Dashes Per Line

Comments are a Great Technique for Finding SQL Errors

Chapter 13 – The WHERE Clause

The WHERE Clause limits Returning Rows

Numbers Don't Need Single Quotes

Not Equal

Searching for NULL Values Using Equality Returns Nothing

Use IS NULL or IS NOT NULL when dealing with NULLs

Using Greater Than Or Equal To (>=)

AND in the WHERE Clause

Troubleshooting AND

OR in the WHERE Clause

Troubleshooting OR

WHY OR must utilize the Column Name Each Time

Troubleshooting Character Data

Using Different Columns in an AND Statement

Quiz – How Many Rows Will Return?

Answer to Quiz – How Many Rows Will Return?

What is the Order of Precedence?

Using Parentheses to change the Order of Precedence

Using an IN List in place of OR

The IN List is an Excellent Technique

IN List vs. OR Brings the Same Results

The IN List Can Use Character Data

Using a NOT IN List

Null Values in a NOT IN List Return No Rows

A Technique for Handling Nulls with a NOT IN List

The BETWEEN Statement is Inclusive

The NOT BETWEEN Statement is also Inclusive

The BETWEEN Statement Works for Character Data

The Like Command Wildcards are Percent and Underscore

LIKE command Underscore is Wildcard for one Character

CASE Matters with the LIKE Command

Using LIKE for all Cases with Lower and Upper

Another Example of UPPER and LOWER

LIKE Command to Find Multiple Characters

LIKE Command to Find Either Character

Using ILIKE Handle Case Issues

Finding Anyone Whose Name Ends in 'Y'

Escape Character in the LIKE Command changes Wildcards

Escape Characters Turn off Wildcards in the LIKE Command

Time Travel Using OFFSET

Time Travel Using TIMESTAMP Part 1

Time Travel Using TIMESTAMP Part 2

Time Travel Using TIMESTAMP Timezone CST

Time Travel Using TIMESTAMP With UTC Time

Time Travel Using QUERY_ID Part 1

Time Travel Using Query_ID Part 2

Chapter 14 – Distinct, Group By, Top, and Pivot

The Distinct Command

DISTINCT vs. GROUP BY

Quiz – How many rows come back from the Distinct?

Answer – How many rows come back from the Distinct?

Top Command

Top Command and Order By

Top Command and Order By Plus Nulls Last

The FETCH Clause

Sample and Tablesample

TOP vs. SAMPLE

Two Forms of Data Sampling

The Pivot Command

Extreme Pivot Challenge

Answer - Extreme Pivot Challenge

Alternate Answer - Extreme Pivot Challenge

Chapter 15 – Aggregation

Quiz – Calculate the Answer Set in your Mind

Answer – Calculate the Answer Set in your Mind

Quiz 2 – Calculate the Answer Set in your Mind

There are Five Aggregates

Quiz – How many Rows Return?

Answer – How many Rows Return?

Casting a Data Type

Troubleshooting Aggregates

GROUP BY Delivers One Row Per Group

GROUP BY DEPT_NO or GROUP BY 1 are Equivalent

Limiting Rows and Improving Performance with WHERE

WHERE Clause in Aggregation limits unneeded Calculations

Keyword HAVING tests Aggregates after they are Totaled

Keyword HAVING is like an Extra WHERE Clause for Totals

ANY_VALUE

COUNT_IF

GROUP BY GROUPING SETS

GROUP BY GROUPING SETS Super Query

GROUP BY ROLLUP

GROUP BY ROLLUP Super Query

GROUP BY CUBE

GROUP BY CUBE Answer Set

GROUP BY CUBE Super Query

Chapter 16 – Joining Tables

Nexus Builds Your Join SQL Automatically

A Two-Table Join Using Traditional Syntax

A Join using Traditional Syntax with Table Alias

You Can Fully Qualify All Columns

A Join using ANSI Syntax

Both Queries Have the Same Results and Performance

Quiz – Can You Finish the Join Syntax?

Answer to Quiz – Can You Finish the Join Syntax?

Quiz – Can You Find the Error?

Answer to Quiz – Can You Find the Error?

Super Quiz – Can You Find the Difficult Error?

Answer to Super Quiz – Can You Find the Difficult Error?

Quiz – Which Rows from Both Tables Won’t Return?

Answer– Which Rows from Both Tables Won’t Return?

LEFT OUTER JOIN

LEFT OUTER Join Results

RIGHT OUTER JOIN

RIGHT OUTER Join Example and Results

FULL OUTER JOIN

FULL OUTER Join Results

Which Tables are the Left and Which are Right?

Answer - Which Tables are the Left and Which are Right?

INNER JOIN with Additional AND Clause

ANSI INNER JOIN with Additional AND Clause

ANSI INNER JOIN with Additional WHERE Clause

OUTER JOIN with Additional WHERE Clause

OUTER JOIN with Additional AND Clause

The DREADED Product Join

The DREADED Product Join Results

Cartesian Product Join with Traditional Syntax

Cartesian Product Join with ANSI Syntax

The CROSS JOIN

The CROSS JOIN Answer Set

The SELF JOIN

The SELF JOIN with ANSI Syntax

An Associative Table is a Bridge that Joins Two Tables

Quiz – Can you Write the Three-Table Join?

Answer to Quiz – Can you Write the Three-Table Join?

Quiz –Write the Three-Table Join Using ANSI Syntax.

Answer –Write the Three-Table Join to ANSI Syntax.

Quiz – Can you Place the ON Clauses at the End?

Answer – Can you Place the ON Clauses at the End?

The Five-Table Join – Logical Insurance Model

Quiz - Write a Five Table Join Using ANSI Syntax

Answer - Write a Five Table Join Using ANSI Syntax

Quiz - Write a Five Table Join Using Traditional Syntax

Answer - Write a Five Table Join Using Non-ANSI Syntax

Quiz – Re-Write this putting the ON clauses at the END

Answer – Re-Write this putting the ON clauses at the END

Chapter 17 – Date Functions

CURRENT_DATE

CURRENT_DATE and CURRENT_TIMESTAMP

Current_Timestamp and Local_Timestamp With Precision

CURRENT_TIME vs. LOCALTIME With Precision

Add or Subtract Days from a Date

The ADD_MONTHS Command

ADD_MONTHS to Add a Year to a Date

ADD_MONTHS to Add Five Years to a Date

Incrementing Date Values Using the Dateadd Function

Incrementing Time Values Using the Dateadd Function

Dateadd Function And Add_Months Function are Different

Formatting a Date

The TO_CHAR Command to Format Dollar Signs

The TO_CHAR Command for Formatting Numbers

The EXTRACT Command

MONTHNAME

EXTRACT from DATES and TIME

EXTRACT from DATES and TIME Optional Syntax

Another Option for Extracting Portions of Dates and Times

The DATE_PART Function

Using DATE_PART to Extract

Implied Extract of Day, Month, and Year using TO_CHAR

The DATE_PART Function Using Day of Week (DOW)

Day of Week and a CASE Statement

Day of Week and DECODE

Great Date Functions to Know

Week of Year and Year of Week

First Day and Last Day Functions

DATEDIFF

Using CASE and Extract to Reformat Dates

Using CAST and SUBSTRING to Reformat Dates

The Date_Trunc Function

DATE_TRUNC Command With Time

DATE_TRUNC Command With Dates

LAST_DAY

NEXT_DAY

PREVIOUS_DAY

MONTHS_BETWEEN

TIME_SLICE

TO_TIMESTAMP

Using Day, Month, and Year intervals

The Basics of a Simple Interval

Chapter 18 – Temporary Tables

Nexus Joins Snowflake Tables with Excel Worksheets

Derived Query Examples with Three Different Techniques

Most Derived Tables Are Used To Join To Other Tables

The Three Components of a Derived Table

Visualize This Derived Table

Quiz - Derived Table Challenge

Answer to Quiz - Derived Table Challenge

An Example of Two Derived Tables Using WITH

WITH RECURSIVE Derived Table Hierarchy

WITH RECURSIVE Derived Table Query

WITH RECURSIVE Derived Table Definition

WITH RECURSIVE Derived Table Seeding

WITH RECURSIVE Derived Table Looping

WITH RECURSIVE Derived Table Looping in Slow Motion

WITH RECURSIVE Derived Table Looping Continued

WITH RECURSIVE Derived Table Ends the Looping

WITH RECURSIVE Derived Table Final SELECT

WITH RECURSIVE Results

Creating a Temporary Table

CREATE a Temporary TABLE using LIKE

Creating a Temporary Table using a CTAS

CREATE Temporary Table AS (CTAS) with Specific Columns

CREATE a Temporary Table AS (CTAS) Using a Join

Cloning a Temporary Table

Dropping a Temporary Table

Chapter 19 – Sub-query Functions

An IN List is much like a Subquery

An IN List Never has Duplicates – Just like a Subquery

An IN List Ignores Duplicates

The Subquery

The Three Steps of How a Basic Subquery Works

These are Equivalent Queries

The Answer Set from the Subquery

Quiz- Answer the Difficult Question

Answer to Quiz- Answer the Difficult Question

Should you use a Subquery or a Join?

Quiz - Write the Subquery

Answer to Quiz- Write the Subquery

Quiz - Write the More Difficult Subquery

Answer to Quiz - Write the More Difficult Subquery

Quiz – Write the Extreme Subquery

Answer To Quiz – Write the Extreme Subquery

Quiz - Write the Subquery with an Aggregate

Answer to Quiz- Write the Subquery with an Aggregate

Quiz- Write the Correlated Subquery

Answer to Quiz- Write the Correlated Subquery

The Basics of a Correlated Subquery

The Top Query always runs first in a Correlated Subquery

Correlated Subquery Example vs. a Join with a Derived Table

Quiz- A Second Chance To Write a Correlated Subquery

Answer - A Second Chance to Write a Correlated Subquery

Quiz- A Third Chance To Write a Correlated Subquery

Answer - A Third Chance to Write a Correlated Subquery

Quiz- Last Chance To Write a Correlated Subquery

Answer – Last Chance to Write a Correlated Subquery

Quiz – Write the Extreme Correlated Subquery

Answer To Quiz – Write the Extreme Correlated Subquery

NOT IN Subquery Returns Nothing when NULLs are Present

Fixing a NOT IN Subquery with Null Values

Quiz- Write the NOT IN Subquery

Answer to Quiz- Write the NOT Subquery

Quiz - Write the Subquery using a WHERE Clause

Answer - Write the Subquery using a WHERE Clause

Quiz - Write the Subquery with Two Parameters

Answer - Write the Subquery with Two Parameters

Quiz - Write the Two Parameter Subquery With an Aggregate

Answer - Two Parameter Subquery and an Aggregate

How the Double Parameter Subquery Works

More on how the Double Parameter Subquery Works

Quiz – Write the Triple Subquery

Answer to Quiz – Write the Triple Subquery

IN is equivalent to =ANY

Using a Correlated Exists

How a Correlated Exists Matches Up

The Correlated NOT Exists

Chapter 20 – Analytic and Window Functions

Nexus Gives You Snowflake Analytics for Free

ROW_NUMBER

Quiz – How did the Row_Number Reset?

Quiz – Return Only the last Two Days Per Product_ID

Answer – Return Only the last Two Days Per Product_ID

Quiz – Return Two Students Per Class_Code with Highest Grades

Answer – Return Two Students Per Class_Code with Highest Grades

Using a Derived Table

RANK

Dense_Rank

RANK vs. DENSE_RANK

Getting RANK to Sort in DESC Order

RANK() OVER, PARTITION BY, and QUALIFY

Using a Derived Table

DENSE_RANK() OVER and PARTITION BY

PERCENT_RANK() OVER with 14 rows in Calculation

PERCENT_RANK() OVER with 21 rows in Calculation

PERCENT_RANK and PARTITION BY

Cumulative Sum

Cumulative Sum – The Sort Explained

Cumulative Sum – Rows Unbounded Preceding Explained

Cumulative Sum – Making Sense of the Data

Cumulative Sum – Major and Minor Sort Keys

Reset with a PARTITION BY Statement

Totals and Subtotals through Partition By

Moving SUM every 3-rows vs. a Continuous Average

Partition By Resets the Calculations

Moving Average

The Moving Window is Current Row and Preceding n

How Moving Average Handles the Order By

Quiz – How is that Total Calculated?

Answer to Quiz – How is that Total Calculated?

Quiz – How is that 4th Row Calculated?

Answer to Quiz – How is that 4th Row Calculated?

Moving Average every 3-rows Vs. a Continuous Average

The Partition By Statement

Partition By Resets an ANSI OLAP

Moving Difference

Moving Difference with Partition By

Finding a Value of a Column in the Next Row with MIN

Finding a Next Row Value with MIN and PARTITION BY

Finding Multiple Values of a Column in Upcoming Rows

Finding The Next Date using MAX

COUNT OVER for a Sequential Number

COUNT OVER using ROWS UNBOUNDED PRECEDING

The MAX OVER Command

MAX OVER with PARTITION BY Reset

The MIN OVER Command

The MIN OVER Command with PARTITION BY

Finding Gaps Between Dates

CSUM For Each Product_ID For the First Three Days

FIRST_VALUE

Using FIRST_VALUE

FIRST_VALUE With Partitioning

Daily_Sales Minus FIRST_VALUE With Partitioning

FIRST_VALUE After Sorting by the Highest Value

FIRST_VALUE Combined with Row_Number and Qualify

FIRST_VALUE and Qualify with a Row_Number

FIRST_VALUE and a Derived Table

Last_Value Can Be Confusing

Last_Value Working Properly

Last_Value With Partitioning

Last_Value And First_Value with Partitioning

First and Last Value Difference

Using LEAD

Using LEAD with a PARTITION Statement

Using LEAD With an Offset of 2

Using LEAD With an Offset of 2 and a PARTITION

Using LAG

Using LAG with a PARTITION Statement

Using LAG With an Offset of 2

Using Two LAG Statements

Using LAG With an Offset of 2 and a PARTITION

CUME_DIST

CUME_DIST With a Tie Value

CUME_DIST With Qualify

CUME_DIST and a Derived Table

CUME_DIST and a Partition By Statement

CUME_DIST with 14 Rows

CUME_DIST With a Partition on 7 Rows

CURRENT ROW AND UNBOUNDED FOLLOWING

Different Windowing Options

How Ntile Works

Ntile Example

Ntile Continued

Ntile Percentile

Using Quantiles (Partitions of Four)

Using Deciles (Partitions of Ten)

NTILE With a Partition

MEDIAN Example

MEDIAN with Partitioning and a WHERE Clause

MEDIAN with Partitioning

PERCENTILE_CONT Function Description and Syntax

Result Information About PERCENTILE_CONT

PERCENTILE_CONT Function Arguments

PERCENTILE_CONT Example

PERCENTILE_CONT Example with Percentage Change

PERCENTILE_CONT With PARTITION Example

PERCENTILE_CONT With PARTITION and (0.4)

PERCENTILE_DISC Function Description and Syntax

PERCENTILE_DISC Function Arguments

PERCENTILE_DISC Example

PERCENTILE_DISC Example with Percentage Change

PERCENTILE_DISC With PARTITION Example

PERCENTILE_DISC With PARTITION and (0.4)

LISTAGG Basic Example

LISTAGG With a Pipe-Separated List

LISTAGG With a Comma-Separated List in Groups

NTH_VALUE Function and Syntax

NTH_VALUE Arguments

NTH_VALUE Function and Syntax

NTH_VALUE With Partition

NTH_VALUE With Partition and Ignore Nulls

RATIO_TO_REPORT Function

RATIO_TO_REPORT Example

ANY_VALUE

MODE

Width_Bucket

COUNT_IF

Chapter 21 – Strings

UPPER and lower Functions

The Length Command Counts Characters

LENGTH Does Not Work on Fixed Length Columns

OCTET_LENGTH

The TRIM Command Trims Leading and Trailing Spaces

The RTRIM and LTRIM Command Trims Spaces

Concatenation

Concat and Concat_WS for Concatenation

The SUBSTR and SUBSTRING Commands

How SUBSTR Works with NO ENDING POSITION

Using SUBSTR and CHAR_LENGTH Together

The POSITION Command finds a Letters Position

The POSITION Command is brilliant with SUBSTR

CHARINDEX Finds a Letter's Position in a String

CHARINDEX Command is Brilliant with SUBSTRING

The CHARINDEX Command Using a Literal

LPAD and RPAD

The REPLACE Function

REGEXP Example for Whitespace Character

REGEXP Example for Non-Whitespace

REGEXP Example for [xyz]

REGEXP Example Start of a String

REGEXP Example End of a String

REGEXP Example Matching Within a Range

REGEXP_REPLACE

REGEXP_REPLACE Example

Another REGEXP_REPLACE Example

REGEXP_INSTR

REGEXP_LIKE

RLIKE

REGEXP_SUBSTR

REGEXP_SUBSTR Example

SOUNDEX Function to Find a Sound

The REVERSE String Function

The RIGHT Function

The LEFT and RIGHT Functions

The ASCII Function

Chapter 22 – Interrogating the Data

Quiz – Fill in the Answers for the NULLIF Command

Answer – Fill in the Answers for the NULLIF Command

The COALESCE Command

COALESCE is Equivalent to this CASE Statement

COALESCE in a Real-World Example

Some Great CAST (Convert And Store) Examples

A Rounding Example Using CAST

CAST will Round Values Up or Down

Valued Case vs. Searched Case

Combining Searched Case and Valued Case

The DECODE Command

DECODE

A Trick for getting a Horizontal Case

Extreme CASE Challenge

Answer - Extreme CASE Challenge

Put a CASE in the ORDER BY

Using A Searched CASE Statement to Sort Logically

Quiz – Can you Add a Minor Sort?

Answer – Can you Add a Minor Sort?

Order By DECODE

Quiz – Can you Add Two Minor Sorts Using Decode?

CASE Challenge

Answer - CASE Challenge

Quiz – Advanced Case Challenge

Answer – Advanced Case Challenge

Chapter 23 – View Functions

The Fundamentals of Views

Creating a Simple View to Restrict Sensitive Columns

Creating a Simple View to Restrict Rows

Creating a View to Join Tables Together

Sometimes We Create Views for Formatting

Basic Rules for Views

How to Modify a View

Creating a Secure View to Restrict DDL Viewing

The Exception to the ORDER BY Rule inside a View

Another Exception to the ORDER BY Rule is TOP

Derived Columns in a View Must Have a Column Alias

The Standard Way Most Aliasing is Done

Another Way to Alias Columns in a View CREATE

What Happens When a View Column gets Aliased Twice?

Chapter 24 – Set Operators

Rules of Set Operators

INTERSECT Explained Logically

Answer - Intersect Explained Logically

Quiz - Union Explained Logically

Answer - Union Explained Logically

Quiz - Union ALL Explained Logically

Answer - Union ALL Explained Logically

Quiz - Except Explained Logically

Answer - Except Explained Logically

Quiz - Minus Explained Logically

Answer - MINUS Explained Logically

Quiz - Testing Your Knowledge

Answer - Testing Your Knowledge

Rule 1 - Equal Number of Columns in Both SELECT Lists

Rule 2 - Top Query Handles all Aliases

Rule 3 - The Bottom Query does the ORDER BY

Intersect Challenge

Answer - Intersect Challenge

Quiz - Set Operator Challenge

Answer – Set Operator Challenge

Quiz - Advanced Set Operator Challenge

Answer - Advanced Set Operator Challenge

UNION Vs. UNION ALL

Using UNION ALL and Literals

Using UNION ALL for Speed in Merging Data Sets

Great Trick: Place your Set Operator in a Derived Table

A Great Example of how EXCEPT and MINUS work

Using UNION to be same as GROUP BY GROUPING SETS

USING Multiple SET Operators in a Single Request

Changing the Order of Precedence with Parentheses

Chapter 25 – Creating Tables

Table Types

SHOW TABLES With LIKE

TABLE STORAGE METRICS QUERY

Snowflake Data Types

More Snowflake Data Types

Show Databases and Table DDL Commands

Finding Constraints

The Basics of Creating a Table

Creating a Table

Creating Temporary and Transient Tables

Snowflake Clustering

Creating a Table with a Cluster Key

Creating a Table with a Cluster Key Function

Creating a Table with a Multi-Cluster Key

Altering a Table to Create or Drop a Cluster Key

Joining Tables Can Have the Same Cluster Keys for Speed

Creating Tables with a Primary Key/Foreign Key Relationship

Primary Key Does Not Enforce Uniqueness

A Table with a NOT NULL Constraint

AUTOINCREMENT

AUTOINCREMENT START = 10 INCREMENT = 20

Restoring Data Using Time Travel Feature (Best Option)

Restoring Data Using Time Travel Feature (Bad Option)

Fail-Safe

Fail-Safe Begins when Time Travel Ends

CREATE TABLE with Time Travel Days

Create Table LIKE

CREATE a Temporary TABLE using LIKE

CREATE TABLE AS (CTAS) Populates the Table With Data

CREATE TABLE AS (CTAS) Can Choose Certain Columns

CREATE a Temporary Table (CTAS) with Specific Columns

CREATE a Temporary Table AS (CTAS) Using a Join

Create a Table IF NOT EXISTS

A Table with a NOT NULL Constraint

Create a Table with a Column Default Value

Creating a Transient Schema

Zero-Copy Cloning Fundamentals

Zero-Copy Cloning

Zero-Copy Cloning with Time Travel

Zero-Copy Cloning Rules

Zero-Copy Cloning a Database and Schema

Cloning a Temporary Table

CREATE TABLE AS (CTAS) Populates the Table With Data

CREATE TABLE AS (CTAS) Can Choose Specific Columns

CREATE Temporary Table AS (CTAS) with Specific Columns

CREATE a Temporary Table AS (CTAS) Using a Join

Chapter 26 – Inserts, Updates, and Deletes

INSERT Syntax # 1

INSERT Syntax # 2

INSERT Example with Multiple Rows

Inserting Null Values into a Table

INSERT/SELECT Command

INSERT/SELECT to Build a Data Mart

UPDATE Examples

Example of Subquery UPDATE Command

MERGE

Drop and Undrop a Table

Drop and Undrop a Schema

Drop and Undrop a Database

Rename a Table

Deleting Rows in a Table

Chapter 27 – Unstructured Data – JSON and XML

JSON and the Internet of Things

Why Use JSON?

JSON Types

JSON Syntax Explanation

Building a Complex Object

Continuing to Build a Complex Object

Creating a Table and Inserting JSON Data

Querying the JSON Table

Performing a Flatten to Display Rows Like a Typical Table

Creating a View on a Flattened Table

Further Building a Complex Object

Creating a Table and Inserting JSON Data

Querying the JSON Table

Creating a JSON Table

Inserting Two Objects Into a JSON Table

How to Query a JSON Variant Data Type

Inserting 10 Objects Into a JSON Table

JSON Technique to CREATE a Table and INSERT Data

How to Query JSON USING $1

How to Format JSON Data

Querying Portions of Semi-Structured Data

Querying Semi-Structured Data With a WHERE Clause

Using the FLATTEN Function to Parse Arrays

Using the FLATTEN Function to Parse Nested Arrays

Extracting Values by Path Using the GET_PATH Function

Creating an XML Table

Insert Into An XML Table

XMLGET and FLATTEN Function For XML Data

PARSE_JSON Semi-Structured Data Function

Describing a Table that Contains a Variant Data Type

Inserting a Wide Variety of Data Into a Variant Data Type

Using the TYPEOF Function

Using the STRIP_NULL_VALUE Function

Chapter 28 – User Defined Functions (UDFs)

User Defined Functions

CREATE Function Syntax

Two Simple Functions with Hard-Coded Values

You Can Use a Function in a Join

You Can Use a Function in WHERE Clause

You Can Use a Function in the ORDER BY Clause

Creating a function with an Input Argument

Create a Function to get Factorial Numbers

Creating a function to Get Fibonacci Numbers

Creating a function with the Keyword TABLE

Joining a Function with the Keyword TABLE

JavaScript UDF with a Try-Catch Block

Using a JavaScript UDF with a Try-Catch Block for Validation

JavaScript UDF with IF, ELSE IF, and ELSE Try Catch Block

DDL and DML Operations are Not Supported in Functions

An EXAMPLE of a CASE Statement Using Two Functions

Functions With Duplicate Names But Different Arguments

You ALTER a Function for Three Reasons

Examples of Altering a Function

SHOW User Functions

Information_Schema Functions

GET DDL Function

Conversion Functions and TRY_CAST

Semi-Structured Data Functions

Functions for Array and Object Creation and Manipulation

Functions for Semi-structured Extraction and Conversion

Functions for Semi-structured Extraction and Type Predicates

Chapter 29 – Stored Procedures

Why Use Snowflake Stored Procedures?

Stored Procedure Syntax Using Javascript

Create and Execute Your First Javascript Stored Procedure

Create and Execute an Argument Using Backticks

A Stored Procedure to INSERT Using Language SQL

SQL Stored Procedure INSERT Using Input Parameters

Creating a Snowflake Stored Procedure that Updates

Stored Procedure UPDATE Using Subquery

Stored Procedure Delete Using an Input Parameter

Snowflake Stored Procedure Control Structures

A Stored Procedure With IF and END IF Logic

A Stored Procedure With IF, ELSEIF, and ELSE Logic

A Stored Procedure With Simple Valued Case Logic

A Stored Procedure With Searched Case Logic

Using Loops in Stored Procedures

Using a WHILE Loop

Using a FOR Loop

Using a FOR Reverse Loop

Stored Procedure Workshop

Stored Procedure Workshop Answer Using Loop

Stored Procedure Workshop Answer Using WHILE

Stored Procedure Workshop Answer Using FOR Loop

Procedure Workshop Answer Using Reverse FOR Loop

Creating a Complex Javascript Stored Procedure

Inserting a Row Into a Table Using Javascript

Updating a Row Into a Table Using Javascript

Deleting a Row Into a Table Using Javascript

DDL that Applies to Stored Procedures

Describe Procedure

Procedure Definition from Information_Schema

Procedure Definition from GET_DDL

Procedure Definition from GET_DDL using TRUE Keyword

Alter Procedure

Renaming a Procedure

Dropping a Procedure

Snowflake Data Types you must Convert to Javascript

Stored Procedure to Get Methods from an Object

Stored Procedure Javascript Methods Part 1

Stored Procedure JavaScript Methods

Stored Procedure Javascript Methods Part 2

Stored Procedure to Get Methods from a Result Set Object

Stored Procedure Javascript Result Set Methods

Make Your Arguments in Upper Case

Best Practice – Make Your Arguments in Upper Case

How to Catch and Error using Try/Catch

Using a WHILE Loop

Creating a Stored Procedure to Get Fibonacci Numbers

Create a Stored Procedure to get Factorial Numbers

Using a WHILE Loop Example Result Set

Line Continuation for Long SQL Statements

Loading Rows From a Join Into a Table

Using Stored Procedures to Produce Dynamic SQL

Dynamic SQL Example to Truncate a Table

Dynamic SQL Store Procedure that Calls Another Procedure

Stored Procedures Privileges

Information_Schema for a Database and a Schema

A Stored Procedure to Show Privileges

Finding Details About Procedures

Granting and Revoking Usage of a Procedure to Another Role

Chapter 30 – Statistical Aggregate Functions

The Stats Table

The KURTOSIS Function

A KURTOSIS Example

The SKEW Function

SKEW Example

The STDDEV_POP Function

STDDEV_POP Example

The STDDEV_SAMP Function

A STDDEV_SAMP Example

The VAR_POP Function

A VAR_POP Example

The VAR_SAMP Function

A VAR_SAMP Example

The CORR Function

A CORR Example

Another CORR Example so you can Compare

The VARIANCE Function

A VARIANCE Example

The COVAR_POP Function

A COVAR_POP Example

Another COVAR_POP Example so you can Compare

The COVAR_SAMP Function

A COVAR_SAMP Example

Another COVAR_SAMP Example so you can Compare

The REGR_INTERCEPT Function

A REGR_INTERCEPT Example

Another REGR_INTERCEPT Example so you can Compare

The REGR_SLOPE Function

A REGR_SLOPE Example

Another REGR_SLOPE Example so you can Compare

The REGR_AVGX Function

A REGR_AVGX Example

Another REGR_AVGX Example so you can Compare

The REGR_AVGY Function

A REGR_AVGY Example

Another REGR_AVGY Example so you can Compare

The REGR_COUNT Function

A REGR_COUNT Example

The REGR_R2 Function

A REGR_R2 Example

The REGR_SXX Function

A REGR_SXX Example

The REGR_SXY Function

A REGR_SXY Example

The REGR_SYY Function

A REGR_SYY Example

Using GROUP BY

APPROX_COUNT_DISTINCT

Chapter 31 – Mathematical Functions

Numeric Manipulation Functions

ABS

ACOS

ACOSH

ASIN

ASINH

ATAN

ATAN2

ATANH

CBRT

Ceil

COS

COSH

COT

DEGREES

Numeric Function – DIV0 (Division Operator)

EXP

FACTORIAL

Floor

HAVERSINE

LOG

MOD

POW or POWER

RADIANS

ROUND

SIGN

SIN

SINH

SQRT

SQUARE

TAN

TANH

TRUNC or TRUNCATE

BACK TO BIG DATA CLASSES