ARA-C01 SnowPro Advanced: Architect Certification Exam Questions and Answers

Tri-Secret Secure is a feature that allows customers to use their own key, called the customer-managed key (CMK), in addition to the Snowflake-managed key, to create a composite master key that encrypts the data in Snowflake. The composite master key is also known as the account master key (AMK), as it is unique for each account and encrypts the table master keys (TMKs) that encrypt the file keys that encrypt the data files. The customer-managed key is used at the account level, not at the root level, the table level, or the micro-partition level. The root level is protected by a hardware security module (HSM), the table level is protected by the TMKs, and the micro-partition level is protected by the file keys12. References:

• Understanding Encryption Key Management in Snowflake
• Tri-Secret Secure FAQ for Snowflake on AWS

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.

• The correct answer is D because the CURRENT_TIME function returns the current timestamp at the start of the statement execution, not at the time of the record insertion. Therefore, if the load operation takes some time to complete, the CURRENT_TIME value may be earlier than the actual load time.
• Option A is incorrect because the parameter setup mismatches do not affect the timestamp values. The parameters are used to control the behavior and performance of the load operation, such as the file format, the error handling, the purge option, etc.
• Option B is incorrect because the Snowflake timezone parameter and the cloud provider’s parameters are independent of each other. The Snowflake timezone parameter determines the session timezone for displaying and converting timestamp values, while the cloud provider’s parameters determine the physical location and configuration of the storage and compute resources.
• Option C is incorrect because the localtimestamp and systimestamp functions are not relevant for the Snowpipe load operation. The localtimestamp function returns the current timestamp in the session timezone, while the systimestamp function returns the current timestamp in the system timezone. Neither of them reflect the actual load time of the records. 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.
• 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.

According to the Snowflake documentation1, the Business Critical edition offers the following features that are relevant to the question:

• Support for Multi-Factor Authentication (MFA): This is a standard feature available in all Snowflake editions1.
• A minimum of 2 months of Time Travel availability: This is an enterprise feature that allows users to access historical data for up to 90 days1.
• Database replication in between different regions: This is an enterprise feature that enables users to replicate databases across different regions or cloud platforms1.
• Native support for JDBC and ODBC: This is a standard feature available in all Snowflake editions1.
• Customer-managed encryption keys using Tri-Secret Secure: This is a business critical feature that provides enhanced security and data protection by allowing customers to manage their own encryption keys1.
• Support for Payment Card Industry Data Security Standards (PCI DSS): This is a business critical feature that ensures compliance with PCI DSS regulations for handling sensitive cardholder data1.

Therefore, the minimum Snowflake edition that should be selected during account creation to provide all the listed services is the Business Critical edition.

References:

• Snowflake Editions | Snowflake Documentation

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]

The AUTO_SUSPEND parameter controls the amount of time, in seconds, of inactivity after which a warehouse is automatically suspended. If the parameter is set to NULL, the warehouse never suspends. If the parameter is set to ‘0’, the warehouse suspends immediately after executing a query. Therefore, the execution behavior of the two virtual warehouses will be different depending on the AUTO_SUSPEND value. The warehouse with NULL value will keep running until it is manually suspended or the resource monitor limits are reached. The warehouse with ‘0’ value will suspend as soon as it finishes a query and release the compute resources. References:

• ALTER WAREHOUSE
• Parameters

• 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@189de0b0

  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@1a394520

  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@189df1f0

  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@18883860

  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 11

  A media company needs a data pipeline that will ingest customer review data into a Snowflake table, and apply some transformations. The company also needs to use Amazon Comprehend to do sentiment analysis and make the de-identified final data set available publicly for advertising companies who use different cloud providers in different regions.

  The data pipeline needs to run continuously ang efficiently as new records arrive in the object storage leveraging event notifications. Also, the operational complexity, maintenance of the infrastructure, including platform upgrades and security, and the development effort should be minimal.

  Which design will meet these requirements?

  Options:

  A.

  Ingest the data using COPY INTO and use streams and tasks to orchestrate transformations. Export the data into Amazon S3 to do model inference with Amazon Comprehend and ingest the data back into a Snowflake table. Then create a listing in the Snowflake Marketplace to make the data available to other companies.

  B.

  Ingest the data using Snowpipe and use streams and tasks to orchestrate transformations. Create an external function to do model inference with Amazon Comprehend and write the final records to a Snowflake table. Then create a listing in the Snowflake Marketplace to make the data available to other companies.

  C.

  Ingest the data into Snowflake using Amazon EMR and PySpark using the Snowflake Spark connector. Apply transformations using another Spark job. Develop a python program to do model inference by leveraging the Amazon Comprehend text analysis API. Then write the results to a Snowflake table and create a listing in the Snowflake Marketplace to make the data available to other companies.

  D.

  Ingest the data using Snowpipe and use streams and tasks to orchestrate transformations. Export the data into Amazon S3 to do model inference with Amazon Comprehend and ingest the data back into a Snowflake table. Then create a listing in the Snowflake Marketplace to make the data available to other companies.

  Show Answer Buy Now

  Answer:

  B

  Explanation:

  Explanation:

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

  References:

  • Snowpipe Overview | Snowflake Documentation
  • Introduction to Data Pipelines | Snowflake Documentation
  • External Functions Overview | Snowflake Documentation
  • Snowflake Data Marketplace Overview | Snowflake Documentation

  Questions 12

  An Architect on a new project has been asked to design an architecture that meets Snowflake security, compliance, and governance requirements as follows:

  1) Use Tri-Secret Secure in Snowflake

  2) Share some information stored in a view with another Snowflake customer

  3) Hide portions of sensitive information from some columns

  4) Use zero-copy cloning to refresh the non-production environment from the production environment

  To meet these requirements, which design elements must be implemented? (Choose three.)

  Options:

  A.

  Define row access policies.

  B.

  Use the Business-Critical edition of Snowflake.

  C.

  Create a secure view.

  D.

  Use the Enterprise edition of Snowflake.

  E.

  Use Dynamic Data Masking.

  F.

  Create a materialized view.

  Show Answer Buy Now

  Answer:

  B, C, E

  Explanation:

  Explanation:

  These three design elements are required to meet the security, compliance, and governance requirements for the project.

  • To use Tri-Secret Secure in Snowflake, the Business Critical edition of Snowflake is required. This edition provides enhanced data protection features, such as customer-managed encryption keys, that are not available in lower editions. Tri-Secret Secure is a feature that combines a Snowflake-maintained key and a customer-managed key to create a composite master key to encrypt the data in Snowflake1.
  • To share some information stored in a view with another Snowflake customer, a secure view is recommended. A secure view is a view that hides the underlying data and the view definition from unauthorized users. Only the owner of the view and the users who are granted the owner’s role can see the view definition and the data in the base tables of the view2. A secure view can be shared with another Snowflake account using a data share3.
  • To hide portions of sensitive information from some columns, Dynamic Data Masking can be used. Dynamic Data Masking is a feature that allows applying masking policies to columns to selectively mask plain-text data at query time. Depending on the masking policy conditions and the user’s role, the data can be fully or partially masked, or shown as plain-text4.

  Questions 13

  An Architect Is designing a data lake with Snowflake. The company has structured, semi-structured, and unstructured data. The company wants to save the data inside the data lake within the Snowflake system. The company is planning on sharing data among Its corporate branches using Snowflake data sharing.

  What should be considered when sharing the unstructured data within Snowflake?

  Options:

  A.

  A pre-signed URL should be used to save the unstructured data into Snowflake in order to share data over secure views, with no time limit for the URL.

  B.

  A scoped URL should be used to save the unstructured data into Snowflake in order to share data over secure views, with a 24-hour time limit for the URL.

  C.

  A file URL should be used to save the unstructured data into Snowflake in order to share data over secure views, with a 7-day time limit for the URL.

  D.

  A file URL should be used to save the unstructured data into Snowflake in order to share data over secure views, with the "expiration_time" argument defined for the URL time limit.

  Show Answer Buy Now

  Answer:

  D

  Explanation:

  Explanation:

  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

  Questions 14

  What Snowflake system functions are used to view and or monitor the clustering metadata for a table? (Select TWO).

  Options:

  A.

  SYSTEMSCLUSTERING

  B.

  SYSTEMSTABLE_CLUSTERING

  C.

  SYSTEMSCLUSTERING_DEPTH

  D.

  SYSTEMSCLUSTERING_RATIO

  E.

  SYSTEMSCLUSTERING_INFORMATION

  Show Answer Buy Now

  Answer:

  C, E

  Explanation:

  Explanation:

  The Snowflake system functions used to view and monitor the clustering metadata for a table are:

  • SYSTEM$CLUSTERING_INFORMATION
  • SYSTEM$CLUSTERING_DEPTH

  Comprehensive But Short Explanation:

  • The SYSTEM$CLUSTERING_INFORMATION function in Snowflake returns a variety of clustering information for a specified table. This information includes the average clustering depth, total number of micro-partitions, total constant partition count, average overlaps, average depth, and a partition depth histogram. This function allows you to specify either one or multiple columns for which the clustering information is returned, and it returns this data in JSON format.
  • The SYSTEM$CLUSTERING_DEPTH function computes the average depth of a table based on specified columns or the clustering key defined for the table. A lower average depth indicates that the table is better clustered with respect to the specified columns. This function also allows specifying columns to calculate the depth, and the values need to be enclosed in single quotes.

  References:

  • SYSTEM$CLUSTERING_INFORMATION: Snowflake Documentation
  • SYSTEM$CLUSTERING_DEPTH: Snowflake Documentation

  Questions 15

  An Architect needs to allow a user to create a database from an inbound share.

  To meet this requirement, the user’s role must have which privileges? (Choose two.)

  Options:

  A.

  IMPORT SHARE;

  B.

  IMPORT PRIVILEGES;

  C.

  CREATE DATABASE;

  D.

  CREATE SHARE;

  E.

  IMPORT DATABASE;

  Show Answer Buy Now

  Answer:

  C, E

  Explanation:

  Explanation:

  According to the Snowflake documentation, to create a database from an inbound share, the user’s role must have the following privileges:

  • The CREATE DATABASE privilege on the current account. This privilege allows the user to create a new database in the account1.
  • The IMPORT DATABASE privilege on the share. This privilege allows the user to import a database from the share into the account2. The other privileges listed are not relevant for this requirement. The IMPORT SHARE privilege is used to import a share into the account, not a database3. The IMPORT PRIVILEGES privilege is used to import the privileges granted on the shared objects, not the objects themselves2. The CREATE SHARE privilege is used to create a share to provide data to other accounts, not to consume data from other accounts4.

  References:

  • CREATE DATABASE | Snowflake Documentation
  • Importing Data from a Share | Snowflake Documentation
  • Importing a Share | Snowflake Documentation
  • CREATE SHARE | Snowflake Documentation

  Questions 16

  Which SQL alter command will MAXIMIZE memory and compute resources for a Snowpark stored procedure when executed on the snowpark_opt_wh warehouse?

  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:

   To maximize memory and compute resources for a Snowpark stored procedure, you need to set the MAX_CONCURRENCY_LEVEL parameter for the warehouse that executes the stored procedure. This parameter determines the maximum number of concurrent queries that can run on a single warehouse. By setting it to 16, you ensure that the warehouse can use all the available CPU cores and memory on a single node, which is the optimal configuration for Snowpark-optimized warehouses. This will improve the performance and efficiency of the stored procedure, as it will not have to share resources with other queries or nodes. The other options are incorrect because they either do not change the MAX_CONCURRENCY_LEVEL parameter, or they set it to a lower value than 16, which will reduce the memory and compute resources for the stored procedure. References:

  • [Snowpark-optimized Warehouses] 1
  • [Training Machine Learning Models with Snowpark Python] 2
  • [Snowflake Shorts: Snowpark Optimized Warehouses] 3

  Questions 17

  Based on the Snowflake object hierarchy, what securable objects belong directly to a Snowflake account? (Select THREE).

  Options:

  A.

  Database

  B.

  Schema

  C.

  Table

  D.

  Stage

  E.

  Role

  F.

  Warehouse

  Show Answer Buy Now

  Answer:

  A, E, F

  Explanation:

  Explanation:

  • A securable object is an entity to which access can be granted in Snowflake. Securable objects include databases, schemas, tables, views, stages, pipes, functions, procedures, sequences, tasks, streams, roles, warehouses, and shares1.
  • The Snowflake object hierarchy is a logical structure that organizes the securable objects in a nested manner. The top-most container is the account, which contains all the databases, roles, and warehouses for the customer organization. Each database contains schemas, which in turn contain tables, views, stages, pipes, functions, procedures, sequences, tasks, and streams. Each role can be granted privileges on other roles or securable objects. Each warehouse can be used to execute queries on securable objects2.
  • Based on the Snowflake object hierarchy, the securable objects that belong directly to a Snowflake account are databases, roles, and warehouses. These objects are created and managed at the account level, and do not depend on any other securable object. The other options are not correct because:

  References:

  • 1: Overview of Access Control | Snowflake Documentation
  • 2: Securable Objects | Snowflake Documentation
  • 3: CREATE SCHEMA | Snowflake Documentation
  • 4: CREATE TABLE | Snowflake Documentation
  • [5]: CREATE STAGE | Snowflake Documentation

  Questions 18

  What built-in Snowflake features make use of the change tracking metadata for a table? (Choose two.)

  Options:

  A.

  The MERGE command

  B.

  The UPSERT command

  C.

  The CHANGES clause

  D.

  A STREAM object

  E.

  The CHANGE_DATA_CAPTURE command

  Show Answer Buy Now

  Answer:

  A, D

  Explanation:

  Explanation:

  In Snowflake, the change tracking metadata for a table is utilized by the MERGE command and the STREAM object. The MERGE command uses change tracking to determine how to apply updates and inserts efficiently based on differences between source and target tables. STREAM objects, on the other hand, specifically capture and store change data, enabling incremental processing based on changes made to a table since the last stream offset was committed.References: Snowflake Documentation on MERGE and STREAM Objects.

  Questions 19

  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 20

  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 21

  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.

  Questions 22

  A Snowflake Architect is designing an application and tenancy strategy for an organization where strong legal isolation rules as well as multi-tenancy are requirements.

  Which approach will meet these requirements if Role-Based Access Policies (RBAC) is a viable option for isolating tenants?

  Options:

  A.

  Create accounts for each tenant in the Snowflake organization.

  B.

  Create an object for each tenant strategy if row level security is viable for isolating tenants.

  C.

  Create an object for each tenant strategy if row level security is not viable for isolating tenants.

  D.

  Create a multi-tenant table strategy if row level security is not viable for isolating tenants.

  Show Answer Buy Now

  Answer:

  A

  Explanation:

  Explanation:

  In a scenario where strong legal isolation is required alongside the need for multi-tenancy, the most effective approach is to create separate accounts for each tenant within the Snowflake organization. This approach ensures complete isolation of data, resources, and management, adhering to strict legal and compliance requirements. Role-Based Access Control (RBAC) further enhances security by allowing granular control over who can access what resources within each account. This solution leverages Snowflake’s capabilities for managing multiple accounts under a single organization umbrella, ensuring that each tenant's data and operations are isolated from others.References: Snowflake documentation on multi-tenancy and account management, part of the SnowPro Advanced: Architect learning path.

  Questions 23

  A company is following the Data Mesh principles, including domain separation, and chose one Snowflake account for its data platform.

  An Architect created two data domains to produce two data products. The Architect needs a third data domain that will use both of the data products to create an aggregate data product. The read access to the data products will be granted through a separate role.

  Based on the Data Mesh principles, how should the third domain be configured to create the aggregate product if it has been granted the two read roles?

  Options:

  A.

  Use secondary roles for all users.

  B.

  Create a hierarchy between the two read roles.

  C.

  Request a technical ETL user with the sysadmin role.

  D.

  Request that the two data domains share data using the Data Exchange.

  Show Answer Buy Now

  Answer:

  D

  Explanation:

  Explanation:

  In the scenario described, where a third data domain needs access to two existing data products in a Snowflake account structured according to Data Mesh principles, the best approach is to utilize Snowflake’s Data Exchange functionality. Option D is correct as it facilitates the sharing and governance of data across different domains efficiently and securely. Data Exchange allows domains to publish and subscribe to live data products, enabling real-time data collaboration and access management in a governed manner. This approach is in line with Data Mesh principles, which advocate for decentralized data ownership and architecture, enhancing agility and scalability across the organization.References:

  • Snowflake Documentation on Data Exchange
  • Articles on Data Mesh Principles in Data Management

  Questions 24

  How is the change of local time due to daylight savings time handled in Snowflake tasks? (Choose two.)

  Options:

  A.

  A task scheduled in a UTC-based schedule will have no issues with the time changes.

  B.

  Task schedules can be designed to follow specified or local time zones to accommodate the time changes.

  C.

  A task will move to a suspended state during the daylight savings time change.

  D.

  A frequent task execution schedule like minutes may not cause a problem, but will affect the task history.

  E.

  A task schedule will follow only the specified time and will fail to handle lost or duplicated hours.

  Show Answer Buy Now

  Answer:

  A, B

  Explanation:

  Explanation:

  According to the Snowflake documentation1 and the web search results2, these two statements are true about how the change of local time due to daylight savings time is handled in Snowflake tasks. A task is a feature that allows scheduling and executing SQL statements or stored procedures in Snowflake. A task can be scheduled using a cron expression that specifies the frequency and time zone of the task execution.

  • A task scheduled in a UTC-based schedule will have no issues with the time changes. UTC is a universal time standard that does not observe daylight savings time. Therefore, a task that uses UTC as the time zone will run at the same time throughout the year, regardless of the local time changes1.
  • Task schedules can be designed to follow specified or local time zones to accommodate the time changes. Snowflake supports using any valid IANA time zone identifier in the cron expression for a task. This allows the task to run according to the local time of the specified time zone, which may include daylight savings time adjustments. For example, a task that uses Europe/London as the time zone will run one hour earlier or later when the local time switches between GMT and BST12.

  References:

  • Snowflake Documentation: Scheduling Tasks
  • Snowflake Community: Do the timezones used in scheduling tasks in Snowflake adhere to daylight savings?

  Questions 25

  In a managed access schema, what are characteristics of the roles that can manage object privileges? (Select TWO).

  Options:

  A.

  Users with the SYSADMIN role can grant object privileges in a managed access schema.

  B.

  Users with the SECURITYADMIN role or higher, can grant object privileges in a managed access schema.

  C.

  Users who are database owners can grant object privileges in a managed access schema.

  D.

  Users who are schema owners can grant object privileges in a managed access schema.

  E.

  Users who are object owners can grant object privileges in a managed access schema.

  Show Answer Buy Now

  Answer:

  B, D

  Explanation:

  Explanation:

  In a managed access schema, the privilege management is centralized with the schema owner, who has the authority to grant object privileges within the schema. Additionally, the SECURITYADMIN role has the capability to manage object grants globally, which includes within managed access schemas. Other roles, such as SYSADMIN or database owners, do not inherently have this privilege unless explicitly granted.

  References: The verified answers are based on Snowflake’s official documentation, which outlines the roles and privileges associated with managed access schemas12.

  Questions 26

  Which of the following are characteristics of how row access policies can be applied to external tables? (Choose three.)

  Options:

  A.

  An external table can be created with a row access policy, and the policy can be applied to the VALUE column.

  B.

  A row access policy can be applied to the VALUE column of an existing external table.

  C.

  A row access policy cannot be directly added to a virtual column of an external table.

  D.

  External tables are supported as mapping tables in a row access policy.

  E.

  While cloning a database, both the row access policy and the external table will be cloned.

  F.

  A row access policy cannot be applied to a view created on top of an external table.

  Show Answer Buy Now

  Answer:

  A, B, C

  Explanation:

  Explanation:

  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

  Questions 27

  An Architect has a VPN_ACCESS_LOGS table in the SECURITY_LOGS schema containing timestamps of the connection and disconnection, username of the user, and summary statistics.

  What should the Architect do to enable the Snowflake search optimization service on this table?

  Options:

  A.

  Assume role with OWNERSHIP on future tables and ADD SEARCH OPTIMIZATION on the SECURITY_LOGS schema.

  B.

  Assume role with ALL PRIVILEGES including ADD SEARCH OPTIMIZATION in the SECURITY LOGS schema.

  C.

  Assume role with OWNERSHIP on VPN_ACCESS_LOGS and ADD SEARCH OPTIMIZATION in the SECURITY_LOGS schema.

  D.

  Assume role with ALL PRIVILEGES on VPN_ACCESS_LOGS and ADD SEARCH OPTIMIZATION in the SECURITY_LOGS schema.

  Show Answer Buy Now

  Answer:

  C

  Explanation:

  Explanation:

  According to the SnowPro Advanced: Architect Exam Study Guide, to enable the search optimization service on a table, the user must have the ADD SEARCH OPTIMIZATION privilege on the table and the schema. The privilege can be granted explicitly or inherited from a higher-level object, such as a database or a role. The OWNERSHIP privilege on a table implies the ADD SEARCH OPTIMIZATION privilege, so the user who owns the table can enable the search optimization service on it. Therefore, the correct answer is to assume a role with OWNERSHIP on VPN_ACCESS_LOGS and ADD SEARCH OPTIMIZATION in the SECURITY_LOGS schema. This will allow the user to enable the search optimization service on the VPN_ACCESS_LOGS table and any future tables created in the SECURITY_LOGS schema. The other options are incorrect because they either grant excessive privileges or do not grant the required privileges on the table or the schema. References:

  • SnowPro Advanced: Architect Exam Study Guide, page 11, section 2.3.1
  • Snowflake Documentation: Enabling the Search Optimization Service

  Questions 28

  Consider the following scenario where a masking policy is applied on the CREDICARDND column of the CREDITCARDINFO table. The masking policy definition Is as follows:

  

  Sample data for the CREDITCARDINFO table is as follows:

  NAME EXPIRYDATE CREDITCARDNO

  JOHN DOE 2022-07-23 4321 5678 9012 1234

  if the Snowflake system rotes have not been granted any additional roles, what will be the result?

  Options:

  A.

  The sysadmin can see the CREDICARDND column data in clear text.

  B.

  The owner of the table will see the CREDICARDND column data in clear text.

  C.

  Anyone with the Pl_ANALYTICS role will see the last 4 characters of the CREDICARDND column data in dear text.

  D.

  Anyone with the Pl_ANALYTICS role will see the CREDICARDND column as*** 'MASKED* **'.

  Show Answer Buy Now

  Answer:

  D

  Explanation:

  Explanation:

  • The masking policy defined in the image indicates that if a user has the PI_ANALYTICS role, they will be able to see the last 4 characters of the CREDITCARDNO column data in clear text. Otherwise, they will see ‘MASKED’. Since Snowflake system roles have not been granted any additional roles, they won’t have the PI_ANALYTICS role and therefore cannot view the last 4 characters of credit card numbers.
  • To apply a masking policy on a column in Snowflake, you need to use the ALTER TABLE … ALTER COLUMN command or the ALTER VIEW command and specify the policy name. For example, to apply the creditcardno_mask policy on the CREDITCARDNO column of the CREDITCARDINFO table, you can use the following command:

  ALTER TABLE CREDITCARDINFO ALTER COLUMN CREDITCARDNO SET MASKING POLICY creditcardno_mask;

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

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

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

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

  References: 1: https://docs.snowflake.com/en/sql-reference/sql/create-masking-policy  2: https://docs.snowflake.com/en/user-guide/security-column-ddm-use  3: https://docs.snowflake.com/en/sql-reference/sql/alter-masking-policy

  Questions 29

  A user named USER_01 needs access to create a materialized view on a schema EDW. STG_SCHEMA. How can this access be provided?

  Options:

  A.

  GRANT CREATE MATERIALIZED VIEW ON SCHEMA EDW.STG_SCHEMA TO USER USER_01;

  B.

  GRANT CREATE MATERIALIZED VIEW ON DATABASE EDW TO USER USERJD1;

  C.

  GRANT ROLE NEW_ROLE TO USER USER_01;

  GRANT CREATE MATERIALIZED VIEW ON SCHEMA ECW.STG_SCHEKA TO NEW_ROLE;

  D.

  GRANT ROLE NEW_ROLE TO USER_01;

  GRANT CREATE MATERIALIZED VIEW ON EDW.STG_SCHEMA TO NEW_ROLE;

  Show Answer Buy Now

  Answer:

  A

  Explanation:

  Explanation:

  • The correct answer is A because it grants the specific privilege to create a materialized view on the schema EDW.STG_SCHEMA to the user USER_01 directly.
  • Option B is incorrect because it grants the privilege to create a materialized view on the entire database EDW, which is too broad and unnecessary. Also, there is a typo in the user name (USERJD1 instead of USER_01).
  • Option C is incorrect because it grants the privilege to create a materialized view on a different schema (ECW.STG_SCHEKA instead of EDW.STG_SCHEMA). Also, there is no need to create a new role for this purpose.
  • Option D is incorrect because it grants the privilege to create a materialized view on an invalid object (EDW.STG_SCHEMA is not a valid schema name, it should be EDW.STG_SCHEMA). Also, there is no need to create a new role for this purpose. References:
  • Snowflake Documentation: CREATE MATERIALIZED VIEW
  • Snowflake Documentation: Working with Materialized Views
  • [Snowflake Documentation: GRANT Privileges on a Schema]

  Questions 30

  Assuming all Snowflake accounts are using an Enterprise edition or higher, in which development and testing scenarios would be copying of data be required, and zero-copy cloning not be suitable? (Select TWO).

  Options:

  A.

  Developers create their own datasets to work against transformed versions of the live data.

  B.

  Production and development run in different databases in the same account, and Developers need to see production-like data but with specific columns masked.

  C.

  Data is in a production Snowflake account that needs to be provided to Developers in a separate development/testing Snowflake account in the same cloud region.

  D.

  Developers create their own copies of a standard test database previously created for them in the development account, for their initial development and unit testing.

  E.

  The release process requires pre-production testing of changes with data of production scale and complexity. For security reasons, pre-production also runs in the production account.

  Show Answer Buy Now

  Answer:

  A, C

  Explanation:

  Explanation:

  Zero-copy cloning is a feature that allows creating a clone of a table, schema, or database without physically copying the data. Zero-copy cloning is suitable for scenarios where the cloned object needs to have the same data and metadata as the original object, and where the cloned object does not need to be modified or updated frequently. Zero-copy cloning is also suitable for scenarios where the cloned object needs to be shared within the same Snowflake account or across different accounts in the same cloud region2

  However, zero-copy cloning is not suitable for scenarios where the cloned object needs to have different data or metadata than the original object, or where the cloned object needs to be modified or updated frequently. Zero-copy cloning is also not suitable for scenarios where the cloned object needs to be shared across different accounts in different cloud regions. In these scenarios, copying of data would be required, either by using the COPY INTO command or by using data sharing with secure views3

  The following are examples of development and testing scenarios where copying of data would be required, and zero-copy cloning would not be suitable:

  • Developers create their own datasets to work against transformed versions of the live data. This scenario requires copying of data because the developers need to modify the data or metadata of the cloned object to perform transformations, such as adding, deleting, or updating columns, rows, or values. Zero-copy cloning would not be suitable because it would create a read-only clone that shares the same data and metadata as the original object, and any changes made to the clone would affect the original object as well4
  • Data is in a production Snowflake account that needs to be provided to Developers in a separate development/testing Snowflake account in the same cloud region. This scenario requires copying of data because the data needs to be shared across different accounts in the same cloud region. Zero-copy cloning would not be suitable because it would create a clone within the same account as the original object, and it would not allow sharing the clone with another account. To share data across different accounts in the same cloud region, data sharing with secure views or COPY INTO command can be used5

  The following are examples of development and testing scenarios where zero-copy cloning would be suitable, and copying of data would not be required:

  • Production and development run in different databases in the same account, and Developers need to see production-like data but with specific columns masked. This scenario can use zero-copy cloning because the data needs to be shared within the same account, and the cloned object does not need to have different data or metadata than the original object. Zero-copy cloning can create a clone of the production database in the development database, and the clone can have the same data and metadata as the original database. To mask specific columns, secure views can be created on top of the clone, and the developers can access the secure views instead of the clone directly6
  • Developers create their own copies of a standard test database previously created for them in the development account, for their initial development and unit testing. This scenario can use zero-copy cloning because the data needs to be shared within the same account, and the cloned object does not need to have different data or metadata than the original object. Zero-copy cloning can create a clone of the standard test database for each developer, and the clone can have the same data and metadata as the original database. The developers can use the clone for their initial development and unit testing, and any changes made to the clone would not affect the original database or other clones7
  • The release process requires pre-production testing of changes with data of production scale and complexity. For security reasons, pre-production also runs in the production account. This scenario can use zero-copy cloning because the data needs to be shared within the same account, and the cloned object does not need to have different data or metadata than the original object. Zero-copy cloning can create a clone of the production database in the pre-production database, and the clone can have the same data and metadata as the original database. The pre-production testing can use the clone to test the changes with data of production scale and complexity, and any changes made to the clone would not affect the original database or the production environment8 References:
  • 1: SnowPro Advanced: Architect | Study Guide 9
  • 2: Snowflake Documentation | Cloning Overview
  • 3: Snowflake Documentation | Loading Data Using COPY into a Table
  • 4: Snowflake Documentation | Transforming Data During a Load
  • 5: Snowflake Documentation | Data Sharing Overview
  • 6: Snowflake Documentation | Secure Views
  • 7: Snowflake Documentation | Cloning Databases, Schemas, and Tables
  • 8: Snowflake Documentation | Cloning for Testing and Development
  • : SnowPro Advanced: Architect | Study Guide
  • : Cloning Overview
  • : Loading Data Using COPY into a Table
  • : Transforming Data During a Load
  • : Data Sharing Overview
  • : Secure Views
  • : Cloning Databases, Schemas, and Tables
  • : Cloning for Testing and Development

  Questions 31

  A company has a table with that has corrupted data, named Data. The company wants to recover the data as it was 5 minutes ago using cloning and Time Travel.

  What command will accomplish this?

  Options:

  A.

  CREATE CLONE TABLE Recover_Data FROM Data AT(OFFSET => -60*5);

  B.

  CREATE CLONE Recover_Data FROM Data AT(OFFSET => -60*5);

  C.

  CREATE TABLE Recover_Data CLONE Data AT(OFFSET => -60*5);

  D.

  CREATE TABLE Recover Data CLONE Data AT(TIME => -60*5);

  Show Answer Buy Now

  Answer:

  C

  Explanation:

  Explanation:

  This is the correct command to create a clone of the table Data as it was 5 minutes ago using cloning and Time Travel. Cloning is a feature that allows creating a copy of a database, schema, table, or view without duplicating the data or metadata. Time Travel is a feature that enables accessing historical data (i.e. data that has been changed or deleted) at any point within a defined period. To create a clone of a table at a point in time in the past, the syntax is:

  CREATE TABLE CLONE AT (OFFSET => );

  The OFFSET parameter specifies the time difference in seconds from the present time. A negative value indicates a point in the past. For example, -60*5 means 5 minutes ago. Alternatively, the TIMESTAMP parameter can be used to specify an exact timestamp in the past. The clone will contain the data as it existed in the source table at the specified point in time12.

  References:

  • Snowflake Documentation: Cloning Objects
  • Snowflake Documentation: Cloning Objects at a Point in Time in the Past

  Questions 32

  What are some of the characteristics of result set caches? (Choose three.)

  Options:

  A.

  Time Travel queries can be executed against the result set cache.

  B.

  Snowflake persists the data results for 24 hours.

  C.

  Each time persisted results for a query are used, a 24-hour retention period is reset.

  D.

  The data stored in the result cache will contribute to storage costs.

  E.

  The retention period can be reset for a maximum of 31 days.

  F.

  The result set cache is not shared between warehouses.

  Show Answer Buy Now

  Answer:

  B, C, F

  Explanation:

  Explanation:

  In Snowflake, the characteristics of result set caches include persistence of data results for 24 hours (B), each use of persisted results resets the 24-hour retention period (C), and result set caches are not shared between different warehouses (F). The result set cache is specifically designed to avoid repeated execution of the same query within this timeframe, reducing computational overhead and speeding up query responses. These caches do not contribute to storage costs, and their retention period cannot be extended beyond the default duration nor up to 31 days, as might be misconstrued.References: Snowflake Documentation on Result Set Caching.

  Questions 33

  Role A has the following permissions:

  . USAGE on db1

  . USAGE and CREATE VIEW on schemal in db1

  . SELECT on tablel in schemal

  Role B has the following permissions:

  . USAGE on db2

  . USAGE and CREATE VIEW on schema2 in db2

  . SELECT on table2 in schema2

  A user has Role A set as the primary role and Role B as a secondary role.

  What command will fail for this user?

  Options:

  A.

  use database db1;

  use schema schemal;

  create view v1 as select * from db2.schema2.table2;

  B.

  use database db2;

  use schema schema2;

  create view v2 as select * from dbl.schemal. tablel;

  C.

  use database db2;

  use schema schema2;

  select * from db1.schemal.tablel union select * from table2;

  D.

  use database db1;

  use schema schemal;

  select * from db2.schema2.table2;

  Show Answer Buy Now

  Answer:

  B

  Explanation:

  Explanation:

  This command will fail because while the user has USAGE permission on db2 and schema2 through Role B, and can create a view in schema2, they do not have SELECT permission on db1.schemal.table1 with Role B. Since Role A, which has SELECT permission on db1.schemal.table1, is not the currently active role when the view v2 is being created in db2.schema2, the user does not have the necessary permissions to read from db1.schemal.table1 to create the view. Snowflake’s security model requires that the active role have all necessary permissions to execute the command.

  Questions 34

  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 35

  Which security, governance, and data protection features require, at a MINIMUM, the Business Critical edition of Snowflake? (Choose two.)

  Options:

  A.

  Extended Time Travel (up to 90 days)

  B.

  Customer-managed encryption keys through Tri-Secret Secure

  C.

  Periodic rekeying of encrypted data

  D.

  AWS, Azure, or Google Cloud private connectivity to Snowflake

  E.

  Federated authentication and SSO

  Show Answer Buy Now

  Answer:

  B, D

  Explanation:

  Explanation:

   According to the SnowPro Advanced: Architect documents and learning resources, the security, governance, and data protection features that require, at a minimum, the Business Critical edition of Snowflake are:

  • Customer-managed encryption keys through Tri-Secret Secure. This feature allows customers to manage their own encryption keys for data at rest in Snowflake, using a combination of three secrets: a master key, a service key, and a security password. This provides an additional layer of security and control over the data encryption and decryption process1.
  • Periodic rekeying of encrypted data. This feature allows customers to periodically rotate the encryption keys for data at rest in Snowflake, using either Snowflake-managed keys or customer-managed keys. This enhances the security and protection of the data by reducing the risk of key compromise or exposure2.

  The other options are incorrect because they do not require the Business Critical edition of Snowflake. Option A is incorrect because extended Time Travel (up to 90 days) is available with the Enterprise edition of Snowflake3. Option D is incorrect because AWS, Azure, or Google Cloud private connectivity to Snowflake is available with the Standard edition of Snowflake4. Option E is incorrect because federated authentication and SSO are available with the Standard edition of Snowflake5. References: Tri-Secret Secure | Snowflake Documentation, Periodic Rekeying of Encrypted Data | Snowflake Documentation, Snowflake Editions | Snowflake Documentation, Snowflake Network Policies | Snowflake Documentation, Configuring Federated Authentication and SSO | Snowflake Documentation

  Questions 36

  An Architect is integrating an application that needs to read and write data to Snowflake without installing any additional software on the application server.

  How can this requirement be met?

  Options:

  A.

  Use SnowSQL.

  B.

  Use the Snowpipe REST API.

  C.

  Use the Snowflake SQL REST API.

  D.

  Use the Snowflake ODBC driver.

  Show Answer Buy Now

  Answer:

  C

  Explanation:

  Explanation:

  The Snowflake SQL REST API is a REST API that you can use to access and update data in a Snowflake database. You can use this API to execute standard queries and most DDL and DML statements. This API can be used to develop custom applications and integrations that can read and write data to Snowflake without installing any additional software on the application server. Option A is not correct because SnowSQL is a command-line client that requires installation and configuration on the application server. Option B is not correct because the Snowpipe REST API is used to load data from cloud storage into Snowflake tables, not to read or write data to Snowflake. Option D is not correct because the Snowflake ODBC driver is a software component that enables applications to connect to Snowflake using the ODBC protocol, which also requires installation and configuration on the application server. References: The answer can be verified from Snowflake’s official documentation on the Snowflake SQL REST API available on their website. Here are some relevant links:

  • Snowflake SQL REST API | Snowflake Documentation
  • Introduction to the SQL API | Snowflake Documentation
  • Submitting a Request to Execute SQL Statements | Snowflake Documentation

  Questions 37

  What is a characteristic of Role-Based Access Control (RBAC) as used in Snowflake?

  Options:

  A.

  Privileges can be granted at the database level and can be inherited by all underlying objects.

  B.

  A user can use a "super-user" access along with securityadmin to bypass authorization checks and access all databases, schemas, and underlying objects.

  C.

  A user can create managed access schemas to support future grants and ensure only schema owners can grant privileges to other roles.

  D.

  A user can create managed access schemas to support current and future grants and ensure only object owners can grant privileges to other roles.

  Show Answer Buy Now

  Answer:

  C

  Explanation:

  Explanation:

   Role-Based Access Control (RBAC) is the Snowflake Access Control Framework that allows privileges to be granted by object owners to roles, and roles, in turn, can be assigned to users to restrict or allow actions to be performed on objects. A characteristic of RBAC as used in Snowflake is:

  • Privileges can be granted at the database level and can be inherited by all underlying objects. This means that a role that has a certain privilege on a database, such as CREATE SCHEMA or USAGE, can also perform the same action on any schema, table, view, or other object within that database, unless explicitly revoked. This simplifies the access control management and reduces the number of grants required.
  • A user can create managed access schemas to support future grants and ensure only schema owners can grant privileges to other roles. This means that a user can create a schema with the MANAGED ACCESS option, which changes the default behavior of object ownership and privilege granting within the schema. In a managed access schema, object owners lose the ability to grant privileges on their objects to other roles, and only the schema owner or a role with the MANAGE GRANTS privilege can do so. This enhances the security and governance of the schema and its objects.

  The other options are not characteristics of RBAC as used in Snowflake:

  • A user can use a “super-user” access along with securityadmin to bypass authorization checks and access all databases, schemas, and underlying objects. This is not true, as there is no such thing as a “super-user” access in Snowflake. The securityadmin role is a predefined role that can manage users and roles, but it does not have any privileges on any database objects by default. To access any object, the securityadmin role must be explicitly granted the appropriate privilege by the object owner or another role with the grant option.
  • A user can create managed access schemas to support current and future grants and ensure only object owners can grant privileges to other roles. This is not true, as this contradicts the definition of a managed access schema. In a managed access schema, object owners cannot grant privileges on their objects to other roles, and only the schema owner or a role with the MANAGE GRANTS privilege can do so.

  References:

  • Overview of Access Control
  • A Functional Approach For Snowflake’s Role-Based Access Controls
  • Snowflake Role-Based Access Control simplified
  • Snowflake RBAC security prefers role inheritance to role composition
  • Overview of Snowflake Role Based Access Control

  Questions 38

  An Architect is designing a pipeline to stream event data into Snowflake using the Snowflake Kafka connector. The Architect’s highest priority is to configure the connector to stream data in the MOST cost-effective manner.

  Which of the following is recommended for optimizing the cost associated with the Snowflake Kafka connector?

  Options:

  A.

  Utilize a higher Buffer.flush.time in the connector configuration.

  B.

  Utilize a higher Buffer.size.bytes in the connector configuration.

  C.

  Utilize a lower Buffer.size.bytes in the connector configuration.

  D.

  Utilize a lower Buffer.count.records in the connector configuration.

  Show Answer Buy Now

  Answer:

  A

  Explanation:

  Explanation:

  The minimum value supported for the buffer.flush.time property is 1 (in seconds). For higher average data flow rates, we suggest that you decrease the default value for improved latency. If cost is a greater concern than latency, you could increase the buffer flush time. Be careful to flush the Kafka memory buffer before it becomes full to avoid out of memory exceptions. https://docs.snowflake.com/en/user-guide/data-load-snowpipe-streaming-kafka

  Questions 39

  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 40

  How does a standard virtual warehouse policy work in Snowflake?

  Options:

  A.

  It conserves credits by keeping running clusters fully loaded rather than starting additional clusters.

  B.

  It starts only if the system estimates that there is a query load that will keep the cluster busy for at least 6 minutes.

  C.

  It starts only f the system estimates that there is a query load that will keep the cluster busy for at least 2 minutes.

  D.

  It prevents or minimizes queuing by starting additional clusters instead of conserving credits.

  Show Answer Buy Now

  Answer:

  D

  Explanation:

  Explanation:

   A standard virtual warehouse policy is one of the two scaling policies available for multi-cluster warehouses in Snowflake. The other policy is economic. A standard policy aims to prevent or minimize queuing by starting additional clusters as soon as the current cluster is fully loaded, regardless of the number of queries in the queue. This policy can improve query performance and concurrency, but it may also consume more credits than an economic policy, which tries to conserve credits by keeping the running clusters fully loaded before starting additional clusters. The scaling policy can be set when creating or modifying a warehouse, and it can be changed at any time.

  References:

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

  Questions 41

  An Architect is designing a file ingestion recovery solution. The project will use an internal named stage for file storage. Currently, in the case of an ingestion failure, the Operations team must manually download the failed file and check for errors.

  Which downloading method should the Architect recommend that requires the LEAST amount of operational overhead?

  Options:

  A.

  Use the Snowflake Connector for Python, connect to remote storage and download the file.

  B.

  Use the get command in SnowSQL to retrieve the file.

  C.

  Use the get command in Snowsight to retrieve the file.

  D.

  Use the Snowflake API endpoint and download the file.

  Show Answer Buy Now

  Answer:

  B

  Explanation:

  Explanation:

  The get command in SnowSQL is a convenient way to download files from an internal stage to a local directory. The get command can be used in interactive mode or in a script, and it supports wildcards and parallel downloads. The get command also allows specifying the overwrite option, which determines how to handle existing files with the same name2

  The Snowflake Connector for Python, the Snowflake API endpoint, and the get command in Snowsight are not recommended methods for downloading files from an internal stage, because they require more operational overhead than the get command in SnowSQL. The Snowflake Connector for Python and the Snowflake API endpoint require writing and maintaining code to handle the connection, authentication, and file transfer. The get command in Snowsight requires using the web interface and manually selecting the files to download34 References:

  • 1: SnowPro Advanced: Architect | Study Guide
  • 2: Snowflake Documentation | Using the GET Command
  • 3: Snowflake Documentation | Using the Snowflake Connector for Python
  • 4: Snowflake Documentation | Using the Snowflake API
  • : Snowflake Documentation | Using the GET Command in Snowsight
  • : SnowPro Advanced: Architect | Study Guide
  • : Using the GET Command
  • : Using the Snowflake Connector for Python
  • : Using the Snowflake API
  • : [Using the GET Command in Snowsight]

  Questions 42

  A large manufacturing company runs a dozen individual Snowflake accounts across its business divisions. The company wants to increase the level of data sharing to support supply chain optimizations and increase its purchasing leverage with multiple vendors.

  The company’s Snowflake Architects need to design a solution that would allow the business divisions to decide what to share, while minimizing the level of effort spent on configuration and management. Most of the company divisions use Snowflake accounts in the same cloud deployments with a few exceptions for European-based divisions.

  According to Snowflake recommended best practice, how should these requirements be met?

  Options:

  A.

  Migrate the European accounts in the global region and manage shares in a connected graph architecture. Deploy a Data Exchange.

  B.

  Deploy a Private Data Exchange in combination with data shares for the European accounts.

  C.

  Deploy to the Snowflake Marketplace making sure that invoker_share() is used in all secure views.

  D.

  Deploy a Private Data Exchange and use replication to allow European data shares in the Exchange.

  Show Answer Buy Now

  Answer:

  D

  Explanation:

  Explanation:

   According to Snowflake recommended best practice, the requirements of the large manufacturing company should be met by deploying a Private Data Exchange in combination with data shares for the European accounts. A Private Data Exchange is a feature of the Snowflake Data Cloud platform that enables secure and governed sharing of data between organizations. It allows Snowflake customers to create their own data hub and invite other parts of their organization or external partners to access and contribute data sets. A Private Data Exchange provides centralized management, granular access control, and data usage metrics for the data shared in the exchange1. A data share is a secure and direct way of sharing data between Snowflake accounts without having to copy or move the data. A data share allows the data provider to grant privileges on selected objects in their account to one or more data consumers in other accounts2. By using a Private Data Exchange in combination with data shares, the company can achieve the following benefits:

  • The business divisions can decide what data to share and publish it to the Private Data Exchange, where it can be discovered and accessed by other members of the exchange. This reduces the effort and complexity of managing multiple data sharing relationships and configurations.
  • The company can leverage the existing Snowflake accounts in the same cloud deployments to create the Private Data Exchange and invite the members to join. This minimizes the migration and setup costs and leverages the existing Snowflake features and security.
  • The company can use data shares to share data with the European accounts that are in different regions or cloud platforms. This allows the company to comply with the regional and regulatory requirements for data sovereignty and privacy, while still enabling data collaboration across the organization.
  • The company can use the Snowflake Data Cloud platform to perform data analysis and transformation on the shared data, as well as integrate with other data sources and applications. This enables the company to optimize its supply chain and increase its purchasing leverage with multiple vendors.

  Questions 43

  An Architect needs to meet a company requirement to ingest files from the company's AWS storage accounts into the company's Snowflake Google Cloud Platform (GCP) account. How can the ingestion of these files into the company's Snowflake account be initiated? (Select TWO).

  Options:

  A.

  Configure the client application to call the Snowpipe REST endpoint when new files have arrived in Amazon S3 storage.

  B.

  Configure the client application to call the Snowpipe REST endpoint when new files have arrived in Amazon S3 Glacier storage.

  C.

  Create an AWS Lambda function to call the Snowpipe REST endpoint when new files have arrived in Amazon S3 storage.

  D.

  Configure AWS Simple Notification Service (SNS) to notify Snowpipe when new files have arrived in Amazon S3 storage.

  E.

  Configure the client application to issue a COPY INTO

  command to Snowflake when new files have arrived in Amazon S3 Glacier storage.
  Show Answer Buy Now

  Answer:

  A, C

  Explanation:

  Explanation:

  Snowpipe is a feature that enables continuous, near-real-time data ingestion from external sources into Snowflake tables. Snowpipe can ingest files from Amazon S3, Google Cloud Storage, or Azure Blob Storage into Snowflake tables on any cloud platform. Snowpipe can be triggered in two ways: by using the Snowpipe REST API or by using cloud notifications2

  To ingest files from the company’s AWS storage accounts into the company’s Snowflake GCP account, the Architect can use either of these methods:

  • Configure the client application to call the Snowpipe REST endpoint when new files have arrived in Amazon S3 storage. This method requires the client application to monitor the S3 buckets for new files and send a request to the Snowpipe REST API with the list of files to ingest. The client application must also handle authentication, error handling, and retry logic3
  • Create an AWS Lambda function to call the Snowpipe REST endpoint when new files have arrived in Amazon S3 storage. This method leverages the AWS Lambda service to execute a function that calls the Snowpipe REST API whenever an S3 event notification is received. The AWS Lambda function must be configured with the appropriate permissions, triggers, and code to invoke the Snowpipe REST API4

  The other options are not valid methods for triggering Snowpipe:

  • Configure the client application to call the Snowpipe REST endpoint when new files have arrived in Amazon S3 Glacier storage. This option is not feasible because Snowpipe does not support ingesting files from Amazon S3 Glacier storage, which is a long-term archival storage service. Snowpipe only supports ingesting files from Amazon S3 standard storage classes5
  • Configure AWS Simple Notification Service (SNS) to notify Snowpipe when new files have arrived in Amazon S3 storage. This option is not applicable because Snowpipe does not support cloud notifications from AWS SNS. Snowpipe only supports cloud notifications from AWS SQS, Google Cloud Pub/Sub, or Azure Event Grid6
  • Configure the client application to issue a COPY INTO
  command to Snowflake when new files have arrived in Amazon S3 Glacier storage. This option is not relevant because it does not use Snowpipe, but rather the standard COPY command, which is a batch loading method. Moreover, the COPY command also does not support ingesting files from Amazon S3 Glacier storage7 References:
  1: SnowPro Advanced: Architect | Study Guide 8
  2: Snowflake Documentation | Snowpipe Overview 9
  3: Snowflake Documentation | Using the Snowpipe REST API 10
  4: Snowflake Documentation | Loading Data Using Snowpipe and AWS Lambda 11
  5: Snowflake Documentation | Supported File Formats and Compression for Staged Data Files 12
  6: Snowflake Documentation | Using Cloud Notifications to Trigger Snowpipe 13
  7: Snowflake Documentation | Loading Data Using COPY into a Table
  : SnowPro Advanced: Architect | Study Guide
  : Snowpipe Overview
  : Using the Snowpipe REST API
  : Loading Data Using Snowpipe and AWS Lambda
  : Supported File Formats and Compression for Staged Data Files
  : Using Cloud Notifications to Trigger Snowpipe
  : Loading Data Using COPY into a Table
  Questions 44

  A new table and streams are created with the following commands:

  CREATE OR REPLACE TABLE LETTERS (ID INT, LETTER STRING) ;

  CREATE OR REPLACE STREAM STREAM_1 ON TABLE LETTERS;

  CREATE OR REPLACE STREAM STREAM_2 ON TABLE LETTERS APPEND_ONLY = TRUE;

  The following operations are processed on the newly created table:

  INSERT INTO LETTERS VALUES (1, 'A');

  INSERT INTO LETTERS VALUES (2, 'B');

  INSERT INTO LETTERS VALUES (3, 'C');

  TRUNCATE TABLE LETTERS;

  INSERT INTO LETTERS VALUES (4, 'D');

  INSERT INTO LETTERS VALUES (5, 'E');

  INSERT INTO LETTERS VALUES (6, 'F');

  DELETE FROM LETTERS WHERE ID = 6;

  What would be the output of the following SQL commands, in order?

  SELECT COUNT (*) FROM STREAM_1;

  SELECT COUNT (*) FROM STREAM_2;

  Options:

  A.

  2 & 6

  B.

  2 & 3

  C.

  4 & 3

  D.

  4 & 6

  Show Answer Buy Now

  Answer:

  C

  Explanation:

  Explanation:

  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.

  Questions 45

  Which of the following are characteristics of Snowflake’s parameter hierarchy?

  Options:

  A.

  Session parameters override virtual warehouse parameters.

  B.

  Virtual warehouse parameters override user parameters.

  C.

  Table parameters override virtual warehouse parameters.

  D.

  Schema parameters override account parameters.

  Show Answer Buy Now

  Answer:

  B

  Explanation:

  Explanation:

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

  Questions 46

  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 47

  A new user user_01 is created within Snowflake. The following two commands are executed:

  Command 1-> show grants to user user_01;

  Command 2 ~> show grants on user user 01;

  What inferences can be made about these commands?

  Options:

  A.

  Command 1 defines which user owns user_01

  Command 2 defines all the grants which have been given to user_01

  B.

  Command 1 defines all the grants which are given to user_01 Command 2 defines which user owns user_01

  C.

  Command 1 defines which role owns user_01

  Command 2 defines all the grants which have been given to user_01

  D.

  Command 1 defines all the grants which are given to user_01

  Command 2 defines which role owns user 01

  Show Answer Buy Now

  Answer:

  D

  Explanation:

  Explanation:

  The SHOW GRANTS command in Snowflake can be used to list all the access control privileges that have been explicitly granted to roles, users, and shares. The syntax and the output of the command vary depending on the object type and the grantee type specified in the command1. In this question, the two commands have the following meanings:

  • Command 1: show grants to user user_01; This command lists all the roles granted to the user user_01. The output includes the role name, the grantee name, and the granted by role name for each grant. This command is equivalent to show grants to user current_user if user_01 is the current user1.
  • Command 2: show grants on user user_01; This command lists all the privileges that have been granted on the user object user_01. The output includes the privilege name, the grantee name, and the granted by role name for each grant. This command shows which role owns the user object user_01, as the owner role has the privilege to modify or drop the user object2.

  Therefore, the correct inference is that command 1 defines all the grants which are given to user_01, and command 2 defines which role owns user_01.

  References:

  • SHOW GRANTS
  • Understanding Access Control in Snowflake

  Questions 48

  What considerations need to be taken when using database cloning as a tool for data lifecycle management in a development environment? (Select TWO).

  Options:

  A.

  Any pipes in the source are not cloned.

  B.

  Any pipes in the source referring to internal stages are not cloned.

  C.

  Any pipes in the source referring to external stages are not cloned.

  D.

  The clone inherits all granted privileges of all child objects in the source object, including the database.

  E.

  The clone inherits all granted privileges of all child objects in the source object, excluding the database.

  Show Answer Buy Now

  Answer:

  A, C
  Exam Code: ARA-C01

  Exam Name: SnowPro Advanced: Architect Certification Exam

  Last Update: May 12, 2024

  Questions: 162

  PDF + Testing Engine

  $56  $159.99

  

  Testing Engine

  $42  $119.99

  

  PDF (Q&A)

  $35  $99.99

  
  

  24/7 Customer Support

  DumpsMate's team of experts is always available to respond your queries on exam preparation. Get professional answers on any topic of the certification syllabus. Our experts will thoroughly satisfy you.

  Site Secure

  

  TESTED 18 May 2024

  Quick Links

  • Home
  • All Vendors
  • Testimonials

  Why Us

  • Contact us
  • About us
  • Guarantee

  Unlimited Packages

  • Unlimited Engine

  SSL SECURE

  • 

  DumpsMate Copyright

  Copyright © 2014-2024 DumpsMate. All Rights Reserved