ADA-C01 Question 35
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. The security team asks an administrator to produce a weekly report that identifies which users executed the most queries across all accounts in the organization during the last 7 days. The report must include the account where each query ran and should use Snowflake-provided historical views rather than custom logging. Which approach should the administrator use?
- A
Query the SNOWFLAKE.ORGANIZATION_USAGE.QUERY_HISTORY view, aggregate by user and account, and filter on the last 7 days.
- B
Query the SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY view from any one account, because ACCOUNT_USAGE automatically includes query history for all accounts in the organization.
- C
Query INFORMATION_SCHEMA.QUERY_HISTORY in each account and union the results, because ORGANIZATION_USAGE does not provide query history.
- D
Query SNOWFLAKE.ORGANIZATION_USAGE.ACCESS_HISTORY, because it is the only organization-level view that contains all executed SQL statements across accounts.
Show answer and explanation
Correct answer: A
Explanation
For cross-account auditing and reporting, Snowflake provides the SNOWFLAKE.ORGANIZATION_USAGE schema, which exposes organization-level usage views for organizations that need visibility across accounts. In contrast, SNOWFLAKE.ACCOUNT_USAGE is limited to the current account. For this scenario, the administrator needs query activity history across all accounts, including the account in which each query ran, so ORGANIZATION_USAGE.QUERY_HISTORY is the correct source. QUERY_HISTORY is the appropriate view for counting and attributing executed queries, while ACCESS_HISTORY is intended for deeper governance analysis of data/object access rather than general query-volume reporting. This aligns with Snowflake best practices: use ACCOUNT_USAGE for per-account historical auditing and ORGANIZATION_USAGE for centralized, cross-account visibility within an organization.
- A. Correct.
Correct. ORGANIZATION_USAGE is designed for organization-wide monitoring and reporting across accounts. The QUERY_HISTORY view in SNOWFLAKE.ORGANIZATION_USAGE can be used to analyze query activity across multiple accounts, including account-level identifiers, which makes it appropriate for a cross-account report of users executing the most queries in the last 7 days.
- B. Incorrect.
Incorrect. ACCOUNT_USAGE is scoped to a single Snowflake account, not the entire organization. A common misconception is that querying ACCOUNT_USAGE from an org admin context automatically spans all accounts, but it does not. To analyze activity across multiple accounts without custom consolidation, ORGANIZATION_USAGE should be used.
- C. Incorrect.
Incorrect. INFORMATION_SCHEMA table functions and views are account-specific and typically suited for shorter retention and operational lookups, not centralized organization-wide historical reporting. While unioning results from each account could work operationally, the scenario explicitly requires Snowflake-provided historical views for organization-wide reporting, which is what ORGANIZATION_USAGE is intended to provide.
- D. Incorrect.
Incorrect. ACCESS_HISTORY is used for object access lineage and governance-style auditing, such as identifying which objects were accessed by a query. It is not the best source for counting all queries by user across accounts. QUERY_HISTORY is the appropriate source when the requirement is to identify executed queries and aggregate them by user and account.