Question No. 1

A company is designing a process for importing a large amount of loT JSON data from cloud storage into Snowflake. New sets of loT data get generated and uploaded approximately every 5 minutes.

Once the loT data is in Snowflake, the company needs up-to-date information from an external vendor to join to the dat

a. This data is then presented to users through a dashboard that shows different levels of aggregation. The external vendor is a Snowflake customer.

What solution will MINIMIZE complexity and MAXIMIZE performance?

A1. Create an external table over the JSON data in cloud storage.
2. Create a task that runs every 5 minutes to run a transformation procedure on new data, based on a saved timestamp.
3. Ask the vendor to expose an API so an external function can be used to generate a call to join the data back to the loT data in the transformation procedure.
4. Give the transformed table access to the dashboard tool.
5. Perform the aggregations on the dashboard tool.

B1. Create an external table over the JSON data in cloud storage.
2. Create a task that runs every 5 minutes to run a transformation procedure on new data based on a saved timestamp.
3. Ask the vendor to create a data share with the required data that can be imported into the company's Snowflake account.
4. Join the vendor's data back to the loT data using a transformation procedure.
5. Create views over the larger dataset to perform the aggregations required by the dashboard.
6. Give the views access to the dashboard tool.

C1. Create a Snowpipe to bring the JSON data into Snowflake.
2. Use streams and tasks to trigger a transformation procedure when new JSON data arrives.
3. Ask the vendor to expose an API so an external function call can be made to join the vendor's data back to the loT data in a transformation procedure.
4. Create materialized views over the larger dataset to perform the aggregations required by the dashboard.
5. Give the materialized views access to the dashboard tool.

D1. Create a Snowpipe to bring the JSON data into Snowflake.
2. Use streams and tasks to trigger a transformation procedure when new JSON data arrives.
3. Ask the vendor to create a data share with the required data that is then imported into the Snowflake account.
4. Join the vendor's data back to the loT data in a transformation procedure
5. Create materialized views over the larger dataset to perform the aggregations required by the dashboard.
6. Give the materialized views access to the dashboard tool.

Show Answer

Correct Answer: D

Using Snowpipe for continuous, automated data ingestion minimizes the need for manual intervention and ensures that data is available in Snowflake promptly after it is generated. Leveraging Snowflake's data sharing capabilities allows for efficient and secure access to the vendor's data without the need for complex API integrations. Materialized views provide pre-aggregated data for fast access, which is ideal for dashboards that require high performance1234.

Reference =

* Snowflake Documentation on Snowpipe4

* Snowflake Documentation on Secure Data Sharing2

* Best Practices for Data Ingestion with Snowflake1

Question No. 2

Which command will create a schema without Fail-safe and will restrict object owners from passing on access to other users?

Acreate schema EDW.ACCOUNTING WITH MANAGED ACCESS;

Bcreate schema EDW.ACCOUNTING WITH MANAGED ACCESS DATA_RETENTION_TIME_IN_DAYS - 7;

Ccreate TRANSIENT schema EDW.ACCOUNTING WITH MANAGED ACCESS DATA_RETENTION_TIME_IN_DAYS = 1;

Dcreate TRANSIENT schema EDW.ACCOUNTING WITH MANAGED ACCESS DATA_RETENTION_TIME_IN_DAYS = 7;

Show Answer

Correct Answer: D

A transient schema in Snowflake is designed without a Fail-safe period, meaning it does not incur additional storage costs once it leaves Time Travel, and it is not protected by Fail-safe in the event of a data loss. The WITH MANAGED ACCESS option ensures that all privilege grants, including future grants on objects within the schema, are managed by the schema owner, thus restricting object owners from passing on access to other users1.

Reference =

* Snowflake Documentation on creating schemas1

* Snowflake Documentation on configuring access control2

* Snowflake Documentation on understanding and viewing Fail-safe3

Question No. 3

An Architect needs to design a data unloading strategy for Snowflake, that will be used with the COPY INTO command.

Which configuration is valid?

ALocation of files: Snowflake internal location
. File formats: CSV, XML
. File encoding: UTF-8
. Encryption: 128-bit

BLocation of files: Amazon S3
. File formats: CSV, JSON
. File encoding: Latin-1 (ISO-8859)
. Encryption: 128-bit

CLocation of files: Google Cloud Storage
. File formats: Parquet
. File encoding: UTF-8
* Compression: gzip

DLocation of files: Azure ADLS
. File formats: JSON, XML, Avro, Parquet, ORC
. Compression: bzip2
. Encryption: User-supplied key

Show Answer

Correct Answer: C

For the configuration of data unloading in Snowflake, the valid option among the provided choices is 'C.' This is because Snowflake supports unloading data into Google Cloud Storage using the COPY INTO <location> command with specific configurations. The configurations listed in option C, such as Parquet file format with UTF-8 encoding and gzip compression, are all supported by Snowflake. Notably, Parquet is a columnar storage file format, which is optimal for high-performance data processing tasks in Snowflake. The UTF-8 file encoding and gzip compression are both standard and widely used settings that are compatible with Snowflake's capabilities for data unloading to cloud storage platforms. Reference:

Snowflake Documentation on COPY INTO command

Snowflake Documentation on Supported File Formats

Snowflake Documentation on Compression and Encoding Options

Question No. 4

A company has built a data pipeline using Snowpipe to ingest files from an Amazon S3 bucket. Snowpipe is configured to load data into staging database tables. Then a task runs to load the data from the staging database tables into the reporting database tables.

The company is satisfied with the availability of the data in the reporting database tables, but the reporting tables are not pruning effectively. Currently, a size 4X-Large virtual warehouse is being used to query all of the tables in the reporting database.

What step can be taken to improve the pruning of the reporting tables?

AEliminate the use of Snowpipe and load the files into internal stages using PUT commands.

BIncrease the size of the virtual warehouse to a size 5X-Large.

CUse an ORDER BY <cluster_key (s) > command to load the reporting tables.

DCreate larger files for Snowpipe to ingest and ensure the staging frequency does not exceed 1 minute.

Show Answer

Correct Answer: C

Effective pruning in Snowflake relies on the organization of data within micro-partitions. By using an ORDER BY clause with clustering keys when loading data into the reporting tables, Snowflake can better organize the data within micro-partitions. This organization allows Snowflake to skip over irrelevant micro-partitions during a query, thus improving query performance and reducing the amount of data scanned12.

Reference =

* Snowflake Documentation on micro-partitions and data clustering2

* Community article on recognizing unsatisfactory pruning and improving it1

Question No. 5

The data share exists between a data provider account and a data consumer account. Five tables from the provider account are being shared with the consumer account. The consumer role has been granted the imported privileges privilege.

What will happen to the consumer account if a new table (table_6) is added to the provider schema?

AThe consumer role will automatically see the new table and no additional grants are needed.

BThe consumer role will see the table only after this grant is given on the consumer side:
grant imported privileges on database PSHARE_EDW_4TEST_DB to DEV_ROLE;

CThe consumer role will see the table only after this grant is given on the provider side:
use role accountadmin;
Grant select on table EDW.ACCOUNTING.Table_6 to share PSHARE_EDW_4TEST;

DThe consumer role will see the table only after this grant is given on the provider side:
use role accountadmin;
grant usage on database EDW to share PSHARE_EDW_4TEST ;
grant usage on schema EDW.ACCOUNTING to share PSHARE_EDW_4TEST ;
Grant select on table EDW.ACCOUNTING.Table_6 to database PSHARE_EDW_4TEST_DB ;

Show Answer

Correct Answer: D

When a new table (table_6) is added to a schema in the provider's account that is part of a data share, the consumer will not automatically see the new table. The consumer will only be able to access the new table once the appropriate privileges are granted by the provider. The correct process, as outlined in option D, involves using the provider's ACCOUNTADMIN role to grant USAGE privileges on the database and schema, followed by SELECT privileges on the new table, specifically to the share that includes the consumer's database. This ensures that the consumer account can access the new table under the established data sharing setup. Reference:

Snowflake Documentation on Managing Access Control

Snowflake Documentation on Data Sharing

Snowflake ARA-R01 Exam Actual Questions

The questions for ARA-R01 were last updated on Oct 3, 2024.

Unlock All Questions for Snowflake ARA-R01 Exam