Snowflake DEA-C01 Online Practice Questions and Exam Preparation

Snowflake DEA-C01 Online Questions & Answers

• Question 31:

  Which methods can be used to create a DataFrame object in Snowpark? (Select THREE)

  A. session.jdbc_connection()
  B. session.read.json{)
  C. session,table()
  D. DataFraas.writeO
  E. session.builder()
  F. session.sql()
  B. session.read.json{)
  C. session,table()
  F. session.sql()

  ---

  Explanation

  The methods that can be used to create a DataFrame object in Snowpark are session.read.json(), session.table(), and session.sql(). These methods can create a DataFrame from different sources, such as JSON files, Snowflake tables, or SQL queries. The other options are not methods that can create a DataFrame object in Snowpark. Option A, session.jdbc_connection(), is a method that can create a JDBC connection object to connect to a database. Option D, DataFrame.write(), is a method that can write a DataFrame to a destination, such as a file or a table. Option E, session.builder(), is a method that can create a SessionBuilder object to configure and build a Snowpark session.

• Question 32:

  What are characteristics of Snowpark Python packages? (Select THREE).

  Third-party packages can be registered as a dependency to the Snowpark session using the session, import () method.

  A. Python packages can access any external endpoints
  B. Python packages can only be loaded in a local environment
  C. Third-party supported Python packages are locked down to prevent hitting
  D. The SQL command DESCRIBE FUNCTION will list the imported Python packages of the Python User-Defined Function (UDF).
  E. Querying information__schema .packages will provide a list of supported Python packages and versions
  A. Python packages can access any external endpoints
  D. The SQL command DESCRIBE FUNCTION will list the imported Python packages of the Python User-Defined Function (UDF).
  E. Querying information__schema .packages will provide a list of supported Python packages and versions

  ---

  Explanation

  The characteristics of Snowpark Python packages are:

  Third-party packages can be registered as a dependency to the Snowpark session using the session.import() method.

  The SQL command DESCRIBE FUNCTION will list the imported Python packages of the Python User-Defined Function (UDF).

  Querying information_schema.packages will provide a list of supported Python packages and versions.

  These characteristics indicate how Snowpark Python packages can be imported, inspected, and verified in Snowflake. The other options are not characteristics of Snowpark Python packages. Option B is incorrect because Python packages

  can be loaded in both local and remote environments using Snowpark. Option C is incorrect because third-party supported Python packages are not locked down to prevent hitting external endpoints, but rather restricted by network policies

  and security settings.

• Question 33:

  A company built a sales reporting system with Python, connecting to Snowflake using the Python Connector. Based on the user's selections, the system generates the SQL queries needed to fetch the data for the report First it gets the customers that meet the given query parameters (on average 1000 customer records for each report run) and then it loops the customer records sequentially Inside that loop it runs the generated SQL clause for the current customer to get the detailed data for that customer number from the sales data table

  When the Data Engineer tested the individual SQL clauses they were fast enough (1 second to get the customers 0 5 second to get the sales data for one customer) but the total runtime of the report is too long

  How can this situation be improved?

  A. Increase the size of the virtual warehouse
  B. Increase the number of maximum clusters of the virtual warehouse
  C. Define a clustering key for the sales data table
  D. Rewrite the report to eliminate the use of the loop construct
  D. Rewrite the report to eliminate the use of the loop construct

  ---

  Explanation

  This option is the best way to improve the situation, as using a loop construct to run SQL queries for each customer is very inefficient and slow. Instead, the report should be rewritten to use a single SQL query that joins the customer and sales data tables and applies the query parameters as filters. This way, the report can leverage Snowflake's parallel processing and optimization capabilities and reduce the network overhead and latency.

• Question 34:

  A Data Engineer is building a set of reporting tables to analyze consumer requests by region for each of the Data Exchange offerings annually, as well as click-through rates for each listing

  Which views are needed MINIMALLY as data sources?

  A. SNOWFLAKE- DATA_SHARING_USAGE - LISTING_EVENTS_BAILY
  B. SNOWFLAKE.DATA_SHARING_USAGE.LISTING_CONSOKE>TION_DAILY
  C. SNOWFLAKE. DATA_SHARING_USAGE. LISTING_TELEMETRY_DAILy
  D. SNOWFLAKE.ACCOUNT_USAGE.DATA _TRANSFER_HISTORY
  B. SNOWFLAKE.DATA_SHARING_USAGE.LISTING_CONSOKE>TION_DAILY

  ---

  Explanation

  The SNOWFLAKE.DATA SHARING _USAGE.LISTING_CONSOKE>TION_DAILY view provides information about consumer requests by region for each of the Data Exchange offeringsannually, as well as click- through rates for each listing. This view is the minimal data source needed for building the reporting tables. The other views are not relevant for this use case.

• Question 35:

  Given the table sales which has a clustering key of column CLOSED_DATE which table function will return the average clustering depth for the SALES_REPRESENTATIVEcolumn for the North American region?

  

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

  ---

  Explanation

  The table function SYSTEM$CLUSTERING_DEPTH returns the average clustering depth for a specified column or set of columns in a table. The function takes two arguments: the table name and the column name(s). In this case, the table name is sales and the column name is SALES_REPRESENTATIVE. The function also supports a WHERE clause to filter the rows for which the clustering depth is calculated. In this case, the WHERE clause is REGION = `North America'. Therefore, the function call in Option B will return the desired result.

• Question 36:

  Assuming a Data Engineer has all appropriate privileges and context which statements would be used to assess whether the User-Defined Function (UDF), MTBATA3ASZ. SALES .REVENUE_BY_REGION, exists and is secure? (Select TWO)

  A. SHOW DS2R FUNCTIONS LIKE 'REVEX'^BYJIESION' IN SCHEMA SALES;
  B. SELECT IS_SECURE FROM SNOWFLAKE. INFCRXATION_SCKZMA. FUNCTIONS WHERE FUNCTI0N_3CHEMA = 'SALES' AND FUNCTI CN_NAXE = 穎tEVEXUE_BY_RKXQH4;
  C. SELECT IS_SEC"JRE FROM INFOR>LVTICN_SCHEMA. FUNCTIONS WHERE FUNCTION_SCHEMA = 'SALES1 AND FUNGTZON_NAME = ' REVENUE_BY_REGION';
  D. SHOW EXTERNAL FUNCTIONS LIKE `REVENUE_BY_REGION'IB SCHEMA SALES;
  E. SHOW SECURE FUNCTIONS LIKE 'REVENUE 3Y REGION' IN SCHEMA SALES;
  A. SHOW DS2R FUNCTIONS LIKE 'REVEX'^BYJIESION' IN SCHEMA SALES;
  B. SELECT IS_SECURE FROM SNOWFLAKE. INFCRXATION_SCKZMA. FUNCTIONS WHERE FUNCTI0N_3CHEMA = 'SALES' AND FUNCTI CN_NAXE = 穎tEVEXUE_BY_RKXQH4;

  ---

  Explanation

  The statements that would be used to assess whether the UDF, MTBATA3ASZ. SALES .REVENUE_BY_REGION, exists and is secure are:

  SHOW DS2R FUNCTIONS LIKE `REVEX'^BYJIESION' IN SCHEMA SALES;:

  This statement will show information about the UDF, including its name, schema, database, arguments, return type, language, and security option. If the UDF does not exist, the statement will return an empty result set. SELECT IS_SECURE

  FROM SNOWFLAKE. INFCRXATION_SCKZMA. FUNCTIONS WHERE FUNCTI0N_3CHEMA = `SALES' AND FUNCTI CN_NAXE = 穎tEVEXUE_BY_RKXQH4;: This statement will query the

  SNOWFLAKE.INFORMATION_SCHEMA.FUNCTIONS view, which contains metadata about the UDFs in the current database. The statement will return the IS_SECURE column, which indicates whether the UDF is secure or not. If the UDF

  does not exist, the statement will return an empty result set. The other statements are not correct because:

  SELECT IS_SEC"JRE FROM INFOR>LVTICN_SCHEMA. FUNCTIONS WHERE FUNCTION_SCHEMA = `SALES1 AND FUNGTZON_NAME = '

  REVENUE_BY_REGION';: This statement will query the INFORMATION_SCHEMA.FUNCTIONS view, which contains metadata about the UDFs in the current schema. However, the statement has a typo in the schema name (`SALES1'

  instead of `SALES'), which will cause it to fail or return incorrect results.

  SHOW EXTERNAL FUNCTIONS LIKE `REVENUE_BY_REGION' IB SCHEMA SALES;: This statement will show information about external functions, not UDFs. External functions are Snowflake functions that invoke external services via

  HTTPS requests and responses. The statement will not return any results for the UDF.

  SHOW SECURE FUNCTIONS LIKE `REVENUE 3Y REGION' IN SCHEMA SALES;: This statement is invalid because there is no such thing as secure functions in Snowflake. Secure functions are a feature of some other databases, such as

  PostgreSQL, but not Snowflake. The statement will cause a syntax error.

• Question 37:

  A Data Engineer has developed a dashboard that will issue the same SQL select clause to Snowflake every 12 hours.

  ---will Snowflake use the persisted query results from the result cache provided that the underlying data has not changed^

  A. 12 hours
  B. 24 hours
  C. 14 days
  D. 31 days
  C. 14 days

  ---

  Explanation

  Snowflake uses the result cache to store the results of queries that have been executed recently. The result cache is maintained at the account level and is shared across all sessions and users. The result cache is invalidated when any changes are made to the tables or views referenced by the query. Snowflake also has a retention policy for the result cache, which determines how long the results are kept in the cache before they are purged. The default retention period for the result cache is 24 hours, but it can be changed at the account, user, or session level. However, there is a maximum retention period of 14 days for the result cache, which cannot be exceeded. Therefore, if the underlying data has not changed, Snowflake will use the persisted query results from the result cache for up to 14 days.

• Question 38:

  A stream called TRANSACTIONS_STM is created on top of a transactions table in a continuous pipeline running in Snowflake. After a couple of months, the TRANSACTIONS table is renamed transactiok3_raw to comply with new naming standards

  What will happen to the TRANSACTIONS _STM object?

  A. TRANSACTIONS _STMwill keep working as expected
  B. TRANSACTIONS _STMwill be stale and will need to be re-created
  C. TRANSACTIONS _STMwill be automatically renamedTRANSACTIONS _RAW_STM.
  D. Reading from the traksactioks_3T>: stream will succeed for some time after the expected STALE_TIME.
  B. TRANSACTIONS _STMwill be stale and will need to be re-created

  ---

  Explanation

  A stream is a Snowflake object that records the history of changes made to a table. A stream is associated with a specific table at the time of creation, and it cannot be altered to point to a different table later. Therefore, if the

  source table is renamed, the stream will become stale and will need to be re-created with the new table name. The other options are not correct because:

  TRANSACTIONS _STM will not keep working as expected, as it will lose track of the changes made to the renamed table.

  TRANSACTIONS _STM will not be automatically renamed TRANSACTIONS _RAW_STM, as streams do not inherit the name changes of their source tables. Reading from the transactions_stm stream will not succeed for some time after the

  expected STALE_TIME, as streams do not have a STALE_TIME property.

• Question 39:

  A database contains a table and a stored procedure defined as.

  

  No other operations are affecting the log_table.

  What will be the outcome of the procedure call?

  A. The Iog_table contains zero records and the stored procedure returned 1 as a return value
  B. The Iog_table contains one record and the stored procedure returned 1 as a return value
  C. The log_table contains one record and the stored procedure returned NULL as a return value
  D. The Iog_table contains zero records and the stored procedure returned NULL as a return value
  B. The Iog_table contains one record and the stored procedure returned 1 as a return value

  ---

  Explanation

  The stored procedure is defined with a FLOAT return type and a JavaScript language. The body of the stored procedure contains a SQL statement that inserts a row into the log_table with a value of `1' for col1. The body also

  contains a return statement that returns 1 as a float value. When the stored procedure is called with any VARCHAR parameter, it will execute successfully and insert one record into the log_table and return 1 as a return value. The other

  options are not correct because:

  The log_table will not be empty after the stored procedure call, as it will contain one record inserted by the SQL statement.

  The stored procedure will not return NULL as a return value, as it has an explicit return statement that returns 1.

• Question 40:

  Assuming that the session parameter USE_CACHED_RESULT is set to false, what are characteristics of Snowflake virtual warehouses in terms of the use of Snowpark?

  A. Creating a DataFrame from a table will start a virtual warehouse
  B. Creating a DataFrame from a staged file with the read () method will start a virtual warehouse
  C. Transforming a DataFrame with methods like replace () will start a virtual warehouse
  D. Calling a Snowpark stored procedure to query the database with session, call () will start a virtual warehouse
  A. Creating a DataFrame from a table will start a virtual warehouse

  ---

  Explanation

  Creating a DataFrame from a table will start a virtual warehouse because it requires reading data from Snowflake. The other options will not start a virtual warehouse because they either operate on local data or use an existing session to query Snowflake.