Snowflake Updated ARA-C01 Exam Questions and Answers by everlyn

This question tests several core Snowflake semi-structured data concepts that are explicitly part of the SnowPro Architect exam scope: working with VARIANT data, array handling, and the use of LATERAL FLATTEN. The activities element in the JSON structure is an array, meaning it must be flattened before individual attributes such as activityNumber and winner can be accessed. Option A is invalid because it attempts to directly reference fields inside an array without flattening it.

Option B correctly uses LATERAL FLATTEN on json:activities, which produces one row per activity. The alias p.value represents each array element, allowing access to activityNumber and winner. The IFF expression then compares the activity’s winner value with leader.number. When they match, the query returns leader.name.default; otherwise, it returns follower.name.default. Casting the result to VARCHAR ensures a proper scalar output.

Option C is incorrect because it attempts to return full JSON objects (leader or follower) rather than the nested name.default value. Option D uses OUTER => TRUE, which is unnecessary in this case because the activities array is guaranteed to exist; while it would still work, Snowflake exam questions typically expect the most precise and minimal correct solution.

 According to the SnowPro Advanced: Architect documents and learning resources, the minimum object privileges required for the Snowpipe user to execute Snowpipe are:

OWNERSHIP on the named pipe. This privilege allows the Snowpipe user to create, modify, and drop the pipe object that defines the COPY statement for loading data from the stage to the table1.

USAGE and READ on the named stage. These privileges allow the Snowpipe user to access and read the data files from the stage that are loaded by Snowpipe2.

USAGE on the target database and schema. These privileges allow the Snowpipe user to access the database and schema that contain the target table3.

INSERT and SELECT on the target table. These privileges allow the Snowpipe user to insert data into the table and select data from the table4.

The other options are incorrect because they do not specify the minimum object privileges required for the Snowpipe user to execute Snowpipe. Option A is incorrect because it does not include the READ privilege on the named stage, which is required for the Snowpipe user to read the data files from the stage. Option C is incorrect because it does not include the OWNERSHIP privilege on the named pipe, which is required for the Snowpipe user to create, modify, and drop the pipe object. Option D is incorrect because it does not include the OWNERSHIP privilege on the named pipe or the READ privilege on the named stage, which are both required for the Snowpipe user to execute Snowpipe. References: CREATE PIPE | Snowflake Documentation, CREATE STAGE | Snowflake Documentation, CREATE DATABASE | Snowflake Documentation, CREATE TABLE | Snowflake Documentation

The Snowflake Connector for Python is designed to integrate Snowflake with Python-based analytics, ETL, and application workloads. One key capability is its support for both synchronous and asynchronous query execution, which allows architects to design scalable pipelines and applications that can submit long-running queries without blocking execution threads (Answer B). This is particularly important for large data sets and complex analytical workloads, where asynchronous execution improves throughput and application responsiveness.

Additionally, the connector automatically converts Snowflake data types into native Python data types wherever possible (Answer D). For example, VARCHAR values are returned as Python strings, numeric values as Python numeric types, and timestamps as Python datetime objects. This default behavior simplifies downstream processing and analysis, eliminating the need for manual casting or parsing in most use cases.

The connector does not convert all values to strings by default, nor does it specifically convert NUMBER to DECIMAL as a required behavior; instead, type conversion is handled intelligently to match Python equivalents. While cursors are used to execute queries, this is standard DB-API behavior and not a distinguishing feature for performance or architecture decisions. For SnowPro Architect candidates, understanding these connector capabilities is essential when designing Python-based data engineering or analytics solutions on Snowflake.

=========

QUESTION NO: 7 [Security and Access Management]

Which parameters can only be set at the account level? (Select TWO).

A. DATA_RETENTION_TIME_IN_DAYS

B. ENFORCE_SESSION_POLICY

C. MAX_CONCURRENCY_LEVEL

D. PERIODIC_DATA_REKEYING

E. TIMESTAMP_INPUT_FORMAT

Answer: B, D

Snowflake parameters exist at different levels of the hierarchy, including account, user, session, warehouse, database, schema, and object levels. However, some parameters are intentionally restricted to the account level because they enforce global security or compliance behavior across the entire Snowflake environment.

ENFORCE_SESSION_POLICY is an account-level parameter that determines whether session policies (such as authentication or session controls) are enforced across all users. Because this impacts authentication and session behavior globally, it cannot be overridden at lower scopes (Answer B).

PERIODIC_DATA_REKEYING is another account-level-only parameter. It controls automatic re-encryption (rekeying) of data to meet strict compliance and security requirements. Rekeying affects all encrypted data in the account and must therefore be centrally managed at the account level (Answer D).

By contrast, DATA_RETENTION_TIME_IN_DAYS can be set at multiple levels (account, database, schema, and table). MAX_CONCURRENCY_LEVEL is a warehouse-level parameter, and TIMESTAMP_INPUT_FORMAT can be set at account, user, or session levels. From a SnowPro Architect perspective, understanding which parameters are global versus scoped is critical for designing secure, compliant, and governable Snowflake architectures.

=========

QUESTION NO: 8 [Snowflake Data Engineering]

A MERGE statement is designed to return duplicated values of a column ID in a USING clause. The column ID is used in the merge condition. The MERGE statement contains these two clauses:

WHEN NOT MATCHED THEN INSERT

WHEN MATCHED THEN UPDATE

What will be the result when this query is run?

A. The MERGE statement will run successfully using the default parameter settings.

B. If the value of the ID is present in the target table, all occurrences will be updated.

C. If the value of the ID is present in the target table, only the first occurrence will be updated.

D. If the ERROR_ON_NONDETERMINISTIC_MERGE = FALSE parameter is set, the MERGE statement will run successfully.

Answer: D

In Snowflake, MERGE statements require deterministic behavior when matching rows between the source (USING clause) and the target table. If the USING clause contains duplicate values for the join condition (in this case, column ID), Snowflake cannot deterministically decide which source row should update or insert into the target. By default, this results in an error to prevent unintended data corruption.

Snowflake provides the parameter ERROR_ON_NONDETERMINISTIC_MERGE to control this behavior. When set to TRUE (the default), Snowflake raises an error if nondeterministic matches are detected. When this parameter is explicitly set to FALSE, Snowflake allows the MERGE statement to run successfully even when duplicate keys exist in the source, accepting the nondeterministic outcome (Answer D).

Snowflake does not guarantee updating all or only the first occurrence in such cases; instead, the behavior is undefined unless the parameter is adjusted. This question tests an architect’s understanding of data correctness, deterministic processing, and safe data engineering practices—key topics within the SnowPro Architect exam scope.

=========

QUESTION NO: 9 [Snowflake Data Engineering]

An Architect needs to define a table structure for an unfamiliar semi-structured data set. The Architect wants to identify a list of distinct key names present in the semi-structured objects.

What function should be used?

A. FLATTEN with the RECURSIVE argument

B. INFER_SCHEMA

C. PARSE_JSON

D. RESULT_SCAN

Answer: A

When working with unfamiliar semi-structured data such as JSON, a common first step is to explore its structure and identify all possible keys. Snowflake’s FLATTEN function is specifically designed to explode VARIANT, OBJECT, or ARRAY data into relational form. Using the RECURSIVE option allows FLATTEN to traverse nested objects and arrays, returning all nested keys regardless of depth (Answer A).

This approach enables architects to query and aggregate distinct key names, making it ideal for schema discovery and exploratory analysis. INFER_SCHEMA, by contrast, is used primarily with staged files to infer column definitions for external tables or COPY operations, not for exploring existing VARIANT data already stored in tables. PARSE_JSON simply converts a string into a VARIANT type and does not help identify keys. RESULT_SCAN is used to query the results of a previously executed query and is unrelated to schema discovery.

For SnowPro Architect candidates, this highlights an important semi-structured data design pattern: using FLATTEN (often with RECURSIVE) to explore, profile, and understand evolving data structures before committing to a relational schema or transformation pipeline.

=========

QUESTION NO: 10 [Security and Access Management]

A global retail company must ensure comprehensive data governance, security, and compliance with various international regulations while using Snowflake for data warehousing and analytics.

What should an Architect do to meet these requirements? (Select TWO).

A. Create a network policy at the column level to secure the data.

B. Use column-level security to restrict access to specific columns.

C. Store encryption keys on an external server to manage encryption manually.

D. Implement Role-Based Access Control (RBAC) to assign roles and permissions.

E. Enable Secure Data Sharing with external partners for collaborative purposes.

Answer: B, D

Snowflake provides built-in governance and security mechanisms that align with global regulatory requirements. Column-level security—implemented through features such as dynamic data masking and row access policies—allows architects to restrict access to sensitive data at a granular level based on roles or conditions (Answer B). This is essential for compliance with regulations such as GDPR, HIPAA, and similar frameworks that require limiting access to personally identifiable or sensitive data.

Role-Based Access Control (RBAC) is the foundation of Snowflake’s security model and is critical for governing who can access which data and perform which actions (Answer D). By assigning privileges to roles instead of users, organizations can centrally manage permissions, enforce separation of duties, and audit access more effectively.

Snowflake does not support column-level network policies, and encryption keys are managed by Snowflake (or via Tri-Secret Secure), not manually by customers. Secure Data Sharing is useful for collaboration but is not a core requirement for governance and compliance in this scenario. For the SnowPro Architect exam, mastering RBAC and column-level security is essential for designing compliant and secure Snowflake architectures.

Snowflake supports cloning of various objects, such as databases, schemas, tables, stages, file formats, sequences, streams, tasks, and roles. Cloning creates a copy of an existing object in the system without copying the data or metadata. Cloning is also known as zero-copy cloning1.

Among the objects listed in the question, the following ones can be cloned in Snowflake:

Permanent table: A permanent table is a type of table that has a Fail-safe period and a Time Travel retention period of up to 90 days. A permanent table can be cloned using the CREATE TABLE … CLONE command2. Therefore, option A is correct.

Transient table: A transient table is a type of table that does not have a Fail-safe period and can have a Time Travel retention period of either 0 or 1 day. A transient table can also be cloned using the CREATE TABLE … CLONE command2. Therefore, option B is correct.

External table: An external table is a type of table that references data files stored in an external location, such as Amazon S3, Google Cloud Storage, or Microsoft Azure Blob Storage. An external table can be cloned using the CREATE EXTERNAL TABLE … CLONE command3. Therefore, option D is correct.

The following objects listed in the question cannot be cloned in Snowflake:

Temporary table: A temporary table is a type of table that is automatically dropped when the session ends or the current user logs out. Temporary tables do not support cloning4. Therefore, option C is incorrect.

Internal stage: An internal stage is a type of stage that is managed by Snowflake and stores files in Snowflake’s internal cloud storage. Internal stages do not support cloning5. Therefore, option E is incorrect.

 Cloning Considerations : CREATE TABLE … CLONE : CREATE EXTERNAL TABLE … CLONE : Temporary Tables : Internal Stages