Penutup!

Manipulasi Data di Snowflake

Jake Roach

Field Data Engineer

Manipulasi data di Snowflake

Pernyataan CASE

Memanipulasi dan mengubah data berdasarkan kondisi.

Subquery

Memakai hasil satu query di query lain.

Common Table Expressions

Mendefinisikan hasil sementara untuk dipakai di query utama.

Logika bersyarat

SELECT
    todays_date,
    temperature,
    status,
    CASE
        WHEN temperature > 70 AND status NOT IN ('Rain', 'Wind') THEN 'Beach'
        WHEN temperature BETWEEN 45 AND 70 AND status = 'Sun' THEN 'Sports'
        WHEN temperature <= 32 OR status = 'Snow' THEN 'Skiing'
        ELSE 'Stay In'
    END AS activity
FROM weather;

Logika bersyarat dapat digabungkan dengan fungsi agregasi.

Subquery

Proses logis memanipulasi data secara lebih mudah dibaca dan modular dengan subquery

SELECT
    month_num,
    AVG(differential) AS avg_differential
    MIN(differential) AS most_differential
FROM (
    SELECT
        month_num,
        windchill - temperature AS differential
    FROM weather
    WHERE 
        season = 'Winter' AND
        temperature < 32
)
GROUP BY month_num;

Common Table Expressions

WITH at_risk AS (
    SELECT
        student_id
        course_name,
        teacher_name,
        grade
    FROM student_courses
    WHERE grade < 70 AND is_required
)

SELECT
    students.student_name,
    at_risk.*
FROM at_risk
JOIN students ON at_risk.student_id = students.id;

Common Table Expressions (CTE) menyimpan sementara hasil query untuk dipakai oleh query lain

Menyusun query agar lebih mudah dibaca, modular
Mendefinisikan dan memakai banyak CTE

Ayo berlatih!

Manipulasi Data di Snowflake