Snowflake DEA-C01 Online Practice Questions and Exam Preparation

Snowflake DEA-C01 Online Questions & Answers

• Question 1:

  Which use case would be BEST suited for the search optimization service?

  A. Analysts who need to perform aggregates over high cardinality columns
  B. Business users who need fast response times using highly selective filters
  C. Data Scientists who seek specific JOIN statements with large volumes of data
  D. Data Engineers who create clustered tables with frequent reads against clustering keys
  B. Business users who need fast response times using highly selective filters

  ---

  Explanation

  The use case that would be best suited for the search optimization service is business users who need fast response times using highly selective filters. The search optimization service is a feature that enables faster queries on tables with high cardinality columns by creating inverted indexes on those columns. High cardinality columns are columns that have a large number of distinct values, such as customer IDs, product SKUs, or email addresses. Queries that use highly selective filters on high cardinality columns can benefit from the search optimization service because they can quickly locate the relevant rows without scanning the entire table. The other options are not best suited for the search optimization service. Option A is incorrect because analysts who need to perform aggregates over high cardinality columns will not benefit from the search optimization service, as they will still need to scan all the rows that match the filter criteria. Option C is incorrect because data scientists who seek specific JOIN statements with large volumes of data will not benefit from the search optimization service, as they will still need to perform join operations that may involve shuffling or sorting data across nodes. Option D is incorrect because data engineers who create clustered tables with frequent reads against clustering keys will not benefit from the search optimization service, as they already have an efficient way to organize and access data based on clustering keys.

• Question 2:

  When would a Data engineer use table with the flatten function instead of the lateral flatten combination?

  A. When TABLE with FLATTENrequires another source in the from clause to refer to
  B. WhenTABLE with FLATTENrequires no additional source m the from clause to refer to
  C. Whenthe LATERALFLATTENcombination requires no other source m the from clause to refer to
  D. When table withFLATTENis acting like a sub-query executed for each returned row
  A. When TABLE with FLATTENrequires another source in the from clause to refer to

  ---

  Explanation

  The TABLE function with the FLATTEN function is used to flatten semi- structured data, such as JSON or XML, into a relational format. The TABLE function returns a table expression that can be used in the FROM clause of a

  query. The TABLE function with the FLATTEN function requires another source in the FROM clause to refer to, such as a table, view, or subquery that contains the semi-structured data. For example:

  SELECT t.value:city::string AS city, f.value AS population FROM cities t, TABLE(FLATTEN(input => t.value:population)) f;

  In this example, the TABLE function with the FLATTEN function refers to the cities table in the FROM clause, which contains JSON data in a variant column named value. The FLATTEN function flattens the population array within each JSON

  object and returns a table expression with two columns: key and value. The query then selects the city and population values from the table expression.

• Question 3:

  A company has an extensive script in Scala that transforms data by leveraging DataFrames. A Data engineer needs to move these transformations to Snowpark.

  ...characteristics of data transformations in Snowpark should be considered to meet this requirement? (Select TWO)

  A. It is possible to join multiple tables using DataFrames.
  B. Snowpark operations are executed lazily on the server.
  C. User-Defined Functions (UDFs) are not pushed down to Snowflake
  D. Snowpark requires a separate cluster outside of Snowflake for computations
  E. Columns in different DataFrames with the same name should be referred to with squared brackets
  A. It is possible to join multiple tables using DataFrames.
  B. Snowpark operations are executed lazily on the server.

  ---

  Explanation

  The characteristics of data transformations in Snowpark that should be considered to meet this requirement are:

  It is possible to join multiple tables using DataFrames. Snowpark operations are executed lazily on the server. These characteristics indicate how Snowpark can perform data transformations using DataFrames, which are similar to the ones

  used in Scala. DataFrames are distributed collections of rows that can be manipulated using various operations, such as joins, filters, aggregations, etc. DataFrames can be created from different sources, such as tables, files, or SQL queries.

  Snowpark operations are executed lazily on the server, which means that they are not performed until an action is triggered, such as a write or a collect operation. This allows Snowpark to optimize the execution plan and reduce the amount of

  data transferred between the client and the server.

  The other options are not characteristics of data transformations in Snowpark that should be considered to meet this requirement. Option C is incorrect because User-Defined Functions (UDFs) are pushed down to Snowflake and executed on

  the server. Option D is incorrect because Snowpark does not require a separate cluster outside of Snowflake for computations, but rather uses virtual warehouses within Snowflake. Option E is incorrect because columns in different

  DataFrames with the same name should be referred to with dot notation, not squared brackets.

• Question 4:

  A Data Engineer is writing a Python script using the Snowflake Connector for Python. The Engineer will use the snowflake. Connector.connect function to connect to Snowflake The requirementsare:

  *Raise an exception if the specified database schema or warehouse does not exist

  *improve download performance

  Whichparameters of the connect function should be used? (Select TWO).

  A. authenticator
  B. arrow_nunber_to_decimal
  C. client_prefetch_threads
  D. client_session_keep_alivs
  E. validate_default_parameters
  C. client_prefetch_threads
  E. validate_default_parameters

  ---

  Explanation

  The parameters of the connect function that should be used are client_prefetch_threads and validate_default_parameters. The client_prefetch_threads parameter controls the number of threads used to download query results from Snowflake. Increasing this parameter can improve download performance by parallelizing the download process. The validate_default_parameters parameter controls whether an exception should be raised if the specified database, schema, or warehouse does not exist or is not authorized. Setting this parameter to True can help catch errors early and avoid unexpected results.

• Question 5:

  What is a characteristic of the use of binding variables in JavaScript stored procedures in Snowflake?

  A. All types of JavaScript variables can be bound
  B. All Snowflake first-class objects can be bound
  C. Only JavaScript variables of type number, string and sf Date can be bound
  D. Users are restricted from binding JavaScript variables because they create SQL injection attack vulnerabilities
  C. Only JavaScript variables of type number, string and sf Date can be bound

  ---

  Explanation

  A characteristic of the use of binding variables in JavaScript stored procedures in Snowflake is that only JavaScript variables of type number, string and sf Date can be bound. Binding variables are a way to pass values from JavaScript variables to SQL statements within a stored procedure. Binding variables can improve the security and performance of the stored procedure by preventing SQL injection attacks and reducing the parsing overhead. However, not all types of JavaScript variables can be bound. Only the primitive types number and string, and the Snowflake-specific type sf Date, can be bound. The other options are incorrect because they do not describe a characteristic of the use of binding variables in JavaScript stored procedures in Snowflake. Option A is incorrect because authenticator is not a type of JavaScript variable, but a parameter of the snowflake.connector.connect function. Option B is incorrect because arrow_number_to_decimal is not a type of JavaScript variable, but a parameter of the snowflake.connector.connect function. Option D is incorrect because users are not restricted from binding JavaScript variables, but encouraged to do so.

• Question 6:

  The JSON below is stored in a variant column named v in a table named jCustRaw:

  

  Which query will return one row per team member (stored in the teamMembers array) along all of the attributes of each team member?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  B. Option B

  ---

  Explanation

• Question 7:

  Which query will show a list of the 20 most recent executions of a specified task kttask, that have been scheduled within the last hour that have ended or are stillrunning's?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  B. Option B

  ---

  Explanation

• Question 8:

  A Data Engineer enables a result cache at the session level with the following command:

  ALTER SESSION SET USE CACHED RESULT = TRUE;

  The Engineer then runs the following select query twice without delay:

  

  The underlying table does not change between executions

  What are the results of both runs?

  A. The first and second run returned the same results because sample is deterministic
  B. The first and second run returned the same results, because the specific SEEDvalue was provided.
  C. The first and second run returned different results because the query is evaluated each time it is run.
  D. The first and second run returned differentresults because the query uses *instead of an explicit column list
  B. The first and second run returned the same results, because the specific SEEDvalue was provided.

  ---

  Explanation

  The result cache is enabled at the session level, which means that repeated queries will return cached results if there is no change in the underlying data or session parameters. However, in this case, the result cache is not relevant because the query uses a specific SEED value for sampling, which makes it deterministic. Therefore, both runs will return the same results regardless of caching.

• Question 9:

  A Data Engineer is trying to load the following rows from a CSV file into a table in Snowflake with the following structure:

  

  ....engineer is using the following COPY INTO statement:

  

  However, the following error is received.

  

  Which file format option should be used to resolve the error and successfully load all the data into the table?

  A. ESCandPE_UNENGLO9ED_FIELD = '\\'
  B. ERROR_ON_COLUMN_COUKT_MISMATCH = FALSE
  C. FIELD_DELIMITER = ","
  D. FIELD OPTIONALLY ENCLOSED BY = " "
  D. FIELD OPTIONALLY ENCLOSED BY = " "

  ---

  Explanation

  The file format option that should be used to resolve the error and successfully load all the data into the table is FIELD_OPTIONALLY_ENCLOSED_BY = `"'. This option specifies that fields in the file may be enclosed by double quotes, which allows for fields that contain commas or newlines within them. For example, in row 3 of the file, there is a field that contains a comma within double quotes: "Smith Jr., John". Without specifying this option, Snowflake will treat this field as two separate fields and cause an error due to column count mismatch. By specifying this option, Snowflake will treat this field as one field and load it correctly into the table.

• Question 10:

  A Data Engineer executes a complex query and wants to make use of Snowflake s query results caching capabilities to reuse the results.

  Which conditions must be met? (Select THREE).

  A. The results must be reused within 72 hours.
  B. The query must be executed using the same virtual warehouse.
  C. The USED_CACHED_RESULT parameter must be included in the query.
  D. The table structure contributing to the query result cannot have changed
  E. The new query must have the same syntax as the previously executed query.
  F. The micro-partitions cannot have changed due to changes to other data in the table
  A. The results must be reused within 72 hours.
  D. The table structure contributing to the query result cannot have changed
  E. The new query must have the same syntax as the previously executed query.

  ---

  Explanation

  Snowflake's query results caching capabilities allow users to reuse the results of previously executed queries without re-executing them. For this to happen, the following conditions must be met:

  The results must be reused within 24 hours (not 72 hours), which is the default time-to-live (TTL) for cached results.

  The query must be executed using any virtual warehouse (not necessarily the same one), as long as it is in the same region and account as the original query. The USED_CACHED_RESULT parameter does not need to be included in the

  query, as it is enabled by default at the account level. However, it can be disabled or overridden at the session or statement level. The table structure contributing to the query result cannot have changed, such as adding or dropping columns,

  changing data types, or altering constraints. The new query must have the same syntax as the previously executed query, including whitespace and case sensitivity.

  The micro-partitions cannot have changed due to changes to other data in the table, such as inserting, updating, deleting, or merging rows.