Free ADA-C01 Practice Test Questions and Answers (2026) | Cert Empire Practice Questions

A common rollback strategy in Snowflake involves using Time Travel to restore a table to a state before an erroneous DML or DDL operation. The CREATE TABLE ... CLONE ... BEFORE (STATEMENT => '...') command is used to create a new table containing the data as it existed immediately before the problematic statement was executed. After verifying the cloned table's integrity, the ALTER TABLE ... SWAP WITH ... command is used to atomically swap the corrupted production table with the restored clone. This two-step process effectively rolls back the change with zero downtime for end-users.

A. SYSTEM$CANCELQUERY is used to abort a query that is currently running; it cannot be used to roll back a transaction that has already completed.

C. RESULTSCAN retrieves the output results of a previously executed query, which is useful for auditing but does not restore the table to its prior state.

E. Contacting Snowflake Support to retrieve Fail-safe data is a last-resort disaster recovery measure, not a common, self-service capability for typical rollback scenarios.

1. Snowflake Documentation: Using Time Travel -> Restoring Objects. This section explicitly details the process of restoring a table to a previous state using CREATE TABLE ... CLONE ... BEFORE (STATEMENT => '...').

2. Snowflake Documentation: SQL Command Reference -> ALTER TABLE. The documentation for SWAP WITH states

"Swapping tables is useful for replacing a table with a new/updated version of the table while performing other operations." This is the final step in the rollback pattern.

3. Snowflake Documentation: Understanding Fail-safe. This document clarifies that "Fail-safe is not provided as a tool for accessing historical data... Accessing historical data is the express purpose of Snowflake Time Travel." This confirms that Fail-safe is not a common rollback tool.

4. Snowflake Documentation: SQL Function Reference -> SYSTEM$CANCELQUERY. The documentation confirms this function "Cancels a specified query that is currently running or has been queued." This supports why option A is incorrect for a completed operation.

To securely share unstructured data stored in a stage, the provider must not grant direct access to the stage itself. Instead, access is provided through temporary URLs. A pre-signed URL is a time-limited, self-contained URL that grants access to a specific file. The recommended and most secure method is for the provider (Company A) to create a secure view that calls the GETPRESIGNEDURL function. This view, when added to the share, allows the consumer (Company B) to query it, retrieve the pre-signed URLs, and use them to download the audio files directly from cloud storage for transcription.

A. A standard file URL (GETFILEURL) requires the user to have privileges on the provider's stage and an active session in the provider's account, which the consumer does not have.

C. The METADATA$FILENAME column only provides the name of the file. It does not provide any means to access or download the file's content.

D. The consumer account lacks the necessary privileges on the provider's stage, so providing the stage name and file path is useless for accessing the files.

1. Snowflake Documentation

"Sharing Unstructured Data". This document explicitly states: "A data provider can generate pre-signed URLs that grant temporary access to files without granting privileges on the stage. The provider can create a secure view that generates pre-signed URLs and share the view with a consumer." It also provides a clear example of creating a secure view using GETPRESIGNEDURL for this purpose.

2. Snowflake Documentation

"GETPRESIGNEDURL". The function description confirms its purpose: "Generates a pre-signed URL to a staged file... A pre-signed URL is a temporary URL that can be used to access a file without granting privileges to the stage."

3. Snowflake Documentation

"Introduction to Secure Data Sharing". This guide reinforces the best practice of using secure views to share data

as they prevent consumers from accessing or viewing the underlying data definitions and logic

which is critical when sharing derived data like pre-signed URLs.

In Snowflake, if a role that owns database objects is dropped, the ownership of those objects is automatically transferred to the role that executes the DROP ROLE command. This is a default behavior designed to prevent objects from becoming orphaned. To avoid unintended ownership transfers, the recommended best practice is to explicitly transfer ownership of all objects from the role to a new, appropriate role before executing the DROP ROLE command.

A. The SYSADMIN role does not automatically inherit ownership unless it is the specific role that executes the DROP ROLE command.

B. Ownership is not transferred up the RBAC hierarchy to a parent role; it is explicitly transferred to the executing role.

D. The SECURITYADMIN role does not automatically inherit ownership unless it is the specific role that executes the DROP ROLE command.

---

1. Snowflake Inc. Documentation. (2023). DROP ROLE. SQL Reference > DDL > Account & Session DDL > Roles. Retrieved from https://docs.snowflake.com/en/sql-reference/sql/drop-role.

Reference Point: In the "Usage Notes" section

it explicitly states: "If a role that owns objects is dropped

ownership of the objects is transferred to the role that executes the DROP ROLE command."

2. Snowflake Inc. Documentation. (2023). Overview of Access Control. Security > Access Control in Snowflake. Retrieved from https://docs.snowflake.com/en/user-guide/security-access-control-overview.

Reference Point: The section on "Object Ownership" discusses the principle that every object has an owner. The behavior of DROP ROLE is a specific implementation of how Snowflake maintains this principle

ensuring ownership is never lost.

The most cost-effective and efficient solution is a hybrid model. For accounts within the same region and cloud platform (e.g., among European offices on Azure), Secure Data Sharing is the optimal method. It provides live, read-only access to data without creating a copy, thus incurring no storage or data transfer costs. For sharing data across different regions and cloud providers (i.e., between the European Azure account and the North American AWS account), Database Replication is necessary. This feature creates a synchronized copy of the database in the target account, enabling cross-platform data availability. This combination uses the most appropriate feature for each scenario, fulfilling the bi-directional sharing requirement while minimizing costs.

A. Using replication everywhere is more expensive than data sharing for same-region scenarios due to data transfer, storage, and compute costs.

B. This manual ETL approach is less secure, more complex to manage, and less efficient than using Snowflake's built-in sharing and replication features.

C. This violates a key constraint of the question, which states the multi-region/cloud setup is necessary to comply with PII regulations.

1. Snowflake Documentation: Sharing Data Securely Across Regions and Clouds. This document explicitly outlines the recommended pattern: "To share data with an account in another region

you must first replicate the data to a region where the consumer account is located and then share the data from the replicated database." This directly supports the hybrid approach in option D.

2. Snowflake Documentation: Introduction to Secure Data Sharing. This section clarifies that "Snowflake enables account-to-account sharing of data through shares... No actual data is copied or transferred between accounts." This confirms that sharing is the zero-copy

cost-effective method for same-region scenarios.

3. Snowflake Documentation: Introduction to Database Replication and Failover. This page states

"Database replication... enables you to replicate databases and keep the database objects and stored data synchronized between multiple Snowflake accounts in different regions and across different cloud platforms." This confirms replication is the correct tool for the cross-continent

cross-cloud requirement.

4. Snowflake Documentation: Data Replication Costs. This page details the costs for data transfer and compute for replication

confirming that it is a more costly operation than Secure Data Sharing

making the hybrid approach the most cost-effective.

To create a stream on a source table, a role requires a specific set of minimum privileges. The role must have USAGE on the parent database and the schema to access them. The CREATE STREAM privilege is a schema-level grant, which is necessary to create the stream object within that schema. Finally, the role needs the SELECT privilege on the source table so the stream can track data manipulation language (DML) changes made to it. These combined grants ensure the stream can be successfully created and can access the source table's change data.

A. The CREATE STREAM privilege is granted on a schema, not on a table.

B. This option is missing the CREATE STREAM privilege on the schema, which is essential for creating the stream object itself.

C. The CREATE STREAM privilege is granted on a schema, not on the parent database.

1. Snowflake Inc. (2024). CREATE STREAM Documentation. Snowflake Documentation. Retrieved from https://docs.snowflake.com/en/sql-reference/sql/create-stream.

Reference Specifics: In the "Required Privileges" section

it states: "Creating a stream requires the CREATE STREAM privilege on the schema as well as the USAGE privilege on the parent database and schema and the SELECT privilege on the source object (table

external table

or view)."

2. Snowflake Inc. (2024). Access Control Privileges. Snowflake Documentation. Retrieved from https://docs.snowflake.com/en/user-guide/security-access-control-privileges.

Reference Specifics: The "Schema Privileges" table lists CREATE STREAM as a privilege that can be granted on a schema

confirming its scope. The "Table Privileges" section confirms SELECT is required to query a table.

The SECURITYADMIN role is granted the MANAGE GRANTS privilege by default. This powerful privilege allows the role to modify any grant in the account, including revoking grants made by other roles. The USERADMIN role, which is focused solely on creating and managing users and roles, does not have this global grant management capability. While SECURITYADMIN inherits the privileges of USERADMIN (like creating users/roles), its unique and distinguishing function is the ability to manage system-wide grants.

A. Reset a Snowflake user's password: The USERADMIN role can perform user management tasks, including resetting passwords for users it creates or has ownership over.

C. Create new users: This is a primary function of the USERADMIN role, which has the CREATE USER privilege.

D. Create new roles: This is a primary function of the USERADMIN role, which has the CREATE ROLE privilege.

---

1. Snowflake Documentation: System-Defined Roles

Section: SECURITYADMIN

Content: "Role that can manage any object grant globally... This role is granted the MANAGE GRANTS privilege to allow it to modify any grant

including revoking it."

Section: USERADMIN

Content: "Role that is dedicated to user and role management only. More specifically

this role can create and manage users and roles..."

2. Snowflake Documentation: Access control privileges

Section: Global Privileges

Content: Describes the MANAGE GRANTS privilege: "Enables a role to grant or revoke privileges that have been granted by the privilege grantor (i.e. the role that holds the OWNERSHIP privilege on the object) to other roles." The SECURITYADMIN role holds this privilege by default.

The query uses the CURRENTTIMESTAMP function, which is volatile. This means it returns a different value each time it is executed, preventing the query text from being identical to previous executions. For Snowflake to use the persisted query result cache, the new query must be a syntactic match to the prior one. By replacing CURRENTTIMESTAMP with CURRENTDATE, the query becomes deterministic for a 24-hour period. All executions of the query on the same day will be identical, allowing Snowflake to retrieve results from the cache instead of re-executing the query.

A. Wrapping the query in a UDF does not solve the underlying issue; the UDF would still call the volatile CURRENTTIMESTAMP function, preventing caching.

B. While users must use the same virtual warehouse to share cached results, this does not fix the volatile function in the query, which is the primary blocker.

C. Using the same security role is a prerequisite for result cache reuse, but it does not address the core problem of the non-deterministic function in the query.

1. Snowflake Documentation: Using Persisted Query Results

Section: Query Result Reuse > Usage Notes

Content: "Certain non-deterministic functions might prevent a query from using the result cache. For example

most functions that deal with the current date or time

such as CURRENTTIME

CURRENTTIMESTAMP

and LOCALTIMESTAMP

are not cacheable." This directly explains why the original query is not optimized for caching.

2. Snowflake Documentation: Date and Time Functions

Section: CURRENTDATE

CURRENTTIMESTAMP

Content: CURRENTDATE returns the current date of the system

which is constant throughout a single day. In contrast

CURRENTTIMESTAMP returns the current timestamp

which changes with each execution. This distinction is key to making the query cacheable on a daily basis.

The DROP ROLE command will fail. According to Snowflake's access control model, a role that owns any securable objects cannot be dropped. The SHOW GRANTS output clearly indicates that ROLE A has the OWNERSHIP privilege on the table DB1.S1.T1. An attempt to drop this role will result in an error, and the operation will be aborted. Because the command fails, the state of the system remains unchanged, and all existing grants, including the SELECT privileges for roles B and C, will persist.

A. The table object DB1. S1. T1 will be dropped.

Dropping a role does not cascade to drop the objects it owns; rather, ownership prevents the role from being dropped.

B. The OWNERSHIP privilege on table DB1. S1. T1 will be transferred to the ACCOUNTADMIN role.

Ownership is not transferred automatically. It must be explicitly transferred to another role using GRANT OWNERSHIP before the owning role can be dropped.

C. The SELECT privilege on table DB1. S1. T1 to role B will be shown as GRANTEDBY the role ACCOUNTADMIN.

The grantor of a privilege is not reassigned. If the grantor role were successfully dropped, the grants it made would be revoked, not transferred.

---

1. Snowflake Documentation: DROP ROLE

Under "Usage Notes

" it explicitly states: "An error is returned if the role owns any objects. To drop the role

you must first transfer ownership of the objects to another role." This directly supports the conclusion that the command will fail.

Source: Snowflake Documentation

DROP ROLE

2. Snowflake Documentation: Overview of Access Control

The section on "Object Ownership" explains that every object is owned by a single role. This principle underpins the rule that an owning role cannot be dropped until ownership is transferred

preventing objects from becoming orphaned.

Source: Snowflake Documentation

Access Control Overview

3. Snowflake Documentation: GRANT OWNERSHIP

This command's documentation details the required procedure for transferring object ownership. It reinforces that dropping an owning role is a two-step process: first transfer ownership

then drop the role. The question's scenario omits the first required step.

Source: Snowflake Documentation

GRANT OWNERSHIP

Before a database can be replicated to another account within the same organization, replication must be explicitly enabled for both the source and target accounts. This action requires the ORGADMIN role. Subsequently, the ACCOUNTADMIN (or a role with sufficient privileges) in the source account must alter the specific database to authorize replication to the target account. Option A correctly outlines this entire prerequisite sequence: using ORGADMIN to enable replication for both ACCOUNT1 and ACCOUNT2, and then using ACCOUNTADMIN in ACCOUNT1 to authorize PRODDB for replication to ACCOUNT2.

B: This option is incomplete. It correctly enables replication for the source account (ACCOUNT1) but fails to enable it for the target account (ACCOUNT2), which is a mandatory prerequisite.

C: This statement is incorrect. Database replication between accounts, even within the same organization, is not enabled by default and requires explicit configuration by an ORGADMIN.

D: This statement is incorrect. Snowflake explicitly supports replicating databases from an account on a higher edition (e.g., Enterprise) to an account on a lower edition (e.g., Standard).

1. Snowflake Documentation: Introduction to replication and failover across multiple accounts.

Section: Prerequisites for Replication -> Step 1: Enable Replication for Accounts in the Organization. This section states

"A user with the ORGADMIN role must enable replication for each source and target account in the organization." It provides the exact syntax: SELECT SYSTEM$GLOBALACCOUNTSETPARAMETER('.'

'ENABLEACCOUNTDATABASEREPLICATION'

'true');. This supports the first two commands in option A.

2. Snowflake Documentation: Replicating a primary database.

Section: Step 2: Promote a Local Database to a Primary Database. This section details the next step: "A user with the ACCOUNTADMIN role... in the source account executes an ALTER DATABASE … ENABLE REPLICATION TO ACCOUNTS statement to authorize replication of the primary database to one or more accounts...". This supports the final command in option A.

3. Snowflake Documentation: Replication and Failover.

Section: Replication from a source account on a higher edition. This section confirms that "Replication and failover from a source account on a higher edition to a target account on a lower edition is supported." This directly refutes option D.

The scenario describes a high-concurrency workload of many small, simple queries (INSERTs). A large, single-cluster warehouse (like a 2XL) is not cost-effective for this pattern. Its resources are designed for complex, single queries, not for handling a high volume of parallel simple ones.

The most effective solution is to use a multi-cluster warehouse (MCW). By resizing the warehouse to a smaller base size (e.g., SMALL) and enabling auto-scaling with multiple clusters, the system can dynamically add compute resources to handle the parallel INSERTs. Each small query can be processed efficiently by a smaller warehouse, and the MCW architecture ensures they run concurrently, minimizing overall load time. This "scaling out" approach is more cost-effective than "scaling up" a single large warehouse.

A. Deploying another 2XL warehouse would increase concurrency but would also significantly increase costs, directly contradicting the primary goal of cost reduction.

B. Changing to a 4XL warehouse doubles the cost and is ineffective for this workload. Scaling up benefits large, complex queries, not a high number of simple, concurrent ones.

D. While table contention can be an issue, this option addresses data modeling rather than the core compute resource and cost problem described, which is about inefficient warehouse configuration for the workload.

1. Snowflake Documentation: Multi-cluster Warehouses: "Multi-cluster warehouses are designed specifically for handling queuing and performance issues related to large numbers of concurrent users and/or queries. ... When you run a query

it is assigned to the next available cluster." This supports using MCW for the "many parallel INSERTs" scenario.

2. Snowflake Documentation: Warehouse Considerations - Resizing a Warehouse: "In general

unless you are running large

complex queries that require more compute resources

you should use a smaller warehouse... Scaling up doesn’t always result in a proportional performance increase

particularly for small

simple queries." This confirms that a 2XL or 4XL is inappropriate for small INSERTs and that a smaller size is more efficient.

3. Snowflake Documentation: Overview of Warehouses - Increased Concurrency: "A multi-cluster warehouse can be configured to automatically start and stop additional clusters as needed to handle fluctuating query loads. This enables you to handle a high number of concurrent queries without requiring a large warehouse." This directly validates the approach in the correct answer.