ARA-C01 Exam Dumps - Snowflake SnowPro Advanced: Architect Questions and Answers

A. The Search Optimization Service is designed to accelerate the performance of queries that use filters on large tables. One of the key considerations for its effectiveness is using it with tables where the columns used in the filter conditions have a high number of distinct values, typically in the hundreds of thousands or more. This is because the service creates a map-reduce-like index on the column to speed up queries that use point lookups or range scans on that column. The more unique values there are, the more effective the index is at narrowing down the search space.

To create a read-only role for certain employees working in the human resources department, the role needs to have the following permissions on the hr_db database:

USAGE on the database: This allows the role to access the database and see its schemas and objects.

USAGE on all schemas in the database: This allows the role to access the schemas and see their objects.

SELECT on all tables in the database: This allows the role to query the data in the tables.

Option A is the correct answer because it grants the minimum permissions required for a read-only role on the hr_db database.

Option B is incorrect because SELECT on schemas is not a valid permission. Schemas only support USAGE and CREATE permissions.

Option C is incorrect because MODIFY on the database is not a valid permission. Databases only support USAGE, CREATE, MONITOR, and OWNERSHIP permissions. Moreover, USAGE on tables is not sufficient for querying the data. Tables support SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, and OWNERSHIP permissions.

Option D is incorrect because REFERENCES on tables is not relevant for querying the data. REFERENCES permission allows the role to create foreign key constraints on the tables.

https://docs.snowflake.com/en/user-guide/security-access-control-privileges.html#database-privileges

https://docs.snowflake.com/en/user-guide/security-access-control-privileges.html#schema-privileges

https://docs.snowflake.com/en/user-guide/security-access-control-privileges.html#table-privileges

This scenario tests understanding of Snowflake Time Travel and operational efficiency, both key topics in the SnowPro Architect exam. When a table is replaced using CREATE OR REPLACE TABLE, Snowflake treats the original table version as dropped but still recoverable within the Time Travel retention period. The goal is to recover the original data with the least effort and risk.

Option A is the most efficient solution. Using Time Travel with the BEFORE (STATEMENT => …) clause allows the Architect to query the exact state of the table immediately before the replacement occurred. This approach precisely targets the moment before the destructive operation, avoids guessing timestamps, and minimizes operational steps. It is also highly accurate, which is critical in production recovery scenarios.

Option B relies on a timestamp, which introduces risk if the timestamp is incorrect or ambiguous. Option C is invalid because UNDROP TABLE only works when a table has been explicitly dropped, not replaced. Option D introduces unnecessary steps and operational overhead, increasing the risk of mistakes.

Snowflake event tables capture logs, traces, and metrics generated by Snowflake features such as stored procedures, functions, and dynamic tables. When a database does not have an explicitly associated event table, Snowflake writes telemetry data to the default account-level event table, which is exposed through SNOWFLAKE.TELEMETRY.EVENTS.

Since the problematic stored procedure resides in marketing_db, and that database is not associated with its own event table, the relevant telemetry data will not appear in sales_db’s event table. Instead, it will be recorded in the account-level telemetry view. Querying SNOWFLAKE.TELEMETRY.EVENTS allows the Architect to analyze logs and traces across databases within the same account.

Creating additional event tables is unnecessary for investigation, and querying MARKETING_DB.TELEMETRY.EVENTS is invalid because no such association exists. From a SnowPro Architect perspective, this question validates understanding of event table scope, default telemetry behavior, and effective troubleshooting of Snowflake-native observability features.

=========

QUESTION NO: 27 [Snowflake Data Engineering]

A data ingestion pipeline loads data randomly throughout the day. A requirement states that data must be processed within 10 minutes of landing in Snowflake.

What is the MOST cost-effective solution?

A. Use a third-party orchestration tool to run the pipeline every 5 minutes.

B. Create views containing transformation logic.

C. Create a task that runs every 5 minutes and processes new rows.

D. Create a stream on the table and a task that runs every 5 minutes using SYSTEM$STREAM_HAS_DATA.

Answer: D

Streams and tasks are Snowflake’s native mechanism for incremental data processing. A stream tracks row-level changes (inserts, updates, deletes) on a table, while tasks enable scheduled or conditional execution of SQL statements.

Using SYSTEM$STREAM_HAS_DATA allows the task to run only when new data is available, avoiding unnecessary compute usage (Answer D). This makes the solution both timely—meeting the 10-minute SLA—and cost-efficient, as compute resources are consumed only when there is work to do.

Views alone do not materialize transformations and still require compute at query time. Third-party orchestration introduces unnecessary operational overhead and cost. Running a task on a fixed schedule without checking stream data would waste compute cycles.

This pattern—streams + tasks with conditional execution—is a core SnowPro Architect design pattern for efficient, low-latency data pipelines.

=========

QUESTION NO: 28 [Performance Optimization and Monitoring]

A query shows a high number of bytes spilled to remote storage. The query includes a WHERE clause that filters out a large percentage of rows.

What actions can reduce spillage and improve performance? (Select TWO).

A. Increase the size of the virtual warehouse.

B. Increase the maximum number of clusters.

C. Define a clustering key on columns used for selective filtering.

D. Define primary and foreign keys.

E. Decrease the data retention period.

Answer: A, C

Bytes spilled to remote storage indicate memory pressure during query execution. Increasing the warehouse size provides more memory and compute resources, reducing the likelihood of spilling (Answer A).

Additionally, defining clustering keys on highly selective filter columns improves micro-partition pruning, which reduces the volume of data scanned and processed in memory (Answer C). Together, these actions address both the root cause (too much data scanned) and the symptom (insufficient memory).

Increasing clusters improves concurrency, not per-query memory. Primary and foreign keys are informational only in Snowflake and do not affect execution plans. Retention settings have no impact on query execution.

This question reinforces SnowPro Architect performance diagnostics: understand both physical execution (memory, spill) and logical data access (pruning).

The masking policy defined in the image indicates that if a user has the PI_ANALYTICS role, they will be able to see the last 4 characters of the CREDITCARDNO column data in clear text. Otherwise, they will see ‘MASKED’. Since Snowflake system roles have not been granted any additional roles, they won’t have the PI_ANALYTICS role and therefore cannot view the last 4 characters of credit card numbers.

To apply a masking policy on a column in Snowflake, you need to use the ALTER TABLE … ALTER COLUMN command or the ALTER VIEW command and specify the policy name. For example, to apply the creditcardno_mask policy on the CREDITCARDNO column of the CREDITCARDINFO table, you can use the following command:

ALTER TABLE CREDITCARDINFO ALTER COLUMN CREDITCARDNO SET MASKING POLICY creditcardno_mask;

For more information on how to create and use masking policies in Snowflake, you can refer to the following resources:

CREATE MASKING POLICY: This document explains the syntax and usage of the CREATE MASKING POLICY command, which allows you to create a new masking policy or replace an existing one.

Using Dynamic Data Masking: This guide provides instructions on how to configure and use dynamic data masking in Snowflake, which is a feature that allows you to mask sensitive data based on the execution context of the user.

ALTER MASKING POLICY: This document explains the syntax and usage of the ALTER MASKING POLICY command, which allows you to modify the properties of an existing masking policy.

According to the SnowPro Advanced: Architect documents and learning resources, the best way to enable optimal clustering to enhance performance for different access paths on a given table is to create multiple materialized views with different cluster keys. A materialized view is a pre-computed result set that is derived from a query on one or more base tables. A materialized view can be clustered by specifying a clustering key, which is a subset of columns or expressions that determines how the data in the materialized view is co-located in micro-partitions. By creating multiple materialized views with different cluster keys, an Architect can optimize the performance of queries that use different access paths on the same base table. For example, if a base table has columns A, B, C, and D, and there are queries that filter on A and B, or on C and D, or on A and C, the Architect can create three materialized views, each with a different cluster key: (A, B), (C, D), and (A, C). This way, each query can leverage the optimal clustering of the corresponding materialized view and achieve faster scan efficiency and better compression.

Snowflake Documentation: Materialized Views

Snowflake Learning: Materialized Views

https://www.snowflake.com/blog/using-materialized-views-to-solve-multi-clustering-performance-problems/

https://docs.snowflake.com/en/user-guide/data-sharing-intro

The Data Vault modeling approach separates data into hubs (business keys), links, and satellites, often requiring simultaneous insertion of related records derived from the same source dataset. Snowflake’s conditional multi-table INSERT enables a single source query to populate multiple target tables in parallel based on conditional logic. This capability aligns well with Data Vault patterns, where hubs and satellites are typically loaded together from the same staging data.

By inserting into hubs and satellites in parallel using surrogate keys (Answer A), architects ensure consistency, atomicity, and efficient processing without re-scanning source data multiple times. This approach reduces compute usage and simplifies ETL logic, which is particularly valuable in large-scale, near–real-time ingestion pipelines.

Options involving dimensions and facts relate more closely to star schema modeling, not Data Vault. Sequential insertion is also less efficient and not the defining advantage of conditional multi-table inserts. For SnowPro Architect candidates, this question emphasizes understanding how Snowflake SQL features support modern data modeling techniques such as Data Vault.

=========

QUESTION NO: 42 [Architecting Snowflake Solutions]

An Architect wants to alter a virtual warehouse for horizontal scaling but cannot find minimum and maximum cluster settings in the interface, even with the ACCOUNTADMIN role.

What is the MOST likely issue?

A. Missing CREATE WAREHOUSE privilege.

B. Incorrect SQL command used.

C. Using Standard edition where multi-cluster is not available.

D. A Snowflake Support ticket is required.

Answer: C

Multi-cluster virtual warehouses are only available in Snowflake Enterprise edition and higher. If the account is running on Standard edition, the options to configure minimum and maximum clusters for horizontal scaling will not appear in the UI or be available via SQL (Answer C).

This limitation is independent of role privileges; even ACCOUNTADMIN cannot enable multi-cluster functionality on unsupported editions. Incorrect SQL or missing privileges would result in errors rather than missing configuration options.

For SnowPro Architect candidates, this question tests awareness of edition-based feature availability, which is critical when designing scalable architectures and recommending Snowflake editions to meet workload requirements.

=========

QUESTION NO: 43 [Performance Optimization and Monitoring]

A 2 TB table with 400 columns is queried frequently to calculate average employee tenure by country and career level. The query exhibits poor partition pruning and runs on an X-Small warehouse.

What improvement meets the requirements with the LEAST operational overhead?

A. Add clustering keys on COUNTRY and EMPLOYMENT_STATUS.

B. Enable Query Acceleration Service (QAS).

C. Enable search optimization on equality predicates for COUNTRY and EMPLOYMENT_STATUS.

D. Build a materialized view for latest active employee records per country.

Answer: C

The query filters on equality predicates for COUNTRY, EMPLOYMENT_STATUS, and a specific EFFECTIVE_DATE value representing the latest record. Search Optimization Service (SOS) is specifically designed to accelerate highly selective equality predicates without requiring data reorganization (Answer C).

Clustering would introduce ongoing maintenance overhead and is less effective when cardinality is low or when multiple filter combinations exist. Query Acceleration Service helps with overall query execution time but does not directly improve partition pruning. Materialized views would require ongoing maintenance and additional storage, increasing operational complexity.

For SnowPro Architect candidates, this question highlights choosing the simplest effective optimization tool—SOS—when dealing with selective filters and large tables.

=========

QUESTION NO: 44 [Snowflake Data Engineering]

A team needs to recover data after pipeline failures or business rule violations.

Requirements:

• Database recoverable for 48 hours

• Analytics schema recoverable for 5 days

What is the correct approach?

A. Use custom stored procedure backups.

B. Use tasks with database and schema clones.

C. Use Time Travel with MIN_DATA_RETENTION_TIME_IN_DAYS = 2 and DATA_RETENTION_TIME_IN_DAYS = 5 on the ANALYTICS schema.

D. Use Time Travel with DATA_RETENTION_TIME_IN_DAYS = 2 and MIN_DATA_RETENTION_TIME_IN_DAYS = 5 on the ANALYTICS schema.

Answer: C

Snowflake Time Travel enables point-in-time recovery without manual backups. Setting MIN_DATA_RETENTION_TIME_IN_DAYS = 2 ensures that all database objects retain at least 48 hours of recoverable history. Extending DATA_RETENTION_TIME_IN_DAYS = 5 at the schema level allows longer recovery specifically for the analytics schema (Answer C).

This approach meets both recovery requirements with minimal operational overhead and aligns with Snowflake best practices. Cloning via tasks introduces unnecessary complexity and storage management. Custom backups are not required due to built-in Time Travel capabilities.

SnowPro Architect exams emphasize using native Snowflake features such as Time Travel for data recovery scenarios.

=========

QUESTION NO: 45 [Security and Access Management]

A financial services company needs to isolate sensitive production data from development data within the same region and support secure data transfer between environments.

What is the best solution?

A. Create two accounts with network policies and use data sharing.

B. Create two accounts with federated authentication and use cloning.

C. Create two databases in one account and use replication.

D. Create two databases in one account and use user-level network policies and shares.

Answer: A

Strong isolation of sensitive production data from development environments is best achieved using separate Snowflake accounts. Account-level isolation ensures independent security boundaries, network policies, and governance controls. Secure Data Sharing allows controlled, read-only access to production data without copying it, supporting safe data transfer between environments (Answer A).

Using cloning across accounts is not supported; cloning works only within the same account. Database-level separation does not provide the same security guarantees as account-level isolation, and user-level network policies are not supported in Snowflake.

This design aligns with SnowPro Architect best practices for regulated industries, emphasizing strong isolation, least privilege, and secure data access mechanisms.

Snowflake external tables support partitioning using the PARTITION BY clause, which allows architects to define logical partitions based on expressions derived from file paths or metadata. This approach is flexible and scalable, making it well-suited for data lakes that grow over time and may require changes to partition logic (Answer A).

Using PARTITION BY enables Snowflake to automatically manage partitions as new data arrives, without requiring manual intervention or table recreation. This aligns with Snowflake best practices for external table design and minimizes operational overhead.

The other options are invalid or unsupported in Snowflake. There is no partition_type = USER_SPECIFIED option, no METADATA$EXTERNAL_TABLE_PARTITION = MANUAL setting, and no ADD_PARTITION_COLUMN command for external tables. For the SnowPro Architect exam, understanding the correct and supported syntax for partitioned external tables is essential for designing maintainable data lake architectures.

=========

QUESTION NO: 62 [Snowflake Ecosystem and Integrations]

A group of marketing and advertising companies want to share data with one another for joint analysis while maintaining strict privacy controls.

How should an Architect design the data sharing solution?

A. Use Data Clean Rooms for privacy-preserving collaboration.

B. Use Secure Data Sharing with standard shares.

C. Use Snowflake Marketplace listings.

D. Use a Data Exchange as a shared data hub.

Answer: A

Snowflake Data Clean Rooms are specifically designed for privacy-preserving collaboration between organizations. They allow multiple parties to perform joint analytics on combined datasets without exposing raw underlying data to one another (Answer A). This makes them ideal for marketing and advertising use cases where sensitive customer data must remain private while still enabling insights such as audience overlap and campaign effectiveness.

Secure Data Sharing and Data Exchanges allow data access but do not inherently prevent participants from seeing raw data. Snowflake Marketplace is designed for broad data distribution rather than tightly controlled, privacy-centric collaboration.

For SnowPro Architect candidates, this question emphasizes selecting the right Snowflake-native collaboration mechanism based on privacy and governance requirements.

=========

QUESTION NO: 63 [Snowflake Data Engineering]

An Architect wants to build an automated ETL pipeline that reads data from an external stage using an external table, performs transformations on changed data, joins with dimension tables, and loads results into a target table.

What should the Architect use?

A. Streams and tasks

B. Dynamic tables

C. Materialized views

D. Snowpipe with auto-ingest

Answer: A

Streams and tasks provide Snowflake’s native framework for building automated, incremental ETL pipelines. A stream can track changes in the external table (or staging table), while tasks orchestrate the execution of transformation logic and loading into target tables (Answer A).

This approach supports complex transformations, joins with dimension tables, and controlled scheduling or event-driven execution. Materialized views cannot perform joins with arbitrary tables and are not suitable for complex ETL. Dynamic tables simplify transformations but are not designed to consume change data directly from external tables. Snowpipe focuses on ingestion only and does not support downstream transformations.

SnowPro Architect exams frequently test understanding of when to use streams and tasks versus newer abstractions like dynamic tables.

=========

QUESTION NO: 64 [Security and Access Management]

A data share exists between a provider and a consumer account. Five tables are already shared, and the consumer role has been granted IMPORTED PRIVILEGES.

What happens if a new table is added to the provider schema?

A. The consumer automatically sees the new table.

B. The consumer sees the table after granting IMPORTED PRIVILEGES again on the consumer side.

C. The consumer sees the table only after SELECT is granted on the new table to the share on the provider side.

D. The consumer sees the table after USAGE is granted on the database and schema and SELECT is granted on the table to the consumer database.

Answer: C

In Snowflake Secure Data Sharing, adding a new table to an existing schema does not automatically make it visible to consumers. The provider must explicitly grant SELECT on the new table to the share (Answer C). This ensures intentional and controlled data exposure.

Once the grant is applied on the provider side, consumer roles that already have IMPORTED PRIVILEGES will automatically see the new table without requiring additional grants on the consumer account. This separation of responsibilities is a core Snowflake governance principle.

This question reinforces SnowPro Architect knowledge of provider-versus-consumer responsibilities in Secure Data Sharing.

=========

QUESTION NO: 65 [Snowflake Data Engineering]

Which technique efficiently ingests and consumes semi-structured data for Snowflake data lake workloads?

A. IDEF1X

B. Schema-on-write

C. Schema-on-read

D. Information schema

Answer: C

Schema-on-read is a fundamental pattern for Snowflake data lake workloads involving semi-structured data such as JSON, Avro, or Parquet (Answer C). Data is ingested in its raw form and interpreted at query time using Snowflake’s VARIANT type and functions like FLATTEN.

This approach provides flexibility as data structures evolve, reduces ingestion complexity, and avoids the need to predefine rigid schemas. Schema-on-write is more appropriate for structured data warehouses but is less efficient for rapidly changing semi-structured data. IDEF1X is a data modeling notation, not an ingestion technique. Information schema is a metadata repository.

For SnowPro Architect candidates, understanding schema-on-read is critical when designing scalable, flexible data lake architectures in Snowflake.

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