Snowflake Architecture and SQL Training

For pricing and availability
Contact: Tom Coffing
Email: Tom.Coffing@CoffingDW.com
Phone: 513 300-0341

Description

You can expect three things from a Tom Coffing Snowflake Architecture and SQL class: hands-on, hands-on, and hands-on training. Students will begin each chapter covering the fundamentals and continue to step up to the most advanced techniques in a building block approach. Each student can create tables, do time travel, work with columnar and understand performance tuning techniques, run thousands of queries, and join many tables combined with subqueries, derived tables, date functions and formats, and advanced analytics. We can also customize the course based on your needs. Welcome to one of the best classes you will ever attend. Each student will have their own Snowflake system in which to work.

Reference Guide

The Snowflake Architecture and SQL book by Tom Coffing and David Cook is a 1146-page masterpiece of clear explanations into Snowflake tips and tricks and every SQL statement. The PDF delivered to each student will be the backbone of the class and provide an excellent reference guide for all work done on any Snowflake system.

Objectives

Whether you are new to Snowflake and SQL or already have enormous experience, Tom Coffing will enrich your knowledge immensely. One thing is for sure: you will be extremely confident and proficient at working with a Snowflake system and writing Snowflake SQL.

Audience, prerequisites, and duration

This course is for everyone who has the opportunity to work on a Snowflake system, from beginners to experts. There are never any prerequisites in a Tom Coffing classroom because Tom has mastered mixing new and experienced students within the same classroom. The course ranges from 2-5 days, depending on your needs, but the more days you choose, the more incredible the outcome will be, as we will customize the classes based on your needs. Most Snowflake classes have been a 5-day boot camp on everything Snowflake.

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 to 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_ SKIP_FILE Option
ON_ERROR_ 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
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
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 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?
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
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 with 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 on 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
LN
LOG
MOD
PI
POW or POWER
RADIANS
ROUND
SIGN
SIN
SINH
SQRT
SQUARE
TAN
TANH
TRUNC or TRUNCATE