Assertions in Snowflake

Question

Is there a way to perform assertions in Snowflake?

Basically, I'm trying to do a bit of testing/TDD, and I'd like a mechanism similar to assertions in every other language:

• If the assertion succeeds, it returns true (and/or prints a success message).
• If the assertion fails, an exception is raised.

Answer 1

We have since moved away from the assert UDF, and instead we now compare the expected & actual values directly in SQL. It has the advantage of avoiding type casting from SQL to JavaScript, thus being more accurate.

SELECT
  'something' AS actual,
  'something_else' AS expected
FROM ...
WHERE actual != expected
   AND ('~\n\nERROR: unexpected result: ' || actual
     || '\n\nExpected: ' || expected || '\n\n~');

And in the case of aggregate values, we simply use HAVING instead of WHERE.

The AND condition will always fail (since it is not a boolean value) if the two values are indeed not equal, but boolean logic will ignore it if the two values are equal (and hence failing the first condition, thus returning no results).

The two ~ are simple separator. It could be anything else.

In Snowsight, the output of an error looks like this:

And in DBT, the output looks like this: