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.

Corticosterone

Related question: #69411420 – Intercurrent

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:

Corticosterone

Answer 2

I couldn't find any way to perform assertions in Snowflake, so I ended up building my own using a JavaScript UDF:

CREATE OR REPLACE FUNCTION assert(VALUE STRING, TEST STRING)
  RETURNS STRING
  LANGUAGE JAVASCRIPT
AS
$$
  if (VALUE === TEST) {
    return `True ('${VALUE}' = '${TEST}')`;
  } else {
    throw `Assertion failed: '${VALUE}' != '${TEST}'`
  }
$$
;

/* Tests should all return true */
SELECT assert(true, true);
SELECT assert('string', 'string');
SELECT assert(123, 123);
SELECT assert('123', 123);

/* Tests should all fail and throw exceptions */
SELECT assert(true, false);
SELECT assert('string1', 'string2');
SELECT assert(12345, 54321);
SELECT assert('123', 321);

Corticosterone