How to skip lazy evaluation/compiler optimizations to prevent hidden errors in Snowflake

Running the following query in Snowflake obviously produces a Division by zero error:

SELECT 1 / 0;

However, some derived queries don't always produce the error. For example, this query simply returns 1:

SELECT COUNT(*)
FROM (SELECT 1 / 0);

I understand this is due to lazy evaluation or some other compiler optimizations in Snowflake. The division is never performed, because Snowflake already knows the final output (number of rows).

However, in real use cases, this can effectively hide errors -- such as when running data tests with DBT (because the DBT executor automatically wraps test queries with a SELECT COUNT(*) FROM (...).

Can I force Snowflake to execute every portion of a query, or somehow skip the lazy evaluation/compiler optimizations?

EDIT: The division by zero is just a simplified example. There are many other things that can cause errors -- sometimes explicitly so, such as assertions.

Solution

Well, the solution proved to be quite simple! Just add a conditional statement targeting the desired portion, and Snowflake will be forced to execute that portion in order to evaluate the condition.

For example, this query will once again produce a Division by zero error:

SELECT COUNT(*)
FROM (
  SELECT (1 / 0) AS n
  WHERE n IS NOT NULL
);