Snowflake ADA-C01 Dumps - Pass SnowPro Advanced: Administrator Certification Exam in 2026

According to the Snowflake documentation1, Snowflake uses a multi-version concurrency control (MVCC) model, which means that each transaction operates on a consistent snapshot of the database at a point in time. This allows queries and DML statements to run concurrently without blocking each other, as they do not modify the same data. Therefore, a deadlock, which occurs when concurrent transactions are waiting on resources that are locked by each other, cannot happen in Snowflake. Option B is incorrect because queries and DML statements do not block each other in Snowflake, unless they are explicitly started transactions and multiple statements in each transaction2. Option C is incorrect because transaction locking in Snowflake is enforced at the partition level, not the row or table level3. Option D is incorrect because queries executed within a given transaction do not see that transaction's uncommitted changes, but only the committed changes that occurred before the transaction started1.

According to the Multi-cluster Warehouses documentation, auto-suspend is a feature that allows a warehouse to automatically suspend itself after a specified period of inactivity. For a multi-cluster warehouse, auto-suspend applies to the entire warehouse, not to individual clusters. Therefore, auto-suspend occurs when the minimum number of clusters is running and there is no activity for the specified period of time. The other options are incorrect because:

* A. Auto-suspend does not occur when there has been no activity on any cluster for the specified period of time. This would imply that each cluster has its own auto-suspend timer, which is not the case. The warehouse has a single auto-suspend timer that is reset by any activity on any cluster.

* B. Auto-suspend does not occur after a specified period of time when an additional cluster has started on the maximum number of clusters specified for a warehouse. This would imply that the auto-suspend timer is affected by the number of clusters running, which is not the case. The auto-suspend timer is only affected by the activity on the warehouse, regardless of the number of clusters running.

* D. Auto-suspend does apply for multi-cluster warehouses, as explained above. It is a feature that can be enabled or disabled for any warehouse, regardless of the number of clusters.

According to the Snowflake Warehouse Cost Optimization blog post, one of the strategies to reduce the cost of running a warehouse is to use a multi-cluster warehouse with auto-scaling enabled. This allows the warehouse to automatically adjust the number of clusters based on the concurrency demand and the queue size. A multi-cluster warehouse can also be configured with a minimum and maximum number of clusters, as well as a scaling policy to control the scaling behavior. This way, the warehouse can handle the parallel load queries efficiently without wasting resources or credits. The blog post also suggests using a smaller warehouse size, such as SMALL or XSMALL, for loading data, as it can perform better than a larger warehouse size for small INSERTs. Therefore, the best option to reduce the costs while minimizing the overall load times for migrating data warehouse history is to keep the warehouse as a SMALL or XSMALL and configure it as a multi-cluster warehouse to handle the parallel load queries. The other options are incorrect because:

* A. Deploying another 2XL warehouse to handle a portion of the load queries will not reduce the costs, but increase them. It will also introduce complexity and potential inconsistency in managing the data loading process across multiple warehouses.

* B. Changing the 2XL warehouse to 4XL will not reduce the costs, but increase them. It will also provide more compute resources than needed for small INSERTs, which are not CPU-intensive but I/O-intensive.

* D. Converting the INSERTs to several tables will not reduce the costs, but increase them. It will also create unnecessary data duplication and fragmentation, which will affect the query performance and data quality.

According to the Working with Secure Views documentation, secure views are designed to limit access to sensitive data that should not be exposed to all users of the underlying table(s). When sharing secure views with another account, the view definition must include a function that returns the identity of the user who is querying the view, such as CURRENT_USER, CURRENT_ROLE, or CURRENT_ACCOUNT. These functions can be used to filter the rows in the base table based on the user's identity. For example, a secure view can use the CURRENT_USER function to compare the user name with a column in the base table that contains the authorized user names. Only the rows that match the user name will be returned by the view. The CURRENT_CLIENT function is not suitable for this purpose, because it returns the IP address of the client that is connected to Snowflake, which is not related to the user's identity.

To retrieve a list of schemas deleted within the last 2 days from the DB1 database, you need a metadata view that includes historical data, including dropped (deleted) objects.

Let's review the options:

B. SNOWFLAKE.ACCOUNT_USAGE.SCHEMATA

This is the correct choice because:

It includes metadata for all schemas, even deleted ones, within the retention period.

It contains a DELETED column and a DELETED_ON timestamp column.

You can filter rows with:

sql

CopyEdit

SELECT *

FROM SNOWFLAKE.ACCOUNT_USAGE.SCHEMATA

WHERE DELETED IS TRUE

AND DELETED_ON >= DATEADD(DAY, -2, CURRENT_TIMESTAMP())

AND CATALOG_NAME = 'DB1';

A. SHOW SCHEMAS IN DATABASE DB1;

Only shows current (active) schemas --- does not include deleted schemas.

C. DB1.INFORMATION_SCHEMA.SCHEMATA

Like option A, this view only includes active schemas in the current database.

No info on deleted schemas is retained.

D. SNOWFLAKE.ACCOUNT_USAGE.DATABASES

This metadata view tracks databases, not individual schemas.

SnowPro Administrator Reference:

SNOWFLAKE.ACCOUNT_USAGE.SCHEMATA documentation

Metadata includes both active and deleted schemas (within retention window).