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

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);