Data manipulation in Snowflake

Data Manipulation in Snowflake

Jake Roach

Field Data Engineer

Snowflake and the modern data stack

Snowflake logo

Data manipulation in Snowflake

Learners will be able apply the tools they learned to manipulate Snowflake data in real-world scenarios

Conditional logic in Snowflake

An example of generic conditional logic

Evaluate the value of a field and do something based on that value.

CASE Statements

Categorize/bucket data
Transform and filter data
Perform operations on that data

CASE statements

CASE ... WHEN ... THEN ... END

Begin the evaluation with CASE
Check a condition with WHEN
Respond with THEN
Complete the evaluation with END
Alias the column

SELECT
    student_name,
    CASE
        WHEN grade_num = 12 THEN 'Senior'
        WHEN grade_num = 11 THEN 'Junior'
        ...
    END AS grade
FROM students;

     |   student_name  |   grade   |
     | --------------- | --------- |
     |     Viraj       |   Junior  |
     |     Stephanie   |   Senior  |

Converting Grades to Grade Point Averages

SELECT
    student_id,
    course_name,

    CASE

        WHEN grade = 'A' THEN 4.0

        WHEN grade = 'B' THEN 3.0

        WHEN grade = 'C' THEN 2.0
        WHEN grade = 'D' THEN 1.0
        WHEN grade = 'F' THEN 0.0

    END AS gpa  -- Grade Point Average

FROM student_courses;

  | student_id |  course_name  | gpa |
  | ---------- | ------------- | --- |
  |     001    |  Stats 101    | 4.0 |
  |     001    |  Calculus     | 3.0 |
  |     002    |  Biology      | 3.0 |
  |     003    |  Finance      | 1.0 |
  |     004    |  Engineering  | 4.0 |
  |     004    |  Sales        | 2.0 |
  |     004    |  Botany       | 4.0 |

  ...

Let's practice!

Data Manipulation in Snowflake