SnowPro Advanced: Security Engineer Question 268
Single answerConfigure email or external integrations for security alerts using tasks and streamsA security engineering team wants Snowflake to notify the on-call mailbox whenever a new high-risk login event is written to a table named SECURITY_DB.MONITORING.LOGIN_EVENTS. They only want to alert once per newly inserted event and avoid sending duplicate emails if the task runs repeatedly on a schedule. The team has already created a notification integration for email delivery. Which design best meets these requirements?
- A
Create a stream on SECURITY_DB.MONITORING.LOGIN_EVENTS, then create a scheduled task that checks the stream for new rows with HIGH risk and calls SYSTEM$SEND_EMAIL using the existing notification integration.
- B
Create a scheduled task that queries SECURITY_DB.MONITORING.LOGIN_EVENTS directly for HIGH risk rows and calls SYSTEM$SEND_EMAIL for every matching row each time the task runs.
- C
Create an external function that polls SECURITY_DB.MONITORING.LOGIN_EVENTS and sends email through a cloud messaging service, because Snowflake tasks cannot send notifications directly.
- D
Create an alert on SECURITY_DB.MONITORING.LOGIN_EVENTS that sends email through the notification integration, because alerts automatically track row-level changes without requiring streams.
Show answer and explanation
Correct answer: A
Explanation
The best design is to use a stream on the LOGIN_EVENTS table together with a scheduled task. Streams are Snowflake’s native mechanism for change data capture and are designed for incremental consumption patterns. A task can run on a schedule, read only unconsumed changes from the stream, filter for HIGH risk login events, and then invoke SYSTEM$SEND_EMAIL with the existing email notification integration. This aligns with Snowflake best practices for event-driven or near-real-time security monitoring workflows where duplicate notifications must be minimized.
Why this matters in security engineering: alerting from the full base table is risky because repeated task executions can generate duplicate notifications unless additional state tracking is built. Streams simplify this by maintaining offsets over table changes. Snowflake documentation for streams and tasks describes this pattern for incremental processing, and the notification integration plus SYSTEM$SEND_EMAIL documentation covers email delivery from SQL-driven workflows. In a certification context, the key applied concept is choosing the native Snowflake objects that together provide both change detection and automated response.
- A. Correct.
Correct. A stream records change data capture metadata for inserts, updates, and deletes on the source object, allowing the task to process only newly arrived rows since the last consumption point. Pairing a stream with a scheduled task is the standard pattern for exactly-once-style incremental processing in Snowflake. If the task filters the stream contents to only HIGH risk events and then calls SYSTEM$SEND_EMAIL using the preconfigured notification integration, it avoids repeatedly reprocessing the full base table and reduces duplicate alerts.
- B. Incorrect.
Incorrect. Querying the base table directly on every schedule does not provide built-in change tracking for only new rows. Unless the team adds and correctly maintains separate state management logic, the task could resend alerts for the same HIGH risk rows every time it runs. This is a common mistake when candidates overlook the role of streams in incremental processing.
- C. Incorrect.
Incorrect. Snowflake tasks can invoke SQL and supported system functions, including SYSTEM$SEND_EMAIL when an email notification integration is configured. An external function is not required just to send email notifications from Snowflake. This option reflects the misconception that all outbound notification workflows must be implemented through external services.
- D. Incorrect.
Incorrect. Snowflake alerts can evaluate a condition and perform an action, but this option is wrong because it claims alerts automatically provide row-level change tracking without streams. When the requirement is to process newly inserted rows exactly once and avoid duplicate processing across runs, streams are the appropriate mechanism for change capture. Relying on the base table alone does not satisfy the stated requirement.