Snowflake ARA-C01 Exam Practice Test Instant Access

Question 1

A company has built a data pipeline using Snowpipe to ingest files from an Amazon S3 bucket. Snowpipe is configured to load data into staging database tables. Then a task runs to load the data from the staging database tables into the reporting database tables.

The company is satisfied with the availability of the data in the reporting database tables, but the reporting tables are not pruning effectively. Currently, a size 4X-Large virtual warehouse is being used to query all of the tables in the reporting database.

What step can be taken to improve the pruning of the reporting tables?

AEliminate the use of Snowpipe and load the files into internal stages using PUT commands.

BIncrease the size of the virtual warehouse to a size 5X-Large.

CUse an ORDER BY <cluster_key (s) > command to load the reporting tables.

DCreate larger files for Snowpipe to ingest and ensure the staging frequency does not exceed 1 minute.

Answer : C

Effective pruning in Snowflake relies on the organization of data within micro-partitions. By using an ORDER BY clause with clustering keys when loading data into the reporting tables, Snowflake can better organize the data within micro-partitions. This organization allows Snowflake to skip over irrelevant micro-partitions during a query, thus improving query performance and reducing the amount of data scanned12.

Reference =

* Snowflake Documentation on micro-partitions and data clustering2

* Community article on recognizing unsatisfactory pruning and improving it1

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

This command will fail because while the user has USAGE permission on db2 and schema2 through Role B, and can create a view in schema2, they do not have SELECT permission on db1.schemal.table1 with Role B. Since Role A, which has SELECT permission on db1.schemal.table1, is not the currently active role when the view v2 is being created in db2.schema2, the user does not have the necessary permissions to read from db1.schemal.table1 to create the view. Snowflake's security model requires that the active role have all necessary permissions to execute the command.

Question 3

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.

Reference

Snowflake Documentation on Cloning Considerations1.

Question 4

An Architect is using SnowCD to investigate a connectivity issue.

Which system function will provide a list of endpoints that the network must be able to access to use a specific Snowflake account, leveraging private connectivity?

ASYSTEMSALLOWLIST ()

BSYSTEMSGET_PRIVATELINK

CSYSTEMSAUTHORIZE_PRIVATELINK

DSYSTEMSALLOWLIST_PRIVATELINK ()

Answer : B

The SYSTEM$GET_PRIVATELINK function is used to retrieve the list of Snowflake service endpoints that need to be accessible when configuring private connectivity (such as AWS PrivateLink or Azure Private Link) for a Snowflake account. The function returns information necessary for setting up the networking infrastructure that allows secure and private access to Snowflake without using the public internet. SnowCD can then be used to verify connectivity to these endpoints.

Question 5

An Architect is designing a data lake with Snowflake. The company has structured, semi-structured, and unstructured data. The company wants to save the data inside the data lake within the Snowflake

system. The company is planning on sharing data among its corporate branches using Snowflake data sharing.

What should be considered when sharing the unstructured data within Snowflake?

AA pre-signed URL should be used to save the unstructured data into Snowflake in order to share data over secure views, with no time limit for the URL.

BA scoped URL should be used to save the unstructured data into Snowflake in order to share data over secure views, with a 24-hour time limit for the URL.

CA file URL should be used to save the unstructured data into Snowflake in order to share data over secure views, with a 7-day time limit for the URL.

DA file URL should be used to save the unstructured data into Snowflake in order to share data over secure views, with the 'expiration_time' argument defined for the URL time limit.

Answer : B

When sharing unstructured data within Snowflake, using a scoped URL is recommended. Scoped URLs provide temporary access to staged files without granting privileges to the stage itself, enhancing security. The URL expires when the persisted query result period ends, which is currently set to 24 hours. This approach is suitable for sharing unstructured data over secure views within Snowflake's data sharing framework.

Question 6

In a managed access schema, what are characteristics of the roles that can manage object privileges? (Select TWO).

AUsers with the SYSADMIN role can grant object privileges in a managed access schema.

BUsers with the SECURITYADMIN role or higher, can grant object privileges in a managed access schema.

CUsers who are database owners can grant object privileges in a managed access schema.

DUsers who are schema owners can grant object privileges in a managed access schema.

EUsers who are object owners can grant object privileges in a managed access schema.

Answer : B, D

In a managed access schema, the privilege management is centralized with the schema owner, who has the authority to grant object privileges within the schema. Additionally, the SECURITYADMIN role has the capability to manage object grants globally, which includes within managed access schemas. Other roles, such as SYSADMIN or database owners, do not inherently have this privilege unless explicitly granted.

Question 7

When loading data into a table that captures the load time in a column with a default value of either CURRENT_TIME () or CURRENT_TIMESTAMP () what will occur?

AAll rows loaded using a specific COPY statement will have varying timestamps based on when the rows were inserted.

BAny rows loaded using a specific COPY statement will have varying timestamps based on when the rows were read from the source.

CAny rows loaded using a specific COPY statement will have varying timestamps based on when the rows were created in the source.

DAll rows loaded using a specific COPY statement will have the same timestamp value.

Answer : D

When using the COPY command to load data into Snowflake, if a column has a default value set to CURRENT_TIME() or CURRENT_TIMESTAMP(), all rows loaded by that specific COPY command will have the same timestamp. This is because the default value for the timestamp is evaluated at the start of the COPY operation, and that same value is applied to all rows loaded by that operation.

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