Snowflake SQL Tutorial 82 - CURRENT_USER, CURRENT_DATABASE, IDENTIFIER - Context Functions

Snowflake SQL · Episode 82

In this post we are going to cover the context functions in Snowflake, and IDENTIFIER.

The context functions answer three questions: who am I, what can I do, and where is this query actually running. They take no arguments and they describe the session rather than any table, so almost every result on this page is a single row.

We will start with who and build it up step by step. On the way we will find the cheapest safety net in this whole course — one line in a WHERE clause that turns a destructive statement into a harmless one — and a mistake with IDENTIFIER that will not even compile.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 82 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

1. Let's set up a session to work in
2. Let's start with who we are
3. Now the three different role questions
4. Can we ask whether a role is in the session?
5. Now let's ask where we are
6. Why does any of that matter?
7. Let's write a pre-flight check
8. Now let's put the guard in the WHERE clause
9. Let's look at the session and the statement
10. Is the session id stable?
11. Can we get back to a result we forgot to save?
12. Now the audit column pattern
13. Let's build a table name out of the context
14. So let's fix it with a session variable
15. One last thing: IDENTIFIER with a plain literal

Let's set up a session to work in

The last part of this post needs the ORDERS and CUSTOMERS tables that the rest of the course uses, so let's point the worksheet at them first.

Sample data

-- ORDERS and CUSTOMERS come from the setup script this course uses
-- throughout. Run it once; every episode reuses the same tables.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

Before anything else, let's ask Snowflake which Snowflake we are talking to.

Query

SELECT CURRENT_VERSION()   AS SNOWFLAKE_VERSION,
       CURRENT_CLIENT()    AS CLIENT_,
       CURRENT_IP_ADDRESS() IS NOT NULL AS IP_IS_KNOWN;

Result1 row · 3 columns

SNOWFLAKE_VERSION	CLIENT_	IP_IS_KNOWN
10.34.101	PythonConnector 4.7.5	TRUE

Version 10.34.101. That is the version these captures were taken on, and it is the version the footer of this post quotes.

The client is the Python connector, because these captures are run by a script rather than typed into a worksheet. Yours will say a web interface or a driver instead.

That is the shape of most of what follows: a question with no arguments at all, and one row of answer about this session.

Let's start with who we are

Four functions, four answers. The user, the role the query runs as, the account, and the region it lives in.

Query

SELECT CURRENT_USER()    AS USER_,
       CURRENT_ROLE()    AS ROLE_,
       CURRENT_ACCOUNT() AS ACCOUNT_,
       CURRENT_REGION()  AS REGION_;

CURRENT_USER() gives the login name. CURRENT_ROLE() gives the primary role, which is the one your privileges come from right now. CURRENT_ACCOUNT() gives the account locator, and CURRENT_REGION() gives the cloud and region it is hosted in.

Why there is no grid under that query

There is no result grid under that query, and there are two more like it further down this post. Every other grid in this series is real captured output, and these would be too — but CURRENT_ACCOUNT() returns this account's locator and CURRENT_USER() returns its login name, and both of those are boxed out of the video and replaced with [REDACTED] in the demo script you can download. A blog post is no more private than a video.

So where a cell holds one of those two values, the query is here and the grid is not. Nothing has been substituted for it. There is no invented value anywhere in this course, and a missing grid is a cheaper price than a made-up one. Run the query yourself and you will get your own four answers, which is the whole point of these functions.

What we can show is the shape of those answers, because these are easy to assume things about. Let's check that they are set, that the user name is not an empty string, and what type comes back.

Query

SELECT CURRENT_USER() IS NOT NULL      AS USER_IS_SET,
       CURRENT_ROLE() IS NOT NULL      AS ROLE_IS_SET,
       CURRENT_ACCOUNT() IS NOT NULL   AS ACCOUNT_IS_SET,
       LENGTH(CURRENT_USER()) > 0      AS USER_IS_NON_EMPTY,
       TYPEOF(CURRENT_USER()::VARIANT) AS USER_TYPE;

Result1 row · 5 columns

USER_IS_SET	ROLE_IS_SET	ACCOUNT_IS_SET	USER_IS_NON_EMPTY	USER_TYPE
TRUE	TRUE	TRUE	TRUE	VARCHAR

All three are set, the user name is not empty, and TYPEOF says VARCHAR. So they are ordinary strings. You can compare them, concatenate them and store them in a column.

Now the three different role questions

There are three separate questions about roles, and people run them together. Let's ask all three at once.

Query

SELECT CURRENT_ROLE()                AS PRIMARY_ROLE,
       CURRENT_SECONDARY_ROLES()     AS SECONDARY_ROLES,
       CURRENT_AVAILABLE_ROLES()     AS AVAILABLE_ROLES;

Result1 row · 3 columns

PRIMARY_ROLE	SECONDARY_ROLES	AVAILABLE_ROLES
ACCOUNTADMIN	{"roles":"ORGADMIN","value":"ALL"}	[ "ACCOUNTADMIN", "ORGADMIN", "PUBLIC", "SECURITYADMIN", "SNOWFLAKE_LEARNING_ROLE", "SYSADMIN", "USERADMIN" ]

CURRENT_ROLE() is the primary role. One value, and it is what the query runs as.

CURRENT_SECONDARY_ROLES() is a different thing, and look at the shape of it. It is JSON, with a roles key and a value key.

CURRENT_AVAILABLE_ROLES() is everything this user could switch to. There are 7 of them in this account.

Now let's look at the types, because this is the part that catches people.

Query

SELECT TYPEOF(CURRENT_ROLE()::VARIANT)             AS PRIMARY_TYPE,
       TYPEOF(CURRENT_SECONDARY_ROLES()::VARIANT)  AS SECONDARY_TYPE,
       TYPEOF(CURRENT_AVAILABLE_ROLES()::VARIANT)  AS AVAILABLE_TYPE,
       ARRAY_SIZE(PARSE_JSON(CURRENT_AVAILABLE_ROLES())) AS HOW_MANY_AVAILABLE;

Result1 row · 4 columns

PRIMARY_TYPE	SECONDARY_TYPE	AVAILABLE_TYPE	HOW_MANY_AVAILABLE
VARCHAR	VARCHAR	VARCHAR	7

All three are VARCHAR. Even the one that looks like an array. It is text that happens to be shaped like JSON, so to count it you have to parse it first.

That last column does exactly that: ARRAY_SIZE(PARSE_JSON(...)) gives 7. Now it is really an array and now the number is real.

Can we ask whether a role is in the session?

We can, and this is the one you actually want in a check. IS_ROLE_IN_SESSION takes a role name and gives back true or false.

Let's try our own role, a role everybody has, and a role that does not exist at all.

Query

SELECT CURRENT_ROLE()                          AS MY_ROLE,
       IS_ROLE_IN_SESSION(CURRENT_ROLE())      AS MY_OWN_ROLE_IS_IN_SESSION,
       IS_ROLE_IN_SESSION('PUBLIC')            AS PUBLIC_IS_IN_SESSION,
       IS_ROLE_IN_SESSION('NO_SUCH_ROLE_XYZ')  AS A_ROLE_THAT_DOES_NOT_EXIST;

Result1 row · 4 columns

MY_ROLE	MY_OWN_ROLE_IS_IN_SESSION	PUBLIC_IS_IN_SESSION	A_ROLE_THAT_DOES_NOT_EXIST
ACCOUNTADMIN	TRUE	TRUE	FALSE

Our own role is TRUE. PUBLIC is TRUE. And the role that does not exist is FALSE.

Not an error. FALSE. That is exactly what you want in a guard, because a guard that throws is a guard you end up taking out.

Now let's ask where we are

Three more functions for the other half of the question: which warehouse is running this, and which database and schema is it looking in. Plus CURRENT_SCHEMAS(), which is the search path.

Query

SELECT CURRENT_WAREHOUSE() AS WAREHOUSE_,
       CURRENT_DATABASE()  AS DATABASE_,
       CURRENT_SCHEMA()    AS SCHEMA_,
       CURRENT_SCHEMAS()   AS SEARCH_PATH;

Result1 row · 4 columns

WAREHOUSE_	DATABASE_	SCHEMA_	SEARCH_PATH
COMPUTE_WH	DEMO_DB	PUBLIC	["DEMO_DB.PUBLIC"]

A warehouse, the DEMO_DB database, the PUBLIC schema, and a search path holding the one schema.

Can any of those be NULL? Let's ask, and put an NVL beside each one while we are there.

Query

SELECT CURRENT_WAREHOUSE() IS NULL AS NO_WAREHOUSE_SET,
       CURRENT_DATABASE() IS NULL  AS NO_DATABASE_SET,
       CURRENT_SCHEMA() IS NULL    AS NO_SCHEMA_SET,
       NVL(CURRENT_WAREHOUSE(), '<none>') AS WAREHOUSE_OR_NONE,
       NVL(CURRENT_DATABASE(), '<none>')  AS DATABASE_OR_NONE;

Result1 row · 5 columns

NO_WAREHOUSE_SET	NO_DATABASE_SET	NO_SCHEMA_SET	WAREHOUSE_OR_NONE	DATABASE_OR_NONE
FALSE	FALSE	FALSE	COMPUTE_WH	DEMO_DB

In this session, none of them are null. But they can be. A session with no warehouse set returns NULL rather than an error, and so does one with no database.

So wrap them. If you write CURRENT_WAREHOUSE() straight into an audit column and it happens to be null, the column is simply blank, and nothing tells you whether that means no warehouse or nobody filled this in.

Why does any of that matter?

Here is why. Watch this query, which is about as ordinary as SQL gets.

Query

SELECT COUNT(*) AS VIA_SESSION_CONTEXT FROM CUSTOMERS;

Result1 row · 1 column

VIA_SESSION_CONTEXT
10

10 customers. No database in the query, no schema, just the table name — and it found that table only because of the context we were just looking at.

The exact same statement, in a session pointed somewhere else, hits a different table. Or no table at all.

The text of the query did not change. The answer did. That is not a hypothetical. That is how people update production from a dev window.

Let's write a pre-flight check

The readable version first. Where am I running, is a database even set, and a plain-English verdict column.

Query

SELECT CURRENT_DATABASE()                       AS RUNNING_IN,
       CURRENT_DATABASE() IS NOT NULL           AS A_DATABASE_IS_SET,
       IFF(CURRENT_DATABASE() IS NOT NULL, 'safe to run', 'REFUSE') AS VERDICT,
       CURRENT_ROLE()                           AS AS_ROLE;

Result1 row · 4 columns

RUNNING_IN	A_DATABASE_IS_SET	VERDICT	AS_ROLE
DEMO_DB	TRUE	safe to run	ACCOUNTADMIN

DEMO_DB, TRUE, safe to run. That is something you can put at the top of a script and read before you run the rest of it.

It is useful, but it still relies on somebody reading it. The better version does not.

Now let's put the guard in the WHERE clause

This is the cheapest safety net in the whole course. Put the check in the WHERE clause, so the statement itself cannot do anything in the wrong place.

First, in the right database.

Query

SELECT COUNT(*) AS ROWS_THAT_WOULD_BE_TOUCHED
FROM ORDERS
WHERE CURRENT_DATABASE() = CURRENT_DATABASE();

Result1 row · 1 column

ROWS_THAT_WOULD_BE_TOUCHED
41

41 rows. All of them, obviously — that condition is always true when it compares the database to itself.

Now the same statement, comparing against a database name that is not this one.

Query

SELECT COUNT(*) AS ROWS_THAT_WOULD_BE_TOUCHED
FROM ORDERS
WHERE CURRENT_DATABASE() = 'A_DATABASE_THAT_IS_NOT_THIS_ONE';

Result1 row · 1 column

ROWS_THAT_WOULD_BE_TOUCHED
0

0 rows. The query ran. It found the table. And it touched nothing.

Now imagine that WHERE clause on a DELETE or an UPDATE. In the right database it does exactly what you meant. In the wrong one it is a no-op, silently and safely.

The habit worth having

That is what fails closed means, and it is the only useful direction for a guard to fail. It costs one line. Put it on anything destructive, and use IS_ROLE_IN_SESSION the same way when the risk is the role rather than the database.

Let's look at the session and the statement

A few identity functions next. The session, the transaction, the statement we are running, and the id of the statement before it.

Query

SELECT CURRENT_SESSION()        AS SESSION_,
       CURRENT_TRANSACTION()    AS TRANSACTION_,
       CURRENT_STATEMENT()      AS THIS_STATEMENT,
       LAST_QUERY_ID()          AS PREVIOUS_QUERY_ID;

Result1 row · 4 columns

SESSION_	TRANSACTION_	THIS_STATEMENT	PREVIOUS_QUERY_ID
4761357794795610	NULL	SELECT CURRENT_SESSION() AS SESSION_, CURRENT_TRANSACTION() AS TRANSACTION_, CURRENT_STATEMENT() AS THIS_STATEMENT, LAST_QUERY_ID() AS PREVIOUS_QUERY_ID	01c74c78-0108-9b8a-0010-ea6e00054dbe

CURRENT_STATEMENT() gives back the text of the query you are running, which is genuinely useful in logging — you can see it in the grid, quoting itself.

CURRENT_TRANSACTION() is NULL here, because this statement is not inside an explicit transaction. That is the normal answer, not a problem.

Is the session id stable?

Worth testing rather than assuming, because a function that changes per row and a function that changes per session look identical when your result has one row in it.

Let's generate a thousand rows and count the distinct answers.

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT(DISTINCT CURRENT_SESSION()) AS DISTINCT_SESSION_IDS,
       COUNT(DISTINCT CURRENT_USER())    AS DISTINCT_USERS
FROM TABLE(GENERATOR(ROWCOUNT => 1000));

Result1 row · 3 columns

ROWS_TOTAL	DISTINCT_SESSION_IDS	DISTINCT_USERS
1000	1	1

1000 rows, 1 distinct session id, 1 distinct user. It is a session value, not a row value, so it is evaluated once and reused.

That is the same behaviour as the clock functions back in Episode 72.

Can we get back to a result we forgot to save?

Sometimes. LAST_QUERY_ID() gives the id of the previous statement, and it takes an optional offset. Let's ask for the default, then minus one, then minus two.

Query

SELECT LAST_QUERY_ID()   AS MOST_RECENT,
       LAST_QUERY_ID(-1) AS ALSO_MOST_RECENT,
       LAST_QUERY_ID(-2) AS THE_ONE_BEFORE,
       LAST_QUERY_ID() IS NOT NULL AS IT_IS_SET;

Result1 row · 4 columns

MOST_RECENT	ALSO_MOST_RECENT	THE_ONE_BEFORE	IT_IS_SET
01c74c78-0108-9e01-0010-ea6e0005f706	01c74c78-0108-9e01-0010-ea6e0005f706	01c74c78-0108-991b-0010-ea6e00052c2a	TRUE

The first two columns are the same id, so the default is the same as -1. The third column is a different id — the statement before that one.

Which is how you grab a result you ran and did not keep. Feed that id to RESULT_SCAN and the rows come back without re-running anything.

Now the audit column pattern

This is what most people use these functions for. Who loaded this row, as what role, on which warehouse. Three extra columns and no extra work.

Query

SELECT ORDER_ID,
       CUSTOMER_ID,
       AMOUNT,
       CURRENT_USER()      AS LOADED_BY,
       CURRENT_ROLE()      AS LOADED_AS,
       CURRENT_WAREHOUSE() AS LOADED_ON
FROM ORDERS
ORDER BY ORDER_ID
LIMIT 5;

This is the second query whose grid is not printed, for the reason in the box near the top of the post: LOADED_BY is the login name on every row of it.

You can work out what it returns from the grid two sections back, though. Those three functions describe the session and not the row, so they are evaluated once and repeated — which is exactly what a thousand rows and 1 distinct answer showed us. Written into a table at load time, that is the cheapest provenance you will ever get.

And this is where the NVL habit from earlier pays off, because CURRENT_WAREHOUSE() is the one of those three that can be null.

Let's build a table name out of the context

Last part of the post, and this is the one that caught the author out while making this course.

The idea is reasonable. There is a hardcoded database name in a query and we want it gone, so let's build the full name out of CURRENT_DATABASE() and CURRENT_SCHEMA() instead.

Query

SELECT CURRENT_DATABASE() AS DATABASE_, CURRENT_SCHEMA() AS SCHEMA_,
       CURRENT_DATABASE() || '.' || CURRENT_SCHEMA() || '.CUSTOMERS' AS THE_FULL_NAME;

Result1 row · 3 columns

DATABASE_	SCHEMA_	THE_FULL_NAME
DEMO_DB	PUBLIC	DEMO_DB.PUBLIC.CUSTOMERS

DEMO_DB.PUBLIC.CUSTOMERS, with nothing hardcoded. That works perfectly, and of course it does.

In fact let's measure it, because the next thing that happens is easier to understand once you have seen that this really is nothing more than a string.

Query

SELECT CURRENT_DATABASE()                                          AS DATABASE_,
       CURRENT_SCHEMA()                                            AS SCHEMA_,
       CURRENT_DATABASE() || '.' || CURRENT_SCHEMA() || '.CUSTOMERS' AS THE_FULL_NAME,
       LENGTH(CURRENT_DATABASE() || '.' || CURRENT_SCHEMA() || '.CUSTOMERS')
         AS ITS_LENGTH;

Result1 row · 4 columns

DATABASE_	SCHEMA_	THE_FULL_NAME	ITS_LENGTH
DEMO_DB	PUBLIC	DEMO_DB.PUBLIC.CUSTOMERS	24

24 characters of ordinary text. Snowflake has no idea it is looking at a table name.

Now let's hand that expression to IDENTIFIER and use it as a table name.

Query

SELECT COUNT(*) AS SHOULD_NOT_GET_THIS_FAR
FROM IDENTIFIER(CURRENT_DATABASE() || '.' || CURRENT_SCHEMA() || '.CUSTOMERS');

Query failed — Snowflake returned an error

001003 (42000): SQL compilation error:
syntax error line 2 at position 16 unexpected 'CURRENT_DATABASE'.

Syntax error, unexpected CURRENT_DATABASE.

Read that carefully, because it is not a runtime error. It is a compilation error. The statement never ran at all.

IDENTIFIER is resolved before anything executes, so it cannot wait for a function to return a value. It needs a constant.

So let's fix it with a session variable

If IDENTIFIER needs a constant, then the name has to be finished before the statement that uses it. A session variable does exactly that.

Query

SET full_name = CURRENT_DATABASE() || '.' || CURRENT_SCHEMA() || '.CUSTOMERS';

Result1 row · 1 column

status
Statement executed successfully.

That statement runs the expression once and keeps the result. Let's see what the variable holds.

Query

SELECT $full_name AS THE_VARIABLE;

Result1 row · 1 column

THE_VARIABLE
DEMO_DB.PUBLIC.CUSTOMERS

The text we built. Now it is a value sitting in the session rather than a function call inside a query.

Query

SELECT COUNT(*) AS VIA_FULL_NAME FROM IDENTIFIER($full_name);

Result1 row · 1 column

VIA_FULL_NAME
10

10, from a fully qualified name that nothing in the query text spells out.

And let's prove the two routes land on the same table rather than assume it.

Query

SELECT (SELECT COUNT(*) FROM CUSTOMERS)              AS VIA_SESSION,
       (SELECT COUNT(*) FROM IDENTIFIER($full_name)) AS VIA_FULL_NAME,
       (SELECT COUNT(*) FROM CUSTOMERS)
         = (SELECT COUNT(*) FROM IDENTIFIER($full_name)) AS SAME;

Result1 row · 3 columns

VIA_SESSION	VIA_FULL_NAME	SAME
10	10	TRUE

10 both ways and SAME is TRUE. The unqualified name resolved through the session context, the other one through the variable, and they met in the same place.

One last thing: IDENTIFIER with a plain literal

The simple case is the one most people actually mean, and it needs no variable at all. A string literal is already a constant.

Query

SELECT COUNT(*) AS VIA_LITERAL FROM IDENTIFIER('CUSTOMERS');

Result1 row · 1 column

VIA_LITERAL
10

10 again. So IDENTIFIER is happy with a literal, happy with a variable, and will not compile with an expression. That is the whole rule.

And when you are finished with a session variable, clear it, so the next thing you run cannot pick it up by accident.

Query

UNSET full_name;

Result1 row · 1 column

status
Statement executed successfully.

And here is the whole context in one query to finish with. This is the third one whose grid is not printed, for the same reason as the other two: its first column is the login name.

Query

SELECT CURRENT_USER()                              AS USER_,
       CURRENT_ROLE()                              AS ROLE_,
       NVL(CURRENT_WAREHOUSE(), '<none>')          AS WAREHOUSE_,
       CURRENT_DATABASE() || '.' || CURRENT_SCHEMA() AS FULL_CONTEXT,
       CURRENT_VERSION()                           AS VERSION_,
       CURRENT_SESSION() IS NOT NULL               AS SESSION_IS_SET;

The user, the role, the warehouse wrapped in an NVL because it is the one that can be null, the database and schema joined into one readable string, the version, and a check that the session id is set.

That is a line worth keeping at the top of any script you did not write yesterday. Run it in your own session and the answers will be yours.

Key points

• CURRENT_USER, CURRENT_ROLE, CURRENT_ACCOUNT and CURRENT_REGION answer who you are, and they all come back as VARCHAR.
• There are three role questions, not one: the primary role, the secondary roles, and everything you could switch to.
• CURRENT_AVAILABLE_ROLES() looks like an array and is text, so parse it before you count it.
• IS_ROLE_IN_SESSION returns FALSE for a role that does not exist rather than raising an error, which is what makes it usable in a guard.
• CURRENT_WAREHOUSE, CURRENT_DATABASE and CURRENT_SCHEMA answer where you are, and any of them can be NULL. Wrap them before writing them into a column.
• An unqualified table name is resolved entirely from your session context. The same query text can hit a different table, or nothing.
• WHERE CURRENT_DATABASE() = 'THE_ONE_I_MEANT' costs one line and makes a destructive statement a no-op in the wrong place. It fails closed.
• CURRENT_SESSION is a session value, not a row value, so it is the same on every row of a result.
• LAST_QUERY_ID() defaults to the previous statement, and takes a negative offset to reach further back.
• The audit-column pattern is three context functions in a SELECT: who loaded it, as what role, on what warehouse.
• IDENTIFIER is resolved at compile time, so it needs a constant. An expression built from context functions does not compile. Build the name into a session variable first, then pass the variable.

Next in this series

Episode 83 — hashing and encoding. HASH, MD5 and SHA2, the encoding functions that go with them, and what a hash is actually safe to be used for.

Every query on this page was run on a live Snowflake account (version 10.34.101) using the demo script for this episode, and every result grid is the output it gave back. Three grids are deliberately absent and their queries are shown without a result: the ones whose cells hold this account's locator or its login name, both of which the video boxes out and the downloadable demo script replaces with [REDACTED]. Nothing has been substituted for them and no value anywhere in this series is invented. The ORDERS and CUSTOMERS data is invented demo data created by the setup script. No performance or benchmark claims are made anywhere in this series. Function behaviour follows the Snowflake context function documentation.