Snowpark workloads are often memory- and compute-intensive, especially when executing complex transformations, large joins, or machine learning logic inside stored procedures. In Snowflake, the MAX_CONCURRENCY_LEVEL warehouse parameter controls how many concurrent queries can run on a single cluster of a virtual warehouse. Lowering concurrency increases the amount of compute and memory available to each individual query.

Setting MAX_CONCURRENCY_LEVEL = 1 ensures that only one query can execute at a time on the warehouse cluster, allowing that query to consume the maximum possible share of CPU, memory, and I/O resources. This is the recommended configuration when the goal is to optimize performance for a single Snowpark job rather than maximizing throughput for many users. Higher concurrency levels would divide resources across multiple queries, reducing per-query performance and potentially causing spilling to remote storage.

For SnowPro Architect candidates, this question reinforces an important cost and performance tradeoff: concurrency tuning is a powerful lever. When running batch-oriented or compute-heavy Snowpark workloads, architects should favor lower concurrency to maximize per-query resources, even if that means fewer concurrent workloads.

=========

QUESTION NO: 12 [Cost Control and Resource Management]

An Architect executes the following query:

SELECT query_hash,

COUNT(*) AS query_count,

SUM(QH.EXECUTION_TIME) AS total_execution_time,

SUM((QH.EXECUTION_TIME / (1000 * 60 * 60)) * 8) AS c

FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY QH

WHERE warehouse_name = ' WH_L '

AND DATE_TRUNC( ' day ' , start_time) > = CURRENT_DATE() - 3

GROUP BY query_hash

ORDER BY c DESC

LIMIT 10;

What information does this query provide? (Select TWO).

A. It shows the total execution time and credit estimates for the 10 most expensive individual queries executed on WH_L over the last 3 days.

B. It shows the total execution time and credit estimates for the 10 most expensive query groups (identical or similar queries) executed on WH_L over the last 3 days.

C. It shows the total execution time and credit estimates for the 10 most frequently run query groups executed on WH_L over the last 3 days.

D. It calculates relative cost by converting execution time to minutes and multiplying by credits used.

E. It calculates relative cost by converting execution time to hours and multiplying by credits used.

Answer: B, E

This query groups results by QUERY_HASH, which represents logically identical SQL statements. As a result, the aggregation is performed at the query group level, not at the individual execution level. This allows architects to identify patterns where the same query (or same logical SQL) repeatedly consumes a large amount of compute (Answer B).

The cost calculation converts execution time from milliseconds to hours by dividing by (1000 * 60 * 60) and then multiplies the result by 8, which represents the hourly credit consumption of the WH_L warehouse size. This provides a relative estimate of credit usage per query group, not an exact billing value but a useful approximation for cost analysis (Answer E).

The query does not identify the most frequently executed queries; although COUNT(*) is included, the ordering is done by calculated cost (c), not by frequency. This type of analysis is directly aligned with SnowPro Architect responsibilities, helping architects optimize workloads, refactor expensive query patterns, and right-size warehouses to control costs.

=========

QUESTION NO: 13 [Architecting Snowflake Solutions]

An Architect is designing a disaster recovery plan for a global fraud reporting system. The plan must support near real-time systems using Snowflake data, operate near regional centers with fully redundant failover, and must not be publicly accessible.

Which steps must the Architect take? (Select THREE).

A. Create multiple replicating Snowflake Standard edition accounts.

B. Establish one Snowflake account using a Business Critical edition or higher.

C. Establish multiple Snowflake accounts in each required region with independent data sets.

D. Set up Secure Data Sharing among all Snowflake accounts in the organization.

E. Create a Snowflake connection object.

F. Create a failover group for the fraud data for each regional account.

Answer: B, C, F

Mission-critical, near real-time systems with strict availability and security requirements require advanced Snowflake features. Business Critical edition (or higher) is required to support failover groups and cross-region replication with higher SLA guarantees and compliance capabilities (Answer B). To meet regional proximity and redundancy requirements, multiple Snowflake accounts must be deployed in each required region, ensuring independence and isolation between regional environments (Answer C).

Failover groups are the core Snowflake mechanism for disaster recovery. They replicate selected databases, schemas, and roles across accounts and allow controlled promotion of secondary accounts to primary during failover events (Answer F). Secure Data Sharing alone does not provide DR or replication, and connection objects are unrelated to availability or redundancy.

This design aligns with SnowPro Architect best practices for multi-region disaster recovery, enabling low-latency regional access, controlled failover, and strong isolation without exposing systems to the public internet.

=========

QUESTION NO: 14 [Snowflake Data Engineering]

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

CREATE PIPE … AS

COPY INTO …

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.

Answer: A, B, D

Snowflake’s COPY INTO statement (including when used with Snowpipe) supports a limited but useful set of transformations. Data can be filtered using a WHERE clause when loading from a staged SELECT statement, enabling simple row-level filtering (Answer A). Columns can also be reordered by explicitly selecting fields in a different order than they appear in the source (Answer B). Additionally, type casting is supported, allowing raw data to be cast into target column data types during ingestion (Answer D).

However, COPY INTO does not support joins with other tables; it is designed for ingestion, not complex transformations. Columns can be omitted implicitly by not selecting them, but this is not considered a transformation feature in the context of Snowpipe exam questions. The ON_ERROR option is an error-handling configuration, not a transformation.

SnowPro Architect candidates are expected to recognize that COPY INTO and Snowpipe are ingestion-focused tools. More complex transformations should be handled downstream using streams and tasks, dynamic tables, or transformation frameworks like dbt.

=========

QUESTION NO: 15 [Security and Access Management]

A company wants to share selected product and sales tables with global partners. The partners are not Snowflake customers but do have access to AWS.

Requirements:

Data access must be governed.

Each partner should only have access to data from its respective region. What is the MOST secure and cost-effective solution?

A. Create reader accounts and share custom secure views.

B. Create an outbound share and share custom secure views.

C. Export secure views to each partner’s Amazon S3 bucket.

D. Publish secure views on the Snowflake Marketplace.

Answer: A

When sharing data with partners who are not Snowflake customers, Snowflake reader accounts provide the most secure and cost-effective solution. Reader accounts allow data providers to host and govern access within their own Snowflake environment while allowing consumers to query shared data without owning a Snowflake account (Answer A). This ensures strong governance, centralized billing, and no data movement.

By sharing custom secure views, the company can enforce row-level and column-level security so that each partner only sees data from its authorized region. Outbound shares require the consumer to have their own Snowflake account, which is not the case here. Exporting data to S3 introduces unnecessary data duplication, security risk, and operational overhead. Snowflake Marketplace is designed for broad distribution, not partner-specific regional restrictions.

For the SnowPro Architect exam, this question highlights best practices in secure data sharing, governance, and cost control when collaborating with external, non-Snowflake partners.

The ON_ERROR clause is an optional parameter for the COPY INTO command that specifies the behavior of the command when it encounters errors in the files. The ON_ERROR clause can have one of the following values1:

CONTINUE: This value instructs the command to continue loading the file and return an error message for a maximum of one error encountered per data file. The difference between the ROWS_PARSED and ROWS_LOADED column values represents the number of rows that include detected errors. To view all errors in the data files, use the VALIDATION_MODE parameter or query the VALIDATE function1.

SKIP_FILE: This value instructs the command to skip the file when it encounters a data error on any of the records in the file. The command moves on to the next file in the stage and continues loading. The skipped file is not loaded and no error message is returned for the file1.

ABORT_STATEMENT: This value instructs the command to stop loading data when the first error is encountered. The command returns an error message for the file and aborts the load operation. This is the default value for the ON_ERROR clause1.

Therefore, options A, B, and C are correct.

 COPY INTO < table >

 Compilation time is the time it takes for the optimizer to create an optimal query plan for the efficient execution of the query. It also involves some pruning of partition files, making the query execution efficient2

If the compilation time is greater than the execution time, it means that the optimizer spent more time analyzing the query than actually running it. This could indicate that the query has overly complex logic, such as multiple joins, subqueries, aggregations, or expressions. The complexity of the query could also affect the size and quality of the query plan, which could impact the performance of the query3

To reduce the compilation time, the Architect can try to simplify the query logic, use views or common table expressions (CTEs) to break down the query into smaller parts, or use hints to guide the optimizer. The Architect can also use the EXPLAIN command to examine the query plan and identify potential bottlenecks or inefficiencies4 References:

1: SnowPro Advanced: Architect | Study Guide 5

2: Snowflake Documentation | Query Profile Overview 6

3: Understanding Why Compilation Time in Snowflake Can Be Higher than Execution Time 7

4: Snowflake Documentation | Optimizing Query Performance 8

 SnowPro Advanced: Architect | Study Guide

 Query Profile Overview

 Understanding Why Compilation Time in Snowflake Can Be Higher than Execution Time

 Optimizing Query Performance

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.

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.

 According to the Snowflake documentation, when loading data into a table that captures the load time in a column with a default value of either CURRENT_TIME () or CURRENT_TIMESTAMP(), the default value is evaluated once per COPY statement, not once per row. Therefore, all rows loaded using a specific COPY statement will have the same timestamp value. This behavior ensures that the timestamp value reflects the time when the data was loaded into the table, not when the data was read from the source or created in the source. References:

Snowflake Documentation: Loading Data into Tables with Default Values

Snowflake Documentation: COPY INTO table

In Snowflake ' s parameter hierarchy, virtual warehouse parameters take precedence over user parameters. This hierarchy is designed to ensure that settings at the virtual warehouse level, which typically reflect the requirements of a specific workload or set of queries, override the preferences set at the individual user level. This helps maintain consistent performance and resource utilization as specified by the administrators managing the virtual warehouses.

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/

The error message indicates that the IP address in the error message is not allowed to access Snowflake because it is not in the allowed list of the network policy. The network policy is a feature that allows restricting access to Snowflake based on IP addresses or ranges. To resolve this error, the Architect should take the following steps:

Add the IP address in the error message to the allowed list in the network policy. This will allow the IP address to access Snowflake using the Private Link URLs. Alternatively, the Architect can disable the network policy if it is not required for security reasons.

Update the configuration of the Azure AD SSO to use the Private Link URLs. This will ensure that the SSO authentication process uses the Private Link URLs instead of the public URLs. The configuration can be updated by following the steps in the Azure documentation1.

These two steps should resolve the error and ensure that the account is accessed using only Private Link. The other options are not necessary or relevant for this scenario. Altering the Azure security integration to use the Private Link URLs is not required because the security integration is used for SCIM provisioning, not for SSO authentication. Generating a new SCIM access token using system$generate_scim_access_token and saving it to Azure AD is not required because the SCIM access token is used for SCIM provisioning, not for SSO authentication. Opening a case with Snowflake Support to authorize the Private Link URLs’ access to the account is not required because the authorization can be done by the account administrator using the SYSTEM$AUTHORIZE_PRIVATELINK function2.