[Jan 03, 2025] Pass ADA-C01 Review Guide, Reliable ADA-C01 Test Engine [Q20-Q39]

[Jan 03, 2025] Pass ADA-C01 Review Guide, Reliable ADA-C01 Test Engine

ADA-C01 Test Engine Practice Test Questions, Exam Dumps

NEW QUESTION # 20
A user with the proper role issues the following commands when setting up and activating network policies:
CREATE OR REPLACE NETWORK POLICY foo_policy
ALLOWED_IP_LIST = ( '1.1.1.0/24', '2.2.2.0/24' , '3.3. 3. 0/24' )
BLOCKED IP LIST = ( '1.1.1.1')
COMMENT = 'Account level policy';
ALTER ACCOUNT SET NETWORK_POLICY=FOO_POLICY;
CREATE OR REPLACE NETWORK POLICY bar_policy
ALLOWED_IP_LIST = ('3.3.3.0/24')
BLOCKED IP LIST = ('3.3.3.10')
COMMENT = 'user level policy';
ALTER USER userl SET NETWORK_POLICY=BAR_POLICY;
Afterwards, user1 attempts to log in to Snowflake from IP address 3.3.3.10.
Will the login be successful?

• A. No, because 3.3.3.10 is found in the BLOCKED_IP_LIST of bar_policy.
• B. Yes, because 3.3.3.10 is found in the ALLOWED_IP_LIST of bar_policy.
• C. No, because 3.3.3.10 is not found in the ALLOWED_IP_LIST of foo_policy.
• D. Yes, because 3.3.3.10 is found in the ALLOWED_IP_LIST of foo_policy.

Answer: A

Explanation:
According to the Snowflake documentation1, network policies are a feature that allows restricting access to your account based on user IP address. A network policy can be applied to an account, a user, or a security integration, and can specify a list of allowed IP addresses and a list of blocked IP addresses. If there are network policies applied to more than one of these, the most specific network policy overrides more general network policies. In this case, the user1 has a network policy (bar_policy) applied to them, which overrides the account-level network policy (foo_policy). The bar_policy allows access only from the IP range 3.3.3.0/24, and blocks access from the IP address 3.3.3.10. Therefore, the user1 will not be able to log in to Snowflake from IP address 3.3.3.10, as it is found in the BLOCKED_IP_LIST of bar_policy. Option A is incorrect because the ALLOWED_IP_LIST of bar_policy does not override the BLOCKED_IP_LIST of bar_policy. Option C is incorrect because the ALLOWED_IP_LIST of foo_policy does not apply to user1, as it is overridden by the user-level network policy. Option D is incorrect because the ALLOWED_IP_LIST of foo_policy does not matter, as it is overridden by the user-level network policy.

NEW QUESTION # 21
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. Sales across stores are not uniformly distributed.
• B. The cardinality of the stores to transaction count ratio is too low to use the STORE_ID as a clustering key.
• C. The table is not big enough to take advantage of the clustering key.
• D. The STORE_ID should be numeric.

Answer: A

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

Answer: C

NEW QUESTION # 23
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. Create views over the incoming shared database and apply the desired RBAC onto these views.
• C. Individually grant imported privileges onto the schema in the share.
• D. Apply RBAC directly onto the partner's shared secure views.

Answer: B

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 to future 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 # 24
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 -> 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 ()
  )
• C. 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
  )
• D. 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
  )

Answer: D

Explanation:
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 # 25
A Snowflake account is configured with SCIM provisioning for user accounts and has bi-directional synchronization for user identities. An Administrator with access to SECURITYADMIN uses the Snowflake UI to create a user by issuing the following commands:
use role USERADMIN;
create or replace role DEVELOPER_ROLE;
create user PTORRES PASSWORD = 'hello world!' MUST_CHANGE_PASSWORD = FALSE default_role = DEVELOPER_ROLE; The new user named PTORRES successfully logs in, but sees a default role of PUBLIC in the web UI. When attempted, the following command fails:
use DEVELOPER_ROLE;
Why does this command fail?

• A. The DEVELOPER_ROLE needs to be granted to SYSADMIN before user PTORRES will be able to use the role.
• B. The new role can only take effect after USERADMIN has logged out.
• C. USERADMIN needs to explicitly grant the DEVELOPER_ROLE to the new USER.
• D. The new role will only take effect once the identity provider has synchronized by way of SCIM with the Snowflake account.

Answer: C

Explanation:
According to the Snowflake documentation1, creating a user with a default role does not automatically grant that role to the user. The user must be explicitly granted the role by the role owner or a higher-level role. Therefore, the USERADMIN role, which created the DEVELOPER_ROLE, needs to explicitly grant the DEVELOPER_ROLE to the new user PTORRES using the GRANT ROLE command. Otherwise, the user PTORRES will not be able to use the DEVELOPER_ROLE and will see the default role of PUBLIC in the web UI. Option A is incorrect because the DEVELOPER_ROLE does not need to be granted to SYSADMIN before user PTORRES can use the role. Option B is incorrect because the new role can take effect immediately after it is created and granted to the user, and does not depend on the USERADMIN role logging out. Option D is incorrect because the new role will not be affected by the identity provider synchronization, as it is created and managed in Snowflake.

NEW QUESTION # 26
Which commands can be performed by a user with the ORGADMIN role but not the ACCOUNTADMIN role? (Select TWO).

• A. SELECT SYSTEM$GLOBAL_ACCOUNT_SET_PARAMETER (
  'ACCOUNT LOCATOR',
  'ENABLE ACCOUNT DATABASE_REPLICATION',
  'true'
  );
• B. GRANT ROLE ORGADMIN TO USER <username>;
• C. SHOW USERS;
• D. SHOW REGIONS;
• E. SHOW ORGANIZATION ACCOUNTS;

Answer: A,E

Explanation:
According to the Snowflake documentation1, the ORGADMIN role is a special system role that is responsible for managing operations at the organization level, such as creating and viewing accounts, enabling database replication, and setting global account parameters. The ACCOUNTADMIN role is a system role that is responsible for managing operations at the account level, such as creating and managing users, roles, warehouses, databases, and shares. Therefore, the commands that can be performed by the ORGADMIN role but not the ACCOUNTADMIN role are:
* SHOW ORGANIZATION ACCOUNTS: This command lists all the accounts in the organization and their properties, such as region, edition, and status2. The ACCOUNTADMIN role can only show the current account and its properties using the SHOW ACCOUNTS command3.
* SELECT SYSTEM$GLOBAL_ACCOUNT_SET_PARAMETER: This function sets a global account parameter for an account in the organization, such as enabling account database replication4. The ACCOUNTADMIN role can only set local account parameters using the ALTER ACCOUNT command.
Option A is incorrect because the SHOW REGIONS command can be executed by any role, not just the ORGADMIN role. Option B is incorrect because the SHOW USERS command can be executed by the ACCOUNTADMIN role, as well as any role that has been granted the MONITOR privilege on the account. Option D is incorrect because the GRANT ROLE ORGADMIN TO USER <username> command can be executed by the ACCOUNTADMIN role, as well as any role that has been granted the ORGADMIN role1.

NEW QUESTION # 27
What are the requirements when creating a new account within an organization in Snowflake? (Select TWO).

• A. The account name must be unique among all Snowflake customers.
• B. The account requires at least one ORGADMIN role within one of the organization's accounts.
• C. The account name must be unique within the organization.
• D. The account name is immutable and cannot be changed.
• E. The account name must be specified when the account is created.

Answer: C,E

Explanation:
Explanation
According to the CREATE ACCOUNT documentation, the account name must be specified when the account is created, and it must be unique within an organization, regardless of which Snowflake Region the account is in. The other options are incorrect because:
*The account does not require at least one ORGADMIN role within one of the organization's accounts. The account can be created by an organization administrator (i.e. a user with the ORGADMIN role) through the web interface or using SQL, but the new account does not inherit the ORGADMIN role from the existing account. The new account will have its own set of users, roles, databases, and warehouses.
*The account name is not immutable and can be changed. The account name can be modified by contacting Snowflake Support and requesting a name change. However, changing the account name may affect some features that depend on the account name, such as SSO or SCIM.
*The account name does not need to be unique among all Snowflake customers. The account name only needs to be unique within the organization, as the account URL also includes the region and cloud platform information. For example, two accounts with the same name can exist in different regions or cloud platforms, such as myaccount.us-east-1.snowflakecomputing.com and myaccount.eu-west-1.aws.snowflakecomputing.com.

NEW QUESTION # 28
What is a characteristic of Snowflake's transaction locking and concurrency modeling?

• A. Queries executed within a given transaction see that transaction's uncommitted changes.
• B. Transaction locking in Snowflake is enforced exclusively at the row and table levels.
• C. If two queries are concurrently executed against the same table, one of the two queries will be blocked until the other query completes.
• D. A deadlock cannot occur in Snowflake, since concurrently executed queries and DML statements do not block one another.

Answer: D

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

NEW QUESTION # 29
What role or roles should be used to properly create the object required to setup OAuth 2.0 integration?

• A. ACCOUNTADMIN and SYSADMIN
• B. ACCOUNTADMIN and SECURITYADMIN
• C. ACCOUNTADMIN only
• D. Any role with GRANT USAGE on SECURITY INTEGRATION

Answer: C

Explanation:
According to the Using OAuth 2.0 with Snowflake - Blog, only the ACCOUNTADMIN role can create and manage integrations, so an administrator must assume that role when creating a security integration for OAuth. The other roles do not have the necessary privileges to create the object required to setup OAuth 2.0 integration.

NEW QUESTION # 30
Which tasks can be performed by the ORGADMIN role? (Select THREE).

• A. Perform zero-copy cloning on account data.
• B. View usage information for all accounts in the organization.
• C. Create a reader account to share data with another organization.
• D. Create one or more accounts in the organization.
• E. View a list of all regions enabled for the organization.
• F. Create secure views on application tables within the organization.

Answer: B,D,E

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 # 31
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. OWNERSHIP on the task
• B. OWNERSHIP on the database and schema containing the task
• C. EXECUTE TASK on the task
• D. USAGE on the database and schema containing the task
• E. OPERATE on the task

Answer: D,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 # 32
A company enabled replication between accounts and is ready to replicate data across regions in the same cloud service provider.
The primary database object is : PROD_AWS_EAST. Location : AWS_EAST
The secondary database object is : PROD_AWS_WEST. Location : AWS_WEST
What command and account location is needed to refresh the data?

• A. Location : AWS_EAST
  Command : REFRESH DATABASE PROD_AWS_WEST REFRESH;
• B. Location : AWS_WEST
  Command : REFRESH DATABASE PROD_AWS WEST REFRESH;
• C. Location : AWS EAST
  Command: ALTER DATABASE PROD_AWS_WEST REFRESH;
• D. Location : AWS_WEST
  Command : ALTER DATABASE PROD AWS WEST REFRESH;

Answer: B

Explanation:
Explanation
The REFRESH DATABASE command is used to refresh a secondary database with the latest data and metadata from the primary database1. The command must be executed in the target account where the secondary database resides2. Therefore, the answer is A, as the location is AWS_WEST and the command is REFRESH DATABASE PROD_AWS_WEST REFRESH. The other options are incorrect because they either use the wrong location, the wrong command, or the wrong database name.

NEW QUESTION # 33
What is a characteristic of Snowflake's transaction locking and concurrency modeling?

• A. Queries executed within a given transaction see that transaction's uncommitted changes.
• B. Transaction locking in Snowflake is enforced exclusively at the row and table levels.
• C. If two queries are concurrently executed against the same table, one of the two queries will be blocked until the other query completes.
• D. A deadlock cannot occur in Snowflake, since concurrently executed queries and DML statements do not block one another.

Answer: D

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

NEW QUESTION # 34
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 DatabaseA. 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. USAGE on the schema DatabaseA clone
• C. USAGE on the database DatabaseA_clone. Schemal
• D. SELECT on all tables, and only secure views in DatabaseA_clone. Schemal
• E. SELECT on all tables, and only non-secure views in DatabaseA_clone. Schemal

Answer: A,E

Explanation:
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 # 35
What access control policy will be put into place when future grants are assigned to both database and schema objects?

• A. Database privileges will take precedence over schema privileges.
• B. An access policy combining both the database object and the schema object will be used, with the most permissive policy taking precedence.
• C. An access policy combining both the database object and the schema object will be used, with the most restrictive policy taking precedence.
• D. Schema privileges will take precedence over database privileges.

Answer: D

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
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 temporary 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 permanent table with a retention time of 0 days.
• D. Create it as a transient table with a retention time of 0 days.

Answer: D

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 # 37
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. Sequences, views, secure views, and materialized views
• C. Sequences, views, and secure views
• D. There are no object references that need to be re-pointed

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 # 38
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:
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 # 39
......

100% Free ADA-C01 Daily Practice Exam With 74 Questions: https://pass4sure.test4cram.com/ADA-C01_real-exam-dumps.html