Question 1

The employee project details has the project names as array against each employee as shown below

Which of the query below will convert the array into individual rows?

Options :

A : select emp_id,
             emp_name,
             p.value::string as project_names
      from employee_project_details,table(flatten(employee_project_details.project_names)) p
      ;

B :
    select emp_id,
             emp_name,
             p.value::string as project_names
      from employee_project_details,lateral(flatten(employee_project_details.project_names)) p
      ;

C : select emp_id,
emp_name,
p.value::string as project_names
from employee_project_details,lateral flatten(employee_project_details.project_names)) p

Answer: A

Question 2

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)?

Options :

A : SYSTEM$TASK_DEPENDENTS

B : SYSTEM$TASK_DEPENDENTS_ENABLE

C : SYSTEM$TASK_DEPENDENTS_RESUME

D : SYSTEM$TASK_RECURSIVE_ENABLE

Answer: B

Question 3

As part of Table Designing, Data Engineer added a timestamp column that inserts the current timestamp as the default value as records are loaded into a table. The intent is to capture the time when eachrecord was loaded into the table; however, the timestamps are earlier than the LOAD_TIME column values returned by COPY_HISTORY view (Account Usage). What could be reason of this issue?

Options :

A : LOAD_TIME column values returned by COPY_HISTORY view (Account Usage) gives the same time as returned by CURRENT_TIMESTAMP.

B : CURRENT_TIMESTAMP values might be different due to query gets executed in warehouse located in different region.

C : It might be possible that Cloud Provider hosted on Snowflake belongs to region having server time zone lagging Cluster time zone of warehouse where queries get processed & committed.

D : The reason is, CURRENT_TIMESTAMP is evaluated when the load operation is com-piled in cloud services rather than when the record is inserted into the table (i.e. when the transaction for the load operation is committed)

Answer: D

Question 4

What will happen if you try to create a materialized view as below
    create or replace materialized view employee_view as
        select id, role, name
            from employee where role = current_role();

Options :

A : Only users with the desired role will be able to retrieve results from the view

B : Only users with the correct role assigned and SYSADMIN will be able to retrieve the results from the view

C : The operation will error out since the function used is a non-deterministic one.

Answer: C

Question 5

Select the correct usage statements with regards to SQL UDF?

Options :

A : The body of a UDF cannot contain DDL statements or any DML statement other than SELECT.

B : Scalar functions (UDFs) have a limit of 500 input arguments.

C : When using a query expression in a SQL UDF, do not include a semicolon within the UDF body to terminate the query expression.

D : You can include only one query expression.

E : All of above are correct.

Answer: A

Smartly Prepare Exam with Free Online DEA-C01 Practice Test

Buying Options: