QUESTION: 90 Exam Topic: Design and develop data processing Questions

You have an Azure subscription that contains an Azure Synapse Analytics dedicated SQL pool named Pool1. Pool1 receives new data once every 24 hours.

You have the following function.

You have the following query.

The query is executed once every 15 minutes and the @parameter value is set to the current date.
You need to minimize the time it takes for the query to return results.

Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

Create an index on the avg_f column.
Convert the avg_c column into a calculated column.
Create an index on the sensorid column.
Enable result set caching.
Change the table distribution to replicate.

Answer(s): B,D

Explanation:

D: When result set caching is enabled, dedicated SQL pool automatically caches query results in the user database for repetitive use. This allows subsequent query executions to get results directly from the persisted cache so recomputation is not needed. Result set caching improves query performance and reduces compute resource usage. In addition, queries using cached results set do not use any concurrency slots and thus do not count against existing concurrency limits.

Incorrect:
Not A, not C: No joins so index not helpful.
Not E: What is a replicated table?
A replicated table has a full copy of the table accessible on each Compute node. Replicating a table removes the need to transfer data among Compute nodes before a join or aggregation. Since the table has multiple copies, replicated tables work best when the table size is less than 2 GB compressed. 2 GB is not a hard limit. If the data is static and does not change, you can replicate larger tables.

Reference:

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/performance-tuning-result-set-caching

Reveal Solution Next Question

QUESTION: 91 Exam Topic: Design and develop data processing Questions

You need to design a solution that will process streaming data from an Azure Event Hub and output the data to Azure Data Lake Storage. The solution must ensure that analysts can interactively query the streaming data.

What should you use?

Azure Stream Analytics and Azure Synapse notebooks
Structured Streaming in Azure Databricks
event triggers in Azure Data Factory
Azure Queue storage and read-access geo-redundant storage (RA-GRS)

Answer(s): C

Explanation:

Apache Spark Structured Streaming is a fast, scalable, and fault-tolerant stream processing API. You can use it to perform analytics on your streaming data in near real-time.
With Structured Streaming, you can use SQL queries to process streaming data in the same way that you would process static data.

Azure Event Hubs is a scalable real-time data ingestion service that processes millions of data in a matter of seconds. It can receive large amounts of data from multiple sources and stream the prepared data to Azure Data Lake or Azure Blob storage.

Azure Event Hubs can be integrated with Spark Structured Streaming to perform the processing of messages in near real-time. You can query and analyze the processed data as it comes by using a Structured Streaming query and Spark SQL.

Reference:

https://k21academy.com/microsoft-azure/data-engineer/structured-streaming-with-azure-event-hubs/

Reveal Solution Next Question

QUESTION: 92 Exam Topic: Design and develop data processing Questions

You are creating an Apache Spark job in Azure Databricks that will ingest JSON-formatted data.
You need to convert a nested JSON string into a DataFrame that will contain multiple rows.
Which Spark SQL function should you use?

explode
filter
coalesce
extract

Answer(s): A

Explanation:

Convert nested JSON to a flattened DataFrame
You can to flatten nested JSON, using only $"column.*" and explode methods.

Note: Extract and flatten
Use $"column.*" and explode methods to flatten the struct and array types before displaying the flattened DataFrame.

Scala
display(DF.select($"id" as "main_id",$"name",$"batters",$"ppu",explode($"topping")) // Exploding the topping column using explode as it is an array type
.withColumn("topping_id",$"col.id") // Extracting topping_id from col using DOT form
.withColumn("topping_type",$"col.type") // Extracting topping_tytpe from col using DOT form
.drop($"col")
.select($"*",$"batters.*") // Flattened the struct type batters tto array type which is batter
.drop($"batters")
.select($"*",explode($"batter"))
.drop($"batter")
.withColumn("batter_id",$"col.id") // Extracting batter_id from col using DOT form
.withColumn("battter_type",$"col.type") // Extracting battter_type from col using DOT form
.drop($"col")
)

Reference:

https://learn.microsoft.com/en-us/azure/databricks/kb/scala/flatten-nested-columns-dynamically

Reveal Solution Next Question

QUESTION: 93 Exam Topic: Design and develop data processing Questions

DRAG DROP (Drag and Drop is not supported)
You have an Azure subscription that contains an Azure Databricks workspace. The workspace contains a notebook named Notebook1.

In Notebook1, you create an Apache Spark DataFrame named df_sales that contains the following columns:

-Customer
-SalesPerson
-Region
-Amount

You need to identify the three top performing salespersons by amount for a region named HQ.

How should you complete the query? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

NOTE: Each correct selection is worth one point.

See Explanation section for answer.

Answer(s): A

Explanation:

Box 1: groupby(col(‘SalesPerson’))
Group by the SalesPerson.

Similar to SQL GROUP BY clause, PySpark groupBy() function is used to collect the identical data into groups on DataFrame and perform count, sum, avg, min, max functions on the grouped data.

Box 2: orderBy(desc(‘TotalAmount’))
Select the top 3.

You can use either sort() or orderBy() function of PySpark DataFrame to sort DataFrame by ascending or descending order based on single or multiple columns, you can also do sorting using PySpark SQL sorting functions.

Sort by Descending (DESC)
If you wanted to specify the sorting by descending order on DataFrame, you can use the desc method of the Column function, for example. From our example, let’s use desc on the state column.

df.sort(df.department.asc(),df.state.desc()).show(truncate=False)
df.sort(col("department").asc(),col("state").desc()).show(truncate=False)
df.orderBy(col("department").asc(),col("state").desc()).show(truncate=False)

Reference:

https://sparkbyexamples.com/pyspark/pyspark-groupby-explained-with-example/
https://sparkbyexamples.com/pyspark/pyspark-orderby-and-sort-explained/

Reveal Solution Next Question

Free DP-203 Exam Braindumps (page: 54)

QUESTION: 90 Exam Topic: Design and develop data processing Questions

Explanation:

Reference:

QUESTION: 91 Exam Topic: Design and develop data processing Questions

Explanation:

Reference:

QUESTION: 92 Exam Topic: Design and develop data processing Questions

Explanation:

Reference:

QUESTION: 93 Exam Topic: Design and develop data processing Questions

Explanation:

Reference: