ADA-C01 Question 36
Single answerAnalyze and audit user and query activity history using the ACCOUNT_USAGE and ORGANIZATION_USAGE schemasA global company uses Snowflake Organizations with several production accounts in different regions. A senior administrator is asked to investigate a security incident: determine which user executed a specific high-cost query pattern across multiple accounts during the last 14 days, and include the account where each query ran. The security team wants to use Snowflake-provided historical views rather than custom logging tables. Which approach should the administrator take?
- A
Query SNOWFLAKE.ORGANIZATION_USAGE.QUERY_HISTORY and join to organization-level user/account identifiers to analyze query activity across accounts in the organization.
- B
Query SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY from any one account, because ACCOUNT_USAGE automatically includes query history for all accounts in the organization.
- C
Query INFORMATION_SCHEMA.QUERY_HISTORY in the organization's primary account, because Information Schema is designed for consolidated cross-account historical reporting.
- D
Query SNOWFLAKE.ORGANIZATION_USAGE.ACCESS_HISTORY, because it is the only organization-wide source that contains all executed SQL text for every query.
Show answer and explanation
Correct answer: A
Explanation
The key decision point is scope. SNOWFLAKE.ACCOUNT_USAGE is intended for historical metadata and activity analysis within a single account, while SNOWFLAKE.ORGANIZATION_USAGE is intended for organization-level visibility across multiple accounts. Because the scenario requires auditing query activity across several production accounts and attributing each query to both a user and an account, ORGANIZATION_USAGE.QUERY_HISTORY is the best fit. INFORMATION_SCHEMA query history functions are useful for operational troubleshooting but are not the right choice for centralized, cross-account historical analysis. ACCESS_HISTORY serves a different purpose: understanding object access and lineage rather than acting as the primary source for broad query-pattern investigation. This aligns with Snowflake best practices for choosing ACCOUNT_USAGE for account-level auditing and ORGANIZATION_USAGE for centralized organization-wide reporting.
- A. Correct.
Correct. For cross-account auditing at the organization level, the administrator should use the SNOWFLAKE.ORGANIZATION_USAGE schema. ORGANIZATION_USAGE provides organization-wide views intended for centralized reporting across accounts, including query history data with account context. This matches the requirement to identify user activity across multiple accounts and include which account executed each query.
- B. Incorrect.
Incorrect. SNOWFLAKE.ACCOUNT_USAGE is scoped to a single Snowflake account. Its QUERY_HISTORY view is useful for auditing within that account, but it does not automatically aggregate query history from other accounts in the organization. This is a common misconception when organizations have many accounts under one Snowflake Organization.
- C. Incorrect.
Incorrect. INFORMATION_SCHEMA table functions and views can expose query history, but they are account-scoped and generally oriented toward more immediate operational access rather than centralized cross-account historical reporting. Using INFORMATION_SCHEMA in one account will not provide consolidated organization-wide query history across separate accounts.
- D. Incorrect.
Incorrect. ACCESS_HISTORY is used to analyze object-level access lineage and what data objects were touched, which is valuable for auditing data access. However, it is not the correct primary source when the requirement is to identify a specific query pattern and the user who executed it across accounts. QUERY_HISTORY is the more direct and appropriate source for executed query activity and associated account context.