Category ARA-C01

Q66. A Snowflake Architect is designing a multiple-account design strategy.
This strategy will be MOST cost-effective with which scenarios? (Select TWO).

Q67. How do Snowflake databases that are created from shares differ from standard databases that are not created from shares? (Choose three.)

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

Q69. What is the recommended strategy to choose the right sized warehouse to achieve best performance based on query processing?

Q70. 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?

Q71. A user is executing the following command sequentially within a timeframe of 10 minutes from start to finish:

What would be the output of this query?

Q72. 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?

Q73. An Architect runs the following SQL query:

How can this query be interpreted?

Q74. You are creating a TASK to query a table streams created on the raw table and insert subsets of rows into multiple tables. You are following the below steps, but when you reached the step to resume the task, you received an error message as below.
Why is this error thrown and who can give you the required privilege?

Steps to be followed to get this error
— Create a landing table to store raw JSON data.
— Snowpipe could load data into this table. create or replace table raw (var variant);
— Create a stream to capture inserts to the landing table.
— A task will consume a set of columns from this stream. create or replace stream rawstream1 on table raw;
— Create a second stream to capture inserts to the landing table.
— A second task will consume another set of columns from this stream. create or replace stream rawstream2 on table raw;
— Create a table that stores the names of office visitors identified in the raw data. create or replace table names (id int, first_name string, last_name string);
— Create a table that stores the visitation dates of office visitors identified in the raw data.
create or replace table visits (id int, dt date);
— Create a task that inserts new name records from the rawstream1 stream into the names table
— every minute when the stream contains records.
— Replace the ‘mywh’ warehouse with a warehouse that your role has USAGE privilege on. create or replace task raw_to_names
warehouse = etl_wh
schedule = ‘1 minute’
when
system$stream_has_data(‘rawstream1’)
as
merge into names n
using (select var:id id, var:fname fname, var:lname lname from rawstream1) r1 on n.id = to_number(r1.id)
when matched then update set n.first_name = r1.fname, n.last_name = r1.lname
when not matched then insert (id, first_name, last_name) values (r1.id, r1.fname, r1.lname)
;
— Create another task that merges visitation records from the rawstream1 stream into the visits table
— every minute when the stream contains records.
— Records with new IDs are inserted into the visits table;
— Records with IDs that exist in the visits table update the DT column in the table.
— Replace the ‘mywh’ warehouse with a warehouse that your role has USAGE privilege on. create or replace task raw_to_visits
warehouse = etl_wh schedule = ‘1 minute’ when
system$stream_has_data(‘rawstream2’) as
merge into visits v
using (select var:id id, var:visit_dt visit_dt from rawstream2) r2 on v.id = to_number(r2.id) when matched then update set v.dt = r2.visit_dt
when not matched then insert (id, dt) values (r2.id, r2.visit_dt);
— Resume both tasks.
alter task raw_to_names resume;

Q75. Based on the Snowflake object hierarchy, what securable objects belong directly to a Snowflake account? (Select THREE).

Q76. An Architect has been asked to clone schema STAGING as it looked one week ago, Tuesday June 1st at 8:00 AM, to recover some objects.
The STAGING schema has 50 days of retention.
The Architect runs the following statement:
CREATE SCHEMA STAGING_CLONE CLONE STAGING at (timestamp => ‘2021-06-01 08:00:00’); The Architect receives the following error: Time travel data is not available for schema STAGING. The requested time is either beyond the allowed time travel period or before the object creation time.
The Architect then checks the schema history and sees the following:
CREATED_ON|NAME|DROPPED_ON
2021-06-02 23:00:00 | STAGING | NULL
2021-05-01 10:00:00 | STAGING | 2021-06-02 23:00:00
How can cloning the STAGING schema be achieved?

Q77. An Architect is designing a pipeline to stream event data into Snowflake using the Snowflake Kafka connector. The Architect’s highest priority is to configure the connector to stream data in the MOST cost-effective manner.
Which of the following is recommended for optimizing the cost associated with the Snowflake Kafka connector?

Q78. What considerations need to be taken when using database cloning as a tool for data lifecycle management in a development environment? (Select TWO).

Q79. A new user user_01 is created within Snowflake. The following two commands are executed:
Command 1-> show grants to user user_01;
Command 2 ~> show grants on user user 01;
What inferences can be made about these commands?

Q80. 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?

Q81. Snowflake supports the following query performance optimizing methods

Q82. A user named USER_01 needs access to create a materialized view on a schema EDW. STG_SCHEMA.
How can this access be provided?

Q83. You have a view.
How will you list all the object references of the view?