Snowflake ARA-R01 SnowPro Advanced: Architect Recertification Exam Exam Practice Test

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

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

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.

 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

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

Dynamic Data Masking is a feature that allows masking sensitive data in query results based on the role of the user who executes the query. A masking policy is a user-defined function that specifies the masking logic and can be applied to one or more columns in one or more tables. A masking policy that is currently set on a table can be dropped using the ALTER TABLE command. A single masking policy can be applied to columns in different tables using the ALTER TABLE command with the SET MASKING POLICY clause. The other options are either incorrect or not supported by Snowflake. A masking policy cannot be applied to the value column of an external table, as external tables do not support column-level security. The role that creates the masking policy will not always see unmasked data in query results, as the masking policy can be applied to the owner role as well. A masking policy cannot be applied to a column with the GEOGRAPHY data type, as Snowflake only supports masking policies for scalar data types. References: Snowflake Documentation: Dynamic Data Masking, Snowflake Documentation: ALTER TABLE

• 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

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.

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.

Data is being imported and stored as JSON in a VARIANT column. Query performance was fine, but most recently, poor query performance has been reported. This could be caused by the following factors:

• The order of the keys in the JSON was changed. Snowflake stores semi-structured data internally in a column-like structure for the most common elements, and the remainder in a leftovers-like column. The order of the keys in the JSON affects how Snowflake determines the common elements and how it optimizes the query performance. If the order of the keys in the JSON was changed, Snowflake might have to re-parse the data and re-organize the internal storage, which could result in slower query performance.
• There were variations in string lengths for the JSON values in the recent data imports. Non-native values, such as dates and timestamps, are stored as strings when loaded into a VARIANT column. Operations on these values could be slower and also consume more space than when stored in a relational column with the corresponding data type. If there were variations in string lengths for the JSON values in the recent data imports, Snowflake might have to allocate more space and perform more conversions, which could also result in slower query performance.

The other options are not valid causes for poor query performance:

• There were JSON nulls in the recent data imports. Snowflake supports two types of null values in semi-structured data: SQL NULL and JSON null. SQL NULL means the value is missing or unknown, while JSON null means the value is explicitly set to null. Snowflake can distinguish between these two types of null values and handle them accordingly. Having JSON nulls in the recent data imports should not affect the query performance significantly.
• The recent data imports contained fewer fields than usual. Snowflake can handle semi-structured data with varying schemas and fields. Having fewer fields than usual in the recent data imports should not affect the query performance significantly, as Snowflake can still optimize the data ingestion and query execution based on the existing fields.

References:

• Considerations for Semi-structured Data Stored in VARIANT
• Snowflake Architect Training
• Snowflake query performance on unique element in variant column
• Snowflake variant performance

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

The most performant design for processing changed records, considering the need to both update records in the f_orders fact table and load changes into the order_repairs table, is to use one stream and two tasks. The stream will monitor changes in the orders table, capturing both inserts and updates. The first task would apply these changes to the f_orders fact table, ensuring all dimensions are accurately represented. The second task would use the same stream to insert relevant changes into the order_repairs table, which is critical for the Accounting department's monthly review. This method ensures efficient processing by minimizing the overhead of managing multiple streams and synchronizing between them, while also allowing specific tasks to optimize for their target operations.References: Snowflake's documentation on streams and tasks for handling data changes efficiently.

The SYSTEM$GET_PRIVATELINK function is used to retrieve the list of Snowflake service endpoints that need to be accessible when configuring private connectivity (such as AWS PrivateLink or Azure Private Link) for a Snowflake account. The function returns information necessary for setting up the networking infrastructure that allows secure and private access to Snowflake without using the public internet. SnowCD can then be used to verify connectivity to these endpoints.

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

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.

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

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

A. The Snowflake SQL REST API does support the use of a GET command, which can be used to retrieve the status of a previously submitted query or to fetch the results of a query once it has been executed.D. The use of a CALL command to a stored procedure is supported, which can return a result set, including a table. This allows the invocation of stored procedures within Snowflake through the SQL REST API.

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

Transient databases in Snowflake are designed for situations where data retention is not critical, and they do not have the fail-safe period that regular databases have. This means that data in a transient database is not recoverable after the Time Travel retention period. Using a transient database is efficient because it minimizes storage costs while still providing most functionalities of a standard database without the overhead of data protection features that are not needed when data retention is not a concern.

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.

 Option C is the correct answer because schema-on-read is a technique that allows Snowflake to ingest and consume semi-structured data without requiring a predefined schema. Snowflake supports various semi-structured data formats such as JSON, Avro, ORC, Parquet, and XML, and provides native data types (ARRAY, OBJECT, and VARIANT) for storing them. Snowflake also provides native support for querying semi-structured data using SQL and dot notation. Schema-on-read enables Snowflake to query semi-structured data at the same speed as performing relational queries while preserving the flexibility of schema-on-read. Snowflake’s near-instant elasticity rightsizes compute resources, and consumption-based pricing ensures you only pay for what you use.

Option A is incorrect because IDEF1X is a data modeling technique that defines the structure and constraints of relational data using diagrams and notations. IDEF1X is not suitable for ingesting and consuming semi-structured data, which does not have a fixed schema or structure.

Option B is incorrect because schema-on-write is a technique that requires defining a schema before loading and processing data. Schema-on-write is not efficient for ingesting and consuming semi-structured data, which may have varying or complex structures that are difficult to fit into a predefined schema. Schema-on-write also introduces additional overhead and complexity for data transformation and validation.

Option D is incorrect because information schema is a set of metadata views that provide information about the objects and privileges in a Snowflake database. Information schema is not a technique for ingesting and consuming semi-structured data, but rather a way of accessing metadata about the data.

References:

• Semi-structured Data
• Snowflake for Data Lake

The object type level at which the APPLY MASKING POLICY, APPLY ROW ACCESS POLICY and APPLY SESSION POLICY privileges can be granted is global. These are account-level privileges that control who can apply or unset these policies on objects such as columns, tables, views, accounts, or users. These privileges are granted to the ACCOUNTADMIN role by default, and can be granted to other roles as needed. The other options are incorrect because they are not the object type level at which these privileges can be granted. Database, schema, and table are lower-level object types that do not support these privileges. References: Access Control Privileges | Snowflake Documentation, Using Dynamic Data Masking | Snowflake Documentation, Using Row Access Policies | Snowflake Documentation, Using Session Policies | Snowflake Documentation

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

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.

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.

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

• 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
  Question 28

  Company A has recently acquired company B. The Snowflake deployment for company B is located in the Azure West Europe region.

  As part of the integration process, an Architect has been asked to consolidate company B's sales data into company A's Snowflake account which is located in the AWS us-east-1 region.

  How can this requirement be met?

  Options:

  A.

  Replicate the sales data from company B's Snowflake account into company A's Snowflake account using cross-region data replication within Snowflake. Configure a direct share from company B's account to company A's account.

  B.

  Export the sales data from company B's Snowflake account as CSV files, and transfer the files to company A's Snowflake account. Import the data using Snowflake's data loading capabilities.

  C.

  Migrate company B's Snowflake deployment to the same region as company A's Snowflake deployment, ensuring data locality. Then perform a direct database-to-database merge of the sales data.

  D.

  Build a custom data pipeline using Azure Data Factory or a similar tool to extract the sales data from company B's Snowflake account. Transform the data, then load it into company A's Snowflake account.

  Show Answer

  Answer:

  A

  Explanation:

  Explanation:

  The best way to meet the requirement of consolidating company B’s sales data into company A’s Snowflake account is to use cross-region data replication within Snowflake. This feature allows data providers to securely share data with data consumers across different regions and cloud platforms. By replicating the sales data from company B’s account in Azure West Europe region to company A’s account in AWS us-east-1 region, the data will be synchronized and available for consumption. To enable data replication, the accounts must be linked and replication must be enabled by a user with the ORGADMIN role. Then, a replication group must be created and the sales database must be added to the group. Finally, a direct share must be configured from company B’s account to company A’s account to grant access to the replicated data. This option is more efficient and secure than exporting and importing data using CSV files or migrating the entire Snowflake deployment to another region or cloud platform. It also does not require building a custom data pipeline using external tools.

  References:

  • Sharing data securely across regions and cloud platforms
  • Introduction to replication and failover
  • Replication considerations
  • Replicating account objects

  Question 29

  An Architect has chosen to separate their Snowflake Production and QA environments using two separate Snowflake accounts.

  The QA account is intended to run and test changes on data and database objects before pushing those changes to the Production account. It is a requirement that all database objects and data in the QA account need to be an exact copy of the database objects, including privileges and data in the Production account on at least a nightly basis.

  Which is the LEAST complex approach to use to populate the QA account with the Production account’s data and database objects on a nightly basis?

  Options:

  A.

  1) Create a share in the Production account for each database

  2) Share access to the QA account as a Consumer

  3) The QA account creates a database directly from each share

  4) Create clones of those databases on a nightly basis

  5) Run tests directly on those cloned databases

  B.

  1) Create a stage in the Production account

  2) Create a stage in the QA account that points to the same external object-storage location

  3) Create a task that runs nightly to unload each table in the Production account into the stage

  4) Use Snowpipe to populate the QA account

  C.

  1) Enable replication for each database in the Production account

  2) Create replica databases in the QA account

  3) Create clones of the replica databases on a nightly basis

  4) Run tests directly on those cloned databases

  D.

  1) In the Production account, create an external function that connects into the QA account and returns all the data for one specific table

  2) Run the external function as part of a stored procedure that loops through each table in the Production account and populates each table in the QA account

  Show Answer

  Answer:

  C

  Explanation:

  Explanation:

  This approach is the least complex because it uses Snowflake’s built-in replication feature to copy the data and database objects from the Production account to the QA account. Replication is a fast and efficient way to synchronize data across accounts, regions, and cloud platforms. It also preserves the privileges and metadata of the replicated objects. By creating clones of the replica databases, the QA account can run tests on the cloned data without affecting the original data. Clones are also zero-copy, meaning they do not consume any additional storage space unless the data is modified. This approach does not require any external stages, tasks, Snowpipe, or external functions, which can add complexity and overhead to the data transfer process.

  References:

  • Introduction to Replication and Failover
  • Replicating Databases Across Multiple Accounts
  • Cloning Considerations

  Question 30

  An Architect is implementing a CI/CD process. When attempting to clone a table from a production to a development environment, the cloning operation fails.

  What could be causing this to happen?

  Options:

  A.

  The table is transient.

  B.

  The table has a masking policy.

  C.

  The retention time for the table is set to zero.

  D.

  Tables cannot be cloned from a higher environment to a lower environment.

  Show Answer

  Answer:

  B

  Explanation:

  Explanation:

  Cloning a table with a masking policy can cause the cloning operation to fail because the masking policy is not automatically cloned with the table. This is due to the fact that the masking policy is considered a separate object with its own set of privileges1.

  References

  Snowflake Documentation on Cloning Considerations1.

  Question 31

  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

  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

  Question 32

  Which query will identify the specific days and virtual warehouses that would benefit from a multi-cluster warehouse to improve the performance of a particular workload?

  A)

  

  B)

  

  C)

  

  D)

  

  Options:

  A.

  Option A

  B.

  Option B

  C.

  Option C

  D.

  Option D

  Show Answer

  Answer:

  B

  Explanation:

  Explanation:

  The correct answer is option B. This query is designed to assess the need for a multi-cluster warehouse by examining the queuing time (AVG_QUEUED_LOAD) on different days and virtual warehouses. When the AVG_QUEUED_LOAD is greater than zero, it suggests that queries are waiting for resources, which can be an indicator that performance might be improved by using a multi-cluster warehouse to handle the workload more efficiently. By grouping by date and warehouse name and filtering on the sum of the average queued load being greater than zero, the query identifies specific days and warehouses where the workload exceeded the available compute resources. This information is valuable when considering scaling out warehouses to multi-cluster configurations for improved performance.

  Question 33

  A user can change object parameters using which of the following roles?

  Options:

  A.

  ACCOUNTADMIN, SECURITYADMIN

  B.

  SYSADMIN, SECURITYADMIN

  C.

  ACCOUNTADMIN, USER with PRIVILEGE

  D.

  SECURITYADMIN, USER with PRIVILEGE

  Show Answer

  Answer:

  C

  Explanation:

  Explanation:

  According to the Snowflake documentation, object parameters are parameters that can be set on individual objects such as databases, schemas, tables, and stages. Object parameters can be set by users with the appropriate privileges on the objects. For example, to set the object parameter AUTO_REFRESH on a table, the user must have the MODIFY privilege on the table. The ACCOUNTADMIN role has the highest level of privileges on all objects in the account, so it can set any object parameter on any object. However, other roles, such as SECURITYADMIN or SYSADMIN, do not have the same level of privileges on all objects, so they cannot set object parameters on objects they do not own or have the required privileges on. Therefore, the correct answer is C. ACCOUNTADMIN, USER with PRIVILEGE.

  References:

  • Parameters | Snowflake Documentation
  • Object Parameters | Snowflake Documentation
  • Object Privileges | Snowflake Documentation

  Question 34

  Which Snowflake architecture recommendation needs multiple Snowflake accounts for implementation?

  Options:

  A.

  Enable a disaster recovery strategy across multiple cloud providers.

  B.

  Create external stages pointing to cloud providers and regions other than the region hosting the Snowflake account.

  C.

  Enable zero-copy cloning among the development, test, and production environments.

  D.

  Enable separation of the development, test, and production environments.

  Show Answer

  Answer:

  D

  Explanation:

  Explanation:

  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.

  Question 35

  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

  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.

  Question 36

  A Snowflake Architect is designing a multiple-account design strategy.

  This strategy will be MOST cost-effective with which scenarios? (Select TWO).

  Options:

  A.

  The company wants to clone a production database that resides on AWS to a development database that resides on Azure.

  B.

  The company needs to share data between two databases, where one must support Payment Card Industry Data Security Standard (PCI DSS) compliance but the other one does not.

  C.

  The company needs to support different role-based access control features for the development, test, and production environments.

  D.

  The company security policy mandates the use of different Active Directory instances for the development, test, and production environments.

  E.

  The company must use a specific network policy for certain users to allow and block given IP addresses.

  Show Answer

  Answer:

  B, D

  Explanation:

  Explanation:

  B. When dealing with PCI DSS compliance, having separate accounts can be beneficial because it enables strong isolation of environments that handle sensitive data from those that do not. By segregating the compliant from non-compliant resources, an organization can limit the scope of compliance, thus making it a cost-effective strategy.D. Different Active Directory instances can be managed more effectively and securely when separated into different accounts. This approach allows for distinct identity and access management policies, which can enforce security requirements and minimize the risk of access policy errors between environments.

  Question 37

  An Architect needs to design a Snowflake account and database strategy to store and analyze large amounts of structured and semi-structured data. There are many business units and departments within the company. The requirements are scalability, security, and cost efficiency.

  What design should be used?

  Options:

  A.

  Create a single Snowflake account and database for all data storage and analysis needs, regardless of data volume or complexity.

  B.

  Set up separate Snowflake accounts and databases for each department or business unit, to ensure data isolation and security.

  C.

  Use Snowflake's data lake functionality to store and analyze all data in a central location, without the need for structured schemas or indexes

  D.

  Use a centralized Snowflake database for core business data, and use separate databases for departmental or project-specific data.

  Show Answer

  Answer:

  D

  Explanation:

  Explanation:

  The best design to store and analyze large amounts of structured and semi-structured data for different business units and departments is to use a centralized Snowflake database for core business data, and use separate databases for departmental or project-specific data. This design allows for scalability, security, and cost efficiency by leveraging Snowflake’s features such as:

  • Database cloning: Cloning a database creates a zero-copy clone that shares the same data files as the original database, but can be modified independently. This reduces storage costs and enables fast and consistent data replication for different purposes.
  • Database sharing: Sharing a database allows granting secure and governed access to a subset of data in a database to other Snowflake accounts or consumers. This enables data collaboration and monetization across different business units or external partners.
  • Warehouse scaling: Scaling a warehouse allows adjusting the size and concurrency of a warehouse to match the performance and cost requirements of different workloads. This enables optimal resource utilization and flexibility for different data analysis needs. References: Snowflake Documentation: Database Cloning, Snowflake Documentation: Database Sharing, [Snowflake Documentation: Warehouse Scaling]

  Question 38

  An Architect clones a database and all of its objects, including tasks. After the cloning, the tasks stop running.

  Why is this occurring?

  Options:

  A.

  Tasks cannot be cloned.

  B.

  The objects that the tasks reference are not fully qualified.

  C.

  Cloned tasks are suspended by default and must be manually resumed.

  D.

  The Architect has insufficient privileges to alter tasks on the cloned database.

  Show Answer

  Answer:

  C

  Explanation:

  Explanation:

   When a database is cloned, all of its objects, including tasks, are also cloned. However, cloned tasks are suspended by default and must be manually resumed by using the ALTER TASK command. This is to prevent the cloned tasks from running unexpectedly or interfering with the original tasks. Therefore, the reason why the tasks stop running after the cloning is because they are suspended by default (Option C). Options A, B, and D are not correct because tasks can be cloned, the objects that the tasks reference are also cloned and do not need to be fully qualified, and the Architect does not need to alter the tasks on the cloned database, only resume them. References: The answer can be verified from Snowflake’s official documentation on cloning and tasks available on their website. Here are some relevant links:

  • Cloning Objects | Snowflake Documentation
  • Tasks | Snowflake Documentation
  • ALTER TASK | Snowflake Documentation

  Question 39

  A company's Architect needs to find an efficient way to get data from an external partner, who is also a Snowflake user. The current solution is based on daily JSON extracts that are placed on an FTP server and uploaded to Snowflake manually. The files are changed several times each month, and the ingestion process needs to be adapted to accommodate these changes.

  What would be the MOST efficient solution?

  Options:

  A.

  Ask the partner to create a share and add the company's account.

  B.

  Ask the partner to use the data lake export feature and place the data into cloud storage where Snowflake can natively ingest it (schema-on-read).

  C.

  Keep the current structure but request that the partner stop changing files, instead only appending new files.

  D.

  Ask the partner to set up a Snowflake reader account and use that account to get the data for ingestion.

  Show Answer

  Answer:

  A

  Explanation:

  Explanation:

   The most efficient solution is to ask the partner to create a share and add the company’s account (Option A). This way, the company can access the live data from the partner without any data movement or manual intervention. Snowflake’s secure data sharing feature allows data providers to share selected objects in a database with other Snowflake accounts. The shared data is read-only and does not incur any storage or compute costs for the data consumers. The data consumers can query the shared data directly or create local copies of the shared objects in their own databases. Option B is not efficient because it involves using the data lake export feature, which is intended for exporting data from Snowflake to an external data lake, not for importing data from another Snowflake account. The data lake export feature also requires the data provider to create an external stage on cloud storage and use the COPY INTO command to export the data into parquet files. The data consumer then needs to create an external table or a file format to load the data from the cloud storage into Snowflake. This process can be complex and costly, especially if the data changes frequently. Option C is not efficient because it does not solve the problem of manual data ingestion and adaptation. Keeping the current structure of daily JSON extracts on an FTP server and requesting the partner to stop changing files, instead only appending new files, does not improve the efficiency or reliability of the data ingestion process. The company still needs to upload the data to Snowflake manually and deal with any schema changes or data quality issues. Option D is not efficient because it requires the partner to set up a Snowflake reader account and use that account to get the data for ingestion. A reader account is a special type of account that can only consume data from the provider account that created it. It is intended for data consumers who are not Snowflake customers and do not have a licensing agreement with Snowflake. A reader account is not suitable for data ingestion from another Snowflake account, as it does not allow uploading, modifying, or unloading data. The company would need to use external tools or interfaces to access the data from the reader account and load it into their own account, which can be slow and expensive. References: The answer can be verified from Snowflake’s official documentation on secure data sharing, data lake export, and reader accounts available on their website. Here are some relevant links:

  • Introduction to Secure Data Sharing | Snowflake Documentation
  • Data Lake Export Public Preview Is Now Available on Snowflake | Snowflake Blog
  • Managing Reader Accounts | Snowflake Documentation

  Question 40

  When activating Tri-Secret Secure in a hierarchical encryption model in a Snowflake account, at what level is the customer-managed key used?

  

  Options:

  A.

  At the root level (HSM)

  B.

  At the account level (AMK)

  C.

  At the table level (TMK)

  D.

  At the micro-partition level

  Show Answer

  Answer:

  B

  Explanation:

  Explanation:

  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

  Question 41

  An Architect needs to automate the daily Import of two files from an external stage into Snowflake. One file has Parquet-formatted data, the other has CSV-formatted data.

  How should the data be joined and aggregated to produce a final result set?

  Options:

  A.

  Use Snowpipe to ingest the two files, then create a materialized view to produce the final result set.

  B.

  Create a task using Snowflake scripting that will import the files, and then call a User-Defined Function (UDF) to produce the final result set.

  C.

  Create a JavaScript stored procedure to read. join, and aggregate the data directly from the external stage, and then store the results in a table.

  D.

  Create a materialized view to read, Join, and aggregate the data directly from the external stage, and use the view to produce the final result set

  Show Answer

  Answer:

  B

  Explanation:

  Explanation:

  According to the Snowflake documentation, tasks are objects that enable scheduling and execution of SQL statements or JavaScript user-defined functions (UDFs) in Snowflake. Tasks can be used to automate data loading, transformation, and maintenance operations. Snowflake scripting is a feature that allows writing procedural logic using SQL statements and JavaScript UDFs. Snowflake scripting can be used to create complex workflows and orchestrate tasks. Therefore, the best option to automate the daily import of two files from an external stage into Snowflake, join and aggregate the data, and produce a final result set is to create a task using Snowflake scripting that will import the files using the COPY INTO command, and then call a UDF to perform the join and aggregation logic. The UDF can return a table or a variant value as the final result set. References:

  • Tasks
  • Snowflake Scripting
  • User-Defined Functions

  Question 42

  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

  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.

  Question 43

  A retailer's enterprise data organization is exploring the use of Data Vault 2.0 to model its data lake solution. A Snowflake Architect has been asked to provide recommendations for using Data Vault 2.0 on Snowflake.

  What should the Architect tell the data organization? (Select TWO).

  Options:

  A.

  Change data capture can be performed using the Data Vault 2.0 HASH_DIFF concept.

  B.

  Change data capture can be performed using the Data Vault 2.0 HASH_DELTA concept.

  C.

  Using the multi-table insert feature in Snowflake, multiple Point-in-Time (PIT) tables can be loaded in parallel from a single join query from the data vault.

  D.

  Using the multi-table insert feature, multiple Point-in-Time (PIT) tables can be loaded sequentially from a single join query from the data vault.

  E.

  There are performance challenges when using Snowflake to load multiple Point-in-Time (PIT) tables in parallel from a single join query from the data vault.

  Show Answer

  Answer:

  A, C

  Explanation:

  Explanation:

  Data Vault 2.0 on Snowflake supports the HASH_DIFF concept for change data capture, which is a method to detect changes in the data by comparing the hash values of the records. Additionally, Snowflake’s multi-table insert feature allows for the loading of multiple PIT tables in parallel from a single join query, which can significantly streamline the data loading process and improve performance1.

  References =

  •Snowflake’s documentation on multi-table inserts1

  •Blog post on optimizing Data Vault architecture on Snowflake2

  Question 44

  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

  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.

  Question 45

  Database DB1 has schema S1 which has one table, T1.

  DB1 --> S1 --> T1

  The retention period of EG1 is set to 10 days.

  The retention period of s: is set to 20 days.

  The retention period of t: Is set to 30 days.

  The user runs the following command:

  Drop Database DB1;

  What will the Time Travel retention period be for T1?

  Options:

  A.

  10 days

  B.

  20 days

  C.

  30 days

  D.

  37 days

  Show Answer

  Answer:

  C

  Explanation:

  Explanation:

  The Time Travel retention period for T1 will be 30 days, which is the retention period set at the table level. The Time Travel retention period determines how long the historical data is preserved and accessible for an object after it is modified or dropped. The Time Travel retention period can be set at the account level, the database level, the schema level, or the table level. The retention period set at the lowest level of the hierarchy takes precedence over the higher levels. Therefore, the retention period set at the table level overrides the retention periods set at the schema level, the database level, or the account level. When the user drops the database DB1, the table T1 is also dropped, but the historical data is still preserved for 30 days, which is the retention period set at the table level. The user can use the UNDROP command to restore the table T1 within the 30-day period. The other options are incorrect because:

  • 10 days is the retention period set at the database level, which is overridden by the table level.
  • 20 days is the retention period set at the schema level, which is also overridden by the table level.
  • 37 days is not a valid option, as it is not the retention period set at any level.

  References:

  • Understanding & Using Time Travel
  • AT | BEFORE
  • Snowflake Time Travel & Fail-safe

  Question 46

  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

  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

  Question 47

  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

  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.

  Question 48

  A user, analyst_user has been granted the analyst_role, and is deploying a SnowSQL script to run as a background service to extract data from Snowflake.

  What steps should be taken to allow the IP addresses to be accessed? (Select TWO).

  Options:

  A.

  ALTERROLEANALYST_ROLESETNETWORK_POLICY='ANALYST_POLICY';

  B.

  ALTERUSERANALYSTJJSERSETNETWORK_POLICY='ANALYST_POLICY';

  C.

  ALTERUSERANALYST_USERSETNETWORK_POLICY='10.1.1.20';

  D.

  USE ROLE SECURITYADMIN;

  CREATE OR REPLACE NETWORK POLICY ANALYST_POLICY ALLOWED_IP_LIST = ('10.1.1.20');

  E.

  USE ROLE USERADMIN;

  CREATE OR REPLACE NETWORK POLICY ANALYST_POLICY

  ALLOWED_IP_LIST = ('10.1.1.20');

  Show Answer

  Answer:

  B, D

  Explanation:

  Explanation:

  To ensure that an analyst_user can only access Snowflake from specific IP addresses, the following steps are required:

  • Option B: This alters the network policy directly linked to analyst_user. Setting a network policy on the user level is effective and ensures that the specified network restrictions apply directly and exclusively to this user.
  • Option D: Before a network policy can be set or altered, the appropriate role with permission to manage network policies must be used. SECURITYADMIN is typically the role that has privileges to create and manage network policies in Snowflake. Creating a network policy that specifies allowed IP addresses ensures that only requests coming from those IPs can access Snowflake under this policy. After creation, this policy can be linked to specific users or roles as needed.

  Options A and E mention altering roles or using the wrong role (USERADMIN typically does not manage network security settings), and option C incorrectly attempts to set a network policy directly as an IP address, which is not syntactically or functionally valid.References: Snowflake's security management documentation covering network policies and role-based access controls.

  Load More ARA-R01 Questions Unlock All ARA-R01 Questions
  Demo: 48 questions
  Total 162 questions

  Get ARA-R01 Full Access Download ARA-R01 PDF
  ×

  Unlock ARA-R01 Features

  • ARA-R01 All Real Exam Questions
  • ARA-R01 Exam easy to use and print PDF format
  • Download Free ARA-R01 Demo (Try before Buy)
  • Free Frequent Updates
  • 100% Passing Guarantee by Certensure

  Download Demo Get Full Access Now

  Close
  ×

  Questions & Answers PDF Demo

  • ARA-R01 All Real Exam Questions
  • ARA-R01 Exam easy to use and print PDF format
  • Download Free ARA-R01 Demo (Try before Buy)
  • Free Frequent Updates
  • 100% Passing Guarantee by Certensure

  Close
  ×

  Practice Tesitng Engine Demo

  
  
  
  

  Close

  Copyright © 2014-2024 Certensure. All Rights Reserved

  • Home
  • All Vendors
  • Contact us
  • About us
  • Guarantee
  • Unlimited Engine
  • DMCA