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

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 DATA_RETENTION_TIME_IN_DAYS parameter controls the Time Travel retention period for an object (database, schema, or table) in Snowflake. This parameter specifies the number of days for which historical data is preserved and can be accessed using Time Travel operations (SELECT, CREATE … CLONE, UNDROP)1.
• The requirement for recovery of staging tables on a rolling 7-day basis means that the DATA_RETENTION_TIME_IN_DAYS parameter should be set to 7 at the database level. However, this parameter can be overridden at the lower levels (schema or table) if they have a different value1.
• Therefore, one possible cause for certain tables to remain unrecoverable past 1 day is that the DATA_RETENTION_TIME_IN_DAYS for the staging schema has been set to 1 day. This would override the database level setting and limit the Time Travel retention period for all the tables in the schema to 1 day. To fix this, the parameter should be unset or set to 7 at the schema level1. Therefore, option B is correct.
• Another possible cause for certain tables to remain unrecoverable past 1 day is that the staging tables are of the TRANSIENT type. Transient tables are tables that do 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 replicated2. To fix this, the tables should be created as permanent tables, which can have a Time Travel retention period of up to 90 days1. Therefore, option D is correct.
• Option A is incorrect because the MANAGED ACCESS feature is not related to the data recovery requirement. MANAGED ACCESS is a feature that allows granting access privileges to objects without explicitly granting the privileges to roles. It does not affect the Time Travel retention period or the data availability3.
• Option C is incorrect because there is no 1 TB limit for data recovery in Snowflake. The data storage size does not affect the Time Travel retention period or the data availability4.
• Option E is incorrect because there is no ALLOW_RECOVERY privilege in Snowflake. The privilege required to perform Time Travel operations is SELECT, which allows querying historical data in tables5.

References: : Understanding & Using Time Travel : Transient Tables : Managed Access : Understanding Storage Cost : Table Privileges

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.

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

• Add the IP address in the error message to the allowed list in the network policy. This will allow the IP address to access Snowflake using the Private Link URLs. Alternatively, the Architect can disable the network policy if it is not required for security reasons.
• Update the configuration of the Azure AD SSO to use the Private Link URLs. This will ensure that the SSO authentication process uses the Private Link URLs instead of the public URLs. The configuration can be updated by following the steps in the Azure documentation1.

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

 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

Enabling the search optimization service on the table can improve the performance of queries that have selective filtering criteria, which seems to be the case here. This service optimizes the execution of queries by creating a persistent data structure called a search access path, which allows some micro-partitions to be skipped during the scanning process. This can significantly speed up query performance without increasing compute costs1.

References

•Snowflake Documentation on Search Optimization Service1.

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

To provide near real-time sales results to category managers, the Architect can use the following steps:

• Create an external stage that references the cloud storage location where the POS sends the sales transactions files. The external stage should use the file format and encryption settings that match the source files2
• Create a Snowpipe that loads the files from the external stage into a target table in Snowflake. The Snowpipe should be configured with AUTO_INGEST = true, which means that it will automatically detect and ingest new files as they arrive in the external stage. The Snowpipe should also use a copy option to purge the files from the external stage after loading, to avoid duplicate ingestion3
• Create a stream on the target table that captures the INSERTS made by the Snowpipe. The stream should include the metadata columns that provide information about the file name, path, size, and last modified time. The stream should also have a retention period that matches the real-time analytics needs4
• Create a task that runs a query on the stream to process the near real-time data. The query should use the stream metadata to extract the store number and timestamps from the file name and path, and perform the calculations for exceptions, aggregations, and scoring using external functions. The query should also output the results to another table or view that can be accessed by the category managers. The task should be scheduled to run at a frequency that matches the real-time analytics needs, such as every minute or every 5 minutes.

The other options are not optimal or feasible for providing near real-time results:

• All files should be concatenated before ingestion into Snowflake to avoid micro-ingestion. This option is not recommended because it would introduce additional latency and complexity in the data pipeline. Concatenating files would require an external process or service that monitors the cloud storage location and performs the file merging operation. This would delay the ingestion of new files into Snowflake and increase the risk of data loss or corruption. Moreover, concatenating files would not avoid micro-ingestion, as Snowpipe would still ingest each concatenated file as a separate load.
• An external scheduler should examine the contents of the cloud storage location and issue SnowSQL commands to process the data at a frequency that matches the real-time analytics needs. This option is not necessary because Snowpipe can automatically ingest new files from the external stage without requiring an external trigger or scheduler. Using an external scheduler would add more overhead and dependency to the data pipeline, and it would not guarantee near real-time ingestion, as it would depend on the polling interval and the availability of the external scheduler.
• The copy into command with a task scheduled to run every second should be used to achieve the near-real time requirement. This option is not feasible because tasks cannot be scheduled to run every second in Snowflake. The minimum interval for tasks is one minute, and even that is not guaranteed, as tasks are subject to scheduling delays and concurrency limits. Moreover, using the copy into command with a task would not leverage the benefits of Snowpipe, such as automatic file detection, load balancing, and micro-partition optimization. References:
• 1: SnowPro Advanced: Architect | Study Guide
• 2: Snowflake Documentation | Creating Stages
• 3: Snowflake Documentation | Loading Data Using Snowpipe
• 4: Snowflake Documentation | Using Streams and Tasks for ELT
• : Snowflake Documentation | Creating Tasks
• : Snowflake Documentation | Best Practices for Loading Data
• : Snowflake Documentation | Using the Snowpipe REST API
• : Snowflake Documentation | Scheduling Tasks
• : SnowPro Advanced: Architect | Study Guide
• : Creating Stages
• : Loading Data Using Snowpipe
• : Using Streams and Tasks for ELT
• : [Creating Tasks]
• : [Best Practices for Loading Data]
• : [Using the Snowpipe REST API]
• : [Scheduling Tasks]

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

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.

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

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.

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

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

• The correct answer is B and E because they use the correct syntax and values for the identifier function and the session variables.
• The identifier function allows you to use a variable or expression as an identifier (such as a table name or column name) in a SQL statement. It takes a single argument and returns it as an identifier. For example, identifier($tbl_ref) returns EMP_TBL as an identifier.
• The session variables are set using the SET command and can be referenced using the $ sign. For example, $var1 returns Name1 as a value.
• Option A is incorrect because it uses Stbl_ref and Scol_ref, which are not valid session variables or identifiers. They should be $tbl_ref and $col_ref instead.
• Option C is incorrect because it uses identifier, which is not a valid syntax for the identifier function. It should be identifier($tbl_ref) instead.
• Option D is incorrect because it uses Cvarl, var2, and var3, which are not valid session variables or values. They should be $var1, $var2, and $var3 instead. References:
• Snowflake Documentation: Identifier Function
• Snowflake Documentation: Session Variables
• Snowflake Learning: SnowPro Advanced: Architect Exam Study Guide

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.

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

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

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

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

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

According to the SnowPro Advanced: Architect documents and learning resources, the Snowflake functionality that should be used to meet these requirements are:

• Use private connectivity from a cloud provider. This feature allows customers to connect to Snowflake from their own private network without exposing their data to the public Internet. Snowflake integrates with AWS PrivateLink, Azure Private Link, and Google Cloud Private Service Connect to offer private connectivity from customers’ VPCs or VNets to Snowflake endpoints. Customers can control how traffic reaches the Snowflake endpoint and avoid the need for proxies or public IP addresses123.
• Set up SSO for federated authentication. This feature allows customers to use their existing identity provider (IdP) to authenticate users for SSO access to Snowflake. Snowflake supports most SAML 2.0-compliant vendors as an IdP, including Okta, Microsoft AD FS, Google G Suite, Microsoft Azure Active Directory, OneLogin, Ping Identity, and PingOne. By setting up SSO for federated authentication, customers can leverage their existing user credentials and profile information, and provide stronger security than username/password authentication4.

The other options are incorrect because they do not meet the requirements or are not feasible. Option A is incorrect because setting up replication does not allow users to connect from outside the company VPN. Replication is a feature of Snowflake that enables copying databases across accounts in different regions and cloud platforms. Replication does not affect the connectivity or visibility of the accounts5. Option B is incorrect because provisioning a unique company Tri-Secret Secure key does not affect the network or authentication requirements. Tri-Secret Secure is a feature of Snowflake that 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. Tri-Secret Secure provides an additional layer of security and control over the data encryption and decryption process, but it does not enable private connectivity or SSO6. Option E is incorrect because using a proxy Snowflake account outside the VPN, enabling client redirect for user logins, is not a supported or recommended way of meeting the requirements. Client redirect is a feature of Snowflake that allows customers to connect to a different Snowflake account than the one specified in the connection string. This feature is useful for scenarios such as cross-region failover, data sharing, and account migration, but it does not provide private connectivity or SSO7. References: AWS PrivateLink & Snowflake | Snowflake Documentation, Azure Private Link & Snowflake | Snowflake Documentation, Google Cloud Private Service Connect & Snowflake | Snowflake Documentation, Overview of Federated Authentication and SSO | Snowflake Documentation, Replicating Databases Across Multiple Accounts | Snowflake Documentation, Tri-Secret Secure | Snowflake Documentation, Redirecting Client Connections | Snowflake Documentation

Event notifications in Snowpipe are messages sent by cloud storage providers to notify Snowflake of new or modified files in a stage. Snowpipe uses these notifications to trigger data loading from the stage to the target table. When a pipe is paused, event messages received for the pipe enter a limited retention period, which varies depending on the cloud storage provider. If the pipe is not resumed within the retention period, the event messages will be discarded and the data will not be loaded automatically. To load the data, the pipe must be resumed and the COPY command must be executed manually. This is a characteristic of event notifications in Snowpipe that distinguishes them from other options. References: Snowflake Documentation: Using Snowpipe, Snowflake Documentation: Pausing and Resuming a Pipe

 This is the correct answer because it allows the company to ingest data from different regions using a storage integration for the external stage. A storage integration is a feature that enables secure and easy access to files in external cloud storage from Snowflake. A storage integration can be used to create an external stage, which is a named location that references the files in the external storage. An external stage can be used to load data into Snowflake tables using the COPY INTO command, or to unload data from Snowflake tables using the COPY INTO LOCATION command. A storage integration can support multiple regions and cloud platforms, as long as the external storage service is compatible with Snowflake12.

References:

• Snowflake Documentation: Storage Integrations
• Snowflake Documentation: External Stages

 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

The data formats that are supported for the messages when using the Snowflake Connector for Kafka are Avro and JSON. These are the two formats that the connector can parse and convert into Snowflake table rows. The connector supports both schemaless and schematized JSON, as well as Avro with or without a schema registry1. The other options are incorrect because they are not supported data formats for the messages. CSV, XML, and Parquet are not formats that the connector can parse and convert into Snowflake table rows. If the messages are in these formats, the connector will load them as VARIANT data type and store them as raw strings in the table2. References: Snowflake Connector for Kafka | Snowflake Documentation, Loading Protobuf Data using the Snowflake Connector for Kafka | Snowflake Documentation

When using the COPY command to load data into Snowflake, if a column has a default value set to CURRENT_TIME() or CURRENT_TIMESTAMP(), all rows loaded by that specific COPY command will have the same timestamp. This is because the default value for the timestamp is evaluated at the start of the COPY operation, and that same value is applied to all rows loaded by that operation.

References: This behavior is consistent with Snowflake’s documentation on the CURRENT_TIMESTAMP function, which specifies that the timestamp is captured at the time the statement is executed1.

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

• Data Loading Considerations
• Preparing Your Data Files
• Planning a Data Load

 Snowflake context functions are functions that return information about the current session, user, role, warehouse, database, schema, or object. They can be used to help determine whether a user is authorized to see data that has column-level security enforced by setting masking policy conditions based on the context functions. The following context functions are relevant for column-level security:

• current_role: This function returns the name of the role in use for the current session. It can be used to set masking policy conditions that target the current session and are not affected by the execution context of the SQL statement. For example, a masking policy condition using current_role can allow or deny access to a column based on the role that the user activated in the session.
• invoker_role: This function returns the name of the executing role in a SQL statement. It can be used to set masking policy conditions that target the executing role and are affected by the execution context of the SQL statement. For example, a masking policy condition using invoker_role can allow or deny access to a column based on the role that the user specified in the SQL statement, such as using the AS ROLE clause or a stored procedure.
• is_role_in_session: This function returns TRUE if the user’s current role in the session (i.e. the role returned by current_role) inherits the privileges of the specified role. It can be used to set masking policy conditions that involve role hierarchy and privilege inheritance. For example, a masking policy condition using is_role_in_session can allow or deny access to a column based on whether the user’s current role is a lower privilege role in the specified role hierarchy.

The other options are not valid ways to use the Snowflake context functions for column-level security:

• Set masking policy conditions using is_role_in_session targeting the role in use for the current account. This option is incorrect because is_role_in_session does not target the role in use for the current account, but rather the role in use for the current session. Also, the current account is not a role, but rather a logical entity that contains users, roles, warehouses, databases, and other objects.
• Determine if there are ownership privileges on the masking policy that would allow the use of any function. This option is incorrect because ownership privileges on the masking policy do not affect the use of any function, but rather the ability to create, alter, or drop the masking policy. Also, this is not a way to use the Snowflake context functions, but rather a way to check the privileges on the masking policy object.
• Assign the accountadmin role to the user who is executing the object. This option is incorrect because assigning the accountadmin role to the user who is executing the object does not involve using the Snowflake context functions, but rather granting the highest-level role to the user. Also, this is not a recommended practice for column-level security, as it would give the user full access to all objects and data in the account, which could compromise data security and governance.

References:

• Context Functions
• Advanced Column-level Security topics
• Snowflake Data Governance: Column Level Security Overview
• Data Security Snowflake Part 2 - Column Level Security

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]

• 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@1b895f20

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

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

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

  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 32

  

  Based on the architecture in the image, how can the data from DB1 be copied into TBL2? (Select TWO).

  A)

  

  B)

  

  C)

  

  D)

  

  E)

  

  Options:

  A.

  Option A

  B.

  Option B

  C.

  Option C

  D.

  Option D

  E.

  Option E

  Show Answer Buy Now

  Answer:

  B, E

  Explanation:

  Explanation:

  • The architecture in the image shows a Snowflake data platform with two databases, DB1 and DB2, and two schemas, SH1 and SH2. DB1 contains a table TBL1 and a stage STAGE1. DB2 contains a table TBL2. The image also shows a snippet of code written in SQL language that copies data from STAGE1 to TBL2 using a file format FF PIPE 1.
  • To copy data from DB1 to TBL2, there are two possible options among the choices given:

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

  use database DB2;

  use schema SH2;

  create stage EXT_STAGE1

  url = @DB1.SH1.STAGE1;

  • uk.co.certification.simulator.questionpool.PList@1c350630

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

  copy into TBL2

  from @EXT_STAGE1

  file format = (format name = DB1.SH1.FF PIPE 1);

  • uk.co.certification.simulator.questionpool.PList@1c350670

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

  use database DB2;

  use schema SH2;

  insert into TBL2

  select * from DB1.SH1.TBL1;

  • The other options are not valid because:

  References:

  • 1: CREATE STAGE | Snowflake Documentation
  • 2: COPY INTO table | Snowflake Documentation
  • 3: Cross-database Queries | Snowflake Documentation

  Questions 33

  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 34

  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 35

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

  Which solution would be MOST cost-effective and performant?

  Options:

  A.

  Use zero-copy cloning into transient tables.

  B.

  Use zero-copy cloning into permanent tables.

  C.

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

  D.

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

  Show Answer Buy Now

  Answer:

  A

  Explanation:

  Explanation:

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

  References

  •Snowflake Documentation on Zero-Copy Cloning3.

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

  Questions 36

  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:

  B

  Explanation:

  Explanation:

  When sharing unstructured data within Snowflake, using a scoped URL is recommended. Scoped URLs provide temporary access to staged files without granting privileges to the stage itself, enhancing security. The URL expires when the persisted query result period ends, which is currently set to 24 hours. This approach is suitable for sharing unstructured data over secure views within Snowflake’s data sharing framework.

  References: The answer is based on Snowflake’s official documentation regarding the sharing of unstructured data and the use of scoped URLs1.

  Questions 37

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

  A)

  

  B)

  

  C)

  

  D)

  

  Options:

  A.

  Option A

  B.

  Option B

  C.

  Option C

  D.

  Option D

  Show Answer Buy Now

  Answer:

  A

  Explanation:

  Explanation:

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

  • Recognizing Disk Spilling
  • Managing the Kafka Connector

  Questions 38

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

  

  This command results in the following error:

  SQL compilation error: invalid parameter 'validation_mode'

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

  Options:

  A.

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

  B.

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

  C.

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

  D.

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

  Show Answer Buy Now

  Answer:

  C

  Explanation:

  Explanation:

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

  References: : COPY INTO

   : Transforming Data During a Load
  Questions 39

  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 40

  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 41

  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 42

  When loading data into a table that captures the load time in a column with a default value of either CURRENT_TIME () or CURRENT_TIMESTAMP() what will occur?

  Options:

  A.

  All rows loaded using a specific COPY statement will have varying timestamps based on when the rows were inserted.

  B.

  Any rows loaded using a specific COPY statement will have varying timestamps based on when the rows were read from the source.

  C.

  Any rows loaded using a specific COPY statement will have varying timestamps based on when the rows were created in the source.

  D.

  All rows loaded using a specific COPY statement will have the same timestamp value.

  Show Answer Buy Now

  Answer:

  D

  Explanation:

  Explanation:

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

  • Snowflake Documentation: Loading Data into Tables with Default Values
  • Snowflake Documentation: COPY INTO table

  Questions 43

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

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

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

  Options:

  A.

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

  B.

  Publish product_category and product_details data sets on the Snowflake Marketplace.

  C.

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

  D.

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

  Show Answer Buy Now

  Answer:

  D

  Explanation:

  Explanation:

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

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

  References:

  • Reader Accounts
  • Secure Views

  Questions 44

  A company is storing large numbers of small JSON files (ranging from 1-4 bytes) that are received from IoT devices and sent to a cloud provider. In any given hour, 100,000 files are added to the cloud provider.

  What is the MOST cost-effective way to bring this data into a Snowflake table?

  Options:

  A.

  An external table

  B.

  A pipe

  C.

  A stream

  D.

  A copy command at regular intervals

  Show Answer Buy Now

  Answer:

  B

  Explanation:

  Explanation:

  • A pipe is a Snowflake object that continuously loads data from files in a stage (internal or external) into a table. A pipe can be configured to use auto-ingest, which means that Snowflake automatically detects new or modified files in the stage and loads them into the table without any manual intervention1.
  • A pipe is the most cost-effective way to bring large numbers of small JSON files into a Snowflake table, because it minimizes the number of COPY commands executed and the number of micro-partitions created. A pipe can use file aggregation, which means that it can combine multiple small files into a single larger file before loading them into the table. This reduces the load time and the storage cost of the data2.
  • An external table is a Snowflake object that references data files stored in an external location, such as Amazon S3, Google Cloud Storage, or Microsoft Azure Blob Storage. An external table does not store the data in Snowflake, but only provides a view of the data for querying. An external table is not a cost-effective way to bring data into a Snowflake table, because it does not support file aggregation, and it requires additional network bandwidth and compute resources to query the external data3.
  • A stream is a Snowflake object that records the history of changes (inserts, updates, and deletes) made to a table. A stream can be used to consume the changes from a table and apply them to another table or a task. A stream is not a way to bring data into a Snowflake table, but a way to process the data after it is loaded into a table4.
  • A copy command is a Snowflake command that loads data from files in a stage into a table. A copy command can be executed manually or scheduled using a task. A copy command is not a cost-effective way to bring large numbers of small JSON files into a Snowflake table, because it does not support file aggregation, and it may create many micro-partitions that increase the storage cost of the data5.

  References: : Pipes : Loading Data Using Snowpipe : External Tables : Streams : COPY INTO

  Questions 45

  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 46

  A healthcare company is deploying a Snowflake account that may include Personal Health Information (PHI). The company must ensure compliance with all relevant privacy standards.

  Which best practice recommendations will meet data protection and compliance requirements? (Choose three.)

  Options:

  A.

  Use, at minimum, the Business Critical edition of Snowflake.

  B.

  Create Dynamic Data Masking policies and apply them to columns that contain PHI.

  C.

  Use the Internal Tokenization feature to obfuscate sensitive data.

  D.

  Use the External Tokenization feature to obfuscate sensitive data.

  E.

  Rewrite SQL queries to eliminate projections of PHI data based on current_role().

  F.

  Avoid sharing data with partner organizations.

  Show Answer Buy Now

  Answer:

  A, B, D

  Explanation:

  Explanation:

  • A healthcare company that handles PHI data must ensure compliance with relevant privacy standards, such as HIPAA, HITRUST, and GDPR. Snowflake provides several features and best practices to help customers meet their data protection and compliance requirements1.
  • One best practice recommendation is to use, at minimum, the Business Critical edition of Snowflake. This edition provides the highest level of data protection and security, including end-to-end encryption with customer-managed keys, enhanced object-level security, and HIPAA and HITRUST compliance2. Therefore, option A is correct.
  • Another best practice recommendation is to create Dynamic Data Masking policies and apply them to columns that contain PHI. Dynamic Data Masking is a feature that allows masking or redacting sensitive data based on the current user’s role. This way, only authorized users can view the unmasked data, while others will see masked values, such as NULL, asterisks, or random characters3. Therefore, option B is correct.
  • A third best practice recommendation is to use the External Tokenization feature to obfuscate sensitive data. External Tokenization is a feature that allows replacing sensitive data with tokens that are generated and stored by an external service, such as Protegrity. This way, the original data is never stored or processed by Snowflake, and only authorized users can access the tokenized data through the external service4. Therefore, option D is correct.
  • Option C is incorrect, because the Internal Tokenization feature is not available in Snowflake. Snowflake does not provide any native tokenization functionality, but only supports integration with external tokenization services4.
  • Option E is incorrect, because rewriting SQL queries to eliminate projections of PHI data based on current_role() is not a best practice. This approach is error-prone, inefficient, and hard to maintain. A better alternative is to use Dynamic Data Masking policies, which can automatically mask data based on the user’s role without modifying the queries3.
  • Option F is incorrect, because avoiding sharing data with partner organizations is not a best practice. Snowflake enables secure and governed data sharing with internal and external consumers, such as business units, customers, or partners. Data sharing does not involve copying or moving data, but only granting access privileges to the shared objects. Data sharing can also leverage Dynamic Data Masking and External Tokenization features to protect sensitive data5.

  References: : Snowflake’s Security & Compliance Reports : Snowflake Editions : Dynamic Data Masking : External Tokenization : Secure Data Sharing

  Questions 47

  There are two databases in an account, named fin_db and hr_db which contain payroll and employee data, respectively. Accountants and Analysts in the company require different permissions on the objects in these databases to perform their jobs. Accountants need read-write access to fin_db but only require read-only access to hr_db because the database is maintained by human resources personnel.

  An Architect needs to create a read-only role for certain employees working in the human resources department.

  Which permission sets must be granted to this role?

  Options:

  A.

  USAGE on database hr_db, USAGE on all schemas in database hr_db, SELECT on all tables in database hr_db

  B.

  USAGE on database hr_db, SELECT on all schemas in database hr_db, SELECT on all tables in database hr_db

  C.

  MODIFY on database hr_db, USAGE on all schemas in database hr_db, USAGE on all tables in database hr_db

  D.

  USAGE on database hr_db, USAGE on all schemas in database hr_db, REFERENCES on all tables in database hr_db

  Show Answer Buy Now

  Answer:

  A

  Explanation:

  Explanation:

  • To create a read-only role for certain employees working in the human resources department, the role needs to have the following permissions on the hr_db database:
  • Option A is the correct answer because it grants the minimum permissions required for a read-only role on the hr_db database.
  • Option B is incorrect because SELECT on schemas is not a valid permission. Schemas only support USAGE and CREATE permissions.
  • Option C is incorrect because MODIFY on the database is not a valid permission. Databases only support USAGE, CREATE, MONITOR, and OWNERSHIP permissions. Moreover, USAGE on tables is not sufficient for querying the data. Tables support SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, and OWNERSHIP permissions.
  • Option D is incorrect because REFERENCES on tables is not relevant for querying the data. REFERENCES permission allows the role to create foreign key constraints on the tables.

  References:

  • : https://docs.snowflake.com/en/user-guide/security-access-control-privileges.html#database-privileges
  • : https://docs.snowflake.com/en/user-guide/security-access-control-privileges.html#schema-privileges
  • : https://docs.snowflake.com/en/user-guide/security-access-control-privileges.html#table-privileges

  Questions 48

  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.

  SnowPro Advanced: Architect |

  Exam Code: ARA-C01

  Exam Name: SnowPro Advanced: Architect Certification Exam

  Last Update: Apr 23, 2024

  Questions: 155

  

  ARA-C01 PDF

  $28  $80

  $56  $159.99

   Add to Cart

  

  ARA-C01 Testing Engine

  $33.25  $95

  $63  $179.99

   Add to Cart

  

  ARA-C01 PDF + Testing Engine

  $45.5  $130

   Add to Cart

  Quick Links

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

  Recently New Released Certification Exams

  • Energy-and-Utilities-Cloud Apr 23, 2024
  • NetSuite-Administrator Apr 23, 2024
  • TCA-Tibco-BusinessWorks Apr 23, 2024
  • KX3-003 Apr 23, 2024
  • Scripting-and-Programming-Foundations Apr 23, 2024
  • 112-51 Apr 23, 2024
  • CIS-FSM Apr 23, 2024
  • NSK101 Apr 23, 2024
  • Cybersecurity-Audit-Certificate Apr 23, 2024
  • LEED-AP-ID+C Apr 23, 2024
  • Media-Cloud-Consultant Apr 23, 2024
  • ISTQB-Agile-Public Apr 23, 2024
  • LEED-AP-O+M Apr 23, 2024
  • Sitecore-XM-Cloud-Developer Apr 23, 2024
  • WELL-AP Apr 23, 2024
  • CTAL-TTA Apr 23, 2024
  • 1z0-1119-1 Apr 23, 2024
  • Salesforce-Marketing-Associate Apr 23, 2024
  • CESP Apr 23, 2024
  • Salesforce-Communications-Cloud Apr 23, 2024

  Site Secure

  

  TESTED 24 Apr 2024

  Copyright © 2014-2024 CramTick. All Rights Reserved