COF-C03 Question 190
Single answerAlertsA data engineering team wants to monitor a SALES_RAW table and notify operations when more than 500 rows with NULL ORDER_ID values are loaded in a day. They create a Snowflake Alert that runs every hour and uses a condition query to count the bad rows for the current date. The alert should send an email only when the threshold is exceeded. Which approach will meet this requirement?
- A
Create an alert with a schedule, define the IF condition query to return TRUE when the count of bad rows is greater than 500, and specify a THEN action that calls SYSTEM$SEND_EMAIL using a configured notification integration.
- B
Create a task that queries SALES_RAW every hour and automatically sends email directly from the warehouse whenever the SELECT statement returns more than 500 rows.
- C
Create an alert without a schedule so that Snowflake evaluates the condition automatically whenever new rows are inserted into SALES_RAW, and configure the alert action to send email.
- D
Create a materialized view on the bad rows and attach an alert to the materialized view so that Snowflake emails users whenever the view changes.
Show answer and explanation
Correct answer: A
Explanation
The correct design is to use a Snowflake Alert with three key components: a schedule, a condition, and an action. In this scenario, the alert should run hourly, evaluate a query such as counting rows in SALES_RAW where ORDER_ID is NULL and the load date is today, and execute the action only when that count exceeds 500. For email delivery, Snowflake supports sending email through SYSTEM$SEND_EMAIL when an email notification integration has been configured. This reflects Snowflake best practices for alerts: use alerts for scheduled conditional monitoring and use notification integrations for outbound notifications. A common mistake is to assume alerts are event-driven like triggers; however, Snowflake alerts are evaluated according to their defined schedule rather than firing automatically on table inserts or view changes.
- A. Correct.
Correct. A Snowflake alert consists of a schedule, a condition, and an action. The condition query is evaluated on the defined schedule, and if it returns TRUE, the alert executes the action. To send email, the action can call SYSTEM$SEND_EMAIL, which requires a configured email notification integration. This matches the scenario exactly: hourly evaluation and email only when the threshold is exceeded.
- B. Incorrect.
Incorrect. A task can run SQL on a schedule, but tasks do not inherently 'send email directly from the warehouse.' Email notifications in Snowflake require the appropriate notification mechanism, such as SYSTEM$SEND_EMAIL with a notification integration. The question specifically asks for an Alerts-based solution, and this option misrepresents task capabilities.
- C. Incorrect.
Incorrect. Snowflake alerts are schedule-driven; they are not automatically triggered by DML events such as inserts into a table. This option reflects a common misconception that alerts are event-based rather than evaluated on a defined schedule.
- D. Incorrect.
Incorrect. Materialized views can optimize query performance for repeated computations, but Snowflake does not support 'attaching an alert' to a materialized view so it automatically emails when the view changes. Alerts run their own scheduled condition query and are not bound to object-change events in this way.