Snowflake SQL Tutorial 139 - My Alert Fired Three Times For One Order And Would Not Stop

Snowflake SQL · Episode 139

In this post we are going to cover ALERT in Snowflake.

An alert is a task with an IF in front of it. It has a condition, an action and a schedule. Every period it asks the condition, and when the condition is true it runs the action.

Which makes the interesting question not whether it ran, but whether it ran and decided there was nothing to do. From outside those two look identical.

We will start with one alert over one table and build it up step by step. Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 139 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

1. Let's create some sample data
2. Now let's create the alert
3. Let's run it by hand while the condition is false
4. Now let's make the condition true
5. So where does a run leave a record?
6. Now let's resume it and leave it alone for a while
7. Can an alert watch a stream?
8. What happens when the action cannot work?
9. Now let's change the alert in place
10. What does CREATE ALERT accept without checking?
11. One last thing: an alert that is not there

Let's create some sample data

We need two tables. One for the alert to watch, and one for it to write into when it fires.

Sample data

-- Sample data for this post.
-- A table for the alert to watch, and a table for it to write into.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE ORDERS_T139 (
    ORDER_ID INT,
    AMOUNT   NUMBER(10,2)
);

CREATE OR REPLACE TABLE RAISED_T139 (
    NOTE    VARCHAR(60),
    WRITTEN TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

INSERT INTO ORDERS_T139 (ORDER_ID, AMOUNT) VALUES (1, 10.00), (2, 20.00);

Two small orders, neither of them large. Now let's ask, by hand, the question the alert is going to ask.

Query

SELECT COUNT(*) AS BIG_ORDERS FROM ORDERS_T139 WHERE AMOUNT > 1000;

Result1 row · 1 column

BIG_ORDERS
0

Zero. There are no big orders. Remember that, because the first thing we do with the alert is run it while the answer is still zero.

One honest note before we start. What everybody actually wants an alert for is email, and email needs a notification integration. So let's ask the account what integrations it has.

SHOW INTEGRATIONS

SHOW INTEGRATIONS;

Result1 row · 6 columns · scroll →

name	type	category	enabled	comment	created_on
SNOWFLAKE$LOCAL_APPLICATION	OAUTH - LOCAL_APPLICATION	SECURITY	true	https://docs.snowflake.com/user-guide/oauth-local-application	2026-09-21 18:33:42.250000-07:00

One row, and it is an OAUTH integration in the SECURITY category. There is no notification integration here.

So this post does not pretend to send mail. It does the part that is real, and later on it lets Snowflake say the rest itself.

Now let's create the alert

An alert needs a warehouse to run on and a schedule to run to. Then IF with a condition, then THEN with the action.

Query

CREATE OR REPLACE ALERT BIGORDER_T139
    WAREHOUSE = COMPUTE_WH
    SCHEDULE  = '1 MINUTE'
IF (EXISTS (
        SELECT ORDER_ID FROM ORDERS_T139 WHERE AMOUNT > 1000
))
THEN
    INSERT INTO RAISED_T139 (NOTE) VALUES ('a big order turned up');

Result1 row · 1 column

status
Alert BIGORDER_T139 successfully created.

Created. Now let's ask the catalog what it thinks it has.

SHOW ALERTS

SHOW ALERTS LIKE '%\\_T139';

Result1 row · 15 columns · scroll →

created_on	name	database_name	schema_name	owner	comment	warehouse	schedule	state	condition	action	owner_role_type	runbook	config	was_auto_suspended
2026-10-02 12:46:59.656000-07:00	BIGORDER_T139	DEMO_DB	PUBLIC	ACCOUNTADMIN		COMPUTE_WH	1 MINUTE	suspended	SELECT ORDER_ID FROM ORDERS_T139 WHERE AMOUNT > 1000	INSERT INTO RAISED_T139 (NOTE) VALUES ('a big order turned up')	ROLE	NULL	NULL	NULL

Fifteen columns, and two of them are the condition and the action, kept as text. So the catalog is holding the whole alert, not just its name.

Have a look at the state column. It reads suspended. Like a task, an alert arrives asleep. It will not run on its schedule until we resume it.

While that grid is in front of you, the blank-looking cells are worth a second. The comment cell is an empty string and the grid marks it empty. runbook, config and was_auto_suspended are real NULLs. Both look like nothing and they are not the same thing.

DESCRIBE ALERT gives us the same shape.

DESCRIBE ALERT

DESCRIBE ALERT BIGORDER_T139;

Result1 row · 15 columns · scroll →

created_on	name	database_name	schema_name	owner	comment	warehouse	schedule	state	condition	action	owner_role_type	runbook	config	was_auto_suspended
2026-10-02 12:46:59.656000-07:00	BIGORDER_T139	DEMO_DB	PUBLIC	ACCOUNTADMIN		COMPUTE_WH	1 MINUTE	suspended	SELECT ORDER_ID FROM ORDERS_T139 WHERE AMOUNT > 1000	INSERT INTO RAISED_T139 (NOTE) VALUES ('a big order turned up')	ROLE	NULL	NULL	NULL

The same fifteen columns for the one alert. There is no second, fuller view of an alert hiding somewhere.

Let's run it by hand while the condition is false

EXECUTE ALERT fires an alert once, now, whatever its schedule says. It is the same idea as EXECUTE TASK.

First let's check the raised table is empty.

Query

SELECT COUNT(*) AS ROWS_IN_THE_RAISED_TABLE FROM RAISED_T139;

Result1 row · 1 column

ROWS_IN_THE_RAISED_TABLE
0

Zero rows. Now let's fire the alert, with no big order in the table.

Query

EXECUTE ALERT BIGORDER_T139;

Result1 row · 1 column

status
Alert BIGORDER_T139 is scheduled to run immediately.

Accepted. The alert is scheduled to run immediately. Let's look at the raised table again.

Query

SELECT COUNT(*) AS ROWS_IN_THE_RAISED_TABLE FROM RAISED_T139;

Result1 row · 1 column

ROWS_IN_THE_RAISED_TABLE
0

Still zero, which is exactly what we wanted. And on its own it proves nothing at all.

Zero is not an answer

A run that asked the condition, got false and did nothing, and a run that never happened, look identical from here. Both leave an empty table.
That is the whole reason an alert has a history.

Now let's make the condition true

One order for five thousand is enough.

Query

INSERT INTO ORDERS_T139 (ORDER_ID, AMOUNT) VALUES (3, 5000.00);

Result1 row · 1 column

number of rows inserted
1

And the condition

SELECT COUNT(*) AS BIG_ORDERS FROM ORDERS_T139 WHERE AMOUNT > 1000;

Result1 row · 1 column

BIG_ORDERS
1

One. There is a big order now. Let's fire the alert again and read the message carefully this time.

Query

EXECUTE ALERT BIGORDER_T139;

Result1 row · 1 column

status
Alert BIGORDER_T139 is scheduled to run immediately after the alert completes its current run.

Let's check the raised table straight away.

Query

SELECT NOTE, COUNT(*) AS HOW_MANY FROM RAISED_T139 GROUP BY NOTE;

Result0 rows · 2 columns

NOTE	HOW_MANY

No rows. Which is the same ambiguous answer as before, and the reason is in the message we just got back. EXECUTE ALERT schedules a run. It does not perform one while we wait.

So where does a run leave a record?

INFORMATION_SCHEMA.ALERT_HISTORY() is a table function, and it is where the runs live. It keeps the condition, the action, a query id for each of them, an error code, an error message, the scheduled and completed times and where the run was scheduled from.

That full result is sixteen columns wide. Two of those columns are query ids from a real account, so it is not reproduced on this page. Let's ask for the four columns the teaching needs instead.

ALERT_HISTORY, four columns of it

SELECT NAME, STATE, SCHEDULED_TIME, COMPLETED_TIME
FROM TABLE(INFORMATION_SCHEMA.ALERT_HISTORY())
WHERE ENDSWITH(NAME, '_T139')
ORDER BY SCHEDULED_TIME;

Result8 rows · 4 columns

NAME	STATE	SCHEDULED_TIME	COMPLETED_TIME
BIGORDER_T139	TRIGGERED	2026-10-02 09:57:57.500000-07:00	2026-10-02 09:58:01.028000-07:00
BIGORDER_T139	TRIGGERED	2026-10-02 09:58:01.028000-07:00	2026-10-02 09:58:05.078000-07:00
BIGORDER_T139	TRIGGERED	2026-10-02 09:59:01.028000-07:00	2026-10-02 09:59:05.025000-07:00
FEEDALERT_T139	TRIGGERED	2026-10-02 09:59:17.308000-07:00	2026-10-02 09:59:20.933000-07:00
EMAIL_T139	ACTION_FAILED	2026-10-02 09:59:19.222000-07:00	2026-10-02 09:59:23.092000-07:00
BIGORDER_T139	CONDITION_FALSE	2026-10-02 09:59:20.917000-07:00	2026-10-02 09:59:22.916000-07:00
FEEDALERT_T139	TRIGGERED	2026-10-02 09:59:20.933000-07:00	2026-10-02 09:59:24.044000-07:00
BIGORDER_T139	EXECUTING	2026-10-02 12:47:00.873000-07:00	NULL

Eight rows. Read the STATE column down the grid, because it is the whole point of asking.

There are four different words in there. TRIGGERED means the condition was true and the action ran. CONDITION_FALSE means the run happened, asked, and found nothing to do. ACTION_FAILED means the condition was true and the action did not work, and we come back to that one later. EXECUTING is a run that had not finished when we asked, which is why its COMPLETED_TIME is a real NULL.

CONDITION_FALSE is the one that answers the question we could not answer from the raised table. An empty table told us nothing. A CONDITION_FALSE row tells us a run really did happen and really did decide there was nothing to do.

What this grid is and is not

Two honest things about that grid. The history is account wide, not per worksheet, and this demo file was run twice on the same day. So some of those timestamps are from the earlier run, and the run fired a moment ago is not in there yet.
A row that is missing from this grid is not an answer. Only a row that is present is.

Now let's resume it and leave it alone for a while

So far we have only fired it by hand. Let's let it run to its own schedule, which is one minute, with the condition true.

First, a count to compare against afterwards.

Query

SELECT COUNT(*) AS ROWS_BEFORE FROM RAISED_T139;

Result1 row · 1 column

ROWS_BEFORE
0

Resume it

ALTER ALERT BIGORDER_T139 RESUME;

Result1 row · 1 column

status
Statement executed successfully.

And its state

SHOW ALERTS LIKE 'BIGORDER\\_T139';

Result1 row · 15 columns · scroll →

created_on	name	database_name	schema_name	owner	comment	warehouse	schedule	state	condition	action	owner_role_type	runbook	config	was_auto_suspended
2026-10-02 12:46:59.656000-07:00	BIGORDER_T139	DEMO_DB	PUBLIC	ACCOUNTADMIN		COMPUTE_WH	1 MINUTE	started	SELECT ORDER_ID FROM ORDERS_T139 WHERE AMOUNT > 1000	INSERT INTO RAISED_T139 (NOTE) VALUES ('a big order turned up')	ROLE	NULL	NULL	false

started now, where it said suspended before. Now we wait.

A wait

SELECT SYSTEM$WAIT(75) AS WAITED;

Result1 row · 1 column

WAITED
waited 75 seconds

That is a wait, not a measurement. Nothing in this series is timed.

Let's suspend it before we look, so nothing fires while we are reading.

Suspend it

ALTER ALERT BIGORDER_T139 SUSPEND;

Result1 row · 1 column

status
Statement executed successfully.

And the count

SELECT COUNT(*) AS ROWS_AFTER FROM RAISED_T139;

Result1 row · 1 column

ROWS_AFTER
3

Three rows. From one order, one condition, and nothing changing in between.

And the history

SELECT NAME, STATE, SCHEDULED_TIME, COMPLETED_TIME
FROM TABLE(INFORMATION_SCHEMA.ALERT_HISTORY())
WHERE ENDSWITH(NAME, '_T139')
ORDER BY SCHEDULED_TIME;

Result10 rows · 4 columns

NAME	STATE	SCHEDULED_TIME	COMPLETED_TIME
BIGORDER_T139	TRIGGERED	2026-10-02 09:57:57.500000-07:00	2026-10-02 09:58:01.028000-07:00
BIGORDER_T139	TRIGGERED	2026-10-02 09:58:01.028000-07:00	2026-10-02 09:58:05.078000-07:00
BIGORDER_T139	TRIGGERED	2026-10-02 09:59:01.028000-07:00	2026-10-02 09:59:05.025000-07:00
FEEDALERT_T139	TRIGGERED	2026-10-02 09:59:17.308000-07:00	2026-10-02 09:59:20.933000-07:00
EMAIL_T139	ACTION_FAILED	2026-10-02 09:59:19.222000-07:00	2026-10-02 09:59:23.092000-07:00
BIGORDER_T139	CONDITION_FALSE	2026-10-02 09:59:20.917000-07:00	2026-10-02 09:59:22.916000-07:00
FEEDALERT_T139	TRIGGERED	2026-10-02 09:59:20.933000-07:00	2026-10-02 09:59:24.044000-07:00
BIGORDER_T139	TRIGGERED	2026-10-02 12:47:00.873000-07:00	2026-10-02 12:47:04.345000-07:00
BIGORDER_T139	TRIGGERED	2026-10-02 12:47:04.345000-07:00	2026-10-02 12:47:07.344000-07:00
BIGORDER_T139	TRIGGERED	2026-10-02 12:48:04.345000-07:00	2026-10-02 12:48:08.185000-07:00

Ten rows in the history now, and the new ones at the bottom say TRIGGERED.

And here is why there are three of them. Let's ask the condition one more time.

Query

SELECT COUNT(*) AS BIG_ORDERS FROM ORDERS_T139 WHERE AMOUNT > 1000;

Result1 row · 1 column

BIG_ORDERS
1

Still one. The order is still there, so the condition is still true, so every period the alert asked again and fired again. We suspended it. If we had not, it would still be going.

The part that surprises people

An alert does not consume its condition. It is a repeating question, not an event.
Which means an alert on a condition you cannot clear is an alert that never stops. If the action does not change the thing the condition asks about, nothing will.

Can an alert watch a stream?

A stream is the obvious condition to want. Let's make a feed table and a stream on it.

A table

CREATE OR REPLACE TABLE FEED_T139 (
    SALE_ID INT,
    AMOUNT  NUMBER(10,2)
);

Result1 row · 1 column

status
Table FEED_T139 successfully created.

A stream on it

CREATE OR REPLACE STREAM FEEDSTRM_T139 ON TABLE FEED_T139;

Result1 row · 1 column

status
Stream FEEDSTRM_T139 successfully created.

And an alert whose condition is the stream itself.

Query

CREATE OR REPLACE ALERT FEEDALERT_T139
    WAREHOUSE = COMPUTE_WH
    SCHEDULE  = '1 MINUTE'
IF (EXISTS (
        SELECT SALE_ID FROM FEEDSTRM_T139 WHERE METADATA$ACTION = 'INSERT'
))
THEN
    INSERT INTO RAISED_T139 (NOTE) VALUES ('the feed moved');

Result1 row · 1 column

status
Alert FEEDALERT_T139 successfully created.

Let's fire it with the stream empty.

Query

EXECUTE ALERT FEEDALERT_T139;

Result1 row · 1 column

status
Alert FEEDALERT_T139 is scheduled to run immediately.

And what is in the raised table

SELECT NOTE, COUNT(*) AS HOW_MANY FROM RAISED_T139 GROUP BY NOTE ORDER BY NOTE;

Result1 row · 2 columns

NOTE	HOW_MANY
a big order turned up	3

Only the big-order note, with its count of three. Nothing from the feed, which is right, because the feed is empty.

Now let's put a row in the feed.

Query

INSERT INTO FEED_T139 (SALE_ID, AMOUNT) VALUES (1, 1.00);

Result1 row · 1 column

number of rows inserted
1

And the stream

SELECT SYSTEM$STREAM_HAS_DATA('FEEDSTRM_T139') AS THE_STREAM_HAS_DATA;

Result1 row · 1 column

THE_STREAM_HAS_DATA
TRUE

TRUE. The stream has something in it. Let's fire the alert again.

Query

EXECUTE ALERT FEEDALERT_T139;

Result1 row · 1 column

status
Alert FEEDALERT_T139 is scheduled to run immediately after the alert completes its current run.

And the raised table

SELECT NOTE, COUNT(*) AS HOW_MANY FROM RAISED_T139 GROUP BY NOTE ORDER BY NOTE;

Result1 row · 2 columns

NOTE	HOW_MANY
a big order turned up	3

Still only the big-order note. Which is not the feed alert refusing to work. It is the same thing we saw earlier: EXECUTE ALERT schedules the run, and the run had not written its row yet when we asked. The feed note does turn up, in a grid further down this page.

Now the question worth asking. Did the alert spend the stream?

Query

SELECT SYSTEM$STREAM_HAS_DATA('FEEDSTRM_T139') AS THE_STREAM_HAS_DATA;

Result1 row · 1 column

THE_STREAM_HAS_DATA
TRUE

Still TRUE. The stream is untouched.

That follows from what a condition is. A condition is a SELECT, and Episode 136 showed that a SELECT on a stream consumes nothing. Only a DML statement moves a stream's offset.

So an alert can watch a stream without spending it. It will also keep firing for as long as the stream holds anything, which is the same trap as before wearing different clothes.

What happens when the action cannot work?

This is where the email comes in. Let's write an alert whose action calls SYSTEM$SEND_EMAIL with an integration name that does not exist, because this account has none to use.

Query

CREATE OR REPLACE ALERT EMAIL_T139
    WAREHOUSE = COMPUTE_WH
    SCHEDULE  = '1 MINUTE'
IF (EXISTS (
        SELECT ORDER_ID FROM ORDERS_T139 WHERE AMOUNT > 1000
))
THEN
    CALL SYSTEM$SEND_EMAIL(
        'NO_SUCH_INTEGRATION_T139',
        'nobody@example.com',
        'a big order turned up',
        'this is the body'
    );

Result1 row · 1 column

status
Alert EMAIL_T139 successfully created.

Created. Successfully. Which is the first surprise. Nothing checked the action.

The condition is still true, so if we fire it the action will be attempted.

Query

EXECUTE ALERT EMAIL_T139;

Result1 row · 1 column

status
Alert EMAIL_T139 is scheduled to run immediately.

Now the history, for this one alert.

Query

SELECT NAME, STATE, SCHEDULED_TIME, COMPLETED_TIME
FROM TABLE(INFORMATION_SCHEMA.ALERT_HISTORY())
WHERE NAME = 'EMAIL_T139'
ORDER BY SCHEDULED_TIME;

Result2 rows · 4 columns

NAME	STATE	SCHEDULED_TIME	COMPLETED_TIME
EMAIL_T139	ACTION_FAILED	2026-10-02 09:59:19.222000-07:00	2026-10-02 09:59:23.092000-07:00
EMAIL_T139	EXECUTING	2026-10-02 12:48:23.810000-07:00	NULL

ACTION_FAILED. Not triggered, not succeeded. Its own state. And below it an EXECUTING row with a NULL completion, which is the run we just fired.

To make the refusal completely unmistakable, let's call that procedure directly, outside any alert.

Query

CALL SYSTEM$SEND_EMAIL(
    'NO_SUCH_INTEGRATION_T139',
    'nobody@example.com',
    'a big order turned up',
    'this is the body'
);

Query failed — Snowflake returned an error

002003 (02000): Execution error in store procedure SYSTEM$SEND_EMAIL:
SQL compilation error:
Integration 'NO_SUCH_INTEGRATION_T139' does not exist or not authorized.
At Snowflake.execute, line 3 position 24

002003, and Snowflake's own sentence: the integration does not exist or is not authorised. That is what was happening inside the alert.

TRIGGERED is not success

Read TRIGGERED carefully from now on. It means the condition was true and the action ran.
It does not mean the action worked. That is what ACTION_FAILED is for, and an alert you are not watching will sit in that state quietly.

Now let's change the alert in place

The schedule, the condition and the action can all be altered without recreating the alert. Let's start with the schedule.

Query

ALTER ALERT BIGORDER_T139 SET SCHEDULE = '5 MINUTE';

Result1 row · 1 column

status
Statement executed successfully.

And the catalog

SHOW ALERTS LIKE 'BIGORDER\\_T139';

Result1 row · 15 columns · scroll →

created_on	name	database_name	schema_name	owner	comment	warehouse	schedule	state	condition	action	owner_role_type	runbook	config	was_auto_suspended
2026-10-02 12:46:59.656000-07:00	BIGORDER_T139	DEMO_DB	PUBLIC	ACCOUNTADMIN		COMPUTE_WH	5 MINUTE	suspended	SELECT ORDER_ID FROM ORDERS_T139 WHERE AMOUNT > 1000	INSERT INTO RAISED_T139 (NOTE) VALUES ('a big order turned up')	ROLE	NULL	NULL	false

5 MINUTE where it said 1 MINUTE. Now the condition and the action.

A new condition

ALTER ALERT BIGORDER_T139 MODIFY CONDITION EXISTS (
    SELECT ORDER_ID FROM ORDERS_T139 WHERE AMOUNT > 100000
);

Result1 row · 1 column

status
Statement executed successfully.

A new action

ALTER ALERT BIGORDER_T139 MODIFY ACTION
    INSERT INTO RAISED_T139 (NOTE) VALUES ('a very big order turned up');

Result1 row · 1 column

status
Statement executed successfully.

And what it looks like now

DESCRIBE ALERT BIGORDER_T139;

Result1 row · 15 columns · scroll →

created_on	name	database_name	schema_name	owner	comment	warehouse	schedule	state	condition	action	owner_role_type	runbook	config	was_auto_suspended
2026-10-02 12:46:59.656000-07:00	BIGORDER_T139	DEMO_DB	PUBLIC	ACCOUNTADMIN		COMPUTE_WH	5 MINUTE	suspended	SELECT ORDER_ID FROM ORDERS_T139 WHERE AMOUNT > 100000	INSERT INTO RAISED_T139 (NOTE) VALUES ('a very big order turned up')	ROLE	NULL	NULL	false

Both changes are in the stored text. The condition now looks for a hundred thousand, and the action writes a different note.

Nothing in our table is over a hundred thousand, so the new condition is false. Let's fire it anyway.

Query

EXECUTE ALERT BIGORDER_T139;

Result1 row · 1 column

status
Alert BIGORDER_T139 is scheduled to run immediately.

And what is in the raised table

SELECT NOTE, COUNT(*) AS HOW_MANY FROM RAISED_T139 GROUP BY NOTE ORDER BY NOTE;

Result2 rows · 2 columns

NOTE	HOW_MANY
a big order turned up	3
the feed moved	1

Two notes now, and the new one is not among them. The big-order note with its three, and the feed note with its one. That is the feed alert's row, arriving late, exactly as promised further up the page.

So the alter landed and the new action never ran, because the new condition was false. Which is the alert working properly.

What does CREATE ALERT accept without checking?

Three experiments, and the last one is the one worth remembering. First, an alert with no schedule at all.

Query

CREATE OR REPLACE ALERT NOSCHED_T139
    WAREHOUSE = COMPUTE_WH
IF (EXISTS (SELECT ORDER_ID FROM ORDERS_T139))
THEN
    INSERT INTO RAISED_T139 (NOTE) VALUES ('no schedule');

Result1 row · 1 column

status
Alert NOSCHED_T139 successfully created.

Created. We will see its schedule in a moment, and it is a NULL.

Second, a condition that is not an EXISTS.

Query

CREATE OR REPLACE ALERT PLAINCOND_T139
    WAREHOUSE = COMPUTE_WH
    SCHEDULE  = '1 MINUTE'
IF (SELECT COUNT(*) FROM ORDERS_T139)
THEN
    INSERT INTO RAISED_T139 (NOTE) VALUES ('plain condition');

Query failed — Snowflake returned an error

001003 (42000): SQL compilation error:
syntax error line 4 at position 4 unexpected 'SELECT'.

001003, a syntax error, unexpected SELECT. So the condition has to be an EXISTS. That is not a style choice, it is the grammar of the statement.

Third, an alert over a table that does not exist.

Query

CREATE OR REPLACE ALERT NOTABLE_T139
    WAREHOUSE = COMPUTE_WH
    SCHEDULE  = '1 MINUTE'
IF (EXISTS (SELECT ORDER_ID FROM NO_SUCH_TABLE_T139))
THEN
    INSERT INTO RAISED_T139 (NOTE) VALUES ('no table');

Result1 row · 1 column

status
Alert NOTABLE_T139 successfully created.

Created successfully. Nothing validated the condition at create time.

Healthy looking and broken

That one deserves a moment. An alert can sit in the catalog looking perfectly healthy, with a condition that cannot run, and fail every single time it fires.
The only place you would find out is the history, and only if you went and looked.

Let's see all of them together, suspended, before we clear up.

SHOW ALERTS

SHOW ALERTS LIKE '%\\_T139';

Result5 rows · 15 columns · scroll →

created_on	name	database_name	schema_name	owner	comment	warehouse	schedule	state	condition	action	owner_role_type	runbook	config	was_auto_suspended
2026-10-02 12:46:59.656000-07:00	BIGORDER_T139	DEMO_DB	PUBLIC	ACCOUNTADMIN		COMPUTE_WH	5 MINUTE	suspended	SELECT ORDER_ID FROM ORDERS_T139 WHERE AMOUNT > 100000	INSERT INTO RAISED_T139 (NOTE) VALUES ('a very big order turned up')	ROLE	NULL	NULL	false
2026-10-02 12:48:23.555000-07:00	EMAIL_T139	DEMO_DB	PUBLIC	ACCOUNTADMIN		COMPUTE_WH	1 MINUTE	suspended	SELECT ORDER_ID FROM ORDERS_T139 WHERE AMOUNT > 1000	CALL SYSTEM$SEND_EMAIL( 'NO_SUCH_INTEGRATION_T139', 'nobody@example.com', 'a big order turned up', 'this is the body' )	ROLE	NULL	NULL	NULL
2026-10-02 12:48:21.149000-07:00	FEEDALERT_T139	DEMO_DB	PUBLIC	ACCOUNTADMIN		COMPUTE_WH	1 MINUTE	suspended	SELECT SALE_ID FROM FEEDSTRM_T139 WHERE METADATA$ACTION = 'INSERT'	INSERT INTO RAISED_T139 (NOTE) VALUES ('the feed moved')	ROLE	NULL	NULL	NULL
2026-10-02 12:48:26.986000-07:00	NOSCHED_T139	DEMO_DB	PUBLIC	ACCOUNTADMIN		COMPUTE_WH	NULL	suspended	SELECT ORDER_ID FROM ORDERS_T139	INSERT INTO RAISED_T139 (NOTE) VALUES ('no schedule')	ROLE	NULL	NULL	NULL
2026-10-02 12:48:27.343000-07:00	NOTABLE_T139	DEMO_DB	PUBLIC	ACCOUNTADMIN		COMPUTE_WH	1 MINUTE	suspended	SELECT ORDER_ID FROM NO_SUCH_TABLE_T139	INSERT INTO RAISED_T139 (NOTE) VALUES ('no table')	ROLE	NULL	NULL	NULL

Five alerts, all suspended. Read the schedule column down the grid and you will find the one with no schedule, holding a real NULL, sitting beside four that have one.

One last thing: an alert that is not there

Three statements, one missing alert, and the same answer each time.

EXECUTE

EXECUTE ALERT NOSUCHALERT_T139;

Query failed — Snowflake returned an error

002003 (02000): SQL compilation error:
Alert 'DEMO_DB.PUBLIC.NOSUCHALERT_T139' does not exist or not authorized. Your primary role ACCOUNTADMIN or one of your secondary roles must have at least one privilege granted on ALERT DEMO_DB.PUBLIC.NOSUCHALERT_T139.

DESCRIBE

DESCRIBE ALERT NOSUCHALERT_T139;

Query failed — Snowflake returned an error

002003 (02000): SQL compilation error:
Alert 'DEMO_DB.PUBLIC.NOSUCHALERT_T139' does not exist or not authorized. Your primary role ACCOUNTADMIN or one of your secondary roles must have at least one privilege granted on ALERT DEMO_DB.PUBLIC.NOSUCHALERT_T139.

RESUME

ALTER ALERT NOSUCHALERT_T139 RESUME;

Query failed — Snowflake returned an error

002003 (02000): SQL compilation error:
Alert 'DEMO_DB.PUBLIC.NOSUCHALERT_T139' does not exist or not authorized. Your primary role ACCOUNTADMIN or one of your secondary roles must have at least one privilege granted on ALERT DEMO_DB.PUBLIC.NOSUCHALERT_T139.

002003 for all three, with the same wording. Snowflake does not distinguish between an alert that is not there and an alert you are not allowed to see.

Then everything goes. Each alert is suspended before it is dropped, which is the habit worth having.

Teardown

ALTER ALERT IF EXISTS BIGORDER_T139 SUSPEND;

Result1 row · 1 column

status
Statement executed successfully.

Teardown

DROP ALERT IF EXISTS BIGORDER_T139;

Result1 row · 1 column

status
BIGORDER_T139 successfully dropped.

Teardown

DROP ALERT IF EXISTS FEEDALERT_T139;

Result1 row · 1 column

status
FEEDALERT_T139 successfully dropped.

Teardown, for one that was never created

DROP ALERT IF EXISTS PLAINCOND_T139;

Result1 row · 1 column

status
Drop statement executed successfully (PLAINCOND_T139 already dropped).

That last message is different from the others. The alert with the plain condition was refused at create time, so there was nothing to drop, and DROP ALERT IF EXISTS says so without complaining.

Teardown

DROP STREAM IF EXISTS FEEDSTRM_T139;

Result1 row · 1 column

status
FEEDSTRM_T139 successfully dropped.

Teardown

DROP TABLE IF EXISTS ORDERS_T139;

Result1 row · 1 column

status
ORDERS_T139 successfully dropped.

And the checks.

Where we are

SELECT CURRENT_DATABASE() AS THE_DATABASE,
       CURRENT_SCHEMA()   AS THE_SCHEMA;

Result1 row · 2 columns

THE_DATABASE	THE_SCHEMA
DEMO_DB	PUBLIC

And what is left

SHOW ALERTS LIKE '%\\_T139';

Result0 rows · 15 columns · scroll →

created_on	name	database_name	schema_name	owner	comment	warehouse	schedule	state	condition	action	owner_role_type	runbook	config	was_auto_suspended

And the tables

SELECT COUNT(*) AS PROBE_TABLES_LEFT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC'
  AND ENDSWITH(TABLE_NAME, '_T139');

Result1 row · 1 column

PROBE_TABLES_LEFT
0

Still in PUBLIC, no alerts of ours left running, and no tables of ours left behind. Back where we started.

Key points

• An alert is a condition, an action and a schedule, and it is created suspended.
• SHOW ALERTS and DESCRIBE ALERT both give fifteen columns, and the condition and the action are two of them.
• EXECUTE ALERT schedules a run immediately. It does not perform one while you wait, so looking at the target table straight afterwards can show you nothing.
• An empty target table cannot tell a run that found nothing from a run that never happened. INFORMATION_SCHEMA.ALERT_HISTORY() can.
• That history carries four states in this episode: TRIGGERED, CONDITION_FALSE, ACTION_FAILED and EXECUTING.
• TRIGGERED means the action ran. It does not mean the action worked. ACTION_FAILED is its own state.
• An alert does not consume its condition. Resumed with the condition true, it raised three rows from one order and would have carried on.
• A condition can read a stream, and the stream still has data afterwards, because a condition is a SELECT.
• SYSTEM$SEND_EMAIL needs a notification integration. Without one it refuses with 002003, and the alert that calls it is still created happily.
• The schedule, the condition and the action can all be altered in place, and DESCRIBE shows the new text.
• A condition that is not an EXISTS is 001003. That is the grammar, not a preference.
• An alert over a table that does not exist is created without complaint, so an alert can look healthy and fail on every run.
• EXECUTE, DESCRIBE and RESUME on an alert that is not there all give 002003.

Next in this series

That is Level 11 finished. Episode 140 opens Level 12, Programmability, with session variables: setting a value, reading it back with a dollar sign, where a variable can and cannot stand in for a piece of SQL, and a view that answers differently once the variable behind it changes.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.35.101) running the demo script for this episode. The full ALERT_HISTORY result is sixteen columns wide and two of them are query ids, so it is not reproduced here; the four-column projections on this page carry the same rows. No account identifier, user name or query id appears anywhere on this page. Every count in the text is read off one of the grids above. Nothing is timed and no performance claims are made anywhere in this series — the one SYSTEM$WAIT is a wait, not a measurement. Behaviour follows the Snowflake CREATE ALERT documentation.