A data engineering manager has noticed that each of the queries in a Databricks SQL dashboard takes a few
minutes to update when they manually click the "Refresh" button. They are curious why this might be
occurring, so a team member provides a variety of reasons on why the delay might be occurring.
Which of the following reasons fails to explain why the dashboard might be taking a few minutes to update?

• A.The queries attached to the dashboard might take a few minutes to run under normal circumstances
• B.The queries attached to the dashboard might all be connected to their own, unstarted Databricks clusters
• C.The SQL endpoint being used by each of the queries might need a few minutes to start up
• D.The Job associated with updating the dashboard might be using a non-pooled endpoint
• E.The queries attached to the dashboard might first be checking to determine if new data is available

Comment: *

Name: *

Email: *

Verification: *

You are trying to calculate total sales made by all the employees by parsing a complex struct data type that stores employee and sales data, how would you approach this in SQL Table definition, batchId INT, performance ARRAY<STRUCT<employeeId: BIGINT, sales: INT>>, in-sertDate TIMESTAMP Sample data of performance column
1.[
2.{ "employeeId":1234
3."sales" : 10000},
4.
5.{ "employeeId":3232
6."sales" : 30000}
7.]
Calculate total sales made by all the employees?
Sample data with create table syntax for the data:
1.create or replace table sales as
2.select 1 as batchId ,
3.from_json('[{ "employeeId":1234,"sales" : 10000 },{ "employeeId":3232,"sales" : 30000 }]',
4. 'ARRAY<STRUCT<employeeId: BIGINT, sales: INT>>') as performance,
5. current_timestamp() as insertDate
6.union all
7.select 2 as batchId ,
8. from_json('[{ "employeeId":1235,"sales" : 10500 },{ "employeeId":3233,"sales" : 32000 }]',
9. 'ARRAY<STRUCT<employeeId: BIGINT, sales: INT>>') as performance,
10. current_timestamp() as insertDate

• A.1.WITH CTE as (SELECT EXPLODE (performance) FROM table_name)
  2.SELECT SUM (performance.sales) FROM CTE
• B.1.WITH CTE as (SELECT FLATTEN (performance) FROM table_name)
  2.SELECT SUM (sales) FROM CTE
• C.1.select aggregate(flatten(collect_list(performance.sales)), 0, (x, y) -> x + y)
  2.as total_sales from sales
• D.SELECT SUM(SLICE (performance, sales)) FROM employee
• E.1.select reduce(flatten(collect_list(performance:sales)), 0, (x, y) -> x + y)
  2.as total_sales from sales

Correct Answer: C

Explanation
The answer is
1.select aggregate(flatten(collect_list(performance.sales)), 0, (x, y) -> x + y)
2.as total_sales from sales
Nested Struct can be queried using the . notation performance.sales will give you access to all the sales values in the performance column.
Note: option D is wrong because it uses performance:sales not performance.sales. ":" this is only used when referring to JSON data but here we are dealing with a struct data type. for the exam please make sure to understand if you are dealing with JSON data or Struct data.

Other solutions:
we can also use reduce instead of aggregate
select reduce(flatten(collect_list(performance.sales)), 0, (x, y) -> x + y) as total_sales from sales we can also use explode and sum instead of using any higher-order funtions.
1.with cte as (
2. select
3. explode(flatten(collect_list(performance.sales))) sales from sales
4.)
5.select
6. sum(sales) from cte
Sample data with create table syntax for the data:
1.create or replace table sales as
2.select 1 as batchId ,
3.from_json('[{ "employeeId":1234,"sales" : 10000 },{ "employeeId":3232,"sales" : 30000 }]',
4. 'ARRAY<STRUCT<employeeId: BIGINT, sales: INT>>') as performance,
5. current_timestamp() as insertDate
6.union all
7.select 2 as batchId ,
8. from_json('[{ "employeeId":1235,"sales" : 10500 },{ "employeeId":3233,"sales" : 32000 }]',
9. 'ARRAY<STRUCT<employeeId: BIGINT, sales: INT>>') as performance,
10. current_timestamp() as insertDate

Comment: *

Name: *

Email: *

Verification: *

Which of the following SQL command can be used to insert or update or delete rows based on a condition to check if a row(s) exists?

• A.MERGE INTO table_name
• B.COPY INTO table_name
• C.UPDATE table_name
• D.INSERT INTO OVERWRITE table_name
• E.INSERT IF EXISTS table_name

Comment: *

Name: *

Email: *

Verification: *

The data engineering team is using a SQL query to review data completeness every day to monitor the ETL job, and query output is being used in multiple dashboards which of the following ap-proaches can be used to set up a schedule and automate this process?

• A.They can schedule the query to run every day from the Jobs UI.
• B.They can schedule the query to refresh every day from the query's page in Databricks SQL
• C.They can schedule the query to run every 12 hours from the Jobs UI.
• D.They can schedule the query to refresh every day from the SQL endpoint's page in Databricks SQL.
• E.They can schedule the query to refresh every 12 hours from the SQL endpoint's page in Databricks SQL

Comment: *

Name: *

Email: *

Verification: *

A data engineer has set up a notebook to automatically process using a Job. The data engineer's manager wants
to version control the schedule due to its complexity.
Which of the following approaches can the data engineer use to obtain a version-controllable con-figuration of
the Job's schedule?

• A.They can link the Job to notebooks that are a part of a Databricks Repo
• B.They can submit the Job once on a Job cluster
• C.They can submit the Job once on an all-purpose cluster
• D.They can download the JSON description of the Job from the Job's page
• E.They can download the XML description of the Job from the Job's page

Comment: *

Name: *

Email: *

Verification: *