ADA-C01 Exam Dumps - SnowPro Advanced: Administrator Certification Exam

•Option A is correct because the ALTER DATABASE … ENABLE REPLICATION TO ACCOUNTS command must be issued from the primary account that owns the database to be replicated1.

•Option B is incorrect because users must be granted REPLICATIONGRANTER privileges in order to enable replication1.

•Option C is incorrect because to start replication, the ALTER DATABASE … REFRESH command must be run on the primary database, not the secondary database1.

•Option D is incorrect because replication can occur across different cloud providers, as well as across regions2.

•Option E is correct because databases created from shares can be replicated, as long as the share is active and the database is not dropped or altered1.

•Option F is correct because users can have unlimited primary databases and they can be replicated to an unlimited number of accounts if all accounts are within the same organization1.

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.

This command will generate a sample of 20% of the table by using the SYSTEM (or BLOCK) sampling method, which selects each block of rows with a probability of 20/100. This method is suitable for taking data by blocks of rows, as the question requires. According to the Snowflake documentation, “SYSTEM (or BLOCK): Includes each block of rows with a probability of p/100. Similar to flipping a weighted coin for each block of rows. This method does not support fixed-size sampling.” The other options are either incorrect or do not meet the requirements of the question. Option A uses the BERNOULLI (or ROW) sampling method, which selects each row with a probability of 20/100, but does not take data by blocks of rows. Option C uses the BLOCK sampling method, but specifies a fixed number of rows (20) instead of a percentage (20%). Option D uses the SYSTEM sampling method, but specifies a seed value (1), which makes the sampling repeatable, contrary to the question.

According to the Using Persisted Query Results documentation, the RESULT_SCAN function allows you to query the result set of a previous command as if it were a table. This can improve the performance of the second query by avoiding reading the same data from micro-partitions again. The other actions do not provide optimal performance for the second query because:

•Assigning additional clusters to the virtual warehouse does not affect the data access speed, but only the query execution speed. It also increases the cost of the warehouse.

•Increasing the STATEMENT_TIMEOUT_IN_SECONDS parameter in the session does not improve the performance of the query, but only allows it to run longer before timing out. It also increases the risk of resource contention and deadlock.

•Preventing the virtual warehouse from suspending between the running of the first and second queries does not guarantee that the data will be cached in memory, as Snowflake uses a least recently used (LRU) cache eviction policy. It also increases the cost of the warehouse.

https://docs.snowflake.com/en/user-guide/querying-persisted-results

According to the Snowflake documentation1, organizations are a feature that allows linking the accounts owned by a business entity, simplifying account management and billing, replication and failover, data sharing, and other account administration tasks. Some of the benefits of using organizations are:

•Administrators can monitor and understand usage across all accounts in the organization using the ORGANIZATION_USAGE schema, which provides historical usage data for all accounts in the organization via views in a shared database named SNOWFLAKE2. This can help to optimize costs and performance across the organization.

•Administrators have the ability to create accounts in any available cloud provider or region using the CREATE ACCOUNT command, which allows specifying the cloud platform and region for the new account3. This can help to meet the business needs and compliance requirements of the organization.

Option A is incorrect because administrators cannot change Snowflake account editions on-demand based on need, but rather have to contact Snowflake Support to request an edition change4. Option C is incorrect because administrators cannot simplify data movement across all accounts within the organization, but rather have to enable account database replication for both the source and target accounts, and use the ALTER DATABASE … ENABLE REPLICATION TO ACCOUNTS command to promote a local database to serve as the primary database and enable replication to the target accounts5. Option D is incorrect because user administration is not simplified across all accounts within the organization, but rather requires creating and managing users, roles, and privileges for each account separately, unless using a federated authentication method such as SSO or SCIM.

According to the Snowflake documentation1, the COPY GRANTS option can be used to copy all privileges, except OWNERSHIP, from the existing table to the new transient table. This option also preserves any future grants defined for the object type in the schema. Option A is incorrect because it does not copy any grants from the original table. Option C is incorrect because it does not copy the data from the original table, only the structure and grants. Option E is incorrect because transient tables are not session-based and do not have a Fail-safe period, but they do have a Time Travel retention period2.

1: CREATE TABLE | Snowflake Documentation 2: Working with Temporary and Transient Tables | Snowflake Documentation

According to the Snowflake documentation1, database replication across accounts within the same organization requires the following steps:

•Link the accounts in the organization using the ORGADMIN role.

•Enable account database replication for both the source and target accounts using the SYSTEM$GLOBAL_ACCOUNT_SET_PARAMETER function.

•Promote a local database to serve as the primary database and enable replication to the target accounts using the ALTER DATABASE … ENABLE REPLICATION TO ACCOUNTS command.

•Create a secondary database in the target account using the CREATE DATABASE … FROM SHARE command.

•Refresh the secondary database periodically using the ALTER DATABASE … REFRESH command.

Option A is incorrect because it does not include the step of creating a secondary database in the target account. Option C is incorrect because replicating databases across accounts within the same organization is not enabled by default, but requires enabling account database replication for both the source and target accounts. Option D is incorrect because it is possible to replicate a database from an Enterprise edition Snowflake account to a Standard edition Snowflake account, as long as the IGNORE EDITION CHECK option is used in the ALTER DATABASE … ENABLE REPLICATION TO ACCOUNTS command2. Option B is correct because it includes all the necessary configuration steps in ACCOUNT1, except for creating a secondary database in ACCOUNT2, which can be done after the replication is enabled.

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.

Network policies are used to restrict access to the Snowflake service and internal stages based on user IP address1. To create network policies, a role must have the global permission of CREATE NETWORK POLICY2. By default, the system-defined roles of SECURITYADMIN and ACCOUNTADMIN have this permission3. However, any other role can be granted this permission by an administrator4. Therefore, the answer is B, C, and E. The other options are incorrect because SYSADMIN and ORGADMIN do not have the CREATE NETWORK POLICY permission by default3, and network policies are not tied to specific databases5.

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.

According to the Using Persisted Query Results documentation, the RESULT_SCAN function allows you to query the result set of a previous command as if it were a table. The LAST_QUERY_ID function returns the query ID of the most recent statement executed in the current session. Therefore, the combination of these two functions can be used to access the output of the SHOW WAREHOUSES command, which returns the configurations of all the virtual warehouses in the account. However, to persist the warehouse data in JSON format in the table VWH_META, the OBJECT_CONSTRUCT function is needed to convert the output of the SHOW WAREHOUSES command into a VARIANT column. The OBJECT_CONSTRUCT function takes a list of key-value pairs and returns a single JSON object. Therefore, the correct commands to execute are:

1.SHOW WAREHOUSES;

2.INSERT INTO VWH_META SELECT CURRENT_TIMESTAMP (), OBJECT_CONSTRUCT (*) FROM TABLE (RESULT_SCAN (LAST_QUERY_ID ()));

The other options are incorrect because:

•A. This option does not use the OBJECT_CONSTRUCT function, so it will not persist the warehouse data in JSON format. Also, it is missing the * symbol in the SELECT clause, so it will not select any columns from the result set of the SHOW WAREHOUSES command.

•B. This option does not use the OBJECT_CONSTRUCT function, so it will not persist the warehouse data in JSON format. It will also try to insert multiple columns into a single VARIANT column, which will cause a type mismatch error.

•D. This option does not use the OBJECT_CONSTRUCT function, so it will not persist the warehouse data in JSON format. It will also try to use the RESULT_SCAN function on a subquery, which is not supported. The RESULT_SCAN function can only be used on a query ID or a table name.

According to the Snowflake documentation1, a standard listing is a type of listing that provides free access to the full data product, with no payment required. A standard listing can be added and queried immediately by the consumer, as long as they accept the terms and conditions of the listing. A monetized listing is a type of listing that charges for access to the data product, using the pricing models offered by Snowflake. A monetized listing requires the consumer to provide payment information and agree to the billing terms before accessing the data product. A regional listing is not a type of listing, but a way to specify the regions where the listing is available. A personalized listing is a type of listing that provides limited trial access to the data product, with unlimited access to the full data product available upon request. A personalized listing requires the consumer to request access from the provider and wait for the provider to grant access before accessing the data product. Therefore, the only type of listing that can be added and queried immediately is the standard listing.

According to the Snowflake documentation1, stages without credentials are a way to create external stages that use storage integrations to access data files in cloud storage without providing any credentials to Snowflake. Storage integrations are objects that define a trust relationship between Snowflake and a cloud provider, allowing Snowflake to authenticate and authorize access to the cloud storage. To limit data exfiltration after a storage integration and associated stages are created, the following account-level parameters can be set:

•REQUIRE_STORAGE_INTEGRATION_FOR_STAGE_CREATION: This parameter enforces that all external stages must be created using a storage integration. This prevents users from creating external stages with inline credentials or URLs that point to unauthorized locations.

•REQUIRE_STORAGE_INTEGRATION_FOR_STAGE_OPERATION: This parameter enforces that all operations on external stages, such as PUT, GET, COPY, and LIST, must use a storage integration. This prevents users from performing operations on external stages with inline credentials or URLs that point to unauthorized locations.

•PREVENT_UNLOAD_TO_INLINE_URL: This parameter prevents users from unloading data from Snowflake tables to inline URLs that do not use a storage integration. This prevents users from exporting data to unauthorized locations.

Therefore, the correct answer is option D, which sets all these parameters to true. Option A is incorrect because it sets PREVENT_UNLOAD_TO_INLINE_URL to false, which allows users to unload data to inline URLs that do not use a storage integration. Option B is incorrect because it sets both REQUIRE_STORAGE_INTEGRATION_FOR_STAGE_CREATION and REQUIRE_STORAGE_INTEGRATION_FOR_STAGE_OPERATION to false, which allows users to create and operate on external stages without using a storage integration. Option C is incorrect because it sets all the parameters to false, which does not enforce any restrictions on data exfiltration.