How lazy is the Snowflake SQL compiler and execution?

I wanted to share my own experiments on this regard. Lazy evaluation is very useful for optimization purposes, and also for testing. I couldn't find documentation promising this behavior, so these experiments help determine current behavior

Throwing exceptions for tests

These potential division by 0 exceptions are not thrown:

select coalesce(1, 1/0), iff(true, 1, 1/0);

This is very useful when writing a sql script to verify results. For example, to verify if a query brings back 3 rows or throw an exception:

select iff(count(*)=3, 1, 1/0)
from (
    select * from values(1),(2),(3)
);

CTE optimization

Let's find out if Snowflake optimizes CTEs, or if they get evaluated no matter what:

with oh_no as (
  select 1/0 oh_no
), fine as (
  select 1 fine
)

select *
from fine
;

The results are good: That CTE doesn't throw an exception, as it's not evaluated as it's not needed.

Extreme laziness

This is an interesting one:

with oh_no as (
  select 1/0 oh_no
), fine as (
  select 1 fine
)

select *
from (
    select * from fine
    union all 
    select * from oh_no
)
limit 1
;

In theory an exception should be thrown out of the union. But Snowflake sees that the LIMIT 1 has been evaluated, and it doesn't waste time processing further rows.

This means the same query might throw errors depending on the order in which rows are processed, and the errors won't be thrown if processing an initial number of rows satisfies the query requirements.

But all these examples are constants?

As @MatBailie notes, all of these examples are constant so they could be optimized before being executed.

Let me add this example for testing the results of a query over an actual table:

select iff(count(*)=7, 1, 1/0)
from (
    select *
    from snowflake_sample_data.tpch_sf001.customer
    where c_phone like '18-8%'
);

The exception is not thrown, as the table has exactly 7 records that match the condition. Good.

Same for the extreme laziness example, with actual data:

with oh_no as (
    select 1/0 oh_no
), fine as (
    select c_custkey
    from snowflake_sample_data.tpch_sf001.customer
    where c_phone like '18-8%'
    limit 1
)

select *
from (
    select * from fine
    union all 
    select * from oh_no
)
limit 1
;