SQL source code used in the Snowflake Resource Monitor reports delivered to Slack using Apache Nifi article: https://medium.com/@ryan_templeton/snowflake-resource-monitor-reports-delivered-to-slack-using-apache-nifi-8dfd4fc4d579

SnowflakeCreditMonitoring.sql

--create a table to hold the metrics you wish to monitor and their threshold values
CREATE TABLE "MY_TEST_DB"."PUBLIC"."THRESHOLDS" ("SERVICE_TYPE" STRING NOT NULL, "CREDITS_BILLED" DOUBLE NOT NULL) COMMENT = 'Used for the Nifi alerting demo';

--insert some sample records into the thresholds table
insert into "MY_TEST_DB"."PUBLIC"."THRESHOLDS" values ('AUTO_CLUSTERING', 10),('PIPE', 10),('MATERIALIZED_VIEW', 10),('WAREHOUSE_METERING', 10);

--query to compare current metrics to threshold values
--This is used for the HOURLY report
select a.*, iff(b.credits_billed is null, 0, b.credits_billed)::string as credits_billed from
"MY_TEST_DB"."PUBLIC"."THRESHOLDS" a left join
(select service_type, sum(credits_billed::double) as credits_billed from "SNOWFLAKE"."ACCOUNT_USAGE"."METERING_DAILY_HISTORY" where usage_date=current_date() group by 1) b
on a.service_type = b.service_type;

--same query as above but this only shows rows where the metric exceedes the threshold
--This is used for the 15min interval altert
select service_type, credits_billed from (
select a.*, iff(b.credits_billed is null, 0, b.credits_billed)::string as credits_billed from
"MY_TEST_DB"."PUBLIC"."THRESHOLDS" a left join
(select service_type, sum(credits_billed::double) as credits_billed from "SNOWFLAKE"."ACCOUNT_USAGE"."METERING_DAILY_HISTORY" where usage_date=current_date() group by 1) b
on a.service_type = b.service_type
) where credits_billed > credits_threshold