Snowflake ARA-R01 Exam Practice Test Instant Access

Question 1

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

AAny pipes in the source are not cloned.

BAny pipes in the source referring to internal stages are not cloned.

CAny pipes in the source referring to external stages are not cloned.

DThe clone inherits all granted privileges of all child objects in the source object, including the database.

EThe clone inherits all granted privileges of all child objects in the source object, excluding the database.

Answer : A, C

Question 2

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

ACOPY INTO tablea FROM @%tablea RETURN_FAILED_ONLY = TRUE;

BCOPY INTO tablea FROM @%tablea;

CCOPY INTO tablea FROM @%tablea FILES = ('file5.csv');

DCOPY INTO tablea FROM @%tablea FORCE = TRUE;

ECOPY INTO tablea FROM @%tablea NEW_FILES_ONLY = TRUE;

FCOPY INTO tablea FROM @%tablea MERGE = TRUE;

Answer : B, C

Option A (RETURN_FAILED_ONLY)will only load files that previously failed to load.Since file5.csv already exists in the stage with the same name,it will not be considered a new file and will not be loaded.

Option D (FORCE)will overwrite any existing data in the table.This is not desired as we only want to load the data from file5.csv.

Option E (NEW_FILES_ONLY)will only load files that have been added to the stage since the last COPY command.This will not work because file5.csv was already in the stage before it was fixed.

Option F (MERGE)is used to merge data from a stage into an existing table,creating new rows for any data not already present.This is not needed in this case as we simply want to load the data from file5.csv.

Therefore, the architect can use either COPY INTO tablea FROM @%tablea or COPY INTO tablea FROM @%tablea FILES = ('file5.csv') to load only file5.csv from the stage. Both options will load the data from the specified file without overwriting any existing data or requiring additional configuration

Question 3

Which statements describe characteristics of the use of materialized views in Snowflake? (Choose two.)

AThey can include ORDER BY clauses.

BThey cannot include nested subqueries.

CThey can include context functions, such as CURRENT_TIME().

DThey can support MIN and MAX aggregates.

EThey can support inner joins, but not outer joins.

Answer : B, D

According to the Snowflake documentation, materialized views have some limitations on the query specification that defines them. One of these limitations is that they cannot include nested subqueries, such as subqueries in the FROM clause or scalar subqueries in the SELECT list. Another limitation is that they cannot include ORDER BY clauses, context functions (such as CURRENT_TIME()), or outer joins. However, materialized views can support MIN and MAX aggregates, as well as other aggregate functions, such as SUM, COUNT, and AVG.

Limitations on Creating Materialized Views | Snowflake Documentation

Working with Materialized Views | Snowflake Documentation

Question 4

Which of the following ingestion methods can be used to load near real-time data by using the messaging services provided by a cloud provider?

ASnowflake Connector for Kafka

BSnowflake streams

CSnowpipe

DSpark

Answer : A

Snowflake Connector for Kafka and Snowpipe are two ingestion methods that can be used to load near real-time data by using the messaging services provided by a cloud provider. Snowflake Connector for Kafka enables you to stream structured and semi-structured data from Apache Kafka topics into Snowflake tables. Snowpipe enables you to load data from files that are continuously added to a cloud storage location, such as Amazon S3 or Azure Blob Storage. Both methods leverage Snowflake's micro-partitioning and columnar storage to optimize data ingestion and query performance. Snowflake streams and Spark are not ingestion methods, but rather components of the Snowflake architecture. Snowflake streams provide change data capture (CDC) functionality by tracking data changes in a table. Spark is a distributed computing framework that can be used to process large-scale data and write it to Snowflake using the Snowflake Spark Connector.Reference:

Snowflake Connector for Kafka

Snowpipe

Snowflake Streams

Snowflake Spark Connector

Question 5

Which Snowflake data modeling approach is designed for BI queries?

A3 NF

BStar schema

CData Vault

DSnowflake schema

Answer : B

In the context of business intelligence (BI) queries, which are typically focused on data analysis and reporting, the star schema is the most suitable data modeling approach.

Option B: Star Schema - The star schema is a type of relational database schema that is widely used for developing data warehouses and data marts for BI purposes. It consists of a central fact table surrounded by dimension tables. The fact table contains the core data metrics, and the dimension tables contain descriptive attributes related to the fact data. The simplicity of the star schema allows for efficient querying and aggregation, which are common operations in BI reporting.

Question 6

The Data Engineering team at a large manufacturing company needs to engineer data coming from many sources to support a wide variety of use cases and data consumer requirements which include:

1) Finance and Vendor Management team members who require reporting and visualization

2) Data Science team members who require access to raw data for ML model development

3) Sales team members who require engineered and protected data for data monetization

What Snowflake data modeling approaches will meet these requirements? (Choose two.)

AConsolidate data in the company's data lake and use EXTERNAL TABLES.

BCreate a raw database for landing and persisting raw data entering the data pipelines.

CCreate a set of profile-specific databases that aligns data with usage patterns.

DCreate a single star schema in a single database to support all consumers' requirements.

ECreate a Data Vault as the sole data pipeline endpoint and have all consumers directly access the Vault.

Answer : B, C

To accommodate the diverse needs of different teams and use cases within a company, a flexible and multi-faceted approach to data modeling is required.

Option B: By creating a raw database for landing and persisting raw data, you ensure that the Data Science team has access to unprocessed data for machine learning model development. This aligns with the best practices of having a staging area or raw data zone in a modern data architecture where raw data is ingested before being transformed or processed for different use cases.

Option C: Having profile-specific databases means creating targeted databases that are designed to meet the specific requirements of each user profile or team within the company. For the Finance and Vendor Management teams, the data can be structured and optimized for reporting and visualization. For the Sales team, the database can include engineered and protected data that is suitable for data monetization efforts. This strategy not only aligns data with usage patterns but also helps in managing data access and security policies effectively.

Question 7

A Snowflake Architect is designing a multi-tenant application strategy for an organization in the Snowflake Data Cloud and is considering using an Account Per Tenant strategy.

Which requirements will be addressed with this approach? (Choose two.)

AThere needs to be fewer objects per tenant.

BSecurity and Role-Based Access Control (RBAC) policies must be simple to configure.

CCompute costs must be optimized.

DTenant data shape may be unique per tenant.

EStorage costs must be optimized.

Answer : B, D

The Account Per Tenant strategy involves creating separate Snowflake accounts for each tenant within the multi-tenant application. This approach offers a number of advantages.

Option B: With separate accounts, each tenant's environment is isolated, making security and RBAC policies simpler to configure and maintain. This is because each account can have its own set of roles and privileges without the risk of cross-tenant access or the complexity of maintaining a highly granular permission model within a shared environment.

Option D: This approach also allows for each tenant to have a unique data shape, meaning that the database schema can be tailored to the specific needs of each tenant without affecting others. This can be essential when tenants have different data models, usage patterns, or application customizations.

Snowflake SnowPro Advanced: Architect Recertification ARA-R01 Exam Practice Test