Free Snowflake ARA-C01 Practice Exams - Page 15 of 22

Question #141

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?

A . Eliminate the use of Snowpipe and load the files into internal stages using PUT commands.
B . Increase the size of the virtual warehouse to a size 5X-Large.
C . Use an ORDER BY <cluster_key (s) > command to load the reporting tables.
D . Create larger files for Snowpipe to ingest and ensure the staging frequency does not exceed 1 minute.

Reveal Solution Hide Solution

Correct Answer: C
C

Explanation:

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 #142

What Snowflake system functions are used to view and or monitor the clustering metadata for a table? (Select TWO).

A . SYSTEMSCLUSTERING
B . SYSTEMSTABLE_CLUSTERING
C . SYSTEMSCLUSTERING_DEPTH
D . SYSTEMSCLUSTERING_RATIO
E . SYSTEMSCLUSTERING_INFORMATION

Reveal Solution Hide Solution

Correct Answer: CE
CE

Explanation:

The Snowflake system functions used to view and monitor the clustering metadata for a table are:

SYSTEM$CLUSTERING_INFORMATION

SYSTEM$CLUSTERING_DEPTH

Comprehensive But Short Explanation

The SYSTEM$CLUSTERING_INFORMATION function in Snowflake returns a variety of clustering information for a specified table. This information includes the average clustering depth, total number of micro-partitions, total constant partition count, average overlaps, average depth, and a partition depth histogram. This function allows you to specify either one or multiple columns for which the clustering information is returned, and it returns this data in JSON format.

The SYSTEM$CLUSTERING_DEPTH function computes the average depth of a table based on specified columns or the clustering key defined for the table. A lower average depth indicates that the table is better clustered with respect to the specified columns. This function also allows specifying columns to calculate the depth, and the values need to be enclosed in single quotes.

Reference: SYSTEM$CLUSTERING_INFORMATION: Snowflake Documentation

SYSTEM$CLUSTERING_DEPTH: Snowflake Documentation

Question #143

What is a characteristic of loading data into Snowflake using the Snowflake Connector for Kafka?

A . The Connector only works in Snowflake regions that use AWS infrastructure.
B . The Connector works with all file formats, including text, JSON, Avro, Ore, Parquet, and XML.
C . The Connector creates and manages its own stage, file format, and pipe objects.
D . Loads using the Connector will have lower latency than Snowpipe and will ingest data in real time.

Reveal Solution Hide Solution

Correct Answer: C
C

Explanation:

According to the SnowPro Advanced: Architect documents and learning resources, a characteristic of loading data into Snowflake using the Snowflake Connector for Kafka is that the Connector creates and manages its own stage, file format, and pipe objects. The stage is an internal stage that is used to store the data files from the Kafka topics. The file format is a JSON or Avro file format that is used to parse the data files. The pipe is a Snowpipe object that is used to load the data files into the Snowflake table. The Connector automatically creates and configures these objects based on the Kafka configuration properties, and handles the cleanup and maintenance of these objects1.

The other options are incorrect because they are not characteristics of loading data into Snowflake using the Snowflake Connector for Kafka. Option A is incorrect because the Connector works in Snowflake regions that use any cloud infrastructure, not just AWS. The Connector supports AWS, Azure, and Google Cloud platforms, and can load data across different regions and cloud platforms using data replication2. Option B is incorrect because the Connector does not work with all file formats, only JSON and Avro. The Connector expects the data in the Kafka topics to be in JSON or Avro format, and parses the data accordingly. Other file formats, such as text, ORC, Parquet, or XML, are not supported by the Connector3. Option D is incorrect because loads using the Connector do not have lower latency than Snowpipe, and do not ingest data in real time. The Connector uses Snowpipe to load data into Snowflake, and inherits the same latency and performance characteristics of Snowpipe. The Connector does not provide real-time ingestion, but near real-time ingestion, depending on the frequency and size of the data files4.

Reference: Installing and Configuring the Kafka Connector | Snowflake Documentation, Sharing Data Across Regions and Cloud Platforms | Snowflake Documentation, Overview of the Kafka Connector | Snowflake Documentation, Using Snowflake Connector for Kafka With Snowpipe Streaming | Snowflake Documentation

Question #144

What are characteristics of Dynamic Data Masking? (Select TWO).

A . A masking policy that Is currently set on a table can be dropped.
B . A single masking policy can be applied to columns in different tables.
C . A masking policy can be applied to the value column of an external table.
D . The role that creates the masking policy will always see unmasked data In query results
E . A masking policy can be applied to a column with the GEOGRAPHY data type.

Reveal Solution Hide Solution

Correct Answer: AB
AB

Explanation:

Dynamic Data Masking is a feature that allows masking sensitive data in query results based on the role of the user who executes the query. A masking policy is a user-defined function that specifies the masking logic and can be applied to one or more columns in one or more tables. A masking policy that is currently set on a table can be dropped using the ALTER TABLE command. A single masking policy can be applied to columns in different tables using the ALTER TABLE command with the SET MASKING POLICY clause. The other options are either incorrect or not supported by Snowflake. A masking policy cannot be applied to the value column of an external table, as external tables do not support column-level security. The role that creates the masking policy will not always see unmasked data in query results, as the masking policy can be applied to the owner role as well. A masking policy cannot be applied to a column with the GEOGRAPHY data type, as Snowflake only supports masking policies for scalar data types.

Reference: Snowflake Documentation: Dynamic Data

Masking, Snowflake Documentation: ALTER TABLE

Question #145

What integration object should be used to place restrictions on where data may be exported?

A . Stage integration
B . Security integration
C . Storage integration
D . API integration

Reveal Solution Hide Solution

Correct Answer: C
C

Explanation:

In Snowflake, a storage integration is used to define and configure external cloud storage that Snowflake will interact with. This includes specifying security policies for access control. One of the main features of storage integrations is the ability to set restrictions on where data may be exported. This is done by binding the storage integration to specific cloud storage locations, thereby ensuring that Snowflake can only access those locations. It helps to maintain control over the data and complies with data governance and security policies by preventing unauthorized data exports to unspecified locations.

Question #146

A company wants to deploy its Snowflake accounts inside its corporate network with no visibility on the internet. The company is using a VPN infrastructure and Virtual Desktop Infrastructure (VDI) for its Snowflake users. The company also wants to re-use the login credentials set up for the VDI to eliminate redundancy when managing logins.

What Snowflake functionality should be used to meet these requirements? (Choose two.)

A . Set up replication to allow users to connect from outside the company VPN.
B . Provision a unique company Tri-Secret Secure key.
C . Use private connectivity from a cloud provider.
D . Set up SSO for federated authentication.
E . Use a proxy Snowflake account outside the VPN, enabling client redirect for user logins.

Reveal Solution Hide Solution

Correct Answer: C, D
C, D

Explanation:

According to the SnowPro Advanced: Architect documents and learning resources, the Snowflake functionality that should be used to meet these requirements are:

Use private connectivity from a cloud provider. This feature allows customers to connect to Snowflake from their own private network without exposing their data to the public Internet. Snowflake integrates with AWS PrivateLink, Azure Private Link, and Google Cloud Private Service Connect to offer private connectivity from customers’ VPCs or VNets to Snowflake endpoints. Customers can control how traffic reaches the Snowflake endpoint and avoid the need for proxies or public IP addresses123.

Set up SSO for federated authentication. This feature allows customers to use their existing identity provider (IdP) to authenticate users for SSO access to Snowflake. Snowflake supports most SAML 2.0-compliant vendors as an IdP, including Okta, Microsoft AD FS, Google G Suite, Microsoft Azure Active Directory, OneLogin, Ping Identity, and PingOne. By setting up SSO for federated authentication, customers can leverage their existing user credentials and profile information, and provide stronger security than username/password authentication4.

The other options are incorrect because they do not meet the requirements or are not feasible. Option A is incorrect because setting up replication does not allow users to connect from outside the company VPN. Replication is a feature of Snowflake that enables copying databases across accounts in different regions and cloud platforms. Replication does not affect the connectivity or visibility of the accounts5. Option B is incorrect because provisioning a unique company Tri-Secret Secure key does not affect the network or authentication requirements. Tri-Secret Secure is a feature of Snowflake that allows customers to manage their own encryption keys for data at rest in Snowflake, using a combination of three secrets: a master key, a service key, and a security password. Tri-Secret Secure provides an additional layer of security and control over the data encryption and decryption process, but it does not enable private connectivity or SSO6. Option E is incorrect because using a proxy Snowflake account outside the VPN, enabling client redirect for user logins, is not a supported or recommended way of meeting the requirements. Client redirect is a feature of Snowflake that allows customers to connect to a different Snowflake account than the one specified in the connection string. This feature is useful for scenarios such as cross-region failover, data sharing, and account migration, but it does not provide private connectivity or SSO7.

Reference: AWS PrivateLink

& Snowflake | Snowflake Documentation, Azure Private Link & Snowflake | Snowflake Documentation, Google Cloud Private Service Connect & Snowflake | Snowflake Documentation, Overview of Federated Authentication and SSO | Snowflake Documentation, Replicating Databases Across Multiple Accounts | Snowflake Documentation, Tri-Secret Secure | Snowflake Documentation, Redirecting Client Connections | Snowflake Documentation

Question #147

An Architect is using SnowCD to investigate a connectivity issue.

Which system function will provide a list of endpoints that the network must be able to access to use a specific Snowflake account, leveraging private connectivity?

A . SYSTEMSALLOWLIST ()
B . SYSTEMSGET_PRIVATELINK
C . SYSTEMSAUTHORIZE_PRIVATELINK
D . SYSTEMSALLOWLIST_PRIVATELINK ()

Reveal Solution Hide Solution

Correct Answer: B
B

Explanation:

The SYSTEM$GET_PRIVATELINK function is used to retrieve the list of Snowflake service endpoints that need to be accessible when configuring private connectivity (such as AWS PrivateLink or Azure Private Link) for a Snowflake account. The function returns information necessary for setting up the networking infrastructure that allows secure and private access to Snowflake without using the public internet. SnowCD can then be used to verify connectivity to these endpoints.

Question #148

What transformations are supported in the below SQL statement? (Select THREE).

CREATE PIPE … AS COPY … FROM (…)

A . Data can be filtered by an optional where clause.
B . Columns can be reordered.
C . Columns can be omitted.
D . Type casts are supported.
E . Incoming data can be joined with other tables.
F . The ON ERROR – ABORT statement command can be used.

Reveal Solution Hide Solution

Correct Answer: A, B, C
A, B, C

Explanation:

The SQL statement is a command for creating a pipe in Snowflake, which is an object that defines the COPY INTO <table> statement used by Snowpipe to load data from an ingestion queue into tables1. The statement uses a subquery in the FROM clause to transform the data from the staged files before loading it into the table2.

The transformations supported in the subquery are as follows2:

Data can be filtered by an optional WHERE clause, which specifies a condition that must be satisfied by the rows returned by the subquery. For example: SQLAI-generated code. Review and use carefully. More info on FAQ.

create pipe mypipe as

copy into mytable

from (

select * from @mystage

where col1 = ‘A’ and col2 > 10

);

Columns can be reordered, which means changing the order of the columns in the subquery to match the order of the columns in the target table. For example: SQLAI-generated code. Review and use carefully. More info on FAQ.

create pipe mypipe as

copy into mytable (col1, col2, col3)

from (

select col3, col1, col2 from @mystage

);

Columns can be omitted, which means excluding some columns from the subquery that are not

needed in the target table. For example:

SQLAI-generated code. Review and use carefully. More info on FAQ.

create pipe mypipe as

copy into mytable (col1, col2)

from (

select col1, col2 from @mystage

);

The other options are not supported in the subquery because2:

Type casts are not supported, which means changing the data type of a column in the subquery. For example, the following statement will cause an error: SQLAI-generated code. Review and use carefully. More info on FAQ.

create pipe mypipe as

copy into mytable (col1, col2)

from (

select col1::date, col2 from @mystage

);

Incoming data can not be joined with other tables, which means combining the data from the staged

files with the data from another table in the subquery. For example, the following statement will

cause an error:

SQLAI-generated code. Review and use carefully. More info on FAQ.

create pipe mypipe as

copy into mytable (col1, col2, col3)

from (

select s.col1, s.col2, t.col3 from @mystage s

join othertable t on s.col1 = t.col1

);

The ON ERROR – ABORT statement command can not be used, which means aborting the entire load operation if any error occurs. This command can only be used in the COPY INTO <table> statement, not in the subquery.

For example, the following statement will cause an error: SQLAI-generated code. Review and use carefully. More info on FAQ.

create pipe mypipe as

copy into mytable

from (

select * from @mystage

on error abort

);

Reference: 1: CREATE PIPE | Snowflake Documentation

2: Transforming Data During a Load | Snowflake Documentation

Question #148

What transformations are supported in the below SQL statement? (Select THREE).

CREATE PIPE … AS COPY … FROM (…)

A . Data can be filtered by an optional where clause.
B . Columns can be reordered.
C . Columns can be omitted.
D . Type casts are supported.
E . Incoming data can be joined with other tables.
F . The ON ERROR – ABORT statement command can be used.

Reveal Solution Hide Solution

Correct Answer: A, B, C
A, B, C

Explanation:

The SQL statement is a command for creating a pipe in Snowflake, which is an object that defines the COPY INTO <table> statement used by Snowpipe to load data from an ingestion queue into tables1. The statement uses a subquery in the FROM clause to transform the data from the staged files before loading it into the table2.

The transformations supported in the subquery are as follows2:

Data can be filtered by an optional WHERE clause, which specifies a condition that must be satisfied by the rows returned by the subquery. For example: SQLAI-generated code. Review and use carefully. More info on FAQ.

create pipe mypipe as

copy into mytable

from (

select * from @mystage

where col1 = ‘A’ and col2 > 10

);

Columns can be reordered, which means changing the order of the columns in the subquery to match the order of the columns in the target table. For example: SQLAI-generated code. Review and use carefully. More info on FAQ.

create pipe mypipe as

copy into mytable (col1, col2, col3)

from (

select col3, col1, col2 from @mystage

);

Columns can be omitted, which means excluding some columns from the subquery that are not

needed in the target table. For example:

SQLAI-generated code. Review and use carefully. More info on FAQ.

create pipe mypipe as

copy into mytable (col1, col2)

from (

select col1, col2 from @mystage

);

The other options are not supported in the subquery because2:

Type casts are not supported, which means changing the data type of a column in the subquery. For example, the following statement will cause an error: SQLAI-generated code. Review and use carefully. More info on FAQ.

create pipe mypipe as

copy into mytable (col1, col2)

from (

select col1::date, col2 from @mystage

);

Incoming data can not be joined with other tables, which means combining the data from the staged

files with the data from another table in the subquery. For example, the following statement will

cause an error:

SQLAI-generated code. Review and use carefully. More info on FAQ.

create pipe mypipe as

copy into mytable (col1, col2, col3)

from (

select s.col1, s.col2, t.col3 from @mystage s

join othertable t on s.col1 = t.col1

);

The ON ERROR – ABORT statement command can not be used, which means aborting the entire load operation if any error occurs. This command can only be used in the COPY INTO <table> statement, not in the subquery.

For example, the following statement will cause an error: SQLAI-generated code. Review and use carefully. More info on FAQ.

create pipe mypipe as

copy into mytable

from (

select * from @mystage

on error abort

);

Reference: 1: CREATE PIPE | Snowflake Documentation

2: Transforming Data During a Load | Snowflake Documentation

Question #150

A user named USER_01 needs access to create a materialized view on a schema EDW. STG_SCHEMA.

How can this access be provided?

A . GRANT CREATE MATERIALIZED VIEW ON SCHEMA EDW.STG_SCHEMA TO USER USER_01;
B . GRANT CREATE MATERIALIZED VIEW ON DATABASE EDW TO USER USERJD1;
C . GRANT ROLE NEW_ROLE TO USER USER_01;
GRANT CREATE MATERIALIZED VIEW ON SCHEMA ECW.STG_SCHEKA TO NEW_ROLE;
D . GRANT ROLE NEW_ROLE TO USER_01;
GRANT CREATE MATERIALIZED VIEW ON EDW.STG_SCHEMA TO NEW_ROLE;

Reveal Solution Hide Solution

Correct Answer: A
A

Explanation:

The correct answer is A because it grants the specific privilege to create a materialized view on the schema EDW.STG_SCHEMA to the user USER_01 directly.

Option B is incorrect because it grants the privilege to create a materialized view on the entire database EDW, which is too broad and unnecessary. Also, there is a typo in the user name (USERJD1 instead of USER_01).

Option C is incorrect because it grants the privilege to create a materialized view on a different schema (ECW.STG_SCHEKA instead of EDW.STG_SCHEMA). Also, there is no need to create a new role for this purpose.

Option D is incorrect because it grants the privilege to create a materialized view on an invalid object (EDW.STG_SCHEMA is not a valid schema name, it should be EDW.STG_SCHEMA). Also, there is no need to create a new role for this purpose.

Reference: Snowflake Documentation: CREATE MATERIALIZED VIEW

Snowflake Documentation: Working with Materialized Views

[Snowflake Documentation: GRANT Privileges on a Schema]