IFF, NVL, DECODE and DIV0
CASE WHEN works in Snowflake as everywhere, and Snowflake adds shorter forms you'll see in real code:
IFF(condition, then, else) is a one-test CASE.NVL(x, fallback) replaces NULL, like COALESCE with two values. ZEROIFNULL(x) is NVL(x, 0).DECODE(x, 'a', 'A', 'b', 'B', 'other') maps values to results, with a last value for everything else. Unlike CASE x WHEN NULL, DECODE does match NULL to NULL.DIV0(a, b) divides but returns 0 when b is 0, where a / b would stop the query with a Division by zero error.
These keep long SELECT lists readable, and they turn up constantly in Snowflake interview questions.
SELECT order_id,
IFF(order_total >= 300, 'large', 'small') AS size,
NVL(discount_code, 'NONE') AS code
FROM orders;
One test with IFF, a fallback for missing codes with NVL.