Snowflake DEA-C01 Online Practice Questions and Exam Preparation

Snowflake DEA-C01 Online Questions & Answers

• Question 61:

  Within a Snowflake account permissions have been defined with custom roles and role hierarchies.

  To set up column-level masking using a role in the hierarchy of the current user, what command would be used?

  A. CORRECT_ROLE
  B. IKVOKER_ROLE
  C. IS_RCLE_IN_SESSION
  D. IS_GRANTED_TO_INVOKER_ROLE
  C. IS_RCLE_IN_SESSION

  ---

  Explanation

  The IS_ROLE_IN_SESSION function is used to set up column-level masking using a role in the hierarchy of the current user. Column-level masking is a feature in Snowflake that allows users to apply dynamic data masking

  policies to specific columns based on the roles of the users who access them. The IS_ROLE_IN_SESSION function takes a role name as an argument and returns true if the role is in the current user's session, or false otherwise. The function

  can be used in a masking policy expression to determine whether to mask or unmask a column value based on the role of the user. For example:

  CREATE OR REPLACE MASKING POLICY email_mask AS (val string) RETURNS string - > CASE WHEN IS_ROLE_IN_SESSION(`HR') THEN val ELSE REGEXP_REPLACE(val, `(.).(.@.)', `\1****\2') END;

  In this example, the IS_ROLE_IN_SESSION function is used to create a masking policy for an email column. The masking policy returns the original email value if the user has the HR role in their session, or returns a masked email value with

  asterisks if not.

• Question 62:

  At what isolation level are Snowflake streams?

  A. Snapshot
  B. Repeatable read
  C. Read committed
  D. Read uncommitted
  B. Repeatable read

  ---

  Explanation

  The isolation level of Snowflake streams is repeatable read, which means that each transaction sees a consistent snapshot of data that does not change during its execution. Streams use time travel internally to provide this isolation level and ensure that queries on streams return consistent results regardless of concurrent transactions on their source tables.

• Question 63:

  While running an external function, me following error message is received:

  Error:function received the wrong number of rows

  What iscausing this to occur?

  A. External functions do not support multiple rows
  B. Nested arrays are not supported in the JSON response
  C. The JSON returned by the remote service is not constructed correctly
  D. The return message did not produce the same number of rows that it received
  D. The return message did not produce the same number of rows that it received

  ---

  Explanation

  The error message "function received the wrong number of rows" is caused by the return message not producing the same number of rows that it received. External functions require that the remote service returns exactly one row for each input row that it receives from Snowflake. If the remote service returns more or fewer rows than expected, Snowflake will raise an error and abort the function execution. The other options are not causes of this error message. Option A is incorrect because external functions do support multiple rows as long as they match the input rows. Option B is incorrect because nested arrays are supported in the JSON response as long as they conform to the return type definition of the external function. Option C is incorrect because the JSON returned by the remote service may be constructed correctly but still produce a different number of rows than expected.

• Question 64:

  A Data Engineer is building a pipeline to transform a 1 TD tab e by joining it with supplemental tables The Engineer is applying filters and several aggregations leveraging Common TableExpressions (CTEs) using a size Medium virtual warehouse in a single query in Snowflake.

  After checking the Query Profile, what is the recommended approach to MAXIMIZE performance of this query if the Profile shows data spillage?

  A. Enable clustering on the table
  B. Increase the warehouse size
  C. Rewrite the query to remove the CTEs.
  D. Switch to a multi-cluster virtual warehouse
  B. Increase the warehouse size

  ---

  Explanation

  The recommended approach to maximize performance of this query if the Profile shows data spillage is to increase the warehouse size. Data spillage occurs when the query requires more memory than the warehouse can provide and has to spill some intermediate results to disk. This can degrade the query performance by increasing the disk IO time. Increasing the warehouse size can increase the amount of memory available for the query and reduce or eliminate data spillage.

• Question 65:

  A company is building a dashboard for thousands of Analysts. The dashboard presents the results of a few summary queries on tables that are regularly updated. The query conditions vary by tope according to what data each Analyst needs Responsiveness of the dashboard queries is a top priority, and the data cache should be preserved.

  How should the Data Engineer configure the compute resources to support this dashboard?

  A. Assign queries to a multi-cluster virtual warehouse with economy auto-scaling Allow the system to automatically start and stop clusters according to demand.
  B. Assign all queries to a multi-cluster virtual warehouse set to maximized mode Monitor to determine the smallest suitable number of clusters.
  C. Create a virtual warehouse for every 250 Analysts Monitor to determine how many of these virtual warehouses are being utilized at capacity.
  D. Create a size XL virtual warehouse to support all the dashboard queries Monitor query runtimes to determine whether the virtual warehouse should be resized.
  B. Assign all queries to a multi-cluster virtual warehouse set to maximized mode Monitor to determine the smallest suitable number of clusters.

  ---

  Explanation

  This option is the best way to configure the compute resources to support this dashboard. By assigning all queries to a multi-cluster virtual warehouse set to maximized mode, the Data Engineer can ensure that there is enough compute capacity to handle thousands of concurrent queries from different analysts. A multi-cluster virtual warehouse can scale up or down by adding or removing clusters based on the load. A maximized scaling policy ensures that there is always at least one cluster running and that new clusters are added as soon as possible whenneeded. By monitoring the utilization and performance of the virtual warehouse, the Data Engineer can determine the smallest suitable number of clusters that can meet the responsiveness requirement and minimize costs.