Snowflake Context Functions

What is the context function in the Snowflake?

They are system-defined functions that fetch the result about the context in which an SQL statement (like DDL or DML statements & other statements) is executed.

These functions are evaluated at most once per statement.

Example:

  1. current_database()
  2. current_schema()
  3. current_role()

Why do we need to learn and master them?

  1. Enterprises don’t opt for Snowflake Cloud Data Warehouse for one data project or for a small data processing workload. They plan to onboard different kinds of workloads to solve their business problems.
  2. Enterprises don’t buy many snowflake instances, and hence many projects, many environment (dev, test, production) and all of them under single instance and that makes their information architecture very complex.
  3. Hence these context function become so useful and simplify your day to day job when you are building data solutions.

Watch the hands-on video

Context Function Categories & what does it mean?

General Context (8)

  • current_client()
  • current_date()
  • current_ip_address()
  • current_region()
  • current_time()
  • current_timestamp()
  • current_version()
  • sysdate()

Session Context (10)

  • all_user_names()
  • current_account()
  • current_role()
  • current_available_role()
  • current_session()
  • current_statement()
  • current_transaction()
  • current_user()
  • last_query_id
  • last_transaction

Session Object Context (8)

  • current_database()
  • current_schema()
  • current_schemas()
  • current_warehouse()
  • invoker_role()
  • invoker_share()
  • is_granted_to_invoker_role()
  • is_role_in_session()

Section 01 SQL Script

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Lets see these functions in action and see what result they bring.
show functions like 'CURRENT%';

-- since they are functions like sum() or avg(), they have to be used within select statement
select  
    current_region(),  
    current_version(), 
    current_user(),    
    current_role(),    
    current_database(), 
    current_schema(),
    current_schemas(),
    current_warehouse();
    
 -- Now set them and see how to change it via SQL without using the UI features
    use role sysadmin; 
    use database my_db;
    use schema my_schema;
    use warehouse sap_load_wh;
    

Section 02 SQL Script - How to use them to set variables

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- they can also be used to set a variable value
-- you don't need to have select keyword in this case.
    
    set my_wh = current_warehouse();

-- Now you can print them using variable name like this
    select $my_wh;

-- if you have to set them for multiple variables together, you can also follow this
    set (my_db, my_schema, my_wh) = (current_database(), current_schema(), current_warehouse());
    
    select $my_db, $my_schema, $my_wh;
    
-- what about current_schemas and how to slipt it
    set my_search_path = current_schemas();
    
    select $my_search_path;
    select * from table(strtok_split_to_table($my_search_path,',')) f;
    -- we have a better way to do it

Section 03 SQL Script - Snowflake Role Hierarchy & Context functions

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
USE ROLE securityadmin;

CREATE ROLE admin01; -- who can create all objects
CREATE ROLE analyst01; -- analyst who can query objects
CREATE ROLE developer01; -- developer role

-- make sure these roles are accessible upward
GRANT ROLE developer01 TO ROLE admin01;
GRANT ROLE analyst01 TO ROLE admin01;
GRANT ROLE admin01 TO ROLE sysadmin;


--make sure role is mapped to user
GRANT ROLE developer01 TO USER ADMIN; 
GRANT ROLE analyst01 TO USER ADMIN; 
GRANT ROLE admin01 TO USER ADMIN; 

--give grants
USE ROLE SYSADMIN;
GRANT CREATE DATABASE ON ACCOUNT TO ROLE admin01;
GRANT CREATE WAREHOUSE ON ACCOUNT TO ROLE admin01;
-- GRANT CREATE DATABASE ON ACCOUNT TO ROLE dev_db_admin WITH GRANT OPTION;

-- now use new role
USE ROLE admin01;

-- create a db and schema
CREATE database db01;
CREATE SCHEMA db01.schema01;
CREATE WAREHOUSE wh01 
  WITH WAREHOUSE_SIZE = 'XLARGE' 
  WAREHOUSE_TYPE = 'STANDARD' 
  AUTO_SUSPEND = 300 
  AUTO_RESUME = TRUE 
  MIN_CLUSTER_COUNT = 1 
  MAX_CLUSTER_COUNT = 1 
  SCALING_POLICY = 'STANDARD' 
  COMMENT = 'this is load wh';

-- grant roles to other roles.
GRANT USAGE ON WAREHOUSE wh01 TO ROLE analyst01;
GRANT USAGE ON WAREHOUSE wh01 TO ROLE developer01;
GRANT USAGE ON database db01 TO ROLE analyst01;
GRANT USAGE ON database db01 TO ROLE developer01;
GRANT USAGE ON SCHEMA db01.schema01 TO ROLE analyst01;
GRANT USAGE ON SCHEMA db01.schema01 TO ROLE developer01;

GRANT create table ON SCHEMA db01.schema01 TO ROLE developer01;

GRANT SELECT ON ALL TABLES IN SCHEMA db01.schema01 TO ROLE analyst01;
GRANT SELECT ON FUTURE TABLES IN SCHEMA db01.schema01 TO ROLE analyst01;


use role developer01;
use schema db01.schema01
create OR replace table t01 (c1 string);
insert into t01 (c1) values ('1');

use role analyst01;
use schema db01.schema01
select * from t01;

Section 04 SQL Script - Context Function & Task

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
use role sysadmin;
use schema my_db.my_schema;
select current_schema(), current_database(), current_role();


create or replace table my_logging_01(
  log_id integer AUTOINCREMENT not null,
  user_name string,
  role_name string,
  db_name string,
  schema_name string,
  wh_name string
);

-- lets check what happens when we insert
insert into my_logging_01 (user_name,role_name,db_name,schema_name,wh_name) 
    values (
            current_user(),
            current_role(),
            current_database(),
            current_schema(),
            current_warehouse());
            
select * from my_logging_01;

-- create a task
create or replace task my_log_task_001
  warehouse = 'compute_wh'
  schedule  = '1 minute'
as
   insert into my_logging_01(user_name,role_name,db_name,schema_name,wh_name) values (current_user(),current_role(),current_database(),current_schema(),current_warehouse());
   

-- start the task
alter task my_log_task_001 resume;

-- check the information schema for 
select *  from table(information_schema.task_history()) where name ='MY_LOG_TASK_001' order by scheduled_time;
select * from my_logging_01;

Section 05 SQL Script - Stored Procedure with Owner/Caller

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
-- check your context
use role sysadmin;
use schema my_db.my_schema;
select current_schema(), current_database(), current_role();

-- lets create a table 
create or replace table logging_sp(
  log_id integer AUTOINCREMENT not null,
  user_name string,
  role_name string,
  db_name string,
  schema_name string,
  wh_name string
);

-- lets check what happens when we insert
insert into logging_sp (user_name,role_name,db_name,schema_name,wh_name) 
    values (
            current_user(),
            current_role(),
            current_database(),
            current_schema(),
            current_warehouse());
            
select * from logging_sp;

-- define a stored procedure with caller's role
create or replace procedure load_sp_caller()
  returns boolean 
  language javascript
  execute as caller
  as
  $$
  sql_insert_command = `insert into logging_sp_01 (user_name,role_name,db_name,schema_name,wh_name) values (current_user(),current_role(),current_database(),current_schema(),current_warehouse());` 
  result_insert = snowflake.execute(
        { 
        sqlText: sql_insert_command
        
        }); 
    return result_insert;
  $$
;

-- define a stored procedure with owner's role
create or replace procedure load_sp_owner()
  returns boolean 
  language javascript
  execute as owner
  as
  $$
  sql_insert_command = `insert into logging_sp_01 (user_name,role_name,db_name,schema_name,wh_name) values (current_user(),current_role(),current_database(),current_schema(),current_warehouse());` 
  result_insert = snowflake.execute(
        { 
        sqlText: sql_insert_command
        
        }); 
    return result_insert;
  $$
;

-- create a task and call the stored procedure
create or replace task my_task_with_sp_owner
  warehouse = 'compute_wh'
  schedule  = '1 minute'
as
   call load_sp_owner();
   
-- resume the task
alter task my_task_with_sp_owner resume;

-- create a task with caller stored procedure
create or replace task my_task_with_sp_caller
  warehouse = 'compute_wh'
  schedule  = '1 minute'
as
   call load_sp_caller();

--resume the task
alter task my_task_with_sp_caller resume;

select *  from table(information_schema.task_history()) 
  where name in ('MY_TASK_WITH_SP_OWNER','MY_TASK_WITH_SP_CALLER')
  order by scheduled_time;