Snowflake Scripting 101: Error Handling

  • Post author:
  • Post last modified:January 22, 2024
  • Post category:Snowflake
  • Reading time:11 mins read

In my other articles, we have talked about how to use basic blocks in Snowflake scripting, how to use Snowflake cursors and Snowflake scripting control structures. In this article, we will check handling error in Snowflake scripting with some relevant examples.

Snowflake Scripting Error Handling

Page Contents

Introduction

Snowflake, a cloud-based data platform, offers powerful scripting capabilities for efficient data management and analysis. Snowflake supports SQL and JavaScript as programming languages for creating stored procedures and user-defined functions. Initially, only JavaScript was supported for creating stored procedures, but recent developments have introduced support for Snowflake script, closely resembling the PL/SQL programming language found in databases like Oracle and Teradata.

Within a Snowflake Scripting block, it is possible to raise an exception when an error occurs. Moreover, you have the capability to manage exceptions that arise within your Snowflake Scripting code.

Understanding Errors in Snowflake

Snowflake Scripting throws exceptions on execution errors, such as dropping non-existent tables. An exception prevents the next lines of code from executing. However, within code blocks, you can define exception handlers to capture specific exceptions (both built-in and custom) and provide custom behavior in response.

Snowflake Scripting utilizes exception handlers to manage errors (e.g., syntax issues, missing tables). Upon an exception, handlers are sought in a hierarchical manner:

  • Local layer: Within the block where the error occurred, if a matching handler exists, execution resumes with the handler.
  • Enclosing layers: If no local handler is found, Snowflake ascends through enclosing blocks, seeking a matching handler at each level.
  • Outermost layer: If no handler is found throughout the script, the exception becomes an uncaught error, reported to the client application.

An exception handler can contain its own exception handler in case an exception occurs while handling another exception.

Handling an Exception in Snowflake Scripting

Withing the Snowflake scripting block, you can explicitly handle an exception by catching it with an EXCEPTION clause, or the block will pass the exception on to the enclosing block.

Within the EXCEPTION clause, you use a WHEN clause to handle an exception by name. You can handle exceptions that you declare as well as built-in exceptions.

Currently, Snowflake scripting supports following built-in exceptions:

  • STATEMENT_ERROR: This exception indicates an error during the execution of a statement. For instance, if you try to drop a table that does not exist, this exception will be raised.
  • EXPRESSION_ERROR: This exception points to an error associated with an expression. For instance, if you generate an expression that results in a VARCHAR and try to assign this expression’s value to a FLOAT, this error will be raised.

Built-in Variables in Snowflake Error Handling using EXCEPTION

When an exception occurs during execution, you can get more detailed information about the exception by reading the following three built-in variables:

  • SQLCODE: This is a 5-digit signed integer. For user-defined exceptions, this is the exception_number.
  • SQLERRM: This is an error message. For user-defined exceptions, this is the exception_message.
  • SQLSTATE: This is a 5-character code modeled on the ANSI SQL standard SQLSTATE . Snowflake uses additional values beyond those in the ANSI SQL standard.

Declaring an User-Defined Exception in Snowflake

Snowflake scripting allows you to declare your own exception in the DECLARE section of the block.

For example,

DECLARE
  my_exception EXCEPTION (-20002, 'MY_EXCEPTION - Custom Exception.');

Raising a Declared Exception in Snowflake

To raise a custom exception, use the RAISE command. 

RAISE my_exception;

Basic Snowflake Error Handling Examples

You can raise and handle the errors in both Snowflake anonymous block and Snowflake scripting stored procedures.

Snowflake Anonymous Block with Error Handling Examples

The following Snowflake scripting anonymous block uses the RAISE command to raise custom exception based on the flag. It also uses EXCEPTION WHEN clause within block to catch the raised exception.

DECLARE
  my_exception EXCEPTION (-20002, 'MY_EXCEPTION - Custom Exception.');
BEGIN
  LET counter := 0;
  LET raise_exception_flag := true;
  IF (raise_exception_flag) THEN
    RAISE my_exception;
  END IF;
  counter := counter + 1;
  RETURN counter;
EXCEPTION
  WHEN statement_error THEN
    RETURN OBJECT_CONSTRUCT('Error type', 'STATEMENT_ERROR',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate);
  WHEN expression_error THEN
    RETURN OBJECT_CONSTRUCT('Error type', 'expression_error',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate);							
  WHEN my_exception THEN
    RETURN OBJECT_CONSTRUCT('Error type', 'MY_EXCEPTION',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate);
  WHEN OTHER THEN
    RETURN OBJECT_CONSTRUCT('Error type', 'Other error',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate);
END;

Snowflake Stored Procedure with Error Handling Examples

The following Snowflake scripting stored procedure uses the RAISE command to raise custom exception based on the flag. It also uses EXCEPTION WHEN clause within procedure block to catch the raised exception.

create or replace procedure exception_handling_test(param1 int)
  returns VARIANT
  language sql
  as
  $$
  
	DECLARE
	  my_exception EXCEPTION (-20002, 'MY_EXCEPTION - Custom Exception.');
	BEGIN
	  LET counter := 0;
	  LET raise_exception_flag := true;
	  IF (raise_exception_flag) THEN
		RAISE my_exception;
	  END IF;
	  counter := counter + 1;
	  RETURN counter;
	EXCEPTION
	  WHEN statement_error THEN
		RETURN OBJECT_CONSTRUCT('Error type', 'STATEMENT_ERROR',
								'SQLCODE', sqlcode,
								'SQLERRM', sqlerrm,
								'SQLSTATE', sqlstate);
	  WHEN expression_error THEN
		RETURN OBJECT_CONSTRUCT('Error type', 'expression_error',
								'SQLCODE', sqlcode,
								'SQLERRM', sqlerrm,
								'SQLSTATE', sqlstate);							
	  WHEN my_exception THEN
		RETURN OBJECT_CONSTRUCT('Error type', 'MY_EXCEPTION',
								'SQLCODE', sqlcode,
								'SQLERRM', sqlerrm,
								'SQLSTATE', sqlstate);
	  WHEN OTHER THEN
		RETURN OBJECT_CONSTRUCT('Error type', 'Other error',
								'SQLCODE', sqlcode,
								'SQLERRM', sqlerrm,
								'SQLSTATE', sqlstate);
	END;

  $$;

Conclusion

In conclusion, this guide helps into the crucial aspect of error handling in Snowflake scripting, covering its fundamentals, built-in functions, user-defined exceptions, and practical implementation through examples in both anonymous blocks and stored procedures. By following the guide, you will be able to handle unexpected situations efficiently.

Related Articles,

Hope this helps 🙂

Tags: , ,