Microsoft Microsoft Certifications DP-203 Questions & Answers

• Question 121:

  HOTSPOT

  You are designing a monitoring solution for a fleet of 500 vehicles. Each vehicle has a GPS tracking device that sends data to an Azure event hub once per minute.

  You have a CSV file in an Azure Data Lake Storage Gen2 container. The file maintains the expected geographical area in which each vehicle should be.

  You need to ensure that when a GPS position is outside the expected area, a message is added to another event hub for processing within 30 seconds. The solution must minimize cost.

  What should you include in the solution? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: Azure Stream Analytics

  Box 2: Hopping Hopping window functions hop forward in time by a fixed period. It may be easy to think of them as Tumbling windows that can overlap and be emitted more often than the window size. Events can belong to more than one Hopping window result set. To make a Hopping window the same as a Tumbling window, specify the hop size to be the same as the window size.

  Box 3: Point within polygon

  Reference: https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-window-functions

• Question 122:

  HOTSPOT

  You are building an Azure Analytics query that will receive input data from Azure IoT Hub and write the results to Azure Blob storage.

  You need to calculate the difference in readings per sensor per hour.

  How should you complete the query? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: LAG

  The LAG analytic operator allows one to look up a “previous” event in an event stream, within certain constraints. It is very useful for computing the rate of growth of a variable, detecting when a variable crosses a threshold, or when a

  condition starts or stops being true.

  Box 2: LIMIT DURATION

  Example: Compute the rate of growth, per sensor:

  SELECT sensorId, growth = reading LAG(reading) OVER (PARTITION BY sensorId LIMIT DURATION(hour, 1)) FROM input

  Reference: https://docs.microsoft.com/en-us/stream-analytics-query/lag-azure-stream-analytics

• Question 123:

  HOTSPOT

  You are designing a real-time dashboard solution that will visualize streaming data from remote sensors that connect to the internet. The streaming data must be aggregated to show the average value of each 10-second interval. The data will

  be discarded after being displayed in the dashboard.

  The solution will use Azure Stream Analytics and must meet the following requirements:

  1.

  Minimize latency from an Azure Event hub to the dashboard.

  2.

  Minimize the required storage.

  3.

  Minimize development effort.

  What should you include in the solution? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point

  Hot Area:

  

  Correct Answer:

  

  Reference: https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-power-bi-dashboard

• Question 124:

  HOTSPOT

  You have an Azure SQL database named Database1 and two Azure event hubs named HubA and HubB. The data consumed from each source is shown in the following table.

  

  You need to implement Azure Stream Analytics to calculate the average fare per mile by driver.

  How should you configure the Stream Analytics input for each source? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  HubA: Stream

  HubB: Stream

  Database1: Reference Reference data (also known as a lookup table) is a finite data set that is static or slowly changing in nature, used to perform a lookup or to augment your data streams. For example, in an IoT scenario, you could store metadata about sensors (which don't change often) in reference data and join it with real time IoT data streams. Azure Stream Analytics loads reference data in memory to achieve low latency stream processing

• Question 125:

  HOTSPOT

  You build an Azure Data Factory pipeline to move data from an Azure Data Lake Storage Gen2 container to a database in an Azure Synapse Analytics dedicated SQL pool.

  Data in the container is stored in the following folder structure.

  /in/{YYYY}/{MM}/{DD}/{HH}/{mm}

  The earliest folder is /in/2021/01/01/00/00. The latest folder is /in/2021/01/15/01/45.

  You need to configure a pipeline trigger to meet the following requirements:

  Existing data must be loaded.

  Data must be loaded every 30 minutes.

  Late-arriving data of up to two minutes must he included in the load for the time at which the data should have arrived.

  How should you configure the pipeline trigger? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: Tumbling window

  To be able to use the Delay parameter we select Tumbling window.

  Box 2:

  Recurrence: 30 minutes, not 32 minutes

  Delay: 2 minutes.

  The amount of time to delay the start of data processing for the window. The pipeline run is started after the expected execution time plus the amount of delay. The delay defines how long the trigger waits past the due time before triggering a

  new run. The delay doesn't alter the window startTime.

  Reference:

  https://docs.microsoft.com/en-us/azure/data-factory/how-to-create-tumbling-window-trigger

• Question 126:

  HOTSPOT

  You have a data model that you plan to implement in a data warehouse in Azure Synapse Analytics as shown in the following exhibit.

  

  All the dimension tables will be less than 2 GB after compression, and the fact table will be approximately 6 TB. Which type of table should you use for each table? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

• Question 127:

  HOTSPOT

  You have an Azure Data Lake Storage Gen2 container.

  Data is ingested into the container, and then transformed by a data integration application. The data is NOT modified after that. Users can read files in the container but cannot modify the files.

  You need to design a data archiving solution that meets the following requirements:

  1.

  New data is accessed frequently and must be available as quickly as possible.

  2.

  Data that is older than five years is accessed infrequently but must be available within one second when requested.

  3.

  Data that is older than seven years is NOT accessed. After seven years, the data must be persisted at the lowest cost possible.

  4.

  Costs must be minimized while maintaining the required availability.

  How should you manage the data? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point

  Hot Area:

  

  Correct Answer:

  

  Box 1: Move to cool storage

  Box 2: Move to archive storage

  Archive - Optimized for storing data that is rarely accessed and stored for at least 180 days with flexible latency requirements, on the order of hours.

  The following table shows a comparison of premium performance block blob storage, and the hot, cool, and archive access tiers.

  

  Reference: https://docs.microsoft.com/en-us/azure/storage/blobs/storage-blob-storage-tiers

• Question 128:

  HOTSPOT

  You are building an Azure Stream Analytics job to identify how much time a user spends interacting with a feature on a webpage.

  The job receives events based on user actions on the webpage. Each row of data represents an event. Each event has a type of either 'start' or 'end'.

  You need to calculate the duration between start and end events.

  How should you complete the query? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: DATEDIFF

  DATEDIFF function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate.

  Syntax: DATEDIFF ( datepart , startdate, enddate )

  Box 2: LAST

  The LAST function can be used to retrieve the last event within a specific condition. In this example, the condition is an event of type Start, partitioning the search by PARTITION BY user and feature. This way, every user and feature is treated

  independently when searching for the Start event. LIMIT DURATION limits the search back in time to 1 hour between the End and Start events.

  Example:

  SELECT [user], feature, DATEDIFF( second, LAST(Time) OVER (PARTITION BY [user], feature LIMIT DURATION(hour, 1) WHEN Event = 'start'), Time) as duration

  FROM input TIMESTAMP BY Time

  WHERE Event = 'end'

  

  Reference: https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-stream-analytics-query-patterns

• Question 129:

  HOTSPOT

  You use Azure Data Factory to prepare data to be queried by Azure Synapse Analytics serverless SQL pools.

  Files are initially ingested into an Azure Data Lake Storage Gen2 account as 10 small JSON files. Each file contains the same data attributes and data from a subsidiary of your company.

  You need to move the files to a different folder and transform the data to meet the following requirements:

  1.

  Provide the fastest possible query times.

  2.

  Automatically infer the schema from the underlying files.

  How should you configure the Data Factory copy activity? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: Preserver herarchy

  Compared to the flat namespace on Blob storage, the hierarchical namespace greatly improves the performance of directory management operations, which improves overall job performance.

  Box 2: Parquet

  Azure Data Factory parquet format is supported for Azure Data Lake Storage Gen2.

  Parquet supports the schema property.

  Reference:

  https://docs.microsoft.com/en-us/azure/storage/blobs/data-lake-storage-introduction

  https://docs.microsoft.com/en-us/azure/data-factory/format-parquet

• Question 130:

  HOTSPOT

  You are planning the deployment of Azure Data Lake Storage Gen2.

  You have the following two reports that will access the data lake:

  1.

  Report1: Reads three columns from a file that contains 50 columns.

  2.

  Report2: Queries a single record based on a timestamp.

  You need to recommend in which format to store the data in the data lake to support the reports. The solution must minimize read times.

  What should you recommend for each report? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Report1: CSV

  CSV: The destination writes records as delimited data.

  Report2: AVRO

  AVRO supports timestamps.

  Not Parquet, TSV: Not options for Azure Data Lake Storage Gen2.

  Reference:

  https://streamsets.com/documentation/datacollector/latest/help/datacollector/UserGuide/Destinations/ADLS-G2-D.html