[Dec-2023] Snowflake DEA-C01 Exam Practice Test Questions - RealExamFree [Q37-Q53]

Share

[Dec-2023] Snowflake DEA-C01 Exam Practice Test Questions - RealExamFree

Updated Certification Exam DEA-C01 Dumps - Practice Test Questions

NEW QUESTION # 37
Dominic, a Data Engineer wants to resume the pipe named stalepipe3 which got stale after 14 days. To do the same, he called the SYSTEM$PIPE_FORCE_RESUME function select sys-tem$pipe_force_resume('snowmydb.mysnowschema.stalepipe3','staleness_check_override'); Let's say If the pipe is resumed 16 days after it was paused, what will happened to the event notifi-cation that were received on the first and second days after the pipe was paused?

  • A. Snowpipe generally skips any event notifications that were received on the first and second days after the pipe was paused.
  • B. Pipe maintains Metadata history of files for 64 days, so in this scenarios Snowpipe pro-cessed all the event notifications that were received for 16 days or so.
  • C. All the events get processed from day 1 if the PURGE properties in the PIPE object definition set to be FALSE initially.
  • D. Once the Pipe got stale, all the events got purged automatically & pipe needs to be rec-reated with modified properties.

Answer: A

Explanation:
Explanation
When a pipe is paused, event messages received for the pipe enter a limited retention period. The period is 14 days by default. If a pipe is paused for longer than 14 days, it is considered stale.
To resume a stale pipe, a qualified role must call the SYSTEM$PIPE_FORCE_RESUME function and input the STALENESS_CHECK_OVERRIDE argument. This argument indicates an under-standing that the role is resuming a stale pipe.
For example, resume the stale stalepipe1 pipe in the mydb.myschema database and schema:
select sys-tem$pipe_force_resume('mydb.myschema.stalepipe3','staleness_check_override'); As an event notification received while a pipe is paused reaches the end of the limited retention pe-riod, Snowflake schedules it to be dropped from the internal metadata. If the pipe is later resumed, Snowpipe processes these older notifications on a best effort basis. Snowflake cannot guarantee that they are processed.
For example, if a pipe is resumed 15 days after it was paused, Snowpipe generally skips any event notifications that were received on the first day the pipe was paused (i.e. that are now more than 14 days old).
If the pipe is resumed 16 days after it was paused, Snowpipe generally skips any event notifications that were received on the first and second days after the pipe was paused. And so on.


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

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

Answer: B,E,F

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


NEW QUESTION # 39
Data Engineer is using existing pipe that automates data loads using event notifications, later he figured out the needs to modify pipe properties. For the same, He decided to recreate the pipe as best practice. He followed the below steps for the same.
1. Query the SYSTEM$PIPE_STATUS function and verify that the pipe execution state is RUN-NING.
2. Recreate the pipe (using CREATE OR REPLACE PIPE).
3. Query the SYSTEM$PIPE_STATUS function and verify that the pipe execution state is RUN-NING.
Which are the Missing recommended steps while Recreating Pipes for Automated Data Loads?

  • A. CREATE OR REPLACE PIPE command will recreate the PIPE successfully.
  • B. Force the pipe to resume (using SYSTEM$PIPE_FORCE_RESUME).
  • C. Terminate the existing pipe (using ALTER PIPE ... SET PIPE_EXECUTION_TERMINATE = true) before recreation.
  • D. Pause the pipe (using ALTER PIPE ... SET PIPE_EXECUTION_PAUSED = true) Pre & Post recreation & Resume after recreation (using ALTER PIPE ... SET PIPE_EXECUTION_PAUSED = false).

Answer: D

Explanation:
Explanation
Recreating a pipe (using a CREATE OR REPLACE PIPE statement) is necessary to modify most pipe properties.
Recreating Pipes for Automated Data Loads
When recreating a pipe that automates data loads using event notifications, it's recommended that Data Engineer complete the following steps:
1. Pause the pipe (using ALTER PIPE ... SET PIPE_EXECUTION_PAUSED = true).
2. Query the SYSTEM$PIPE_STATUS function and verify that the pipe execution state is PAUSED.
3. Recreate the pipe (using CREATE OR REPLACE PIPE).
4. Pause the pipe again.
5. Review the configuration steps for your cloud messaging service to ensure the settings are still accurate.
6. Query the SYSTEM$PIPE_STATUS function again and verify that the pipe execution state is RUNNING.


NEW QUESTION # 40
Select the correct usage statements with regards to SQL UDF?

  • A. All of above are correct.
  • B. You can include only one query expression.
  • C. Scalar functions (UDFs) have a limit of 500 input arguments.
  • D. When using a query expression in a SQL UDF, do not include a semicolon within the UDF body to terminate the query expression.
  • E. The body of a UDF cannot contain DDL statements or any DML statement other than SELECT.

Answer: A


NEW QUESTION # 41
A large table with 200 columns contains two years of historical data. When queried. the table is filtered on a single day Below is the Query Profile:

Using a size 2XL virtual warehouse, this query look over an hour to complete What will improve the query performance the MOST?

  • A. Increase the number of clusters in the virtual warehouse
  • B. Implement the search optimization service on the table
  • C. Add a date column as a cluster key on the table
  • D. increase the size of the virtual warehouse.

Answer: C

Explanation:
Explanation
Adding a date column as a cluster key on the table will improve the query performance by reducing the number of micro-partitions that need to be scanned. Since the table is filtered on a single day, clustering by date will make the query more selective and efficient.


NEW QUESTION # 42
A SQL UDF evaluates an arbitrary SQL expression and returns the result(s) of the expression. Which value type it can returns?

  • A. A Set of Rows
  • B. Regex
  • C. Scaler or Tabular depend on input SQL expression
  • D. Single Value

Answer: C


NEW QUESTION # 43
The smaller the average depth, the better clustered the table is with regards to the specified column?

  • A. TRUE
  • B. FALSE

Answer: A


NEW QUESTION # 44
Which of the below concepts/functions helps while implementing advanced Column-level Security?

  • A. CURRENT_ROLE
  • B. CURRENT_CLIENT
  • C. INVOKER_ROLE
  • D. Role Hierarchy

Answer: A,C,D

Explanation:
Explanation
Column-level Security supports using Context Functions in the conditions of the masking policy body to enforce whether a user has authorization to see data. To determine whether a user can see data in a given SQL statement, it is helpful to consider:
Masking policy conditions using CURRENT_ROLE target the role in use for the current session.
Masking policy conditions using INVOKER_ROLE target the executing role in a SQL statement.
Role hierarchy
Determine if a specified role in a masking policy condition (e.g. ANALYST custom role) is a lower privilege role in the CURRENT_ROLE or INVOKER_ROLE role hierarchy. If so, then the role returned by the CURRENT_ROLE or INVOKER_ROLE functions inherits the privileges of the specified role.


NEW QUESTION # 45
Let us say you have List of 50 Source files, which needs to be loaded into Snowflake internal stage. All these Source system files are already Brotli-compressed files. Which statement is correct with respect to Compression of Staged Files?

  • A. Even though Source files are already compressed, Snowflake do apply default gzip2 Compression to optimize the storage cost.
  • B. Snowflake automatically detect Brotli Compression, will skip further compression of all 50 files.
  • C. When staging 50 compressed files in a Snowflake stage, the files are automatically com-pressed using gzip.
  • D. Auto-detection is not yet supported for Brotli-compressed files; when staging or loading Brotli-compressed files, you must explicitly specify the compression method that was used.

Answer: D

Explanation:
Explanation
Auto-detection is not yet supported for Brotli-compressed files; when staging or loading Brotli-compressed files, you must explicitly specify the compression method that was used.
To Know more about Compression of Staged Files, please refer the link:
https://docs.snowflake.com/en/user-guide/intro-summary-loading.html#compression-of-staged-files


NEW QUESTION # 46
Database XYZ has the data_retention_time_in_days parameter set to 7 days and table xyz.public.ABC has the data_retention_time_in_daysset to 10 days.
A Developer accidentally dropped the database containing this single table 8 days ago and just discovered the mistake.
How can the table be recovered?

  • A. undrop database xyz;
  • B. create -able abc_restore as select * from xyz.public.abc at {offset => -60*60*24*8};
  • C. Create a Snowflake Support case lo restore the database and tab e from "a i-safe
  • D. create table abc_restore clone xyz.public.abc at (offset => -300G*24*3);

Answer: A

Explanation:
Explanation
The table can be recovered by using the undrop database xyz; command. This command will restore the database that was dropped within the last 14 days, along with all its schemas and tables, including the customer table. The data_retention_time_in_days parameter does not affect this command, as it only applies to time travel queries that reference historical data versions of tables or databases. The other options are not valid ways to recover the table. Option B is incorrect because creating a table as select * from xyz.public.ABC at
{offset => -6060248} will not work, as this query will try to access a historical data version of the ABC table that does not exist anymore after dropping the database. Option C is incorrect because creating a table clone xyz.public.ABC at {offset => -360024*3} will not work, as this query will try to clone a historical data version of the ABC table that does not exist anymore after dropping the database. Option D is incorrect because creating a Snowflake Support case to restore the database and table from fail-safe will not work, as fail-safe is only available for disaster recovery scenarios and cannot be accessed by customers.


NEW QUESTION # 47
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. Recreate the pipe by using the create or replace pipe command
  • C. Modify the file by adding a blank row to the bottom and re-stage the file
  • D. Set the FORCE=TRUE option in the Snowpipe COPY INTO command

Answer: D

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


NEW QUESTION # 48
The Snowpipe API provides REST endpoints for fetching load reports. One of the Endpoint named insertReport helps to retrieves a report of files submitted via insertFiles end point whose contents were recently ingested into a table. A success response (200) contains information about files that have recently been added to the table. Response Looks like below:
1.{
2."pipe": "SNOWTESTDB.SFTESTSCHEMA.SFpipe",
3."completeResult": true,
4."nextBeginMark": "1_16",
5."files": [
6.{
7."path": "data4859992083898.csv",
8."stageLocation": "s3://mybucket/",
9."fileSize": 89,
10."timeReceived": "2022-01-31T04:47:41.453Z",
11."lastInsertTime": "2022-01-31T04:48:28.575Z",
12."rowsInserted": 1,
13."rowsParsed": 1,
14."errorsSeen": 0,
15."errorLimit": 1,
16."complete": true,
17."status": "????"
18.}
19.]
20.}
Which one is the correct value of status string data in the Response Body?

  • A. LOADED_SUCCESS
  • B. LOAD_SUCCESS
  • C. LOADED
  • D. SUCCESS

Answer: B

Explanation:
Explanation
Permissible Load status for the file:
LOAD_IN_PROGRESS: Part of the file has been loaded into the table, but the load process has not completed yet.
LOADED: The entire file has been loaded successfully into the table.
LOAD_FAILED: The file load failed.
PARTIALLY_LOADED: Some rows from this file were loaded successfully, but others were not loaded due to errors. Processing of this file is completed.
Please not the different Response Codes available with their meaning.
200 - Success. Report returned.
400 - Failure. Invalid request due to an invalid format, or limit exceeded.
404 - Failure. pipeName not recognized.
This error code can also be returned if the role used when calling the endpoint does not have suffi-cient privileges. For more information, see Granting Access Privileges.
429 - Failure. Request rate limit exceeded.
500 - Failure. Internal error occurred.
As you could understand from the questions, there is 200 Success response returned, Status in the response body would be LOADED.


NEW QUESTION # 49
Ron, Snowflake Developer needs to capture change data (insert only) on the source views, for that he follows the below steps:
Enable change tracking on the source views & its underlying tables.
Inserted the data via Scripts scheduled with the help of Tasks.
then simply run the below Select statements.
1.select *
2.from test_table
3.changes(information => append_only)
4.at(timestamp => (select current_timestamp()));
Select the Correct Query Execution Output option below:

  • A. Select query will fail with error: 'SQL compilation error-Incorrect Keyword "Chang-es()" found'
  • B. No Error reported, select command gives Changed records with Metadata columns as change tracking enabled on the Source views & its underlying tables.
  • C. Select statement complied but gives erroneous results.
  • D. Developer missed to create stream on the source table which can further query to cap-ture DML records.

Answer: B

Explanation:
Explanation
As an alternative to streams, Snowflake supports querying change tracking metadata for tables or views using the CHANGES clause for SELECT statements. The CHANGES clause enables query-ing change tracking metadata between two points in time without having to create a stream with an explicit transactional offset.
To Know more about Snowflake CHANGES clause, please refer the mentioned link:
https://docs.snowflake.com/en/sql-reference/constructs/changes


NEW QUESTION # 50
When using the CURRENT_ROLE and CURRENT_USER functions with secure UDFs that will be shared with Snowflake accounts, Snowflake returns a NULL value for these functions?

  • A. TRUE
  • B. FALSE

Answer: A

Explanation:
Explanation
When using the CURRENT_ROLE and CURRENT_USER functions with secure UDFs that will be shared with Snowflake accounts, Snowflake returns a NULL value for these functions. The rea-son is that the owner of the data being shared does not typically control the users or roles in the ac-count with which the UDF is being shared.


NEW QUESTION # 51
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.
  • B.
  • C.
  • D.

Answer: B

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


NEW QUESTION # 52
Tasks may optionally use table streams to provide a convenient way to continuously process new or changed data. A task can transform new or changed rows that a stream surfaces. Each time a task is scheduled to run, it can verify whether a stream contains change data for a table and either consume the change data or skip the current run if no change data exists. Which System Function can be used by Data engineer to verify whether a stream contains changed data for a table?

  • A. SYSTEM$STREAM_DELTA_DATA
  • B. SYSTEM$STREAM_HAS_DATA
  • C. SYSTEM$STREAM_CDC_DATA
  • D. SYSTEM$STREAM_HAS_CHANGE_DATA

Answer: B

Explanation:
Explanation
SYSTEM$STREAM_HAS_DATA
Indicates whether a specified stream contains change data capture (CDC) records.


NEW QUESTION # 53
......

Updated Verified DEA-C01 dumps Q&As - Pass Guarantee or Full Refund: https://www.realexamfree.com/DEA-C01-real-exam-dumps.html

DEA-C01 PDF Questions and Testing Engine With 67 Questions: https://drive.google.com/open?id=1Qj0z8JtKttAxrdkYTTa2lARzezN2YLxf