[Apr-2024] ADA-C01 Free Sample Questions to Practice One Year Update [Q22-Q37]

[Apr-2024] ADA-C01 Free Sample Questions to Practice One Year Update

Download ADA-C01 exam with Snowflake ADA-C01 Real Exam Questions

NEW QUESTION # 22
.
Company A uses Snowflake to manage audio files of call recordings. Company A hired Company B, who also uses Snowflake, to transcribe the audio files for further analysis.
Company A's Administrator created a share.
What object should be added to the share to allow Company B access to the files?

• A. A secure view with a column for the stage name and a column for the file path.
• B. A secure view with a column for pre-signed URLs.
• C. A secure view with a column for file URLs.
• D. A secure view with a column for METADATA$FILENAME.

Answer: B

Explanation:
Explanation
According to the Snowflake documentation1, pre-signed URLs are required to access external files in a share.
A secure view can be used to generate pre-signed URLs for the audio files stored in an external stage and expose them to the consumer account. Option A is incorrect because file URLs alone are not sufficient to access external files in a share. Option C is incorrect because METADATA$FILENAME only returns the file name, not the full path or URL. Option D is incorrect because the stage name and file path are not enough to generate pre-signed URLs.

NEW QUESTION # 23
A Snowflake Administrator wants to create a virtual warehouse that supports several dashboards, issuing various queries on the same database.
For this warehouse, why should the Administrator consider setting AUTO_SUSPEND to 0 or NULL?

• A. To save costs on warehouse shutdowns and startups for different queries
• B. To save costs by running the warehouse as little as possible
• C. To keep the query result cache warm for good performance on repeated queries
• D. To keep the data cache warm to support good performance of similar queries

Answer: D

Explanation:
Explanation
According to the Snowflake documentation1, the AUTO_SUSPEND property specifies the number of seconds of inactivity after which a warehouse is automatically suspended. If the property is set to 0 or NULL, the warehouse never suspends automatically. For a warehouse that supports several dashboards, issuing various queries on the same database, setting AUTO_SUSPEND to 0 or NULL can help to keep the data cache warm, which means that the data used by the queries is already loaded into the warehouse memory and does not need to be fetched from the storage layer. This can improve the performance of similar queries that access the same data. Option A is incorrect because setting AUTO_SUSPEND to 0 or NULL does not save costs on warehouse shutdowns and startups, but rather increases the costs by keeping the warehouse running continuously. Option B is incorrect because setting AUTO_SUSPEND to 0 or NULL does not run the warehouse as little as possible, but rather runs the warehouse as much as possible. Option D is incorrect because setting AUTO_SUSPEND to 0 or NULL does not affect the query result cache, which is a separate cache that stores the results of previous queries for a period of time. The query result cache is not dependent on the warehouse state, but on the query criteria2.

NEW QUESTION # 24
An Administrator has a user who needs to be able to suspend and resume a task based on the current virtual warehouse load, but this user should not be able to modify the task or start a new run.
What privileges should be granted to the user to meet these requirements? (Select TWO).

• A. OPERATE on the task
• B. EXECUTE TASK on the task
• C. OWNERSHIP on the task
• D. OWNERSHIP on the database and schema containing the task
• E. USAGE on the database and schema containing the task

Answer: A,E

Explanation:
Explanation
The user needs the OPERATE privilege on the task to suspend and resume it, and the USAGE privilege on the database and schema containing the task to access it1. The EXECUTE TASK privilege is not required for suspending and resuming a task, only for triggering a new run1. The OWNERSHIP privilege on the task or the database and schema would allow the user to modify or drop the task, which is not desired.

NEW QUESTION # 25
A team is provisioning new lower environments from the production database using cloning. All production objects and references reside in the database, and do not have external references.
What set of object references needs to be re-pointed before granting access for usage?

• A. Sequences, storage integrations, views, secure views, and materialized views
• B. There are no object references that need to be re-pointed
• C. Sequences, views, secure views, and materialized views
• D. Sequences, views, and secure views

Answer: A

Explanation:
Explanation
According to the Snowflake documentation1, when an object in a schema is cloned, any future grants defined for this object type in the schema are applied to the cloned object unless the COPY GRANTS option is specified in the CREATE statement for the clone operation. However, some objects may still reference the source object or external objects after cloning, which may cause issues with access or functionality. These objects include:
*Sequences: If a table column references a sequence that generates default values, the cloned table may reference the source or cloned sequence, depending on where the sequence is defined. To avoid conflicts, the sequence reference should be re-pointed to the desired sequence using the ALTER TABLE command2.
*Storage integrations: If a stage or a table references a storage integration, the cloned object may still reference the source storage integration, which may not be accessible or valid in the new environment. To avoid errors, the storage integration reference should be re-pointed to the desired storage integration using the ALTER STAGE or ALTER TABLE command34.
*Views, secure views, and materialized views: If a view references another view or table, the cloned view may still reference the source object, which may not be accessible or valid in the new environment. To avoid errors, the view reference should be re-pointed to the desired object using the CREATE OR REPLACE VIEW command5.
1: Cloning Considerations | Snowflake Documentation 2: [ALTER TABLE | Snowflake Documentation] 3:
[ALTER STAGE | Snowflake Documentation] 4: [ALTER TABLE | Snowflake Documentation] 5: [CREATE VIEW | Snowflake Documentation]

NEW QUESTION # 26
The following commands were executed:
Grant usage on database PROD to role PROD_ANALYST;
Grant usage on database PROD to role PROD_SUPERVISOR;
Grant ALL PRIVILEGES on schema PROD. WORKING to role PROD_ANALYST;
Grant ALL PRIVILEGES on schema PROD. WORKING to role PROD_SUPERVISOR;
Grant role PROD ANALYST to user A;
Grant role PROD SUPERVISOR to user B;
What authority does each user have on the WORKING schema?

• A. Only user B can create tables, because all privileges were transferred to PROD_SUPERVISOR.
• B. All existing tables in schema PROD. WORKING will be visible to both users.
• C. Tables created by either user A or user B will be visible to both users.
• D. Both user A and user B can create tables in the PROD. WORKING schema.

Answer: D

NEW QUESTION # 27
A team is provisioning new lower environments from the production database using cloning. All production objects and references reside in the database, and do not have external references.
What set of object references needs to be re-pointed before granting access for usage?

• A. Sequences, storage integrations, views, secure views, and materialized views
• B. There are no object references that need to be re-pointed
• C. Sequences, views, secure views, and materialized views
• D. Sequences, views, and secure views

Answer: A

Explanation:
According to the Snowflake documentation1, when an object in a schema is cloned, any future grants defined for this object type in the schema are applied to the cloned object unless the COPY GRANTS option is specified in the CREATE statement for the clone operation. However, some objects may still reference the source object or external objects after cloning, which may cause issues with access or functionality. These objects include:
* Sequences: If a table column references a sequence that generates default values, the cloned table may reference the source or cloned sequence, depending on where the sequence is defined. To avoid conflicts, the sequence reference should be re-pointed to the desired sequence using the ALTER TABLE command2.
* Storage integrations: If a stage or a table references a storage integration, the cloned object may still reference the source storage integration, which may not be accessible or valid in the new environment. To avoid errors, the storage integration reference should be re-pointed to the desired storage integration using the ALTER STAGE or ALTER TABLE command34.
* Views, secure views, and materialized views: If a view references another view or table, the cloned view may still reference the source object, which may not be accessible or valid in the new environment. To avoid errors, the view reference should be re-pointed to the desired object using the CREATE OR REPLACE VIEW command5.
1: Cloning Considerations | Snowflake Documentation 2: [ALTER TABLE | Snowflake Documentation] 3: [ALTER STAGE | Snowflake Documentation] 4: [ALTER TABLE | Snowflake Documentation] 5: [CREATE VIEW | Snowflake Documentation]

NEW QUESTION # 28
Which Snowflake objects can be managed using SCIM integration? (Select TWO).

• A. Users
• B. Shares
• C. Stages
• D. Roles
• E. Warehouses

Answer: A,D

Explanation:
Explanation
A SCIM security integration allows the automated management of user identities and groups (i.e. roles) by creating an interface between Snowflake and a third-party Identity Provider (IdP)1. Snowflake supports SCIM integration with Okta, Azure, and custom SCIM clients2. SCIM integration does not support managing other Snowflake objects, such as stages, warehouses, or shares3. Therefore, the answer is B. Users and D. Roles.

NEW QUESTION # 29
A company's Snowflake account has multiple roles. Each role should have access only to data that resides in the given role's specific region.
When creating a row access policy, which code snippet below will provide privileges to the role ALL_ACCESS_ROLE to see all rows regardless of region, while the other roles can only see rows for their own regions?

• A. create or replace row access policy region policy as (region_value varchar) returns boolean ->
  'ALL_ACCESS_ROLE' = current_role ()
  or exists (
  select 1 from entitlement_table
  where role = current_role ()
  and region = region_value
  )
• B. create or replace row access policy region policy as (region_value varchar) returns boolean ->
  'ALL ACCESS_ROLE' = current_role ()
  and exists (
  select 1 from entitlement_table
  where role = current_role ()
  and region = region_value
  )
• C. create or replace row access policy region policy as (region_value varchar) returns boolean ->
  'ALL ACCESS ROLE' = current_role ()
  )
• D. create or replace row access policy region policy as (region_value varchar) returns boolean -> exists ( select 1 from entitlement_table where role = current_role () and region = region_value )

Answer: A

Explanation:
This code snippet will create a row access policy that returns true if the current role is ALL_ACCESS_ROLE or if the current role matches the region value in the entitlement_table. This means that the ALL_ACCESS_ROLE can see all rows regardless of region, while the other roles can only see rows for their own regions. According to the Snowflake documentation, the CURRENT_ROLE context function returns the name of the current role for the session. The EXISTS function returns true if the subquery returns any rows. The OR operator returns true if either operand is true. Therefore, this code snippet satisfies the requirements of the question.

NEW QUESTION # 30
In general, the monthly billing for database replication is proportional to which variables? (Select TWO).

• A. The frequency of the secondary database refreshes from the primary database
• B. The number and size of warehouses defined in the primary account
• C. The number of times data moves across regions and/or cloud service providers between the primary and secondary database accounts
• D. The frequency of changes to the primary database as a result of data loading or DML operations
• E. The amount of table data in the primary database that changes as a result of data loading or DML operations

Answer: D,E

Explanation:
Snowflake charges for database replication based on two categories: data transfer and compute resources1. Data transfer costs depend on the amount of data that is transferred from the primary database to the secondary database across regions and/or cloud service providers2. Compute resource costs depend on the use of Snowflake-provided compute resources to copy data between accounts across regions1. Both data transfer and compute resource costs are proportional to the frequency and amount of changes to the primary database as a result of data loading or DML operations3.
Therefore, the answer is A and B. The other options are not directly related to the replication billing, as the frequency of secondary database refreshes does not affect the amount of data transferred or copied4, and the number and size of warehouses defined in the primary account do not affect the replication process5.

NEW QUESTION # 31
A Snowflake customer is experiencing higher costs than anticipated while migrating their data warehouse workloads from on-premises to Snowflake. The migration workloads have been deployed on a single warehouse and are characterized by a large number of small INSERTs rather than bulk loading of large extracts. That single warehouse has been configured as a single cluster, 2XL because there are many parallel INSERTs that are scheduled during nightly loads.
How can the Administrator reduce the costs, while minimizing the overall load times, for migrating data warehouse history?

• A. The 2XL warehouse should be changed to 4XL to increase the number of threads available for parallel load queries.
• B. The warehouse should be kept as a SMALL or XSMALL and configured as a multi-cluster warehouse to handle the parallel load queries.
• C. The INSERTS should be converted to several tables to avoid contention on large tables that slows down query processing.
• D. There should be another 2XL warehouse deployed to handle a portion of the load queries.

Answer: B

Explanation:
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.

NEW QUESTION # 32
How should an Administrator configure a Snowflake account to use AWS PrivateLink?

• A. Contact Snowflake Support.
• B. Create CNAME records in the DNS.
• C. Use SnowCD to evaluate the network connection.
• D. Block public access to Snowflake.

Answer: A

Explanation:
Explanation
To configure a Snowflake account to use AWS PrivateLink, the Administrator needs to create CNAME records in the DNS that point to the private endpoints provided by Snowflake. This allows the clients to connect to Snowflake using the same URL as before, but with private connectivity. According to the Snowflake documentation, "After you have created the VPC endpoints, Snowflake provides you with a list of private endpoints for your account. You must create CNAME records in your DNS that point to these private endpoints. The CNAME records must use the same hostnames as the original Snowflake URLs for your account." The other options are either incorrect or not sufficient to configure AWS PrivateLink. Option B is not necessary, as the Administrator can enable AWS PrivateLink using the SYSTEM$AUTHORIZE_PRIVATELINK function1. Option C is not recommended, as it may prevent some data traffic from reaching Snowflake, such as large result sets stored on AWS S32. Option D is not related to AWS PrivateLink, but to Snowflake Connectivity Diagnostic (SnowCD), which is a tool for diagnosing network issues between clients and Snowflake3.

NEW QUESTION # 33
Which masking policy will mask a column whenever it is queried through a view owned by a role named MASKED_VIEW_ROLE?

• A. create or replace masking policy maskString as (val string) returns string -> case when array_contains ('MASKED_VIEW_ROLE' :: variant, parse_json (current_available_roles ())) then '* else val end;
  ** '
• B. create or replace masking policy maskString as (val string) returns string -> case when current_role() in ('MASKED_VIEW_ROLE') then ' ********* ' else val end;
• C. create or replace masking policy maskstring as (val string) returns string -> case when invoker_role() in ('MASKED_VIEW_ROLE') then else val end;
  ' **
• D. create or replace masking policy maskstring as (val string) returns string -> case when is_role_in_session ('MASKED_VIEW_ROLE') then ' ** else val end;
  *,

Answer: D

Explanation:
A masking policy is a SQL expression that transforms the data in a column based on the role that queries the column1. The is_role_in_session function returns true if the specified role is in the current session2. Therefore, the masking policy in option A will mask the column data with asterisks whenever it is queried through a view owned by the MASKED_VIEW_ROLE3. The other options use different functions that do not check the ownership of the view, but rather the current role, the invoker role, or the available roles in the session45. These functions may not return the desired result if the role that owns the view is different from the role that queries the view.

NEW QUESTION # 34
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. Use the CURRENT_ROLE and CURRENT_USER context functions to integrate with a secure view and filter the sensitive data.
• C. Create a separate table for all the non-Pll columns and grant the role DEVELOPER access to the new table.
• D. Use the CURRENT_ROLE context function to integrate with a masking policy on the fields that contain sensitive data.

Answer: D

NEW QUESTION # 35
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. Database privileges will take precedence over schema privileges.
• C. Schema privileges will take precedence over database privileges.
• D. An access policy combining both the database object and the schema object will be used, with the most permissive policy taking precedence.

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 # 36
A retailer uses a TRANSACTIONS table (100M rows, 1.2 TB) that has been clustered by the STORE_ID column (varchar(50)). The vast majority of analyses on this table are grouped by STORE_ID to look at store performance.
There are 1000 stores operated by the retailer but most sales come from only 20 stores. The Administrator notes that most queries are currently experiencing poor pruning, with large amounts of bytes processed by even simple queries.
Why is this occurring?

• A. The table is not big enough to take advantage of the clustering key.
• B. Sales across stores are not uniformly distributed.
• C. The cardinality of the stores to transaction count ratio is too low to use the STORE_ID as a clustering key.
• D. The STORE_ID should be numeric.

Answer: B

Explanation:
According to the Snowflake documentation1, clustering keys are most effective when the data is evenly distributed across the key values. If the data is skewed, such as in this case where most sales come from only 20 stores out of 1000, then the micro-partitions will not be well-clustered and the pruning will be poor. This means that more bytes will be scanned by queries, even if they filter by STORE_ID. Option A is incorrect because the data type of the clustering key does not affect the pruning. Option B is incorrect because the table is large enough to benefit from clustering, if the data was more balanced. Option D is incorrect because the cardinality of the clustering key is not relevant for pruning, as long as the key values are distinct.
1: Considerations for Choosing Clustering for a Table | Snowflake Documentation

NEW QUESTION # 37
......

Real exam questions are provided for SnowPro Advanced: Administrator tests, which can make sure you 100% pass: https://dumpstorrent.prep4surereview.com/ADA-C01-latest-braindumps.html