ADA-C02 SnowPro Advanced: Administrator Questions and Answers

Snowflake multi-cluster warehouses primarily address query concurrency rather than the computational demands of individual complex statements. Increasing the number of clusters allows additional queries to execute concurrently, but does not necessarily reduce the execution time of an individual query.

Option B is appropriate because a separate, correctly sized warehouse isolates the monthly analytical workload from regular reporting activity. This prevents resource-intensive queries from consuming capacity needed by everyday users. It also allows independent warehouse sizing, scheduling, and cost monitoring.

Option D addresses the underlying workload. Appropriate clustering keys can improve micro-partition pruning, while materialized views can reduce repeated scanning and aggregation. Both techniques should be evaluated against actual query profiles.

Option A introduces scheduled resizing, which could help in some circumstances but requires accurate workload scheduling and might increase compute costs unnecessarily.

Option E increases baseline capacity without resolving inefficient statements and can increase running costs.

Current-version qualification: Option C is no longer categorically invalid. Snowflake now supports more than ten clusters for eligible warehouse sizes, including up to 40 clusters for a 2X-Large warehouse. Increasing the maximum can reduce concurrency-related queueing, although it does not necessarily improve individual query execution time.

Therefore, B and D are the strongest two answers, but C is conditionally valid under current Snowflake capabilities, making the question potentially ambiguous without additional warehouse-size information.

Official Snowflake references: Multi-cluster Warehouses; Limiting Concurrently Running Queries; Working with Materialized Views.

Snowflake Marketplace providers must maintain stable schemas and object identifiers to prevent disruption to consumer applications, analytical queries, and automated data pipelines.

Option A is correct because dropping a column changes the structure of the shared dataset. Consumer queries, views, or ingestion processes that reference the removed column may subsequently fail.

Option D is correct because renaming a table changes the identifier that consumers use when referencing the shared object. Existing queries using the original name may become invalid unless compatible replacement objects or migration arrangements are provided.

Snowflake explicitly identifies column additions or removals, object renaming, and object removal as examples of breaking changes to shared data products.

Option B describes a data modification rather than an automatic schema or interface incompatibility. Although deleting records may affect downstream business results, it does not inherently constitute the structural breaking change addressed by the question.

Option C concerns publication and availability rather than a schema modification. Unpublishing can affect product discovery or distribution, but its precise effects depend on listing lifecycle behavior.

Option E expands regional availability without changing existing consumer-facing object definitions.

Technical qualification: Unpublishing a listing can have serious operational implications, and deleting essential data can break downstream business logic. Therefore, the question should explicitly refer to schema and object-interface breaking changes to eliminate ambiguity.

Official Snowflake references: Modify Published Listings: Update a Data Share.

Snowflake future grants automatically assign privileges to qualifying database objects when those objects are created. They provide a scalable mechanism for maintaining consistent access across development teams.

Option C is the intended solution because the administrator can configure future SELECT grants on tables created in the new schema, assigning them to the shared DEV_TEAM role.

For example:

GRANT SELECT

ON FUTURE TABLES IN SCHEMA DEV_DB01.PROJECT_SCHEMA

TO ROLE DEV_TEAM;

Developers assigned roles that inherit DEV_TEAM can subsequently access the newly created tables through that shared role, provided the necessary parent-object privileges are also available.

Option A is unsuitable because transferring schema ownership does not automatically grant the required SELECT privileges on every subsequently created table.

Option B is incorrect because DEV_TEAM already possesses warehouse USAGE, and its privileges are inherited by JDOE_PROFILE.

Option D centralizes grant administration but does not independently assign SELECT privileges to DEV_TEAM.

Technical correction required: The source uses DEFAULT_ROLE='JDOE PROFILE', whereas the created role is JDOE_PROFILE. These names differ. The typographic quotation marks also need correction for executable SQL. Furthermore, future SELECT grants alone are insufficient if DEV_TEAM lacks USAGE on the newly created schema. Administrators must ensure both schema access and appropriate table privileges are granted.

Official Snowflake references: Configuring Access Control — Future Grants; CREATE USER; Overview of Access Control.

Snowflake supports statistical sampling through the SAMPLE and TABLESAMPLE SQL clauses. The two principal sampling methods are BERNOULLI, also called ROW sampling, and SYSTEM, also called BLOCK sampling.

SYSTEM sampling evaluates blocks of rows rather than independently selecting individual rows. The specified percentage represents the probability that each block will be included in the resulting sample.

Option B is correct because:

SELECT *

FROM LINEITEM

SAMPLE SYSTEM (20);

selects blocks using a 20% inclusion probability without specifying a deterministic random seed.

Consequently, subsequent executions may select different blocks and produce different samples.

Option A uses BERNOULLI sampling, which evaluates rows individually rather than selecting blocks.

Option C is incorrect because SYSTEM or BLOCK sampling does not support fixed-row-count sampling through the proposed syntax.

Option D uses the correct block-based sampling method but supplies SEED (1), requesting reproducible sampling behavior. This contradicts the requirement that the sample not be repeatable.

An important distinction is that SYSTEM (20) does not guarantee exactly 20% of the table's rows. Because Snowflake selects entire blocks probabilistically, the actual number of sampled rows can vary depending on block composition and selection.

Official Snowflake references: SAMPLE / TABLESAMPLE: Sampling Methods; Probability; SEED / REPEATABLE.

Answer: B and C

Explanation

B. Administrators can monitor and understand usage across all accounts in the organization (Correct): Snowflake Organizations introduces the ORGANIZATION_USAGE schema and Snowsight Admin views. This allows users with the ORGADMIN role to centrally track credit consumption, storage usage, and billing across all accounts within the business entity.

C. Administrators can simplify data movement across all accounts within the organization (Correct): Organizations streamline cross-account and cross-region operations by enabling native database replication, failover/failback groups, and secure data sharing without requiring custom pipeline infrastructure.

Why the Other Options Are Incorrect

A is incorrect: Account editions (Standard, Enterprise, Business Critical) cannot be switched dynamically or on-demand by an administrator; changing an existing account’s edition typically requires contacting Snowflake Support.

D is incorrect: User administration and access control remain account-scoped. Snowflake Organizations does not provide centralized, cross-account user identity management or unified SCIM provisioning.

E is incorrect: Although administrators can self-provision accounts across major supported cloud providers (AWS, Azure, GCP), this capability is bounded by organization contract agreements, account limits, and region availability (certain isolated or government cloud regions cannot be freely created on-demand).

Official Snowflake references: Introduction to Organizations; Working with Account Editions; Organization Users; January and May 2026 general-availability announcements.

Snowflake provides the MINS_TO_BYPASS_MFA user property to support temporary authentication recovery when an enrolled user cannot complete multi-factor authentication.

This property specifies the number of minutes during which the affected user may authenticate without providing the normally required second authentication factor.

The requested duration is 24 hours, equivalent to 1,440 minutes:

ALTER USER user1

SET MINS_TO_BYPASS_MFA = 1440;

An authorized administrator can execute this command to establish a temporary MFA bypass for user1. The bypass expires after the configured duration, allowing normal MFA enforcement to resume without permanently removing the user's enrollment.

Option B is incorrect because DISABLE_MFA is a Boolean user property, not a numeric duration setting. Assigning 1440 does not express the intended temporary bypass. Options C and D reference properties that are not supported by Snowflake's ALTER USER syntax.

The administrator should use the minimum bypass duration necessary to restore access because authentication without a second factor increases exposure to compromised credentials.

Snowflake also supports ALTER USER ... ENROLL MFA to help locked-out users register a replacement authentication method. That approach may be preferable when the original device has been lost.

Official Snowflake references: ALTER USER; CREATE USER; Multi-factor Authentication: Recovering a User Who Is Locked Out.

Snowflake separates the privileges required to discover listings, request access, create imported databases, and query data received through secure sharing.

Option C is correct because ACCOUNTADMIN has the necessary administrative authority to request and receive data through a Data Exchange.

Option D is correct because Snowflake supports delegating these capabilities to custom roles that hold the global IMPORT SHARE and CREATE DATABASE privileges.

IMPORT SHARE permits a role to access incoming shares made available to the consumer account. CREATE DATABASE permits creation of the database object used to expose the shared data.

For example:

GRANT IMPORT SHARE ON ACCOUNT

TO ROLE DATA_CONSUMER;

GRANT CREATE DATABASE ON ACCOUNT

TO ROLE DATA_CONSUMER;

```

Option A is incorrect because SYSADMIN does not automatically receive IMPORT SHARE merely through its standard system-role membership.

Option B is similarly incorrect because SECURITYADMIN's administrative authority does not automatically confer the privileges required to consume shares.

Option E references inappropriate privilege terminology and does not describe a valid combination for receiving Data Exchange data.

Once the shared database is created, access can be delegated through the applicable imported-database privileges.

Snowflake explicitly documents ACCOUNTADMIN and roles holding IMPORT SHARE together with CREATE DATABASE as the supported consumer-access models.

Official Snowflake references: Access a Data Exchange; Grant Privileges to Other Roles; Enable Non-ACCOUNTADMIN Roles to Perform Data Sharing Tasks.

Snowflake configures OAuth 2.0 authentication through a security integration created using the CREATE SECURITY INTEGRATION statement. The integration defines the relevant OAuth authentication and authorization settings, depending on whether Snowflake OAuth or External OAuth is implemented.

Creating an OAuth security integration requires the global CREATE INTEGRATION privilege on the Snowflake account.

The ACCOUNTADMIN system role possesses this privilege by default. Therefore, ACCOUNTADMIN is the appropriate selection when evaluating the standard system-role configuration without additional privilege delegation.

Option A is incorrect because USAGE on an existing security integration does not provide authority to create a new integration.

Options B and C are incorrect because neither SYSADMIN nor SECURITYADMIN is inherently required to participate in creation. These roles also do not receive the required CREATE INTEGRATION privilege merely through their default administrative responsibilities.

The following statement illustrates the intended administrative context:

USE ROLE ACCOUNTADMIN;

CREATE SECURITY INTEGRATION oauth_integration

TYPE = OAUTH

ENABLED = TRUE

OAUTH_CLIENT = CUSTOM;

```

Additional integration parameters may be required depending on the OAuth implementation.

Verification qualification: The phrase "ACCOUNTADMIN only" is technically too restrictive. Snowflake explicitly permits the CREATE INTEGRATION privilege to be granted to other roles. Thus, D is the intended answer for default system-role privileges, but not an absolute authorization rule.

Official Snowflake references: CREATE SECURITY INTEGRATION (Snowflake OAuth); CREATE SECURITY INTEGRATION (External OAuth); Configure Snowflake OAuth for Partner Applications.

Snowflake Time Travel enables administrators to query historical table versions within the configured data-retention period. The AT and BEFORE clauses support recovery based on timestamps, relative time offsets, and statement identifiers.

Option B is correct because AT(TIMESTAMP = > ...) retrieves the table's historical state at the specified timestamp. Since the update occurred at 07:01, querying the table at 07:00 retrieves its preceding state.

Option E is correct because OFFSET specifies a relative number of seconds. At the hypothetical current time of 07:30, -60*30 refers to 07:00, before the update.

Option F is correct because BEFORE with a STATEMENT parameter retrieves the table state immediately before the specified statement completed.

Options A, C, and D use unsupported Time Travel syntax.

Snowflake requires historical data to remain within the table's retention period. Additionally, the STATEMENT parameter must identify a qualifying query executed within the previous 14 days.

Current-version qualification: The scenario is explicitly set on January 1, 2021. These statements are valid in that historical context; they cannot be used today to recover January 2021 data unless an applicable historical recovery mechanism still retains it. Timestamp interpretation also depends on Snowflake's timestamp type and session time zone.

Official Snowflake references: AT | BEFORE; Understanding & Using Time Travel.

Snowflake provides temporary MFA bypass functionality to recover user access when an enrolled authentication device becomes unavailable. The appropriate administrative action is to configure the MINS_TO_BYPASS_MFA property on the affected user.

For example:

ALTER USER user1

SET MINS_TO_BYPASS_MFA = 30;

This command permits the user to authenticate temporarily without completing MFA for the configured 30-minute period. Importantly, the operation does not revoke the user's existing MFA enrollment or permanently disable the authentication requirement.

Option B is unsuitable because DISABLE_MFA is a Boolean setting and does not automatically provide a 24-hour expiration period. It is not equivalent to a controlled, time-limited bypass.

Option C is incorrect because Snowflake supports MFA authentication through SnowSQL and other supported clients. Changing the connection client does not inherently eliminate MFA enforcement.

Option D proposes an unsupported URL-based authentication bypass.

From a security-administration perspective, the administrator should first validate the user's identity through an approved recovery procedure, establish the shortest practical bypass interval, and guide the user through registration of a replacement MFA method.

Current Snowflake documentation also describes ALTER USER ... ENROLL MFA as a recovery mechanism for users who must register a new authenticator.

Official Snowflake references: Multi-factor Authentication; Recovering a User Who Is Locked Out; ALTER USER.

Snowflake Dynamic Data Masking enforces column-level protection at query execution time. Masking policies determine whether the querying context receives the original value or a transformed representation.

Option B is correct because the same masking policy can be assigned to multiple compatible columns across different tables or views. This supports centralized governance and consistent protection of sensitive fields throughout an environment.

Option C is also correct. Snowflake supports assigning a masking policy to an external table's automatically generated VALUE column. The policy must accept and return the VARIANT data type, and the assignment must be performed using an ALTER TABLE statement rather than during initial external-table creation.

Option A is incorrect because a masking policy cannot normally be dropped while it remains associated with a column or tag. The administrator must first remove the applicable assignment.

Option D is incorrect because creating a policy does not automatically authorize viewing unmasked data. Visibility depends on the policy expression and execution context.

Option E is incorrect because a masking policy's argument and return signatures impose data-type compatibility requirements. One policy cannot be freely attached to columns of unrelated types.

This distinction is important when implementing reusable policies and managing external data.

Official Snowflake references: Understanding Column-level Security; DROP MASKING POLICY; Tag-based Masking Policies.

A Snowflake multi-cluster warehouse is appropriate when workloads generate consistent query concurrency with occasional increases in demand.

The scenario indicates that two Small clusters are required for routine activity, with temporary increases to three or four clusters during busier periods.

Option B correctly establishes a baseline of two clusters and allows Snowflake to automatically increase capacity when concurrent query demand grows.

When query activity declines, additional clusters can be removed according to the warehouse's scaling policy. AUTO_SUSPEND and AUTO_RESUME provide additional controls to prevent unnecessary compute usage during periods of inactivity.

The ANALYST role requires only USAGE to execute queries, preserving least-privilege access without permitting warehouse configuration changes.

Option A is unsuitable because manual cluster management introduces unnecessary administrative responsibility. Moreover, MANAGE is not the applicable object-level privilege for the described warehouse operations.

Option C confuses warehouse size with independent cluster capacity. A single X-Large warehouse does not provide the same concurrency-scaling behavior as four independently managed Small clusters.

Option D introduces operational complexity and requires users to select warehouses manually.

Terminology warning: The original choices use MIN_CLUSTERS. The actual Snowflake configuration parameter is MIN_CLUSTER_COUNT. Option B is conceptually correct, but its wording should be corrected before publication.

Official Snowflake references: Multi-cluster Warehouses; CREATE WAREHOUSE; Access Control Privileges.

Snowflake Time Travel and zero-copy cloning provide an efficient mechanism for recovering from accidental data modifications without requiring a conventional full-table backup.

Option B uses Time Travel cloning with the BEFORE clause to create a table representing the historical state immediately preceding a specified statement. The STATEMENT parameter identifies the relevant query ID, and Snowflake reconstructs the qualifying historical version from retained data.

Option D uses ALTER TABLE ... SWAP WITH to exchange the original and restored table objects atomically. This enables the recovered data to become available under the production table name while retaining the displaced table as another object.

Option A cancels an executing query but does not reverse changes that have already committed.

Option C queries a previous statement's result set rather than reconstructing the table's historical state. RESULT_SCAN is not a substitute for Time Travel recovery.

Option E involves Snowflake Fail-safe, which is a last-resort disaster-recovery capability, not the normal administrative mechanism for reversing an erroneous statement.

Technical qualification: The source's problematic_query_id is a placeholder, and the SQL would require an actual qualifying statement ID. Successful recovery also requires the historical version to remain within the configured Time Travel retention period. The administrator must review dependencies, object privileges, and downstream effects before swapping production tables.

Official Snowflake references: Understanding & Using Time Travel; AT | BEFORE; ALTER TABLE; Cloning Considerations.

Snowflake provides account-level security parameters that restrict how external stages are created and used, reducing opportunities for unauthorized data extraction through external cloud storage.

Option D correctly enables all three protective controls.

REQUIRE_STORAGE_INTEGRATION_FOR_STAGE_CREATION=TRUE requires newly created named external stages accessing private cloud storage to reference a storage integration rather than explicitly supplied cloud credentials.

REQUIRE_STORAGE_INTEGRATION_FOR_STAGE_OPERATION=TRUE requires loading and unloading operations involving private cloud storage to use an authorized external stage backed by a storage integration.

PREVENT_UNLOAD_TO_INLINE_URL=TRUE prevents ad hoc unloading directly to cloud storage URLs through COPY INTO statements.

Option A leaves inline URL unloading permitted, creating an unnecessary exfiltration pathway.

Option B disables both storage-integration requirements, allowing other credential-based configurations.

Option C disables all three controls and therefore provides the weakest protection.

Technical qualification: These parameters constrain specified data-loading and unloading mechanisms; they do not eliminate every possible data-exfiltration pathway. Administrators should also consider stage privileges, storage integration permissions, network controls, and restrictions on unloading to internal stages.

Official Snowflake references: Parameters: REQUIRE_STORAGE_INTEGRATION_FOR_STAGE_CREATION; REQUIRE_STORAGE_INTEGRATION_FOR_STAGE_OPERATION; PREVENT_UNLOAD_TO_INLINE_URL.

Snowflake implements a hierarchical system of predefined administrative roles to separate identity administration, security governance, and object management responsibilities.

USERADMIN is primarily responsible for creating and administering users and roles. It has the CREATE USER and CREATE ROLE global privileges by default.

SECURITYADMIN occupies a higher position in the standard role hierarchy and inherits USERADMIN's capabilities.

In addition, SECURITYADMIN possesses the global MANAGE GRANTS privilege, which authorizes administrators to grant and revoke privileges across the Snowflake account.

Option B is therefore correct because managing account-wide privilege grants is a capability assigned to SECURITYADMIN but not automatically granted to USERADMIN.

Option A is incorrect because USERADMIN can manage credentials for users it owns or otherwise has authorization to modify.

Options C and D are incorrect because user and role creation are foundational USERADMIN responsibilities.

An important security distinction is that MANAGE GRANTS enables privilege administration but does not automatically grant direct access to all protected data or confer object-creation privileges.

For example, SECURITYADMIN may grant SELECT on a table to a role without necessarily having the SELECT privilege itself.

Organizations should delegate these responsibilities through appropriate custom roles rather than granting SECURITYADMIN broadly to users performing routine identity-management tasks.

Official Snowflake references: Overview of Access Control: System-defined Roles; Access Control Best Practices.