Snowflake ARA-C01 - SnowPro Advanced: Architect Certification Exam

The requirement is for the data to be accessible as quickly as possible after it arrives in the external stage with minimal coding effort.

Option A:Snowpipe with auto-ingest is a service that continuously loads data as it arrives in the stage. With auto-ingest, Snowpipe automatically detects new files as they arrive in a cloud stage and loads the data into the specified Snowflake table with minimal delay and no intervention required. This is an ideal low-maintenance solution for the given scenario where files are arriving at a very high frequency.

Option E:Using a combination of a task and a stream allows for real-time change data capture in Snowflake. A stream records changes (inserts, updates, and deletes) made to a table, and a task can be scheduled to trigger on a very short interval, ensuring that changes are processed into the dashboard tables as they occur.

A healthcare company that handles PHI data must ensure compliance with relevant privacy standards, such as HIPAA, HITRUST, and GDPR. Snowflake provides several features and best practices to help customers meet their data protection and compliance requirements1.

One best practice recommendation is to use, at minimum, the Business Critical edition of Snowflake. This edition provides the highest level of data protection and security, including end-to-end encryption with customer-managed keys, enhanced object-level security, and HIPAA and HITRUST compliance2. Therefore, option A is correct.

Another best practice recommendation is to create Dynamic Data Masking policies and apply them to columns that contain PHI. Dynamic Data Masking is a feature that allows masking or redacting sensitive data based on the current user’s role. This way, only authorized users can view the unmasked data, while others will see masked values, such as NULL, asterisks, or random characters3. Therefore, option B is correct.

A third best practice recommendation is to use the External Tokenization feature to obfuscate sensitive data. External Tokenization is a feature that allows replacing sensitive data with tokens that are generated and stored by an external service, such as Protegrity. This way, the original data is never stored or processed by Snowflake, and only authorized users can access the tokenized data through the external service4. Therefore, option D is correct.

Option C is incorrect, because the Internal Tokenization feature is not available in Snowflake. Snowflake does not provide any native tokenization functionality, but only supports integration with external tokenization services4.

Option E is incorrect, because rewriting SQL queries to eliminate projections of PHI data based on current_role() is not a best practice. This approach is error-prone, inefficient, and hard to maintain. A better alternative is to use Dynamic Data Masking policies, which can automatically mask data based on the user’s role without modifying the queries3.

Option F is incorrect, because avoiding sharing data with partner organizations is not a best practice. Snowflake enables secure and governed data sharing with internal and external consumers, such as business units, customers, or partners. Data sharing does not involve copying or moving data, but only granting access privileges to the shared objects. Data sharing can also leverage Dynamic Data Masking and External Tokenization features to protect sensitive data5.

 Snowflake’s Security & Compliance Reports : Snowflake Editions : Dynamic Data Masking : External Tokenization : Secure Data Sharing

The correct answer is option B. This query is designed to assess the need for a multi-cluster warehouse by examining the queuing time (AVG_QUEUED_LOAD) on different days and virtual warehouses. When theAVG_QUEUED_LOADis greater than zero, it suggests that queries are waiting for resources, which can be an indicator that performance might be improved by using a multi-cluster warehouse to handle the workload more efficiently. By grouping by date and warehouse name and filtering on the sum of the average queued load being greater than zero, the query identifies specific days and warehouses where the workload exceeded the available compute resources. This information is valuable when considering scaling out warehouses to multi-cluster configurations for improved performance.

Zero-copy cloning is a Snowflake feature that can be used to save quarter-end financial results for the previous six years. Zero-copy cloning allows creating a copy of a database, schema, table, or view without duplicating the data or metadata. The clone shares the same data files as the original object, but tracks any changes made to the clone or the original separately. Zero-copy cloning can be used to create snapshots of data at different points in time, such as quarter-end financial results, and preserve them for future analysis or comparison. Zero-copy cloning is fast, efficient, and does not consume any additional storage space unless the data is modified1.

Zero-Copy Cloning | Snowflake Documentation

 A star schema model is a type of dimensional data model that consists of a single fact table and multiple dimension tables. A 3NF model is a type of relational data model that follows the third normal form, which eliminates data redundancy and ensures referential integrity. A Snowflake Architect might use a star schema model rather than a 3NF model when designing a data architecture to run in Snowflake for the following reasons:

A star schema model is more suitable for analytical queries that require aggregating and slicing data across different dimensions, such as those performed by a BI tool. A 3NF model is more suitable for transactional queries that require inserting, updating, and deleting individual records.

A star schema model is simpler and faster to query than a 3NF model, as it involves fewer joins and less complex SQL statements. A 3NF model is more complex and slower to query, as it involves more joins and more complex SQL statements.

A star schema model can provide a simple flattened single view of the data to a particular group of end users, such as business analysts or data scientists, who need to explore and visualize the data. A 3NF model can provide a more detailed and normalized view of the data to a different group of end users, such as application developers or data engineers, who need to maintain and update the data.

The other options are not valid reasons for choosing a star schema model over a 3NF model in Snowflake:

Snowflake can handle the joins implied in a 3NF data model, as it supports ANSI SQL and has a powerful query engine that can optimize and execute complex queries efficiently.

The Architect can use both star schema and 3NF models to remove data duplication from the data stored in Snowflake, as both models can enforce data integrity and avoid data anomalies. However, the trade-off is that a star schema model may have more data redundancy than a 3NF model, as it denormalizes the data for faster query performance, while a 3NF model may have less data redundancy than a star schema model, as it normalizes the data for easier data maintenance.

The Architect can use both star schema and 3NF models to design a landing zone to receive raw data into Snowflake, as both models can accommodate different types of data sources and formats. However, the choice of the model may depend on the purpose and scope of the landing zone, such as whether it is a temporary or permanent storage, whether it is a staging area or a data lake, and whether it is a single source or a multi-source integration.

Snowflake Architect Training

Data Modeling: Understanding the Star and Snowflake Schemas

Data Vault vs Star Schema vs Third Normal Form: Which Data Model to Use?

Star Schema vs Snowflake Schema: 5 Key Differences

Dimensional Data Modeling - Snowflake schema

Star schema vs Snowflake Schema

 These two features are relevant for modeling using Data Vault on Snowflake. Data Vault is a data modeling approach that organizes data into hubs, links, and satellites. Data Vault is designed to enable high scalability, flexibility, and performance for data integration and analytics. Snowflake is a cloud data platform that supports various data modeling techniques, including Data Vault. Snowflake provides some features that can enhance the Data Vault modeling, such as:

Snowflake’s support of multi-table inserts into the data model’s Data Vault tables. Multi-table inserts (MTI) are a feature that allows inserting data from a single query into multiple tables in a single DML statement. MTI can improve the performance and efficiency of loading data into Data Vault tables, especially for real-time or near-real-time data integration. MTI can also reduce the complexity and maintenance of the loading code, as well as the data duplication and latency12.

Scaling up the virtual warehouses will support parallel processing of new source loads. Virtual warehouses are a feature that allows provisioning compute resources on demand for data processing. Virtual warehouses can be scaled up or down by changing the size of the warehouse, which determines the number of servers in the warehouse. Scaling up the virtual warehouses can improve the performance and concurrency of processing new source loads into Data Vault tables, especially for large or complex data sets. Scaling up the virtual warehouses can also leverage the parallelism and distribution of Snowflake’s architecture, which can optimize the data loading and querying34.

Snowflake Documentation: Multi-table Inserts

Snowflake Blog: Tips for Optimizing the Data Vault Architecture on Snowflake

Snowflake Documentation: Virtual Warehouses

Snowflake Blog: Building a Real-Time Data Vault in Snowflake

Client Redirect is a Snowflake feature designed to support business continuity and disaster recovery in multi-account, multi-region architectures. When configured, it allows client applications to automatically connect to a secondary Snowflake account if the primary account becomes unavailable. This secondary account must be part of a configured failover group and promoted to act as the primary during a failover event.

The correct behavior of Client Redirect is to redirect client connections transparently to another Snowflake account—typically in a different region—where replicated data and objects are available and the account is acting as the primary (Answer D). This minimizes downtime and avoids the need for manual connection string updates across applications.

The feature does not simply provide alternate login URLs, nor does it dynamically route queries across multiple active accounts. It also does not automatically select the “closest” region; redirection follows the configured failover topology. For SnowPro Architect candidates, this question emphasizes understanding Snowflake’s native disaster recovery mechanisms and how Client Redirect integrates with failover groups.

=========

QUESTION NO: 47 [Security and Access Management]

A call center processes sensitive PII and PHI data. Requirements:

Object owners and privileged roles must see sensitive columns.

Pre-tokenized data must be de-tokenized at query time.What should be used? (Select TWO).

A. Object tagging

B. Materialized views

C. External tokenization

D. Dynamic Data Masking

E. Role-Based Access Control (RBAC)

Answer: D, E

Dynamic Data Masking is a Snowflake security feature that conditionally masks or reveals column values at query time based on the executing role (Answer D). This allows privileged users (such as object owners or compliance roles) to see clear-text PII/PHI, while other users see masked or tokenized values. Because masking is evaluated at query time, it supports de-tokenization logic when the role is authorized.

Role-Based Access Control (RBAC) underpins this behavior by defining which roles are privileged and allowed to view unmasked data (Answer E). Masking policies are evaluated in the context of the active role, making RBAC essential for enforcing correct access.

Object tagging alone does not enforce access control. Materialized views duplicate data and do not support dynamic de-tokenization logic. External tokenization is not required when Snowflake masking policies can handle conditional reveal. This question tests SnowPro Architect knowledge of advanced column-level security and privacy controls.

=========

QUESTION NO: 48

(Skipped — no Question 48 in the original set)

=========

QUESTION NO: 49 [Snowflake Data Engineering]

A table contacts_data contains a VARIANT column with nested JSON data.

Which queries correctly retrieve the first name value "Snow"? (Select TWO).

A. SELECT o[customer][name][first]::STRING FROM contacts_data;

B. SELECT o['customer']['name']['first']::STRING FROM contacts_data;

C. SELECT o:['customer']:['name']:['first']::STRING FROM contacts_data;

D. SELECT o:Customer.Name.First::STRING FROM contacts_data;

E. SELECT o:customer.name.first::STRING FROM contacts_data;

Answer: B, E

Snowflake supports multiple syntaxes for accessing elements in VARIANT columns. Bracket notation with quoted keys is valid for object traversal, making option B correct. Dot notation with colon syntax is also valid and commonly used for readability, as shown in option E.

Option A is invalid because unquoted identifiers inside brackets are interpreted as column names, not JSON keys. Option C contains incorrect syntax combining brackets and colons. Option D is invalid because JSON key names are case-sensitive unless quoted, and the provided structure uses lowercase keys.

Understanding VARIANT traversal syntax is a core SnowPro Architect skill for semi-structured data handling and transformation.

=========

QUESTION NO: 50 [Cost Control and Resource Management]

An Architect is evaluating compute and storage costs related to performance optimization techniques.

Which Snowflake performance optimizations have storage costs associated with them? (Select THREE).

A. Rekeying

B. Materialized views

C. Clustering a table

D. Query Acceleration Service

E. Search Optimization Service

F. Multi-cluster virtual warehouse

Answer: B, C, E

Some Snowflake performance features require additional storage to maintain auxiliary data structures. Materialized views store precomputed query results and therefore consume storage (Answer B). Clustering a table can increase storage usage due to additional micro-partitions created during reclustering operations (Answer C).

Search Optimization Service builds and maintains search access paths that persist as additional metadata structures, incurring storage costs (Answer E). In contrast, Query Acceleration Service and multi-cluster warehouses affect compute usage only. Rekeying is a security operation and does not create separate storage structures.

This question tests SnowPro Architect understanding of cost tradeoffs between compute and storage when selecting optimization techniques.

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