March Sale Special Limited Time 70% Discount Offer - Ends in 0d 00h 00m 00s - Coupon code: 70percent

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

Demo: 46 questions
Total 155 questions

SnowPro Advanced: Architect Certification Exam Questions and Answers

Question 1

An Architect is designing a file ingestion recovery solution. The project will use an internal named stage for file storage. Currently, in the case of an ingestion failure, the Operations team must manually download the failed file and check for errors.

Which downloading method should the Architect recommend that requires the LEAST amount of operational overhead?

Options:

A.

Use the Snowflake Connector for Python, connect to remote storage and download the file.

B.

Use the get command in SnowSQL to retrieve the file.

C.

Use the get command in Snowsight to retrieve the file.

D.

Use the Snowflake API endpoint and download the file.

Question 2

Which feature provides the capability to define an alternate cluster key for a table with an existing cluster key?

Options:

A.

External table

B.

Materialized view

C.

Search optimization

D.

Result cache

Question 3

A company needs to share its product catalog data with one of its partners. The product catalog data is stored in two database tables: product_category, and product_details. Both tables can be joined by the product_id column. Data access should be governed, and only the partner should have access to the records.

The partner is not a Snowflake customer. The partner uses Amazon S3 for cloud storage.

Which design will be the MOST cost-effective and secure, while using the required Snowflake features?

Options:

A.

Use Secure Data Sharing with an S3 bucket as a destination.

B.

Publish product_category and product_details data sets on the Snowflake Marketplace.

C.

Create a database user for the partner and give them access to the required data sets.

D.

Create a reader account for the partner and share the data sets as secure views.

Question 4

Which query will identify the specific days and virtual warehouses that would benefit from a multi-cluster warehouse to improve the performance of a particular workload?

Options:

A.

A screen shot of a computer Description automatically generated

B.

A white background with black text Description automatically generated

C.

A white background with black text Description automatically generated

D.

A close up of a message Description automatically generated

Question 5

A company has several sites in different regions from which the company wants to ingest data.

Which of the following will enable this type of data ingestion?

Options:

A.

The company must have a Snowflake account in each cloud region to be able to ingest data to that account.

B.

The company must replicate data between Snowflake accounts.

C.

The company should provision a reader account to each site and ingest the data through the reader accounts.

D.

The company should use a storage integration for the external stage.

Question 6

The following DDL command was used to create a task based on a stream:

Assuming MY_WH is set to auto_suspend – 60 and used exclusively for this task, which statement is true?

Options:

A.

The warehouse MY_WH will be made active every five minutes to check the stream.

B.

The warehouse MY_WH will only be active when there are results in the stream.

C.

The warehouse MY_WH will never suspend.

D.

The warehouse MY_WH will automatically resize to accommodate the size of the stream.

Question 7

A company has a table with that has corrupted data, named Data. The company wants to recover the data as it was 5 minutes ago using cloning and Time Travel.

What command will accomplish this?

Options:

A.

CREATE CLONE TABLE Recover_Data FROM Data AT(OFFSET => -60*5);

B.

CREATE CLONE Recover_Data FROM Data AT(OFFSET => -60*5);

C.

CREATE TABLE Recover_Data CLONE Data AT(OFFSET => -60*5);

D.

CREATE TABLE Recover Data CLONE Data AT(TIME => -60*5);

Question 8

What is a characteristic of Role-Based Access Control (RBAC) as used in Snowflake?

Options:

A.

Privileges can be granted at the database level and can be inherited by all underlying objects.

B.

A user can use a "super-user" access along with securityadmin to bypass authorization checks and access all databases, schemas, and underlying objects.

C.

A user can create managed access schemas to support future grants and ensure only schema owners can grant privileges to other roles.

D.

A user can create managed access schemas to support current and future grants and ensure only object owners can grant privileges to other roles.

Question 9

An Architect clones a database and all of its objects, including tasks. After the cloning, the tasks stop running.

Why is this occurring?

Options:

A.

Tasks cannot be cloned.

B.

The objects that the tasks reference are not fully qualified.

C.

Cloned tasks are suspended by default and must be manually resumed.

D.

The Architect has insufficient privileges to alter tasks on the cloned database.

Question 10

Company A has recently acquired company B. The Snowflake deployment for company B is located in the Azure West Europe region.

As part of the integration process, an Architect has been asked to consolidate company B's sales data into company A's Snowflake account which is located in the AWS us-east-1 region.

How can this requirement be met?

Options:

A.

Replicate the sales data from company B's Snowflake account into company A's Snowflake account using cross-region data replication within Snowflake. Configure a direct share from company B's account to company A's account.

B.

Export the sales data from company B's Snowflake account as CSV files, and transfer the files to company A's Snowflake account. Import the data using Snowflake's data loading capabilities.

C.

Migrate company B's Snowflake deployment to the same region as company A's Snowflake deployment, ensuring data locality. Then perform a direct database-to-database merge of the sales data.

D.

Build a custom data pipeline using Azure Data Factory or a similar tool to extract the sales data from company B's Snowflake account. Transform the data, then load it into company A's Snowflake account.

Question 11

An Architect would like to save quarter-end financial results for the previous six years.

Which Snowflake feature can the Architect use to accomplish this?

Options:

A.

Search optimization service

B.

Materialized view

C.

Time Travel

D.

Zero-copy cloning

E.

Secure views

Question 12

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?

Options:

A.

A 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.

B.

A 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.

C.

A 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.

D.

A 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.

Question 13

Database DB1 has schema S1 which has one table, T1.

DB1 --> S1 --> T1

The retention period of EG1 is set to 10 days.

The retention period of s: is set to 20 days.

The retention period of t: Is set to 30 days.

The user runs the following command:

Drop Database DB1;

What will the Time Travel retention period be for T1?

Options:

A.

10 days

B.

20 days

C.

30 days

D.

37 days

Question 14

A Data Engineer is designing a near real-time ingestion pipeline for a retail company to ingest event logs into Snowflake to derive insights. A Snowflake Architect is asked to define security best practices to configure access control privileges for the data load for auto-ingest to Snowpipe.

What are the MINIMUM object privileges required for the Snowpipe user to execute Snowpipe?

Options:

A.

OWNERSHIP on the named pipe, USAGE on the named stage, target database, and schema, and INSERT and SELECT on the target table

B.

OWNERSHIP on the named pipe, USAGE and READ on the named stage, USAGE on the target database and schema, and INSERT end SELECT on the target table

C.

CREATE on the named pipe, USAGE and READ on the named stage, USAGE on the target database and schema, and INSERT end SELECT on the target table

D.

USAGE on the named pipe, named stage, target database, and schema, and INSERT and SELECT on the target table

Question 15

An Architect needs to grant a group of ORDER_ADMIN users the ability to clean old data in an ORDERS table (deleting all records older than 5 years), without granting any privileges on the table. The group’s manager (ORDER_MANAGER) has full DELETE privileges on the table.

How can the ORDER_ADMIN role be enabled to perform this data cleanup, without needing the DELETE privilege held by the ORDER_MANAGER role?

Options:

A.

Create a stored procedure that runs with caller’s rights, including the appropriate "> 5 years" business logic, and grant USAGE on this procedure to ORDER_ADMIN. The ORDER_MANAGER role owns the procedure.

B.

Create a stored procedure that can be run using both caller’s and owner’s rights (allowing the user to specify which rights are used during execution), and grant USAGE on this procedure to ORDER_ADMIN. The ORDER_MANAGER role owns the procedure.

C.

Create a stored procedure that runs with owner’s rights, including the appropriate "> 5 years" business logic, and grant USAGE on this procedure to ORDER_ADMIN. The ORDER_MANAGER role owns the procedure.

D.

This scenario would actually not be possible in Snowflake – any user performing a DELETE on a table requires the DELETE privilege to be granted to the role they are using.

Question 16

A table, EMP_ TBL has three records as shown:

The following variables are set for the session:

Which SELECT statements will retrieve all three records? (Select TWO).

Options:

A.

Select * FROM Stbl_ref WHERE Scol_ref IN ('Name1','Nam2','Name3');

B.

SELECT * FROM EMP_TBL WHERE identifier(Scol_ref) IN ('Namel','Name2', 'Name3');

C.

SELECT * FROM identifier WHERE NAME IN ($var1, $var2, $var3);

D.

SELECT * FROM identifier($tbl_ref) WHERE ID IN Cvarl','var2','var3');

E.

SELECT * FROM $tb1_ref WHERE $col_ref IN ($var1, Svar2, Svar3);

Question 17

Which technique will efficiently ingest and consume semi-structured data for Snowflake data lake workloads?

Options:

A.

IDEF1X

B.

Schema-on-write

C.

Schema-on-read

D.

Information schema

Question 18

A large manufacturing company runs a dozen individual Snowflake accounts across its business divisions. The company wants to increase the level of data sharing to support supply chain optimizations and increase its purchasing leverage with multiple vendors.

The company’s Snowflake Architects need to design a solution that would allow the business divisions to decide what to share, while minimizing the level of effort spent on configuration and management. Most of the company divisions use Snowflake accounts in the same cloud deployments with a few exceptions for European-based divisions.

According to Snowflake recommended best practice, how should these requirements be met?

Options:

A.

Migrate the European accounts in the global region and manage shares in a connected graph architecture. Deploy a Data Exchange.

B.

Deploy a Private Data Exchange in combination with data shares for the European accounts.

C.

Deploy to the Snowflake Marketplace making sure that invoker_share() is used in all secure views.

D.

Deploy a Private Data Exchange and use replication to allow European data shares in the Exchange.

Question 19

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

Options:

A.

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

B.

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

C.

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

D.

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

E.

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

Question 20

A company wants to Integrate its main enterprise identity provider with federated authentication with Snowflake.

The authentication integration has been configured and roles have been created in Snowflake. However, the users are not automatically appearing in Snowflake when created and their group membership is not reflected in their assigned rotes.

How can the missing functionality be enabled with the LEAST amount of operational overhead?

Options:

A.

OAuth must be configured between the identity provider and Snowflake. Then the authorization server must be configured with the right mapping of users and roles.

B.

OAuth must be configured between the identity provider and Snowflake. Then the authorization server must be configured with the right mapping of users, and the resource server must be configured with the right mapping of role assignment.

C.

SCIM must be enabled between the identity provider and Snowflake. Once both are synchronized through SCIM, their groups will get created as group accounts in Snowflake and the proper roles can be granted.

D.

SCIM must be enabled between the identity provider and Snowflake. Once both are synchronized through SCIM. users will automatically get created and their group membership will be reflected as roles In Snowflake.

Question 21

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?

Options:

A.

use database db1;

use schema schemal;

create view v1 as select * from db2.schema2.table2;

B.

use database db2;

use schema schema2;

create view v2 as select * from dbl.schemal. tablel;

C.

use database db2;

use schema schema2;

select * from db1.schemal.tablel union select * from table2;

D.

use database db1;

use schema schemal;

select * from db2.schema2.table2;

Question 22

What are characteristics of the use of transactions in Snowflake? (Select TWO).

Options:

A.

Explicit transactions can contain DDL, DML, and query statements.

B.

The autocommit setting can be changed inside a stored procedure.

C.

A transaction can be started explicitly by executing a begin work statement and end explicitly by executing a commit work statement.

D.

A transaction can be started explicitly by executing a begin transaction statement and end explicitly by executing an end transaction statement.

E.

Explicit transactions should contain only DML statements and query statements. All DDL statements implicitly commit active transactions.

Question 23

When using the Snowflake Connector for Kafka, what data formats are supported for the messages? (Choose two.)

Options:

A.

CSV

B.

XML

C.

Avro

D.

JSON

E.

Parquet

Question 24

A company is using Snowflake in Azure in the Netherlands. The company analyst team also has data in JSON format that is stored in an Amazon S3 bucket in the AWS Singapore region that the team wants to analyze.

The Architect has been given the following requirements:

1. Provide access to frequently changing data

2. Keep egress costs to a minimum

3. Maintain low latency

How can these requirements be met with the LEAST amount of operational overhead?

Options:

A.

Use a materialized view on top of an external table against the S3 bucket in AWS Singapore.

B.

Use an external table against the S3 bucket in AWS Singapore and copy the data into transient tables.

C.

Copy the data between providers from S3 to Azure Blob storage to collocate, then use Snowpipe for data ingestion.

D.

Use AWS Transfer Family to replicate data between the S3 bucket in AWS Singapore and an Azure Netherlands Blob storage, then use an external table against the Blob storage.

Question 25

A company is storing large numbers of small JSON files (ranging from 1-4 bytes) that are received from IoT devices and sent to a cloud provider. In any given hour, 100,000 files are added to the cloud provider.

What is the MOST cost-effective way to bring this data into a Snowflake table?

Options:

A.

An external table

B.

A pipe

C.

A stream

D.

A copy command at regular intervals