1. Data manipulation in Snowflake
Welcome in! I'm Jake, and I'll be your instructor as we explore the best ways to manipulate data in Snowflake. I'm a Data Engineer, and Snowflake is one of my favorite tools!
2. Snowflake and the modern data stack
But I'm not the only one that loves Snowflake; in fact, Snowflake is one of the most popular tools in the modern data stack. Data analysts, scientists, and engineers use Snowflake to manipulate data with the end goal of creating value from that data.
3. Data manipulation in Snowflake
Some of these tasks might include categorizing and analyzing song metrics with `CASE` statements, breaking down top-performing products with subqueries, and performing item-level order validation with common table expressions.
By the end of the course, you'll be able to do these things as well. But for now, let's start with conditional logic and `CASE` statements.
4. Conditional logic in Snowflake
In fancy terms, conditional logic allows us to check the value of some field and DO SOMETHING based on that value.
In other programming languages, this is commonly referred to as "if-then" logic. In Snowflake, we'll use `CASE` statements. `CASE` statements allow us to do things like categorize or transform data, and perform operations on that data.
5. CASE statements
We begin a `CASE` statement with the `CASE` keyword. Here, we've written a `CASE` statement to create a new column that stores each month as an integer. We use the `WHEN` keyword to check a condition followed by `THEN` to respond accordingly. `WHEN` the `grade_num` is 12, `THEN` return Senior. If it's 11, Junior, and so on. Finally, we complete the `CASE` statement with the END keyword and alias the column.
The order of these steps is `CASE`, `WHEN`, `THEN`, `END`. We aren't limited to one or two `WHEN` statements; we can add as many as we'd like!
6. Converting Grades to Grade Point Averages
Check out this `CASE` statement. Here, we're converting letter grades to a grade point averages, also known as GPAs. We start with the `CASE` keyword. For each letter grade, we create a `WHEN`-`THEN` clause. `WHEN` the grade field is an "A", `THEN` the GPA for that course is 4.0. `WHEN` the grade field is a "B", `THEN` the corresponding GPA is 3.0, and so on. You can add as many `WHEN`-`THEN` clauses in a `CASE` statement as you'd like. Here, we do this for letter grades A, B, C, D, and F.
The `END` keyword marks the end of the `CASE` statement. Here, we've also gone ahead and aliased the results of the `CASE` statement as "gpa". This keeps your output looking clean.
This is the standard syntax for a `CASE` statement. And although we'll expand on it a bit more, this is the basic framework we'll leverage moving forward.
7. Let's practice!
Now, it's time to get your hands dirty both in the Snowflake UI and writing Snowflake queries. Good luck!