Objetivo

Após realizar Curso Snowflake Architecture and SQL, este você será capaz de:

• Entenda a ingestão de dados
• Implementar procedimentos armazenados e UDFs
• Domine habilidades avançadas de SQL
• Compartilhar dados
• Execute análises avançadas

Pre-Requisitos

• Os participantes tenham experiência anterior em SQL SELECT e que pelo menos os dois primeiros capítulos serão revisados. 

Materiais

Conteúdo Programatico

Getting Started with a Snowflake Trial Account

1. How to Create a Starter Account on Snowflake
2. Click on the START FOR FREE Menu Item
3. Start Your 30-Day Free Trial
4. Choose Your Edition of Snowflake and Cloud Provider
5. You're Now Signed Up – An Email Will Arrive Soon
6. Take the Snowflake Email and CLICK TO ACTIVATE
7. Click the Snowflake Link to Enter a Username and Password
8. Welcome to Snowflake Message Comes Up
9. Snowflake Browser and Query Tool
10. Snowflake Worksheet to Create and Run Queries
11. Script to Create the Database, Schema, Tables, and Views
12. Choose the COURSE OUTLINE Button for Snowflake
13. Press on Download Snowflake Class Database Script Button
14. Copy All of the Text From Your SnowflakeClass.Txt Script
15. Run Your Script on the Snowflake Website – First Way
16. Copy Your Script to the Snowflake Website – Alternative Way
17. Run Your Script on the Snowflake Website – Alternative Way
18. Your Systems Tree will Contain the Nexus Database
19. The SQL_CLASS Schema Contains Your Tables
20. Set Your Database to NEXUS and Schema to SQL_CLASS
21. Download Nexus at CoffingDW.com
22. Nexus Server Migrates All Data Warehouses to Snowflake
23. Nexus Joins Data Across Platforms with the Super Join Builder 

What is Snowflake?

1. Snowflake IPO Largest in History
2. Advertising in the Computer Industry Pays off Each Year
3. Scaling Compute and Storage on Public Clouds
4. Customer Retention is Gold in the Computer Industry
5. High-powered Investors are Critical
6. Who are the Biggest Losers in the IPO?
7. Sharing Data Between Customers and Providers is Amazing
8. Standard SQL Makes Application Migration Easy
9. Hiring a Proven CEO can make the Difference
10. Snowflake Architecture
11. Snowflake Tables are Immutable
12. Elasticity
13. Worker Nodes and Worker Processes
14. Min-Max Based Pruning
15. Snowflake Execution Engine
16. Fault Tolerance
17. Semi-Structured Data
18. Snowflake Editions
19. Snowflake Pricing
20. Snowflake Virtual Warehouse Sizes
21. Snowflake Storage Pricing

Configuring and Managing the Snowflake Warehouse

1. Five System Defined Snowflake Roles
2. Create a Snowflake Virtual Data Warehouse
3. Create a Role
4. Create a User and Grant a Role
5. Scaling Up or Down the Virtual Data Warehouse
6. Scaling Up the Virtual Warehouse
7. Scaling Out the Virtual Data Warehouse
8. Configuring a Warehouse
9. The Life of a Query
10. Resource Monitor
11. Create Resource Monitor Screen
12. Creating a Resource Monitor with SQL 

Brilliant Features of Snowflake 

1. Snowflake Caching
2. The Life of a Query with Caching
3. Time Travel Using
4. Restoring Data Using Time Travel Feature (Best Option)
5. Restoring Data Using Time Travel Feature (Bad Option)
6. CREATE TABLE with Time Travel Days
7. Account Usage
8. Drop and Undrop
9. Rename a Table
10. SHOW TABLES With LIKE
11. Table Types
12. TABLE STORAGE METRICS QUERY
13. Creating a Transient Schema
14. Zero-Copy Cloning
15. Cloning a Temporary Table
16. Creating Temporary and Transient Tables
17. Fail-Safe

Performance Tuning with Cluster Keys

1. What is Parallel Processing?
2. The Basics of a Single Computer
3. Data in Memory is Fast as Lightning
4. Parallel Processing Of Data
5. Snowflake has Linear Scalability
6. Snowflake Clustering For Performance Tuning
7. Creating a Table with a Cluster Key
8. Altering a Table to Create or Drop a Cluster Key
9. Joining Tables Can Have the Same Cluster Keys for Speed
10. The Emp_Tbl CREATE Statement with Eight Rows
11. Snowflake Quiz –Put the Rows on the Proper Micro-Partition
12. The First Row is Now on the Proper Micro-Partition
13. Place the Remaining Rows on the Proper Micro-Partition
14. The Rows Are Now on the Proper Micro-Partition
15. Snowflake Slices will use Columnar Storage
16. Snowflake Micro-Partitions Create Metadata for each Block
17. Emp_Tbl CREATE Statement with a Cluster Key of DeptNo
18. Snowflake Quiz –Place the Rows on the Micro-Partition
19. The First Row is on the Proper Micro-Partition
20. Place the Remaining Rows on the Proper Micro-Partitions
21. The Rows are on the Proper Micro-Partitions
22. Snowflake will use Columnar Storage
23. Each Block Comes with Metadata
24. Snowflake Architecture:  Data Storage
25. Min-Max Based Pruning

Data Sharing

1. Data Sharing
2. Implementing Data Sharing
3. Investigating Data Sharing – SHOW SHARES
4. SEE SHARE DETAILS with the DESC Command
5. CREATE a Database for the SHARE
6. Sharing All Tables in a Database and Schema
7. Investigating Data Sharing – SHOW SHARES
8. SEE SHARE DETAILS with the DESC Command
9. Data Sharing for Non-Snowflake Account Users
10. Steps We Need to Take to Share with Non-Snowflake Users
11. Creating a Reader Account
12. Viewing Managed Accounts
13. Sharing Data with the Managed Account
14. Troubleshooting Sharing Data with the Managed Account
15. Gathering the URL for Logging Into a Reader Account
16. Using the URL to Login to your Snowflake Reader Account
17. SHOW SHARES for Reader Account
18. SEE SHARE DETAILS with the DESC Command
19. CREATE a Database and Warehouse for the SHARE
20. Create a User and Grant a Role for Reader Account Users

Snowflake Tasks

1. Creating a Task
2. Creating a Task Using CRON
3. Creating a Task Using CRON Using Minute and Hour
4. Tasks Using CRON Using Minute, Hour, and Day of Week
5. Creating a Tree of Tasks

Information Schema System Catalog

1. APPLICABLE_ROLES
2. COLUMNS
3. COLUMNS EXAMPLE
4. DATABASES
5. ENABLED_ROLES
6. EXTERNAL_TABLES
7. FILE_FORMATS
8. FUNCTIONS
9. INFORMATION_SCHEMA_CATALOG_NAME
10. LOAD_HISTORY
11. OBJECT_PRIVILEGES
12. PACKAGES
13. PIPES
14. PROCEDURES
15. REFERENTIAL_CONSTRAINTS
16. REPLICATION_DATABASES
17. SCHEMATA
18. SEQUENCES
19. STAGES
20. TABLE_CONSTRAINTS
21. TABLE_PRIVILEGES
22. TABLE_STORAGE_METRICS
23. TABLES
24. USAGE_PRIVILEGES
25. VIEWS

Systems Functions

1. ALL_USER_NAMES and CURRENT_IP_ADDRESS
2. CURRENT_ACCOUNT, CURRENT_AVAILABLE_ROLES
3. CURRENT_CLIENT and CURRENT_DATE
4. CURRENT_DATABASE and CURRENT_SCHEMA
5. CURRENT_SESSION and CURRENT_STATEMENT
6. CURRENT_TIME and CURRENT_TIMESTAMP
7. CURRENT_VERSION and CURRENT_WAREHOUSE
8. CURRENT_TRANSACTION and CURRENT_USER
9. CURRENT_REGION and CURRENT_ROLE

Data Ingestion

1. Loading Data
2. What is a Staging Area?
3. Snowflake has Four Types of Stages
4. Snowflake Internal Stages
5. Data File Details
6. User Stage
7. Loading User Stage Data with the Snowflake Web Interface
8. Go to the Databases Tab to See Your Databases
9. Click on the Table You Want to Load
10. The Table Columns Appear – Choose Load Table
11. Load Data Screen Appears
12. Choose Your Source Files
13. Choose Your File Format
14. Create the File Format
15. Create the File Format – Press Load
16. Load Results
17. Table Stage
18. Download a Flat File From Our Website for Load Exercise
19. Choose the COURSE OUTLINE Button for Snowflake
20. Press on Download Snowflake Class Database Script Button
21. Copying Data Into a Table Using the Table Stage
22. The PUT Command will not work on Snowflake Browser
23. Query a Table Residing in the Table Stage
24. Using the Copy Command with File Format Options
25. Named Stages
26. Create Three Internal Named Stages Using SQL
27. Creating Three Internal Named Stages with Nexus
28. Using an Internal Named Stage to Load Data with PUT
29. Create Named Stage Using the Snowflake Browser Tool
30. Importing Data From External Stages
31. Example of Copy Command from an External Stage
32. Example of Copy Command Using Pattern
33. Pattern Results from Loading Multiple Files
34. Copying Only Some Columns Into a Table
35. Functions for Transforming Data During a Load
36. Transformation Example Using CASE and Implicit CAST
37. Transformation Example To Populate Only Two Columns
38. Using the ON_ERROR Options
39. Creating and Describing a File Format
40. Altering or Changing Attributes in a File Format
41. DESCRIBING our File Format to Confirm Attribute Changes
42. Important Copy Options – Validation Mode
43. Validation Mode RETURN_ERRORS
44. Saving Load Error Rows in a Table
45. Another Technique to Save Load Error Rows in a Table
46. SPLIT_PART Function for Easy Reading of Errors
47. Important Copy Options – Size Limit
48. Example of Using the Size_Limit Option
49. Important Copy Options – RETURNED_FAILED_ONLY
50. RETURNED_FAILED_ONLY Example
51. Important Copy Options – TRUNCATECOLUMNS
52. TRUNCATECOLUMNS Example
53. Important Copy Options – FORCE
54. FORCE Example
55. An Example of a JSON File
56. Creating a JSON Stage and File Format
57. Querying the JSON Table
58. Tricks and Tips to Query the JSON Table
59. Creating a Table with Flattened Data
60. LOAD_HISTORY
61. STAGES View in Information_Schema
62. Nexus Server Migrates All Data Warehouses to Snowflake

Introduction to SQL

1. Introduction
2. SELECTING Current Information
3. Setting Your Default DATABASE and SCHEMA
4. SELECT * (All Columns) in a Table
5. SELECT Specific Columns in a Table
6. Commas in the Front or Back?
7. Place your Commas in front for better Debugging Capabilities
8. Sort the Data with the ORDER BY Keyword
9. Use a Column Name or Number in an ORDER BY Statement
10. Two Examples of ORDER BY using Different Techniques
11. Changing the ORDER BY to Descending Order
12. NULL Values Sort Last in Ascending Mode (Default)
13. Using the Nulls First Command
14. NULL Values Sort First in Descending Mode (DESC)
15. Using the Nulls Last Command
16. Major Sort vs. Minor Sort
17. Multiple Sort Keys using Names vs. Numbers
18. An Order By That Uses an Expression
19. Sorts are Alphabetical, NOT Logical
20. Using A Valued CASE Statement to Sort Logically
21. Using A Searched CASE Statement to Sort Logically
22. Order By Decode
23. How to ALIAS a Column Name
24. Using an Alias in the WHERE and ORDER BY Clause
25. Using an Alias in the ORDER BY Clause with Decode
26. A Missing Comma Can Become an Alias by Mistake
27. Comments using Double Dashes are Single Line Comments
28. Comments for Multi-Lines
29. Comments are a Great Technique for Finding SQL Errors

The WHERE Clause

1. The WHERE Clause limits Returning Rows
2. Numbers Don't Need Single Quotes
3. Not Equal
4. Searching for NULL Values Using Equality Returns Nothing
5. Use IS NULL or IS NOT NULL when dealing with NULLs
6. Using Greater Than Or Equal To (>=)
7. AND in the WHERE Clause
8. Troubleshooting AND
9. OR in the WHERE Clause
10. Troubleshooting OR
11. WHY OR must utilize the Column Name Each Time
12. Troubleshooting Character Data
13. Using Different Columns in an AND Statement
14. What is the Order of Precedence?
15. Using Parentheses to change the Order of Precedence
16. Using an IN List in place of OR
17. The IN List is an Excellent Technique
18. IN List vs. OR Brings the Same Results
19. The IN List Can Use Character Data
20. Using a NOT IN List
21. Null Values in a NOT IN List Return No Rows
22. A Technique for Handling Nulls with a NOT IN List
23. The BETWEEN Statement is Inclusive
24. The NOT BETWEEN Statement is also Inclusive
25. The BETWEEN Statement Works for Character Data
26. The Like Command Wildcards are Percent and Underscore
27. LIKE command Underscore is Wildcard for one Character
28. CASE Matters with the LIKE Command
29. Using LIKE for all Cases with Lower and Upper
30. Another Example of UPPER and LOWER
31. LIKE Command to Find Multiple Characters
32. LIKE Command to Find Either Character
33. Using ILIKE Handle Case Issues
34. Finding Anyone Whose Name Ends in 'Y'
35. Escape Character
36. Time Travel

Distinct, Group By, Top, and Pivot

1. The Distinct Command
2. DISTINCT vs. GROUP BY
3. Top Command
4. Top Command and Order By
5. Top Command and Order By Plus Nulls Last
6. The FETCH Clause
7. Sample and Tablesample
8. TOP vs. SAMPLE
9. Two Forms of Data Sampling
10. The Pivot Command

Aggregation

1. Casting a Data Type
2. Troubleshooting Aggregates
3. GROUP BY Delivers One Row Per Group
4. GROUP BY DEPT_NO or GROUP BY 1 are Equivalent
5. Limiting Rows and Improving Performance with WHERE
6. WHERE Clause in Aggregation limits unneeded Calculations
7. Keyword HAVING tests Aggregates after they are Totaled
8. Keyword HAVING is like an Extra WHERE Clause for Totals
9. ANY_VALUE
10. COUNT_IF
11. GROUP BY GROUPING SETS
12. GROUP BY ROLLUP
13. GROUP BY CUBE

Joining Tables

1. A Two-Table Join Using Traditional Syntax
2. A Join using Traditional Syntax with Table Alias
3. You Can Fully Qualify All Columns
4. A Join using ANSI Syntax
5. Both Queries Have the Same Results and Performance
6. LEFT OUTER JOIN
7. RIGHT OUTER JOIN
8. FULL OUTER JOIN
9. Which Tables are the Left and Which are Right?
10. Answer - Which Tables are the Left and Which are Right?
11. INNER JOIN
12. ANSI INNER JOIN
13. OUTER JOIN
14. The DREADED Product Join
15. Cartesian Product Join
16. The CROSS JOIN
17. The SELF JOIN
18. An Associative Table is a Bridge that Joins Two Tables
19. The Five-Table Join – Logical Insurance Model

Date Functions

1. CURRENT_DATE
2. CURRENT_DATE and CURRENT_TIMESTAMP
3. Current_Timestamp and Local_Timestamp With Precision
4. CURRENT_TIME vs. LOCALTIME With Precision
5. Add or Subtract Days from a Date
6. The ADD_MONTHS Command
7. ADD_MONTHS to Add a Year to a Date
8. ADD_MONTHS to Add Five Years to a Date
9. Incrementing Date Values Using the Dateadd Function
10. Incrementing Time Values Using the Dateadd Function
11. Dateadd Function And Add_Months Function are Different
12. Formatting a Date
13. The TO_CHAR Command to Format Dollar Signs
14. The TO_CHAR Command for Formatting Numbers
15. The EXTRACT Command
16. MONTHNAME
17. EXTRACT from DATES and TIME
18. EXTRACT from DATES and TIME Optional Syntax
19. Another Option for Extracting Portions of Dates and Times
20. The DATE_PART Function
21. Using DATE_PART to Extract
22. Implied Extract of Day, Month, and Year using TO_CHAR
23. The DATE_PART Function Using Day of Week (DOW)
24. Day of Week and a CASE Statement
25. Day of Week and DECODE
26. Great Date Functions to Know
27. Week of Year and Year of Week
28. First Day and Last Day Functions
29. DATEDIFF
30. Using CASE and Extract to Reformat Dates
31. Using CAST and SUBSTRING to Reformat Dates
32. The Date_Trunc Function
33. DATE_TRUNC Command With Time
34. DATE_TRUNC Command With Dates
35. LAST_DAY
36. LAST_DAY
37. NEXT_DAY
38. PREVIOUS_DAY
39. MONTHS_BETWEEN
40. TIME_SLICE
41. TO_TIMESTAMP
42. Using Day, Month, and Year intervals
43. The Basics of a Simple Interval

Temporary Tables

1. Derived Query Examples with Three Different Techniques
2. Most Derived Tables Are Used To Join To Other Tables
3. The Three Components of a Derived Table
4. Visualize This Derived Table
5. An Example of Two Derived Tables Using WITH
6. WITH RECURSIVE
7. Creating a Temporary Table
8. CREATE a Temporary TABLE using LIKE
9. Creating a Temporary Table using a CTAS
10. CREATE Temporary Table AS (CTAS) with Specific Columns
11. CREATE a Temporary Table AS (CTAS) Using a Join
12. Cloning a Temporary Table
13. Dropping a Temporary Table

Sub-query Functions

1. An IN List is much like a Subquery
2. An IN List Never has Duplicates – Just like a Subquery
3. An IN List Ignores Duplicates
4. The Subquery
5. The Three Steps of How a Basic Subquery Works
6. These are Equivalent Queries
7. The Answer Set from the Subquery
8. Answer to Quiz- Answer the Difficult Question
9. Should you use a Subquery or a Join?
10. The Basics of a Correlated Subquery
11. The Top Query always runs first in a Correlated Subquery
12. Correlated Subquery Example vs. a Join with a Derived Table
13. NOT IN Subquery Returns Nothing when NULLs are Present
14. Fixing a NOT IN Subquery with Null Values
15. How the Double Parameter Subquery Works
16. More on how the Double Parameter Subquery Works
17. IN is equivalent to =ANY
18. Using a Correlated Exists
19. How a Correlated Exists Matches Up
20. The Correlated NOT Exists

Analytic and Window Functions

1. ROW_NUMBER
2. Using a Derived Table
3. RANK
4. Dense_Rank
5. RANK vs. DENSE_RANK
6. Getting RANK to Sort in DESC Order
7. RANK() OVER, PARTITION BY, and QUALIFY
8. Using a Derived Table
9. DENSE_RANK() OVER and PARTITION BY
10. PERCENT_RANK() OVER with 14 rows in Calculation
11. PERCENT_RANK() OVER with 21 rows in Calculation
12. PERCENT_RANK and PARTITION BY
13. Cumulative Sum
14. Reset with a PARTITION BY Statement
15. Totals and Subtotals through Partition By
16. Moving SUM every 3-rows vs. a Continuous Average
17. Partition By Resets the Calculations
18. Moving Average
19. The Moving Window is Current Row and Preceding n
20. How Moving Average Handles the Order By
21. Moving Average every 3-rows Vs. a Continuous Average
22. The Partition By Statement
23. Partition By Resets an ANSI OLAP
24. Moving Difference
25. Moving Difference with Partition By
26. Finding a Value of a Column in the Next Row with MIN
27. Finding a Next Row Value with MIN and PARTITION BY
28. Finding Multiple Values of a Column in Upcoming Rows
29. Finding The Next Date using MAX
30. COUNT OVER for a Sequential Number
31. COUNT OVER using ROWS UNBOUNDED PRECEDING
32. The MAX OVER Command
33. MAX OVER  with PARTITION BY Reset
34. The MIN OVER Command
35. The MIN OVER Command with PARTITION BY
36. Finding Gaps Between Dates
37. CSUM For Each Product_ID For the First Three Days
38. FIRST_VALUE
39. Using FIRST_VALUE
40. Last_Value
41. First and Last Value Difference
42. Using LEAD
43. Using LAG
44. CUME_DIST
45. CURRENT ROW AND UNBOUNDED FOLLOWING
46. Different Windowing Options
47. How Ntile Works
48. Ntile
49. Using Quantiles (Partitions of Four)
50. Using Deciles (Partitions of Ten)
51. MEDIAN
52. PERCENTILE_CONT Function Description and Syntax
53. Result Information About PERCENTILE_CONT
54. PERCENTILE_CONT
55. LISTAGG
56. RATIO_TO_REPORT
57. ANY_VALUE
58. MODE
59. Width_Bucket
60. COUNT_IF

Strings

1. UPPER and lower  Functions
2. The Length Command Counts Characters
3. LENGTH Does Not Work on Fixed Length Columns
4. OCTET_LENGTH
5. The TRIM Command Trims Leading and Trailing Spaces
6. The RTRIM and LTRIM Command Trims Spaces
7. Concatenation
8. Concat and Concat_WS for Concatenation
9. The SUBSTR and SUBSTRING Commands
10. How SUBSTR Works with NO ENDING POSITION
11. Using SUBSTR and CHAR_LENGTH Together
12. The POSITION Command finds a Letters Position
13. The POSITION Command is brilliant with SUBSTR
14. CHARINDEX Finds a Letter's Position in a String
15. CHARINDEX Command is Brilliant with SUBSTRING
16. The CHARINDEX Command Using a Literal
17. LPAD and RPAD
18. The REPLACE Function
19. REGEXP
20. SOUNDEX Function to Find a Sound
21. The REVERSE String Function
22. The RIGHT Function
23. The LEFT and RIGHT Functions
24. The ASCII Function

Interrogating the Data

1. The COALESCE Command
2. COALESCE is Equivalent to this CASE Statement
3. Some Great CAST (Convert And Store) Examples
4. A Rounding Example Using CAST
5. CAST will Round Values Up or Down
6. Valued Case vs. Searched Case
7. Combining Searched Case and Valued Case
8. The DECODE Command
9. DECODE
10. A Trick for getting a Horizontal Case
11. Put a CASE in the ORDER BY
12. Using A Searched CASE Statement to Sort Logically
13. Order By DECODE
14. CASE Challenge

View Functions

1. The Fundamentals of Views
2. Creating a Simple View to Restrict Sensitive Columns
3. Creating a Simple View to Restrict Rows
4. Creating a View to Join Tables Together
5. Sometimes We Create Views for Formatting
6. Basic Rules for Views
7. How to Modify a View
8. Creating a Secure View to Restrict DDL Viewing
9. The Exception to the ORDER BY Rule inside a View
10. Another Exception to the ORDER BY Rule is TOP
11. Derived Columns in a View Must Have a Column Alias
12. The Standard Way Most Aliasing is Done
13. Another Way to Alias Columns in a View CREATE
14. What Happens When a View Column gets Aliased Twice?

Set Operators

1. Rules of Set Operators
2. INTERSECT Explained Logically
3. Rule 1 - Equal Number of Columns in Both SELECT Lists
4. Rule 2 -  Top Query Handles all Aliases
5. Rule 3 - The Bottom Query does the ORDER BY
6. Intersect Challenge
7. Answer - Intersect Challenge
8. UNION Vs. UNION ALL
9. Using UNION ALL and Literals
10. Using UNION ALL for Speed in Merging Data Sets
11. Great Trick:  Place your Set Operator in a Derived Table
12. A Great Example of how EXCEPT and MINUS work
13. Using UNION to be same as GROUP BY GROUPING SETS
14. USING Multiple SET Operators in a Single Request
15. Changing the Order of Precedence with Parentheses

Creating Tables

1. Table Types
2. SHOW TABLES With LIKE
3. TABLE STORAGE METRICS QUERY
4. Snowflake Data Types
5. More Snowflake Data Types
6. Show Databases and Table DDL Commands
7. Finding Constraints
8. The Basics of Creating a Table
9. Creating a Table
10. Creating Temporary and Transient Tables
11. Snowflake Clustering
12. Creating a Table with a Cluster Key
13. Creating a Table with a Cluster Key Function
14. Creating a Table with a Multi-Cluster Key
15. Altering a Table to Create or Drop a Cluster Key
16. Joining Tables Can Have the Same Cluster Keys for Speed
17. Creating Tables with a Primary Key/Foreign Key Relationship
18. Primary Key Does Not Enforce Uniqueness
19. A Table with a NOT NULL Constraint
20. AUTOINCREMENT
21. Restoring Data Using Time Travel Feature
22. Fail Safe
23. CREATE TABLE with Time Travel Days
24. Create Table LIKE
25. CREATE a Temporary TABLE using LIKE
26. CREATE TABLE AS (CTAS) Populates the Table With Data
27. CREATE TABLE AS (CTAS) Can Choose Certain Columns
28. CREATE a Temporary Table (CTAS) with Specific Columns
29. CREATE a Temporary Table AS (CTAS) Using a Join
30. Create a Table IF NOT EXISTS
31. A Table with a NOT NULL Constraint
32. Create a Table with a Column Default Value
33. Creating a Transient Schema
34. Zero-Copy Cloning
35. Cloning a Temporary Table
36. CREATE TABLE AS (CTAS) Populates the Table With Data
37. CREATE TABLE AS (CTAS) Can Choose Specific Columns
38. CREATE Temporary Table AS (CTAS) with Specific Columns
39. CREATE a Temporary Table AS (CTAS) Using a Join

Inserts, Updates, and Deletes

1. INSERT Syntax
2. INSERT Example with Multiple Rows
3. Inserting Null Values into a Table
4. INSERT/SELECT Command
5. INSERT/SELECT to Build a Data Mart
6. UPDATE Examples
7. Example of Subquery UPDATE Command
8. MERGE
9. Drop and Undrop a Table
10. Drop and Undrop a Schema
11. Drop and Undrop a Database
12. Rename a Table
13. Deleting Rows in a Table

Unstructured Data: JSON and XML

1. JSON and the Internet of Things
2. JSON Syntax Explanation
3. Building a Complex Object
4. Continuing to Build a Complex Object
5. Creating a Table and Inserting JSON Data
6. Querying the JSON Table
7. Performing a Flatten to Display Rows Like a Typical Table
8. Creating a View on a Flattened Table
9. Further Building a Complex Object
10. Creating a Table and Inserting JSON Data
11. Querying the JSON Table
12. Creating a JSON Table
13. Inserting Two Objects Into a JSON Table
14. How to Query a JSON Variant Data Type
15. Inserting 10 Objects Into a JSON Table
16. JSON Technique to CREATE a Table and INSERT Data
17. How to Query JSON USING $1
18. How to Format JSON Data
19. Querying Portions of Semi-Structured Data
20. Querying Semi-Structured Data With a WHERE Clause
21. Using the FLATTEN Function to Parse Arrays
22. Using the FLATTEN Function to Parse Nested Arrays
23. Extracting Values by Path Using the GET_PATH Function
24. Creating an XML Table
25. Insert Into An XML Table
26. XMLGET and FLATTEN Function For XML Data
27. PARSE_JSON Semi-Structured Data Function
28. Describing a Table that Contains a Variant Data Type
29. Inserting a Wide Variety of Data Into a Variant Data Type
30. Using the TYPEOF Function
31. Using the STRIP_NULL_VALUE Function

User-Defined Functions (UDFs)

1. CREATE Function Syntax
2. Two Simple Functions with Hard-Coded Values
3. You Can Use a Function in a Join
4. You Can Use a Function in WHERE Clause
5. You Can Use a Function in the ORDER BY Clause
6. Creating a function with an Input Argument
7. Create a Function to get Factorial Numbers
8. Creating a function to Get Fibonacci Numbers
9. Creating a function with the Keyword TABLE
10. Joining a Function with the Keyword TABLE
11. JavaScript UDF with a Try Catch Block
12. Using a JavaScript UDF with a Try Catch Block for Validation
13. JavaScript UDF with IF, ELSE IF, and ELSE Try Catch Block
14. DDL and DML Operations are Not Supported in Functions
15. An EXAMPLE of a CASE Statement Using Two Functions
16. Functions With Duplicate Names But Different Arguments
17. You ALTER a Function for Three Reasons
18. Examples of Altering a Function
19. SHOW User Functions
20. Information_Schema Functions
21. GET DDL Function
22. Conversion Functions and TRY_CAST
23. Semi-Structured Data Functions
24. Functions for Array and Object Creation and Manipulation
25. Functions for Semi-structured Extraction and Conversion
26. Functions for Semi-structured Extraction and Type Predicates

Stored Procedures

1. Why Use Snowflake Stored Procedures?
2. Stored Procedure Syntax Using Javascript
3. Create and Execute Your First Javascript Stored Procedure
4. Create and Execute with an Argument Using Backticks
5. A Stored Procedure to INSERT Using Language SQL
6. SQL Stored Procedure INSERT Using Input Parameters
7. Creating a Snowflake Stored Procedure that Updates
8. Stored Procedure UPDATE Using Subquery
9. Stored Procedure Delete Using an Input Parameter
10. Snowflake Stored Procedure Control Structures
11. A Stored Procedure With IF and END IF Logic
12. A Stored Procedure With IF, ELSEIF, and ELSE Logic
13. A Stored Procedure With Simple Valued Case Logic
14. A Stored Procedure With Searched Case Logic
15. Using Loops in Stored Procedures
16. Using a WHILE Loop
17. Using a FOR Loop
18. Using a FOR Reverse Loop
19. Stored Procedure Workshop
20. Creating a Complex Javascript Stored Procedure
21. Inserting a Row Into a Table Using Javascript
22. Updating a Row Into a Table Using Javascript
23. Deleting a Row Into a Table Using Javascript
24. DDL that Applies to Stored Procedures
25. Describe Procedure
26. Procedure Definition from Information_Schema
27. Procedure Definition from GET_DDL
28. Procedure Definition from GET_DDL using TRUE Keyword
29. Alter Procedure
30. Renaming a Procedure
31. Dropping a Procedure
32. Snowflake Data Types you must Convert to Javascript
33. Stored Procedure to Get Methods from an Object
34. Stored Procedure Javascript Methods Part 1
35. Stored Procedure JavaScript Methods
36. Stored Procedure Javascript Methods Part 2
37. Stored Procedure to Get Methods from a Result Set Object
38. Stored Procedure Javascript Result Set Methods
39. Make Your Arguments in Upper Case
40. Best Practice – Make Your Arguments in Upper Case
41. How to Catch and Error using Try/Catch
42. Using a WHILE Loop
43. Creating a Stored Procedure to Get Fibonacci Numbers
44. Create a Stored Procedure to get Factorial Numbers
45. Using a WHILE Loop Example Result Set
46. Line Continuation for Long SQL Statements
47. Loading Rows From a Join Into a Table
48. Using Stored Procedures to Produce Dynamic SQL
49. Dynamic SQL Example to Truncate a Table
50. Dynamic SQL Store Procedure that Calls Another Procedure
51. Stored Procedures Privileges
52. Information_Schema for a Database and a Schema
53. A Stored Procedure to Show Privileges
54. Finding Details About Procedures
55. Granting and Revoking Usage on a Procedure to Another Role

Statistical Aggregate Functions

1. The Stats Table
2. The KURTOSIS Function
3. The SKEW Function
4. The STDDEV_POP Function
5. STDDEV_POP Example
6. The VAR_POP Function
7. The VAR_SAMP Function
8. The CORR Function
9. Another CORR Example so you can Compare
10. The VARIANCE Function
11. The COVAR_POP Function
12. The COVAR_SAMP Function
13. Another COVAR_SAMP Example so you can Compare
14. The REGR_INTERCEPT  Function
15. The REGR_SLOPE Function
16. The REGR_AVGX Function
17. The REGR_AVGY   Function
18. The REGR_COUNT   Function
19. The REGR_R2 Function
20. The REGR_SXX   Function
21. The REGR_SXY Function
22. The REGR_SYY Function
23. Using GROUP BY
24. APPROX_COUNT_DISTINCT