Snowflake SQL Tutorial - Zero To Hero

In this series we go through Snowflake SQL from the very first SELECT all the way to administration, one topic at a time. Every episode is a short written post with the queries, the real output, and a video you can follow along with.

There are 173 episodes across 13 chapters. Start at the top if you are new to Snowflake, or jump straight to the chapter you need - each post stands on its own.

Chapter 1 - Querying Data - SELECT, WHERE, ORDER BY

Episodes 1 to 12

1. SELECT Statement Explained - EXCLUDE, RENAME, REPLACE & ILIKE
2. FROM Clause Explained - Table Aliases, Qualified Names & SELECT With No Table
3. WHERE Clause Explained - Comparison Operators + The NULL Trap
4. BETWEEN, IN, LIKE, ILIKE & RLIKE Explained
5. AND, OR, NOT Explained - The Operator Precedence Mistake Everybody Makes
6. NULL in Snowflake Explained - Why NOT IN Can Return ZERO Rows
7. ORDER BY in Snowflake - NULLS FIRST, NULLS LAST & Why NULLs Don't Always Sort Last
8. TOP 3 Is NOT the Top 3 - LIMIT, TOP, FETCH & OFFSET in Snowflake
9. DISTINCT Is NOT a Function - Why COUNT(DISTINCT) Gave Me a Different Answer
10. You CAN Use a Column Alias in WHERE - in Snowflake - Aliases & AS
11. 7 divided by 2 Is NOT 3 - Operators, Precedence & Concatenation in Snowflake
12. Why Your Query Returns ZERO Rows - Case Sensitivity in Snowflake Explained

Chapter 2 - Aggregation - GROUP BY and the aggregate functions

Episodes 13 to 23

13. COUNT(Star) vs COUNT(col) vs COUNT(DISTINCT) - Three Correct Answers, One Table
14. SUM, AVG, MIN and MAX in Snowflake
15. GROUP BY in Snowflake - Eight Rows In, Four Rows Out - The Whole Idea in One Picture
16. GROUP BY ALL in Snowflake
17. HAVING in Snowflake
18. ROLLUP, CUBE and GROUPING SETS in Snowflake
19. LISTAGG in Snowflake
20. ARRAY_AGG and OBJECT_AGG in Snowflake
21. MEDIAN, STDDEV and Percentiles in Snowflake
22. Approximate Aggregates in Snowflake
23. QUALIFY in Snowflake

Chapter 3 - Joins and Set Operators

Episodes 24 to 36

24. INNER JOIN in Snowflake
25. LEFT and RIGHT OUTER JOIN in Snowflake
26. FULL OUTER JOIN in Snowflake
27. CROSS JOIN in Snowflake
28. USING vs ON in Snowflake, and Why NATURAL JOIN Returned Zero Rows
29. Joining Three or More Tables in Snowflake
30. Self Joins in Snowflake
31. Non-Equi Joins in Snowflake
32. ASOF JOIN in Snowflake
33. LATERAL in Snowflake
34. UNION vs UNION ALL in Snowflake
35. INTERSECT, MINUS and EXCEPT in Snowflake
36. CONNECT BY in Snowflake

Chapter 4 - Subqueries and CTEs

Episodes 37 to 45

37. Scalar Subqueries in Snowflake
38. The NOT IN NULL Trap in Snowflake - Correct SQL, No Error, Zero Rows
39. EXISTS and NOT EXISTS in Snowflake - SELECT 1 vs SELECT Star
40. ANY, ALL and SOME in Snowflake
41. Correlated Subqueries in Snowflake
42. Derived Tables in Snowflake - A Subquery in FROM Is a Table That Lives for One Query
43. WITH & CTEs in Snowflake - The Same Query, Read Top to Bottom Instead of Inside Out
44. Recursive CTEs in Snowflake - I Run the Recursion by Hand So You Can See 1 + 5 + 2 = 8
45. VALUES in Snowflake - A Table Made of Literals

Chapter 5 - Window Functions

Episodes 46 to 58

46. OVER() in Snowflake - Aggregate Without Losing Your Rows
47. PARTITION BY in Snowflake - The Same Groups as GROUP BY - Without Losing a Single Row
48. ORDER BY Inside OVER in Snowflake - Three Words That Turn a Total Into a Running Total
49. ROW_NUMBER in Snowflake
50. RANK vs DENSE_RANK in Snowflake
51. NTILE, PERCENT_RANK and CUME_DIST in Snowflake
52. LAG and LEAD in Snowflake
53. LAST_VALUE in Snowflake
54. Window Frames in Snowflake - Measure the Frame WITH the Frame - and Watch It Move
55. ROWS vs RANGE in Snowflake
56. Moving Averages in Snowflake
57. RATIO_TO_REPORT, WIDTH_BUCKET and CONDITIONAL_TRUE_EVENT
58. Top-N Per Group in Snowflake

Chapter 6 - Reshaping and Sampling - PIVOT, UNPIVOT, MATCH_RECOGNIZE

Episodes 59 to 63

59. PIVOT in Snowflake - Rows Into Columns - and the One Mistake That Turns 8 Rows Into 41
60. UNPIVOT in Snowflake - Why 8 Rows x 4 Columns Gives You 13 Rows, Not 32
61. SAMPLE in Snowflake
62. MATCH_RECOGNIZE in Snowflake
63. AFTER MATCH SKIP in Snowflake - Same Pattern, Same 23 Rows, Three Different Answers

Chapter 7 - Built-in Functions - strings, numbers, dates, conversion

Episodes 64 to 85

64. CONCAT, Double Pipe and LENGTH in Snowflake - Strings 1
65. TRIM, LTRIM, RTRIM, LPAD, RPAD in Snowflake - Strings 2
66. SUBSTR, LEFT, RIGHT, POSITION, CHARINDEX - Strings 3
67. REPLACE, TRANSLATE, SPLIT, SPLIT_PART, STRTOK - Strings 4
68. REGEXP_LIKE and REGEXP_COUNT - Regex 1 (the anchoring rule)
69. REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR - Regex 2
70. ROUND, CEIL, FLOOR, TRUNC, ABS, SIGN, MOD - Numbers
71. POWER, SQRT, EXP, LN, LOG, RANDOM, UNIFORM - Maths
72. DATEADD, DATEDIFF, CURRENT_DATE - Dates 1
73. DATE_TRUNC, DATE_PART, EXTRACT, LAST_DAY, NEXT_DAY - Dates 2
74. DAYNAME, MONTHNAME, Weekday vs Weekend, TIME_SLICE - Dates 3
75. TO_CHAR, TO_DATE, TRY_TO_DATE - Dates 4
76. TIMESTAMP_NTZ vs LTZ vs TZ, CONVERT_TIMEZONE - Dates 5
77. CAST, TO_NUMBER, TO_VARCHAR, TO_BOOLEAN - Conversion
78. TRY_TO_NUMBER, TRY_CAST, TRY_TO_DATE - The TRY_ Family
79. CASE WHEN, Simple vs Searched, NULL - The ELSE Trap
80. IFF, NVL, NVL2, COALESCE, NULLIF, DECODE - Null Handling
81. ZEROIFNULL, NULLIFZERO, GREATEST, LEAST, EQUAL_NULL
82. CURRENT_USER, CURRENT_DATABASE, IDENTIFIER - Context Functions
83. HASH, MD5, SHA2, BASE64, HEX - Hashing and Encoding
84. BITAND, BITOR, BITXOR, BITNOT, Shifts - Bitwise Functions
85. GENERATOR, SEQ4, Date Spine - Making Rows From Nothing

Chapter 8 - Semi-Structured Data - JSON, VARIANT, XML

Episodes 86 to 96

86. The VARIANT Type, TYPEOF, IS_NULL_VALUE - Semi-Structured Data
87. PARSE_JSON, TRY_PARSE_JSON, CHECK_JSON, TO_JSON - Loading JSON
88. Navigating JSON - Colon, Dots, Brackets, GET and GET_PATH
89. Casting Out of VARIANT - Double Colon, TRY_TO_NUMBER, and why '1' is not 1
90. LATERAL FLATTEN - Arrays into Rows, and the Total That Doubles
91. FLATTEN Part 2 - RECURSIVE, MODE, PATH, and Nested Flattens
92. ARRAY Functions - SIZE, CONTAINS, SLICE, CAT, SORT, AGG
93. OBJECT Functions - CONSTRUCT, KEYS, INSERT, DELETE, PICK, AGG
94. TYPEOF, IS_ and AS_ - The Safe VARIANT Cast at Last
95. Structured Types - ARRAY(INT), OBJECT and MAP - It Refuses
96. XML - PARSE_XML, CHECK_XML and XMLGET - Level 8 Finale

Chapter 9 - DDL - databases, schemas, tables, views

Episodes 97 to 114

97. Data Types - Which Names Are Lies - Level 9 Opens
98. CREATE OR REPLACE DATABASE Is Destructive (and UNDROP Can't Save You)
99. Schemas, and Dropping the PUBLIC Schema - Same Query, Different Rows
100. CREATE TABLE - A DEFAULT Is Not a Fallback for NULL
101. CTAS vs LIKE vs CLONE - What a CTAS Silently Throws Away
102. A TEMPORARY Table Can Hide a PERMANENT One (Qualifying Won't Save You)
103. ALTER TABLE - ADD, DROP, RENAME and SWAP WITH
104. ALTER COLUMN - Three Rules, One Syntax (and a DEFAULT You Can't Change)
105. PRIMARY KEY, UNIQUE and FOREIGN KEY Are Not Enforced - Level 9
106. UNDROP Is Not A Stack, And TRUNCATE Does Not Reset AUTOINCREMENT
107. CREATE OR ALTER TABLE Drops The Column You Leave Out
108. A SELECT Star View Does Not Go Stale, It Breaks
109. A Materialized View Is A Much Smaller Language
110. Dynamic Tables Take the Query a Materialized View Refuses
111. A Sequence Is Not In Order, And The Catalog Says So
112. SHOW Is Not Queryable, Until You Wrap It - RESULT_SCAN
113. External And Iceberg Tables - The Refusals Are The Specification
114. How Do I Create An Index In Snowflake - The Answer Has Three Parts

Chapter 10 - DML - INSERT, UPDATE, DELETE, MERGE

Episodes 115 to 121

115. INSERT Matches By Position, Not By Name
116. INSERT ALL Wrote 7 Rows From 5. INSERT FIRST Wrote 5
117. UPDATE Has A Column That Says Your Join Matched Twice
118. The DELETE That Emptied The Whole Table, And How I Got It Back
119. MERGE - A Condition In The ON Clause Duplicated A Row
120. MERGE Refuses The Obvious Dead Branch And Accepts The Subtle One
121. The CREATE TABLE That Committed My Transaction

Chapter 11 - Loading and Pipelines - stages, COPY INTO, streams, tasks

Episodes 122 to 139

122. Two Of Your Stages Already Exist And You Never Created Either
123. PUT Renames Your File And GET Hands It Back Still Zipped
124. My PATTERN Matched Nothing And Snowflake Said Nothing
125. The Same CSV File Read Twice, Giving Different Data
126. I Ran The Same COPY INTO Twice And The Second One Did Nothing
127. PARTIALLY_LOADED Is A Successful Load With Rows Missing
128. COPY INTO Writes A Directory, Not A File
129. INFER_SCHEMA Sized My Columns To Four Rows Of Data
130. COPY FILES Won't Touch Your User Stage, And Doesn't Say Why
131. My Snowpipe Loaded Six Rows Twice And COPY_HISTORY Told Me Why
132. My Time Travel Repair Restored Six Rows And Still Got It Wrong
133. The Catalog Listed My Dropped Table And UNDROP Said It Was Purged
134. I Cloned A Schema And One View Kept Pointing At The Original
135. A Stream Said It Had Data And Then Returned Zero Rows
136. A Statement That Inserted Zero Rows Still Emptied My Stream
137. I Ran A Task By Hand And The Table Was Still Empty
138. I Resumed The Root And Snowflake Let Me Build A Dead DAG
139. My Alert Fired Three Times For One Order And Would Not Stop

Chapter 12 - Programmability - UDFs and stored procedures

Episodes 140 to 154

140. A View That Returns Different Rows Depending On Who Is Looking
141. My Function Said RETURNS INT And Handed Back 3.5
142. My Comma Join To A Table Function Silently Dropped A Region
143. My JavaScript UDF Could Not See Its Own Argument
144. Python Kept My 30-Digit Number And JavaScript Could Not Even Declare It
145. The RETURNS Clause My Function Ignored Is Enforced In A Procedure
146. The Colon Only Matters In One Place, And This Is It
147. My Loop Failed Half Way And Kept Every Row It Had Already Written
148. My Cursor Walked Once And Then Gave Me Nothing
149. I Caught The Exception And Every Row It Wrote Is Still There
150. One Quote Mark Turned A Two Row Lookup Into Three
151. A Procedure That Vanishes After One Statement, And The Row It Left Behind
152. EXPLAIN Doesn't Run It, And I Counted The Rows To Prove It
153. I Ran The Same Query Twice And Got 4 Then 1
154. External Access Is Not Supported For Trial Accounts

Chapter 13 - Administration - warehouses, roles, security, usage

Episodes 155 to 173

155. CREATE WAREHOUSE Moved My Session And Broke My Own Demo
156. Resizing A Suspended Warehouse Switched It On, And UNSET Gave Me NULL
157. I Dropped A Resource Monitor That Was Guarding A Warehouse, And Snowflake Said Nothing
158. A New Role Can Do Nothing, And Nothing Tells You So
159. I Said A SELECT Needs Four Privileges. It Needs Three
160. Revoking A Future Grant Doesn't Take Back What It Gave
161. Snowflake Let Me Write A Grant That Does Absolutely Nothing
162. SHOW GRANTS Gave Me A True Answer And A Wrong One
163. Snowflake Let Me Point A User At A Role That Does Not Exist
164. Snowflake Refused To Drop This One, And In Episode 157 It Did Not
165. I Wrote The Masking Policy Myself And Snowflake Still Hid My Data From Me
166. My Own Table Returned Zero Rows And Snowflake Called That Success
167. I Removed A Value From A Tag's Allowed List And The Object Kept It
168. The One Sharing Statement I Refused To Run, And Why
169. One Statement Took A Role From 0 Grants To 673
170. The Same COUNT Gave Me 76 And 64, And That Is The Whole Point
171. ACCOUNT_USAGE Found The Table I Just Dropped. It Was The Wrong One
172. One Parameter, Three Different Answers. ALTER SESSION Explained
173. I Said These Would Be Refused. All Eight Ran. Replication Explained