ARA-C01 practice questions 151–160

1. Question 151 · 193 in bank

   An Architect needs to automate the daily import of two files from an external stage into Snowflake. One file has Parquet-formatted data, the other has CSV-formatted data. How should the data be joined and aggregated to produce a final result set?

   • AUse Snowpipe to ingest the two files, then create a materialized view to produce the final result set.
   • BCreate a task using Snowflake scripting that will import the files, and then call a User-Defined Function (UDF) to produce the final result set.
   • CCreate a JavaScript stored procedure to read, join, and aggregate the data directly from the external stage, and then store the results in a table.
   • DCreate a materialized view to read, join, and aggregate the data directly from the external stage, and use the view to produce the final result set.

   Show answer

   Answer: A

   Next question

2. Question 152 · 193 in bank

   Select 3

   What considerations should be taken into account when deciding which method to use to load data into Snowflake? (Choose three.)

   • AThe ETL method is typically faster than other methods, as it transforms data on ingestion.
   • BWith the ETL method, users may need to reload the incoming data if there is an error in the process.
   • CThe ELT method is more scalable than other methods as it uses Snowflake’s virtual warehouses.
   • DThe ELT method can separate the ingestion and transformation resources, making it the most flexible method.
   • EThe ELT method will not allow any type of data transformation on ingestion.
   • FWith the ELT method, transformations may take longer to execute than other methods, as they depend on the size of the data.

   Show answer

   Answer: BCD

   Next question

3. Question 153 · 193 in bank

   A company has data in two databases, DB1 and DB2, with the following roles: Role R1 has select access to all tables in database DB1. Role R2 has select access to all tables in database DB2. Users should, by default only, be allowed to access data from one of the databases to ensure that the DEFAULT_ROLE setting should be used. A limited number of users that have select access to DB1 also need select access to DB2 in the same query. How can the access be provided for this limited number of users using the LEAST amount of operational overhead?

   • AAlter the users and set the DEFAULT_SECONDARY_ROLE property to R2.
   • BGrant R2 to the users and use the USE_SECONDARY_ROLES setting for SELECT.
   • CGrant R2 access to the R1 role so the users can take advantage of privilege inheritance.
   • DGrant R2 access to the users and have them activate it using the USE_SECONDARY_ROLES command.

   Show answer

   Answer: D

   Next question

4. Question 154 · 193 in bank

   An Architect is working with a healthcare company’s Enterprise data governance team to review how company-sensitive data is protected within Snowflake. Physicians in the company network can run queries against views. There are two views, one shows mental health information, and the other shows physical health information: create view mental_health_view as select * from patients where category = 'MentalHealth'; create view physical_health_view as select * from patients where category = 'PhysicalHealth'; Most physicians do not have direct access to the table. Instead, they are assigned one of two roles: 1. MentalHealth, which has privileges to read from mental_health_view. or 2. PhysicalHealth, which has privileges to read from physical_health_view. A physician with the PhysicalHealth role wants to know whether there are any mental health patients in the table, and used the following query: select * from physical_health_view where 1/iff(category = 'MentalHealth', 0, 1) = 1; How will this query affect the sensitive data?

   • ASnowflake will ensure that for any scenario, the underlying patient sensitive information will not be revealed directly or indirectly in any manner.
   • BIf the physician is also assigned SECURITYADMIN or ACCOUNTADMIN roles, they will be able to view the mental health data, despite not being named explicitly in that role.
   • CIf the physician is also assigned the SYSADMIN role and SYSADMIN is the owner of the patient table, the physician will be able to view the mental health data.
   • DIt depends on what choices the query optimizer has made, and how the views are written. The physician with physical_health_view privileges will not see any rows for patients with mental health issues, but can deduce that there is at least one patient in the mental health category.

   Show answer

   Answer: D

   Next question

5. Question 155 · 193 in bank

   Select 2

   A company that is new to Snowflake is able to connect to its account using SnowSQL, but receives the following error message in the connection logs: SEVERE: WARNING!!! Using fail-open to connect. Driver is connecting to an HTTPS endpoint without OCSP based Certificate Revocation checking as it could not obtain a valid OCSP Response to use from the CA OCSP responder. What does this message indicate? (Choose two.)

   • AThe SnowSQL ocsp_fail_open parameter is set to use the default value in the connection.
   • BPrivate Link is not enabled in the account.
   • CThe DNS team did not allow port 443 in the firewall.
   • DThe DNS team did not allow port 80 in the firewall.
   • EThe client IP is not on the allow list.

   Show answer

   Answer: AD

   Next question

6. Question 156 · 193 in bank

   Select 3

   A company is considering adopting Data Mesh as their data platform architecture. Which Snowflake features and services offer native support of this architecture? (Choose three.)

   • AIsolated compute and shared storage allowing data teams to manage their own workloads.
   • BData sharing, the Snowflake Marketplace, and the Data Exchange supporting the Data Mesh publish-and-serve model.
   • CDynamic Data Masking ensuring that data is always masked with hashing functions.
   • DMaterialized views supporting that external tables in the mesh are visible from all databases and accounts.
   • EBuilt-In data governance managing the visibility and accessibility of data product components.
   • FFederated authentication supporting external user access to Snowflake data products accounts.

   Show answer

   Answer: ABE

   Next question

7. Question 157 · 193 in bank

   A company’s table, employees, was replaced with a new version:

   

   How can the original table be recovered with the LEAST operational overhead?

   • AUse Time Travel to recover the data using this command: SELECT * FROM employees BEFORE(statement => ‘01a5c8b3-0601-ad2b-0067-a503000al312’);
   • BUse Time Travel with the timestamp to recover the data using this command: SELECT * FROM employees AT(timestamp => ‘2022-07-22 16:35:00.000 -0700’::timestamp_tz);
   • CRevert to the original employees table using this command: UNDROP TABLE employees;
   • DRename the new employees table and undrop the original employees table using this command: ALTER TABLE employees rename to employees_bad; UNDROP TABLE employees;

   Show answer

   Answer: D

   Next question

8. Question 158 · 193 in bank

   A company has several applications that have been developed in-house that need to connect to Snowflake. These applications do not have access to a web browser, and have no redirect capabilities. Based on this scenario, what is the Snowflake best practice recommendation for application authentication?

   • AUse Snowflake OAuth.
   • BUse usernames and passwords.
   • CUse an external OAuth.
   • DUse key pair authentication with a service user.

   Show answer

   Answer: D

   Next question

9. Question 159 · 193 in bank

   Select 2

   An Architect is troubleshooting a long-running statement and needs to identify the list of transactions that are blocked and the queries that are blocking them. Which views should be used for troubleshooting? (Choose two.)

   • AQUERY_HISTORY
   • BOBJECT_DEPENDENCIES
   • CDATA_TRANSFER_HISTORY
   • DLOCK_WAIT_HISTORY
   • EACCESS HISTORY

   Show answer

   Answer: AD

   Next question

10. Question 160 · 193 in bank

    An Architect is designing multiple data pipelines to ingest, transform, and consume data in Snowflake in support of a newly-developed application. While the development team has significant SQL expertise, most members lack experience with Python. Which architecture design will meet these requirements using the LEAST amount of operational overhead?

    • APrioritize the use of external tables in the architecture to minimize the time required to ingest the data into Snowflake.
    • BUse streams, tasks, and stored procedures to set up the pipelines, and use templated patterns for the application of the MERGE logic.
    • CUse dynamic tables to build the Directed Acyclic Graphs (DAGs) using the CREATE DYNAMIC TABLE command for each node, and fine-tune the tables using custom DML.
    • DUse dynamic tables for the ingestion pipelines and use the TARGET LAG = DOWNSTREAM parameter for all but the final table in the Directed Acyclic Graphs (DAGs).

    Show answer

    Answer: D

    Next question

Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page

ARA-C01 practice, question 151. 193 questions available.