Snowflake ARA-C01 Online Practice Questions and Exam Preparation

Snowflake ARA-C01 Online Questions & Answers

• Question 61:

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

  ---

  Explanation/Reference:

  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 62:

  A user has the appropriate privilege to see unmasked data in a column.

  If the user loads this column data into another column that does not have a masking policy, what will occur?

  A. Unmasked data will be loaded in the new column.
  B. Masked data will be loaded into the new column.
  C. Unmasked data will be loaded into the new column but only users with the appropriate privileges will be able to see the unmasked data.
  D. Unmasked data will be loaded into the new column and no users will be able to see the unmasked data.
  A. Unmasked data will be loaded in the new column.

  ---

  Explanation/Reference:

  According to the SnowPro Advanced: Architect documents and learning resources, column masking policies are applied at query time based on the privileges of the user who runs the query. Therefore, if a user has the privilege to see unmasked data in a column, they will see the original data when they query that column. If they load this column data into another column that does not have amasking policy, the unmasked data will be loaded in the new column, and any user who can query the new column will see the unmasked data as well. The masking policy does not affect the underlying data in the column, only the query results. References: Snowflake Documentation: Column Masking Snowflake Learning: Column Masking

• Question 63:

  A company has several sites in different regions from which the company wants to ingest data.

  Which of the following will enable this type of data ingestion?

  A. The company must have a Snowflake account in each cloud region to be able to ingest data to that account.
  B. The company must replicate data between Snowflake accounts.
  C. The company should provision a reader account to each site and ingest the data through the reader accounts.
  D. The company should use a storage integration for the external stage.
  D. The company should use a storage integration for the external stage.

  ---

  Explanation/Reference:

  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

• Question 64:

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

  How can this data be shared?

  A. The healthcare company will need to change the institute's Snowflake edition in the accounts panel.
  B. By default, sharing is supported from a Business Critical Snowflake edition to a Standard edition.
  C. Contact Snowflake and they will execute the share request for the healthcare company.
  D. Set the share_restriction parameter on the shared object to false.
  D. Set the share_restriction parameter on the shared object to false.

  ---

  Explanation/Reference:

  By default, Snowflake does not allow sharing data from a Business Critical edition to a non-Business Critical edition. This is because Business Critical edition provides enhanced security and data protection features that are not available in lower editions. However, this restriction can be overridden by setting the share_restriction parameter on the shared object (database, schema, or table) to false. This parameter allows the data provider to explicitly allow sharing data with lower edition accounts. Note that this parameter can only be set by the data provider, not the data consumer. Also, setting this parameter to false may reduce the level of security and data protection for the shared data. References: Enable Data Share:Business Critical Account to Lower Edition Sharing Is Not Allowed From An Account on BUSINESS CRITICAL Edition to an Account On A Lower Edition SQL Execution Error: Sharing is Not Allowed from an Account on BUSINESS CRITICAL Edition to an Account on a Lower Edition Snowflake Editions | Snowflake Documentation

• Question 65:

  A table contains five columns and it has millions of records. The cardinality distribution of the columns is shown below:

  

  Column C4 and C5 are mostly used by SELECT queries in the GROUP BY and ORDER BY clauses. Whereas columns C1, C2 and C3 are heavily used in filter and join conditions of SELECT queries.

  The Architect must design a clustering key for this table to improve the query performance.

  Based on Snowflake recommendations, how should the clustering key columns be ordered while defining the multi-column clustering key?

  A. C5, C4, C2
  B. C3, C4, C5
  C. C1, C3, C2
  D. C2, C1, C3
  C. C1, C3, C2

  ---

  Explanation/Reference:

  According to the Snowflake documentation, the following are some considerations for choosing clustering for a table1:

  Clustering is optimal when either:

  Clustering is most effective when the clustering key is used in the following types of query predicates:

  Clustering is less effective when the clustering key is not used in any of the above query predicates, or when the clustering key is used in a predicate that requires a function or expression to be applied to the key (e.g. DATE_TRUNC,

  TO_CHAR, etc.).

  For most tables, Snowflake recommends a maximum of 3 or 4 columns (or expressions) per key. Adding more than 3-4 columns tends to increase costs more than benefits.

  Based on these considerations, the best option for the clustering key columns is C. C1, C3, C2, because:

  These columns are heavily used in filter and join conditions of SELECT queries, which are the most effective types of predicates for clustering. These columns have high cardinality, which means they have many distinct values and can help

  reduce the clustering skew and improve the compression ratio. These columns are likely to be correlated with each other, which means they can help co-locate similar rows in the same micro-partitions and improve the scan efficiency.

  These columns do not require any functions or expressions to be applied to them, which means they can be directly used in the predicates without affecting the clustering.

  References: 1: Considerations for Choosing Clustering for a Table | Snowflake Documentation