Latest ADA-C01 exam dumps with real Snowflake questions and answers
ADA-C01 Exam in First Attempt Guaranteed
Snowflake ADA-C01 Exam Syllabus Topics:
| Topic | Details |
|---|---|
| Topic 1 |
|
| Topic 2 |
|
| Topic 3 |
|
| Topic 4 |
|
| Topic 5 |
|
NEW QUESTION # 19
What are benefits of creating and maintaining resource monitors in Snowflake? (Select THREE).
- A. Multiple triggers can be configured across various virtual warehouse thresholds.
- B. Resource monitor governance is tightly controlled and monitors can only be created by the ACCOUNTADMIN role or users with the CREATE MONITOR privilege.
- C. Resource monitors can be applied to more than one virtual warehouse.
- D. Multiple resource monitors can be applied to a single virtual warehouse.
- E. The cost of running a resource monitor is only 10% of a credit, per day of operation.
- F. Resource monitors add no additional load to virtual warehouse compute.
Answer: A,C,F
Explanation:
Explanation
According to the Snowflake documentation1, resource monitors are a feature that helps you manage and control Snowflake costs by monitoring and setting limits on your compute resources. Resource monitors do not consume any credits or add any load to the virtual warehouses they monitor1. Resource monitors can also have multiple triggers that specify different actions (such as suspending or notifying) when certain percentages of the credit quota are reached2. Resource monitors can be applied to either the entire account or a specific set of individual warehouses1. The other options are not benefits of resource monitors. The cost of running a resource monitor is negligible, not 10% of a credit3. Multiple resource monitors cannot be applied to a single virtual warehouse; only one resource monitor can be assigned to a warehouse at a time2. Resource monitor governance is not tightly controlled; account administrators can enable users with other roles to view and modify resource monitors using SQL2.
NEW QUESTION # 20
An Administrator wants to delegate the administration of a company's data exchange to users who do not have access to the ACCOUNTADMIN role.
How can this requirement be met?
- A. Grant imported privileges on data exchange EXCHANGE_NAME to ROLE_NAME;
- B. Grant modify on data exchange EXCHANGE_NAME to ROLE_NAME;
- C. Grant ownership on data exchange EXCHANGE_NAME to ROLE NAME;
- D. Grant usage on data exchange EXCHANGE_NAME to ROLE_NAME;
Answer: B
Explanation:
Explanation
According to the [GRANT MODIFY] documentation, the MODIFY privilege on a data exchange allows a role to perform administrative tasks on the data exchange, such as inviting members, approving profiles, and reviewing listings. This privilege can be granted by the ACCOUNTADMIN role or a role that already has the MODIFY privilege on the data exchange. Therefore, to delegate the administration of a company's data exchange to users who do not have access to the ACCOUNTADMIN role, the best option is to grant the MODIFY privilege on the data exchange to a role that the users can assume. The other options are incorrect because:
*A. There is no such privilege as IMPORTED PRIVILEGES in Snowflake. The correct privilege name is IMPORT SHARE, which allows a role to create a database from a share. This privilege is not related to the administration of a data exchange, but to the consumption of shared data.
*C. There is no such privilege as OWNERSHIP in Snowflake. The correct privilege name is OWNED BY, which indicates the role that owns an object and has full control over it. However, this privilege cannot be granted or revoked, but only transferred by the current owner to another role using the GRANT OWNERSHIP command. Therefore, this option is not feasible for delegating the administration of a data exchange.
*D. The USAGE privilege on a data exchange allows a role to access the data exchange and view the available data listings. This privilege does not allow a role to perform administrative tasks on the data exchange, such as inviting members, approving profiles, and reviewing listings. Therefore, this option is not sufficient for delegating the administration of a data exchange.
NEW QUESTION # 21
An Administrator receives data from a Snowflake partner. The partner is sharing a dataset that contains multiple secure views. The Administrator would like to configure the data so that only certain roles can see certain secure views.
How can this be accomplished?
- A. Clone the data and insert it into a company-owned share and apply the desired RBAC on the new tables.
- B. Apply RBAC directly onto the partner's shared secure views.
- C. Create views over the incoming shared database and apply the desired RBAC onto these views.
- D. Individually grant imported privileges onto the schema in the share.
Answer: C
Explanation:
Explanation
According to the Snowflake documentation1, secure views are only exposed to authorized users who have been granted the role that owns the view. Therefore, applying RBAC directly onto the partner's shared secure views (option A) is not possible, as the administrator does not own those views. Individually granting imported privileges onto the schema in the share (option B) is also not feasible, as the privileges granted on the schema do not apply to existing secure views, only tofuture ones2. Cloning the data and inserting it into a company-owned share (option C) is not recommended, as it would create unnecessary duplication of data and increase storage costs. The best option is to create views over the incoming shared database and apply the desired RBAC onto these views (option D). This way, the administrator can control the access to the data based on the roles in their account, without modifying the original data or views from the partner.
NEW QUESTION # 22
An Administrator is evaluating a complex query using the EXPLAIN command. The Globalstats operation indicates 500 partitionsAssigned.
The Administrator then runs the query to completion and opens the Query Profile. They notice that the partitions scanned value is 429.
Why might the actual partitions scanned be lower than the estimate from the EXPLAIN output?
- A. The GlobalStats partition assignment includes the micro-partitions that will be assigned for preservation of the query results.
- B. The EXPLAIN results always include a 10-15% safety factor in order to provide conservative estimates.
- C. In-flight data compression will result in fewer micro-partitions being scanned at the virtual warehouse layer than were identified at the storage layer.
- D. Runtime optimizations such as join pruning can reduce the number of partitions and bytes scanned during query execution.
Answer: D
Explanation:
Explanation
The EXPLAIN command returns the logical execution plan for a query, which shows the upper bound estimates for the number of partitions and bytes that might be scanned by the query1. However, these estimates do not account for the runtime optimizations that Snowflake performs to improve the query performance and reduce the resource consumption2. One of these optimizations is join pruning, which eliminates unnecessary partitions from the join inputs based on the join predicates2. This can result in fewer partitions and bytes scanned than the estimates from the EXPLAIN output3. Therefore, the actual partitions scanned value in the Query Profile can be lower than the partitionsAssigned value in the EXPLAIN output4.
NEW QUESTION # 23
DatabaseA has a single schema called Schema1. This schema contains many tables and views. The ANALYST role has privileges to select from all objects in Database A. Schema1.
The SYSADMIN role clones DatabaseA to DatabaseA_clone.
What privileges does the ANALYST role have on tables and views in DatabaseA_clone? (Select TWO).
- A. SELECT on all tables and views in DatabaseA_clone. Schema1
- B. SELECT on all tables, and only secure views in DatabaseA_clone. Schemal
- C. USAGE on the schema DatabaseA clone
- D. USAGE on the database DatabaseA_clone. Schemal
- E. SELECT on all tables, and only non-secure views in DatabaseA_clone. Schemal
Answer: A,E
Explanation:
According to the Snowflake documentation, when a database or schema is cloned, the clone inherits all granted privileges on the clones of all child objects contained in the source object, such as tables and views. However, the clone of the container itself does not inherit the privileges granted on the source container. Therefore, the ANALYST role will have SELECT privilege on all tables and views in DatabaseA_clone.Schema1, but not USAGE privilege on the database or schema. The type of view (secure or non-secure) does not affect the cloning of privileges.
NEW QUESTION # 24
What are benefits of creating and maintaining resource monitors in Snowflake? (Select THREE).
- A. Multiple triggers can be configured across various virtual warehouse thresholds.
- B. Resource monitor governance is tightly controlled and monitors can only be created by the ACCOUNTADMIN role or users with the CREATE MONITOR privilege.
- C. Resource monitors can be applied to more than one virtual warehouse.
- D. Multiple resource monitors can be applied to a single virtual warehouse.
- E. The cost of running a resource monitor is only 10% of a credit, per day of operation.
- F. Resource monitors add no additional load to virtual warehouse compute.
Answer: A,C,F
Explanation:
According to the Snowflake documentation1, resource monitors are a feature that helps you manage and control Snowflake costs by monitoring and setting limits on your compute resources. Resource monitors do not consume any credits or add any load to the virtual warehouses they monitor1. Resource monitors can also have multiple triggers that specify different actions (such as suspending or notifying) when certain percentages of the credit quota are reached2. Resource monitors can be applied to either the entire account or a specific set of individual warehouses1. The other options are not benefits of resource monitors. The cost of running a resource monitor is negligible, not 10% of a credit3. Multiple resource monitors cannot be applied to a single virtual warehouse; only one resource monitor can be assigned to a warehouse at a time2. Resource monitor governance is not tightly controlled; account administrators can enable users with other roles to view and modify resource monitors using SQL2.
NEW QUESTION # 25
A Snowflake organization MYORG consists of two Snowflake accounts:
The ACCOUNT1 has a database PROD_DB and the ORGADMIN role enabled.
Management wants to have the PROD_DB database replicated to ACCOUNT2.
Are there any necessary configuration steps in ACCOUNT1 before the database replication can be configured and initiated in ACCOUNT2?
- A. It is not possible to replicate a database from an Enterprise edition Snowflake account to a Standard edition Snowflake account.
- B. USE ROLE ORGADMIN;
SELECT SYSTEMSGLOBAL ACCOUNT SET_PARAMETER ( 'MYORG. ACCOUNT1',
'ENABLE_ACCOUNT_DATABASE_REPLICATION', 'TRUE');
USE ROLE ACCOUNTADMIN;
ALTER DATABASE PROD_DB ENABLE REPLICATION TO ACCOUNTS MYORG. ACCOUNT2
IGNORE EDITION CHECK; - C. No configuration steps are necessary in ACCOUNT1. Replicating databases across accounts within the same Snowflake organization is enabled by default.
- D. USE ROLE ORGADMIN;
SELECT SYSTEMSGLOBAL_ACCOUNT_SET_PARAMETER ('MYORG. ACCOUNT1',
'ENABLE_ACCOUNT_DATABASE_REPLICATION', 'TRUE');
SELECT SYSTEMSGLOBAL_ACCOUNT_SET_PARAMETER ('MYORG. ACCOUNT2',
'ENABLE_ACCOUNT_DATABASE_REPLICATION', 'TRUE');
USE ROLE ACCOUNTADMIN;
ALTER DATABASE PROD DB ENABLE REPLICATION TO ACCOUNTS MYORG. ACCOUNT2;
Answer: B
Explanation:
Explanation
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.
NEW QUESTION # 26
Which tasks can be performed by the ORGADMIN role? (Select THREE).
- A. Create secure views on application tables within the organization.
- B. Perform zero-copy cloning on account data.
- C. View usage information for all accounts in the organization.
- D. Create one or more accounts in the organization.
- E. Create a reader account to share data with another organization.
- F. View a list of all regions enabled for the organization.
Answer: C,D,F
Explanation:
Explanation
A user with the ORGADMIN role can perform the following tasks1:
*Create one or more accounts in the organization.
*View a list of all regions enabled for the organization.
*View usage information for all accounts in the organization.
Option C is incorrect because creating secure views on application tables is not a function of the ORGADMIN role, but rather a function of the roles that have access to the tables and schemas within the accounts. Option E is incorrect because performing zero-copy cloning on account data is not a function of the ORGADMIN role, but rather a function of the roles that have the CLONE privilege on the objects within the accounts. Option F is incorrect because creating a reader account to share data with another organization is not a function of the ORGADMIN role, but rather a function of the roles that have the CREATE SHARE privilege on the objects within the accounts.
NEW QUESTION # 27
MY_TABLE is a table that has not been updated or modified for several days. On 01 January 2021 at 07:01, a user executed a query to update this table. The query ID is
'8e5d0ca9-005e-44e6-b858-a8f5b37c5726'. It is now 07:30 on the same day.
Which queries will allow the user to view the historical data that was in the table before this query was executed? (Select THREE).
- A. SELECT * FROM my table WITH TIME_TRAVEL (OFFSET => -60*30);
- B. SELECT * FROM my_table AT (OFFSET => -60*30);
- C. SELECT * FROM my table PRIOR TO STATEMENT '8e5d0ca9-005e-44e6-b858-a8f5b37c5726';
- D. SELECT * FROM TIME_TRAVEL ('MY_TABLE', 2021-01-01 07:00:00);
- E. SELECT * FROM my_table AT (TIMESTAMP => '2021-01-01 07:00:00' :: timestamp);
- F. SELECT * FROM my_table BEFORE (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726');
Answer: C,E,F
Explanation:
According to the AT | BEFORE documentation, the AT or BEFORE clause is used for Snowflake Time Travel, which allows you to query historical data from a table based on a specific point in the past. The clause can use one of the following parameters to pinpoint the exact historical data you wish to access:
* TIMESTAMP: Specifies an exact date and time to use for Time Travel.
* OFFSET: Specifies the difference in seconds from the current time to use for Time Travel.
* STATEMENT: Specifies the query ID of a statement to use as the reference point for Time Travel.
Therefore, the queries that will allow the user to view the historical data that was in the table before the query was executed are:
* B. SELECT * FROM my_table AT (TIMESTAMP => '2021-01-01 07:00:00' :: timestamp); This query uses the TIMESTAMP parameter to specify a point in time that is before the query execution time of 07:01.
* D. SELECT * FROM my table PRIOR TO STATEMENT '8e5d0ca9-005e-44e6-b858-a8f5b37c5726'; This query uses the PRIOR TO STATEMENT keyword and the STATEMENT parameter to specify a point in time that is immediately preceding the query execution time of 07:01.
* F. SELECT * FROM my_table BEFORE (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726'); This query uses the BEFORE keyword and the STATEMENT parameter to specify a point in time that is immediately preceding the query execution time of 07:01.
The other queries are incorrect because:
* A. SELECT * FROM my table WITH TIME_TRAVEL (OFFSET => -60*30); This query uses the OFFSET parameter to specify a point in time that is 30 minutes before the current time, which is 07:30. This is after the query execution time of 07:01, so it will not show the historical data before the query was executed.
* C. SELECT * FROM TIME_TRAVEL ('MY_TABLE', 2021-01-01 07:00:00); This query is not valid syntax for Time Travel. The TIME_TRAVEL function does not exist in Snowflake. The correct syntax is to use the AT or BEFORE clause after the table name in the FROM clause.
* E. SELECT * FROM my_table AT (OFFSET => -60*30); This query uses the AT keyword and the OFFSET parameter to specify a point in time that is 30 minutes before the current time, which is 07:30. This is equal to the query execution time of 07:01, so it will not show the historical data before the query was executed. The AT keyword specifies that the request is inclusive of any changes made by a statement or transaction with timestamp equal to the specified parameter. To exclude the changes made by the query, the BEFORE keyword should be used instead.
NEW QUESTION # 28
Which statement allows this user to access this Snowflake account from a specific IP address (192.168.1.100) while blocking their access from anywhere else?
- A. CREATE NETWORK POLICY ADMIN_POLICY
ALLOWED IP LIST = ('192.168.1.100')
BLOCKED_IP_LIST = ('0.0.0.0/0');
ALTER USER ABC SET NETWORK_POLICY = 'ADMIN_POLICY'; - B. CREATE NETWORK POLICY ADMIN POLICY
ALLOWED_IP_LIST = ('192.168.1.100');
ALTER ROLE ACCOUNTADMIN SET NETWORK_POLICY = 'ADMIN_POLICY'; - C. CREATE NETWORK POLICY ADMIN_POLICY
ALLOWED_IP_LIST = ('192.168.1.100');
ALTER USER ABC SET NETWORK_POLICY = 'ADMIN_POLICY';
User ABC is the only user with an ACCOUNTADMIN role. - D. CREATE OR REPLACE NETWORK POLICY ADMIN_POLICY
ALLOWED_IP_LIST = ('192.168. 1. 100/0') ;
ALTER USER ABC SET NETWORK_POLICY = 'ADMIN_POLICY';
Answer: A
Explanation:
Option C creates a network policy that allows only the IP address 192.168.1.100 and blocks all other IP addresses using the CIDR notation 0.0.0.0/01. It then applies the network policy to the user ABC, who has the ACCOUNTADMIN role. This ensures that only this user can access the Snowflake account from the specified IP address, while blocking their access from anywhere else. Option A does not block any other IP addresses, option B applies the network policy to the role instead of the user, and option D uses an invalid CIDR notation.
NEW QUESTION # 29
The ACCOUNTADMIN of Account 123 works with Snowflake Support to set up a Data Exchange. After the exchange is populated with listings from other Snowflake accounts, what roles in Account 123 are allowed to request and get data?
- A. Any role that the listing provider has designated as authorized
- B. Only the ACCOUNTADMIN role, and no other roles
- C. Any role with IMPORT SHARE and CREATE DATABASE privileges
- D. Any role with USAGE privilege on the Data Exchange
Answer: D
Explanation:
To request and get data from a Data Exchange, the role in Account 123 must have the USAGE privilege on the Data Exchange object. This privilege allows the role to view the listings and request access to the data. According to the Snowflake documentation, "To view the listings in a data exchange, a role must have the USAGE privilege on the data exchange object. To request access to a listing, a role must have the USAGE privilege on the data exchange object and the IMPORT SHARE privilege on the account." The other options are either incorrect or not sufficient to request and get data from a Data Exchange. Option A is incorrect, as the ACCOUNTADMIN role is not the only role that can request and get data, as long as other roles have the necessary privileges. Option C is incorrect, as the IMPORT SHARE and CREATE DATABASE privileges are not required to request and get data, but only to create a database from a share after the access is granted. Option D is incorrect, as the listing provider does not designate the authorized roles in Account 123, but only approves or denies the requests from Account 123.
NEW QUESTION # 30
Which type of listing in the Snowflake Marketplace can be added and queried immediately?
- A. Personalized listing
- B. Regional listing
- C. Monetized listing
- D. Standard listing
Answer: D
Explanation:
Explanation
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 andqueried 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.
NEW QUESTION # 31
A company has implemented Snowflake replication between two Snowflake accounts, both of which are running on a Snowflake Enterprise edition. The replication is for the database APP_DB containing only one schema, APP_SCHEMA. The company's Time Travel retention policy is currently set for 30 days for both accounts. An Administrator has been asked to extend the Time Travel retention policy to 60 days on the secondary database only.
How can this requirement be met?
- A. Set the data retention policy on the schemas in the secondary database to 60 days.
- B. Set the data retention policy on the primary database to 60 days.
- C. Set the data retention policy on the secondary database to 60 days.
- D. Set the data retention policy on the primary database to 30 days and the schemas to 60 days.
Answer: C
Explanation:
Explanation
According to the Replication considerations documentation, the Time Travel retention period for a secondary database can be different from the primary database. The retention period can be set at the database, schema, or table level using the DATA_RETENTION_TIME_IN_DAYS parameter. Therefore, to extend the Time Travel retention policy to 60 days on the secondary database only, the best option is to set the data retention policy on the secondary database to 60 days using the ALTER DATABASE command. The other options are incorrect because:
*B. Setting the data retention policy on the schemas in the secondary database to 60 days will not affect the database-level retention period, which will remain at 30 days. The most specific setting overrides the more general ones, so the schema-level setting will apply to the tables in the schema, but not to the database itself.
*C. Setting the data retention policy on the primary database to 30 days and the schemas to 60 days will not affect the secondary database, which will have its own retention period. The replication process does not copy the retention period settings from the primary to the secondary database, so they can be configured independently.
*D. Setting the data retention policy on the primary database to 60 days will not affect the secondary database, which will have its own retention period. The replication process does not copy the retention period settings from the primary to the secondary database, so they can be configured independently.
NEW QUESTION # 32
An Administrator has been asked to support the company's application team need to build a loyalty program for its customers. The customer table contains Personal Identifiable Information (PII), and the application team's role is DEVELOPER.
CREATE TABLE customer_data (
customer_first_name string,
customer_last_name string,
customer_address string,
customer_email string,
... some other columns,
);
The application team would like to access the customer data, but the email field must be obfuscated.
How can the Administrator protect the sensitive information, while maintaining the usability of the data?
- A. Create a view on the customer_data table to eliminate the email column by omitting it from the SELECT clause. Grant the role DEVELOPER access to the view.
- B. Create a separate table for all the non-Pll columns and grant the role DEVELOPER access to the new table.
- C. Use the CURRENT_ROLE context function to integrate with a masking policy on the fields that contain sensitive data.
- D. Use the CURRENT_ROLE and CURRENT_USER context functions to integrate with a secure view and filter the sensitive data.
Answer: C
NEW QUESTION # 33
A Snowflake Administrator needs to persist all virtual warehouse configurations for auditing and backups. Given a table already exists with the following schema:
Table Name : VWH_META
Column 1 : SNAPSHOT_TIME TIMESTAMP_NTZ
Column 2 : CONFIG VARIANT
Which commands should be executed to persist the warehouse data at the time of execution in JSON format in the table VWH META?
- A. 1. SHOW WAREHOUSES;
2. INSERT INTO VWH META
SELECT CURRENT TIMESTAMP (),
FROM TABLE (RESULT_SCAN (LAST_QUERY_ID(1) ) ) ; - B. 1. SHOW WAREHOUSES;
2. INSERT INTO VWH_META
SELECT CURRENT_TIMESTAMP (),
OBJECT CONSTRUCT (*)
FROM TABLE (RESULT_SCAN (LAST_QUERY_ID ())); - C. 1. SHOW WAREHOUSES;
2. INSERT INTO VWH META
SELECT CURRENT TIMESTAMP (), *
FROM TABLE (RESULT_SCAN (LAST_QUERY_ID ())) ; - D. 1. SHOW WAREHOUSES;
2. INSERT INTO VWH META
SELECT CURRENT TIMESTAMP (), *
FROM TABLE (RESULT_SCAN (SELECT
LAST QUERY ID(-1)));
Answer: B
Explanation:
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.
NEW QUESTION # 34
An Administrator loads data into a staging table every day. Once loaded, users from several different departments perform transformations on the data and load it into different production tables.
How should the staging table be created and used to MINIMIZE storage costs and MAXIMIZE performance?
- A. Create it as a transient table with a retention time of 0 days.
- B. Create it as an external table, which will not incur Time Travel costs.
- C. Create it as a temporary table with a retention time of 0 days.
- D. Create it as a permanent table with a retention time of 0 days.
Answer: A
Explanation:
Explanation
According to the Snowflake documentation1, a transient table is a type of table that does not support Time Travel or Fail-safe, which means that it does not incur any storage costs for maintaining historical versions of the data or backups for disaster recovery. A transient table can be dropped at any time, and the data is not recoverable. A transient table can also have a retention time of 0 days, which means that the data is deleted immediately after the table is dropped or truncated. Therefore, creating the staging table as a transient table with a retention time of 0 days can minimize the storage costs and maximize the performance, as the data is only loaded and transformed once, and then deleted after the production tables are populated. Option A is incorrect because creating the staging table as an external table, which references data files stored in a cloud storage location, can incur additional costs and complexity for data transfer and synchronization, and may not provide the best performance for data loading and transformation. Option C is incorrect because creating the staging table as a temporary table, which is automatically dropped when the session ends or the user logs out, can cause data loss or inconsistency if the session is interrupted or terminated before the production tables are populated. Option D is incorrect because creating the staging table as a permanent table, which supports Time Travel and Fail-safe, can incur additional storage costs for maintaining historical versions of the data and backups for disaster recovery, and may not provide the best performance for data loading and transformation.
NEW QUESTION # 35
A Snowflake Administrator is investigating why a query is not re-using the persisted result cache.
The Administrator found the two relevant queries from the SNOWFLAKE. ACCOUNT_USAGE.
QUERY_HISTORY view:
Why is the second query re-scanning micro-partitions instead of using the first query's persisted result cache?
- A. The second query includes a CURRENT_DATE () function.
- B. The second query includes a CURRENT_TIMESTAMP () function.
- C. The queries are executed with two different roles.
- D. The queries are executed with two different virtual warehouses.
Answer: D
Explanation:
Explanation
The inclusion of the CURRENT_TIMESTAMP() function in the second query prevents it from re-using the first query's persisted result cache because this function makes each execution unique due to the constantly changing timestamp. According to the Snowflake documentation, "The query does not include non-reusable functions, which return different results for successive runs of the same query. UUID_STRING, RANDOM, and RANDSTR are good examples of non-reusable functions." The CURRENT_TIMESTAMP() function is another example of a non-reusable function, as it returns the current date and time at the start of query execution, which varies for each run. Therefore, the second query is not identical to the first query, and the result cache is not reused. The other options are either incorrect or irrelevant to the question. Option B is incorrect, as the CURRENT_DATE() function is a reusable function, as it returns the same value for all queries executed within the same day. Option C is irrelevant, as the virtual warehouse used to execute the query does not affect the result cache reuse. Option D is also irrelevant, as the role used to execute the query does not affect the result cache reuse, as long as the role has the necessary access privileges for all the tables used in the query.
NEW QUESTION # 36
A virtual warehouse report_wh is configured with AUTO_RESUME=TRUE and AUTO_SUSPEND=300. A user has been granted the role accountant.
An application with the accountant role should use this warehouse to run financial reports, and should keep track of compute credits used by the warehouse.
What minimal privileges on the warehouse should be granted to the role to meet the requirements for the application? (Select TWO).
- A. MONITOR
- B. OPERATE
- C. USAGE
- D. MODIFY
- E. OWNERSHIP
Answer: A,C
Explanation:
Explanation
According to the Snowflake documentation1, the MONITOR privilege on a warehouse grants the ability to view the warehouse usage and performance metrics, such as the number of credits consumed, the average and maximum run time, and the number of queries executed. The USAGE privilege on a warehouse grants the ability to use the warehouse to execute queries and load data. Therefore, the minimal privileges on the warehouse that should be granted to the role to meet the requirements for the application are MONITOR and USAGE. Option A is incorrect because the OPERATE privilege on a warehouse grants the ability to start, stop, resume, and suspend the warehouse, which is not required for the application. Option B is incorrect because the MODIFY privilege on a warehouse grants the ability to alter the warehouse properties, such as the size, auto-suspend, and auto-resume settings, which is not required for the application. Option E is incorrect because the OWNERSHIP privilege on a warehouse grants the ability to drop the warehouse, grant or revoke privileges on the warehouse, and transfer the ownership to another role, which is not required for the application.
NEW QUESTION # 37
What access control policy will be put into place when future grants are assigned to both database and schema objects?
- A. An access policy combining both the database object and the schema object will be used, with the most restrictive policy taking precedence.
- B. An access policy combining both the database object and the schema object will be used, with the most permissive policy taking precedence.
- C. Schema privileges will take precedence over database privileges.
- D. Database privileges will take precedence over schema privileges.
Answer: C
Explanation:
Explanation
When future grants are defined on the same object type for a database and a schema in the same database, the schema-level grants take precedence over the database level grants, and the database level grants are ignored4.
This behavior applies to privileges on future objects granted to one role or different roles4. Future grants allow defining an initial set of privileges to grant on new (i.e. future) objects of a certain type in a database or a schema3. As soon as the new objects are created inside the database or schema, the predefined set of privileges are assigned to the object automatically without any manual intervention3.
NEW QUESTION # 38
A data provider wants to share data from multiple databases with a data consumer account.
How can this be accomplished?
- A. The data provider needs to create a secure view and must grant the REFERENCE_USAGE privilege on the database where the secure view is created.
- B. The data provider needs to create a secure view and grant the REFERENCE_USAGE privilege on each database referenced by the secure view.
- C. The data provider needs to create a secure view and grant the USAGE privilege on each database referenced by the secure view.
- D. The data provider needs to create a secure view and grant the REFERENCE_USAGE privilege to a database role to include objects from multiple databases in a share
Answer: B
Explanation:
Explanation
Option B is the correct answer because it follows the steps described in the Snowflake documentation for sharing data from multiple databases using secure views. The data provider needs to grant the REFERENCE_USAGE privilege on each database that contains objects referenced by the secure view, and the USAGE privilege only on the database where the secure view is created. Option A is incorrect because it grants the USAGE privilege instead of the REFERENCE_USAGE privilege. Option C is incorrect because it grants the REFERENCE_USAGE privilege to a database role, which is not supported. Option D is incorrect because it grants the REFERENCE_USAGE privilege on the wrong database.
NEW QUESTION # 39
......
Exam Sure Pass Snowflake Certification with ADA-C01 exam questions: https://pass4sure.updatedumps.com/Snowflake/ADA-C01-updated-exam-dumps.html