ARA-R01 SnowPro Advanced: Architect Recertification Exam Questions and Answers

According to the Snowflake documentation, unstructured data files can be shared by using a secure view and Secure Data Sharing. A secure view allows the result of a query to be accessed like a table, and a secure view is specifically designated for data privacy. A scoped URL is an encoded URL that permits temporary access to a staged file without granting privileges to the stage. The URL expires when the persisted query result period ends, which is currently 24 hours. A scoped URL is recommended for file administrators to give scoped access to data files to specific roles in the same account. Snowflake records information in the query history about who uses a scoped URL to access a file, and when. Therefore, a scoped URL is the best option to share unstructured data within Snowflake, as it provides security, accountability, and control over the data access. References:

• Sharing unstructured Data with a secure view
• Introduction to Loading Unstructured Data

Snowflake is a cloud data platform that supports various data models for modeling tables in a Snowflake environment. The data models can be classified into two categories: dimensional and normalized. Dimensional data models are designed to optimize query performance and ease of use for business intelligence and analytics. Normalized data models are designed to reduce data redundancy and ensure data integrity for transactional and operational systems. The following are some of the data models that can be used in Snowflake:

• Dimensional/Kimball: This is a popular dimensional data model that uses a star or snowflake schema to organize data into fact and dimension tables. Fact tables store quantitative measures and foreign keys to dimension tables. Dimension tables store descriptive attributes and hierarchies. A star schema has a single denormalized dimension table for each dimension, while a snowflake schema has multiple normalized dimension tables for each dimension. Snowflake supports both star and snowflake schemas, and allows users to create views and joins to simplify queries.
• Inmon/3NF: This is a common normalized data model that uses a third normal form (3NF) schema to organize data into entities and relationships. 3NF schema eliminates data duplication and ensures data consistency by applying three rules: 1) every column in a table must depend on the primary key, 2) every column in a table must depend on the whole primary key, not a part of it, and 3) every column in a table must depend only on the primary key, not on other columns. Snowflake supports 3NF schema and allows users to create referential integrity constraints and foreign key relationships to enforce data quality.
• Data vault: This is a hybrid data model that combines the best practices of dimensional and normalized data models to create a scalable, flexible, and resilient data warehouse. Data vault schema consists of three types of tables: hubs, links, and satellites. Hubs store business keys and metadata for each entity. Links store associations and relationships between entities. Satellites store descriptive attributes and historical changes for each entity or relationship. Snowflake supports data vault schema and allows users to leverage its features such as time travel, zero-copy cloning, and secure data sharing to implement data vault methodology.

References: What is Data Modeling? | Snowflake, Snowflake Schema in Data Warehouse Model - GeeksforGeeks, [Data Vault 2.0 Modeling with Snowflake]

In Snowflake, a stream records data manipulation language (DML) changes to its base table since the stream was created or last consumed. STREAM_1 will show all changes including the TRUNCATE operation, while STREAM_2, being APPEND_ONLY, will not show deletions like TRUNCATE. Therefore, STREAM_1 will count the three inserts, the TRUNCATE (counted as a single operation), and the subsequent two inserts before the delete, totaling 4. STREAM_2 will only count the three initial inserts and the two after the TRUNCATE, totaling 3, as it does not count the TRUNCATE or the delete operation.

References: The explanation is based on the Snowflake documentation on streams, which details how streams track changes and the difference between standard and APPEND_ONLY streams12.

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.

A transient schema in Snowflake is designed without a Fail-safe period, meaning it does not incur additional storage costs once it leaves Time Travel, and it is not protected by Fail-safe in the event of a data loss. The WITH MANAGED ACCESS option ensures that all privilege grants, including future grants on objects within the schema, are managed by the schema owner, thus restricting object owners from passing on access to other users1.

References =

•Snowflake Documentation on creating schemas1

•Snowflake Documentation on configuring access control2

•Snowflake Documentation on understanding and viewing Fail-safe3

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 the VALUE 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.

References:

• 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

The Snowflake architecture recommendation that necessitates multiple Snowflake accounts for implementation is the separation of development, test, and production environments. This approach, known as Account per Tenant (APT), isolates tenants into separate Snowflake accounts, ensuring dedicated resources and security isolation12.

References

•Snowflake’s white paper on “Design Patterns for Building Multi-Tenant Applications on Snowflake” discusses the APT model and its requirement for separate Snowflake accounts for each tenant1.

•Snowflake Documentation on Secure Data Sharing, which mentions the possibility of sharing data across multiple accounts3.

• The correct answer is A because it improves the performance of queries by reducing the amount of data scanned and processed. By adding a create_date field with a timestamp data type, Snowflake can automatically cluster the table based on this field and prune the micro-partitions that do not match the filter condition. This avoids the need to parse the JSON data and access the variant field for every record.
• Option B is incorrect because it does not improve the performance of queries. By adding a create_date field with a varchar data type, Snowflake cannot automatically cluster the table based on this field and prune the micro-partitions that do not match the filter condition. This still requires parsing the JSON data and accessing the variant field for every record.
• Option C is incorrect because it does not address the root cause of the performance issue. By validating the size of the warehouse being used, Snowflake can adjust the compute resources to match the data volume and parallelize the query execution. However, this does not reduce the amount of data scanned and processed, which is the main bottleneck for queries on JSON data.
• Option D is incorrect because it adds unnecessary complexity and overhead to the data loading and querying process. By incorporating the use of multiple tables partitioned by date ranges, Snowflake can reduce the amount of data scanned and processed for queries that specify a date range. However, this requires creating and maintaining multiple tables, loading data into the appropriate table based on the date, and joining the tables for queries that span multiple date ranges. References:
• Snowflake Documentation: Loading Data Using Snowpipe: This document explains how to use Snowpipe to continuously load data from external sources into Snowflake tables. It also describes the syntax and usage of the COPY INTO command, which supports various options and parameters to control the loading behavior, such as ON_ERROR, PURGE, and SKIP_FILE.
• Snowflake Documentation: Date and Time Data Types and Functions: This document explains the different data types and functions for working with date and time values in Snowflake. It also describes how to set and change the session timezone and the system timezone.
• Snowflake Documentation: Querying Metadata: This document explains how to query the metadata of the objects and operations in Snowflake using various functions, views, and tables. It also describes how to access the copy history information using the COPY_HISTORY function or the COPY_HISTORY view.
• Snowflake Documentation: Loading JSON Data: This document explains how to load JSON data into Snowflake tables using various methods, such as the COPY INTO command, the INSERT command, or the PUT command. It also describes how to access and query JSON data using the dot notation, the FLATTEN function, or the LATERAL join.
• Snowflake Documentation: Optimizing Storage for Performance: This document explains how to optimize the storage of data in Snowflake tables to improve the performance of queries. It also describes the concepts and benefits of automatic clustering, search optimization service, and materialized views.

• The SQL statement is a command for creating a pipe in Snowflake, which is an object that defines the COPY INTO statement used by Snowpipe to load data from an ingestion queue into tables1. The statement uses a subquery in the FROM clause to transform the data from the staged files before loading it into the table2.
  The transformations supported in the subquery are as follows2:

  SQLAI-generated code. Review and use carefully. More info on FAQ.

  create pipe mypipe as

  copy into mytable

  from (

  select * from @mystage

  where col1 = 'A' and col2 > 10

  );

  • uk.co.certification.simulator.questionpool.PList@18277a90

  SQLAI-generated code. Review and use carefully. More info on FAQ.

  create pipe mypipe as

  copy into mytable (col1, col2, col3)

  from (

  select col3, col1, col2 from @mystage

  );

  • uk.co.certification.simulator.questionpool.PList@18277ad0

  SQLAI-generated code. Review and use carefully. More info on FAQ.

  create pipe mypipe as

  copy into mytable (col1, col2)

  from (

  select col1, col2 from @mystage

  );

  • The other options are not supported in the subquery because2:

  SQLAI-generated code. Review and use carefully. More info on FAQ.

  create pipe mypipe as

  copy into mytable (col1, col2)

  from (

  select col1::date, col2 from @mystage

  );

  • uk.co.certification.simulator.questionpool.PList@18275290

  SQLAI-generated code. Review and use carefully. More info on FAQ.

  create pipe mypipe as

  copy into mytable (col1, col2, col3)

  from (

  select s.col1, s.col2, t.col3 from @mystage s

  join othertable t on s.col1 = t.col1

  );

  • uk.co.certification.simulator.questionpool.PList@182752f0

  SQLAI-generated code. Review and use carefully. More info on FAQ.

  create pipe mypipe as

  copy into mytable

  from (

  select * from @mystage

  on error abort

  );

  References:

  • 1: CREATE PIPE | Snowflake Documentation
  • 2: Transforming Data During a Load | Snowflake Documentation
  Questions 13

  A user is executing the following command sequentially within a timeframe of 10 minutes from start to finish:

  

  What would be the output of this query?

  Options:

  A.

  Table T_SALES_CLONE successfully created.

  B.

  Time Travel data is not available for table T_SALES.

  C.

  The offset -> is not a valid clause in the clone operation.

  D.

  Syntax error line 1 at position 58 unexpected 'at’.

  Show Answer Buy Now

  Answer:

  A

  Explanation:

  Explanation:

  The query is executing a clone operation on an existing table t_sales with an offset to account for the retention time. The syntax used is correct for cloning a table in Snowflake, and the use of the at(offset => -60*30) clause is valid. This specifies that the clone should be based on the state of the table 30 minutes prior (60 seconds * 30). Assuming the table t_sales exists and has been modified within the last 30 minutes, and considering the data_retention_time_in_days is set to 1 day (which enables time travel queries for the past 24 hours), the table t_sales_clone would be successfully created based on the state of t_sales 30 minutes before the clone command was issued.

  Questions 14

  Which statements describe characteristics of the use of materialized views in Snowflake? (Choose two.)

  Options:

  A.

  They can include ORDER BY clauses.

  B.

  They cannot include nested subqueries.

  C.

  They can include context functions, such as CURRENT_TIME().

  D.

  They can support MIN and MAX aggregates.

  E.

  They can support inner joins, but not outer joins.

  Show Answer Buy Now

  Answer:

  B, D

  Explanation:

  Explanation:

   According to the Snowflake documentation, materialized views have some limitations on the query specification that defines them. One of these limitations is that they cannot include nested subqueries, such as subqueries in the FROM clause or scalar subqueries in the SELECT list. Another limitation is that they cannot include ORDER BY clauses, context functions (such as CURRENT_TIME()), or outer joins. However, materialized views can support MIN and MAX aggregates, as well as other aggregate functions, such as SUM, COUNT, and AVG.

  References:

  • Limitations on Creating Materialized Views | Snowflake Documentation
  • Working with Materialized Views | Snowflake Documentation

  Questions 15

  A Data Engineer is designing a near real-time ingestion pipeline for a retail company to ingest event logs into Snowflake to derive insights. A Snowflake Architect is asked to define security best practices to configure access control privileges for the data load for auto-ingest to Snowpipe.

  What are the MINIMUM object privileges required for the Snowpipe user to execute Snowpipe?

  Options:

  A.

  OWNERSHIP on the named pipe, USAGE on the named stage, target database, and schema, and INSERT and SELECT on the target table

  B.

  OWNERSHIP on the named pipe, USAGE and READ on the named stage, USAGE on the target database and schema, and INSERT end SELECT on the target table

  C.

  CREATE on the named pipe, USAGE and READ on the named stage, USAGE on the target database and schema, and INSERT end SELECT on the target table

  D.

  USAGE on the named pipe, named stage, target database, and schema, and INSERT and SELECT on the target table

  Show Answer Buy Now

  Answer:

  B

  Explanation:

  Explanation:

   According to the SnowPro Advanced: Architect documents and learning resources, the minimum object privileges required for the Snowpipe user to execute Snowpipe are:

  • OWNERSHIP on the named pipe. This privilege allows the Snowpipe user to create, modify, and drop the pipe object that defines the COPY statement for loading data from the stage to the table1.
  • USAGE and READ on the named stage. These privileges allow the Snowpipe user to access and read the data files from the stage that are loaded by Snowpipe2.
  • USAGE on the target database and schema. These privileges allow the Snowpipe user to access the database and schema that contain the target table3.
  • INSERT and SELECT on the target table. These privileges allow the Snowpipe user to insert data into the table and select data from the table4.

  The other options are incorrect because they do not specify the minimum object privileges required for the Snowpipe user to execute Snowpipe. Option A is incorrect because it does not include the READ privilege on the named stage, which is required for the Snowpipe user to read the data files from the stage. Option C is incorrect because it does not include the OWNERSHIP privilege on the named pipe, which is required for the Snowpipe user to create, modify, and drop the pipe object. Option D is incorrect because it does not include the OWNERSHIP privilege on the named pipe or the READ privilege on the named stage, which are both required for the Snowpipe user to execute Snowpipe. References: CREATE PIPE | Snowflake Documentation, CREATE STAGE | Snowflake Documentation, CREATE DATABASE | Snowflake Documentation, CREATE TABLE | Snowflake Documentation

  Questions 16

  A Snowflake Architect is setting up database replication to support a disaster recovery plan. The primary database has external tables.

  How should the database be replicated?

  Options:

  A.

  Create a clone of the primary database then replicate the database.

  B.

  Move the external tables to a database that is not replicated, then replicate the primary database.

  C.

  Replicate the database ensuring the replicated database is in the same region as the external tables.

  D.

  Share the primary database with an account in the same region that the database will be replicated to.

  Show Answer Buy Now

  Answer:

  B

  Explanation:

  Explanation:

  Database replication is a feature that allows you to create a copy of a database in another account, region, or cloud platform for disaster recovery or business continuity purposes. However, not all database objects can be replicated. External tables are one of the exceptions, as they reference data files stored in an external stage that is not part of Snowflake. Therefore, to replicate a database that contains external tables, you need to move the external tables to a separate database that is not replicated, and then replicate the primary database that contains the other objects. This way, you can avoid replication errors and ensure consistency between the primary and secondary databases. The other options are incorrect because they either do not address the issue of external tables, or they use an alternative method that is not supported by Snowflake. You cannot create a clone of the primary database and then replicate it, as replication only works on the original database, not on its clones. You also cannot share the primary database with another account, as sharing is a different feature that does not create a copy of the database, but rather grants access to the shared objects. Finally, you do not need to ensure that the replicated database is in the same region as the external tables, as external tables can access data files stored in any region or cloud platform, as long as the stage URL is valid and accessible. References:

  • [Replication and Failover/Failback] 1
  • [Introduction to External Tables] 2
  • [Working with External Tables] 3
  • [Replication : How to migrate an account from One Cloud Platform or Region to another in Snowflake] 4

  Questions 17

  The Business Intelligence team reports that when some team members run queries for their dashboards in parallel with others, the query response time is getting significantly slower What can a Snowflake Architect do to identify what is occurring and troubleshoot this issue?

  A)

  

  B)

  

  C)

  

  D)

  

  Options:

  A.

  Option A

  B.

  Option B

  C.

  Option C

  D.

  Option D

  Show Answer Buy Now

  Answer:

  A

  Explanation:

  Explanation:

  The image shows a SQL query that can be used to identify which queries are spilled to remote storage and suggests changing the warehouse parameters to address this issue. Spilling to remote storage occurs when the memory allocated to a warehouse is insufficient to process a query, and Snowflake uses disk or cloud storage as a temporary cache. This can significantly slow down the query performance and increase the cost. To troubleshoot this issue, a Snowflake Architect can run the query shown in the image to find out which queries are spilling, how much data they are spilling, and which warehouses they are using. Then, the architect can adjust the warehouse size, type, or scaling policy to provide enough memory for the queries and avoid spilling12. References:

  • Recognizing Disk Spilling
  • Managing the Kafka Connector

  Questions 18

  A global company needs to securely share its sales and Inventory data with a vendor using a Snowflake account.

  The company has its Snowflake account In the AWS eu-west 2 Europe (London) region. The vendor's Snowflake account Is on the Azure platform in the West Europe region. How should the company's Architect configure the data share?

  Options:

  A.

  1. Create a share.

  2. Add objects to the share.

  3. Add a consumer account to the share for the vendor to access.

  B.

  1. Create a share.

  2. Create a reader account for the vendor to use.

  3. Add the reader account to the share.

  C.

  1. Create a new role called db_share.

  2. Grant the db_share role privileges to read data from the company database and schema.

  3. Create a user for the vendor.

  4. Grant the ds_share role to the vendor's users.

  D.

  1. Promote an existing database in the company's local account to primary.

  2. Replicate the database to Snowflake on Azure in the West-Europe region.

  3. Create a share and add objects to the share.

  4. Add a consumer account to the share for the vendor to access.

  Show Answer Buy Now

  Answer:

  A

  Explanation:

  Explanation:

  The correct way to securely share data with a vendor using a Snowflake account on a different cloud platform and region is to create a share, add objects to the share, and add a consumer account to the share for the vendor to access. This way, the company can control what data is shared, who can access it, and how long the share is valid. The vendor can then query the shared data without copying or moving it to their own account. The other options are either incorrect or inefficient, as they involve creating unnecessary reader accounts, users, roles, or database replication.

  https://learn.snowflake.com/en/certifications/snowpro-advanced-architect/

  Questions 19

  A company has an external vendor who puts data into Google Cloud Storage. The company's Snowflake account is set up in Azure.

  What would be the MOST efficient way to load data from the vendor into Snowflake?

  Options:

  A.

  Ask the vendor to create a Snowflake account, load the data into Snowflake and create a data share.

  B.

  Create an external stage on Google Cloud Storage and use the external table to load the data into Snowflake.

  C.

  Copy the data from Google Cloud Storage to Azure Blob storage using external tools and load data from Blob storage to Snowflake.

  D.

  Create a Snowflake Account in the Google Cloud Platform (GCP), ingest data into this account and use data replication to move the data from GCP to Azure.

  Show Answer Buy Now

  Answer:

  B

  Explanation:

  Explanation:

  The most efficient way to load data from the vendor into Snowflake is to create an external stage on Google Cloud Storage and use the external table to load the data into Snowflake (Option B). This way, you can avoid copying or moving the data across different cloud platforms, which can incur additional costs and latency. You can also leverage the external table feature to query the data directly from Google Cloud Storage without loading it into Snowflake tables, which can save storage space and improve performance. Option A is not efficient because it requires the vendor to create a Snowflake account and a data share, which can be complicated and costly. Option C is not efficient because it involves copying the data from Google Cloud Storage to Azure Blob storage using external tools, which can be slow and expensive. Option D is not efficient because it requires creating a Snowflake account in the Google Cloud Platform (GCP), ingesting data into this account, and using data replication to move the data from GCP to Azure, which can be complex and time-consuming. References: The answer can be verified from Snowflake’s official documentation on external stages and external tables available on their website. Here are some relevant links:

  • Using External Stages | Snowflake Documentation
  • Using External Tables | Snowflake Documentation
  • Loading Data from a Stage | Snowflake Documentation

  Questions 20

  A company needs to share its product catalog data with one of its partners. The product catalog data is stored in two database tables: product_category, and product_details. Both tables can be joined by the product_id column. Data access should be governed, and only the partner should have access to the records.

  The partner is not a Snowflake customer. The partner uses Amazon S3 for cloud storage.

  Which design will be the MOST cost-effective and secure, while using the required Snowflake features?

  Options:

  A.

  Use Secure Data Sharing with an S3 bucket as a destination.

  B.

  Publish product_category and product_details data sets on the Snowflake Marketplace.

  C.

  Create a database user for the partner and give them access to the required data sets.

  D.

  Create a reader account for the partner and share the data sets as secure views.

  Show Answer Buy Now

  Answer:

  D

  Explanation:

  Explanation:

   A reader account is a type of Snowflake account that allows external users to access data shared by a provider account without being a Snowflake customer. A reader account can be created and managed by the provider account, and can use the Snowflake web interface or JDBC/ODBC drivers to query the shared data. A reader account is billed to the provider account based on the credits consumed by the queries1. A secure view is a type of view that applies row-level security filters to the underlying tables, and masks the data that is not accessible to the user. A secure view can be shared with a reader account to provide granular and governed access to the data2. In this scenario, creating a reader account for the partner and sharing the data sets as secure views would be the most cost-effective and secure design, while using the required Snowflake features, because:

  • It would avoid the data transfer and storage costs of using an S3 bucket as a destination, and the potential security risks of exposing the data to unauthorized access or modification.
  • It would avoid the complexity and overhead of publishing the data sets on the Snowflake Marketplace, and the potential loss of control over the data ownership and pricing.
  • It would avoid the need to create a database user for the partner and grant them access to the required data sets, which would require the partner to have a Snowflake account and consume the provider’s resources.

  References:

  • Reader Accounts
  • Secure Views

  Questions 21

  The IT Security team has identified that there is an ongoing credential stuffing attack on many of their organization’s system.

  What is the BEST way to find recent and ongoing login attempts to Snowflake?

  Options:

  A.

  Call the LOGIN_HISTORY Information Schema table function.

  B.

  Query the LOGIN_HISTORY view in the ACCOUNT_USAGE schema in the SNOWFLAKE database.

  C.

  View the History tab in the Snowflake UI and set up a filter for SQL text that contains the text "LOGIN".

  D.

  View the Users section in the Account tab in the Snowflake UI and review the last login column.

  Show Answer Buy Now

  Answer:

  B

  Explanation:

  Explanation:

  This view can be used to query login attempts by Snowflake users within the last 365 days (1 year). It provides information such as the event timestamp, the user name, the client IP, the authentication method, the success or failure status, and the error code or message if the login attempt was unsuccessful. By querying this view, the IT Security team can identify any suspicious or malicious login attempts to Snowflake and take appropriate actions to prevent credential stuffing attacks1. The other options are not the best ways to find recent and ongoing login attempts to Snowflake. Option A is incorrect because the LOGIN_HISTORY Information Schema table function only returns login events within the last 7 days, which may not be sufficient to detect credential stuffing attacks that span a longer period of time2. Option C is incorrect because the History tab in the Snowflake UI only shows the queries executed by the current user or role, not the login events of other users or roles3. Option D is incorrect because the Users section in the Account tab in the Snowflake UI only shows the last login time for each user, not the details of the login attempts or the failures.

  Questions 22

  Files arrive in an external stage every 10 seconds from a proprietary system. The files range in size from 500 K to 3 MB. The data must be accessible by dashboards as soon as it arrives.

  How can a Snowflake Architect meet this requirement with the LEAST amount of coding? (Choose two.)

  Options:

  A.

  Use Snowpipe with auto-ingest.

  B.

  Use a COPY command with a task.

  C.

  Use a materialized view on an external table.

  D.

  Use the COPY INTO command.

  E.

  Use a combination of a task and a stream.

  Show Answer Buy Now

  Answer:

  A, E

  Explanation:

  Explanation:

  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.

  Questions 23

  Which system functions does Snowflake provide to monitor clustering information within a table (Choose two.)

  Options:

  A.

  SYSTEM$CLUSTERING_INFORMATION

  B.

  SYSTEM$CLUSTERING_USAGE

  C.

  SYSTEM$CLUSTERING_DEPTH

  D.

  SYSTEM$CLUSTERING_KEYS

  E.

  SYSTEM$CLUSTERING_PERCENT

  Show Answer Buy Now

  Answer:

  A, C

  Explanation:

  Explanation:

  According to the Snowflake documentation, these two system functions are provided by Snowflake to monitor clustering information within a table. A system function is a type of function that allows executing actions or returning information about the system. A clustering key is a feature that allows organizing data across micro-partitions based on one or more columns in the table. Clustering can improve query performance by reducing the number of files to scan.

  • SYSTEM$CLUSTERING_INFORMATION is a system function that returns clustering information, including average clustering depth, for a table based on one or more columns in the table. The function takes a table name and an optional column name or expression as arguments, and returns a JSON string with the clustering information. The clustering information includes the cluster by keys, the total partition count, the total constant partition count, the average overlaps, and the average depth1.
  • SYSTEM$CLUSTERING_DEPTH is a system function that returns the clustering depth for a table based on one or more columns in the table. The function takes a table name and an optional column name or expression as arguments, and returns an integer value with the clustering depth. The clustering depth is the maximum number of overlapping micro-partitions for any micro-partition in the table. A lower clustering depth indicates a better clustering2.

  References:

  • SYSTEM$CLUSTERING_INFORMATION | Snowflake Documentation
  • SYSTEM$CLUSTERING_DEPTH | Snowflake Documentation

  Questions 24

  An Architect entered the following commands in sequence:

  

  USER1 cannot find the table.

  Which of the following commands does the Architect need to run for USER1 to find the tables using the Principle of Least Privilege? (Choose two.)

  Options:

  A.

  GRANT ROLE PUBLIC TO ROLE INTERN;

  B.

  GRANT USAGE ON DATABASE SANDBOX TO ROLE INTERN;

  C.

  GRANT USAGE ON SCHEMA SANDBOX.PUBLIC TO ROLE INTERN;

  D.

  GRANT OWNERSHIP ON DATABASE SANDBOX TO USER INTERN;

  E.

  GRANT ALL PRIVILEGES ON DATABASE SANDBOX TO ROLE INTERN;

  Show Answer Buy Now

  Answer:

  B, C

  Explanation:

  Explanation:

  • According to the Principle of Least Privilege, the Architect should grant the minimum privileges necessary for the USER1 to find the tables in the SANDBOX database.
  • The USER1 needs to have USAGE privilege on the SANDBOX database and the SANDBOX.PUBLIC schema to be able to access the tables in the PUBLIC schema. Therefore, the commands B and C are the correct ones to run.
  • The command A is not correct because the PUBLIC role is automatically granted to every user and role in the account, and it does not have any privileges on the SANDBOX database by default.
  • The command D is not correct because it would transfer the ownership of the SANDBOX database from the Architect to the USER1, which is not necessary and violates the Principle of Least Privilege.
  • The command E is not correct because it would grant all the possible privileges on the SANDBOX database to the USER1, which is also not necessary and violates the Principle of Least Privilege.

  References: : Snowflake - Principle of Least Privilege : Snowflake - Access Control Privileges : Snowflake - Public Role : Snowflake - Ownership and Grants

  Questions 25

  You are a snowflake architect in an organization. The business team came to to deploy an use case which requires you to load some data which they can visualize through tableau. Everyday new data comes in and the old data is no longer required.

  What type of table you will use in this case to optimize cost

  Options:

  A.

  TRANSIENT

  B.

  TEMPORARY

  C.

  PERMANENT

  Show Answer Buy Now

  Answer:

  A

  Explanation:

  Explanation:

  • A transient table is a type of table in Snowflake that does not have a Fail-safe period and can have a Time Travel retention period of either 0 or 1 day. Transient tables are suitable for temporary or intermediate data that can be easily reproduced or replicated1.
  • A temporary table is a type of table in Snowflake that is automatically dropped when the session ends or the current user logs out. Temporary tables do not incur any storage costs, but they are not visible to other users or sessions2.
  • A permanent table is a type of table in Snowflake that has a Fail-safe period and a Time Travel retention period of up to 90 days. Permanent tables are suitable for persistent and durable data that needs to be protected from accidental or malicious deletion3.
  • In this case, the use case requires loading some data that can be visualized through Tableau. The data is updated every day and the old data is no longer required. Therefore, the best type of table to use in this case to optimize cost is a transient table, because it does not incur any Fail-safe costs and it can have a short Time Travel retention period of 0 or 1 day. This way, the data can be loaded and queried by Tableau, and then deleted or overwritten without incurring any unnecessary storage costs.

  References: : Transient Tables : Temporary Tables : Understanding & Using Time Travel

  Questions 26

  Company A would like to share data in Snowflake with Company B. Company B is not on the same cloud platform as Company A.

  What is required to allow data sharing between these two companies?

  Options:

  A.

  Create a pipeline to write shared data to a cloud storage location in the target cloud provider.

  B.

  Ensure that all views are persisted, as views cannot be shared across cloud platforms.

  C.

  Setup data replication to the region and cloud platform where the consumer resides.

  D.

  Company A and Company B must agree to use a single cloud platform: Data sharing is only possible if the companies share the same cloud provider.

  Show Answer Buy Now

  Answer:

  C

  Explanation:

  Explanation:

   According to the SnowPro Advanced: Architect documents and learning resources, the requirement to allow data sharing between two companies that are not on the same cloud platform is to set up data replication to the region and cloud platform where the consumer resides. Data replication is a feature of Snowflake that enables copying databases across accounts in different regions and cloud platforms. Data replication allows data providers to securely share data with data consumers across different regions and cloud platforms by creating a replica database in the consumer’s account. The replica database is read-only and automatically synchronized with the primary database in the provider’s account. Data replication is useful for scenarios where data sharing is not possible or desirable due to latency, compliance, or security reasons1. The other options are incorrect because they are not required or feasible to allow data sharing between two companies that are not on the same cloud platform. Option A is incorrect because creating a pipeline to write shared data to a cloud storage location in the target cloud provider is not a secure or efficient way of sharing data. It would require additional steps to load the data from the cloud storage to the consumer’s account, and it would not leverage the benefits of Snowflake’s data sharing features. Option B is incorrect because ensuring that all views are persisted is not relevant for data sharing across cloud platforms. Views can be shared across cloud platforms as long as they reference objects in the same database. Persisting views is an option to improve the performance of querying views, but it is not required for data sharing2. Option D is incorrect because Company A and Company B do not need to agree to use a single cloud platform. Data sharing is possible across different cloud platforms using data replication or other methods, such as listings or auto-fulfillment3. References: Replicating Databases Across Multiple Accounts | Snowflake Documentation, Persisting Views | Snowflake Documentation, Sharing Data Across Regions and Cloud Platforms | Snowflake Documentation

  Questions 27

  Is it possible for a data provider account with a Snowflake Business Critical edition to share data with an Enterprise edition data consumer account?

  Options:

  A.

  A Business Critical account cannot be a data sharing provider to an Enterprise consumer. Any consumer accounts must also be Business Critical.

  B.

  If a user in the provider account with role authority to create or alter share adds an Enterprise account as a consumer, it can import the share.

  C.

  If a user in the provider account with a share owning role sets share_restrictions to False when adding an Enterprise consumer account, it can import the share.

  D.

  If a user in the provider account with a share owning role which also has override share restrictions privilege share_restrictions set to False when adding an Enterprise consumer account, it can import the share.

  Show Answer Buy Now

  Answer:

  B

  Explanation:

  Explanation:

  In Snowflake, data sharing capabilities allow a Business Critical edition account to share data with an Enterprise edition consumer account. The ability to share data is contingent upon the role permissions within the provider account. If a user has the necessary role authority (like ACCOUNTADMIN or a role with similar privileges to create or manage shares), they can add an Enterprise edition account as a consumer. This feature enables flexibility in data sharing across different Snowflake account editions, facilitating broader data collaboration and accessibility.References: Snowflake's data sharing documentation and the specifics of edition-based capabilities discussed in SnowPro Advanced: Architect certification materials.

  Questions 28

  Consider the following COPY command which is loading data with CSV format into a Snowflake table from an internal stage through a data transformation query.

  

  This command results in the following error:

  SQL compilation error: invalid parameter 'validation_mode'

  Assuming the syntax is correct, what is the cause of this error?

  Options:

  A.

  The VALIDATION_MODE parameter supports COPY statements that load data from external stages only.

  B.

  The VALIDATION_MODE parameter does not support COPY statements with CSV file formats.

  C.

  The VALIDATION_MODE parameter does not support COPY statements that transform data during a load.

  D.

  The value return_all_errors of the option VALIDATION_MODE is causing a compilation error.

  Show Answer Buy Now

  Answer:

  C

  Explanation:

  Explanation:

  • The VALIDATION_MODE parameter is used to specify the behavior of the COPY statement when loading data into a table. It is used to specify whether the COPY statement should return an error if any of the rows in the file are invalid or if it should continue loading the valid rows. The VALIDATION_MODE parameter is only supported for COPY statements that load data from external stages1.
  • The query in the question uses a data transformation query to load data from an internal stage. A data transformation query is a query that transforms the data during the load process, such as parsing JSON or XML data, applying functions, or joining with other tables2.
  • According to the documentation, VALIDATION_MODE does not support COPY statements that transform data during a load. If the parameter is specified, the COPY statement returns an error1. Therefore, option C is the correct answer.

  References: : COPY INTO

   : Transforming Data During a Load
  Questions 29

  A company’s daily Snowflake workload consists of a huge number of concurrent queries triggered between 9pm and 11pm. At the individual level, these queries are smaller statements that get completed within a short time period.

  What configuration can the company’s Architect implement to enhance the performance of this workload? (Choose two.)

  Options:

  A.

  Enable a multi-clustered virtual warehouse in maximized mode during the workload duration.

  B.

  Set the MAX_CONCURRENCY_LEVEL to a higher value than its default value of 8 at the virtual warehouse level.

  C.

  Increase the size of the virtual warehouse to size X-Large.

  D.

  Reduce the amount of data that is being processed through this workload.

  E.

  Set the connection timeout to a higher value than its default.

  Show Answer Buy Now

  Answer:

  A, B

  Explanation:

  Explanation:

  These two configuration options can enhance the performance of the workload that consists of a huge number of concurrent queries that are smaller and faster.

  • Enabling a multi-clustered virtual warehouse in maximized mode allows the warehouse to scale out automatically by adding more clusters as soon as the current cluster is fully loaded, regardless of the number of queries in the queue. This can improve the concurrency and throughput of the workload by minimizing or preventing queuing. The maximized mode is suitable for workloads that require high performance and low latency, and are less sensitive to credit consumption1.
  • Setting the MAX_CONCURRENCY_LEVEL to a higher value than its default value of 8 at the virtual warehouse level allows the warehouse to run more queries concurrently on each cluster. This can improve the utilization and efficiency of the warehouse resources, especially for smaller and faster queries that do not require a lot of processing power. The MAX_CONCURRENCY_LEVEL parameter can be set when creating or modifying a warehouse, and it can be changed at any time2.

  References:

  • Snowflake Documentation: Scaling Policy for Multi-cluster Warehouses
  • Snowflake Documentation: MAX_CONCURRENCY_LEVEL

  Questions 30

  An Architect with the ORGADMIN role wants to change a Snowflake account from an Enterprise edition to a Business Critical edition.

  How should this be accomplished?

  Options:

  A.

  Run an ALTER ACCOUNT command and create a tag of EDITION and set the tag to Business Critical.

  B.

  Use the account's ACCOUNTADMIN role to change the edition.

  C.

  Failover to a new account in the same region and specify the new account's edition upon creation.

  D.

  Contact Snowflake Support and request that the account's edition be changed.

  Show Answer Buy Now

  Answer:

  D

  Explanation:

  Explanation:

  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.

  References: This process is outlined in the Snowflake documentation, which specifies that changes to an account’s edition should be facilitated through Snowflake Support1.

  Questions 31

  An Architect needs to design a solution for building environments for development, test, and pre-production, all located in a single Snowflake account. The environments should be based on production data.

  Which solution would be MOST cost-effective and performant?

  Options:

  A.

  Use zero-copy cloning into transient tables.

  B.

  Use zero-copy cloning into permanent tables.

  C.

  Use CREATE TABLE ... AS SELECT (CTAS) statements.

  D.

  Use a Snowflake task to trigger a stored procedure to copy data.

  Show Answer Buy Now

  Answer:

  A

  Explanation:

  Explanation:

  Zero-copy cloning is a feature in Snowflake that allows for the creation of a clone of a database, schema, or table without duplicating any data, which is cost-effective as it saves on storage costs. Transient tables are temporary and do not incur storage costs for the time they are not accessed, making them a cost-effective option for development, test, and pre-production environments that do not require the durability of permanent tables123.

  References

  •Snowflake Documentation on Zero-Copy Cloning3.

  •Articles discussing the cost-effectiveness and performance benefits of zero-copy cloning12.

  Questions 32

  The data share exists between a data provider account and a data consumer account. Five tables from the provider account are being shared with the consumer account. The consumer role has been granted the imported privileges privilege.

  What will happen to the consumer account if a new table (table_6) is added to the provider schema?

  Options:

  A.

  The consumer role will automatically see the new table and no additional grants are needed.

  B.

  The consumer role will see the table only after this grant is given on the consumer side:

  grant imported privileges on database PSHARE_EDW_4TEST_DB to DEV_ROLE;

  C.

  The consumer role will see the table only after this grant is given on the provider side:

  use role accountadmin;

  Grant select on table EDW.ACCOUNTING.Table_6 to share PSHARE_EDW_4TEST;

  D.

  The consumer role will see the table only after this grant is given on the provider side:

  use role accountadmin;

  grant usage on database EDW to share PSHARE_EDW_4TEST ;

  grant usage on schema EDW.ACCOUNTING to share PSHARE_EDW_4TEST ;

  Grant select on table EDW.ACCOUNTING.Table_6 to database PSHARE_EDW_4TEST_DB ;

  Show Answer Buy Now

  Answer:

  D

  Explanation:

  Explanation:

  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.References:

  • Snowflake Documentation on Managing Access Control
  • Snowflake Documentation on Data Sharing

  Questions 33

  A healthcare company wants to share data with a medical institute. The institute is running a Standard edition of Snowflake; the healthcare company is running a Business Critical edition.

  How can this data be shared?

  Options:

  A.

  The healthcare company will need to change the institute’s Snowflake edition in the accounts panel.

  B.

  By default, sharing is supported from a Business Critical Snowflake edition to a Standard edition.

  C.

  Contact Snowflake and they will execute the share request for the healthcare company.

  D.

  Set the share_restriction parameter on the shared object to false.

  Show Answer Buy Now

  Answer:

  D

  Explanation:

  Explanation:

  By default, Snowflake does not allow sharing data from a Business Critical edition to a non-Business Critical edition. This is because Business Critical edition provides enhanced security and data protection features that are not available in lower editions. However, this restriction can be overridden by setting the share_restriction parameter on the shared object (database, schema, or table) to false. This parameter allows the data provider to explicitly allow sharing data with lower edition accounts. Note that this parameter can only be set by the data provider, not the data consumer. Also, setting this parameter to false may reduce the level of security and data protection for the shared data.

  References:

  • Enable Data Share:Business Critical Account to Lower Edition
  • Sharing Is Not Allowed From An Account on BUSINESS CRITICAL Edition to an Account On A Lower Edition
  • SQL Execution Error: Sharing is Not Allowed from an Account on BUSINESS CRITICAL Edition to an Account on a Lower Edition
  • Snowflake Editions | Snowflake Documentation

  Questions 34

  A company is using Snowflake in Azure in the Netherlands. The company analyst team also has data in JSON format that is stored in an Amazon S3 bucket in the AWS Singapore region that the team wants to analyze.

  The Architect has been given the following requirements:

  1. Provide access to frequently changing data

  2. Keep egress costs to a minimum

  3. Maintain low latency

  How can these requirements be met with the LEAST amount of operational overhead?

  Options:

  A.

  Use a materialized view on top of an external table against the S3 bucket in AWS Singapore.

  B.

  Use an external table against the S3 bucket in AWS Singapore and copy the data into transient tables.

  C.

  Copy the data between providers from S3 to Azure Blob storage to collocate, then use Snowpipe for data ingestion.

  D.

  Use AWS Transfer Family to replicate data between the S3 bucket in AWS Singapore and an Azure Netherlands Blob storage, then use an external table against the Blob storage.

  Show Answer Buy Now

  Answer:

  B

  Explanation:

  Explanation:

   Option A is the best design to meet the requirements because it uses a materialized view on top of an external table against the S3 bucket in AWS Singapore. A materialized view is a database object that contains the results of a query and can be refreshed periodically to reflect changes in the underlying data1. An external table is a table that references data files stored in a cloud storage service, such as Amazon S32. By using a materialized view on top of an external table, the company can provide access to frequently changing data, keep egress costs to a minimum, and maintain low latency. This is because the materialized view will cache the query results in Snowflake, reducing the need to access the external data files and incur network charges. The materialized view will also improve the query performance by avoiding scanning the external data files every time. The materialized view can be refreshed on a schedule or on demand to capture the changes in the external data files1.

  Option B is not the best design because it uses an external table against the S3 bucket in AWS Singapore and copies the data into transient tables. A transient table is a table that is not subject to the Time Travel and Fail-safe features of Snowflake, and is automatically purged after a period of time3. By using an external table and copying the data into transient tables, the company will incur more egress costs and operational overhead than using a materialized view. This is because the external table will access the external data files every time a query is executed, and the copy operation will also transfer data from S3 to Snowflake. The transient tables will also consume more storage space in Snowflake and require manual maintenance to ensure they are up to date.

  Option C is not the best design because it copies the data between providers from S3 to Azure Blob storage to collocate, then uses Snowpipe for data ingestion. Snowpipe is a service that automates the loading of data from external sources into Snowflake tables4. By copying the data between providers, the company will incur high egress costs and latency, as well as operational complexity and maintenance of the infrastructure. Snowpipe will also add another layer of processing and storage in Snowflake, which may not be necessary if the external data files are already in a queryable format.

  Option D is not the best design because it uses AWS Transfer Family to replicate data between the S3 bucket in AWS Singapore and an Azure Netherlands Blob storage, then uses an external table against the Blob storage. AWS Transfer Family is a service that enables secure and seamless transfer of files over SFTP, FTPS, and FTP to and from Amazon S3 or Amazon EFS5. By using AWS Transfer Family, the company will incur high egress costs and latency, as well as operational complexity and maintenance of the infrastructure. The external table will also access the external data files every time a query is executed, which may affect the query performance.

  References: 1: Materialized Views 2: External Tables 3: Transient Tables 4: Snowpipe Overview 5: AWS Transfer Family

  Questions 35

  An Architect for a multi-national transportation company has a system that is used to check the weather conditions along vehicle routes. The data is provided to drivers.

  The weather information is delivered regularly by a third-party company and this information is generated as JSON structure. Then the data is loaded into Snowflake in a column with a VARIANT data type. This

  table is directly queried to deliver the statistics to the drivers with minimum time lapse.

  A single entry includes (but is not limited to):

  - Weather condition; cloudy, sunny, rainy, etc.

  - Degree

  - Longitude and latitude

  - Timeframe

  - Location address

  - Wind

  The table holds more than 10 years' worth of data in order to deliver the statistics from different years and locations. The amount of data on the table increases every day.

  The drivers report that they are not receiving the weather statistics for their locations in time.

  What can the Architect do to deliver the statistics to the drivers faster?

  Options:

  A.

  Create an additional table in the schema for longitude and latitude. Determine a regular task to fill this information by extracting it from the JSON dataset.

  B.

  Add search optimization service on the variant column for longitude and latitude in order to query the information by using specific metadata.

  C.

  Divide the table into several tables for each year by using the timeframe information from the JSON dataset in order to process the queries in parallel.

  D.

  Divide the table into several tables for each location by using the location address information from the JSON dataset in order to process the queries in parallel.

  Show Answer Buy Now

  Answer:

  B

  Explanation:

  Explanation:

  To improve the performance of queries on semi-structured data, such as JSON stored in a VARIANT column, Snowflake’s search optimization service can be utilized. By adding search optimization specifically for the longitude and latitude fields within the VARIANT column, the system can perform point lookups and substring queries more efficiently. This will allow for faster retrieval of weather statistics, which is critical for the drivers to receive timely updates.

  References: The solution is supported by Snowflake documentation that details how search optimization can enhance query performance for semi-structured data1.

  Questions 36

  An Architect is troubleshooting a query with poor performance using the QUERY function. The Architect observes that the COMPILATION_TIME Is greater than the EXECUTION_TIME.

  What is the reason for this?

  Options:

  A.

  The query is processing a very large dataset.

  B.

  The query has overly complex logic.

  C.

  The query Is queued for execution.

  D.

  The query Is reading from remote storage

  Show Answer Buy Now

  Answer:

  B

  Explanation:

  Explanation:

  • 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.

  Questions 37

  Which Snowflake data modeling approach is designed for BI queries?

  Options:

  A.

  3 NF

  B.

  Star schema

  C.

  Data Vault

  D.

  Snowflake schema

  Show Answer Buy Now

  Answer:

  B

  Explanation:

  Explanation:

  In the context of business intelligence (BI) queries, which are typically focused on data analysis and reporting, the star schema is the most suitable data modeling approach.

  Option B: Star Schema - The star schema is a type of relational database schema that is widely used for developing data warehouses and data marts for BI purposes. It consists of a central fact table surrounded by dimension tables. The fact table contains the core data metrics, and the dimension tables contain descriptive attributes related to the fact data. The simplicity of the star schema allows for efficient querying and aggregation, which are common operations in BI reporting.

  Questions 38

  A user has activated primary and secondary roles for a session.

  What operation is the user prohibited from using as part of SQL actions in Snowflake using the secondary role?

  Options:

  A.

  Insert

  B.

  Create

  C.

  Delete

  D.

  Truncate

  Show Answer Buy Now

  Answer:

  B

  Explanation:

  Explanation:

  In Snowflake, when a user activates a secondary role during a session, certain privileges associated with DDL (Data Definition Language) operations are restricted. The CREATE statement, 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.References: Snowflake's security and access control documentation specifying the limitations and capabilities of primary versus secondary roles in session management.

  Questions 39

  What are purposes for creating a storage integration? (Choose three.)

  Options:

  A.

  Control access to Snowflake data using a master encryption key that is maintained in the cloud provider’s key management service.

  B.

  Store a generated identity and access management (IAM) entity for an external cloud provider regardless of the cloud provider that hosts the Snowflake account.

  C.

  Support multiple external stages using one single Snowflake object.

  D.

  Avoid supplying credentials when creating a stage or when loading or unloading data.

  E.

  Create private VPC endpoints that allow direct, secure connectivity between VPCs without traversing the public internet.

  F.

  Manage credentials from multiple cloud providers in one single Snowflake object.

  Show Answer Buy Now

  Answer:

  B, C, D

  Explanation:

  Explanation:

  The purpose of creating a storage integration in Snowflake includes:B. Store a generated identity and access management (IAM) entity for an external cloud provider - This helps in managing authentication and authorization with external cloud storage without embedding credentials in Snowflake. It supports various cloud providers like AWS, Azure, or GCP, ensuring that the identity management is streamlined across platforms.C. Support multiple external stages using one single Snowflake object - Storage integrations allow you to set up access configurations that can be reused across multiple external stages, simplifying the management of external data integrations.D. Avoid supplying credentials when creating a stage or when loading or unloading data - By using a storage integration, Snowflake can interact with external storage without the need to continuously manage or expose sensitive credentials, enhancing security and ease of operations.References: Snowflake documentation on storage integrations, found within the SnowPro Advanced: Architect course materials.

  Questions 40

  Why might a Snowflake Architect use a star schema model rather than a 3NF model when designing a data architecture to run in Snowflake? (Select TWO).

  Options:

  A.

  Snowflake cannot handle the joins implied in a 3NF data model.

  B.

  The Architect wants to remove data duplication from the data stored in Snowflake.

  C.

  The Architect is designing a landing zone to receive raw data into Snowflake.

  D.

  The Bl tool needs a data model that allows users to summarize facts across different dimensions, or to drill down from the summaries.

  E.

  The Architect wants to present a simple flattened single view of the data to a particular group of end users.

  Show Answer Buy Now

  Answer:

  D, E

  Explanation:

  Explanation:

   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.

  References:

  • 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

  Questions 41

  The Data Engineering team at a large manufacturing company needs to engineer data coming from many sources to support a wide variety of use cases and data consumer requirements which include:

  1) Finance and Vendor Management team members who require reporting and visualization

  2) Data Science team members who require access to raw data for ML model development

  3) Sales team members who require engineered and protected data for data monetization

  What Snowflake data modeling approaches will meet these requirements? (Choose two.)

  Options:

  A.

  Consolidate data in the company’s data lake and use EXTERNAL TABLES.

  B.

  Create a raw database for landing and persisting raw data entering the data pipelines.

  C.

  Create a set of profile-specific databases that aligns data with usage patterns.

  D.

  Create a single star schema in a single database to support all consumers’ requirements.

  E.

  Create a Data Vault as the sole data pipeline endpoint and have all consumers directly access the Vault.

  Show Answer Buy Now

  Answer:

  B, C

  Explanation:

  Explanation:

  To accommodate the diverse needs of different teams and use cases within a company, a flexible and multi-faceted approach to data modeling is required.

  Option B: By creating a raw database for landing and persisting raw data, you ensure that the Data Science team has access to unprocessed data for machine learning model development. This aligns with the best practices of having a staging area or raw data zone in a modern data architecture where raw data is ingested before being transformed or processed for different use cases.

  Option C: Having profile-specific databases means creating targeted databases that are designed to meet the specific requirements of each user profile or team within the company. For the Finance and Vendor Management teams, the data can be structured and optimized for reporting and visualization. For the Sales team, the database can include engineered and protected data that is suitable for data monetization efforts. This strategy not only aligns data with usage patterns but also helps in managing data access and security policies effectively.

  Questions 42

  A company wants to Integrate its main enterprise identity provider with federated authentication with Snowflake.

  The authentication integration has been configured and roles have been created in Snowflake. However, the users are not automatically appearing in Snowflake when created and their group membership is not reflected in their assigned rotes.

  How can the missing functionality be enabled with the LEAST amount of operational overhead?

  Options:

  A.

  OAuth must be configured between the identity provider and Snowflake. Then the authorization server must be configured with the right mapping of users and roles.

  B.

  OAuth must be configured between the identity provider and Snowflake. Then the authorization server must be configured with the right mapping of users, and the resource server must be configured with the right mapping of role assignment.

  C.

  SCIM must be enabled between the identity provider and Snowflake. Once both are synchronized through SCIM, their groups will get created as group accounts in Snowflake and the proper roles can be granted.

  D.

  SCIM must be enabled between the identity provider and Snowflake. Once both are synchronized through SCIM. users will automatically get created and their group membership will be reflected as roles In Snowflake.

  Show Answer Buy Now

  Answer:

  D

  Explanation:

  Explanation:

  The best way to integrate an enterprise identity provider with federated authentication and enable automatic user creation and role assignment in Snowflake is to use SCIM (System for Cross-domain Identity Management). SCIM allows Snowflake to synchronize with the identity provider and create users and groups based on the information provided by the identity provider. The groups are mapped to roles in Snowflake, and the users are assigned the roles based on their group membership. This way, the identity provider remains the source of truth for user and group management, and Snowflake automatically reflects the changes without manual intervention. The other options are either incorrect or incomplete, as they involve using OAuth, which is a protocol for authorization, not authentication or user provisioning, and require additional configuration of authorization and resource servers.

  Questions 43

  A retail company has 2000+ stores spread across the country. Store Managers report that they are having trouble running key reports related to inventory management, sales targets, payroll, and staffing during business hours. The Managers report that performance is poor and time-outs occur frequently.

  Currently all reports share the same Snowflake virtual warehouse.

  How should this situation be addressed? (Select TWO).

  Options:

  A.

  Use a Business Intelligence tool for in-memory computation to improve performance.

  B.

  Configure a dedicated virtual warehouse for the Store Manager team.

  C.

  Configure the virtual warehouse to be multi-clustered.

  D.

  Configure the virtual warehouse to size 4-XL

  E.

  Advise the Store Manager team to defer report execution to off-business hours.

  Show Answer Buy Now

  Answer:

  B, C

  Explanation:

  Explanation:

  The best way to address the performance issues and time-outs faced by the Store Manager team is to configure a dedicated virtual warehouse for them and make it multi-clustered. This will allow them to run their reports independently from other workloads and scale up or down the compute resources as needed. A dedicated virtual warehouse will also enable them to apply specific security and access policies for their data. A multi-clustered virtual warehouse will provide high availability and concurrency for their queries and avoid queuing or throttling.

  Using a Business Intelligence tool for in-memory computation may improve performance, but it will not solve the underlying issue of insufficient compute resources in the shared virtual warehouse. It will also introduce additional costs and complexity for the data architecture.

  Configuring the virtual warehouse to size 4-XL may increase the performance, but it will also increase the cost and may not be optimal for the workload. It will also not address the concurrency and availability issues that may arise from sharing the virtual warehouse with other workloads.

  Advising the Store Manager team to defer report execution to off-business hours may reduce the load on the shared virtual warehouse, but it will also reduce the timeliness and usefulness of the reports for the business. It will also not guarantee that the performance issues and time-outs will not occur at other times.

  References:

  • Snowflake Architect Training
  • Snowflake SnowPro Advanced Architect Certification - Preparation Guide
  • SnowPro Advanced: Architect Exam Study Guide

  Questions 44

  Two queries are run on the customer_address table:

  create or replace TABLE CUSTOMER_ADDRESS ( CA_ADDRESS_SK NUMBER(38,0), CA_ADDRESS_ID VARCHAR(16), CA_STREET_NUMBER VARCHAR(IO) CA_STREET_NAME VARCHAR(60), CA_STREET_TYPE VARCHAR(15), CA_SUITE_NUMBER VARCHAR(10), CA_CITY VARCHAR(60), CA_COUNTY

  VARCHAR(30), CA_STATE VARCHAR(2), CA_ZIP VARCHAR(10), CA_COUNTRY VARCHAR(20), CA_GMT_OFFSET NUMBER(5,2), CA_LOCATION_TYPE

  VARCHAR(20) );

  ALTER TABLE DEMO_DB.DEMO_SCH.CUSTOMER_ADDRESS ADD SEARCH OPTIMIZATION ON SUBSTRING(CA_ADDRESS_ID);

  Which queries will benefit from the use of the search optimization service? (Select TWO).

  Options:

  A.

  select * from DEMO_DB.DEMO_SCH.CUSTOMER_ADDRESS Where substring(CA_ADDRESS_ID,1,8)= substring('AAAAAAAAPHPPLBAAASKDJHASLKDJHASKJD',1,8);

  B.

  select * from DEMO_DB.DEMO_SCH.CUSTOMER_ADDRESS Where CA_ADDRESS_ID= substring('AAAAAAAAPHPPLBAAASKDJHASLKDJHASKJD',1,16);

  C.

  select*fromDEMO_DB.DEMO_SCH.CUSTOMER_ADDRESSWhereCA_ADDRESS_IDLIKE ’%BAAASKD%';

  D.

  select*fromDEMO_DB.DEMO_SCH.CUSTOMER_ADDRESSWhereCA_ADDRESS_IDLIKE '%PHPP%';

  E.

  select*fromDEMO_DB.DEMO_SCH.CUSTOMER_ADDRESSWhereCA_ADDRESS_IDNOT LIKE '%AAAAAAAAPHPPL%';

  Show Answer Buy Now

  Answer:

  A, B

  Explanation:

  Explanation:

  The use of the search optimization service in Snowflake is particularly effective when queries involve operations that match exact substrings or start from the beginning of a string. The ALTER TABLE command adding search optimization specifically for substrings on the CA_ADDRESS_ID field allows the service to create an optimized search path for queries using substring matches.

  • Option A benefits because it directly matches a substring from the start of the CA_ADDRESS_ID, aligning with the optimization's capability to quickly locate records based on the beginning segments of strings.
  • Option B also benefits, despite performing a full equality check, because it essentially compares the full length of CA_ADDRESS_ID to a substring, which can leverage the substring index for efficient retrieval.Options C, D, and E involve patterns that do not start from the beginning of the string or use negations, which are not optimized by the search optimization service configured for starting substring matches.References: Snowflake's documentation on the use of search optimization for substring matching in SQL queries.

  Questions 45

  An Architect uses COPY INTO with the ON_ERROR=SKIP_FILE option to bulk load CSV files into a table called TABLEA, using its table stage. One file named file5.csv fails to load. The Architect fixes the file and re-loads it to the stage with the exact same file name it had previously.

  Which commands should the Architect use to load only file5.csv file from the stage? (Choose two.)

  Options:

  A.

  COPY INTO tablea FROM @%tablea RETURN_FAILED_ONLY = TRUE;

  B.

  COPY INTO tablea FROM @%tablea;

  C.

  COPY INTO tablea FROM @%tablea FILES = ('file5.csv');

  D.

  COPY INTO tablea FROM @%tablea FORCE = TRUE;

  E.

  COPY INTO tablea FROM @%tablea NEW_FILES_ONLY = TRUE;

  F.

  COPY INTO tablea FROM @%tablea MERGE = TRUE;

  Show Answer Buy Now

  Answer:

  B, C

  Explanation:

  Explanation:

  • Option A (RETURN_FAILED_ONLY) will only load files that previously failed to load. Since file5.csv already exists in the stage with the same name, it will not be considered a new file and will not be loaded.
  • Option D (FORCE) will overwrite any existing data in the table. This is not desired as we only want to load the data from file5.csv.
  • Option E (NEW_FILES_ONLY) will only load files that have been added to the stage since the last COPY command. This will not work because file5.csv was already in the stage before it was fixed.
  • Option F (MERGE) is used to merge data from a stage into an existing table, creating new rows for any data not already present. This is not needed in this case as we simply want to load the data from file5.csv.

  Therefore, the architect can use either COPY INTO tablea FROM @%tablea or COPY INTO tablea FROM @%tablea FILES = ('file5.csv') to load only file5.csv from the stage. Both options will load the data from the specified file without overwriting any existing data or requiring additional configuration

  Questions 46

  Which Snowflake objects can be used in a data share? (Select TWO).

  Options:

  A.

  Standard view

  B.

  Secure view

  C.

  Stored procedure

  D.

  External table

  E.

  Stream

  Show Answer Buy Now

  Answer:

  B, D

  Explanation:

  Explanation:

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

  Questions 47

  An Architect is troubleshooting a query with poor performance using the QUERY_HIST0RY function. The Architect observes that the COMPILATIONJHME is greater than the EXECUTIONJTIME.

  What is the reason for this?

  Options:

  A.

  The query is processing a very large dataset.

  B.

  The query has overly complex logic.

  C.

  The query is queued for execution.

  D.

  The query is reading from remote storage.

  Show Answer Buy Now

  Answer:

  B

  Explanation:

  Explanation:

   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

  Questions 48

  What are characteristics of the use of transactions in Snowflake? (Select TWO).

  Options:

  A.

  Explicit transactions can contain DDL, DML, and query statements.

  B.

  The autocommit setting can be changed inside a stored procedure.

  C.

  A transaction can be started explicitly by executing a begin work statement and end explicitly by executing a commit work statement.

  D.

  A transaction can be started explicitly by executing a begin transaction statement and end explicitly by executing an end transaction statement.

  E.

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

  Show Answer Buy Now

  Answer:

  A, C

  Explanation:

  Explanation:

  A. Snowflake's transactions can indeed include DDL (Data Definition Language), DML (Data Manipulation Language), and query statements. When executed within a transaction block, they all contribute to the atomicity of the transaction—either all of them commit together or none at all.C. Snowflake supports explicit transaction control through the use of the BEGIN TRANSACTION (or simply BEGIN) and COMMIT statements. Alternatively, the BEGIN WORK and COMMIT WORK syntax is also supported, which is a standard SQL syntax for initiating and ending transactions, respectively.Note: The END TRANSACTION statement is not used in Snowflake to end a transaction; the correct statement is COMMIT or COMMIT WORK.

  Snowflake Certification |

  Exam Code: ARA-R01

  Exam Name: SnowPro Advanced: Architect Recertification Exam

  Last Update: May 5, 2024

  Questions: 162

  

  ARA-R01 PDF

  $28  $80

  $56  $159.99

   Add to Cart

  

  ARA-R01 Testing Engine

  $33.25  $95

  $63  $179.99

   Add to Cart

  

  ARA-R01 PDF + Testing Engine

  $45.5  $130

   Add to Cart

  Quick Links

  • Home
  • All Exams
  • Contact us
  • About us
  • Guarantee

  Recently New Released Certification Exams

  • SPS May 5, 2024
  • NPPE May 5, 2024
  • PVIP May 5, 2024
  • C_HAMOD_2404 May 5, 2024
  • HFCP May 5, 2024
  • Salesforce-Contact-Center May 5, 2024
  • Energy-and-Utilities-Cloud May 5, 2024
  • NetSuite-Administrator May 5, 2024
  • TCA-Tibco-BusinessWorks May 5, 2024
  • KX3-003 May 5, 2024
  • Scripting-and-Programming-Foundations May 5, 2024
  • 112-51 May 5, 2024
  • CIS-FSM May 5, 2024
  • NSK101 May 5, 2024
  • Cybersecurity-Audit-Certificate May 5, 2024
  • LEED-AP-ID+C May 5, 2024
  • Media-Cloud-Consultant May 5, 2024
  • ISTQB-Agile-Public May 5, 2024
  • LEED-AP-O+M May 5, 2024
  • Sitecore-XM-Cloud-Developer May 5, 2024

  Site Secure

  

  TESTED 06 May 2024

  Copyright © 2014-2024 CramTick. All Rights Reserved