How to notify users in Snowflake ?
Today’s blog is concentrating on notifying Snowflake users. This can be based on certain trigger point or can be easily used as part of automation.
This is one of the important piece which was missing in Snowflake and many of consumer including me has requested for this feature from long time. “Simple Mail Transfer Protocol” is just widely used and known term in other database technologies. The Notification System Stored Procedure feature is only hosted on Amazon Web Services (AWS) and currently in
The content of a message sent using AWS SES may be retained by Snowflake for up to thirty days to manage the delivery of the message and is thereafter deleted.
Which AWS region this notification system is hosted on?
Below are the region as per Snowflake documentation.
- us-west-2
- us-east-1
- eu-west-1
Is this feature available to other cloud provider?
Yes, this feature is available and working in all 3 cloud provider. You will see a demo screenshot below from all 3 cloud provider.
How does this notification process work?
Each supported Snowflake provider region will have an inbuilt procedure SYSTEM$SEND_EMAIL() which is used to send email notification to all verified Snowflake user only.
Email notifications can only be sent to Snowflake users within the same account, and those users must verify their email addresses via Snowsight or classic webui.
You need to create a NOTIFICATION INTEGRATION with TYPE=EMAIL as prerequisite to send email. So the actual process will be like below. The maximum number of email addresses that you can specify is 50.
What are the use-case where we can use this?
How about some Demo?
Step 1: Verify the email ID to which you want to send the mail from the current account.
Step 2: Create Notification Integration
If any email address in the recipient list is not included in the ALLOWED_RECIPIENTS parameter for the notification integration, no emails are sent.
Create notification integration Email_Notification_Integration
type=email
enabled=true
allowed_recipients=(‘<Email>’);
Use SHOW INTEGRATION function to verify if it exists or not.
show integrations like ‘EMAIL_NOTIFICATION_INTEGRATION’;
Step 3: Call the SYSTEM$SEND_EMAIL proc. Make sure the role you are using has privilege to use notification integration.
Call SYSTEM$SEND_EMAIL(
‘EMAIL_NOTIFICATION_INTEGRATION’,
<‘verified email id’>,
‘Email Alert: Task has finished.’,
‘Task has successfully finished on ‘||CURRENT_ACCOUNT()||’ account which is deployed on ‘||CURRENT_REGION()||’ region at ‘|| CURRENT_TIMESTAMP()
);
Below is the email sample received from AWS platform.
Below is the email sample received from Azure platform.
Below is the email sample received from GCP platform.
Hope this blog helps you to get insight into the upcoming Sending Email Notifications feature. Feel free to ask a question in the comment section if you have any doubts regarding this. Give a clap if you like the blog. Stay connected to see many more such cool stuff. Thanks for your support.
You Can Find Me:
Subscribe to my YouTube Channel: https://www.youtube.com/c/RajivGuptaEverydayLearning
Follow me on Medium: https://rajivgupta780184.medium.com/
Follow me on Twitter: https://twitter.com/RAJIVGUPTA780
Connect with me in LinkedIn: https://www.linkedin.com/in/rajiv-gupta-618b0228/
#Keep learning #Keep Sharing #RajivGuptaEverydayLearning #SnowflakeDataSuperhero #RajivGupta
