# Custom SQL Monitors

URL: https://select.dev/docs/reference/using-select/monitors/custom-sql
Index of all SELECT docs: https://select.dev/docs/llms.txt

> Learn how to create your own monitors for a SQL query

## 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:

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

## Configure the monitors schedule

![SELECT Custom SQL Monitors](https://select.dev/cdn-cgi/imagedelivery/1zmOcgV1p520E4lLTrYjjg/b317d251-1749-4db0-6a8f-e694b24bc400/width=932,quality=75)

### 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](https://select.dev/docs/monitors/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](https://docs.snowflake.com/en/sql-reference/snowflake-db). 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:

```sql
select
    query_id,
    start_time,
    max(start_time) over () as select__incremental_key_ts
from select_ingest.task_queries
where start_time >
    coalesce(
      try_to_timestamp('{{ last_run.select__incremental_key_ts | default("") }}'),
      to_timestamp_ntz('1970-01-01 00:00:00')
    )
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.

![SELECT Custom SQL Monitors](https://select.dev/cdn-cgi/imagedelivery/1zmOcgV1p520E4lLTrYjjg/6bea2f29-c111-4ac3-9402-dd5411800000/width=944,quality=75)

### 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 dependent 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:

![SELECT Custom SQL Monitors](https://select.dev/cdn-cgi/imagedelivery/1zmOcgV1p520E4lLTrYjjg/b6b696a1-c103-48e6-3c80-cec1aa174100/width=527,quality=75)

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.

![SELECT Custom SQL Monitors](https://select.dev/cdn-cgi/imagedelivery/1zmOcgV1p520E4lLTrYjjg/258b2dc6-df13-48e7-5579-53135bf44300/width=1942,quality=75)

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

```sql
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:

```sql
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.

![SELECT Custom SQL Monitors](https://select.dev/cdn-cgi/imagedelivery/1zmOcgV1p520E4lLTrYjjg/908556b7-121e-4f8a-d948-860185540b00/width=1932,quality=75)

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](https://tedboy.github.io/jinja2/templ14.html) are available, here are some potentially useful examples:

1\. Truncate a long uuid to only 10 characters

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

2\. Handle rendering a nullable column

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

3\. Format a float to 2 decimal places: `1234.5678` → `1234.57`

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

4\. 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

![SELECT Custom SQL Monitors](https://select.dev/cdn-cgi/imagedelivery/1zmOcgV1p520E4lLTrYjjg/8d1a3024-4dce-41a3-7061-9fd723000300/width=936,quality=75)

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

![SELECT Custom SQL Monitors](https://select.dev/cdn-cgi/imagedelivery/1zmOcgV1p520E4lLTrYjjg/2d2f08b4-cbf6-4914-855b-6dfa97ea2300/width=962,quality=75)

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:

![SELECT Custom SQL Monitors](https://select.dev/cdn-cgi/imagedelivery/1zmOcgV1p520E4lLTrYjjg/36252a41-8697-4b0d-f3d4-c889fb1f0b00/width=942,quality=75)

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](https://jinja.palletsprojects.com/en/stable/templates/#whitespace-control).

## Error Handling

If either your SQL query or the templated expression fails to send, you will receive an error message in your destination in its place.

## Example Custom SQL Monitors Use Cases

### Long running queries

This topic has some special nuances and deserves its own page. The details can be found [here in our FAQ](https://help.select.dev/articles/7345399580-how-can-i-monitor-long-running-queries-in-my-snowflake-account).

### Snowflake Services that Increased More than 50% in costs

![SELECT Custom SQL Monitors](https://select.dev/cdn-cgi/imagedelivery/1zmOcgV1p520E4lLTrYjjg/7c633fbb-d0f3-4d10-e4f8-814321adbe00/width=1208,quality=75)

**SQL Query**

```sql
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 %}
```

****

**Message Template**

```markdown
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

![SELECT Custom SQL Monitors](https://select.dev/cdn-cgi/imagedelivery/1zmOcgV1p520E4lLTrYjjg/bd0b4f18-733d-4760-c704-04985af1fa00/width=2400,quality=75)

**SQL Query**

```sql
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**

```markdown
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 %}
```

### High warehouse queueing

For warehouses which back customer facing services, BI tools, or other workloads where fast response time is desirable, it may be beneficial to implement a queueing monitor for that warehouse. We can use the built in [warehouse_load_history](https://docs.snowflake.com/en/sql-reference/functions/warehouse_load_history) function in Snowflake to query relatively live load information for warehouses which the user has `MONITOR` privileges on.

It's worth noting that this monitor would be inappropriate for an ETL (or similar background task) warehouse, where query queueing is a normal and desirable feature for efficient resource utilization.

This monitor is designed to be used for **one warehouse at a time**, as it is unusual for the trigger parameters to be identical for each warehouse given their potentially diverse workloads.

**SQL Query**

When considering excessive queueing, there are three sensitivity parameters which we recommend you consider to minimize false positives without missing any true positives. If you're unsure how to determine appropriate values for these, we provide a _tuning query_ below which can be used to give you the right context. The parameters are:

1. The _time window_ over which we should evaluate the monitor (set to 1 minute below). Shorter windows allow a faster response to a spike in load, but they can create false positives during short transient spikes. In general we'd recommend setting this to ~3-4x the length of your _normal load_ spikes. This gives you some robustness against slightly longer spikes in normal operation, but would detect issues before they're 10x longer than normal.
2. The _intensity_ of queueing which you deem unacceptable. This depends entirely on your use case. If your warehouse serves customer queries directly, then you may set a lower threshold than for an internal BI tool warehouse where a small amount of queueing is more tolerable to users.
3. The number of time periods in your window which are over your intensity threshold. A blunt solution is to ignore this value (i.e. remove the `HAVING` clause below), which simply means the alert will fire if _any_ five second period in your time window is over the intensity threshold. In general, this can lead to unacceptable false positives however, and so we would recommend that roughly 50% is a good starting point (i.e. your _time window_ in seconds, divided by ten).

To tune your parameters, you can use the following query to see what data your target warehouse would currently give you. Plotting this as a chart in snowsight, can give you insight into how intense, and how long your normal spikes in queueing are. You'll also be able to examine the approximate latency of the data (by looking at the timestamp of the freshest value).

```sql
select warehouse_name, start_time, avg_queued_load
from table(
    -- See: https://docs.snowflake.com/en/sql-reference/functions/warehouse_load_history
    snowflake.information_schema.warehouse_load_history(
        -- For diagnosis, choose a period of high load, ideally a recent
        -- time users have noticed slow responses.
        -- The data is returned in five second time slices.
        date_range_start=>dateadd('minute',-60, current_timestamp()),
        -- Replace this with the name of the warehouse you
        -- want to monitor
        warehouse_name=>'<TARGET_WAREHOUSE>'
    )
)
order by start_time desc
```

Using the query above, you can use the query below as a template for your monitor. Adjust the provided defaults as appropriate for your use case.

```sql
-- Warhouse Queueing Monitor Query
-- -------------------------------
-- The data is returned in five second time slices.
-- By counting the returned rows, we look for a certain
-- number of periods in the last minute that were over
-- the queueing threshold.
select warehouse_name, count(*) as number_of_periods
from table(
    -- See: https://docs.snowflake.com/en/sql-reference/functions/warehouse_load_history
    snowflake.information_schema.warehouse_load_history(
        -- Parameter #1: --
        -- Here we define how far back we look. In this case
        -- we're fetching the last minute of data, and so (once
        -- we account for the lag in the datasource) will get
        -- rougly 13 rows of raw data.
        date_range_start => dateadd('minute', -1, current_timestamp()),
        -- Replace this with the name of the warehouse you
        -- want to monitor
        warehouse_name => '<TARGET_WAREHOUSE>'
    )
)
-- Parameter #2: --
-- Adjust this queued load number as appropriate for your
-- warehouse. In this example, to be more than 0.5 would mean,
-- on average there was at least one query queued for 50% of
-- the time in that five second period.
where avg_queued_load >= 0.5
-- NOTE: The GROUP BY here is so that we only get one consolidated
-- row in the results. The query only returns results for a single
-- warehouse, but there will be potentially many rows for that warehouse.
group by warehouse_name
-- Parameter #3: --
-- By including a HAVING clause we only get results when this
-- warehouse has more than a certain number of periods of high
-- queueing in the window. Adjusting this can help avoid false
-- positives on transient spikes.
having number_of_periods > 4
```

**Message Template**

```markdown
🚨 Warehouse `{{rows[0].warehouse_name}}` is currently experiencing high queueing. There have been {{rows[0].number_of_periods}} time slices in the last minute where queuing has been unacceptably high. Any Users or Workloads querying this warehouse will be experiencing slower response times and potentially timeouts.

### Next Steps
Urgently review warehouse size and max cluster count to assess whether either should be increased, or whether a workload is generating anomalous query load.
```