Custom SQL Monitors

What are Custom SQL Monitors?

While SELECT offers many types of monitors to help you stay on top of your Snowflake usage, these one size fits all solutions may not suit your needs. When our out-of-the-box monitors fall short, you can use Custom SQL Monitors to get alerted on anything in your Snowflake account that can be returned by a SQL query.

Configuring Custom SQL monitors is simple:

Configure the monitors schedule and where alerts will be sent
Define a SQL query
Customize the monitors message using the results of the SQL query

Configure the monitors schedule

Scheduling frequency

There are a number of options for how often your monitor will run. SELECT syncs with your Snowflake account daily at around 3-5AM UTC, this will occur before any daily, weekly, or monthly monitors run.

Hourly: Your monitor will run every hour
Daily: Your monitor will run every day
Weekly: Your monitor will run once a week on Monday
Monthly: Your monitor will run on the first of the month
Cron: Your monitor will run on a customizable schedule defined by a cron expression

Destinations

Destinations determine where you will be alerted when your monitor runs, see: configuring destinations to learn more about setting these up.

Sending Options

You can optionally choose to only have your monitor send a message if the SQL statement returns some records.

This is helpful when you only want to get alerted if results are returned from the query.

Define a SQL query

You can define a single SQL expression which will run under the Snowflake user you’ve created for SELECT. By default, this means you’ll only be able to access data in the Snowflake metadata database. Grant the SELECT Snowflake service user access to additional databases if you want to build alerts off of other datasets.

It’s worth noting that we will automatically limit the results of your query to at most 50 rows, but we suggest putting a more aggressive limit than this on your query as some destinations may have character limits and your message will be truncated if it exceeds this limit.

Limit Query to data since last run

SELECT exposes an optional system for limiting a monitor run to only the data since the last run that returned values.

If your query returns a value called select__incremental_key_ts the first row with this value will be stored and provided to the next run of the query. Here is the intended usage:

select query_id, start_time,
  max(start_time) over () as select__incremental_key_ts
from select_injest...task_queries
where start_time > TO_TIMESTAMP('{{last_run.select__incremental_key_ts  | default("1970-1-1")}}')
LIMIT 1

Let's break it down

The select clause contains:

max(start_time) over () as select__incremental_key_ts

Calculates and returns the maximum start_time across all rows in the result set
- Every row will have the same value for this column
Names it select__incremental_key_ts which is the special column name that triggers the incremental mechanism
Uses a window function (over ()) to return this max value in every row

The where clause contains:

start_time > TO_TIMESTAMP('{{last_run.select__incremental_key_ts | default("1970-1-1")}}')

Uses a template variable {{last_run.select__incremental_key_ts}} which contains the timestamp from the previous run
The | default("1970-1-1") part provides a fallback value (Jan 1, 1970) if no previous monitor runs have returned a value
- This isn’t obviously isn’t standard SQL, we’re using jinja to inject the string value into your query before running it
Converts this string to a timestamp with TO_TIMESTAMP()
Only returns rows with a start_time newer than the last run

Customize the monitors message

After running your query you can edit a message template. A reasonable default will be generated to get you started.

This message template uses markdown for formatting and Jinja for powerful templating based on your query results.

Your markdown will be converted to a format that is compatible with your destination, and as such only markdown that is supported by your destination is applicable here.

Message structure

The area highlighted in purple is the name of the monitor, this can be configured at the top of the page.

The area highlighted in orange is fully customizable using the message template.

SELECT will also include a link to the monitor as a footer on your message, not shown here.

Formatting Options

The top bar of the editor provides shortcuts for a variety of common formatting options, in order they are:

Bolded text
Italicized text
strikethrough text
A link
A bullet point list
A numbered list
A quote block
An inline code snippet
A code block

The preview is a close approximation of what the message will look like in emails, but the exact format of each style is dependant on the destination that you choose.

It's recommended to use the ‘Send preview’ button at the bottom of the page to double check formatting in your target destination is what you expect.

More advanced markdown features such as headers or image insertion will also work, but may yield unexpected results in some destinations. Notable examples include:

Slack does not support headers, and will render bolded text instead.
Teams does not support code blocks.

Tag a user in Slack

You can alert a specific person in slack by including a <@slack_member_id> tag in your message. A user’s Slack ID can be copied from their profile in slack here:

If your query returns Slack ID’s, you might include them like this <@{{row.slack_user_id}}>.

Adding in data from the query results with Jinja Templating

Your message can also use Jinja to inject information from your query or metadata about the monitor into your message.

We suggest exploring the options by clicking the ‘Insert variable’ variable button to see what’s available, as well as some useful snippets for common patterns.

Let's take a deeper look at the suggested default message

A total of {{count}} rows were returned:
# Loop over the query results here #
{% for row in rows[:10] %}
- {{ row.sum_serverless_task_cost }}, {{ row.sum_query_cost }}, {{ row.start_day }}, {{ row.first_ever_start_time }}, {{ row.identifying_metadata }}
{% endfor %}
# Include any other useful information here, using markdown to format it #

# Next Steps
Optionally describe any action users should take off of this alert.

On line 1 we’re using {{count}} to inject the number of rows returned, a variety of other variables, such as the name of the monitor or when it ran are also available from the ‘Insert Variable’ menu.

Line 2 contains a comment that will not be included in the final message.

On lines 3-5, we loop over the first 10 rows returned by the query and include some of the results. The snippet generator will pick out the first few values for you, but any value can be displayed with {{row.<column_name>}}. If you would like to render a row but the column name isn’t a valid identifier, for example:

select sum(foo) from bar

You can also use the alternative [] syntax to render this value like this: {{row['sum(foo)']}}

To discover other row values, click ‘Insert Variable’ → ‘Row Values’ These snippets must be used within a for {% for row in rows %} style loop, as the row variable must be defined.

If you only care about the first row, you can also insert these values like this {{rows[0].some_value}}!

Using Jinja filters to format values

All default Jinja filters are available, here are some potentially useful examples:

Truncate a long uuid to only 10 characters

{{row.query_id | truncate(10)}}

Handle rendering a nullable column

{{ row.maybe_this_is_null | default("N/A", true) }}

Format a float to 2 decimal places: 1234.5678 → 1234.57

{{ "%0.2f" | format(row.float_value | float) }}

Format a timestamp to a human readable date and time: 2024-01-01T12:00:00.000Z → Jan 1, 2024 at 12:00 PM

{{ row.timestamp_column | format_timestamp("%b %d, %Y at %I:%M %p") }}

Whitespace control

Markdown can often seem somewhat verbose when it comes to white space. In this example you might expect that bold and italics render on separate lines, but an empty new line is needed between them

Markdown rendering occasionally requires a newline when you might not expect.

Additionally Jinja provides a shorthand for removing whitespace on either side of a Jinja block, for example, here is a list of all column names returned by a query:

The - in {%- endfor %} tells Jinja to remove whitespace before the end of the block, without this, each column renders on a new line. SELECT runs Jinja with the default settings described here.

Error Handling

If either your SQL query or the templated expression fail to send, you will receive an error message in your destination in it’s place.

Example Custom SQL Monitors Use Cases

Long running queries

Here’s an example monitor you can create called “Long Running Queries”:

SQL Query

select
    user_name,
    query_type,
    query_id,
    substring(regexp_replace(query_text, '\\s+', ' '), 1, 100) as trimmed_query_text,
    warehouse_name,
    warehouse_size,
    total_elapsed_time/1000 as total_elapsed_time_s
from snowflake.account_usage.query_history
where true
    and start_time > current_date -1
    and start_time < current_date
    and total_elapsed_time_s > 1
    and user_name <> 'SELECT_BACKEND'
order by total_elapsed_time_s desc
limit 5

Message Template

A total of {{count}} rows were returned:
{% for row in rows[:5] %}
* Query run by `{{ row.user_name }}` took `{{ "%0.1f" | format(row.total_elapsed_time_s | float) }}`s on {{ row.warehouse_size }} WH [→](https://app.snowflake.com/scwxhob/ad38017/#/compute/history/queries/{{row.query_id}}/detail)
{% endfor %}

For the query profile link, you’ll need to update the URL with your values. Here’s an example from our account: https://app.snowflake.com/scwxhob/ad38017/#/compute/history/queries/01ba8ed5-0000-be68-0000-3de928e6403a/detail

The structure is:

https://app.snowflake.com/<organization_name>/<account_id>/#/compute/history/queries/<query_id>/detail

Snowflake Services that Increased More than 50% in costs

SQL Query

with
hourly_spend_by_service as (
    select
        start_time,
        service_type,
        name,
        credits_used,
        lag(credits_used) over (partition by service_type, name order by start_time) as prev_credits_used
    from snowflake.account_usage.metering_history
    where true
        and start_time > current_date - 7
)
select 
  *,
  round((credits_used - prev_credits_used)/prev_credits_used*100, 2) as pct_change
from hourly_spend_by_service
where true
		-- increased by more than 50% compared to previous hour
    and credits_used > prev_credits_used*1.5
    -- Set a minimum threshold, don't care about tiny spikes
    and credits_used > 1
    and start_time between current_date - 1 and current_date
order by start_time desc

Message Template

The following resources had a usage increase of than 50%:

{% for row in rows %}
- {{ row.start_time }}: `{{ row.name }}` ({{ row.service_type }}) had credit usage of {{ row.credits_used }} (+{{ row.pct_change }}%)
{% endfor %}

Failing Tasks

SQL Query

select
    name,
    completed_time,
    state,
    query_id,
    error_code,
    error_message
from snowflake.account_usage.task_history
where
    completed_time between current_date - 1 and current_date
    and state='FAILED'
order by completed_time desc
limit 10

Message Template

A total of {{count}} tasks failed recently:

{% for row in rows[:10] %}
- **{{ row.name }}** failed at `{{ row.completed_time }}` 
    - Error Code: `{{ row.error_code }}` 
    - Error Message: _{{row.error_message | truncate(50)}}_
{% endfor %}

### Next Steps
Optionally describe any action users should take off of this alert or [link to a guide](google.ca)!