Snowflake ARA-C01 Exam Prep Guide Prep guide for the ARA-C01 Exam [Q120-Q142]

Rate this post

Snowflake ARA-C01 Exam Prep Guide: Prep guide for the ARA-C01 Exam

2023 New Preparation Guide of Snowflake ARA-C01 Exam

The Snowflake ARA-C01 exam is a challenging test that requires the candidate to have a deep understanding of Snowflake’s cloud data platform and its various components. The exam tests the candidate’s ability to design and implement solutions that are scalable, high-performance, and cost-effective.

To be eligible to take the SnowPro Advanced Architect Certification exam, candidates must have a solid understanding of Snowflake data warehousing and data management concepts, as well as practical experience in designing and implementing Snowflake solutions. Candidates are also required to have completed the SnowPro Core Certification exam, which tests their foundational knowledge of Snowflake data warehousing and data management.

The Snowflake ARA-C01 exam is a certification exam designed for advanced architects who are experienced in implementing and operating Snowflake solutions. This exam is the highest level of certification offered by Snowflake and is intended to validate the skills and expertise of professionals who are responsible for designing, building, and managing complex Snowflake environments.

QUESTION 120
An Architect has chosen to separate their Snowflake Production and QA environments using two separate Snowflake accounts.
The QA account is intended to run and test changes on data and database objects before pushing those changes to the Production account. It is a requirement that all database objects and data in the QA account need to be an exact copy of the database objects, including privileges and data in the Production account on at least a nightly basis.
Which is the LEAST complex approach to use to populate the QA account with the Production account’s data and database objects on a nightly basis?

1) Create a share in the Production account for each database
2) Share access to the QA account as a Consumer
3) The QA account creates a database directly from each share
4) Create clones of those databases on a nightly basis
5) Run tests directly on those cloned databases

1) Create a stage in the Production account
2) Create a stage in the QA account that points to the same external object-storage location
3) Create a task that runs nightly to unload each table in the Production account into the stage
4) Use Snowpipe to populate the QA account

1) Enable replication for each database in the Production account
2) Create replica databases in the QA account
3) Create clones of the replica databases on a nightly basis
4) Run tests directly on those cloned databases

1) In the Production account, create an external function that connects into the QA account and returns all the data for one specific table
2) Run the external function as part of a stored procedure that loops through each table in the Production account and populates each table in the QA account

QUESTION 121
Below are the rest APIs provided by Snowpipe

insertFiles

insertReport

loadData

QUESTION 122
You have a very large table which is already clustered on columns that are used to retrieve data from the table by a business group. The base table data does not change much. Another business group came to you and requested for a relatively small subset of data from the table which they will query using complex aggregation logic. You know that querying with those columns will take a lot of time because the table is not clustered on those columns.
What is the most optimal solution that you will suggest to the business team?

CREATE A MATERIALIZED VIEW AND CLUSTER THE VIEW ON THOSE COLUMNS

CREATE A SECURE VIEW

CREATE A REGULAR VIEW

QUESTION 123
A company wants to deploy its Snowflake accounts inside its corporate network with no visibility on the internet. The company is using a VPN infrastructure and Virtual Desktop Infrastructure (VDI) for its Snowflake users. The company also wants to re-use the login credentials set up for the VDI to eliminate redundancy when managing logins.
What Snowflake functionality should be used to meet these requirements? (Choose two.)

Set up replication to allow users to connect from outside the company VPN.

Provision a unique company Tri-Secret Secure key.

Use private connectivity from a cloud provider.

Set up SSO for federated authentication.

Use a proxy Snowflake account outside the VPN, enabling client redirect for user logins.

QUESTION 124
Let’s say that you have two JSONs as below
1. {“stuId”:2000, “stuName”:”Amy”}
2. {“stuId”:2000,”stuCourse”:”Snowflake”}
How will you write a query that will check if stuId in JSON in #1 is also there in JSON in#2

with stu_demography as (select parse_json(column1) as src, src:stuId as ID from values(‘{“stuId”:2000, “stuName”:”Amy”}’)),

stu_course as (select parse_json(column1) as src, src:stuId as ID from values(‘{“stuId”:2000,”stuCourse”:”Snowflake”}’)) select case when stdemo.ID in(select ID from stu_course) then ‘True’ else ‘False’ end as result from stu_demography stdemo;

with stu_demography as (select parse_json(column1) as src, src[‘stuId’] as ID from values(‘{“stuId”:2000, “stuName”:”Amy”}’)), stu_course as (select parse_json(column1) as src, src[‘stuId’] as ID from values(‘{“stuId”:2000,”stuCourse”:”Snowflake”}’)) select case when stdemo.ID in(select ID from stu_course) then ‘True’ else ‘False’ end as result from stu_demography stdemo;

SELECT CONTAINS(‘{“stuId”:2000, “stuName”:”Amy”}’,'{“stuId”:2000,”stuCourse”:”Snowflake”}’);

with stu_demography as (select parse_json(column1) as src, src[‘STUID’] as ID from values(‘{“stuId”:2000, “stuName”:”Amy”}’)), stu_course as (select parse_json(column1) as src, src[‘stuId’] as ID from values(‘{“stuId”:2000,”stuCourse”:”Snowflake”}’)) select case when stdemo.ID in(select ID from stu_course) then ‘True’ else ‘False’ end as result from stu_demography stdemo;

QUESTION 125
You are a snowflake architect in an organization. The business team came to to deploy an use case which requires you to load some data which they can visualize through tableau. Everyday new data comes in and the old data is no longer required.
What type of table you will use in this case to optimize cost

TRANSIENT

TEMPORARY

PERMANENT

QUESTION 126
Files arrive in an external stage every 10 seconds from a proprietary system. The files range in size from 500 K to 3 MB. The data must be accessible by dashboards as soon as it arrives.
How can a Snowflake Architect meet this requirement with the LEAST amount of coding? (Choose two.)

Use Snowpipe with auto-ingest.

Use a COPY command with a task.

Use a materialized view on an external table.

Use the COPY INTO command.

Use a combination of a task and a stream.

QUESTION 127
You have a table named customer_table. You want to create another table as customer_table_other which will be same as customer_table with respect to schema and data.
What is the best option?

CREATE TABLE customer_table_other CLONE customer_table

CREATE TABLE customer_table_other AS SELECT * FROM customer_table

ALTER TABLE customer_table_other SWAP WITH customer_table

QUESTION 128
An Architect uses COPY INTO with the ON_ERROR=SKIP_FILE option to bulk load CSV files into a table called TABLEA, using its table stage. One file named file5.csv fails to load. The Architect fixes the file and re-loads it to the stage with the exact same file name it had previously.
Which commands should the Architect use to load only file5.csv file from the stage? (Choose two.)

COPY INTO tablea FROM @%tablea RETURN_FAILED_ONLY = TRUE;

COPY INTO tablea FROM @%tablea;

COPY INTO tablea FROM @%tablea FILES = (‘file5.csv’);

COPY INTO tablea FROM @%tablea FORCE = TRUE;

COPY INTO tablea FROM @%tablea NEW_FILES_ONLY = TRUE;

COPY INTO tablea FROM @%tablea MERGE = TRUE;

QUESTION 129
You have written a procedure and a function in snowflake.
How do you find your procedures and functions in snowflake?

SHOW PROCEDURES;

SHOW FUNCTIONS;

SHOW ALL PROCEDURES AND FUNCTIONS

SHOW PROCEDURES, FUNCTIONS

QUESTION 130
How do you validate the data that is unloaded using COPY INTO command

After unloading, load the data into a relational table and validate the rows

Load the data into a CSV file to validate the rows

Use validation_mode=’RETURN_ROWS’; with COPY command

QUESTION 131
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?

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

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

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

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

QUESTION 132
A DevOps team has a requirement for recovery of staging tables used in a complex set of data pipelines. The staging tables are all located in the same staging schem a. One of the requirements is to have online recovery of data on a rolling 7-day basis.
After setting up the DATA_RETENTION_TIME_IN_DAYS at the database level, certain tables remain unrecoverable past 1 day.
What would cause this to occur? (Choose two.)

The staging schema has not been setup for MANAGED ACCESS.

The DATA_RETENTION_TIME_IN_DAYS for the staging schema has been set to 1 day.

The tables exceed the 1 TB limit for data recovery.

The staging tables are of the TRANSIENT type.

The DevOps role should be granted ALLOW_RECOVERY privilege on the staging schema.

QUESTION 133
Out of the three query optimization techniques(search optimization, clustering and materialized view) which one does not have storage cost

Search Optimization Service

Materialized View

Clustering the Table

QUESTION 134
Which Snowflake data modeling approach is designed for BI queries?

3 NF

Star schema

Data Vault

Snowflake schema

QUESTION 135
What is a characteristic of loading data into Snowflake using the Snowflake Connector for Kafka?

The Connector only works in Snowflake regions that use AWS infrastructure.

The Connector works with all file formats, including text, JSON, Avro, Ore, Parquet, and XML.

The Connector creates and manages its own stage, file format, and pipe objects.

Loads using the Connector will have lower latency than Snowpipe and will ingest data in real time.

QUESTION 136
create or replace table result_scan_table as
select * from table(result_scan(last_query_id()));
Will the above query cost you any compute credits?

It will not cost any compute credit because this query is a metadata operation

It will cost compute credit because processing is required to insert the data into result_scan_table

It will depend on the warehouse chosen

QUESTION 137
Snowflake supports the following query performance optimizing methods

Caching techniques

Retrieving results of previous query from cache

B-tree type indexes

QUESTION 138
Which copy options are not supported by CREATE PIPE…AS COPY FROM command?

FILES = ( ‘file_name1’ [ , ‘file_name2’, … ] )

FORCE = TRUE | FALSE

ON_ERROR = ABORT_STATEMENT

VALIDATION_MODE = RETURN_n_ROWS | RETURN_ERRORS | RETURN_ALL_ERRORS

MATCH_BY_COLUMN_NAME = CASE_SENSITIVE | CASE_INSENSITIVE | NONE

QUESTION 139
How do you refresh a materialized view?

ALTER VIEW <MV_NAME> REFRESH

REFRESH MATERIALIZED VIEW <MV_NAME>

Materialized views are automatically refreshed by snowflake and does not require manual intervention

QUESTION 140
You have created a TASK in snowflake.
How will you resume it?

No need to resume, the creation operation automatically enables the task

ALTER TASK mytask1 RESUME;

ALTER TASK mytask1 START;

QUESTION 141
The endpoint insertFiles of SnowPipe rest API is used to inform Snowflake about the files to be ingested into a table. Select the two statements for this end point.

The post can contain at most 5000 files.

Each file path given must be <= 1024 bytes long when serialized as UTF-8

The post can contain unlimited number of files.

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

CSV

XML

Avro

JSON

Parquet