Databricks Databricks-Certified-Professional-Data-Engineer Dumps Questions Answers

Explanation

The answer isSELECT * FROM streadming_view order by id Please Note: Sorting with Group by will work without any issues

see below explanation for each option of the options,

Graphical user interface, text, application Description automatically generated

Certain operations are not allowed on streaming data, please see highlighted in bold.

https://spark.apache.org/docs/latest/structured-streaming-programming-guide.html#unsupported-operations

• Multiple streaming aggregations (i.e. a chain of aggregations on a streaming DF) are not yet supported on streaming Datasets.
• Limit and take the first N rows are not supported on streaming Datasets.
• Distinct operations on streaming Datasets are not supported.
• Deduplication operation is not supported after aggregation on a streaming Datasets.
• Sorting operations are supported on streaming Datasets only after an aggregation and in Complete Output Mode.

        Note: Sorting without aggregation function is not supported.

Here is the sample code to prove this,

Setup test stream

Graphical user interface, text, application, email Description automatically generated

Sum aggregation function has no issues on stream

Graphical user interface, application Description automatically generated

Max aggregation function has no issues on stream

Graphical user interface, application Description automatically generated

Group by with Order by  has no issues on stream

Group by has no issues on stream

Table Description automatically generated

Order by without group by fails.

Graphical user interface, text, application Description automatically generated

Explanation

Cluster pools allow us to reserve VM's ahead of time, when a new job cluster is created VM are grabbed from the pool. Note: when the VM's are waiting to be used by the cluster only cost incurred is Azure. Databricks run time cost is only billed once VM is allocated to a cluster.

Here is a demo of how to setup a pool and follow some best practices,

Graphical user interface, text Description automatically generated

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>') 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>') as performance,

10. current_timestamp() as insertDate