Course Catalog  > Data & Databases  > Snowflake
TP3258

Snowflake SQL Training

In this course, students will learn the Snowflake architecture with an excellent level of detail. Students will also learn the clever tricks and tips that make Snowflake unique, including failsafe, time travel, and min-max pruning.
Request Information
Request On-Site or Customized Course Info
Course Details

Duration

2.5 days

Prerequisites

No prior knowledge is presumed.

Target Audience

Anyone who desires to learn Snowflake architecture and SQL, from beginners to experienced users.

Skills Gained

Students will have detailed knowledge and understanding of the Snowflake architecture, creating and managing tables and objects, utilizing time travel and restoration features, performance tuning expertise, and essential, intermediate, and advanced SQL hands-on training. Students will be prepared to pass certification exams.
Course Outline
  • 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
  • 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
  • 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
  • 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
  • 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
    • 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
    • Emp_Tbl CREATE Statement with a Cluster Key of DeptNo
    • 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
    • Each Block Comes With Metadata
    • Snowflake Architecture – Data Storage
    • Min-Max Based Pruning
  • 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
  • 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
  • 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
    • Creating Users
    • Simplifying Grant Management Using Future Grants
  • 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
    • Pattern Results from Loading Multiple Files
    • Copying Only Some Columns Into a Table
    • Functions for Transforming Data During a Load
    • 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
    • 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
    • Important Copy Options – RETURNED_FAILED_ONLY
    • Important Copy Options – TRUNCATECOLUMNS
    • Important Copy Options – FORCE
    • 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
  • Information_Schema System Catalog
    • APPLICABLE_ROLES
    • COLUMNS
    • 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
  • 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
  • Introduction to SQL
    • 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
    • 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
    • Order By 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
  • 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
    • 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
    • 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
    • Time Travel Using TIMESTAMP Timezone CST
    • Time Travel Using TIMESTAMP With UTC Time
    • Time Travel Using QUERY_ID
  • Distinct, Group By, Top, and Pivot
    • The Distinct Command
    • DISTINCT vs. GROUP BY
    • 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
  • Aggregation
    • There are Five Aggregates
    • 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
  • 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
    • LEFT OUTER JOIN
    • LEFT OUTER Join Results
    • RIGHT OUTER JOIN
    • FULL OUTER JOIN
    • FULL OUTER Join Results
    • 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
  • 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
  • Temporary Tables
    • Nexus Joins Snowflake Tables with Excel Worksheets
    • Most Derived Tables Are Used To Join To Other Tables
    • The Three Components of a Derived Table
    • Visualize This Derived Table
    • 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
  • 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
    • Should you use a Subquery or a Join?
    • The Basics of a Correlated Subquery
    • The Top Query always runs first in a Correlated Subquery
    • NOT IN Subquery Returns Nothing when NULLs are Present
    • Fixing a NOT IN Subquery with Null Values
    • How the Double Parameter Subquery Works
    • More on how the Double Parameter Subquery Works
    • IN is equivalent to =ANY
    • Using a Correlated Exists
    • How a Correlated Exists Matches Up
    • The Correlated NOT Exists
  • Analytic and Window Functions
    • Nexus Gives You Snowflake Analytics for Free
    • ROW_NUMBER
    • 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
    • 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 Continued
    • Ntile Percentile
    • Using Quantiles (Partitions of Four)
    • Using Deciles (Partitions of Ten)
    • NTILE With a Partition
    • 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 With PARTITION and (0.4)
    • PERCENTILE_DISC Function Description and Syntax
    • PERCENTILE_DISC Function Arguments
    • PERCENTILE_DISC With PARTITION and (0.4)
    • 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
    • ANY_VALUE
    • MODE
    • Width_Bucket
    • COUNT_IF
  • 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_REPLACE
    • REGEXP_INSTR
    • REGEXP_LIKE
    • RLIKE
    • REGEXP_SUBSTR
    • SOUNDEX Function to Find a Sound
    • The REVERSE String Function
    • The RIGHT Function
    • The LEFT and RIGHT Functions
    • The ASCII Function
  • Interrogating the Data
    • The COALESCE Command
    • COALESCE is Equivalent to this CASE Statement
    • 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
    • Put a CASE in the ORDER BY
    • Using A Searched CASE Statement to Sort Logically
    • Order By DECODE
  • 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?
  • Set Operators
    • Rules of Set Operators
    • INTERSECT Explained Logically
    • 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
    • UNION Vs. UNION ALL
    • Using UNION ALL and Literals
    • Using UNION ALL for Speed in Merging Data Sets
    • 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
  • 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
  • Inserts, Updates, and Deletes
    • INSERT Syntax
    • Inserting Null Values into a Table
    • INSERT/SELECT Command
    • INSERT/SELECT to Build a Data Mart
    • MERGE
    • Drop and Undrop a Table
    • Drop and Undrop a Schema
    • Drop and Undrop a Database
    • Rename a Table
    • Deleting Rows in a Table
  • 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
  • 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
    • Functions With Duplicate Names But Different Arguments
    • You ALTER a Function for Three Reasons
    • 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
  • 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
    • 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
    • Line Continuation for Long SQL Statements
    • Loading Rows From a Join Into a Table
    • Using Stored Procedures to Produce Dynamic SQL
    • 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
  • Statistical Aggregate Functions
    • The Stats Table
    • The KURTOSIS Function
    • The SKEW Function
    • The STDDEV_POP Function
    • The STDDEV_SAMP Function
    • The VAR_POP Function
    • The VAR_SAMP Function
    • The CORR Function
    • The VARIANCE Function
    • The COVAR_POP Function
    • The COVAR_SAMP Function
    • The REGR_INTERCEPT Function
    • The REGR_SLOPE Function
    • The REGR_AVGX Function
    • The REGR_AVGY Function
    • The REGR_COUNT Function
    • The REGR_R2 Function
    • The REGR_SXX Function
    • The REGR_SXY Function
    • The REGR_SYY Function
    • 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