COF-C03 Question 191
Single answerAlertsA data engineering team wants to monitor a SALES.ORDERS table and automatically notify operators whenever the number of orders with STATUS = 'FAILED' exceeds 100 in a 15-minute period. They need the check to run on a schedule without building an external scheduler. Which solution best meets this requirement using Snowflake Alerts?
- A
Create an ALERT with a SCHEDULE, define a CONDITION query that counts failed orders in the last 15 minutes, and configure an ACTION that sends a notification when the condition evaluates to TRUE.
- B
Create a TASK that queries SALES.ORDERS every 15 minutes and returns the failed count; Snowflake will automatically send an alert email whenever the query result is greater than 100.
- C
Create a MATERIALIZED VIEW on failed orders and attach an ALERT directly to the view so Snowflake automatically monitors it in real time.
- D
Create a STREAM on SALES.ORDERS and rely on the stream itself to send notifications whenever more than 100 failed rows are captured within 15 minutes.
Show answer and explanation
Correct answer: A
Explanation
Snowflake Alerts are intended to evaluate a SQL condition on a schedule and execute an action when the condition is met. This makes them appropriate for monitoring business or operational thresholds, such as failed order counts over a recent time window, without relying on an external orchestrator. In practice, the alert would use a schedule such as every 15 minutes, a condition query that counts rows where STATUS = 'FAILED' and timestamp criteria match the last 15 minutes, and an action such as calling a notification integration or executing SQL logic. Tasks are also schedulable, but they are general-purpose orchestration objects and do not automatically behave as alerts. Streams and materialized views can support data processing or query optimization, but they do not replace the core alert pattern of schedule + condition + action. This aligns with Snowflake documentation and best practices for native monitoring and automation with Alerts.
- A. Correct.
Correct. A Snowflake alert is designed for scheduled condition monitoring. You define a SCHEDULE, a CONDITION expression based on a SQL query, and an ACTION to execute when the condition is TRUE. This matches the requirement to run every 15 minutes without an external scheduler and trigger a notification or other SQL-based action.
- B. Incorrect.
Incorrect. Tasks can run SQL on a schedule, but they do not automatically send notifications simply because a query returns a threshold value. A task would require additional logic and is not itself an alerting mechanism. This option reflects the common misconception that scheduled query results inherently produce notifications.
- C. Incorrect.
Incorrect. Materialized views improve query performance for eligible queries, but Snowflake alerts are not attached directly to a materialized view for automatic real-time monitoring in that way. An alert can query a table or view, but the alert object still needs its own schedule and condition.
- D. Incorrect.
Incorrect. Streams track change data capture metadata for tables and views, but a stream does not independently schedule checks or send notifications. A stream could be part of a broader solution, but by itself it does not satisfy the requirement for scheduled threshold-based alerting.