Notifications in Snowflake
1. Notifications in Snowflake
Notifications in Snowflake allow you to really level up what you're doing with alerts. And what I love about the notifications feature is that there are so many different ways to use it. The concept of a notification isn't new. In fact, you probably receive notifications every day from all sorts of different services like web apps, mobile apps, email accounts, text messages, and much more. That's essentially what notifications with Snowflake allow you to do. You can send notifications to a channel of your choice. In fact, you're able to send notifications to email addresses, webhooks. Webhooks are useful for broader team channels like Slack or Microsoft Teams, for example. You can even send notifications to cloud provider queues like Amazon SNS, Google Cloud PubSub, and Azure Event Grid. Notifications are frequently combined with alerts. For example, imagine that an alert has triggered because throughput in a pipeline has dropped drastically. You can send a notification via email or Slack, for example, to let a team of data engineers know about that drop. Of course, you can combine a notification with any alert of your choice. And this is what I mean when I say that there are many different ways to use notifications. So let's get hands-on and add a notification for our pipeline. We'll want to send an email to our data quality team if we observe null values in our order header table. Now is a good time to pause the video if you need to log into your Snowflake account. Locate the notification.sql file in the Module 2 folder. Copy its contents. Open a new SQL worksheet in Snowflake and paste in the code. This code does a couple things. It defines an email integration, which we need to complete, and it also defines a stored procedure for sending out an email notification. We also need to complete the stored procedure. You'll also note that this is the stored procedure that our alert calls. It sends out a basic but important email containing information about null values in the order header table. Let's complete both of these objects. Start by setting your context. Next, we need to create our email notification integration object. Type create or replace notification integration. Email underscore notification underscore in. On the next line, set the type value to email. Then add the recipient's email address on the next line. If you want to see the email in action, and I assume you do, you must use the email associated with the Snowflake account you're currently using. I'll drop my email here. Okay, run this block of code to create the integration. Let's move on to the stored procedure. Let's start by giving it a name matching the call we make in the alert. Type staging tastybites dot raw underscore pos dot notify data quality team. This procedure does a few main things. It queries records with null values. It gets a count of the records. And it formats this information into a table that can be sent via email. At the bottom, we have the code to actually send the email. Let's complete it. In session dot call, type as a first argument system dollar sign send underscore email. This is a system function within Snowflake to send emails. On the next line, enter the name of the email notification integration we set up. Type email underscore notification underscore int. Finally, add the same email address on the next line that we added up above. Okay, great. Run the block of code to create the procedure. Navigate to the worksheet where you defined the alert and uncomment the call to the stored procedure. Rerun the block to recreate the alert. Navigate back to the worksheet where we created the notify data quality team stored procedure. Let's run our alert. We have records in our table with null values, so the alert should trigger the call to the stored procedure. So this means we should see an email with the relevant information arriving in our inbox. On the next line, check the status of the alert. If it's suspended, you can run the next line of code. My alert has started, so now let's execute the alert. And now for the moment of truth. Let's go check our email inbox for an email. And there it is. You can see it's sent from Snowflake Computing. And if I open it up, I get this beautifully formatted email with all of the information my data quality team would need to take action. Amazing. Okay, let's not forget to stop our alert. Run the last line of code to suspend the alert. Just remember that this would just suspend the alert. It'll continue to run on its own schedule. If you want to remove the alert completely, run the last line of code. Drop alert, order data quality alert. Great job. The code that we implemented focuses on using alerts and notifications based on order data. But remember, you can use alerts and notifications for just about any other use case as well. Alerts can be set up for just about anything, like compute resources, services consumption, or even data validation logic. You can then tie these alerts with a notification if you'd like. This means you can use this pattern for many other aspects of your data pipeline. Now, ideally, your notifications are as close to real-time as possible. But, of course, you should weigh this against the type of alert you're triggering off of. For example, an alert that signals a breaking change in a critical pipeline likely warrants a real-time notification. But a warning about a slightly slower-than-expected ingestion speed might not. It's going to be highly dependent on the variables in your use case. Okay, we've now covered the core of Snowflake's observability framework, Snowflake Trail. We've covered event tables, logs, traces, alerts, and notifications. These are all native capabilities in Snowflake. Understandably, you may be integrating with other third-party observability tools, so join me in the next video to learn about how Snowflake supports this.2. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.