Hey Snowflake, send me an email

Snowflake can send emails now —so let’s build a stored procedure that sends you the results of a query every morning — including if there’s a Holiday somewhere in the world that’s affecting your numbers.

Image generated by AI

Snowflake can send emails now

To send an email notification with Snowflake, you now need only one SQL call:

call system$send_email(
    'my_email_int',
    'felipe@example.com, dan@example.com, rajiv@example.com',
    'This is the subject',
    'This is the body'
);

To set this up, read the docs (or the posts by Rajiv Gupta and Dan Galavan):

How to notify users in Snowflake ?

❄️ Snowflake in a Nutshell — Email Notifications 📬

The goal of this post is to tie this functionality with a scheduler and some formatting code — so every morning from Monday to Friday we can get the results of a SQL query, including any upcoming Holidays. Like this one:

An email sent by Snowflake, with the upcoming Holidays

Step 1: Develop a stored procedure that formats and emails the result of any query

This is the code I wrote, while debugging my abilities to email a well formatted query result:

with pretty_email_results as procedure()
returns string
language python
packages = ('snowflake-snowpark-python', 'tabulate')
handler = 'x'
as
$$
def x(session):
    printed = session.sql(
        "select * from table(result_scan(last_query_id(-1)))"
      ).to_pandas().to_markdown()
    session.call('system$send_email',
        'my_email_int',
        'felipe@example.com',
        'Email Alert: Task A has finished.',
        printed)
$$
call pretty_email_results();

Some interesting details in it:

• Snowflake now has “Anonymous Procedures” that allow you to define and call them in one step. You don’t need create or replace first and call later — which is especially useful while debugging.
• I chose to write this procedure in Python, to use the library tabulate. This library will format the results of a query, so they look pretty on our outgoing email.
• The tabulate library is already provided by Anaconda in Snowflake, so we get to use it with the line packages = (‘tabulate’).
• With result_scan(last_query_id(-1) Snowflake can access the results of the last query executed — which allows pretty_email_results() to email whatever results we got in the previous step.
• Snowpark gives us the session object in our stored procedure, which we can use to execute the result scan with session.sql().
• session.sql() gives us a Snowpark DataFrame, and calling .to_pandas() on it gets usPandas DataFrame. Pandas’ .to_markdown() then gets to call tabulate — and it will complain unless you not explicitly request this package with packages=(...).

Step 2: Move the interactive code to a stored procedure

Once your interactive debugging is done, you can move the code to a permanent stored procedure:

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 100"
        ).to_pandas().to_markdown()
    except snowflake.snowpark.exceptions.SnowparkSQLException as e:
        body = '%s\n%s' % (type(e), e)
    session.call('system$send_email',
        'my_email_int',
        send_to,
        subject,
        body)
    return 'email sent:\n%s' % body
$$;

This looks similar to the previous code, with some minor differences:

• execute as caller is needed here so the stored procedure can find the query history of the user calling it.
• runtime_version=3.8 needs to be explicit in a permanent stored procedure.
• I added code to catch exceptions and send a pretty email anyways.
• I moved the subject and recipients to the procedure arguments.
• I added a limit 100 to prevent sending TB sized emails.

With this I’m ready to email the results of any query with a simple call:

call email_last_results('felipe@example.com', 'results from snowflake');

Step 3: Create a procedure that executes a query and emails the results

Now a simple SQL stored procedure can run a query and ask our Python procedure to email the results:

create or replace procedure run_query_and_email_it()
returns string
execute as caller
as
$$
begin
    -- any query you'd like to run
    select 'now' as what, to_char(current_timestamp) as value
    union all
    select 'upcoming', day || ' ' ||holiday
    from table(fh_db.public.holidays('US', [year(current_date), year(current_date)+1]))
    where day between current_date() and current_date()+60
    ;
    -- call the stored procedure that formats and emails the results
    call email_last_results('felipe@example.com', 'upcoming holidays');
    return 'done';
end
$$
;

This is cool, and it’s just a SQL stored procedure. There’s nothing too fancy about it — other than the results.

• To get a table with all the Holidays for the next 60 days, it calls my previously defined holidays UDTF.

Generating all the Holidays in SQL — with a Python UDTF

Test it with:

call run_query_and_email_it();

Step 4: Schedule sending the email

Snowflake has a task scheduler ready for your requests:

create or replace task email_me_frequently
warehouse = 's'
schedule = 'using cron 0 8 * * 1-5 America/Los_Angeles'
as call run_query_and_email_it();

In this case I’m asking it to run the stored procedure every morning at 8am PST, but only Monday to Friday (1–5).

Some notes:

• I asked it to use my warehouse called “s”. Snowflake also offers Serverless Tasks, but those don’t run Python stored procedures.
• I had to modify my holidays() UDF by moving the Python .zip dependencies from a user stage to a named stage. When it lived inside my user stage the task threw the error Remote file ‘holidays.zip’ was not found.
• Change the schedule to every1 minute if you need to debug.

Don’t forget to start it:

alter task email_me_frequently resume;

And to monitor its runs:

select *
  from table(information_schema.task_history(
    scheduled_time_range_start=>dateadd('hour',-48,current_timestamp()),
    result_limit => 100));

Images generated by AI

Looking forward: Alerts

Don’t worry too much about building an alert system — Snowflake is already developing this (currently in private preview):

CREATE ALERT Warehouse_Credit_Usage_Alert
 WAREHOUSE = my_warehouse
 SCHEDULE = ‘USING CRON 0 7 * * *UTC’ // everyday at 7 am
 IF (EXISTS (SELECT
  Warehouse_name,
  SUM(CREDITS_USED) AS credits
  FROM snowflake.account_usage.warehouse_metering_history
  // aggregate warehouse Credit_used for the past 24 hours
 WHERE datediff(hour, start_time, CURRENT_TIMESTAMP ())<=24
 GROUP BY 1
 HAVING credits > 10
 ORDER BY 2 DESC))
 THEN system$send_email (
  ‘My_email_notification_integration’, 
        ‘admin1@company,com, admin2@company.com’,
        ‘Email Alert: Excessive warehouse usage!’, 
        ‘Warehouse usage exceeds 10 credits in the past 24 hours’
)

New Ways to Observe Your Snowflake Workloads

Want more?

• Try this out with a Snowflake free trial account — you only need an email address to get started.
• Play with the formatting options that tabulate offers.

I’m Felipe Hoffa, Data Cloud Advocate for Snowflake. Thanks for joining me on this adventure. You can follow me on Twitter and LinkedIn. And subscribe to reddit.com/r/snowflake for the most interesting Snowflake news.

Images generated by AI