Did you know that you can send emails in Snowflake now?
In the Meetup event below, we demonstrate how to use Snowflake’s emailing features to notify yourself and/or your team by email so that you can remain on top of the data.
We got the idea for this topic from a great article written by Felipe Hoffa and we encourage you to check it out!
Other Meetups and Events to check out:
Transcript from the Meetup event:
So hello everyone, welcome to another Carolinas Snowflake Meetup group event. Today we will be going over sending emails in Snowflake. So our previous meetups are all recorded and they’re available on our YouTube channel, usually one to three days after the actual meetup.
So we’ll go over a quick Snowflake refresher and some use cases and benefits of sending emails in Snowflake. And then Anubhav will be providing a demo for you guys and then we can do a little Q and A.
I’ll take it from there. So basically your Snowflake is your cloud-based database platform and they have the option of using either AWS or GCP or as your underlying cloud platform. It comes with host of features along with the traditional on-premise features that you have in databases like Postgres, Oracle, SQL, all of those.
You even have that back in Snowflake. You can have your OLTP databases, you can have your EDL pipeline such as your Snowpipe. You have Marketplace where you can download data sets which are provided by custom vendors out there.
It also has its own in-house UI platform which is called as Snowsite, which helps you build quick charts and quick analysis. But you can also connect your Snowflake platform with your favorite BI tool Looker, Tableau, and so on.
And just like any database, you can perform your normal data transformations, integration, analytics, and so on. Thank you, Anubhav. What I really wanted to mention is that you can pull all your different data from your different sources into the cloud and it gives you a single source of truth so you can kind of get a holistic picture of the business.
So now we’ll go over some of the use cases. Why you would actually want to send emails in Snowflake. You can have them sent to keep an eye on your consumption usage, warehouse usage, your Snowpipe consumption whenever tasks are executed.
You can also get a notification whenever your password expires and it’s time to set up another one. Or if you need your MFA set up or if some unauthorized user is attempting to access the account, you can set it up to where you’ll get an email every time that happens.
So there’s a lot of good use cases that you might want to set up emails in Snowflake. So some of the benefits here, it will save you time and money. You’ll have enhanced security because you can get those notifications if unauthorized users are there or if you need MFA set up.
It’s going to increase visibility because you’ll be able to see what’s going on with your consumption and task execution. And then you can also use it to automate processes. So Snowflake has its own mechanism which is called as Snow alert mechanism, where they’ve given you the feature to kind of raise certain form of alerts.
So if you have any business logic, any business rule, then Snowflake has given you the feature to kind of raise alerts when your business rules are applied. I think very recently Snowflake added the feature that along with the alerts that you have in place, you can even set up email notifications.
In fact, in a couple of minutes, I will just go back doing a quick demo as to where and how you would set up those email notifications from Snowflake. But before I get there, I just want to kind of talk about few use cases where we feel email notifications would be really helpful.
For example, for the first one, which is warehouse usage, that’s a very important feature because there have been cases where you would have that due to some of the heavy loads or heavy data that’s been pushed into Snowflake.
You might cross your predefined credits that’s given to you by Snowflake and you might want to have an alert mechanism through an email which tells you that, hey, you are about to cross your assigned limit and you might want to keep a track of it.
Or you might want to kind of tone down some of your warehouses or probably go and purchase more of the snowflake credits. Another very important business use case that we’ve come across are Snowpipe consumptions because there might be features that in fact in Snowflake you can have data up to ten GB that can come across Snowpipe and Snowflake but it comes in different various formats.
Sometimes you might have ten MB, you might have 200 MB, 300 MB. So just to make sure that you are able to track the consumption that come across Snowpipe over a week, over a month, over a day to and understand what it looks like, email notifications are great.
Second scenario are usually tasks. Tasks are nothing but just automated execution objects in Snowflake which perform or which calls our routines automatically on a predefined schedule. So for example, if you have tasks which have been running across let’s say more than a day, more than two days or probably are probably stuck in some infinite loop, you can send an email notification saying that hey, this task has been running for the last 40 hours.
You might want to go and shut it down or probably see what’s causing the problem. And then the next is your MFA. It’s best practice to have MFA enabled for at least all the account admins on your group and more often than not in a hurry people forget to turn on the MFA.
I think that’s a great notification that you can write a quick logic. To check whether you have MFA enabled or not for all your account admins. And if not, you can send an email saying that so and so person does not have the MFA enabled and you can take the next action accordingly.
And then the same logic goes for password expiration and unauthorized access attempts. So now as part of the demo, I would just take one example where we try to explain that how we’ve tracked the warehouse usage.
And based on the usage, we’ve kind of sent out a quick email notification. It’s a quick demo, but it covers the essential elements that are required to configure email setup from Snowflake. Okay, so this is the classic console of Snowflake.
And if you look at the setup over here, the first step in setting up notifications are basically nothing but setting a notification integration. So Snowflake has the property or something called as role-based access control, where it makes sure that whoever accesses the system is assigned to a certain role and then certain roles have access to certain objects in Snowflake.
So every entity in Snowflake would be an object. So that’s why when you go about starting about setting up email configuration, you would set up something called as notification integration, which kind of indicates that’s the object you’re creating.
So step number one, you will create a notification integration, which is my email, and you can give any name to it which will indicate the type that you have. And you can specify the recipients that would have access to the email list to which this email will be sent out.
So in this scenario, I’ve added my email here. And then what we have done is we have created two stored procedures. In case if you’re wondering about what stored procedures are, we have covered store procedures, snowflake, a while back.
But if you have any questions around stored procedures we can take them later during the Q and A session. But if you look at this logic, here’s what’s happening here. So just focus on this first procedure that is generate query.
It’s written completely in SQL. So in Snowflake you can create store procedures, JavaScript, Java, and I think they’ve even come up with Python in the last few months. And you also have SQL. So we just focus on this logic that runs here, which I want to highlight here.
This logic is trying to kind of get a list of all warehouses that it gives you your name of the warehouse, it gives you that what’s the credit used by the warehouse for this month? And that’s why you notice that I’ve used data trunk day trunk function here to kind of bring it down to this month.
And what’s the percentage of this usage compared to your total credit that’s assigned to your account? So what I mean by that, based on your contract with Snowflake, you would have total amount of credits given to you by Snowflake.
So in this example, I’ve taken 500. So this query tells me that, give me a list of all the warehouses that have consumed more than, since we’re still running in March, I’ve taken only 3% right now, but you would take a number like 50%, 60%.
So give me a list of all those warehouses that have used more than 3% of the credit that’s assigned to you from all the warehouse houses. So by this logic, it gives you some form of control that if you have multiple teams in your company, you might have a market in sales, you might have data science, you might have data engineering and so on.
And you might have assigned a certain set of credits to each team. And if you want to check. That. And if you want to keep a rule that hey, no team should use more than 15% or 20% of my credits assigned every month.
And if you have any team that uses more than 20%, you can have an email notification configured to it. So that’s the rationale behind having this kind of a setup. So this query would kind of get you that list of those list of warehouses.
And then what we’re doing is that we generate so if I just go and do call generate query here, it just gets that list of information here for me in this format. This is not right now formatted very well, but there are certain functions in Snowflake that you can use to kind of format them and you can make it in a much better format.
And then we have another store procedure over here which basically calls this procedure to get the list of warehouses. And then it calls this system function which is called System Send Email. And this System Send Email is the one which would actually send out the email for you.
So what are the arguments for this function? You have to specify this notification integration which we created here. You’ve got to send the email to which it has to go to the subject of the email. And then what’s the body of the email that has to go?
So if I just say after getting the list of warehouses, you can just go and say that, run query and email it. You would get done, which is basically we’ve returned from our store, so you know that it goes.
And if you look at the over here, you basically have this email that comes from Noreply@snowflake.net. That is something fixed. You cannot change that. And then it gives you this list that we have configured here.
So right now I’ve called all of these manually. But to automate this, your next step could be you create a task where you can call the store procedure and you can specify on what time, what date it has to run.
So for example, you could have like a job, some form of a task that runs at the end of the day which would run this logic in the store procedure. And if it satisfies it sends you an email every day to kind of tell you whether you have any of your warehouses which are crossed your assigned credit with the same logic we’ve used warehouses.
You can do the same with snowpipe consumptions, you can do the same with task execution time and so on and achieve the same result. Here. I’m going to go ahead and put the link for the Medium article written by Felipe Hoffa that we sort of base the idea of doing our meet up on this off of.
He writes some really great content, so we love to check out his stuff and encourage you guys to check it out whenever you get a chance to. I think somebody raised a hand in the chat. Hey Ashish. Hey.
Yeah, so I have two questions. One is the example which you are talking about particular tasks running for very long period of time? I think not sure, snowflake might have some out of box like they kill our task every an hour.
So I was just curious on I’m not sure whether they kill it every hour but I know there is a default with the habit. Just wanted to make sure this use case which you’re talking about related or something on that.
So I wanted to check on this use case, whether that snowflake, whichever it provides to kill any running task more than an hour, whether that would solve our problem or this will solve our problem if they don’t have that feature.
Yeah, I think Ashish, you’re absolutely right. And when you say that, it does remind me that there is a time off cut-off limit. I need to double-check on the fact whether it is 60 minutes or not because I know that while setting up the task, you can also specify the time that you want the task to run to.
So I think if your feature is to just safeguard that you don’t want your task to keep running entirely and they just keep running the entire time, it will satisfy a problem. Okay, I think what happened in US yeah, I think what we think from the email standpoint is that because right now if your tasks fail right, you have to manually go and check in snowflake that your tasks have failed and then figure out why it has failed.
So they’ve been from scenarios that, for example, that we had some tasks that were reading data from a source and that was working every day. It was working perfectly fine, but then something happened in the source and the task failed for some reason and the only way we could go and figure out was by manually going looking at the task list to see what happened to figure out oh, that this task failed at this particular time.
So I think email from that point of view could be helpful that you don’t have to wait to kind of go check. You would be notified also in case if your task fails. But to your point yes, that even that execution time off limit should satisfy your requirement.
Yeah, I think that makes sense. Task failure notification and example you which you are showing the warehouse usage. Another question I have related to number of this notification integration we can create in one account, do we have any limit on that?
And if you have it then. I have another question. I am not aware of any limit as such on the notification integration. Ahish. I think what we experienced recently, we thought to have squad start adopting this one, but by DBA was saying that looks like there is a limit for ten.
That’s the max number I can create. So we should be careful if we are asking a squad to create this integration. Okay, I will check. But if there is a limb ten integration we can create, then it means it may be related to a very generic thing, not in space, because somebody from application team is asking for notification to be created in their application.
So that’s why I got to be curious. That is a great question, Ashi, because I believe that you’re right that if there are limits as a DBA statement, then that would beat the entire point of having notification integrations.
I can double-check on that point, actually, and come back to you if there are any limits. But as of now, I don’t recollect of any limits. But yeah, I’ll be happy to go and take a look and double-check on that point also.
Yeah, that would be really helpful. Thanks. For sure. Yeah, that’s all I had. Okay, thank you. Wonderful questions. Thank you. Anybody else have any questions?
Hi. This is Bamsi. Thanks for this session. I have one question actually. Can we do any templating with this email system? Like right now what we are doing is we have a bunch of warehouses where we are processing. So every day what we do, we will run a query and we will check like how many credits has been used by each warehouse and then we will notify that particular department saying like, hey, you were.
Your incremental load or your load has more than compared to yesterday. So maybe it is worth noticing to check or something like that. We do this manually and then we will templatize it in a HTML format and then we will send but I doubt we can templatize it.
But I’m just checking that with you guys. What has happened is that recently Snowflake has also really improved its offerings by adding Python features in Snowflake. And there are some features like for example, what you could do is that you could create some form of so when you see a template, there is no native support to create template like the HTML one that you’re talking about.
But what you can do is you can have a template created using some of the Python features such as string formation using Python and that can be converted into some of a data frame and that can be moved into a markdown.
So you can move into a markdown format and that markdown that can be used to kind of create that into a template that can be sent across an email, but it will not be as sophisticated as the one that probably which you’re talking about in form of an email in sort of HTML.
But there are some more features that we could do using. So basically what you’re suggesting is like use the snow SQL or any version of the connector and then run the query and get the result and put it in a data frame.
And then use this Note like integration email option and then send that data frame via email. In a nutshell, that is what you are suggesting, right? The other thing I was thinking is that you could spin up a notebook.
Where you can connect with connect using Snowpark libraries in Python itself. And I’m sure there would be some in fact, I know there are some Python libraries out there that you could use to format some of your content out there.
I don’t remember at the back of my mind, but you could do it in Python and then push it back by using the same store procedure here and then pushing the email. I don’t think is there any limit on that information subject, like the body of that.
Let’s say if I add more these HTML text, the content will be will be big, right? So I don’t think this particular integration doesn’t have any limit on how much MB we can send or anything. Right? Yeah.
Snowflake hasn’t specified any limit as such on the content as of now that will be derived based on the kind of format you use in for the object. Yeah, understood. Thank you very much. That’s all I have.
Thank you. Thank you. All right, great session. So we do have another meetup planned for next month. I think we are going to be going over Snowpark. Right Anubhav. I have not sent out the invites yet, but I will be posting it by the end of the week in our Meetup group.
Right. We’ve got some Snowpark work that we want to kind of start discussing about. So we’ll have Snowpark sessions coming up in the next few months here. Awesome. And then we also we have our East Coast dbt Meetup Group.
And then we have a Midwest dbt Meetup Group, and we’ll have some sessions coming out in there. And those are all a part of the modern data professionals and. Meetup network, which we’re a part of as well.
So you might see those come through the network. If you’re interested in joining those, we encourage you to do so. Well, thank you everyone, for joining tonight and we will see you at the next one.
