Snowflake ARA-C01 Exam Practice Test Instant Access

Question 1

What actions are permitted when using the Snowflake SQL REST API? (Select TWO).

AThe use of a GET command

BThe use of a PUT command

CThe use of a ROLLBACK command

DThe use of a CALL command to a stored procedure which returns a table

ESubmitting multiple SQL statements in a single call

Answer : D, E

Question 2

Role A has the following permissions:

. USAGE on db1

. USAGE and CREATE VIEW on schemal in db1

. SELECT on tablel in schemal

Role B has the following permissions:

. USAGE on db2

. USAGE and CREATE VIEW on schema2 in db2

. SELECT on table2 in schema2

A user has Role A set as the primary role and Role B as a secondary role.

What command will fail for this user?

Ause database db1;
use schema schemal;
create view v1 as select * from db2.schema2.table2;

Buse database db2;
use schema schema2;
create view v2 as select * from dbl.schemal. tablel;

Cuse database db2;
use schema schema2;
select * from db1.schemal.tablel union select * from table2;

Duse database db1;
use schema schemal;
select * from db2.schema2.table2;

Answer : B

Question 3

A Developer is having a performance issue with a Snowflake query. The query receives up to 10 different values for one parameter and then performs an aggregation over the majority of a fact table. It then

joins against a smaller dimension table. This parameter value is selected by the different query users when they execute it during business hours. Both the fact and dimension tables are loaded with new data in an overnight import process.

On a Small or Medium-sized virtual warehouse, the query performs slowly. Performance is acceptable on a size Large or bigger warehouse. However, there is no budget to increase costs. The Developer

needs a recommendation that does not increase compute costs to run this query.

What should the Architect recommend?

ACreate a task that will run the 10 different variations of the query corresponding to the 10 different parameters before the users come in to work. The query results will then be cached and ready to respond quickly when the users re-issue the query.

BCreate a task that will run the 10 different variations of the query corresponding to the 10 different parameters before the users come in to work. The task will be scheduled to align with the users' working hours in order to allow the warehouse cache to be used.

CEnable the search optimization service on the table. When the users execute the query, the search optimization service will automatically adjust the query execution plan based on the frequently-used parameters.

DCreate a dedicated size Large warehouse for this particular set of queries. Create a new role that has USAGE permission on this warehouse and has the appropriate read permissions over the fact and dimension tables. Have users switch to this role and use this warehouse when they want to access this data.

Answer : C

Enabling the search optimization service on the table can improve the performance of queries that have selective filtering criteria, which seems to be the case here. This service optimizes the execution of queries by creating a persistent data structure called a search access path, which allows some micro-partitions to be skipped during the scanning process. This can significantly speed up query performance without increasing compute costs1.

* Snowflake Documentation on Search Optimization Service1.

Question 4

A company is designing its serving layer for data that is in cloud storage. Multiple terabytes of the data will be used for reporting. Some data does not have a clear use case but could be useful for experimental analysis. This experimentation data changes frequently and is sometimes wiped out and replaced completely in a few days.

The company wants to centralize access control, provide a single point of connection for the end-users, and maintain data governance.

What solution meets these requirements while MINIMIZING costs, administrative effort, and development overhead?

AImport the data used for reporting into a Snowflake schema with native tables. Then create external tables pointing to the cloud storage folders used for the experimentation data. Then create two different roles with grants to the different datasets to match the different user personas, and grant these roles to the corresponding users.

BImport all the data in cloud storage to be used for reporting into a Snowflake schema with native tables. Then create a role that has access to this schema and manage access to the data through that role.

CImport all the data in cloud storage to be used for reporting into a Snowflake schema with native tables. Then create two different roles with grants to the different datasets to match the different user personas, and grant these roles to the corresponding users.

DImport the data used for reporting into a Snowflake schema with native tables. Then create views that have SELECT commands pointing to the cloud storage files for the experimentation data. Then create two different roles to match the different user personas, and grant these roles to the corresponding users.

Answer : A

The most cost-effective and administratively efficient solution is to use a combination of native and external tables. Native tables for reporting data ensure performance and governance, while external tables allow for flexibility with frequently changing experimental data. Creating roles with specific grants to datasets aligns with the principle of least privilege, centralizing access control and simplifying user management12.

* Snowflake Documentation on Optimizing Cost1.

* Snowflake Documentation on Controlling Cost2.

Question 5

Which Snowflake architecture recommendation needs multiple Snowflake accounts for implementation?

AEnable a disaster recovery strategy across multiple cloud providers.

BCreate external stages pointing to cloud providers and regions other than the region hosting the Snowflake account.

CEnable zero-copy cloning among the development, test, and production environments.

DEnable separation of the development, test, and production environments.

Answer : D

The Snowflake architecture recommendation that necessitates multiple Snowflake accounts for implementation is the separation of development, test, and production environments. This approach, known as Account per Tenant (APT), isolates tenants into separate Snowflake accounts, ensuring dedicated resources and security isolation12.

* Snowflake's white paper on ''Design Patterns for Building Multi-Tenant Applications on Snowflake'' discusses the APT model and its requirement for separate Snowflake accounts for each tenant1.

* Snowflake Documentation on Secure Data Sharing, which mentions the possibility of sharing data across multiple accounts3.

Question 6

An Architect needs to design a solution for building environments for development, test, and pre-production, all located in a single Snowflake account. The environments should be based on production data.

Which solution would be MOST cost-effective and performant?

AUse zero-copy cloning into transient tables.

BUse zero-copy cloning into permanent tables.

CUse CREATE TABLE ... AS SELECT (CTAS) statements.

DUse a Snowflake task to trigger a stored procedure to copy data.

Answer : A

Zero-copy cloning is a feature in Snowflake that allows for the creation of a clone of a database, schema, or table without duplicating any data, which is cost-effective as it saves on storage costs. Transient tables are temporary and do not incur storage costs for the time they are not accessed, making them a cost-effective option for development, test, and pre-production environments that do not require the durability of permanent tables123.

* Snowflake Documentation on Zero-Copy Cloning3.

* Articles discussing the cost-effectiveness and performance benefits of zero-copy cloning12.

Question 7

An Architect is implementing a CI/CD process. When attempting to clone a table from a production to a development environment, the cloning operation fails.

What could be causing this to happen?

AThe table is transient.

BThe table has a masking policy.

CThe retention time for the table is set to zero.

DTables cannot be cloned from a higher environment to a lower environment.

Answer : B

Cloning a table with a masking policy can cause the cloning operation to fail because the masking policy is not automatically cloned with the table. This is due to the fact that the masking policy is considered a separate object with its own set of privileges1.

Snowflake Documentation on Cloning Considerations1.

Snowflake ARA-C01 SnowPro Advanced: Architect Certification Exam Practice Test