SnowPro Advanced Architect ARA-C01 Training Questions

test2022-12-28T03:55:30+00:00

Snowflake SnowPro Advanced Architect Certification is a professional certification program that tests and verifies the skills and knowledge of advanced Snowflake architects. To become a Snowflake SnowPro Advanced Architect, you must pass an ARA-C01 exam. There are a variety of resources available to help candidates prepare for the SnowPro Advanced Architect ARA-C01 exam. FreeTestShare provides a list of recommended training and SnowPro Advanced Architect ARA-C01 Training Questions on its website. It is important for candidates to thoroughly prepare for the exam in order to increase their chances of success.

Check free ARA-C01 practice exam to assess your preparation level.

Page 1 of 4

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

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

3. When unloading from a snowflake table to an internal or external stage, the COPY INTO command supports which of the below ones?

String truncation

Reordering columns

Casts

Join

Omission of columns

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

5. Which of the below objects cannot be replicated from one region to the other?

File Formats

Sequences

Pipes

Views

Materialized views

Streams

6. Which command will you run to list all privileges and roles granted to the role

SHOW GRANTS TO ROLE <ROLE NAME>

SHOW GRANTS ON ROLE <ROLE NAME>

SHOW GRANTS OF ROLE <ROLE NAME>

SHOW GRANTS FOR ROLE <ROLE NAME>

7. Who can view account-level Credit and Storage Usage?

ACCOUNTADMIN

A role which has been granted the MONITOR USAGE global privilege

STORAGEADMIN

STORAGEMONITOR

8. Which of the below commands lists all the pipes for which you have access?

Display Pipes()

SHOW PIPES()

LIST PIPES()

LIST @PIPES

9. With default settings for multi cluster warehouse, how does snowflake determines when to start a new cluster?

Immediately when either a query is queued or the system detects that there’s one more query than the currently-running clusters can execute

Only if the system estimates there’s enough query load to keep the cluster busy for at least 6 minutes.

Only if the system estimates there’s enough query load to keep the cluster busy for at least 4 minutes.

10. While using joins, non-equality join predicates might result in significantly slower processing speeds and should be avoided if possible.

TRUE

FALSE

Page 2 of 4

11. You will be using a multi cluster warehouse. You will statically control the available resources (i.e. servers) and you have large numbers of concurrent user sessions and/or queries and the numbers do not fluctuate significantly.

Which mode will you use for the warehouse?

Maximized

Auto-Scale

Minimized

12. By default, the maximum file size that can be unloaded to a single file in snowflake is

10 MB

16 MB

5 GB for Azure, AWS and GCP

13. If your role does not own the share, but owns the objects in the share, how can you block access to the objects

Revoking the USAGE or SELECT privileges with CASCADE on the objects from the share owner.

Revoking the USAGE or SELECT privileges on the objects from the share owner

You will need to connect with the share owner

14. Secure views cannot take advantage of the internal optimizations which require access to the underlying data in the base tables for the view.

TRUE

FALSE

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

16. Which of the two are limitations of the insertReport API of SnowPipe?

The 10,000 most recent events are retained

Events are retained for a maximum of 10 minutes

Events are retained for a maximum of 24 hours

17. select metadata$filename, metadata$file_row_number from @filestage/data1.json.gz;

Please select the correct statements for the above-mentioned query.

FILESTAGE is the stage name, METADATA$FILE_ROW_NUMBER will give the row number for each record in the container staged data file

FILESTAGE is the file name, METADATA$FILE_ROW_NUMBER will give the path to the data file in the stage

FILESTAGE is the stage name, METADATA$FILE_ROW_NUMBER will give the path to the data file in the stage

18. You ran a query and the query

SELECT * FROM inventory WHERE BIBNUMBER = 2805127;

The query profile looks as below. If you would like to further tune the query, what is the best thing to do?

Execute the below query to enable auto clustering

alter table inventory cluster by (BIBNUMBER);

Create an index on column BIBNUMBER

Divide the table into multiple smaller tables

19. You need to choose a high cardinality column for the clustering key

TRUE

FALSE

20. External functions must be scalar functions

TRUE

FALSE

Page 3 of 4

21. When does a multi-cluster warehouse shuts down with default scaling policy

Immediately after the query is run

After 2 to 3 consecutive successful checks (performed at 1 minute intervals), which determine whether the load on the least-loaded cluster could be redistributed to the other clusters without spinning up the cluster again.

After 5 to 6 consecutive successful checks (performed at 1 minute intervals), which determine whether the load on the least-loaded cluster could be redistributed to the other clusters without spinning up the cluster again.

22. Validation mode can take the below options

RETURN_<n>_ROWS

RETURN_ERRORS

RETURN_ALL_ERRORS

RETURN_SEVERE_EERORS_ONLY

23. If a multi-cluster warehouse is resized, the new size applies to

Clusters that are currently running

Clusters that are started after the warehouse is resized

All of the above

24. USERADMIN and Security administrators (i.e. users with the SECURITYADMIN role) or higher can create roles.

TRUE

FALSE

25. You can define a clustering key directly on top of VARIANT columns

TRUE

FALSE

26. You have a large warehouse with auto suspend configured for 10 minutes. You submitted a query and it is going to run for more than 10 minutes.

What will happen to the query?

The query will automatically cancelled after 10 minutes

The query will go on a hold state

The query will continue to run

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

28. What does Percentage scanned from cache in the query profile signify?

The percentage of data scanned from the local disk cache

The percentage of data scanned from the METADATA cache

The percentage of data scanned from the QUERY cache

29. When would you usually consider to add clustering key to a table

The performance of the query has deteriorated over a period of time.

The number of users querying the table has increased

it is a multi-terabyte size table

The table has more than 20 columns

30. EXECUTE TASK privilege can be granted by whom

ACCOUNTADMIN

SECURITYADMIN

ROLE WHO OWNS THE TASK

Page 4 of 4

31. Materialized views based on external tables can improve query performance

TRUE

FALSE

32. JSON and PARQUET files can be loaded to columns in the same table

TRUE

FALSE

33. Cloud services can help in pruning even if the columns are variant columns.

TRUE

FALSE

34. A stream stores data with the same columns as the source data but with additional columns.

What are those additional columns?

METADATA$ACTION

METADATA$ISUPDATE

METADATA$ROW_ID

METADATA$DELETE

35. One of your colleagues has submitted a long running query in Snowflake. how long the query can run till snowflake automatically cancels the query?

14 hours

2 days

2 hours

24 hours

36. Which semi structured data function interprets an input string as a JSON document, producing a VARIANT value.

PARSE_JSON

PARSE_XML

STRIP_JSON

37. You have a view.

How will you list all the object references of the view?

GET_OBJECT_REFERENCES

GET_VIEW_REFERENCES

GET_VIEW_METADATA

38. For which use cases, will you use cross-cloud and cross-region replication?

Business continuity and disaster recovery

Secure data sharing across regions/cloud

Data portability and account migrations

All of these