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

According to the Snowflake documentation, the data loading performance can be improved by following some best practices and guidelines for preparing and staging the data files. One of the recommendations is to aim for data files that are roughly 100-250 MB (or larger) in size compressed, as this will optimize the number of parallel operations for a load. Smaller files should be aggregated and larger files should be split to achieve this size range. Another recommendation is to use a multi-cluster warehouse for loading, as this will allow for scaling up or out the compute resources depending on the load demand. A single-cluster warehouse may not be able to handle the load concurrency and throughput efficiently. Therefore, by creating a multi-cluster warehouse and merging smaller files to create bigger files, the data loading performance can be improved. References:

Data Loading Considerations

Preparing Your Data Files

Planning a Data Load

In Snowflake, when a user activates a secondary role during a session, certain privileges associated with DDL (Data Definition Language) operations are restricted. TheCREATEstatement, which falls under DDL operations, cannot be executed using a secondary role. This limitation is designed to enforce role-based access control and ensure that schema modifications are managed carefully, typically reserved for primary roles that have explicit permissions to modify database structures.

According to the SnowPro Advanced: Architect documents and learning resources, column masking policies are applied at query time based on the privileges of the user who runs the query. Therefore, if a user has the privilege to see unmasked data in a column, they will see the original data when they query that column. If they load this column data into another column that does not have a masking policy, the unmasked data will be loaded in the new column, and any user who can query the new column will see the unmasked data as well. The masking policy does not affect the underlying data in the column, only the query results.

Snowflake Documentation: Column Masking

Snowflake Learning: Column Masking

Comprehensive and Detailed 150 to 250 words of Explanation From Snowflake SnowPro Architect exam scope and all publicly documented material:

Clustering keys are most beneficial when they improve micro-partition pruning for common filter patterns and when the chosen columns provide a useful ordering that co-locates data. A common heuristic is to place lower-cardinality columns earlier (to quickly narrow partitions) and then add a higher-cardinality column that further reduces scanned partitions for selective access paths. Here, C_DATE has very low NDV (110), making it an excellent leading key to organize data by date and enable strong pruning for time-bound queries typical of event tables. Next, A_ID (11K) is moderate cardinality and can further segment data within a date range, helping point lookups or narrow scans by identifier. For the third key, the options force choosing between very high-cardinality event activity columns; selecting EVENT_ACT_0 (1.1G) is preferable to EVENT_ACT_4 (2.2G) because it is comparatively less distinct while still supporting additional pruning when queries filter by that attribute. This ordering aligns with Snowflake guidance: keep keys few, ordered to match common predicates, and avoid excessively high-cardinality keys unless they directly match frequent selective filters.

=========

QUESTION NO: 2 [Performance Optimization and Monitoring]

An Architect has implemented the search optimization service for a table. A user adds a new column to the table and there is a decrease in query performance. The Architect executes the DESCRIBE SEARCH OPTIMIZATION command and finds that the newly added column was not included in the search access path. Why did this occur?

A. The new column has a data type that is not supported by the search optimization service.

B. The new column is automatically included in the search access path, but there is a time delay before it takes effect.

C. The ON clause was used when enabling the search optimization service, which does not automatically include new columns.

D. The search optimization property needs to be dropped and then added back for the changes to take effect.

Answer: C

Comprehensive and Detailed 150 to 250 words of Explanation From Snowflake SnowPro Architect exam scope and all publicly documented material:

Search Optimization Service (SOS) builds and maintains a “search access path” for predicates on specific columns. When SOS is enabled without specifying columns, Snowflake can manage broader coverage depending on configuration; however, if SOS is enabled using an ON clause (targeting particular columns/expressions), the access path is explicitly defined and does not automatically expand to include newly added columns. That means queries filtering on the new column won’t benefit from SOS, and if workload patterns shift toward that column, perceived performance can drop relative to expectations. DESCRIBE SEARCH OPTIMIZATION exposing that the new column is absent from the access path is consistent with SOS being configured for a fixed set of columns. In SnowPro Architect terms, this is an operational/design consideration: SOS requires deliberate selection of access paths aligned to query predicates, and schema evolution (adding columns) may require revisiting SOS configuration to include new predicates that matter. This also reinforces the cost/performance tradeoff: SOS accelerates selective point-lookups and highly selective filters, but it must be targeted and maintained as query patterns and schemas change.

=========

QUESTION NO: 3 [Architecting Snowflake Solutions]

An Architect is defining transaction rules to adhere to ACID properties to ensure that executed statements are either committed or rolled back. Based on this scenario, what characteristics of transactions should be considered? (Select TWO).

A. The autocommit setting can be changed inside a stored procedure.

B. Explicit transactions should contain DDL, DML, and query statements.

C. Explicit transactions should contain only DML statements and query statements. All DDL statements implicitly commit active transactions.

D. An explicit transaction can be started by executing a BEGIN WORK statement and can be ended by executing a COMMIT WORK statement.

E. An explicit transaction can be started by executing a BEGIN TRANSACTION statement and can be ended by executing an END TRANSACTION statement.

Answer: C, D

Comprehensive and Detailed 150 to 250 words of Explanation From Snowflake SnowPro Architect exam scope and all publicly documented material:

Snowflake supports transactional behavior for DML (and SELECT statements within a transaction context), but DDL statements have special behavior: many DDL operations implicitly commit the current transaction. Because of that, mixing DDL with DML inside an explicit transaction undermines the “all-or-nothing” rollback expectations and complicates ACID-driven rules. Therefore, a key design characteristic is that explicit transactions should generally be limited to DML and query statements, while recognizing that DDL can implicitly commit and break transactional boundaries (Choice C). Additionally, Snowflake supports explicit transaction control statements using standard SQL forms; BEGIN WORK and COMMIT WORK are valid ways to open and close an explicit transaction (Choice D). From an architecting standpoint, this matters for pipeline design, error handling, and idempotency: multi-step loads/merges should group logically atomic DML steps into an explicit transaction so they commit together or roll back together, but DDL (like CREATE/ALTER) should be separated or executed with full awareness of implicit commits. This is consistent with SnowPro Architect expectations around reliable data engineering patterns and correctness under failure.

=========

QUESTION NO: 4 [Snowflake Data Engineering]

A Snowflake account has the following parameters:

MIN_DATA_RETENTION_TIME_IN_DAYS is set to 5 at the account level.

DATA_RETENTION_TIME_IN_DAYS is set to 4 on database DB1, and 6 on Schema1 in DB1.

DATA_RETENTION_TIME_IN_DAYS is set to 5 on database DB2, and 8 on Schema2 in DB2. What will be the result?

A. DB1 and Schema1 retained 5 days; DB2 and Schema2 retained 5 days.

B. DB1 and Schema1 retained 4 days; DB2 and Schema2 retained 5 days.

C. DB1 retained 4 days and Schema1 6 days; DB2 retained 5 days and Schema2 8 days.

D. DB1 retained 5 days and Schema1 6 days; DB2 retained 5 days and Schema2 8 days.

Answer: D

Comprehensive and Detailed 150 to 250 words of Explanation From Snowflake SnowPro Architect exam scope and all publicly documented material:

Snowflake Time Travel retention is governed by a parameter hierarchy and by edition-dependent limits, but the key behavior in this scenario is the minimum retention guardrail. MIN_DATA_RETENTION_TIME_IN_DAYS sets a floor: objects cannot effectively use a DATA_RETENTION_TIME_IN_DAYS value below that minimum. With an account-level minimum of 5 days, a database-level setting of 4 days is below the permitted minimum, so the effective retention for DB1 cannot be 4 days; it must be at least 5 days. Meanwhile, Schema1 is set to 6 days, which is above the minimum and should apply at the schema level (schemas can override their parent database settings within allowed bounds). For DB2, the database setting is 5 days, which matches the minimum and is valid; Schema2 at 8 days is also valid and applies to objects under that schema where applicable. From an architectural and operational perspective, this highlights two exam-relevant points: (1) retention is controlled via hierarchical parameters (account → database → schema → table), and (2) minimum retention settings enforce governance constraints across environments, preventing overly aggressive reductions that might violate recovery requirements.

=========

QUESTION NO: 5 [Performance Optimization and Monitoring]

An Architect has configured the search optimization service on a table, but metrics show that performance of a number of regularly executed queries is not improving. What could be causing this? (Select TWO).

A. The queries contain SEARCH functions.

B. The queries exceed the predicate limit.

C. The queries contain a predicate mismatch.

D. The queries are running against tables that contain semi-structured data.

E. The queries have a scalar subquery that queries the same table as the table in an outer query.

Answer: B, C

Comprehensive and Detailed 150 to 250 words of Explanation From Snowflake SnowPro Architect exam scope and all publicly documented material:

Search Optimization Service improves performance by maintaining additional search structures to accelerate selective predicates for supported query patterns. Two common reasons it may not help are (1) exceeding the predicate limit and (2) predicate mismatch. If queries include more predicates than SOS can leverage effectively (or beyond documented limits for the access path), Snowflake may not apply the optimization and will revert to normal micro-partition pruning and scanning behavior (Choice B). Predicate mismatch occurs when the query’s filter conditions do not align with the SOS access path definition—examples include using different columns than those optimized, applying functions/casts that prevent using the access path, or using non-supported predicate forms—so SOS cannot accelerate those queries (Choice C). Semi-structured data alone does not inherently prevent SOS benefits; SOS can be used with certain patterns, including on some expressions, but it still must match the configured access path. Likewise, the presence of SEARCH functions is not the core issue; SOS is about access paths and supported predicates, not requiring SEARCH() usage. Architecturally, this is why SOS should be selectively enabled for columns and predicate shapes that dominate high-cost workloads, and monitored/adjusted as query patterns evolve.

In Snowflake, a storage integration is used to define and configure external cloud storage that Snowflake will interact with. This includes specifying security policies for access control. One of the main features of storage integrations is the ability to set restrictions on where data may be exported. This is done by binding the storage integration to specific cloud storage locations, thereby ensuring that Snowflake can only access those locations. It helps to maintain control over the data and complies with data governance and security policies by preventing unauthorized data exports to unspecified locations.

Using Snowpipe for continuous, automated data ingestion minimizes the need for manual intervention and ensures that data is available in Snowflake promptly after it is generated. Leveraging Snowflake’s data sharing capabilities allows for efficient and secure access to the vendor’s data without the need for complex API integrations. Materialized views provide pre-aggregated data for fast access, which is ideal for dashboards that require high performance1234.

References =

•Snowflake Documentation on Snowpipe4

•Snowflake Documentation on Secure Data Sharing2

•Best Practices for Data Ingestion with Snowflake1

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.

Warehouse credits are used to pay for the processing time used by each virtual warehouse in Snowflake. A virtual warehouse is a cluster of compute resources that enables executing queries, loading data, and performing other DML operations. Warehouse credits are charged based on the number of virtual warehouses you use, how long they run, and their size1.

Among the commands listed in the question, the following ones will use warehouse credits:

SELECT MAX(FLAKE_ID) FROM SNOWFLAKE: This command will use warehouse credits because it is a query that requires a virtual warehouse to execute. The query will scan the SNOWFLAKE table and return the maximum value of the FLAKE_ID column2. Therefore, option B is correct.

SELECT COUNT(*) FROM SNOWFLAKE: This command will also use warehouse credits because it is a query that requires a virtual warehouse to execute. The query will scan the SNOWFLAKE table and return the number of rows in the table3. Therefore, option C is correct.

SELECT COUNT(FLAKE_ID) FROM SNOWFLAKE GROUP BY FLAKE_ID: This command will also use warehouse credits because it is a query that requires a virtual warehouse to execute. The query will scan the SNOWFLAKE table and return the number of rows for each distinct value of the FLAKE_ID column4. Therefore, option D is correct.

The command that will not use warehouse credits is:

SHOW TABLES LIKE ‘SNOWFL%’: This command will not use warehouse credits because it is a metadata operation that does not require a virtual warehouse to execute. The command will return the names of the tables that match the pattern ‘SNOWFL%’ in the current database and schema5. Therefore, option A is incorrect.

 Understanding Compute Cost : MAX Function : COUNT Function : GROUP BY Clause : SHOW TABLES