Pass the Snowflake SnowPro Advanced: Architect ARA-C01 Questions and answers with Dumpstech

The requirements state that the data analysts need to be able to create and modify database objects and load data, but should not be able to manage access for users outside of their role.

Option C:By making each schema within the database a managed access schema and having them owned by SYSADMIN, the ability to grant privileges on the schema's objects is strictly controlled. Managed access schemas limit the granting of privileges to the role specified as the owner of the schema, in this case, SYSADMIN. The ANALYST_ROLE can be granted the privileges necessary to create and modify objects within these schemas, satisfying the requirement for the analysts to perform their tasks without being able to extend access beyond their role.

 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 three statements are true according to the Snowflake documentation and the web search results. A row access policy is a feature that allows filtering rows based on user-defined conditions. A row access policy can be applied to an external table, which is a table that reads data from external files in a stage. However, there are some limitations and considerations for using row access policies with external tables.

An external table can be created with a row access policy by using the WITH ROW ACCESS POLICY clause in the CREATE EXTERNAL TABLE statement. The policy can be applied to theVALUE column, which is the column that contains the raw data from the external files in a VARIANT data type1.

A row access policy can also be applied to the VALUE column of an existing external table by using the ALTER TABLE statement with the SET ROW ACCESS POLICY clause2.

A row access policy cannot be directly added to a virtual column of an external table. A virtual column is a column that is derived from the VALUE column using an expression. To apply a row access policy to a virtual column, the policy must be applied to the VALUE column and the expression must be repeated in the policy definition3.

External tables are not supported as mapping tables in a row access policy. A mapping table is a table that is used to determine the access rights of users or roles based on some criteria. Snowflake does not support using an external table as a mapping table because it may cause performance issues or errors4.

While cloning a database, Snowflake clones the row access policy, but not the external table. Therefore, the policy in the cloned database refers to a table that is not present in the cloned database. To avoid this issue, the external table must be manually cloned or recreated in the cloned database4.

A row access policy can be applied to a view created on top of an external table. The policy can be applied to the view itself or to the underlying external table. However, if the policy is applied to the view, the view must be a secure view, which is a view that hides the underlying data and the view definition from unauthorized users5.

CREATE EXTERNAL TABLE | Snowflake Documentation

ALTER EXTERNAL TABLE | Snowflake Documentation

Understanding Row Access Policies | Snowflake Documentation

Snowflake Data Governance: Row Access Policy Overview

Secure Views | Snowflake Documentation

When a new table (table_6) is added to a schema in the provider's account that is part of a data share, the consumer will not automatically see the new table. The consumer will only be able to access the new table once the appropriate privileges are granted by the provider. The correct process, as outlined in option D, involves using the provider’s ACCOUNTADMIN role to grant USAGE privileges on the database and schema, followed by SELECT privileges on the new table, specifically to the share that includes the consumer's database. This ensures that the consumer account can access the new table under the established data sharing setup.

To change the edition of a Snowflake account, an organization administrator (ORGADMIN) cannot directly alter the account settings through SQL commands or the Snowflake interface. The proper procedure is to contact Snowflake Support to request an edition change for the account. This ensures that the change is managed correctly and aligns with Snowflake’s operational protocols.

The correct answer is B because the compilation time is the time it takes for the optimizer to create an optimal query plan for the efficient execution of the query. The compilation time depends on the complexity of the query, such as the number of tables, columns, joins, filters, aggregations, subqueries, etc. The more complex the query, the longer it takes to compile.

Option A is incorrect because the query processing time is not affected by the size of the dataset, but by the size of the virtual warehouse. Snowflake automatically scales the compute resources to match the data volume and parallelizes the query execution. The size of the dataset may affect the execution time, but not the compilation time.

Option C is incorrect because the query queue time is not part of the compilation time or the execution time. It is a separate metric that indicates how long the query waits for a warehouse slot before it starts running. The query queue time depends on the warehouse load, concurrency, and priority settings.

Option D is incorrect because the query remote IO time is not part of the compilation time or the execution time. It is a separate metric that indicates how long the query spends reading data from remote storage, such as S3 or Azure Blob Storage. The query remote IO time depends on the network latency, bandwidth, and caching efficiency. References:

Understanding Why Compilation Time in Snowflake Can Be Higher than Execution Time: This article explains why the total duration (compilation + execution) time is an essential metric to measure query performance in Snowflake. It discusses the reasons for the long compilation time, including query complexity and the number of tables and columns.

Exploring Execution Times: This document explains how to examine the past performance of queries and tasks using Snowsight or by writing queries against views in the ACCOUNT_USAGE schema. It also describes the different metrics and dimensions that affect query performance, such as duration, compilation, execution, queue, and remote IO time.

What is the “compilation time” and how to optimize it?: This community post provides some tips and best practices on how to reduce the compilation time, such as simplifying the query logic, using views or common table expressions, and avoiding unnecessary columns or joins.

For global enterprises handling sensitive data, Snowflake architects must design for both secure access control and secure network connectivity. Network policies allow administrators to restrict access to Snowflake accounts based on approved IP address ranges, ensuring that only corporate offices or trusted networks can connect (Answer D). This is a core Snowflake security control and is frequently tested in the SnowPro Architect exam.

AWS PrivateLink provides private, non-internet-based connectivity between Snowflake and customer AWS VPCs (Answer E). This ensures that data traffic does not traverse the public internet, which is critical for meeting regional regulatory and compliance requirements related to data privacy and sovereignty. PrivateLink also simplifies security posture by reducing exposure to public endpoints.

While SAML authentication is important for identity management, it does not address secure data transport. Public endpoints with SSL are secure but do not meet stricter regulatory requirements when private connectivity is mandated. Snowflake does not provide detailed packet-level network traffic monitoring. Together, network policies and PrivateLink provide strong, compliant, and regionally secure Snowflake architectures.

=========

QUESTION NO: 17 [Cost Control and Resource Management]

An Architect plans to stream data using the Snowflake Connector for Kafka in Snowpipe.

What setting will optimize costs?

A. Set buffer.flush.time = 1.

B. Set buffer.count.records = 1.

C. Set buffer.size.bytes = 10 MB.

D. Maximize the number of micro-partitions.

Answer: C

When using the Snowflake Connector for Kafka with Snowpipe, cost efficiency depends on batching data into appropriately sized files before ingestion. Snowflake charges Snowpipe costs based on the number of files ingested and compute used for loading. Very small files significantly increase overhead and cost.

Setting buffer.size.bytes = 10 MB allows the connector to batch records into reasonably sized files before flushing them to Snowflake (Answer C). This strikes a balance between ingestion latency and cost efficiency and aligns with Snowflake best practices for streaming ingestion. Extremely small buffer sizes or very frequent flush intervals (such as 1 record or 1 second) lead to excessive file creation and higher Snowpipe costs.

Maximizing micro-partitions is not configurable directly and is counterproductive for cost and performance. For SnowPro Architect candidates, this question emphasizes the importance of batching and file sizing strategies when designing streaming ingestion pipelines with Kafka and Snowpipe.

=========

QUESTION NO: 18 [Security and Access Management]

An Architect is creating a new database role and is considering using the OR REPLACE keywords in the CREATE DATABASE ROLE command.

What should be considered before using OR REPLACE?

A. The dropped database role cannot be recreated.

B. Recreating a database role drops it from any shares it is granted to.

C. The OR REPLACE keywords are unsupported for database roles.

D. The database role can only be dropped by a role with MANAGE GRANTS privilege.

Answer: B

Using CREATE OR REPLACE DATABASE ROLE in Snowflake drops and recreates the role if it already exists. While this can simplify deployment scripts, it has important side effects that architects must understand. When a database role is dropped and recreated, all grants associated with that role—including grants to shares—are removed (Answer B). This can unintentionally break data sharing configurations or downstream access.

Although the role itself can be recreated, the loss of grants requires manual remediation, increasing operational risk. The OR REPLACE syntax is supported for database roles, and while privilege requirements matter, they are not the key risk being tested here.

For SnowPro Architect candidates, this question reinforces best practices around role lifecycle management and governance. In production environments, replacing roles should be done cautiously, with a clear understanding of grant dependencies and the potential impact on security and data sharing.

=========

QUESTION NO: 19 [Performance Optimization and Monitoring]

A large table is accessed by multiple teams using point-lookup queries on different columns. Query performance is poor.

What can be done to ensure good performance for all teams?

A. Build multiple materialized views with cluster keys on each column.

B. Build materialized views and allow Snowflake to replace the base table.

C. Use the search optimization service on the underlying table.

D. Create clustering keys using a combination of all lookup columns.

Answer: C

When multiple teams perform selective point-lookups on different columns of the same large table, traditional clustering is not effective because a table can have only one clustering key. Creating a composite clustering key across many columns often leads to poor clustering depth and limited pruning benefits.

The Search Optimization Service (SOS) is specifically designed for this scenario (Answer C). It enables fast point-lookups across multiple columns without requiring table reclustering. SOS maintains additional search access paths that accelerate highly selective predicates and is ideal when queries vary across many lookup columns.

Materialized views introduce additional storage, maintenance overhead, and operational complexity, and Snowflake does not automatically replace base tables with materialized views. This question tests an architect’s ability to choose the right optimization technique for multi-access-path workloads, a core SnowPro Architect competency.

=========

QUESTION NO: 20 [Snowflake Ecosystem and Integrations]

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

A. The Connector only works in AWS regions.

B. The Connector works with all file formats.

C. The Connector creates and manages its own stage, file format, and pipe objects.

D. Loads using the Connector have lower latency than Snowpipe and ingest data in real time.

Answer: C

The Snowflake Connector for Kafka simplifies streaming ingestion by automatically managing Snowflake objects required for ingestion. It creates and manages internal stages, file formats, and Snowpipe pipes without requiring manual configuration (Answer C). This reduces operational overhead and simplifies deployment.

The connector is cloud-agnostic and works across supported Snowflake clouds, not just AWS. It supports a defined set of formats (such as JSON and Avro), not all possible formats. While the connector enables near real-time ingestion, it still uses Snowpipe under the hood and does not guarantee lower latency than Snowpipe Streaming.

For SnowPro Architect candidates, this question emphasizes understanding how Snowflake-managed connectors abstract infrastructure details while still relying on Snowflake-native ingestion services.

This question evaluates deep understanding of Snowflake RBAC, managed access schemas, and privilege inheritance, all of which are core SnowPro Architect topics. The schema DEV_TEST_DB.SCHTEST is created WITH MANAGED ACCESS, meaning that only the schema owner (or a higher role in the hierarchy) can grant object privileges such as SELECT on tables within the schema.

The table CURRENCY is created by the role DEV_PROJ_OWN, making it the table owner. As the owner, DEV_PROJ_OWN implicitly has full privileges on the table, including SELECT. Therefore, option B succeeds because the role selecting the data owns the table.

In option A, DEV_PROJ_OWN explicitly grants SELECT on the table to ANALYST_PROJ. Since this grant is performed by the schema/table owner and the role ANALYST_PROJ already has USAGE on both the database and schema, the subsequent SELECT succeeds. This makes A valid.

Option C fails because SYSADMIN does not inherit privileges from DEV_SYSADMIN or DEV_PROJ_OWN; Snowflake role inheritance is directional and not lateral. Option D fails for the same reason—MAPPING_ROLE has no privileges on the database or schema. Option E fails because ACCOUNTADMIN does not automatically bypass RBAC; without explicit USAGE and SELECT, access is denied.

This design meets all the requirements for the data pipeline. Snowpipe is a feature that enables continuous data loading into Snowflake from object storage using event notifications. It is efficient, scalable, and serverless, meaning it does not require any infrastructure or maintenance from the user. Streams and tasks are features that enable automated data pipelines within Snowflake, using change data capture and scheduled execution. They are also efficient, scalable, and serverless, and they simplify the data transformation process. External functions are functions that can invoke external services or APIs from within Snowflake. They can be used to integrate with Amazon Comprehend and perform sentiment analysis on the data. The results can be written back to a Snowflake table using standard SQL commands. Snowflake Marketplace is a platform that allows data providers to share data with data consumers across different accounts, regions, and cloud platforms. It is a secure and easy way to make data publicly available to other companies.

Snowpipe Overview | Snowflake Documentation

Introduction to Data Pipelines | Snowflake Documentation

External Functions Overview | Snowflake Documentation

Snowflake Data Marketplace Overview | Snowflake Documentation

 A masking policy is a feature of Snowflake that allows masking sensitive data in query results based on the role of the user and the condition of the data. A masking policy can be applied to a column in a table or a view, and it can use another column in the same table or view as a conditional column. A conditional column is a column that determines whether the masking policy is applied or not based on its value1.

In this case, the requirement can be met by using a masking policy on the username column with event_timestamp as a conditional column. The masking policy can use a function that masks the username if the event_timestamp is older than six months based on the current date, and returns the original username otherwise. The masking policy canbe applied to the user_events table, and it will also be applied when creating views using the table or cloning the table2.

The other options are not correct because:

A. Using a masking policy on the username column using an entitlement table with valid dates would require creating another table that stores the valid dates for each username, and joining it with the user_events table in the masking policy function. This would add complexity and overhead to the masking policy, and it would not use the event_timestamp column as the condition for masking.

B. Using a row level policy on the user_events table using an entitlement table with valid dates would require creating another table that stores the valid dates for each username, and joining it with the user_events table in the row access policy function. This would filter out the rows that have event_timestamp older than six months based on the valid dates, instead of masking the username column. This would not meet the requirement of masking the username, and it would also reduce the visibility of the event data.

D. Using a secure view on the user_events table using a case statement on the username column would require creating a view that uses a case expression to mask the username column based on the event_timestamp column. This would meet the requirement of masking the username, but it would not be applied when cloning the table. A secure view is a view that prevents the underlying data from being exposed by queries on the view. However, a secure view does not prevent the underlying data from being exposed by cloning the table3.

1: Masking Policies | Snowflake Documentation

2: Using Conditional Columns in Masking Policies | Snowflake Documentation

3: Secure Views | Snowflake Documentation