System Functions in Snowflake - Nimbus Intelligence

System Functions in Snowflake gives you the option to check your Snowflake instance and have information about it. All system functions have the syntax as SYSTEM$…. It can be a function call without any parameters or if you need you can add it into () after ‘$‘ sign.

There are 3 main types of System Functions:

Control Functions
Information Functions
Query Information Functions

Control Functions

Functions that allow you to execute certain immediate system changing actions such as cancelling a query or cancelling all queries. You can try these functions by yourself:

SELECT SYSTEM$CANCEL_ALL_QUERIES;                       --> cancel all queries

SELECT SYSTEM$CANCEL_QUERY( <query_id> );               --> cancel the query that you specified by id

SELECT SYSTEM$ABORT_SESSION( <session_id> );            --> abort session that you specified by id

CALL SYSTEM$WAIT(  <amount>, [<time_unit>] );           --> by default time_unit is SECONDS, this function is executed with 'CALL' syntax

These functions, as you can see, allow us to make certain actions on our Snowflake instance. There are many other options for these types of functions. You can find them from here.

Information Functions

Functions that return information about system. There are a lot of options for these types of functions, they range from functions that return you the type of the value you give as a paramter to functions that precalculate the search optimazation cost!

First you can try to run these codes in your Snowflake instance and then we will talk about the more interesting ones:

SELECT SYSTEM$TYPEOF( <any_object> );                                                  --> returns the type of the object

SELECT SYSTEM$CLUSTERING_INFORMATION( '<table_name>' , [ '(column, column, ...)']);        --> returns the clustering information about a specific table

SELECT SYSTEM$CLUSTERING_DEPTH( '<table_name>' , [ '(column, column, ...)']);             --> returns the clustering depth of a specific table

SELECT SYSTEM$GET_TAG( '<tag_name>' , '<obj_name>' , '<obj_domain>' );                    --> returns the tag of specified object

SELECT SYSTEM$GENERATE_SCIM_ACCESS_TOKEN('<integration_name>');                       --> generates a token for your integration, VALID FOR 6 MONTHS

SELECT SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS('<table_name>' [ , '<search_method_with_target>' ]);                             --> returns all of the details about search optimazation + if it is not enabled, how much will be the cost

SELECT SYSTEM$DATABASE_REFRESH_HISTORY(  ['<secondary_db_name>'] )                          --> returns the refresh history for database

As I mentioned before there are a lot of options on Information Functions, the ones about clustering information and depth are really interesting in my opinion. They give you the opportunity to see what’s happening behind the system.

I would say that ESTIMATE_SEARCH_OPTIMIZATION_COSTS() is another very interesting function. You should keep in mind that to use this function you must have your Snowflake instance as Enterprise or higher. It shows you the details and the cost informaiton about search optimizaton before you create it, so you can decide it afterwards to execute or not.

Query Information Functions

Functions that return information about queries. For example information about EXPLAIN plans.

You can run these examples on your Snowflake instance:

SELECT *
    FROM TABLE(GET_QUERY_OPERATOR_STATS(LAST_QUERY_ID()));          --> returns stats for that query operator

SELECT * SYSTEM$ESTIMATE_QUERY_ACCELERATION( '<query_id>' );          --> estimates query acceleration

Conclusion

There are many interesting System Functions in Snowflake and some of them can take your experience in Snowflake to another level. Using these functions may impact your work in a very professional and controlable way. Please don’t forget to check out this link for the full list of System Functions. Thank you for reading my article.