[2025] Pass your ADA-C01 exam with this 100% Free ADA-C01 Braindump [Q11-Q33]

Q11. What are benefits of creating and maintaining resource monitors in Snowflake? (Select THREE).

The cost of running a resource monitor is only 10% of a credit, per day of operation.

Multiple resource monitors can be applied to a single virtual warehouse.

Resource monitors add no additional load to virtual warehouse compute.

Multiple triggers can be configured across various virtual warehouse thresholds.

Resource monitor governance is tightly controlled and monitors can only be created by the ACCOUNTADMIN role or users with the CREATE MONITOR privilege.

Resource monitors can be applied to more than one virtual warehouse.

Q12. 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).

OPERATE

MODIFY

MONITOR

USAGE

OWNERSHIP

Q13. 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?

The DEVELOPER_ROLE needs to be granted to SYSADMIN before user PTORRES will be able to use the role.

The new role can only take effect after USERADMIN has logged out.

USERADMIN needs to explicitly grant the DEVELOPER_ROLE to the new USER.

The new role will only take effect once the identity provider has synchronized by way of SCIM with the Snowflake account.

Q14. A large international company with many operating regions requires data to be shared bi-directionally among all offices (head office to regional offices and regional offices among themselves). This company is a Snowflake account holder with European operations deployed in Microsoft Azure (single region) while North American regional offices are using AWS (single region) as their deployment cloud. This setup is required to comply with Personal Identifiable Information (PII) regulations in some of the European countries. The corporate head office is in Europe.
How can this data be shared bi-directionally, while MINIMIZING costs?

Use data replication everywhere to reduce costs associated with same-region sharing.

Use the PUT command to move files to an Amazon S3 bucket and Azure Blobs, and use an external file management application to move files within the corporate VPC.

Move all the Snowflake accounts to a single region, and implement data sharing.

Use bi-directional data sharing among offices in the same region and replication among offices across the continents.

Q15. 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).

EXECUTE TASK on the task

OWNERSHIP on the task

OPERATE on the task

USAGE on the database and schema containing the task

OWNERSHIP on the database and schema containing the task

Q16. 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?

The second query includes a CURRENT_TIMESTAMP () function.

The second query includes a CURRENT_DATE () function.

The queries are executed with two different virtual warehouses.

The queries are executed with two different roles.

Q17. 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).

SELECT * FROM my table WITH TIME_TRAVEL (OFFSET => -60*30);

SELECT * FROM my_table AT (TIMESTAMP => ‘2021-01-01 07:00:00’ :: timestamp);

SELECT * FROM TIME_TRAVEL (‘MY_TABLE’, 2021-01-01 07:00:00);

SELECT * FROM my table PRIOR TO STATEMENT ‘8e5d0ca9-005e-44e6-b858-a8f5b37c5726’;

SELECT * FROM my_table AT (OFFSET => -60*30);

SELECT * FROM my_table BEFORE (STATEMENT => ‘8e5d0ca9-005e-44e6-b858-a8f5b37c5726’);

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.

Q18. A Snowflake Administrator needs to retrieve the list of the schemas deleted within the last two days from the DB1 database.
Which of the following will achieve this?

SHOW SCHEMAS IN DATABASE DB1;

SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.SCHEMATA;

SELECT * FROM DB1.INFORMATION_SCHEMA.SCHEMATA;

SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASES;

Q19. What Snowflake capabilities are commonly used in rollback scenarios? (Select TWO).

SELECT SYSTEM$CANCEL_QUERY(‘problematic_query_id’);

CREATE TABLE prd_table_bkp CLONE prd_table BEFORE (STATEMENT => ‘problematic_query_id’);

CREATE TABLE prd_table_bkp AS SELECT * FROM TABLE(RESULT_SCAN(‘problematic_query_id’));

ALTER TABLE prd_table SWAP WITH prd_table_bkp;

Contact Snowflake Support to retrieve Fail-safe data.

Q20. What SCIM integration types are supported in Snowflake? (Select THREE).

Amazon Web Services (AWS)

Google Cloud Platform (GCP)

Okta

Custom

Azure Active Directory (Azure AD)

Duo Security Provisioning Connector

Q21. 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?

There should be another 2XL warehouse deployed to handle a portion of the load queries.

The 2XL warehouse should be changed to 4XL to increase the number of threads available for parallel load queries.

The warehouse should be kept as a SMALL or XSMALL and configured as a multi-cluster warehouse to handle the parallel load queries.

The INSERTS should be converted to several tables to avoid contention on large tables that slows down query processing.

Q22. Which masking policy will mask a column whenever it is queried through a view owned by a role named MASKED_VIEW_ROLE?

create or replace masking policy maskstring as (val string) returns string -> case when is_role_in_session (‘MASKED_VIEW_ROLE’) then ‘ ** else val end;
*,

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;
** ‘

create or replace masking policy maskstring as (val string) returns string -> case when invoker_role() in (‘MASKED_VIEW_ROLE’) then else val end;
‘ **

create or replace masking policy maskString as (val string) returns string -> case when current_role() in (‘MASKED_VIEW_ROLE’) then ‘ ********* ‘ else val end;

Q23. 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?

The STORE_ID should be numeric.

The table is not big enough to take advantage of the clustering key.

Sales across stores are not uniformly distributed.

The cardinality of the stores to transaction count ratio is too low to use the STORE_ID as a clustering key.

Q24. 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?

To save costs on warehouse shutdowns and startups for different queries

To save costs by running the warehouse as little as possible

To keep the data cache warm to support good performance of similar queries

To keep the query result cache warm for good performance on repeated queries

Q25. A company has many users in the role ANALYST who routinely query Snowflake through a reporting tool. The Administrator has noticed that the ANALYST users keep two small clusters busy all of the time, and occasionally they need three or four clusters of that size.
Based on this scenario, how should the Administrator set up a virtual warehouse to MOST efficiently support this group of users?

Create a multi-cluster warehouse with MIN_CLUSTERS set to 1. Give MANAGE privileges to the ANALYST role so this group can start and stop the warehouse, and increase the number of clusters as needed.

Create a multi-cluster warehouse with MIN_CLUSTERS set to 2. Set the warehouse to auto-resume and auto-suspend, and give USAGE privileges to the ANALYST role. Allow the warehouse to auto-scale.

Create a standard X-Large warehouse, which is equivalent to four small clusters. Set the warehouse to auto-resume and auto-suspend, and give USAGE privileges to the ANALYST role.

Create four virtual warehouses (sized Small through XL) and set them to auto-suspend and auto-resume. Have users in the ANALYST role select the appropriate warehouse based on how many queries are being run.

Q26. 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?

1. SHOW WAREHOUSES;
2. INSERT INTO VWH META
SELECT CURRENT TIMESTAMP (),
FROM TABLE (RESULT_SCAN (LAST_QUERY_ID(1) ) ) ;

1. SHOW WAREHOUSES;
2. INSERT INTO VWH META
SELECT CURRENT TIMESTAMP (), *
FROM TABLE (RESULT_SCAN (LAST_QUERY_ID ())) ;

1. SHOW WAREHOUSES;
2. INSERT INTO VWH_META
SELECT CURRENT_TIMESTAMP (),
OBJECT CONSTRUCT (*)
FROM TABLE (RESULT_SCAN (LAST_QUERY_ID ()));

1. SHOW WAREHOUSES;
2. INSERT INTO VWH META
SELECT CURRENT TIMESTAMP (), *
FROM TABLE (RESULT_SCAN (SELECT
LAST QUERY ID(-1)));

Q27. A Snowflake user runs a complex SQL query on a dedicated virtual warehouse that reads a large amount of data from micro-partitions. The same user wants to run another query that uses the same data set.
Which action would provide optimal performance for the second SQL query?

Assign additional clusters to the virtual warehouse.

Increase the STATEMENT_TIMEOUT_IN_SECONDS parameter in the session.

Prevent the virtual warehouse from suspending between the running of the first and secondqueries.

Use the RESULT_SCAN function to post-process the output of the first query.

Q28. An Administrator needs to implement an access control mechanism across an organization. The organization users access sensitive customer data that comes from different regions and needs to be accessible for Analysts who work in these regions. Some Analysts need very specific access control depending on their functional roles in the organization. Following Snowflake recommended practice, how should these requirements be met? (Select TWO).

Implement views on top of base tables that exclude or mask sensitive data.

Implement row access policies and Dynamic Data Masking policies.

Include masking rules as part of data ingestion and transformation pipelines.

Use a third-party tool to share the data.

Use zero-copy cloning to replicate the database schema and provide access as needed.

Q29. Which actions are considered breaking changes to data that is shared with consumers in the Snowflake Marketplace? (Select TWO).

Dropping a column from a table

Deleting data from a table

Unpublishing the data listing

Renaming a table

Adding region availability to the listing

Q30. 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?

Set the data retention policy on the secondary database to 60 days.

Set the data retention policy on the schemas in the secondary database to 60 days.

Set the data retention policy on the primary database to 30 days and the schemas to 60 days.

Set the data retention policy on the primary database to 60 days.

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

SHOW REGIONS;

SHOW USERS;

SHOW ORGANIZATION ACCOUNTS;

GRANT ROLE ORGADMIN TO USER <username>;

SELECT SYSTEM$GLOBAL_ACCOUNT_SET_PARAMETER (

Q32. A requirement has been identified to allow members of a corporate Data Product team to bring in data sets from the Snowflake Marketplace. The members of this team use the role DP_TEAM.
What grant statements must the ACCOUNTADMIN execute in order for the DP TEAM role to import and work with data from the Marketplace?

grant marketplace in account to role dp_team;
grant create database from share to role dp_team;

grant usage on snowflake_marketplace to role dp_team;
grant create database on account to role dp_team;

grant imported privileges on account to role dp_team;
grant create database on account to role dp_team;

grant import share on account to role dp_team;
grant create database on account to role dp_team;

Q33. What are characteristics of Dynamic Data Masking? (Select TWO).

A masking policy that is currently set on a table can be dropped.

A single masking policy can be applied to columns in different tables.

A masking policy can be applied to the VALUE column of an external table.

The role that creates the masking policy will always see unmasked data in query results.

A single masking policy can be applied to columns with different data types.