SnowPro Advanced: Security Engineer Question 340
Single answerMonitor Snowflake logsA security engineer needs to investigate whether a privileged role was used to run suspicious SQL statements over the last 7 days and wants to automate ongoing monitoring. The account has Enterprise Edition, and the engineer needs a solution that can be queried with SQL, retained longer than the Information Schema table functions, and enriched later with other account activity data. Which approach best meets these requirements?
- A
Query QUERY_HISTORY in the Information Schema each day and export the results manually to CSV files for long-term retention.
- B
Use the ACCOUNT_USAGE views in the shared SNOWFLAKE database, such as QUERY_HISTORY and LOGIN_HISTORY, and persist the relevant results into a security monitoring table or pipeline.
- C
Enable object event logging on the suspect schemas and use those logs as the primary source for all SQL text and role usage details.
- D
Use SHOW GRANTS and SHOW ROLES output to determine which SQL statements were executed by privileged roles during the last 7 days.
Show answer and explanation
Correct answer: B
Explanation
For monitoring Snowflake logs and investigating suspicious privileged activity, the best practice is to use the SNOWFLAKE database's ACCOUNT_USAGE views for account-level auditing and monitoring. In this scenario, QUERY_HISTORY can help identify executed SQL text, execution context, and related metadata, while LOGIN_HISTORY can support correlation with authentication events. Compared with Information Schema history functions, ACCOUNT_USAGE is generally better suited for centralized security analytics because it offers longer retention and is easier to integrate into repeatable SQL-based monitoring workflows. A common operational pattern is to schedule queries or ETL jobs that persist relevant ACCOUNT_USAGE data into dedicated monitoring tables for alerting and investigation. This aligns with Snowflake documentation on Account Usage and access/query history for security monitoring and audit use cases.
- A. Incorrect.
Incorrect. Information Schema table functions such as QUERY_HISTORY are useful for recent operational investigation, but they are not the best fit for durable monitoring and longer-term analysis requirements. They have more limited retention windows than ACCOUNT_USAGE and do not by themselves provide an automated, centralized monitoring pattern. Manual CSV export also does not scale well for ongoing security monitoring.
- B. Correct.
Correct. The SNOWFLAKE shared database includes ACCOUNT_USAGE views such as QUERY_HISTORY and LOGIN_HISTORY that are designed for account-level monitoring and auditing use cases. These views can be queried with SQL, joined with other account activity metadata, and used to populate internal security monitoring tables or downstream pipelines. This aligns with the requirement for longer retention than Information Schema history functions and supports ongoing automated analysis of privileged activity.
- C. Incorrect.
Incorrect. Object event logging is not the primary mechanism for capturing full SQL execution history and role usage for account-wide investigation. Suspicious SQL statements and login/account activity are typically monitored through query and access history sources such as ACCOUNT_USAGE views, not by relying on object-level event logging as the main audit source.
- D. Incorrect.
Incorrect. SHOW GRANTS and SHOW ROLES help identify role assignments, privilege inheritance, and current security configuration, but they do not provide execution history showing which SQL statements were actually run over the last 7 days. This is a common confusion between configuration metadata and activity/audit data.