[Oct-2023] Exam Sure Pass Snowflake Certification with DEA-C01 exam questions [Q37-Q60]

NO.37 Jackie, a Data engineer advised to his data team members about one of the Role highlighting fol-lows points:
1. Avoid Using the <?> Role for Automated Scripts
2. Avoid Using the <?> Role to Create Objects
Which System defined or Custom Role She is mentioning?

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

NO.39 Which are supported Programming Languages for Creating UDTFs?

NO.40 A Data Engineer wants to create a new development database (DEV) as a clone of the permanent production database (PROD) There is a requirement to disable Fail-safe for all tables.
Which command will meet these requirements?

NO.41 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?

NO.42 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?

NO.43 External Function is a type of UDF & can be Scaler or Tabular?

NO.44 What are Invalid rules applicable when using stored procedure contains transaction?

NO.45 Mark the Correct Statements:
Statement 1. Enable failover for a primary database to one or more accounts in your organization using an ALTER DATABASE … ENABLE FAILOVER TO ACCOUNTS statement.
Statement 2. Enabling failover for a primary database can be done by Data Engineer either before or after a replica of the primary database has been created in a specified account.

NO.46 Which connector creates the RECORD_CONTENT and RECORD_METADATA columns in the existing Snowflake table while connecting to Snowflake?

NO.47 1.+————————————————————–+
2.| SYSTEM$CLUSTERING_INFORMATION(‘SF_DATA’, ‘(COL1, COL3)’) |
3.|————————————————————–|
4.| { |
5.| “cluster_by_keys” : “(COL1, COL3)”, |
6.| “total_partition_count” : 1156, |
7.| “total_constant_partition_count” : 0, |
8.| “average_overlaps” : 117.5484, |
9.| “average_depth” : 64.0701, |
10.| “partition_depth_histogram” : { |
11.| “00000” : 0, |
12.| “00001” : 0, |
13.| “00002” : 3, |
14.| “00003” : 3, |
15.| “00004” : 4, |
16.| “00005” : 6, |
17.| “00006” : 3, |
18.| “00007” : 5, |
19.| “00008” : 10, |
20.| “00009” : 5, |
21.| “00010” : 7, |
22.| “00011” : 6, |
23.| “00012” : 8, |
24.| “00013” : 8, |
25.| “00014” : 9, |
26.| “00015” : 8, |
27.| “00016” : 6, |
28.| “00032” : 98, |
29.| “00064” : 269, |
30.| “00128” : 698 |
31.| } |
32.| } |
33.+————————————————————–+
The Above example indicates that the SF_DATA table is not well-clustered for which of following valid reasons?

NO.48 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?

NO.49 Data Engineer, ran the below clustering depth analysis function:
select system$clustering_depth(‘TPCH_CUSTOMERS’, ‘(C1, C6)’, ‘C9 = 30’); on TPCH_CUSTOMERS table, will return which of the following?

NO.50 Which UDF programming language is not supported with Snowflake Secure Data Sharing feature?

NO.51 As Data Engineer, you have requirement to Load set of New Product Files containing Product rele-vant information into the Snowflake internal tables, Later you analyzed that some of the Source files are already loaded in one of the historical batch & for that you have prechecked Metadata col-umn LAST_MODIFIED date for a staged data file & found out that LAST_MODIFIED date is older than 64 days for few files and the initial set of data was loaded into the table more than 64 days earlier, Which one is the best approach to Load Source data files with expired load metadata along with set of files whose metadata might be available to avoid data duplication?

NO.52 Which Function would Data engineer used to recursively resume all tasks in Chain of Tasks rather than resuming each task individually (using ALTER TASK … RESUME)?

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

NO.54 Which are the two ways to access elements in a JSON object?

NO.55 In efforts to recover the dropped child tables within schema named SCV_SCHEMA by Data Engi-neer, She found that DATA_RETENTION_TIME_IN_DAYS parameter set with value 45 days at Schema level &the data retention period for child tables explicitly set at 85 days. What will happen when she will try to run undrop table command on Child tables to recover them on the 50th day as-suming SCV_SCHEMA is already dropped on 45th day?

NO.56 What are Common Query Problems a Data Engineer can identified using Query Profiler?

NO.57 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).

NO.58 PARTITION_TYPE = USER_SPECIFIED must be used when you prefer to add and remove par-titions selectively rather than automatically adding partitions for all new files in an external storage location that match an expression?

NO.59 Data Engineer wants to analyze query performance & looking out for profiling information, He went to Query/Operator Details also called Profile Overview of Query Profile Interface & searching for statistics attributes around I/O. Which of the following information he can’t get from there?

NO.60 To advance the offset of a stream to the current table version without consuming the change data in a DML operation, which of the following operations can be done by Data Engineer? [Select 2]