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

This is the valid object hierarchy when building a Snowflake environment, according to the Snowflake documentation and the web search results. Snowflake is a cloud data platform that supports various types of objects, such as databases, schemas, tables, views, stages, warehouses, and more. These objects are organized in a hierarchical structure, as follows:

• Organization: An organization is the top-level entity that represents a group of Snowflake accounts that are related by business needs or ownership. An organization can have one or more accounts, and can enable features such as cross-account data sharing, billing and usage reporting, and single sign-on across accounts12.
• Account: An account is the primary entity that represents a Snowflake customer. An account can have one or more databases, schemas, stages, warehouses, and other objects. An account can also have one or more users, roles, and security integrations. An account is associated with a specific cloud platform, region, and Snowflake edition34.
• Database: A database is a logical grouping of schemas. A database can have one or more schemas, and can store structured, semi-structured, or unstructured data. A database can also have properties such as retention time, encryption, and ownership56.
• Schema: A schema is a logical grouping of tables, views, stages, and other objects. A schema can have one or more objects, and can define the namespace and access control for the objects. A schema can also have properties such as ownership and default warehouse .
• Stage: A stage is a named location that references the files in external or internal storage. A 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 stage can be created at the account, database, or schema level, and can have properties such as file format, encryption, and credentials .

The other options listed are not valid object hierarchies, because they either omit or misplace some objects in the structure. For example, option A omits the organization level and places the warehouse under the schema level, which is incorrect. Option C omits the organization, account, and stage levels, and places the table under the schema level, which is incorrect. Option D omits the database level and places the stage and table under the account level, which is incorrect.

References:

• Snowflake Documentation: Organizations
• Snowflake Blog: Introducing Organizations in Snowflake
• Snowflake Documentation: Accounts
• Snowflake Blog: Understanding Snowflake Account Structures
• Snowflake Documentation: Databases
• Snowflake Blog: How to Create a Database in Snowflake
• [Snowflake Documentation: Schemas]
• [Snowflake Blog: How to Create a Schema in Snowflake]
• [Snowflake Documentation: Stages]
• [Snowflake Blog: How to Use Stages in Snowflake]

 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

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

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.

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.

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.

This is the best practice recommendation for the order of priority when applications authenticate to Snowflake, according to the Snowflake documentation and the web search results. Authentication is the process of verifying the identity of a user or application that connects to Snowflake. Snowflake supports multiple authentication methods, each with different advantages and disadvantages. The recommended order of priority is based on the following factors:

• Security: The authentication method should provide a high level of security and protection against unauthorized access or data breaches. The authentication method should also support multi-factor authentication (MFA) or single sign-on (SSO) for additional security.
• Convenience: The authentication method should provide a smooth and easy user experience, without requiring complex or manual steps. The authentication method should also support seamless integration with external identity providers or applications.
• Flexibility: The authentication method should provide a range of options and features to suit different use cases and scenarios. The authentication method should also support customization and configuration to meet specific requirements.

Based on these factors, the recommended order of priority is:

• OAuth (either Snowflake OAuth or External OAuth): OAuth is an open standard for authorization that allows applications to access Snowflake resources on behalf of a user, without exposing the user’s credentials. OAuth provides a high level of security, convenience, and flexibility, as it supports MFA, SSO, token-based authentication, and various grant types and scopes. OAuth can be implemented using either Snowflake OAuth or External OAuth, depending on the identity provider and the application12.
• External browser: External browser is an authentication method that allows users to log in to Snowflake using a web browser and an external identity provider, such as Okta, Azure AD, or Ping Identity. External browser provides a high level of security and convenience, as it supports MFA, SSO, and federated authentication. External browser also provides a consistent user interface and experience across different platforms and devices34.
• Okta native authentication: Okta native authentication is an authentication method that allows users to log in to Snowflake using Okta as the identity provider, without using a web browser. Okta native authentication provides a high level of security and convenience, as it supports MFA, SSO, and federated authentication. Okta native authentication also provides a native user interface and experience for Okta users, and supports various Okta features, such as password policies and user management56.
• Key Pair Authentication: Key Pair Authentication is an authentication method that allows users to log in to Snowflake using a public-private key pair, without using a password. Key Pair Authentication provides a high level of security, as it relies on asymmetric encryption and digital signatures. Key Pair Authentication also provides a flexible and customizable authentication option, as it supports various key formats, algorithms, and expiration times. Key Pair Authentication is mostly used for service account users, such as applications or scripts that connect to Snowflake programmatically7 .
• Password: Password is the simplest and most basic authentication method that allows users to log in to Snowflake using a username and password. Password provides a low level of security, as it relies on symmetric encryption and is vulnerable to brute force attacks or phishing. Password also provides a low level of convenience and flexibility, as it requires manual input and management, and does not support MFA or SSO. Password is the least recommended authentication method, and should be used only as a last resort or for testing purposes .

References:

• Snowflake Documentation: Snowflake OAuth
• Snowflake Documentation: External OAuth
• Snowflake Documentation: External Browser Authentication
• Snowflake Blog: How to Use External Browser Authentication with Snowflake
• Snowflake Documentation: Okta Native Authentication
• Snowflake Blog: How to Use Okta Native Authentication with Snowflake
• Snowflake Documentation: Key Pair Authentication
• [Snowflake Blog: How to Use Key Pair Authentication with Snowflake]
• [Snowflake Documentation: Password Authentication]
• [Snowflake Blog: How to Use Password Authentication with Snowflake]

According to the SnowPro Advanced: Architect documents and learning resources, the ways that Snowflake databases that are created from shares differ from standard databases that are not created from shares are:

• Shared databases are read-only. This means that the data consumers who access the shared databases cannot modify or delete the data or the objects in the databases. The data providers who share the databases have full control over the data and the objects, and can grant or revoke privileges on them1.
• Shared databases cannot be cloned. This means that the data consumers who access the shared databases cannot create a copy of the databases or the objects in the databases. The data providers who share the databases can clone the databases or the objects, but the clones are not automatically shared2.
• Shared databases are not supported by Time Travel. This means that the data consumers who access the shared databases cannot use the AS OF clause to query historical data or restore deleted data. The data providers who share the databases can use Time Travel on the databases or the objects, but the historical data is not visible to the data consumers3.

The other options are incorrect because they are not ways that Snowflake databases that are created from shares differ from standard databases that are not created from shares. Option B is incorrect because shared databases do not need to be refreshed in order for new data to be visible. The data consumers who access the shared databases can see the latest data as soon as the data providers update the data1. Option E is incorrect because shared databases will not have the PUBLIC or INFORMATION_SCHEMA schemas without explicitly granting these schemas to the share. The data consumers who access the shared databases can only see the objects that the data providers grant to the share, and the PUBLIC and INFORMATION_SCHEMA schemas are not granted by default4. Option F is incorrect because shared databases cannot be created as transient databases. Transient databases are databases that do not support Time Travel or Fail-safe, and can be dropped without affecting the retention period of the data. Shared databases are always created as permanent databases, regardless of the type of the source database5. References: Introduction to Secure Data Sharing | Snowflake Documentation, Cloning Objects | Snowflake Documentation, Time Travel | Snowflake Documentation, Working with Shares | Snowflake Documentation, CREATE DATABASE | 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 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

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

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

References:

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

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 SnowPro Advanced: Architect documents and learning resources, a characteristic of loading data into Snowflake using the Snowflake Connector for Kafka is that the Connector creates and manages its own stage, file format, and pipe objects. The stage is an internal stage that is used to store the data files from the Kafka topics. The file format is a JSON or Avro file format that is used to parse the data files. The pipe is a Snowpipe object that is used to load the data files into the Snowflake table. The Connector automatically creates and configures these objects based on the Kafka configuration properties, and handles the cleanup and maintenance of these objects1.

The other options are incorrect because they are not characteristics of loading data into Snowflake using the Snowflake Connector for Kafka. Option A is incorrect because the Connector works in Snowflake regions that use any cloud infrastructure, not just AWS. The Connector supports AWS, Azure, and Google Cloud platforms, and can load data across different regions and cloud platforms using data replication2. Option B is incorrect because the Connector does not work with all file formats, only JSON and Avro. The Connector expects the data in the Kafka topics to be in JSON or Avro format, and parses the data accordingly. Other file formats, such as text, ORC, Parquet, or XML, are not supported by the Connector3. Option D is incorrect because loads using the Connector do not have lower latency than Snowpipe, and do not ingest data in real time. The Connector uses Snowpipe to load data into Snowflake, and inherits the same latency and performance characteristics of Snowpipe. The Connector does not provide real-time ingestion, but near real-time ingestion, depending on the frequency and size of the data files4. References: Installing and Configuring the Kafka Connector | Snowflake Documentation, Sharing Data Across Regions and Cloud Platforms | Snowflake Documentation, Overview of the Kafka Connector | Snowflake Documentation, Using Snowflake Connector for Kafka With Snowpipe Streaming | Snowflake Documentation

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

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

  create pipe mypipe as

  copy into mytable

  from (

  select * from @mystage

  where col1 = 'A' and col2 > 10

  );

  • uk.co.certification.simulator.questionpool.PList@189de0b0

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

  create pipe mypipe as

  copy into mytable (col1, col2, col3)

  from (

  select col3, col1, col2 from @mystage

  );

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

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

  create pipe mypipe as

  copy into mytable (col1, col2)

  from (

  select col1, col2 from @mystage

  );

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

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

  create pipe mypipe as

  copy into mytable (col1, col2)

  from (

  select col1::date, col2 from @mystage

  );

  • uk.co.certification.simulator.questionpool.PList@189df1f0

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

  create pipe mypipe as

  copy into mytable (col1, col2, col3)

  from (

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

  join othertable t on s.col1 = t.col1

  );

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

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

  create pipe mypipe as

  copy into mytable

  from (

  select * from @mystage

  on error abort

  );

  References:

  • 1: CREATE PIPE | Snowflake Documentation
  • 2: Transforming Data During a Load | Snowflake Documentation
  Question # 19

  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?

  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.

  View Answer Full Access

  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

  Question # 20

  A group of Data Analysts have been granted the role analyst role. They need a Snowflake database where they can create and modify tables, views, and other objects to load with their own data. The Analysts should not have the ability to give other Snowflake users outside of their role access to this data.

  How should these requirements be met?

  A.

  Grant ANALYST_R0LE OWNERSHIP on the database, but make sure that ANALYST_ROLE does not have the MANAGE GRANTS privilege on the account.

  B.

  Grant SYSADMIN ownership of the database, but grant the create schema privilege on the database to the ANALYST_ROLE.

  C.

  Make every schema in the database a managed access schema, owned by SYSADMIN, and grant create privileges on each schema to the ANALYST_ROLE for each type of object that needs to be created.

  D.

  Grant ANALYST_ROLE ownership on the database, but grant the ownership on future [object type] s in database privilege to SYSADMIN.

  View Answer Full Access

  Answer:

  C

  Explanation:

  Explanation:

  The requirements state that the data analysts need to be able to create and modify database objects and load data, but should not be able to manage access for users outside of their role.

  Option C: By making each schema within the database a managed access schema and having them owned by SYSADMIN, the ability to grant privileges on the schema's objects is strictly controlled. Managed access schemas limit the granting of privileges to the role specified as the owner of the schema, in this case, SYSADMIN. The ANALYST_ROLE can be granted the privileges necessary to create and modify objects within these schemas, satisfying the requirement for the analysts to perform their tasks without being able to extend access beyond their role.

  Question # 21

  A company has built a data pipeline using Snowpipe to ingest files from an Amazon S3 bucket. Snowpipe is configured to load data into staging database tables. Then a task runs to load the data from the staging database tables into the reporting database tables.

  The company is satisfied with the availability of the data in the reporting database tables, but the reporting tables are not pruning effectively. Currently, a size 4X-Large virtual warehouse is being used to query all of the tables in the reporting database.

  What step can be taken to improve the pruning of the reporting tables?

  A.

  Eliminate the use of Snowpipe and load the files into internal stages using PUT commands.

  B.

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

  C.

  Use an ORDER BY command to load the reporting tables.

  D.

  Create larger files for Snowpipe to ingest and ensure the staging frequency does not exceed 1 minute.

  View Answer Full Access

  Answer:

  C

  Explanation:

  Explanation:

  Effective pruning in Snowflake relies on the organization of data within micro-partitions. By using an ORDER BY clause with clustering keys when loading data into the reporting tables, Snowflake can better organize the data within micro-partitions. This organization allows Snowflake to skip over irrelevant micro-partitions during a query, thus improving query performance and reducing the amount of data scanned12.

  References =

  •Snowflake Documentation on micro-partitions and data clustering2

  •Community article on recognizing unsatisfactory pruning and improving it1

  Question # 22

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

  A.

  SYSTEMSCLUSTERING

  B.

  SYSTEMSTABLE_CLUSTERING

  C.

  SYSTEMSCLUSTERING_DEPTH

  D.

  SYSTEMSCLUSTERING_RATIO

  E.

  SYSTEMSCLUSTERING_INFORMATION

  View Answer Full Access

  Answer:

  C, E

  Explanation:

  Explanation:

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

  • SYSTEM$CLUSTERING_INFORMATION
  • SYSTEM$CLUSTERING_DEPTH

  Comprehensive But Short Explanation:

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

  References:

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

  Question # 23

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

  A.

  ACCOUNTADMIN, SECURITYADMIN

  B.

  SYSADMIN, SECURITYADMIN

  C.

  ACCOUNTADMIN, USER with PRIVILEGE

  D.

  SECURITYADMIN, USER with PRIVILEGE

  View Answer Full Access

  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 # 24

  Which command will create a schema without Fail-safe and will restrict object owners from passing on access to other users?

  A.

  create schema EDW.ACCOUNTING WITH MANAGED ACCESS;

  B.

  create schema EDW.ACCOUNTING WITH MANAGED ACCESS DATA_RETENTION_TIME_IN_DAYS - 7;

  C.

  create TRANSIENT schema EDW.ACCOUNTING WITH MANAGED ACCESS DATA_RETENTION_TIME_IN_DAYS = 1;

  D.

  create TRANSIENT schema EDW.ACCOUNTING WITH MANAGED ACCESS DATA_RETENTION_TIME_IN_DAYS = 7;

  View Answer Full Access

  Answer:

  D

  Explanation:

  Explanation:

  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

  Question # 25

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

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

  A.

  TRANSIENT

  B.

  TEMPORARY

  C.

  PERMANENT

  View Answer Full Access

  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

  Question # 26

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

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

  A.

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

  B.

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

  C.

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

  D.

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

  E.

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

  View Answer Full Access

  Answer:

  A, B

  Explanation:

  Explanation:

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

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

  References:

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

  Question # 27

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

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

  A.

  1. Create a share.

  2. Add objects to the share.

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

  B.

  1. Create a share.

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

  3. Add the reader account to the share.

  C.

  1. Create a new role called db_share.

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

  3. Create a user for the vendor.

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

  D.

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

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

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

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

  View Answer Full Access

  Answer:

  A

  Explanation:

  Explanation:

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

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

  Question # 28

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

  A.

  Extended Time Travel (up to 90 days)

  B.

  Customer-managed encryption keys through Tri-Secret Secure

  C.

  Periodic rekeying of encrypted data

  D.

  AWS, Azure, or Google Cloud private connectivity to Snowflake

  E.

  Federated authentication and SSO

  View Answer Full Access

  Answer:

  B, D

  Explanation:

  Explanation:

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

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

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

  Question # 29

  A company has a Snowflake environment running in AWS us-west-2 (Oregon). The company needs to share data privately with a customer who is running their Snowflake environment in Azure East US 2 (Virginia).

  What is the recommended sequence of operations that must be followed to meet this requirement?

  A.

  1. Create a share and add the database privileges to the share

  2. Create a new listing on the Snowflake Marketplace

  3. Alter the listing and add the share

  4. Instruct the customer to subscribe to the listing on the Snowflake Marketplace

  B.

  1. Ask the customer to create a new Snowflake account in Azure EAST US 2 (Virginia)

  2. Create a share and add the database privileges to the share

  3. Alter the share and add the customer's Snowflake account to the share

  C.

  1. Create a new Snowflake account in Azure East US 2 (Virginia)

  2. Set up replication between AWS us-west-2 (Oregon) and Azure East US 2 (Virginia) for the database objects to be shared

  3. Create a share and add the database privileges to the share

  4. Alter the share and add the customer's Snowflake account to the share

  D.

  1. Create a reader account in Azure East US 2 (Virginia)

  2. Create a share and add the database privileges to the share

  3. Add the reader account to the share

  4. Share the reader account's URL and credentials with the customer

  View Answer Full Access

  Answer:

  C

  Explanation:

  Explanation:

   Option C is the correct answer because it allows the company to share data privately with the customer across different cloud platforms and regions. The company can create a new Snowflake account in Azure East US 2 (Virginia) and set up replication between AWS us-west-2 (Oregon) and Azure East US 2 (Virginia) for the database objects to be shared. This way, the company can ensure that the data is always up to date and consistent in both accounts. The company can then create a share and add the database privileges to the share, and alter the share and add the customer’s Snowflake account to the share. The customer can then access the shared data from their own Snowflake account in Azure East US 2 (Virginia).

  Option A is incorrect because the Snowflake Marketplace is not a private way of sharing data. The Snowflake Marketplace is a public data exchange platform that allows anyone to browse and subscribe to data sets from various providers. The company would not be able to control who can access their data if they use the Snowflake Marketplace.

  Option B is incorrect because it requires the customer to create a new Snowflake account in Azure East US 2 (Virginia), which may not be feasible or desirable for the customer. The customer may already have an existing Snowflake account in a different cloud platform or region, and may not want to incur additional costs or complexity by creating a new account.

  Option D is incorrect because it involves creating a reader account in Azure East US 2 (Virginia), which is a limited and temporary way of sharing data. A reader account is a special type of Snowflake account that can only access data from a single share, and has a fixed duration of 30 days. The company would have to manage the reader account’s URL and credentials, and renew the account every 30 days. The customer would not be able to use their own Snowflake account to access the shared data, and would have to rely on the company’s reader account.

  References:

  • Snowflake Replication
  • Secure Data Sharing Overview
  • Snowflake Marketplace Overview
  • Reader Account Overview

  Question # 30

  A Snowflake Architect is designing a multi-tenant application strategy for an organization in the Snowflake Data Cloud and is considering using an Account Per Tenant strategy.

  Which requirements will be addressed with this approach? (Choose two.)

  A.

  There needs to be fewer objects per tenant.

  B.

  Security and Role-Based Access Control (RBAC) policies must be simple to configure.

  C.

  Compute costs must be optimized.

  D.

  Tenant data shape may be unique per tenant.

  E.

  Storage costs must be optimized.

  View Answer Full Access

  Answer:

  B, D

  Explanation:

  Explanation:

  The Account Per Tenant strategy involves creating separate Snowflake accounts for each tenant within the multi-tenant application. This approach offers a number of advantages.

  Option B: With separate accounts, each tenant's environment is isolated, making security and RBAC policies simpler to configure and maintain. This is because each account can have its own set of roles and privileges without the risk of cross-tenant access or the complexity of maintaining a highly granular permission model within a shared environment.

  Option D: This approach also allows for each tenant to have a unique data shape, meaning that the database schema can be tailored to the specific needs of each tenant without affecting others. This can be essential when tenants have different data models, usage patterns, or application customizations.

  Question # 31

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

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

  A.

  Insert

  B.

  Create

  C.

  Delete

  D.

  Truncate

  View Answer Full Access

  Answer:

  B

  Explanation:

  Explanation:

  In Snowflake, when a user activates a secondary role during a session, certain privileges associated with DDL (Data Definition Language) operations are restricted. The CREATE statement, which falls under DDL operations, cannot be executed using a secondary role. This limitation is designed to enforce role-based access control and ensure that schema modifications are managed carefully, typically reserved for primary roles that have explicit permissions to modify database structures.References: Snowflake's security and access control documentation specifying the limitations and capabilities of primary versus secondary roles in session management.

  Question # 32

  A retail company has over 3000 stores all using the same Point of Sale (POS) system. The company wants to deliver near real-time sales results to category managers. The stores operate in a variety of time zones and exhibit a dynamic range of transactions each minute, with some stores having higher sales volumes than others.

  Sales results are provided in a uniform fashion using data engineered fields that will be calculated in a complex data pipeline. Calculations include exceptions, aggregations, and scoring using external functions interfaced to scoring algorithms. The source data for aggregations has over 100M rows.

  Every minute, the POS sends all sales transactions files to a cloud storage location with a naming convention that includes store numbers and timestamps to identify the set of transactions contained in the files. The files are typically less than 10MB in size.

  How can the near real-time results be provided to the category managers? (Select TWO).

  A.

  All files should be concatenated before ingestion into Snowflake to avoid micro-ingestion.

  B.

  A Snowpipe should be created and configured with AUTO_INGEST = true. A stream should be created to process INSERTS into a single target table using the stream metadata to inform the store number and timestamps.

  C.

  A stream should be created to accumulate the near real-time data and a task should be created that runs at a frequency that matches the real-time analytics needs.

  D.

  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.

  E.

  The copy into command with a task scheduled to run every second should be used to achieve the near-real time requirement.

  View Answer Full Access

  Answer:

  B, C

  Explanation:

  Explanation:

  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]

  Question # 33

  At which object type level can the APPLY MASKING POLICY, APPLY ROW ACCESS POLICY and APPLY SESSION POLICY privileges be granted?

  A.

  Global

  B.

  Database

  C.

  Schema

  D.

  Table

  View Answer Full Access

  Answer:

  A

  Explanation:

  Explanation:

  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

  Question # 34

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

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

  A.

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

  B.

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

  C.

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

  D.

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

  View Answer Full Access

  Answer:

  B

  Explanation:

  Explanation:

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

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

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

  Question # 35

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

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

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

  A.

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

  B.

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

  C.

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

  D.

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

  View Answer Full Access

  Answer:

  D

  Explanation:

  Explanation:

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

  Question # 36

  What does a Snowflake Architect need to consider when implementing a Snowflake Connector for Kafka?

  A.

  Every Kafka message is in JSON or Avro format.

  B.

  The default retention time for Kafka topics is 14 days.

  C.

  The Kafka connector supports key pair authentication, OAUTH. and basic authentication (for example, username and password).

  D.

  The Kafka connector will create one table and one pipe to ingest data for each topic. If the connector cannot create the table or the pipe it will result in an exception.

  View Answer Full Access

  Answer:

  D

  Explanation:

  Explanation:

  The Snowflake Connector for Kafka is a Kafka Connect sink connector that reads data from one or more Apache Kafka topics and loads the data into a Snowflake table. The connector supports different authentication methods to connect to Snowflake, such as key pair authentication, OAUTH, and basic authentication (for example, username and password). The connector also supports different encryption methods, such as HTTPS and SSL1. The connector does not require that every Kafka message is in JSON or Avro format, as it can handle other formats such as CSV, XML, and Parquet2. The default retention time for Kafka topics is not relevant for the connector, as it only consumes the messages that are available in the topics and does not store them in Kafka. The connector will create one table and one pipe to ingest data for each topic by default, but this behavior can be customized by using the snowflake.topic2table.map configuration property3. If the connector cannot create the table or the pipe, it will log an error and retry the operation until it succeeds or the connector is stopped4. References:

  • Installing and Configuring the Kafka Connector
  • Overview of the Kafka Connector
  • Managing the Kafka Connector
  • Troubleshooting the Kafka Connector

  Question # 37

  An Architect runs the following SQL query:

  

  How can this query be interpreted?

  A.

  FILEROWS is a stage. FILE_ROW_NUMBER is line number in file.

  B.

  FILEROWS is the table. FILE_ROW_NUMBER is the line number in the table.

  C.

  FILEROWS is a file. FILE_ROW_NUMBER is the file format location.

  D.

  FILERONS is the file format location. FILE_ROW_NUMBER is a stage.

  View Answer Full Access

  Answer:

  A

  Explanation:

  Explanation:

  • A stage is a named location in Snowflake that can store files for data loading and unloading. A stage can be internal or external, depending on where the files are stored.
  • The query in the question uses the LIST function to list the files in a stage named FILEROWS. The function returns a table with various columns, including FILE_ROW_NUMBER, which is the line number of the file in the stage.
  • Therefore, the query can be interpreted as listing the files in a stage named FILEROWS and showing the line number of each file in the stage.

  References:

  • : Stages
  • : LIST Function

  Question # 38

  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)

  

  A.

  Option A

  B.

  Option B

  C.

  Option C

  D.

  Option D

  View Answer Full Access

  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 # 39

  What is the MOST efficient way to design an environment where data retention is not considered critical, and customization needs are to be kept to a minimum?

  A.

  Use a transient database.

  B.

  Use a transient schema.

  C.

  Use a transient table.

  D.

  Use a temporary table.

  View Answer Full Access

  Answer:

  A

  Explanation:

  Explanation:

  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.

  Question # 40

  The following DDL command was used to create a task based on a stream:

  

  Assuming MY_WH is set to auto_suspend – 60 and used exclusively for this task, which statement is true?

  A.

  The warehouse MY_WH will be made active every five minutes to check the stream.

  B.

  The warehouse MY_WH will only be active when there are results in the stream.

  C.

  The warehouse MY_WH will never suspend.

  D.

  The warehouse MY_WH will automatically resize to accommodate the size of the stream.

  View Answer Full Access

  Answer:

  B

  Explanation:

  Explanation:

   The warehouse MY_WH will only be active when there are results in the stream. This is because the task is created based on a stream, which means that the task will only be executed when there are new data in the stream. Additionally, the warehouse is set to auto_suspend - 60, which means that the warehouse will automatically suspend after 60 seconds of inactivity. Therefore, the warehouse will only be active when there are results in the stream. References:

  • [CREATE TASK | Snowflake Documentation]
  • [Using Streams and Tasks | Snowflake Documentation]
  • [CREATE WAREHOUSE | Snowflake Documentation]

  Question # 41

  A company has a Snowflake account named ACCOUNTA in AWS us-east-1 region. The company stores its marketing data in a Snowflake database named MARKET_DB. One of the company’s business partners has an account named PARTNERB in Azure East US 2 region. For marketing purposes the company has agreed to share the database MARKET_DB with the partner account.

  Which of the following steps MUST be performed for the account PARTNERB to consume data from the MARKET_DB database?

  A.

  Create a new account (called AZABC123) in Azure East US 2 region. From account ACCOUNTA create a share of database MARKET_DB, create a new database out of this share locally in AWS us-east-1 region, and replicate this new database to AZABC123 account. Then set up data sharing to the PARTNERB account.

  B.

  From account ACCOUNTA create a share of database MARKET_DB, and create a new database out of this share locally in AWS us-east-1 region. Then make this database the provider and share it with the PARTNERB account.

  C.

  Create a new account (called AZABC123) in Azure East US 2 region. From account ACCOUNTA replicate the database MARKET_DB to AZABC123 and from this account set up the data sharing to the PARTNERB account.

  D.

  Create a share of database MARKET_DB, and create a new database out of this share locally in AWS us-east-1 region. Then replicate this database to the partner’s account PARTNERB.

  View Answer Full Access

  Answer:

  C

  Explanation:

  Explanation:

  • Snowflake supports data sharing across regions and cloud platforms using account replication and share replication features. Account replication enables the replication of objects from a source account to one or more target accounts in the same organization. Share replication enables the replication of shares from a source account to one or more target accounts in the same organization1.
  • To share data from the MARKET_DB database in the ACCOUNTA account in AWS us-east-1 region with the PARTNERB account in Azure East US 2 region, the following steps must be performed:
  • Therefore, option C is the correct answer.

  References: : Replicating Shares Across Regions and Cloud Platforms : Working with Organizations and Accounts : Replicating Databases Across Multiple Accounts : Replicating Shares Across Multiple Accounts

  Question # 42

  How can an Architect enable optimal clustering to enhance performance for different access paths on a given table?

  A.

  Create multiple clustering keys for a table.

  B.

  Create multiple materialized views with different cluster keys.

  C.

  Create super projections that will automatically create clustering.

  D.

  Create a clustering key that contains all columns used in the access paths.

  View Answer Full Access

  Answer:

  B

  Explanation:

  Explanation:

  According to the SnowPro Advanced: Architect documents and learning resources, the best way to enable optimal clustering to enhance performance for different access paths on a given table is to create multiple materialized views with different cluster keys. A materialized view is a pre-computed result set that is derived from a query on one or more base tables. A materialized view can be clustered by specifying a clustering key, which is a subset of columns or expressions that determines how the data in the materialized view is co-located in micro-partitions. By creating multiple materialized views with different cluster keys, an Architect can optimize the performance of queries that use different access paths on the same base table. For example, if a base table has columns A, B, C, and D, and there are queries that filter on A and B, or on C and D, or on A and C, the Architect can create three materialized views, each with a different cluster key: (A, B), (C, D), and (A, C). This way, each query can leverage the optimal clustering of the corresponding materialized view and achieve faster scan efficiency and better compression.

  References:

  • Snowflake Documentation: Materialized Views
  • Snowflake Learning: Materialized Views

  https://www.snowflake.com/blog/using-materialized-views-to-solve-multi-clustering-performance-problems/

  Question # 43

  A company is trying to Ingest 10 TB of CSV data into a Snowflake table using Snowpipe as part of Its migration from a legacy database platform. The records need to be ingested in the MOST performant and cost-effective way.

  How can these requirements be met?

  A.

  Use ON_ERROR = continue in the copy into command.

  B.

  Use purge = TRUE in the copy into command.

  C.

  Use FURGE = FALSE in the copy into command.

  D.

  Use on error = SKIP_FILE in the copy into command.

  View Answer Full Access

  Answer:

  D

  Explanation:

  Explanation:

  For ingesting a large volume of CSV data into Snowflake using Snowpipe, especially for a substantial amount like 10 TB, the on error = SKIP_FILE option in the COPY INTO command can be highly effective. This approach allows Snowpipe to skip over files that cause errors during the ingestion process, thereby not halting or significantly slowing down the overall data load. It helps in maintaining performance and cost-effectiveness by avoiding the reprocessing of problematic files and continuing with the ingestion of other data.

  Question # 44

  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?

  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.

  View Answer Full Access

  Answer:

  D

  Explanation:

  Explanation:

  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.

  Question # 45

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

  How should the database be replicated?

  A.

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

  B.

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

  C.

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

  D.

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

  View Answer Full Access

  Answer:

  B

  Explanation:

  Explanation:

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

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

  Question # 46

  A company is designing its serving layer for data that is in cloud storage. Multiple terabytes of the data will be used for reporting. Some data does not have a clear use case but could be useful for experimental analysis. This experimentation data changes frequently and is sometimes wiped out and replaced completely in a few days.

  The company wants to centralize access control, provide a single point of connection for the end-users, and maintain data governance.

  What solution meets these requirements while MINIMIZING costs, administrative effort, and development overhead?

  A.

  Import the data used for reporting into a Snowflake schema with native tables. Then create external tables pointing to the cloud storage folders used for the experimentation data. Then create two different roles with grants to the different datasets to match the different user personas, and grant these roles to the corresponding users.

  B.

  Import all the data in cloud storage to be used for reporting into a Snowflake schema with native tables. Then create a role that has access to this schema and manage access to the data through that role.

  C.

  Import all the data in cloud storage to be used for reporting into a Snowflake schema with native tables. Then create two different roles with grants to the different datasets to match the different user personas, and grant these roles to the corresponding users.

  D.

  Import the data used for reporting into a Snowflake schema with native tables. Then create views that have SELECT commands pointing to the cloud storage files for the experimentation data. Then create two different roles to match the different user personas, and grant these roles to the corresponding users.

  View Answer Full Access

  Answer:

  A

  Explanation:

  Explanation:

  The most cost-effective and administratively efficient solution is to use a combination of native and external tables. Native tables for reporting data ensure performance and governance, while external tables allow for flexibility with frequently changing experimental data. Creating roles with specific grants to datasets aligns with the principle of least privilege, centralizing access control and simplifying user management12.

  References

  •Snowflake Documentation on Optimizing Cost1.

  •Snowflake Documentation on Controlling Cost2.

  Question # 47

  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.

  What could be causing this?

  A.

  There were JSON nulls in the recent data imports.

  B.

  The order of the keys in the JSON was changed.

  C.

  The recent data imports contained fewer fields than usual.

  D.

  There were variations in string lengths for the JSON values in the recent data imports.

  View Answer Full Access

  Answer:

  B

  Explanation:

  Explanation:

  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

  Quick Links

  • Home
  • All Vendors
  • Disclaimer

  Why Us

  • Contact us
  • About us
  • Guarantee

  Unlimited Packages

  • Unlimited Engine

  Site Secure

  

  TESTED 27 Jul 2024

  We Accept

  
  Copyright myexamcollection.com. All Right Reserved.