Alerts in Snowflake
1. Alerts in Snowflake
Generating and querying logs and traces to keep track of what happens in your code is one key part of observability. Another equally important part is the ability to be alerted when key events occur in your data environment. This is where Snowflake's Alerts feature can help immensely. Alerts in Snowflake allow you to perform an action in your data environment when specific conditions are met. These actions can include sending out email notifications or even executing a SQL statement, for example. An alert is made up of three components. A condition that triggers the alert. The action to perform when the condition is met. And finally, how often to evaluate the condition. In other words, a schedule to check for the condition. Alerts in Snowflake can be set up using SQL, and once defined, can be configured to meet your specific use case. They frequently play a role across several different aspects of a pipeline. For example, you can use alerts on compute resources, for generating notifications on data processing failures, and even for taking action if your data fails to comply with a particular business rule. These are just some examples. Alerts are very versatile and can be used for many different types of conditions and use cases. In this exercise, we're going to set an alert on the order header table. We want to keep an eye on the values for order amount and order total. If these values are null, we'll create an entry in a table. This will help us keep a record of a potential data quality event. You can imagine that we might want to be notified immediately if order amounts and totals somehow mysteriously begin disappearing. Then, we'll use the alert to call a stored procedure that notifies the data quality team of a potential issue. We'll implement the stored procedure and notification in the next exercise. Now is a good time to pause the video and log into your Snowflake account so you can follow along. Navigate to the Module 2 folder and copy the contents of the alert.sql file. Open a new SQL worksheet in Snowflake and paste the code into it. Run the first few lines to set your context. Okay, here you see that we create a new table called DataQualityAlerts for our alerts. If you would prefer to log alerts into the event table, you would need to create a stored procedure that uses logging libraries to write alerts into the event table. This is because you can't write to event tables directly. The approach we use in this exercise will help us focus on the alert's implementation rather than more stored procedures. Now create the table by running the command. Our alert will look for null values for OrderAmount and OrderTotal in the OrderHeader table. If a row in the table matches these conditions, we'll insert a record into our DataQualityAlerts table. Let's define the alert. On the next line, set the compute resource that should be used when this alert runs. Type, Finally, give the alert a schedule. This is how frequently the alert will check for the condition. Set schedule to, open single quotes, 30 minutes. Okay, that's it. Run the command to create the alert. Hmm. Actually, let's revisit something. This comment mentions that we should create a serverless alert. A serverless alert is an alert that doesn't require us to set and manage a compute resource for it. This is pretty easy to do. Delete the line of code where we set the virtual warehouse. Now, rerun the command to create the alert. That's it! We're now using a serverless alert, and Snowflake will automatically right-size the compute resource for this alert for us. How cool is that? Scroll down a bit. Here, we have a comment about calling a stored procedure for a notification. We haven't covered notifications with Snowflake yet. We'll do that in the next exercise, but let's set up our alert to be ready for when we do. For this comment, type, call stagingTastyBites.rawPOS.notifyDataQualityAim. This object isn't defined, and we don't want to run into errors when we run our alert shortly, so be sure to comment it out with two dashes in front of the keyword call. Let's recreate the alert once more. Okay, excellent. Alerts all start in a suspended state. You can verify this by running the next line. Here, you see that the value of state for this alert is suspended. Let's start the alert by running the next line. If you now rerun the previous line of showAlerts, you'll notice the alert is now in a started state. Great. So, the alert is started, but it hasn't been executed. That's because the schedule is set to 30 minutes, and we're not going to sit around for 30 minutes for it to run. So, let's manually execute it to start immediately. Under this comment, type executeAlertOrderDataQualityAlert. Once we run this, we'll want to quickly run the set of the dummy data that we'll insert into OrderHeader. This dummy data contains records with null values that the alert should pick up. Okay, let's execute the alert. And now, run the insertInto statement that inserts the dummy data. Okay, great. The alert should have picked up these records based on the conditions that we set in the alert. Let's query our alert table to make sure this information was captured. Yep, there it is. You can see the alert time, name, severity level, and message we're logging. You can also see the number of records with null values that the alert captured at that moment in time. You could imagine tracking the record count column using widgets in a Streamlit app, and then surfacing this app somewhere if you're keeping an active eye on orders. You did it. Great job. We're now capturing alerts for potential data quality issues in order data. As you probably noticed, our alerts are, for the moment, being captured into more tables. This is important, but to make use of the full power of alerts, we want to be able to take action when a critical alert is generated. We've already taken steps to get there by defining a call to a stored procedure. Join me in the next exercise to flesh out the stored procedure and implement notifications for this alert.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.