Snowflake DEA-C01 Online Practice Questions and Exam Preparation

Snowflake DEA-C01 Online Questions & Answers

• Question 51:

  Which functions will compute a 'fingerprint' over an entire table, query result, or window to quickly detect changes to table contents or query results? (Select TWO).

  A. HASH (*)
  B. HASH_AGG(*)
  C. HASH_AGG(, )
  D. HASH_AGG_COMPARE (*)
  E. HASH COMPARE(*)
  B. HASH_AGG(*)
  C. HASH_AGG(, )

  ---

  Explanation

  The functions that will compute a `fingerprint' over an entire table, query result, or window to quickly detect changes to table contents or query results are:

  HASH_AGG(*): This function computes a hash value over all columns and rows in a table, query result, or window. The function returns a single value for each group defined by a GROUP BY clause, or a single value for the entire input if no

  GROUP BY clause is specified.

  HASH_AGG(, ): This function computes a hash value over two expressions in a table, query result, or window. The function returns a single value for each group defined by a GROUP BY clause, or a single value for the entire

  input if no GROUP BY clause is specified. The other functions are not correct because:

  HASH (*): This function computes a hash value over all columns in a single row. The function returns one value per row, not one value per table, query result, or window.

  HASH_AGG_COMPARE (): This function compares two hash values computed by HASH_AGG() over two tables or query results and returns true if they are equal or false if they are different. The function does not compute a hash value itself,

  but rather compares two existing hash values.

  HASH COMPARE(): This function compares two hash values computed by HASH() over two rows and returns true if they are equal or false if they are different. The function does not compute a hash value itself, but rather compares two

  existing hash values.

• Question 52:

  A table is loaded using Snowpipe and truncated afterwards Later, a Data Engineer finds that the table needs to be reloaded but the metadata of the pipe will not allow the same files to be loaded again.

  How can this issue be solved using the LEAST amount of operational overhead?

  A. Wait until the metadata expires and then reload the file using Snowpipe
  B. Modify the file by adding a blank row to the bottom and re-stage the file
  C. Set the FORCE=TRUE option in the Snowpipe COPY INTO command
  D. Recreate the pipe by using the create or replace pipe command
  C. Set the FORCE=TRUE option in the Snowpipe COPY INTO command

  ---

  Explanation

  The FORCE=TRUE option in the Snowpipe COPY INTO command allows Snowpipe to load files that have already been loaded before, regardless of the metadata. This is the easiest way to reload the same files without modifying them or recreating the pipe.

• Question 53:

  Which methods will trigger an action that will evaluate a DataFrame? (Select TWO)

  A. DataFrame.random_split ( )
  B. DataFrame.collect ()
  C. DateFrame.select ()
  D. DataFrame.col ( )
  E. DataFrame.show ()
  B. DataFrame.collect ()
  E. DataFrame.show ()

  ---

  Explanation

  The methods that will trigger an action that will evaluate a DataFrame are DataFrame.collect() and DataFrame.show(). These methods will force the execution of any pending transformations on the DataFrame and return or display the results. The other options are not methods that will evaluate a DataFrame. Option A, DataFrame.random_split(), is a method that will split a DataFrame into two or more DataFrames based on random weights. Option C, DataFrame.select(), is a method that will project a set of expressions on a DataFrame and return a new DataFrame. Option D, DataFrame.col(), is a method that will return a Column object based on a column name in a DataFrame.

• Question 54:

  The following code is executed ina Snowflake environment with the default settings:

  

  What will be the result of the select statement?

  A. SQL compilation error object CUSTOMER' does not exist or is not authorized.
  B. John
  C. 1
  D. 1John
  C. 1

  ---

  Explanation

• Question 55:

  The following is returned fromSYSTEMCLUSTERING_INFORMATION () for a tablenamed orders with adate column named O_ORDERDATE:

  

  What does the total_constant_partition_count value indicate about this table?

  A. The table is clustered very well on_ORDERDATE, as there are 493 micro-partitions that could not be significantly improved by reclustering
  B. The table is not clustered well on O_ORDERDATE, as there are 493 micro-partitions where the range of values in that column overlap with every other micro partition in the table.
  C. The data inO_ORDERDATEdoes not change very often as there are 493 micro- partitionscontaining rows where that column has not been modified since the row was created
  D. The data inO_ORDERDATEhas a very low cardinality as there are 493 micro-partitions where there is only a single distinct value in that column for all rows in the micro-partition
  B. The table is not clustered well on O_ORDERDATE, as there are 493 micro-partitions where the range of values in that column overlap with every other micro partition in the table.

  ---

  Explanation

  The total_constant_partition_count value indicates the number of micro- partitions where the clustering key column has a constant value across all rows in the micro-partition. However, this does not necessarily mean that the table is clustered well on that column, as there could be other micro-partitions where the range of values in that column overlap with each other. This is the case for the orders table, as the clustering depth is 1, which means that every micro-partition overlaps with every other micro-partition on O_ORDERDATE. This indicates that the table is not clustered well on O_ORDERDATE and could benefit from reclustering.

• Question 56:

  What is the purpose of the BUILD_FILE_URL function in Snowflake?

  A. It generates an encrypted URL foe accessing a file in a stage.
  B. It generates a staged URL for accessing a file in a stage.
  C. It generates a permanent URL for accessing files in a stage.
  D. It generates a temporary URL for accessing a file in a stage.
  B. It generates a staged URL for accessing a file in a stage.

  ---

  Explanation

  The BUILD_FILE_URL function in Snowflake generates a temporary URL for accessing a file in a stage. The function takes two arguments: the stage name and the file path. The generated URL is valid for 24 hours and can be used to download or view the file contents. The other options are incorrect because they do not describe the purpose of the BUILD_FILE_URL function.

• Question 57:

  A Data Engineer needs to know the details regarding the micro-partition layout for a table named invoice using a built-in function.

  Which query will provide this information?

  A. SELECT SYSTEM$CLUSTERING_INTFORMATICII (`Invoice' ) ;
  B. SELECT $CLUSTERXNG_INFQRMATION ('Invoice')'
  C. CALL SYSTEM$CLUSTERING_INFORMATION (`Invoice');
  D. CALL $CLUSTERINS_INFORMATION('Invoice');
  A. SELECT SYSTEM$CLUSTERING_INTFORMATICII (`Invoice' ) ;

  ---

  Explanation

  The query that will provide information about the micro-partition layout for a table named invoice using a built-in function is SELECT SYSTEM$CLUSTERING_INFORMATION(`Invoice');. The SYSTEM$CLUSTERING_INFORMATION function returns information about the clustering status of a table, such as the clustering key, the clustering depth, the clustering ratio, the partition count, etc. The function takes one argument: the table name in a qualified or unqualified form. In this case, the table name is Invoice and it is unqualified, which means that it will use the current database and schema as the context. The other options are incorrect because they do not use a valid built-in function for providing information about the micro-partition layout for a table. Option B is incorrect because it uses $CLUSTERING_INFORMATION instead of SYSTEM$CLUSTERING_INFORMATION, which is not a valid function name. Option C is incorrect because it uses CALL instead of SELECT, which is not a valid way to invoke a table function. Option D is incorrect because it uses CALL instead of SELECT and $CLUSTERING_INFORMATION instead of SYSTEM$CLUSTERING_INFORMATION, which are both invalid.

• Question 58:

  Which Snowflake feature facilitates access to external API services such as geocoders. data transformation, machine Learning models and other custom code?

  A. Security integration
  B. External tables
  C. External functions
  D. Java User-Defined Functions (UDFs)
  C. External functions

  ---

  Explanation

  External functions are Snowflake functions that facilitate access to external API services such as geocoders, data transformation, machine learning models and other custom code. External functions allow users to invoke external services from within SQL queries and pass arguments and receive results as JSON values. External functions require creating an API integration object and an external function object in Snowflake, as well as deploying an external service endpoint that can communicate with Snowflake via HTTPS.

• Question 59:

  A Data Engineer is investigating a query that is taking a long time to return The Query Profile shows the following:

  

  What step should the Engineer take to increase the query performance?

  A. Add additional virtual warehouses.
  B. increasethe size of the virtual warehouse.
  C. Rewrite the query using Common Table Expressions (CTEs)
  D. Change the order of the joins and start with smaller tables first
  B. increasethe size of the virtual warehouse.

  ---

  Explanation

  The step that the Engineer should take to increase the query performance is to increase the size of the virtual warehouse. The Query Profile shows that most of the time was spent on local disk IO, which indicates that the query was reading a lot of data from disk rather than from cache. This could be due to a large amount of data being scanned or a low cache hit ratio. Increasing the size of the virtual warehouse will increase the amount of memory and cache available for the query, which could reduce the disk IO time and improve the query performance. The other options are not likely to increase the query performance significantly. Option A, adding additional virtual warehouses, will not help unless they are used in a multi-cluster warehouse configuration or for concurrent queries. Option C, rewriting the query using Common Table Expressions (CTEs), will not affect the amount of data scanned or cached by the query. Option D, changing the order of the joins and starting with smaller tables first, will not reduce the disk IO time unless it also reduces the amount of data scanned or cached by the query.

• Question 60:

  A Data Engineer has written a stored procedure that will run with caller's rights. The Engineer has granted ROLEA right to use this stored procedure.

  What is a characteristic of the stored procedure being called using ROLEA?

  A. The stored procedure must run with caller's rights it cannot be converted later to run with owner's rights
  B. If the stored procedure accesses an object that ROLEA does not have access to the stored procedure will fail
  C. The stored procedure will run in the context (database and schema) where the owner created the stored procedure
  D. ROLEA will not be able to see the source code for the stored procedure even though the role has usage privileges on the stored procedure
  B. If the stored procedure accesses an object that ROLEA does not have access to the stored procedure will fail

  ---

  Explanation

  A stored procedure that runs with caller's rights executes with the privileges of the role that calls it. Therefore, if the stored procedure accesses an object that ROLEA does not have access to, such as a table or a view, the stored

  procedure will fail with an insufficient privileges error. The other options are not correct because:

  A stored procedure can be converted from caller's rights to owner's rights by using the ALTER PROCEDURE command with the EXECUTE AS OWNER option. A stored procedure that runs with caller's rights executes in the context

  (database and schema) of the caller, not the owner.

  ROLEA will be able to see the source code for the stored procedure by using the GET_DDL function or the DESCRIBE command, as long as it has usage privileges on the stored procedure.