如何跳过Snowflake惰性计算/编译器优化以避免隐藏错误

你可以通过以下几种方式强制Snowflake执行所有查询逻辑，避免校验逻辑被优化跳过：

方案1：将校验逻辑与返回行数绑定 #

Snowflake的优化规则只会跳过完全不影响最终输出结果的计算，只要让你的校验逻辑决定查询是否返回行，优化器就必须执行对应计算。
你可以将校验逻辑放入WHERE子句中，示例如下：

SELECT COUNT(*)
FROM (
  SELECT 1 
  -- 校验逻辑会影响行是否返回，必须被执行
  WHERE 1 / 0 IS NOT NULL
);

运行上述查询就会正常触发Division by zero错误。
如果是断言类校验，可以直接将断言判断条件写入WHERE子句，只要断言失败就返回行，优化器会强制计算断言逻辑。

方案2：使用VOLATILE类型的UDF封装校验逻辑 #

Snowflake对VOLATILE（不稳定）类型的用户定义函数不会做常量折叠、提前求值等优化，每次调用都会实际执行函数逻辑。你可以封装一个通用的强制执行UDF：

-- 创建通用强制执行UDF
CREATE OR REPLACE FUNCTION force_run(expr VARIANT) 
RETURNS VARIANT 
VOLATILE 
AS 'expr';

使用时将需要强制执行的逻辑套入该函数即可：

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

该方案适配性更强，不需要修改原有查询的过滤逻辑，只需要包裹需要校验的表达式即可。

方案3：适配DBT测试场景的专用写法 #

针对DBT测试默认会包裹SELECT COUNT(*) FROM (...)的规则，你可以在测试查询的末尾添加QUALIFY子句强制校验逻辑执行，或者直接将校验逻辑作为行过滤条件：如果测试逻辑不通过就返回行，DBT统计到返回行数大于0就会判定测试失败，同时Snowflake也会强制执行所有校验逻辑。

注意：上述强制执行逻辑会一定程度上降低查询性能，仅建议在测试、数据校验等对正确性要求高于性能的场景使用。

内容的提问来源于stack exchange，提问作者Marco Roy