HOW TO: Apply Conditional Logic with Snowflake CASE (2024)

Conditional expressions are an important concept to understand if you want to make decisions based on certain conditions and criteria, and handle more complex logic. Snowflake offers loads of conditional expression functions to facilitate flexible logic implementation and handle different conditions. Among these functions, Snowflake CASE stands out as one of the most commonly used. Snowflake CASE is a function that returns a value based on a set of conditions. You can use it to compare values, check for nulls, perform calculations–and much more.

In this article, we'll cover everything you need to know about Snowflake CASE: how it works, its syntax, practical examples, alternatives to Snowflake CASE, the difference between IFF and Snowflake CASE, effective usage scenarios—and so much more!!

What Is CASE in Snowflake?

Snowflake CASE is a conditional expression function that returns a value based on a set of conditions. It is similar to the IF-THEN-ELSE logic in any programming language, but it is more concise as well as flexible.

Snowflake CASE statements evaluate conditions and return results accordingly. For example, they can categorize records into categories, transform values, replace NULLs, and much more. Thus, Snowflake CASE provides a flexible way to apply logical conditions without resorting to complex nested IF statements. You can use Snowflake CASE to perform tasks such as:

• Compare values and return different results based on the comparison
• Check for null values and replace ‘em with default or custom values
• Perform calculations and apply different formulas based on the input
• Handle errors/exceptions and return appropriate messages or actions

Snowflake CASE can help you handle various scenarios and outcomes in your analysis and decision-making process. It can also help you write more readable and maintainable code that can handle complex logic.

How Does a Snowflake CASE Statement Work?

Snowflake CASE statement evaluates a set of conditions and returns a value based on the first condition that is true. The syntax of a Snowflake CASE statement is relatively straightforward:

CASE
    WHEN <condition-1> THEN <result1>
  [ WHEN <condition-2> THEN <result2> ]
  [ ... ]
  [ ELSE <result3> ]
END

It works by evaluating condition-1, condition-2, and so on in sequence. If a condition evaluates to TRUE, the Snowflake CASE statement returns the result for that condition and stops further evaluation.

If no conditions match, it returns the result specified in the ELSE clause. If there is no ELSE, it returns NULL.

Let’s break down the key arguments of Snowflake CASE statement:

• <condition#>: An expression that evaluates to TRUE, FALSE or NULL
• <result#>: The value to return if the corresponding condition is TRUE
• [ELSE]: An optional clause that specifies a default value to be returned by the Snowflake CASE statement if none of the conditions match.

Snowflake CASE statements can also be used in a shorthand form where an expression is directly compared to each WHEN value:

CASE <expr>
    WHEN <value1> THEN <result1>
  [ WHEN <value2> THEN <result2> ]
  [ ... ]
  [ ELSE <result3> ]
END

Here, the <expression> is evaluated and compared to each <value> in order. The result of the first matching value is returned. This technique is useful for quick categorization or transformation based on matching values.

Let’s break down the key arguments of this technique:

• <condition#>: An expression that evaluates to TRUE, FALSE or NULL
• <expr>: A general expression that is evaluated against each value in the Snowflake CASE statement.
• <value>: A value that is compared with the <expr> in the Snowflake CASE statement. The value can be a literal or an expression and must be the same data type as the <expr>, or must be a data type that can be cast to the data type of the <expr>.
• <result#>: A value that is returned by the Snowflake CASE statement if the corresponding <exp> and <value> match.
• ELSE <else_result>: An optional clause that specifies a default value to be returned by the Snowflake CASE statement if none of the <expr> and <value> pairs match.

Now, let's dive into a simple example of how to use the Snowflake CASE statement:

First, we will create an orders table and insert some dummy data into it.

CREATE TABLE orders (
  order_id NUMBER,
  order_amount NUMBER
);

INSERT INTO orders VALUES 
  (1, 500),
  (2, 2500),
  (3, 10000),
  (4, 150),
  (5, 500),
  (6, 20000),
  (7, 15050),
  (8, 1000),
  (9, 5000),
  (10, 9000);

Now, let's utilize the Snowflake CASE to categorize orders into size buckets:

SELECT 
  order_id,
  CASE 
    WHEN order_amount < 1000 THEN 'Small'
    WHEN order_amount >= 1000 AND order_amount < 5000 THEN 'Medium' 
    WHEN order_amount >= 5000 THEN 'Large'
  END AS order_size
FROM orders;

As you can see, Snowflake CASE statement checks the order_amount column and categorie each record into Small, Medium, or Large size buckets based on the amount. This shows how Snowflake  CASE can classify data based on conditions.

What Is the Alternative to CASE  in Snowflake?

Snowflake CASE is not the only conditional expression function that you can use in Snowflake. An alternative to Snowflake CASE is Snowflake DECODE function. DECODE is a function that compares an expression with a list of values and returns a corresponding result. It is similar to the simple form of Snowflake CASE, but it uses a comma-separated list of arguments instead of keywords.

The syntax of a Snowflake DECODE is relatively straightforward:

DECODE(<expr>, <search1>, <result1>, <search2>, <result2>, ..., <searchN>, <resultN>, [<default>])

The arguments of Snowflake DECODE are:

• <expr>: An expression that is compared with each <search> value in the DECODE function. It can be any valid SQL expression, such as a column name, a constant, a function, or a subquery. It must have the same data type as the <search> values.
• <search>: A value that is compared with the <expr> in the DECODE function.It must have the same data type as the <expr>.
• <result>: A value that is returned by the DECODE function if the corresponding <expr> and <search> match. It can be any valid SQL expression, such as a column name, a constant, a function, or a subquery. It can have any data type, but it must be compatible with the data type of the <default> if specified.
• [<default>]: An optional argument that specifies a default value to be returned by the DECODE function if none of the <expr> and <search> pairs match.

The key things to know about DECODE vs CASE:

• DECODE returns the result of the first matching value
• Snowflake CASE evaluates all conditions, DECODE stops after the first match
• NULL values do not match in DECODE, but must be checked explicitly in CASE

Now, let's convert the same example we used earlier with the Snowflake CASE statement into the Snowflake DECODE function instead:

SELECT
  order_id,
  DECODE(order_amount, 
    order_amount < 1000, 'Small',
    order_amount >= 1000 AND order_amount < 5000, 'Medium',
    order_amount >= 5000, 'Large') AS order_size
FROM orders;

The syntax is slightly simpler for DECODE, but Snowflake CASE provides more flexibility for complex conditional logic.

How Snowflake CASE Statement Handle Null Values?

Snowflake CASE statements treat null values differently from other values, and you need to be aware of how they affect your results.

One of the most important things to remember is that null values do not match other null values in Snowflake CASE statements, meaning that if you have a condition that compares an expression with a null value, such as CASE expr WHEN NULL THEN result, it will never return true, even if the expression is also null. This is because null values are considered to be incomparable, and any comparison with a null value will result in a null value, not a true or false value.

Therefore, if you want to check for null values in your Snowflake CASE statements, you must do so explicitly, using the IS NULL or IS NOT NULL operators.

For example, if you want to return a result for null values, you can use a condition like CASE WHEN expr IS NULL THEN result. Similarly, if you want to exclude null values, you can use a condition like CASE WHEN expr IS NOT NULL THEN result. Here is an example of using a Snowflake CASE statement to handle null values:

SELECT
  order_id,
  DECODE(order_amount, 
    order_amount < 1000, 'Small',
    order_amount >= 1000 AND order_amount < 5000, 'Medium',
    order_amount >= 5000, 'Large') AS order_size
FROM orders;

Practical Examples of Snowflake CASE Statement

Now that we’ve covered the basics of Snowflake CASE syntax and usage, let’s look at some practical real-world examples:

Example 1 — Basic Usage of Snowflake CASE statement

In this very first example, we will use a Snowflake CASE statement to categorize orders and customers based on certain criteria.

Now, let's create a sample table and insert some dummy data into it.

CREATE TABLE orders (
  order_id INT,
  user_id INT,
  product_id INT,
  quantity INT,
  price DECIMAL(10,2)
);

-- Insert some data into the orders table
INSERT INTO orders VALUES
  (1, 1, 1, 5, 10.00),
  (2, 2, 2, 10, 20.00),
  (3, 3, 3, 15, 30.00),
  (4, 4, 4, 20, 40.00),
  (5, 5, 5, 25, 50.00);

-- Create a sample table for buyers
CREATE TABLE buyers (
  user_id INT,
  name VARCHAR,
  email VARCHAR,
  phone VARCHAR
);

-- Insert some data into the buyers table
INSERT INTO buyers VALUES
  (1, 'Chaos', 'Chaos@chaosgenius.io', '111-1111-111'),
  (2, 'Genius', 'Genius@chaosgenius.io', '222-2222-222'),
  (3, 'Elon', 'Elon@chaosgenius.io', '333-3333-333'),
  (4, 'Jeff', 'Jeff@chaosgenius.io', '444-4444-444'),
  (5, 'Larry', 'Larry@chaosgenius.io', '555-5555-555');

As you can see, the orders table contains information about the orders placed by the buyers, including the order ID, user ID, product ID, quantity, and price. Meanwhile, the buyers table contains information about the customers, including user ID, name, email, and phone.

a) Categorize Orders Based on Quantity

Let's start with the first example. Suppose we want to categorize the orders into three groups: Small, Medium, and Large, based on the quantity ordered. To do so, you can use the following query.

SELECT
  o.order_id,
  o.product_id,
  o.quantity,
  CASE
    WHEN o.quantity < 10 THEN 'Small'
    WHEN o.quantity BETWEEN 10 AND 20 THEN 'Medium'
    ELSE 'Large'
  END AS order_category
FROM orders o;

As you can see, we used Snowflake CASE statements to categorize the orders based on different criteria. We have also used the WHEN and ELSE keywords to specify the conditions and the results.

b) Categorize Buyers Based on Total Amount Spent

Now, let's move on to the next example where we categorize the buyers/customers into three groups: Bronze, Silver, and Gold, based on the total amount spent by the users/buyers. To accomplish this, we can use a Snowflake CASE statement, demonstrated below:

SELECT
  o.order_id,
  o.user_id,
  b.name,
  o.product_id,
  o.quantity,
  o.price,
  CASE 
    WHEN SUM(o.price * o.quantity) OVER (PARTITION BY o.user_id) < 100 THEN 'Bronze'
    WHEN SUM(o.price * o.quantity) OVER (PARTITION BY o.user_id) BETWEEN 100 AND 200 THEN 'Silver'
    ELSE 'Gold'
  END AS customer_category
FROM orders o
JOIN buyers b
ON o.user_id = b.user_id;

Example 2—Using nested CASE statements to create multi-level conditions

In some cases, you may want to create more complex conditions that involve multiple levels of logic. For example, you may want to check for different conditions based on the value of another condition. In such cases, you can use nested Snowflake CASE statements to create multi-level conditions.

A nested CASE statement is a CASE statement that is used within another Snowflake CASE statement. You can nest as many CASE statements as you need, as long as they are properly enclosed by the END keyword. You can use nested CASE statements to create more flexible and powerful conditional expressions.

Here is an example of using a nested Snowflake CASE statement:

But first, let's create a table called students and insert some dummy data into ‘em.

CREATE TABLE students (
  student_id INT,
  name VARCHAR,
  gender VARCHAR,
  age INT,
  grade DECIMAL(4,2)
);

INSERT INTO students VALUES
  (1, 'Elon', 'M', 18, 3.50),
  (2, 'Larry', 'M', 19, 3.00),
  (3, 'Jeff', 'M', 20, 2.50),
  (4, 'Warren', 'M', 21, 2.00),
  (5, 'Julia', 'F', 22, 1.50),
  (6, 'Sussan', 'F', 23, 2.50),
  (7, 'Mark', 'M', 20, 3.50),
  (8, 'Gina', 'F', 18, 1.50);

Now let's nest CASE statement to assign a scholarship amount based on the gender and grade of the student.

SELECT
  student_id,
  name,
  gender,
  age,
  grade,
  CASE gender
    WHEN 'F' THEN 
      CASE
        WHEN grade >= 3.5 THEN 1000
        WHEN grade >= 3.0 THEN 800 
        WHEN grade >= 2.5 THEN 600
        ELSE 0 
      END
    WHEN 'M' THEN
      CASE
        WHEN grade >= 3.5 THEN 500 
        WHEN grade >= 3.0 THEN 400
        WHEN grade >= 2.5 THEN 300 
        ELSE 0
      END
    ELSE NULL
  END AS scholarship
FROM students;

As you can see, we used a nested Snowflake CASE statement to assign a scholarship amount based on the gender and grade of the student. We have used two levels of CASE statements, one for the gender and one for the grade. We have used different criteria and values for each gender. Also, we implemented the ELSE clause to handle unknown or invalid values.

Example 3—Using subqueries within CASE statements

Now, let's move on to the second example. In this case, we'll use a Snowflake CASE statement to apply discounts based on product categories. But before that, let's create separate tables for products and discounts and insert some dummy data into ‘em.

CREATE TABLE products (
  product_id INT,
  name VARCHAR,
  category VARCHAR,
  cost DECIMAL(10,2),
  price DECIMAL(10,2)
);

INSERT INTO products VALUES
  (1, 'Xbox', 'Console', 400.50, 0.20),
  (2, 'PS5', 'Console', 480.40, 0.20),
  (3, 'Acer Predator', 'Laptop', 1000.30, 0.15),
  (4, 'Sony Bravia', 'TV', 10000.20, 0.30),
  (5, 'Samsung Galaxy', 'Mobile', 100.10, 0.25),
  (6, 'Dell Latitude', 'Laptop', 500.10, 0.15),
  (7, 'Apple Iphone', 'Mobile', 1200.10, 0.25);

CREATE TABLE discounts (
  category VARCHAR,
  discount DECIMAL(4,2)
);

INSERT INTO discounts VALUES
  ('Console', 0.20),
  ('Laptop', 0.15), 
  ('TV', 0.30),
  ('Mobile', 0.25);

As you can see, products table contains information about the products, such as the product ID, the name, the category, the cost, and the price and the discounts table contains information about the discounts, such as the category and the discount percentage.

Now, let's use Snowflake CASE statement to apply discounts based on the product categories and calculate the final price and profit for each product. To achieve this, you can utilize the following query:

SELECT
  p.product_id,
  p.name,
  p.category,
  p.cost, 
  p.price,
  -- Lookup discount percentage for each category
  CASE p.category
    WHEN 'Console' THEN d.discount 
    WHEN 'Laptop' THEN d.discount
    WHEN 'TV' THEN d.discount
    WHEN 'Mobile' THEN d.discount
    ELSE 0 
  END AS discount,
-- Calculate final price after applying discount 
  p.price * (1 - 
    CASE p.category
      WHEN 'Console' THEN d.discount
      WHEN 'Laptop' THEN d.discount
      WHEN 'TV' THEN d.discount 
      WHEN 'Mobile' THEN d.discount
      ELSE 0
    END) AS final_price,
-- Calculate profit after discount
  (p.price - p.cost) * (1 - 
    CASE p.category
      WHEN 'Console' THEN d.discount
      WHEN 'Laptop' THEN d.discount
      WHEN 'TV' THEN d.discount
      WHEN 'Mobile' THEN d.discount  
      ELSE 0
    END) AS profit
FROM products p
LEFT JOIN discounts d
  ON p.category = d.category;

Example 4—Handling null values and errors within CASE Statement

Earlier, we saw how to handle null values in Snowflake CASE statements by using the IS NULL or IS NOT NULL operators. But, sometimes you may encounter other types of errors or exceptions in your analysis process.

Here is an example of using a Snowflake CASE statement to handle NULLs:

Firstly, we will start by creating a table called sales, and then we will insert some dummy data into it.

CREATE TABLE sales (
  product_id INT, 
  quantity INT,
  price DECIMAL(10,2)
);

INSERT INTO sales VALUES  
(1, 10, 100.00),
(2, 20, 200.00), 
(3, 0, 300.00),  
(4, NULL, 400.00),
(5, NULL, 500.00);

Now, let's use Snowflake CASE statement to handle NULL values. To do so, you must do so explicitly, using the IS NULL or IS NOT NULL operators.

SELECT
  product_id,
  quantity,
  price,
    CASE 
        WHEN quantity IS NULL OR quantity = 0 THEN 'No product available'
        ELSE CAST((quantity * price) AS STRING)
    END AS Total_order_price
FROM sales;

What Is the Difference Between Snowflake IFF and Snowflake CASE?

IFF is another Snowflake conditional expression function similar to Snowflake CASE. The key differences are:

When to Use Snowflake CASE Statement?

Snowflake CASE statement is a powerful and versatile function that can help you perform various tasks in data analysis and decision-making. You can use Snowflake CASE statement in many situations, such as:

1) Avoiding Nested IFs Statements

When dealing with multiple conditions, using nested IFs statements can become confusing. Snowflake CASE statement offers a solution to simplify your logic, making your code more readable and maintainable. It replaces nested IF statements with a single expression capable of handling various scenarios and outcomes.

2) Classifying Data Into Categories

For large, datasets requiring grouping or labeling based on specific criteria, Snowflake CASE statement helps in creating categories and assigning values. It enables comparisons between values, returning different results based on the comparison. On top fo that, it also facilitates creating ranges or intervals and delivering distinct outcomes based on these ranges.

3) Replace Missing Values and Handling Nulls

If you have a data set that contains missing or unknown values, Snowflake CASE statement can help you deal with them and avoid errors or exceptions. You can use Snowflake CASE statement to check for null values and replace them with default or custom values. You can also use Snowflake CASE statement to handle errors and exceptions and return appropriate values or messages.

4) Complex Data Transformations and Multi-Step Calculations

For datasets necessitating intricate transformations or multi-step calculations involving various conditions, Snowflake CASE statement can help you perform them efficiently and accurately. You can use Snowflake CASE statement to evaluate expressions and return values based on whether they are true or false.  It also facilitates calculations, applying different formulas based on input. You can also use nested Snowflake CASE statements to create multi-level conditions and logic.

Conclusion

And that’s a wrap! Snowflake CASE statement is a flexible and powerful function that can help you write more concise and elegant code that can handle various scenarios and outcomes.  It manages conditional logic in a highly versatile way without convoluted nested IFs. Thus, a thorough understanding of how to apply CASE effectively can significantly enhance code efficiency and readability.

In this article, we have covered:

• What Is Snowflake CASE Statement?
• How Does a Snowflake CASE Statement Work?
• What Is the Alternative to CASE  in Snowflake?
• How Snowflake CASE Statement Handles Null Values?
• Practical Examples of Snowflake CASE Statement
• What Is the Difference Between IFF and Snowflake CASE?
• When to Use Snowflake CASE Statement?

—and so much more!!

Using Snowflake CASE is like having an advanced multi-level decision maker that can route data and logic based on any criteria you define. Think of CASE statements as powerful SQL switches that go far beyond basic IF-THEN logic.

FAQs

What is a Snowflake CASE statement?

Snowflake CASE is a conditional expression in Snowflake that allows you to perform different computations based on certain conditions.

How does CASE work in Snowflake?

Snowflake CASE evaluates conditions in sequence and returns the result of the first matching condition. An optional ELSE clause specifies a default result.

Can I use Snowflake CASE in a SELECT query in Snowflake?

Yes, Snowflake CASE can be used in SELECT, INSERT, UPDATE and other statements anywhere an expression is valid.

How do I check for NULL values in a CASE statement?

You can use IS NULL or IS NOT NULL to explicitly check for nulls, as nulls do not match other nulls in CASE.

Can I nest Snowflake CASE statements in Snowflake?

Yes, you can nest Snowflake CASE statements to create multi-level conditional logic.

What is an alternative to CASE in Snowflake?

DECODE is an alternative that compares an expression to a list of values to return a match.

When should I use Snowflake CASE instead of DECODE in Snowflake?

Snowflake CASE provides more flexibility for complex logic with multiple conditions. DECODE is simpler for basic value matching.

What is the difference between CASE and IFF in Snowflake?

CASE evaluates all conditions, IFF evaluates only the first match. CASE can check multiple values, IFF cannot.

How can I handle errors with Snowflake CASE?

Always check for errors and exceptions explicitly in Snowflake CASE conditions, and return appropriate messages.

Can Snowflake CASE improve performance ?

Snowflake CASE can improve readability over nested IFs. However, joining may be faster than complex Snowflake CASEs.

What are some common uses of CASE in Snowflake?

Data transformations, conditional aggregation, pivoting, error handling, and business logic.

What data types can I use with Snowflake CASE?

Snowflake CASE results and return values can be any Snowflake data type.

Is Snowflake CASE statement support standard SQL?

Yes, CASE conditional expressions are part of the ANSI SQL standard.

Are there any limitations with Snowflake CASE?

No major limitations. Just watch for performance with over complex logic.

Can I use subqueries in a Snowflake CASE?

Yes, Snowflake supports using subqueries in CASE conditions and return values.