Now you want to see query results in email from Snowflake

In our last post, we discussed the most basic of all ways how in Snowflake you can send out a basic email. It was pretty simple, straight text – nothing to really grab the attention of our readers – which we know is the way to craft an email, right?

To do this, we’re going to have some fun in Python. Yes, delve deep into your bag of Python tricks as we get up to some shenanigans with Snowflake and Python.

create or replace procedure email_last_results(send_to string, subject string)
returns string
language python
runtime_version=3.8
packages = ('snowflake-snowpark-python', 'tabulate')
handler = 'x'
execute as caller
as
$$

import snowflake

def x(session, send_to, subject):
    try:
        body = session.sql("select * from table(result_scan(last_query_id(-1))) limit 250").to_pandas().to_html()
        body = body.replace('class="dataframe"', 'style="border: solid 2px #DDEEEE; border-collapse: collapse; border-spacing: 0; font: normal 14px Roboto, sans-serif;"')
        body = body.replace('<th>', '<th style="background-color: #DDEFEF; border: solid 1px #DDEEEE; color: #336B6B; padding: 10px; text-align: left; text-shadow: 1px 1px 1px #fff;">')
        body = body.replace('<td>', '<td style="    border: solid 1px #DDEEEE; color: #333; padding: 10px; text-shadow: 1px 1px 1px #fff;">')       
    except snowflake.snowpark.exceptions.SnowparkSQLException as e:
        body = '%s\n%s' % (type(e), e)
    session.call('system$send_email',
        'EMAIL_NOTIFICATION_INTEGRATION',
        send_to,
        subject,
        body,
        'text/html')

    return 'email sent:\n%s' % body
$$;

Here, we have a fun Python procedure that will send out to whomever – passed as a parameter – with a subject – also passed in as a parameter – the results of the last query that the user last ran.

* Please note that you will have to change the EMAIL_NOTIFICATION_INTEGRATION to match your notification integration for this to work correctly.

Now, this sounds a little funky, so let’s step through what has to happen in order.

1. Run a query where data is selected. Yes, that does cut out a fair amount of queries you can run, but it’s rather difficult to do more than send an email saying “Yep. Updated.” if you ran an update statement.
2. Call the above procedure with the parameters you need.

Each day, I run a series of Data Quality Scripts in my environment to validate that no issues have arisen that I need to take care of. For this, I have set up a procedure that simply runs the queries in the order that I need them to be run.

CREATE OR REPLACE PROCEDURE SnowflakeDataQualityEmail()
returns string
execute as caller
as
$$
begin
      select * from vw_DQScriptA ORDER BY Name, Level
      call email_last_results('sherpa@sherpaofdata.com', 'DQ Script A Results')

      select * from vw_DQScriptB ORDER BY Name, Level
      call email_last_results('sherpa@sherpaofdata.com', 'DQ Script B Results')
end
$$
;

Thankfully, I have placed all of my logic for Scripts A and B into a view, thus containing all of the logic in one place and allowing anyone to run the scripts at any time. Notice, that I’ve also ordered the data in the exact order I want it to be displayed.

Then I call my Python procedure which takes the results from my last query and puts those results into an HTML-formatted email to me (as long as I am part of the EMAIL_NOTIFICATION_INTEGRATION notification integration). Also, notice that the Python code above formats the table results in an alternating fashion so that it is easier to read.

Now, once I have that SnowflakeDataQualityEmail procedure created, I use a task to kick that off at approximately 8 AM Eastern each weekday so that I get the email and can work off of that as soon as I get my coffee and am ready to work – approximately 8:02 AM Eastern.

Next time, we’ll talk about creating tasks in Snowflake. For now, I hope you’ve learned a lot about creating emails for your users that will make their jobs (and yours) a lot easier. Until next time my friends!

For those that don’t know the song, there’s a line. “I think that I might die, If I miss anything at all, text me, send me an e-mail, ring me up, give me a call.”