Receiving notifications from Snowflake directly to your email is certainly helpful. However, centralizing these notifications can streamline processes and reduce reliance on individual team members. By routing notifications to a Slack channel instead of email, you can eliminate the need to manually update email addresses when a team member leaves or changes roles. This approach ensures that notifications remain accessible and relevant without the hassle of constantly managing email settings for different project members.
Since you’re reading this, it’s safe to assume you’re considering Slack as your notification and communication channel for Snowflake alerts and are looking for practical examples to get started. Let’s dive straight into the topic and explore how to seamlessly route notifications from Snowflake to a Slack channel.
Here’s a step-by-step guide on how I set up a task to send notifications from Snowflake to Slack whenever DDL operations are performed by the ACCOUNTADMIN role, as this role is not intended for such operations.
1- To begin, configure the Slack channel where notifications will be sent. Open the desired Slack channel, navigate to the ‘Integrations’ section, and select ‘Send Emails to this Channel’ to generate an email address for receiving notifications.
2. Copy the email address generated by the Slack channel.
3- Create a user for the Slack channel using the slack adress provided earlier.
4. In my configuration, I intend to use the roles ‘integrations’ and ‘monitoring’ to manage integration objects and oversee the task of monitoring the database and sending notifications. To set this up, I create the roles as shown in the code snippet below.
/* Using generic_scim_provisioner for creating user and roles. */
/* In your architecture you can use the role you have created for
managing users and roles, instead of generic_scim_provisioner*/
use role generic_scim_provisioner;
/* Creating "Slack Integration" user with the email adress provided by the Slack channel*/
CREATE USER "SLACK_INTEGRATION"
LOGIN_NAME = 'SLACK_INTEGRATION'
DISPLAY_NAME = 'Platform Data Alerts'
EMAIL = 'gra-data-a-aefjhkjhfwkj@gra.slack.com'
PASSWORD = '12345'
DEFAULT_ROLE = PUBLIC;
/* Creating "integrations" and "monitoring" roles for creating integrations and task operations. */
create role if not exists integrations;
create role if not exists monitoring;
/* We will need to give "create integrations" priviliges to the "integrations"
role as we will use this role to create integrations on our account */
/* Use accountadmin role to grant create integration priviliges to the "integrations" role */
use role accountadmin;
grant create integration on account to role integrations;
/* Change the role to securityadmin. Using Accountadmin is not always a good idea.
You should only use accountadmin role if you really need to. */
/* Later, I need to grant access on the integration. Therefore,
integration should have "manage grants" rights for handling that. */
use role securityadmin;
grant manage grants on account to role integrations;5. Navigate to the ‘Classic Console’ in Snowflake and grant the ‘SLACK_INTEGRATION’ user permission to receive emails.
6. Using the ‘integrations’ role, create a notification integration for the Slack channel’s email address. Then, grant the ‘monitoring’ role usage permissions on the integration.
use role integrations;
CREATE OR REPLACE NOTIFICATION INTEGRATION Gra_Notification_Integration
TYPE=EMAIL
ENABLED=TRUE
ALLOWED_RECIPIENTS=('asli.imre@gra.com' ,
'gra-data-a-aefjhkjhfwkj@gra.slack.com' );
grant usage on integration Gra_Notification_Integration to role monitoring; 7. Now, we can create a task to send notifications to the Slack channel. In my setup, the task is configured to send notifications to both my email and the Slack channel whenever a user with the ACCOUNTADMIN role performs DDL operations.
use database UTIL_DB;
use schema TASKS_AND_MONITORING;
create or replace task ACCOUNTADMIN_ALERT_TASK
warehouse=DB_TASKS
schedule=’USING CRON 0 10 * * * Europe/Oslo’
as EXECUTE IMMEDIATE
$$
declare
AccountAdmin_login_count := 0 ;
msg_cursor cursor for
select distinct USER_NAME || ‘ user -‘ || database_name || ‘.’ ||schema_name || ‘ schema – ‘|| query_type || ‘ as query type ‘
from snowflake.account_usage.query_history
where ROLE_NAME = ‘ACCOUNTADMIN’
and (query_type like ‘%EXECUTE%’
or query_type like ‘%RENAME%’
or query_type like ‘%DELETE%’
or query_type like ‘%TRUNCATE%’
or query_type like ‘%CREATE%’
or query_type like ‘%ALTER%’
or query_type like ‘%DROP%’
) and start_time > DATEADD(day , -1 , current_timestamp )
and role_name = ‘ACCOUNTADMIN’ ;
msg varchar default ” ;
msg_row_variable varchar default ” ;
query_email varchar default ” ;
begin
select count(*) into AccountAdmin_login_count
from snowflake.account_usage.LOGIN_HISTORY
where role_name = ‘ACCOUNTADMIN’
and EVENT_TIMESTAMP > DATEADD(day , -1 , current_timestamp ) ;
open msg_cursor ;
loop
fetch msg_cursor into msg_row_variable;
if (msg_row_variable <> ”) then
msg_row_variable := concat( msg_row_variable , ‘\n\n’ ) ;
msg := concat( msg , msg_row_variable::varchar) ;
else break;
end if ;
end loop ;
close msg_cursor;
if (msg <> ” and AccountAdmin_login_count > 0 ) then
query_email := ‘ CALL SYSTEM$SEND_EMAIL( ‘ ||
”’Gra_Notification_Integration”’ || ‘,’ ||
”’asli.imre@gra.com , gra-data-a-aefjhkjhfwkj@gra.slack.com”’ || ‘,’ ||
”’Email Alert-Snowflake: A user with ACCOUNTADMIN rights logged into the Snowflake database.”’ || ‘,’ ||
”’A user with ACCOUNTADMIN role logged into the Snowflake database. \n You can see the operations performed by checking the query_history. Here is a summary of the operation done with ACCOUNTADMIN rights\n \n’ || msg || ”” || ‘);’ ;
execute immediate query_email ;
end if;
end ;
$$;
8. Finally, do not forget to resume the task 🙂
