Free Snowflake SnowPro Advanced: Administrator (ADA-C02) Sample Questions

Correct answers: B, D — Assign the ACCOUNTADMIN role to at least two, but a very limited number of, individual users.; Ensure all users with the ACCOUNTADMIN role have multi-factor authentication (MFA) enabled.

• A. Incorrect. Granting the ACCOUNTADMIN role to a service account violates the principle of least privilege. This gives the CI/CD pipeline excessive and unnecessary permissions, creating a significant security risk if the service account credentials are compromised. Automated processes should use specific, least-privileged roles tailored to their required tasks.
• B. Correct. This is a key best practice. Assigning ACCOUNTADMIN to a very small, limited number of trusted individuals minimizes the attack surface. Having at least two users with this role provides necessary redundancy, preventing a single point of failure if one user is unavailable or their account is locked. This balances security with operational continuity.
• C. Incorrect. Using the ACCOUNTADMIN role for daily tasks is a poor practice that violates the principle of least privilege. The ACCOUNTADMIN role should be used sparingly for account-level configuration only. Daily administrative tasks, such as creating warehouses, databases, and users, should be delegated to lower-level roles like SYSADMIN and USERADMIN to limit the potential impact of errors or a compromised account.
• D. Correct. This is a critical security control. Enforcing Multi-Factor Authentication (MFA) for all users assigned the ACCOUNTADMIN role adds a crucial layer of security. It significantly reduces the risk of unauthorized access, even if a user's password is stolen or compromised, thereby protecting the most powerful role in the account.
• E. Incorrect. This action would disrupt Snowflake's recommended role hierarchy and violate the principle of separation of duties. Granting ACCOUNTADMIN to SYSADMIN would make the SYSADMIN role equivalent to ACCOUNTADMIN, defeating the purpose of having distinct roles for account-level and system-level administration. The best practice is to keep these roles separate and grant privileges in a top-down manner.

Correct answer: C — Create a share in each account from the `SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY` view and share it with a central auditor account.

• A. Incorrect. This approach is inefficient and violates the principle of least privilege. It requires high administrative overhead to create and manage users in every account, and the SECURITYADMIN role has far more permissions than necessary for this read-only task, such as managing users and grants.
• B. Incorrect. While the ORGADMIN role provides access to the `ORGANIZATION_USAGE.LOGIN_HISTORY` view which contains the required data, it is a highly privileged administrative role. Its capabilities, such as creating new accounts, far exceed the read-only needs of an auditor, thus violating the principle of minimum necessary privileges.
• C. Correct. This solution perfectly adheres to the principle of minimum necessary privileges. By creating secure views on the `LOGIN_HISTORY` data in each account and sharing them with a central auditor account, the auditor gets read-only access to exactly the data needed and nothing more. Although it requires more initial setup, it is the most secure and compliant method for this requirement.
• D. Incorrect. The ACCOUNTADMIN role is scoped to a single account and cannot access data from other accounts in the organization, failing to meet the core requirement. Furthermore, it is the most powerful role in an account, making it a significant violation of the principle of minimum necessary privileges.

Correct answers: A, C — Create a SAML2 security integration in Snowflake to handle SSO and a separate SCIM security integration to handle user provisioning.; In Okta, configure the Snowflake application to send the `loginName` and `email` attributes in the SAML assertion.

• A. This is a correct action. Snowflake requires distinct configurations for federated authentication (SSO) and automated user provisioning. A SAML2 security integration must be created in Snowflake to handle SSO with an external IdP like Okta. Separately, a SCIM security integration must be created to provide the API endpoint and generate an access token that Okta will use for automated user and group lifecycle management (provisioning, updates, and de-provisioning).
• B. This is incorrect. A single OAuth security integration does not handle both SAML-based SSO and SCIM provisioning. OAuth integrations are primarily used for client applications and services to obtain access tokens for making API calls to Snowflake. SAML2 is the standard protocol for federated SSO, and SCIM is the standard for user provisioning, each requiring its own specific integration type in Snowflake.
• C. This is a correct action. For SSO to function, the Identity Provider (Okta) must send a SAML assertion containing attributes that Snowflake can use to uniquely identify the user. It is an essential configuration step within Okta to ensure that the SAML assertion includes an identifier, such as the user's login name. Snowflake uses this attribute (typically mapped from the SAML `NameID` field) to match the incoming authentication request to a specific user in the Snowflake account.
• D. This is incorrect. While creating a network policy to restrict traffic is a security best practice, it is not an essential functional requirement for setting up SSO or SCIM. Furthermore, cloud-based IdPs like Okta use a wide and dynamic range of IP addresses, making an IP-based allow-list brittle and difficult to maintain.
• E. This is incorrect. The primary purpose of using SCIM is to automate user provisioning and de-provisioning, which eliminates the need for manual user creation. This option describes a manual, error-prone process that is made unnecessary by a proper SCIM implementation.

Correct answer: B — Snowflake OAuth, allowing the application to request access tokens from users to act on their behalf.

• A. Incorrect. Key-pair authentication requires the application server to store a private key. A private key is a form of credential, and storing it would violate the company's security policy. This method is more suitable for service-to-service communication, not for delegated user authorization.
• B. Correct. Snowflake OAuth is designed for this exact use case. It allows an application to obtain authorization from a user to act on their behalf without ever handling or storing the user's primary credentials. The user authenticates directly with Snowflake (or an IdP), grants consent, and the application receives a short-lived, revocable access token to make API calls.
• C. Incorrect. Storing username and password credentials, even within an encrypted vault, still constitutes storing user credentials. This directly violates the stated security policy and is a less secure pattern than using a token-based protocol like OAuth.
• D. Incorrect. Using a generic service account means all queries would be executed under the identity of that single service account, not on behalf of the individual users. This would result in a loss of user-level authorization and auditability. Furthermore, the application would still need to store credentials (e.g., a key pair or password) for this service account, violating the core security requirement.

Correct answers: C, E — OPERATE; USAGE

• A. Incorrect. The SELECT privilege is a Data Query Language (DQL) privilege used to query data from database objects like tables and views. It is not applicable to compute resources like virtual warehouses.
• B. Incorrect. Although MODIFY is a valid privilege on a warehouse, allowing a role to alter warehouse properties (e.g., size, auto-suspend time) and drop it, it is not one of the intended correct answers for this question, which focuses on the more common operational privileges. In a real-world scenario with these options, MODIFY would be a correct choice.
• C. Correct. The OPERATE privilege allows a role to manage the state of a virtual warehouse. This includes starting, stopping, suspending, and resuming the warehouse, as well as canceling queries running on it. This privilege is typically granted to administrative roles.
• D. Incorrect. The INSERT privilege is a Data Manipulation Language (DML) privilege used to add new rows to a table. It has no association with virtual warehouse objects.
• E. Correct. The USAGE privilege is the most fundamental privilege on a virtual warehouse. It allows a role to use the warehouse's compute resources to execute queries, load data, or perform other operations. Without USAGE, a user or role cannot run any tasks on that warehouse.

Correct answers: B, D — Create a database role `FINANCE_DB.ANALYST_R` and grant it `USAGE` on the database and schema, and `SELECT` on all tables in the `ANALYTICS` schema.; Create a database role `FINANCE_DB.ENGINEER_RW` and grant it `USAGE` on the database and schema, and the `CREATE TABLE` privilege on the `ETL` schema.

• A. Incorrect. This approach is incomplete because it fails to grant the necessary `USAGE` privilege on the parent database (`FINANCE_DB`) and schema (`ANALYTICS`). Without `USAGE`, the role cannot 'see' or access the objects within those containers, making the `SELECT` grant ineffective. Furthermore, for permissions scoped to a single database, using a database role is the recommended best practice to create a clear, modular hierarchy.
• B. Correct. This option follows best practices by creating a database role, which is designed to encapsulate permissions for objects within a specific database. It correctly grants the necessary `USAGE` privilege on both the database and the schema, which is a prerequisite for accessing any objects within them. Finally, it grants the specific `SELECT` privilege on all tables, adhering to the principle of least privilege for the analysts' read-only requirement.
• C. Incorrect. Granting the `OWNERSHIP` privilege is excessive and violates the principle of least privilege. `OWNERSHIP` confers full control over an object, including the ability to alter, drop, and manage grants, which is far more than the required ability to create and modify tables. A more granular privilege like `CREATE TABLE` is appropriate.
• D. Correct. This is a sound approach that uses a database role to manage permissions within the `FINANCE_DB`. It correctly grants `USAGE` on the database and schema, allowing access to the `ETL` schema. Granting the `CREATE TABLE` privilege on the schema directly addresses the core requirement for Data Engineers. This follows the principle of least privilege by providing only the necessary permissions to create objects, rather than overly broad privileges like `OWNERSHIP`.
• E. Incorrect. This action contradicts the stated security policy that a clear hierarchy should exist. The recommended practice is to grant specific, functional database roles to higher-level, account-wide business roles (e.g., `DATA_ENGINEER_ROLE`, `ANALYST_ROLE`). These account-level roles are then assigned to users. Granting database roles directly to users creates a flat access control model that is harder to manage and scale.

Correct answer: B — The security integration in Snowflake used for SCIM was not created with a provisioner role that has CREATE ROLE privileges.

• A. This is incorrect. A key feature of Snowflake's SCIM integration is its ability to provision groups from an identity provider (like Okta) as roles within Snowflake. The described issue is related to configuration or permissions, not a fundamental limitation of the integration.
• B. This is the correct answer. The Snowflake security integration for SCIM requires a `SCIM_PROVISIONER` role to be specified. This role executes the provisioning commands (e.g., CREATE USER, CREATE ROLE) on behalf of Okta. If the specified provisioner role lacks the `CREATE ROLE` privilege in Snowflake, it cannot create new roles when new groups are pushed from Okta, which perfectly matches the described symptoms.
• C. This is incorrect. While it is true that 'Push Groups' functionality must be enabled and configured in Okta, the scenario states the 'SCIM integration runs,' which implies an attempt to provision was made. A failure to create the specific role points more directly to a permissions issue within Snowflake, rather than a feature not being enabled in Okta.
• D. This is incorrect. An expired access token would cause a general authentication failure for the entire SCIM integration. All provisioning tasks, including user and group updates, would fail, likely with an explicit authorization error. The problem describes a specific failure (role creation) rather than a complete breakdown of the integration.

Correct answers: A, B, E — First, create and test the network rules and policy in a separate development or test account.; Before activating the policy at the account level, apply it to a single, non-critical test user to verify its behavior.; Create a separate network policy with a single rule allowing the administrator's current IP address and apply it to the administrator's user before activating the account-level policy.

• A. This is a fundamental best practice for any significant change. Creating and validating network policies in a separate development or test account allows the administrator to iterate on the rules, catch configuration mistakes, and verify the intended behavior without any risk of impacting the production environment or its users.
• B. This is a prudent 'canary testing' approach within the production environment. By applying the policy to a single, non-critical test user first, an administrator can observe its real-world effects and confirm it works as expected. This incremental rollout significantly reduces the blast radius if the policy has unintended consequences.
• C. This is not a recommended best practice because it is error-prone and brittle. Administrators may have dynamic IP addresses, work from multiple locations, or use VPNs, making a static list of IPs difficult to maintain and unreliable. A much more robust safety mechanism is to use a separate, user-level policy for administrators, as described in another option.
• D. This is the opposite of a best practice and is the primary action to avoid. Immediately enforcing a new, restrictive policy at the account level without prior validation is extremely risky and is the most likely way to inadvertently lock out all users, including the administrators trying to implement the change.
• E. This is a highly recommended safety measure. Snowflake's network policy hierarchy dictates that a policy applied at the user level takes precedence over an account-level policy. By creating a separate, more permissive policy for their own user (e.g., allowing their current IP), the administrator creates a 'backdoor' to ensure they retain access to the account even if the new account-level policy is misconfigured and locks out other users.

Correct answers: C, E — The PUBLIC role is implicitly granted to every user and every role.; The ACCOUNTADMIN role encompasses the privileges of both SYSADMIN and SECURITYADMIN.

• A. This statement is incorrect. The inheritance hierarchy is the reverse. The `SECURITYADMIN` role, which is responsible for managing roles and grants, inherits the privileges of the `USERADMIN` role. This hierarchy ensures that a security administrator can also perform user management tasks.
• B. This statement is incorrect. In the default Snowflake role hierarchy, `SYSADMIN` and `SECURITYADMIN` are sibling roles. Both roles are granted to the top-level `ACCOUNTADMIN` role. There is no default inheritance or grant relationship directly between `SYSADMIN` and `SECURITYADMIN`.
• C. This statement is correct. The `PUBLIC` role is a special, system-defined role that is implicitly granted to every user and every other role within a Snowflake account. Any privileges granted to the `PUBLIC` role are automatically available to all users.
• D. This statement is incorrect. This contradicts the principle of separation of duties in Snowflake's default roles. The `SYSADMIN` role is intended for creating and managing account-level objects like warehouses and databases. The responsibility for creating and managing users and roles belongs to the `USERADMIN` and `SECURITYADMIN` roles.
• E. This statement is correct. `ACCOUNTADMIN` is the highest-level role in the system. It stands at the top of the role hierarchy and, by default, encompasses the privileges of both `SYSADMIN` and `SECURITYADMIN`. This is because both of these roles are granted to `ACCOUNTADMIN`, allowing it to perform any system administration or security administration task.

Correct answer: C — Revoke the `SNOWFLAKE.CORTEX_USER` database role and the `USE AI FUNCTIONS` privilege from the `PUBLIC` role, then grant them only to the specific roles that require Cortex access.

• A. Incorrect. Snowflake's access control model does not use a `DENY` statement for managing privileges. Access is managed exclusively through `GRANT` and `REVOKE` commands.
• B. Incorrect. This command is ineffective because the `JUNIOR_ANALYST` role does not have the `SNOWFLAKE.CORTEX_USER` database role granted to it directly. It inherits this privilege from the `PUBLIC` role, so a direct `REVOKE` from the `JUNIOR_ANALYST` role will have no effect.
• C. Correct. According to Snowflake documentation, this is the recommended approach. By default, Cortex access is granted to the `PUBLIC` role. To implement the principle of least privilege, an `ACCOUNTADMIN` must first revoke these default grants from `PUBLIC` and then explicitly grant the `SNOWFLAKE.CORTEX_USER` database role and the `USE AI FUNCTIONS` privilege to only the necessary roles.
• D. Incorrect. While this would prevent the role from using Cortex functions, it is an overly broad and destructive action. Revoking `USAGE` on the entire `SNOWFLAKE` database would also prevent the `JUNIOR_ANALYST` role from accessing many other critical system-defined views and functions required for basic operations.

Correct answer: C — CREATE OR REPLACE MASKING POLICY account_mask AS (val STRING) RETURNS STRING -> CASE WHEN IS_ROLE_IN_SESSION('COMPLIANCE_AUDITOR') THEN val ELSE CONCAT('********', RIGHT(val, 4)) END;

• A. Incorrect. This statement uses `INVOKER_ROLE()`, which is not a valid Snowflake function for checking the current session's role within a masking policy. Context functions like `CURRENT_ROLE()` or `IS_ROLE_IN_SESSION()` should be used instead.
• B. Incorrect. This option uses `CURRENT_ROLE()`, which only evaluates the single, primary active role for the session. This is not the recommended best practice, as a user might have the `COMPLIANCE_AUDITOR` role as a secondary role and would not see the unmasked data. `IS_ROLE_IN_SESSION()` is the more robust function for this use case.
• C. Correct. This statement correctly follows Snowflake best practices. It uses `IS_ROLE_IN_SESSION('COMPLIANCE_AUDITOR')`, which checks if the specified role is part of the current session's active role hierarchy (primary or secondary). This is the most robust way to implement role-based access control in a masking policy. The use of `CREATE OR REPLACE` allows for easier policy management, and the masking logic itself is syntactically correct.
• D. Incorrect. This approach is invalid for two main reasons. First, basing security on individual users (`CURRENT_USER()`) rather than roles is not a scalable data governance practice. Second, and more critically, Snowflake masking policies do not permit the use of subqueries in their definition for security and performance reasons.

Correct answers: A, C — The connection will fail because the account name `old_company` is no longer a valid alias.; The most robust way to update the connection string is to use the locator: `qr44556.snowflakecomputing.com`.

• A. This statement is correct. When a Snowflake account is renamed, the original account name is no longer a valid alias or identifier for the account. Consequently, any connection attempts using the old hostname (`old_company.snowflakecomputing.com`) will fail as DNS will no longer resolve it to the correct account.
• B. This statement is incorrect. The legacy connection string explicitly references the account name (`old_company`), not the account locator. While the account locator is indeed immutable and does not change, this fact does not make the old, name-based URL continue to function after the rename.
• C. This statement is correct. Using the account locator is the most robust connection method because the locator is a unique and immutable identifier for the account. It will remain valid even if the account is renamed again. The example URL `qr44556.snowflakecomputing.com` is a valid format because the account is in AWS `us-west-2`, which is Snowflake's original default region, making the region identifier optional in the URL.
• D. This statement is incorrect because of the word 'must'. While `new_company.us-west-2.snowflakecomputing.com` is a valid URL to connect to the renamed account, it is not the only valid format. Other valid options include using the new account name without the region (`new_company.snowflakecomputing.com`) since it's in `us-west-2`, or using the account locator.
• E. This statement is incorrect. Snowflake does not have a process for 'certifying' applications in this manner, and there is certainly no requirement to 're-certify' an application after an account name change. The only necessary action is to update the application's connection configuration.

Correct answers: A, C — ORGADMIN: `SHOW ACCOUNTS IN ORGANIZATION;`; ACCOUNTADMIN: `SELECT SYSTEM$ENABLE_FEATURE('new_feature');`

• A. Correct. The `SHOW ACCOUNTS IN ORGANIZATION` command is the standard and direct way for a user with the ORGADMIN role to list all accounts within their organization. The output includes crucial information such as the account name, edition, region, and creation date, directly satisfying the central IT team's requirement.
• B. Incorrect. The `SNOWFLAKE.ACCOUNT_USAGE` schema is specific to an individual account and is not accessible at the organization level by the ORGADMIN role. The ORGADMIN role would use views from the `ORGANIZATION_USAGE` shared database to programmatically retrieve organization-wide usage metrics, not the `ACCOUNT_USAGE` schema.
• C. Correct. The `SYSTEM$ENABLE_FEATURE()` is a system function used to enable specific, self-service preview features within an account. This action requires account-level administrative privileges, making it a task for the ACCOUNTADMIN role. The function is called with the specific feature name as an argument.
• D. Incorrect. The `ALTER SYSTEM` command is not a valid SQL command in Snowflake. Account-level settings and parameters are modified using the `ALTER ACCOUNT` command, not `ALTER SYSTEM`.
• E. Incorrect. `SHOW ORGANIZATION_FEATURES` is not a valid Snowflake command. While commands like `SHOW FEATURES` exist to display features, they do not list accounts and their editions. This command does not fulfill the requirement of the ORGADMIN.

Correct answers: D, E — WHERE SESSION_ID IN (SELECT SESSION_ID FROM SESSIONS WHERE CLIENT_APPLICATION ILIKE '%Python%'); WHERE IS_CLIENT_GENERATED_STATEMENT = TRUE

• A. Incorrect. This filter is too specific and unreliable. It only identifies queries run by a single, predefined user account. The auditor needs to find all application-generated queries, which could be executed by various service accounts or even individual user accounts through a connector. This approach would miss a significant portion of the relevant activity.
• B. Incorrect. The execution status of a query (e.g., 'SUCCESS') is irrelevant to its origin. Both human-executed queries and client-generated queries can succeed or fail. This filter does not help differentiate between the two sources as required by the auditor.
• C. Incorrect. Filtering the query text for 'select %' is not a reliable method for identifying the query's origin. Both humans and client applications execute SELECT statements. Furthermore, this would incorrectly exclude other important statements generated by applications, such as INSERT, MERGE, PUT, or metadata queries.
• D. Correct. This is an effective method. The `SNOWFLAKE.ACCOUNT_USAGE.SESSIONS` view (or `INFORMATION_SCHEMA.SESSIONS` table function) contains a `CLIENT_APPLICATION` column that logs the name of the client or driver used to establish the session (e.g., 'PythonConnector', 'Tableau', 'JDBC'). By joining `QUERY_HISTORY` with `SESSIONS` on the `SESSION_ID` and filtering on `CLIENT_APPLICATION`, the auditor can precisely identify queries originating from specific applications.
• E. Correct. This is the most direct and effective filter for this purpose. The `IS_CLIENT_GENERATED_STATEMENT` column in the `QUERY_HISTORY` view is a boolean flag specifically designed to identify statements that were generated by a client driver (like a JDBC or Python driver) rather than being manually typed by a user. This includes many metadata queries, `PUT`/`GET` commands, and other automated statements, making it an ideal filter for the auditor's investigation.

Correct answer: B — The product manager's active role does not have `SELECT` privileges on the `ANALYTICS.CAMPAIGN_DATA` table.

• A. Incorrect. The choice of virtual warehouse provides compute resources for a query but does not govern permissions to access data objects like tables. A permissions error on a table indicates a data access issue, not a problem with the selected compute warehouse. If the user lacked privileges on the warehouse itself, a different error would appear.
• B. Correct. In Snowflake's role-based access control (RBAC) model, having `USAGE` privileges on a database and schema allows a user's role to see the objects within them, but it does not grant permission to query the data from those objects. To retrieve data from a table, the role must have explicit `SELECT` privileges on that table. The empty chart with a permissions error is a classic symptom of the viewer's active role lacking the necessary `SELECT` grant on the underlying table.
• C. Incorrect. This option is invalid because the question explicitly states the chart is based on the `ANALYTICS.CAMPAIGN_DATA` table, not a view. Therefore, the distinction between secure and non-secure views is irrelevant to the scenario described.
• D. Incorrect. If the chart or its parent dashboard had not been shared correctly, the product manager would likely be unable to open or see the chart object at all. The fact that they can see the chart container, but it is empty with a permissions error, points to a problem with querying the underlying data, not an issue with the sharing of the Snowsight object itself.

Correct answer: C — To control how long a SQL statement remains in a queued state before it is cancelled.

• A. Incorrect. This describes the `STATEMENT_TIMEOUT_IN_SECONDS` parameter, which controls the maximum execution time for a query that is actively running, not the time it spends waiting in a queue.
• B. Incorrect. This functionality is controlled by the `AUTO_SUSPEND` parameter, which determines the period of inactivity before a warehouse is automatically suspended.
• C. Correct. The `STATEMENT_QUEUED_TIMEOUT_IN_SECONDS` parameter specifies the maximum time a SQL statement can remain in a queued state before it is cancelled. A statement is queued if the warehouse is busy with other queries or is in a suspended state, and this parameter prevents statements from waiting indefinitely.
• D. Incorrect. There is no specific parameter to set a time limit for a warehouse to resume. While `AUTO_RESUME` enables a warehouse to start automatically, the time it takes is managed by Snowflake and is not configured via a timeout parameter.

Correct answer: D — Pre-process the source data by combining the small files into larger files (100-250 MB).

• A. Incorrect. While increasing the warehouse size provides more compute resources and threads for parallel processing, it is not the most effective first step. The primary bottleneck in this scenario is the high per-file overhead associated with processing thousands of small files. A larger warehouse would still struggle with this overhead, making file consolidation a more impactful initial action.
• B. Incorrect. A multi-cluster warehouse is designed to handle query concurrency by scaling out to accommodate more simultaneous queries. It does not improve the performance of a single, long-running query like this `COPY` command. The issue is the inefficiency of the load itself, not a lack of resources to handle concurrent loads.
• C. Incorrect. Increasing the `STATEMENT_TIMEOUT_IN_SECONDS` parameter only allows the statement to run for a longer duration before being automatically cancelled. It does not improve the performance or throughput of the data loading job in any way; it merely prevents a timeout error.
• D. Correct. This is a Snowflake best practice for data loading. Combining many small files into fewer, larger files (ideally 100-250 MB each) drastically reduces the per-file processing overhead. This allows Snowflake to parallelize the data ingestion much more efficiently, leading to a significant improvement in load performance. This action directly addresses the root cause of the problem described.

Correct answer: B — To list the S3 bucket locations that Snowflake is permitted to access for table data when using this catalog integration, acting as a security boundary.

• A. Incorrect. The `CATALOG_ALLOWED_LOCATIONS` parameter is used to specify storage paths (e.g., S3 URIs), not geographic AWS regions. While the S3 buckets reside in a region, this parameter's function is to control access at the bucket or prefix level.
• B. Correct. This parameter's primary purpose is security. It defines an explicit list of S3 storage locations (buckets and optional paths) that Snowflake can access for external table data when using the catalog integration. This acts as an allowlist, preventing users from creating external tables that point to unauthorized S3 locations.
• C. Incorrect. This parameter is unrelated to connection strings like JDBC. The connection details to the external catalog service (like AWS Glue's API endpoint) are managed implicitly by Snowflake or through other parameters within the catalog integration, not `CATALOG_ALLOWED_LOCATIONS`.
• D. Incorrect. The mapping between Snowflake schemas/databases and external catalog databases is not defined by this parameter. This mapping occurs when you create an external database or schema in Snowflake that references the catalog integration. `CATALOG_ALLOWED_LOCATIONS` is solely for restricting storage access paths.

Correct answers: B, D — They prevent users from seeing the view's underlying definition and base tables via `SHOW VIEWS` or `GET_DDL`.; They help prevent data leakage through the query optimizer's details, which could expose data from rows that the user should not have access to.

• A. Incorrect. Secure Views are not designed for performance optimization. In fact, to prevent data leakage, Snowflake limits certain query optimizer rewrites and optimizations when querying a secure view. This can sometimes result in slower performance compared to a standard view with the same definition.
• B. Correct. This is a primary security feature of a Secure View. For users who are not the owner of the view, the underlying SQL definition and details about the base tables are hidden. Commands like `SHOW VIEWS` will show a NULL value in the `text` column, and `GET_DDL` will produce an error, thus protecting the intellectual property of the view's logic.
• C. Incorrect. Both standard and secure views are metadata objects that can be cloned. Cloning a view does not clone the underlying base tables. Therefore, this is not a differentiating benefit of using a secure view over a standard view.
• D. Correct. This is another key benefit of Secure Views. With standard views, it's possible for a user to infer information about the underlying data they don't have access to by analyzing the query plan or through certain error messages (e.g., a 'division by zero' error). Secure Views prevent these side-channel attacks by modifying how the query is optimized and executed.
• E. Incorrect. Views, whether standard or secure, cannot be defined on temporary tables. Temporary tables are session-specific and are dropped at the end of the session, making them unsuitable as a stable base for a view intended for persistent access control and sharing.

Correct answers: B, C, D — The clone is a metadata-only operation initially, and it shares the underlying micro-partitions of the source object.; Any child objects within the database, such as schemas, tables, and views, are also cloned.; Modifying data in the cloned database does not affect the source database.

• A. This statement is incorrect. The 'zero-copy' aspect of cloning means that it does not create an immediate physical copy of the data. Instead, it's a metadata-only operation that does not incur any additional storage costs at the time of creation.
• B. This statement is correct. A clone is created by copying the metadata of the source object. The new cloned object then points to the same underlying micro-partitions as the source object, sharing the storage. This allows clones to be created nearly instantaneously with no initial storage cost.
• C. This statement is correct. When a container object like a database or schema is cloned, all the objects contained within it are also cloned recursively. This ensures the clone is a complete, point-in-time replica of the source database, including all its schemas, tables, views, etc.
• D. This statement is correct. Snowflake uses a copy-on-write mechanism. When data in either the source or the cloned object is modified, Snowflake creates new micro-partitions to store the changes for the modified object, leaving the other object's data unaffected. This ensures complete isolation between the source and the clone.
• E. This statement is incorrect. Warehouses are compute resources and are completely decoupled from storage objects like databases. Cloning a database is a data management operation and has no effect on compute resources. A separate, pre-existing warehouse must be used to query the cloned database.

Correct answer: A — The expression in the `SESSION_PARAMETERS` clause is evaluated only once at task creation time, not at each run.

• A. This is the correct explanation. The expressions within the `SESSION_PARAMETERS` clause are evaluated only once, at the time the `CREATE TASK` statement is executed. The resulting value is then stored as part of the task's static definition and used for every subsequent run. To achieve dynamic date calculation for each run, the function call (e.g., `DATEADD('day', -1, CURRENT_DATE())`) should be placed directly within the task's SQL body instead of in the session parameters.
• B. This is incorrect. The `CURRENT_DATE()` function is allowed in expressions within the `SESSION_PARAMETERS` clause. The problem is not the function's validity but the timing of its evaluation, which happens only once at task creation.
• C. This is incorrect. Using a `$` prefix to reference a session variable (`$DATE_PARAM`) is valid syntax in Snowflake SQL. The alternative `GETVARIABLE('DATE_PARAM')` function would also be valid, but the syntax used is not the source of the issue.
• D. This is incorrect. A permissions issue would likely cause the `CREATE TASK` statement to fail or the session variable to be NULL during execution, which would lead to an error or no rows being processed. It would not result in the variable being set to a static date based on the creation time.

Correct answer: C — It is an internal package used by Snowflake handlers and formatters to structure log records before they are sent to the event table.

• A. Incorrect. The `snowflake.events` package is not a client library for querying event tables. Querying is performed using standard SQL through the Snowflake connector for Python, Snowpark, or other client libraries. The purpose of `snowflake.events` is related to formatting and emitting event records, not retrieving them.
• B. Incorrect. While it is used in the context of logging, it's not a package that a user directly installs to enable logging. Instead, it is a dependency used by Snowflake's logging handlers. Enabling event logging involves configuring these handlers and formatters in your code, which in turn use this internal package.
• C. Correct. The `snowflake.events` package serves as an internal helper module used by Snowflake's provided logging handlers and formatters. Its primary role is to structure and format Python log records into the specific schema and payload expected by Snowflake event tables before they are ingested.
• D. Incorrect. This package does not provide User-Defined Functions (UDFs) for table management. The creation and management of event tables are performed using standard Data Definition Language (DDL) SQL statements, not through functions within this specific Python package.

Correct answers: A, C, E — The complexity of the query, requiring the optimizer to evaluate a vast number of join orders and plans.; The query involves complex views that must be expanded during the parsing phase.; The query uses User-Defined Functions (UDFs) that are slow to initialize.

• A. This is a primary cause. Joining a large number of tables (like 25) creates a combinatorial explosion in the number of possible join orders and execution plans. The Snowflake optimizer is cost-based and must evaluate a vast search space to find the most efficient plan, which is computationally expensive and can significantly increase compilation time.
• B. Incorrect. The warehouse cache (local disk cache) stores data that has been recently accessed from remote storage. Its purpose is to speed up data retrieval during the 'Execution' phase. The 'Compilation' phase involves parsing and optimizing the query using metadata, not reading the actual table data, so the state of the cache has no impact on compilation time.
• C. This is a very likely cause. If the query references complex or deeply nested views, Snowflake must expand these views into their underlying SQL definitions during the compilation phase. This can transform a seemingly simple query into a much larger and more complex one, significantly increasing the workload for the optimizer and thus extending the compilation time.
• D. Incorrect. The compilation phase primarily works with table metadata to create an execution plan. Snowflake's Multi-Version Concurrency Control (MVCC) architecture minimizes locking contention for read queries. Any potential waiting for locks would occur during the 'Execution' phase when data is being accessed or modified, not during query planning.
• E. This is a possible cause. User-Defined Functions (UDFs), especially complex SQL UDFs or those written in external languages like Java or Python, can add overhead to compilation. The optimizer must perform validation, type checking, and potentially initialize the secure sandbox environment for the UDF, all of which contribute to the overall compilation time before execution begins.

Correct answers: C, E — SELECT statements can be blocked by DML operations that are waiting to acquire table-level metadata locks.; Because of MVCC, readers (SELECT statements) do not block writers (DML statements), and writers do not block readers.

• A. Incorrect. Snowflake's use of Multi-Version Concurrency Control (MVCC) means that SELECT statements read from a consistent snapshot of the data as it existed at the start of the query. They do not acquire shared locks on tables that would block DML operations, which is a key feature that allows for high concurrency between readers and writers.
• B. Incorrect. Snowflake employs an optimistic concurrency control model, not pessimistic. This means it assumes conflicts are rare and proceeds with operations without taking locks beforehand. This is the opposite of pessimistic control, which locks resources first to prevent conflicts.
• C. Correct. While Snowflake's MVCC generally prevents data-level blocking between reads and writes, locking still occurs at the metadata level. Certain DML or DDL statements that require an exclusive lock on the table's metadata can block other operations. If such a DML is waiting for this metadata lock, it can in turn block subsequent queries, including SELECTs, which also need to access the table's metadata to plan and begin execution.
• D. Incorrect. This statement is false due to the word 'always'. Most DML operations in Snowflake are granular and lock only the specific micro-partitions they are modifying, not the entire table. This allows other operations on different parts of the table to proceed concurrently. Only specific operations, primarily DDL, will lock the entire table.
• E. Correct. This is a fundamental principle of Snowflake's MVCC architecture for data access. Readers (SELECT) operate on a snapshot of the data and do not block writers (DML). Conversely, writers create new versions of data (micro-partitions) and do not block readers from accessing the older, consistent versions. This non-blocking behavior for data read/write operations is a key reason for Snowflake's high concurrency.

Correct answer: B — Break the single `MERGE` statement into a series of smaller `MERGE` statements, each processing a smaller batch of data within its own transaction.

• A. Incorrect. While increasing the warehouse size might shorten the overall execution time of the `MERGE` statement, it does not change the fundamental locking behavior. The operation would still be a single, long-running transaction that holds locks for its entire duration, which could still be substantial. This approach does not solve the core concurrency problem and is a costly solution.
• B. Correct. This is a standard best practice for improving concurrency with large DML operations. By breaking the single large `MERGE` into multiple smaller batches, each executed in its own transaction, the duration of each individual transaction is significantly reduced. Consequently, locks are acquired and released much more frequently, allowing other DML operations to proceed between the batches. This directly addresses the problem of long-held locks and improves overall system concurrency.
• C. Incorrect. Replacing `MERGE` with a `DELETE` and `INSERT` within the same single transaction offers no advantage for concurrency. The locks required for both operations would still be held until the entire transaction commits. This does not reduce the lock duration and could potentially be less performant and more complex than a `MERGE` statement.
• D. Incorrect. The `LOCK_TIMEOUT` parameter determines how long a statement will wait to acquire a lock that is held by another transaction, not how long it holds a lock itself. Setting this to a low value would cause the `MERGE` job to fail quickly if it's blocked, but it does absolutely nothing to reduce the duration of the locks it holds once it starts running. This would lead to failed jobs rather than improved concurrency.

Correct answer: C — `ACCOUNT_MONITOR`: 5,500, `BI_MONITOR`: 1,000

• A. This option is incorrect. The account-level monitor (`ACCOUNT_MONITOR`) aggregates credit usage from all warehouses in the account. It would not exclude the 1,000 credits consumed by `BI_WH`, so its total should be 5,500 (1,000 + 4,500), not 4,500.
• B. This option is incorrect. The `USED_CREDITS` column reflects the actual credits consumed during the interval, not the credit quota assigned to the monitor. While the `ACCOUNT_MONITOR` value is correct, the `BI_MONITOR` should show the 1,000 credits actually consumed by `BI_WH`, not its 5,000 credit quota.
• C. This is the correct answer. The `BI_MONITOR` is assigned directly to the `BI_WH` warehouse, so its `USED_CREDITS` tracks only the consumption of that specific warehouse, which is 1,000. The `ACCOUNT_MONITOR` is a top-level monitor that aggregates consumption across the entire account, so its `USED_CREDITS` is the sum of all warehouse usage: 1,000 (`BI_WH`) + 4,500 (other warehouses) = 5,500.
• D. This option is incorrect. Although the `BI_MONITOR` value of 1,000 is correct, the `ACCOUNT_MONITOR` value of 20,000 is an arbitrary number and does not reflect the calculated total account consumption of 5,500 credits.

Correct answer: D — View: `QUERY_HISTORY`, Column: `QUERY_TAG` after programmatically setting the tag in the notebook.

• A. Incorrect. The `WAREHOUSE_METERING_HISTORY` view aggregates credit consumption at the warehouse level. This approach cannot isolate usage specifically from Notebooks if the warehouse is shared with other workloads (e.g., worksheets, data loading), thus failing to provide the detailed and accurate breakdown required.
• B. Incorrect. While queries from Notebooks do use the `QUERY_HISTORY` view, the `CLIENT_APPLICATION_ID` of 'SnowflakeUI' is too generic. This identifier is used for all activities originating from the Snowsight web interface, including worksheets, making it impossible to reliably distinguish Notebook-specific queries from other UI-based queries.
• C. Incorrect. The `METERING_DAILY_HISTORY` view provides a high-level, daily summary of costs and lacks the query-level granularity needed for this task. Furthermore, there is no `SERVICE_TYPE` value of 'NOTEBOOKS'; compute credits used by Notebooks are reported under the `WAREHOUSE_METERING` service type.
• D. Correct. Using `QUERY_TAG` is the recommended and most robust method for fine-grained cost attribution. A data scientist can programmatically set a unique `QUERY_TAG` at the beginning of a notebook session (e.g., using `ALTER SESSION SET QUERY_TAG = 'datasci_model_dev_notebook';`). All subsequent queries from that session will be tagged, allowing for precise filtering in the `QUERY_HISTORY` view to accurately track and report the specific credit consumption attributable to that notebook.

Correct answer: B — Create a second, smaller warehouse and direct off-peak traffic to it.

• A. Incorrect. Changing the scaling policy to Economy primarily affects how aggressively new clusters are added; it prioritizes filling existing clusters before spinning up new ones. This would not solve the core issue of the single base cluster running during off-peak hours. Furthermore, it could negatively impact performance by delaying the scale-out process during peak demand.
• B. Correct. This is a common and highly effective Snowflake cost-optimization pattern. By creating a separate, smaller warehouse for off-peak usage, you can right-size the compute for the workload. This allows the large, expensive multi-cluster warehouse to fully suspend during periods of low concurrency, generating significant cost savings. The large warehouse remains available for peak hours, ensuring performance is not compromised.
• C. Incorrect. While decreasing `AUTO_SUSPEND` can reduce costs during periods of complete inactivity, the scenario describes low but consistent usage. A very short suspend time would cause frequent suspend/resume cycles between queries, introducing significant latency for dashboard users each time the warehouse has to wake up. This would lead to a poor user experience and is less effective than separating the workloads.
• D. Incorrect. This change would be counterproductive to the goal of saving costs. Setting the minimum and maximum cluster count to the same value forces the warehouse to run at a fixed, high capacity at all times, eliminating the cost-saving benefits of auto-scaling and suspension. This would dramatically increase credit consumption, especially during off-peak hours.

Correct answer: B — To enable multiple parties to securely analyze their combined datasets for joint insights without sharing the underlying raw data with each other.

• A. Incorrect. This describes a basic data sharing scenario, not the collaborative analysis function of a Data Clean Room. While data might be sanitized, the primary purpose of a clean room is to enable joint analysis across multiple parties' datasets, not just distribute PII-free copies from a single provider.
• B. Correct. This is the exact definition of a Snowflake Data Clean Room. It provides a secure environment where multiple organizations can combine their data for analysis and derive mutual insights without ever exposing their sensitive, underlying raw data to the other parties. This is achieved through secure compute, governed sharing, and strict access controls.
• C. Incorrect. This describes the function of a data staging area, which is part of a data ingestion or ELT/ETL pipeline, often using Snowflake stages. A Data Clean Room is not for data loading but for privacy-preserving, multi-party analytics on data that is already loaded.
• D. Incorrect. This describes data governance features like Snowflake's Data Classification and Dynamic Data Masking. While these features can be used within a Data Clean Room to enhance security, they are not its primary function, which is to facilitate collaborative analysis between different entities.

Correct answer: C — The administrator must explicitly run `GRANT SELECT ON TABLE FINANCE_DB.PUBLIC.Q3_RESULTS TO SHARE FINANCE_SHARE`.

• A. Incorrect. Granting privileges on objects within a schema to a share is a point-in-time operation. New objects created in that schema after the share is configured are not automatically added to the share unless a `FUTURE` grant was previously established.
• B. Incorrect. While `GRANT ... ON FUTURE TABLES` is the correct mechanism to automatically share tables created in the future, it must be executed *before* those tables are created. Since the `Q3_RESULTS` table already exists, this command will not retroactively apply the grant to it.
• C. Correct. Since the table was created after the initial share setup and without a pre-existing `FUTURE` grant, the provider must explicitly grant the `SELECT` privilege on this specific, existing table to the share. This action adds the table to the share and makes it accessible to consumers.
• D. Incorrect. Dropping and recreating a share is a disruptive and unnecessary action. Shares are designed to be dynamic; new object grants can be added to an existing share at any time without affecting the consumer's access to the already-shared objects.

Correct answer: C — A Private listing, where the provider manually adds the account locators of licensed consumers.

• A. Incorrect. A public, free listing would expose the dataset to all Marketplace users, allowing anyone to access the data without a license. Relying on instructions in the description is not an enforcement mechanism and completely contradicts the security and control required by a BYOL model.
• B. Incorrect. Using an artificially high price on a public listing is an unsupported and unreliable workaround, not a proper access control method. This approach does not align with the BYOL model, where licensing and payment occur externally, and it does not truly prevent access.
• C. Correct. A Private listing is the ideal solution for a BYOL model. It allows the provider to maintain complete control over who can see and access the dataset. The provider can collect a consumer's Snowflake account locator during the external licensing process and then manually add that specific account to the private listing, ensuring only authorized, contracted consumers gain access.
• D. Incorrect. While Personalized listings target specific consumers, the inclusion of 'auto-fulfillment' makes this option unsuitable. Auto-fulfillment would grant access automatically upon request, bypassing the provider's essential step of manually verifying the external contract and license before granting access, which is the core of the BYOL workflow.

Correct answers: A, B, C — Implement a Client Redirect connection URL and ensure all applications use it for database connections.; Set the `REPLICATION_SCHEDULE` for the failover group to a low value, such as `10 MINUTES`, to minimize data loss (RPO).; Create warehouses in the secondary account with the same configuration as the primary, and ensure they are started, to reduce recovery time (RTO).

• A. This is a correct action. Implementing a Client Redirect connection URL is critical for minimizing the Recovery Time Objective (RTO). This feature allows client applications to be transparently and automatically redirected to the active (primary) account after a failover event, eliminating the need for manual reconfiguration of connection strings. This significantly speeds up the process of restoring application connectivity.
• B. This is a correct action. The `REPLICATION_SCHEDULE` parameter directly controls the frequency of data synchronization between the primary and secondary accounts. Setting it to a low value, like the minimum of 10 minutes, ensures that the data in the secondary account is as current as possible. This directly minimizes the Recovery Point Objective (RPO), which is the maximum acceptable amount of data loss measured in time.
• C. This is a correct action. While warehouse objects are replicated to the secondary account, they are created in a suspended state. To minimize the Recovery Time Objective (RTO), compute resources must be available immediately after failover. Pre-creating warehouses with identical configurations and ensuring they are started or can be started immediately avoids the delay of provisioning and starting them during the critical recovery window.
• D. This is incorrect. Storing sensitive information like private keys and credentials in a Snowflake internal stage is a severe security anti-pattern. This information should be managed securely using a dedicated secrets manager or vault. This practice does not contribute to RPO or RTO and introduces significant security risks.
• E. This is incorrect. Snowflake does not provide a fully automatic failover feature that detects an outage and promotes a secondary account. The failover process is a deliberate action that must be initiated by an administrator by executing the `ALTER FAILOVER GROUP ... PRIMARY` command. A robust DR plan must include a process for detecting an outage and a script or manual procedure to trigger the failover.

Correct answer: C — The failover group in `ACCT_P` must be configured to include `SHARES`, which will then be active in `ACCT_DR` upon promotion.

• A. Incorrect. Share objects are not automatically included in replication by default. The provider must explicitly configure the failover group to include shares. Furthermore, even with proper configuration, the consumer account must still take action to create a new database from the share in the newly promoted provider account.
• B. Incorrect. This describes a manual recovery process that would be necessary only if the failover group was not configured correctly to include shares. The proper procedure for a robust disaster recovery plan involves proactively replicating the share object itself, not manually recreating it after a failure.
• C. Correct. According to Snowflake documentation, to ensure a share is available after a failover, the provider must explicitly include the `SHARES` object type in the failover group configuration. This action replicates the share object and its associated grants to the secondary account (`ACCT_DR`). When `ACCT_DR` is promoted to primary, the share becomes active, and the consumer (`ACCT_C`) can then create a database from this share in the new primary account.
• D. Incorrect. Snowflake Data Sharing supports cross-region and cross-cloud sharing. The consumer account's location or region does not impact its ability to access a share from the provider. The critical factor is the availability and granting of the share object in the active provider account.

Correct answer: A — CREATE TABLE CUSTOMER_SEGMENTS_ANALYSIS CLONE CUSTOMER_SEGMENTS AT(TIMESTAMP => '2023-10-26 14:00:00 -0700'::timestamp_tz);

• A. Correct. This command utilizes Snowflake's zero-copy cloning in conjunction with Time Travel. Cloning is a metadata-only operation that does not physically duplicate the data. Instead, it creates a new table that points to the same underlying micro-partitions that existed at the specified timestamp. This is the most efficient method as it uses minimal compute resources and incurs no initial additional storage costs, perfectly aligning with the requirements.
• B. Incorrect. This `CREATE TABLE ... AS SELECT` (CTAS) statement will achieve the desired outcome but is highly inefficient compared to cloning. It requires a virtual warehouse to scan all the historical data from the specified point in time and then physically write a new, complete copy of that data into the new table. This process consumes significant compute resources and doubles the storage footprint for the copied data.
• C. Incorrect. This two-step approach is also inefficient. It first creates an empty table with the same structure (`CREATE TABLE ... LIKE`) and then runs an `INSERT` statement to populate it. This process, similar to CTAS, involves a full scan of the historical data and a full write operation, consuming substantial compute and storage resources. It is functionally equivalent to the CTAS in terms of inefficiency for this scenario.
• D. Incorrect. The `UNDROP TABLE` command is used exclusively to restore a table that has been previously dropped. It cannot be used to create a copy or clone of an existing, live table at a specific point in time. This command is functionally inappropriate for the task described.

Correct answer: B — The query will fail, returning an error that the requested time is beyond the retention period.

• A. Incorrect. Fail-safe is a 7-day data recovery service that begins after the Time Travel retention period ends. It is designed for disaster recovery and is only accessible by Snowflake Support. Users cannot directly query data from the Fail-safe period using SQL Time Travel functions like `AT()` or `BEFORE()`.
• B. Correct. The query attempts to access data from 7 days in the past using an `AT(OFFSET)` clause. However, the table's `DATA_RETENTION_TIME_IN_DAYS` parameter is set to 5 days. Because the requested time is outside the configured Time Travel window for the table, Snowflake cannot fulfill the request and will return an error stating that the requested time is beyond the retention period.
• C. Incorrect. The query will not execute successfully. It will fail with an error because the specified timestamp is beyond the table's retention period. An empty result set would be returned only if the query's timestamp was *within* the valid Time Travel window (e.g., 4 days ago), but the specific data did not exist at that historical point.
• D. Incorrect. Snowflake does not automatically route user queries to Snowflake Support. Initiating a data recovery from Fail-safe is a manual process that requires the customer to contact Snowflake Support. A standard SQL query that attempts to access data beyond the Time Travel period will simply fail with an error.