ARA-C01 Practice Questions

The most cost-effective way to increase the availability and performance of the reports during peak

usage times, while keeping costs under control, is to use a multi-cluster warehouse in auto-scale

mode. Option D suggests using a multi-cluster warehouse with 1 size Medium cluster and allowing it

to auto-scale between 1 and 4 clusters based on demand. This setup ensures that additional

computing resources are available when needed (e.g., during Monday morning peaks) and are scaled

down to minimize costs when the demand decreases. This approach optimizes resource utilization

and cost by adjusting the compute capacity dynamically, rather than maintaining a larger fixed size or

multiple clusters continuously.

Reference: Snowflake's official documentation on managing warehouses and using auto-scaling

features.

Event notifications in Snowpipe are messages sent by cloud storage providers to notify Snowflake of

new or modified files in a stage. Snowpipe uses these notifications to trigger data loading from the

stage to the target table. When a pipe is paused, event messages received for the pipe enter a

limited retention period, which varies depending on the cloud storage provider. If the pipe is not

resumed within the retention period, the event messages will be discarded and the data will not be

loaded automatically. To load the data, the pipe must be resumed and the COPY command must be

executed manually. This is a characteristic of event notifications in Snowpipe that distinguishes them

from other options. Reference: Snowflake Documentation: Using Snowpipe, Snowflake

Documentation: Pausing and Resuming a Pipe

A securable object is an entity to which access can be granted in Snowflake. Securable objects

include databases, schemas, tables, views, stages, pipes, functions, procedures, sequences, tasks,

streams, roles, warehouses, and shares1.

The Snowflake object hierarchy is a logical structure that organizes the securable objects in a nested

manner. The top-most container is the account, which contains all the databases, roles, and

warehouses for the customer organization. Each database contains schemas, which in turn contain

tables, views, stages, pipes, functions, procedures, sequences, tasks, and streams. Each role can be

granted privileges on other roles or securable objects. Each warehouse can be used to execute

queries on securable objects2.

Based on the Snowflake object hierarchy, the securable objects that belong directly to a Snowflake

account are databases, roles, and warehouses. These objects are created and managed at the

account level, and do not depend on any other securable object. The other options are not correct

because:

Schemas belong to databases, not to accounts. A schema must be created within an existing

database3.

Tables belong to schemas, not to accounts. A table must be created within an existing schema4.

Stages belong to schemas or tables, not to accounts. A stage must be created within an existing

schema or table.

Reference:

1: Overview of Access Control | Snowflake Documentation

2: Securable Objects | Snowflake Documentation

3: CREATE SCHEMA | Snowflake Documentation

4: CREATE TABLE | Snowflake Documentation

[5]: CREATE STAGE | Snowflake Documentation

According to the Snowflake documentation, the data loading performance can be improved by

following some best practices and guidelines for preparing and staging the data files. One of the

recommendations is to aim for data files that are roughly 100-250 MB (or larger) in size compressed,

as this will optimize the number of parallel operations for a load. Smaller files should be aggregated

and larger files should be split to achieve this size range. Another recommendation is to use a multi-

cluster warehouse for loading, as this will allow for scaling up or out the compute resources

depending on the load demand. A single-cluster warehouse may not be able to handle the load

concurrency and throughput efficiently. Therefore, by creating a multi-cluster warehouse and

merging smaller files to create bigger files, the data loading performance can be

improved. Reference:

Data Loading Considerations

Preparing Your Data Files

Planning a Data Load

Snowflake is a cloud data platform that supports various data models for modeling tables in a

Snowflake environment. The data models can be classified into two categories: dimensional and

normalized. Dimensional data models are designed to optimize query performance and ease of use

for business intelligence and analytics. Normalized data models are designed to reduce data

redundancy and ensure data integrity for transactional and operational systems. The following are

some of the data models that can be used in Snowflake:

Dimensional/Kimball: This is a popular dimensional data model that uses a star or snowflake schema

to organize data into fact and dimension tables. Fact tables store quantitative measures and foreign

keys to dimension tables. Dimension tables store descriptive attributes and hierarchies. A star

schema has a single denormalized dimension table for each dimension, while a snowflake schema

has multiple normalized dimension tables for each dimension. Snowflake supports both star and

snowflake schemas, and allows users to create views and joins to simplify queries.

Inmon/3NF: This is a common normalized data model that uses a third normal form (3NF) schema to

organize data into entities and relationships. 3NF schema eliminates data duplication and ensures

data consistency by applying three rules: 1) every column in a table must depend on the primary key,

2) every column in a table must depend on the whole primary key, not a part of it, and 3) every

column in a table must depend only on the primary key, not on other columns. Snowflake supports

3NF schema and allows users to create referential integrity constraints and foreign key relationships

to enforce data quality.

Data vault: This is a hybrid data model that combines the best practices of dimensional and

normalized data models to create a scalable, flexible, and resilient data warehouse. Data vault

schema consists of three types of tables: hubs, links, and satellites. Hubs store business keys and

metadata for each entity. Links store associations and relationships between entities. Satellites store

descriptive attributes and historical changes for each entity or relationship. Snowflake supports data

vault schema and allows users to leverage its features such as time travel, zero-copy cloning, and

secure data sharing to implement data vault methodology.

Reference: What is Data Modeling? | Snowflake, Snowflake Schema in Data Warehouse Model -

GeeksforGeeks, [Data Vault 2.0 Modeling with Snowflake]

According to the SnowPro Advanced: Architect documents and learning resources, column masking

policies are applied at query time based on the privileges of the user who runs the query. Therefore,

if a user has the privilege to see unmasked data in a column, they will see the original data when

they query that column. If they load this column data into another column that does not have a

masking policy, the unmasked data will be loaded in the new column, and any user who can query

the new column will see the unmasked data as well. The masking policy does not affect the

underlying data in the column, only the query results.

Reference:

Snowflake Documentation: Column Masking

Snowflake Learning: Column Masking

Option A is the best design to meet the requirements because it uses a materialized view on top of

an external table against the S3 bucket in AWS Singapore. A materialized view is a database object

that contains the results of a query and can be refreshed periodically to reflect changes in the

underlying data1. An external table is a table that

data files stored in a cloud storage

service, such as Amazon S32. By using a materialized view on top of an external table, the company

can provide access to frequently changing data, keep egress costs to a minimum, and maintain low

latency. This is because the materialized view will cache the query results in Snowflake, reducing the

need to access the external data files and incur network charges. The materialized view will also

improve the query performance by avoiding scanning the external data files every time. The

materialized view can be refreshed on a schedule or on demand to capture the changes in the

external data files1.

Option B is not the best design because it uses an external table against the S3 bucket in AWS

Singapore and copies the data into transient tables. A transient table is a table that is not subject to

the Time Travel and Fail-safe features of Snowflake, and is automatically purged after a period of

time3. By using an external table and copying the data into transient tables, the company will incur

more egress costs and operational overhead than using a materialized view. This is because the

external table will access the external data files every time a query is executed, and the copy

operation will also transfer data from S3 to Snowflake. The transient tables will also consume more

storage space in Snowflake and require manual maintenance to ensure they are up to date.

Option C is not the best design because it copies the data between providers from S3 to Azure Blob

storage to collocate, then uses Snowpipe for data ingestion. Snowpipe is a service that automates

the loading of data from external sources into Snowflake tables4. By copying the data between

providers, the company will incur high egress costs and latency, as well as operational complexity

and maintenance of the infrastructure. Snowpipe will also add another layer of processing and

storage in Snowflake, which may not be necessary if the external data files are already in a queryable

format.

Option D is not the best design because it uses AWS Transfer Family to replicate data between the S3

bucket in AWS Singapore and an Azure Netherlands Blob storage, then uses an external table against

the Blob storage. AWS Transfer Family is a service that enables secure and seamless transfer of files

over SFTP, FTPS, and FTP to and from Amazon S3 or Amazon EFS5. By using AWS Transfer Family, the

company will incur high egress costs and latency, as well as operational complexity and maintenance

of the infrastructure. The external table will also access the external data files every time a query is

executed, which may affect the query performance.

Reference: 1: Materialized Views 2: External Tables 3: Transient Tables 4: Snowpipe Overview 5: AWS

Transfer Family

The image shows a SQL query that can be used to identify which queries are spilled to remote

storage and suggests changing the warehouse parameters to address this issue. Spilling to remote

storage occurs when the memory allocated to a warehouse is insufficient to process a query, and

Snowflake uses disk or cloud storage as a temporary cache. This can significantly slow down the

query performance and increase the cost. To troubleshoot this issue, a Snowflake Architect can run

the query shown in the image to find out which queries are spilling, how much data they are spilling,

and which warehouses they are using. Then, the architect can adjust the warehouse size, type, or

scaling policy to provide enough memory for the queries and avoid spilling12. Reference:

Recognizing Disk Spilling

Managing the Kafka Connector

The purpose of creating a storage integration in Snowflake includes:

B. Store a generated identity and access management (IAM) entity for an external cloud provider -

This helps in managing authentication and authorization with external cloud storage without

embedding credentials in Snowflake. It supports various cloud providers like AWS, Azure, or GCP,

ensuring that the identity management is streamlined across platforms.

C. Support multiple external stages using one single Snowflake object - Storage integrations allow

you to set up access configurations that can be reused across multiple external stages, simplifying the

management of external data integrations.

D. Avoid supplying credentials when creating a stage or when loading or unloading data - By using a

storage integration, Snowflake can interact with external storage without the need to continuously

manage or expose sensitive credentials, enhancing security and ease of operations.

Reference: Snowflake documentation on storage integrations, found within the SnowPro Advanced:

Architect course materials.

According to the Snowflake documentation, to create a database from an inbound share, the user’s

role must have the following privileges:

The CREATE DATABASE privilege on the current account. This privilege allows the user to create a

new database in the account1.

The IMPORT DATABASE privilege on the share. This privilege allows the user to import a database

from the share into the account2. The other privileges listed are not relevant for this

requirement. The IMPORT SHARE privilege is used to import a share into the account, not a

database3. The IMPORT PRIVILEGES privilege is used to import the privileges granted on the shared

objects, not the objects themselves2. The CREATE SHARE privilege is used to create a share to

provide data to other accounts, not to consume data from other accounts4.

Reference:

CREATE DATABASE | Snowflake Documentation

Importing Data from a Share | Snowflake Documentation

Importing a Share | Snowflake Documentation

CREATE SHARE | Snowflake Documentation