0
0
In today’s fast-paced data-driven environments, timely notifications about data processing and events are critical. Automating these notifications within Snowflake can significantly enhance operational efficiency. This use case demonstrates how to set up automated email notifications to a distribution list using Snowflake Stored Procedures and the SYSTEM$SEND_EMAIL function.
A retail company wants to automate notifications about data loads, ETL process completions, and data anomalies to their data engineering and analytics teams. The teams are part of an email distribution list named TEST-SF-DNGL-GRP@yourorg.com.
Why Use a Distribution List (DL)?
While it’s straightforward to specify individual email addresses in the ALLOWED_RECIPIENTS field for notifications, managing these can become cumbersome over time. Instead, using a distribution list allows for easy management of recipients. Team members can be added or removed from the DL without needing to reconfigure the Snowflake notification setup. This ensures flexibility and scalability in managing email notifications.
Steps to Implement
- Establish a Distribution List:
- Create an email distribution list within your company’s email system (e.g., TEST-SF-DNGL-GRP@yourorg.com).
- Ensure all relevant team members are add to this list.
- Create a Service User in Snowflake:
- Create a service user in Snowflake with an associated email address matching the distribution list.
create or replace user SUPER_USER password = 'xxxxxxx' must_change_password = false
EMAIL='TEST-SF-DNGL-GRP@yourorg.com';
3.Validate Snowflake User Email:
-
- Log in using the service user credentials and validate the email address.
- This triggers a validation email to the distribution list. Validate the email as described in the uploaded document.
5. Verify and validate the Email. This Email can be validated by any one of the member present in the DL. There is no need that every member of DL has to validate the Email.
5. Configure Notification Integration:
- Adjust the notification integration’s
ALLOWED_RECIPIENTSfield to include the distribution list email address.
6. Create and Test the Stored Procedure:
7. Execute the procedure and it will send an email to the Distribution list.
