Logging in Snowflake
1. Logging in Snowflake
You can think of a log as a record of an activity occurring within a system. And if you want to keep track of certain activities in your pipeline, then it makes sense to generate and capture logs for those activities. For example, if a piece of transformation logic executes at a scheduled time of day, there might be a log perpetually generated and captured for when that activity occurs. This could be one of many other logs in that logic. If we imagine that the transformation logic is critical to the pipeline's operation, then there's a good chance that a data engineer might want to track certain aspects of it using logs. This could help them measure its success and, when necessary, help them debug it. Snowflake supports five standard log levels that align with common logging frameworks. Those levels are debug, which contains detailed diagnostic information, info, confirmation that things are working as expected, warn slash warning, which is an indication that something unexpected has happened, error, this means a more serious problem prevented software from functioning properly, and fatal slash critical, a severe error that may cause application termination. Each of these has its own specific meaning, which you can see here. To set a log level in Snowflake, you run the alter account set log level command and set it to a log level that I previously mentioned. When you set a log level, you're establishing a minimum severity threshold, and all this means is that you'll capture that level and all higher severity levels. So if you set the log level to info, you'll capture info, warning, error, and critical messages, but not debug messages. And all of this is configured before you start generating and capturing logs in your code. Once configured, you can use common logging libraries in your preferred programming language to start generating and capturing logs. For example, stored procedures are common places to generate logs from. I prefer writing my procedures in Python, so the Python logging library that I would use would be logging. Yes, that's the actual name of the library. Then, using Python in my stored procedure, I could easily generate a log with a command like this. This log would be captured in my event table, which means I could query it and other logs when I want. In this exercise, we're going to add logs to the process order headers stream stored procedure in our pipeline. As a quick refresher, this procedure tracks daily sales for the city of Hamburg, Germany. It processes order data that has been captured in a snowflake stream and aggregates it by day. This procedure is an excellent candidate for implementing logging. This will help us keep an eye on the functional success of its logic. Now is a good time to pause the video if you need to log into your Snowflake account. Navigate to the sproc.sql file in the Module 2 folder. Copy its contents and paste them into a new SQL worksheet. Run the first few lines of code to set your context. Start by configuring the log level for our account. Type alter account set log level equals info. Run the command. We're now capturing logs of level info and above in our account. Here's our stored procedure written in Python. On this line, we create a logging instance and give it a name. We can use it to generate logs throughout the code. Under Log Procedure Start, add the following log. Logger.info Starting Process Order Headers Stream Procedure You'll notice the code has been refactored a bit to include a try-catch block. This is typical because we might want to generate logs for specific conditions. Under Hamburg Count, add a log that captures how many orders were captured from Hamburg. Logger.info f double quotes found open curly brace Hamburg underscore count orders from Hamburg close double quotes This log interpolates the value from the Hamburg count variable. Let's add one final log. Let's add a successful completion log right above the return statement. Type logger.info procedure completed successfully. Okay, that looks great. Run this code to create the procedure with these logs. Call the stored procedure on the next line. Okay, we see a success message. This code processes a stream. Let's insert some dummy data into the table being tracked by the stream. Run the insert into command. Okay, great. We have three rows of dummy data inserted. This should trigger the stream. Call the stored procedure once more. Okay, great. Now let's query our event table once more. Amazing! There they are. Let's browse this a little bit. Scroll to the record type column and look for log entries. Here they are. Now look at the value column. Look, there are the log messages that we added. And if you want to do some more analysis, you could imagine querying this table by record type where the value is some value you want to explore. Maybe you want to look at timestamps or some other piece of information. The final line of code here will get you started so that you can start tinkering. Great job! Keep in mind that the level of log detail you'll want to capture in your logs may vary based on your pipeline's environments. For example, it may turn out that you'll disable less important log levels in, say, your development environment, but have these log levels enabled in your production environment. A practice like this will help you keep event tables full of sufficient operational data for each environment without capturing too many unnecessary details in either environment. And finally, always understand what you're generating logs for and what information is being captured. You could run the risk of accidentally logging sensitive information in an event table. If you're worried about this, you could consider applying row access policies to the event table to restrict access to rows containing PII. You could also consider creating a view on top of the event table and applying a masking policy. There are several ways to go about this, and your approach will depend on your use case. Okay, you have logs covered. Join me in the next video to learn how to capture traces in event tables.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.