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.
