Microsoft Microsoft Certified: Azure Data Engineer Associate DP-203 Questions & Answers

• Question 301:

  You are designing an Azure Stream Analytics solution that will analyze Twitter data.

  You need to count the tweets in each 10-second window. The solution must ensure that each tweet is counted only once.

  Solution: You use a tumbling window, and you set the window size to 10 seconds.

  Does this meet the goal?

  A. Yes

  B. No

  Correct Answer: A

  Tumbling windows are a series of fixed-sized, non-overlapping and contiguous time intervals. The following diagram illustrates a stream with a series of events and how they are mapped into 10-second tumbling windows.

  

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

• Question 302:

  You are developing a solution that will stream to Azure Stream Analytics. The solution will have both streaming data and reference data. Which input type should you use for the reference data?

  A. Azure Cosmos DB

  B. Azure Blob storage

  C. Azure IoT Hub

  D. Azure Event Hubs

  Correct Answer: B

  Stream Analytics supports Azure Blob storage and Azure SQL Database as the storage layer for Reference Data.

  Reference: https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-use-reference-data

• Question 303:

  You are designing an Azure Stream Analytics job to process incoming events from sensors in retail environments.

  You need to process the events to produce a running average of shopper counts during the previous 15 minutes, calculated at five-minute intervals.

  Which type of window should you use?

  A. snapshot

  B. tumbling

  C. hopping

  D. sliding

  Correct Answer: C

  

  Unlike tumbling windows, hopping windows model scheduled overlapping windows. A hopping window specification consist of three parameters: the timeunit, the windowsize (how long each window lasts) and the hopsize (by how much each window moves forward relative to the previous one).

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

• Question 304:

  You are designing an Azure Databricks table. The table will ingest an average of 20 million streaming events per day.

  You need to persist the events in the table for use in incremental load pipeline jobs in Azure Databricks. The solution must minimize storage costs and incremental load times.

  What should you include in the solution?

  A. Partition by DateTime fields.

  B. Sink to Azure Queue storage.

  C. Include a watermark column.

  D. Use a JSON format for physical data storage.

  Correct Answer: B

  A. Partition by DataTime field

  Each partition will generate a file. Loading latency may reduce, but feel storage cost will increase because generate more folders and files for different partition. Is it right???

  B. Sink to Azure Queue Storage.

  Read this document. Spark table files are stored in DBFS. Mount Azure Blob storage containers to Databricks File System (DBFS). If The Databricks ABS-AQS provides these two benefits, sounds like it is a correct answer.

  https://docs.microsoft.com/en-us/azure/databricks/spark/latest/structured-streaming/aqs

  C. Include a watermark column: For sure it is not correct

  Watermarks define how long your aggregate should wait around for data delay.

  D. User a Json format for physical data storage. - ???

  Don't find any documents to compare physical data storage of JSON, CSV, and Parquet.

  The Databricks ABS-AQS connector uses Azure Queue Storage (AQS) to provide an optimized file source that lets you find new files written to an Azure Blob storage (ABS) container without repeatedly listing all of the files. This provides two major advantages:

  1.

  Lower latency: no need to list nested directory structures on ABS, which is slow and resource intensive.

  2.

  Lower costs: no more costly LIST API requests made to ABS.

  Reference: https://docs.microsoft.com/en-us/azure/databricks/spark/latest/structured-streaming/aqs

• Question 305:

  You need to schedule an Azure Data Factory pipeline to execute when a new file arrives in an Azure Data Lake Storage Gen2 container. Which type of trigger should you use?

  A. on-demand

  B. tumbling window

  C. schedule

  D. storage event

  Correct Answer: D

  Event-driven architecture (EDA) is a common data integration pattern that involves production, detection, consumption, and reaction to events. Data integration scenarios often require Data Factory customers to trigger pipelines based on events happening in storage account, such as the arrival or deletion of a file in Azure Blob Storage account.

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

• Question 306:

  You have two Azure Data Factory instances named ADFdev and ADFprod. ADFdev connects to an Azure DevOps Git repository.

  You publish changes from the main branch of the Git repository to ADFdev.

  You need to deploy the artifacts from ADFdev to ADFprod.

  What should you do first?

  A. From ADFdev, modify the Git configuration.

  B. From ADFdev, create a linked service.

  C. From Azure DevOps, create a release pipeline.

  D. From Azure DevOps, update the main branch.

  Correct Answer: C

  In Azure Data Factory, continuous integration and delivery (CI/CD) means moving Data Factory pipelines from one environment (development, test, production) to another.

  Note:

  The following is a guide for setting up an Azure Pipelines release that automates the deployment of a data factory to multiple environments.

  1.

  In Azure DevOps, open the project that's configured with your data factory.

  2.

  On the left side of the page, select Pipelines, and then select Releases.

  3.

  Select New pipeline, or, if you have existing pipelines, select New and then New release pipeline.

  4.

  In the Stage name box, enter the name of your environment.

  5.

  Select Add artifact, and then select the git repository configured with your development data factory. Select the publish branch of the repository for the Default branch. By default, this publish branch is adf_publish.

  6.

  Select the Empty job template.

  Reference: https://docs.microsoft.com/en-us/azure/data-factory/continuous-integration-deployment

• Question 307:

  You have an Azure Stream Analytics job that receives clickstream data from an Azure event hub.

  You need to define a query in the Stream Analytics job. The query must meet the following requirements:

  Count the number of clicks within each 10-second window based on the country of a visitor. Ensure that each click is NOT counted more than once.

  How should you define the Query?

  A. SELECT Country, Avg(*) AS Average FROM ClickStream TIMESTAMP BY CreatedAt GROUP BY Country, SlidingWindow(second, 10)

  B. SELECT Country, Count(*) AS Count FROM ClickStream TIMESTAMP BY CreatedAt GROUP BY Country, TumblingWindow(second, 10)

  C. SELECT Country, Avg(*) AS Average FROM ClickStream TIMESTAMP BY CreatedAt GROUP BY Country, HoppingWindow(second, 10, 2)

  D. SELECT Country, Count(*) AS Count FROM ClickStream TIMESTAMP BY CreatedAt GROUP BY Country, SessionWindow(second, 5, 10)

  Correct Answer: B

  Tumbling window functions are used to segment a data stream into distinct time segments and perform a function against them, such as the example below. The key differentiators of a Tumbling window are that they repeat, do not overlap,

  and an event cannot belong to more than one tumbling window.

  Example:

  Incorrect Answers:

  A: Sliding windows, unlike Tumbling or Hopping windows, output events only for points in time when the content of the window actually changes. In other words, when an event enters or exits the window. Every window has at least one event, like in the case of Hopping windows, events can belong to more than one sliding window.

  C: 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, so 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.

  D: Session windows group events that arrive at similar times, filtering out periods of time where there is no data.

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

• Question 308:

  You have files and folders in Azure Data Lake Storage Gen2 for an Azure Synapse workspace as shown in the following exhibit.

  

  You create an external table named ExtTable that has LOCATION='/topfolder/'.

  When you query ExtTable by using an Azure Synapse Analytics serverless SQL pool, which files are returned?

  A. File2.csv and File3.csv only

  B. File1.csv and File4.csv only

  C. File1.csv, File2.csv, File3.csv, and File4.csv

  D. File1.csv only

  Correct Answer: B

  To run a T-SQL query over a set of files within a folder or set of folders while treating them as a single entity or rowset, provide a path to a folder or a pattern (using wildcards) over a set of files or folders.

  Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/query-data-storage#query-multiple-files-or-folders

• Question 309:

  You are designing the folder structure for an Azure Data Lake Storage Gen2 container.

  Users will query data by using a variety of services including Azure Databricks and Azure Synapse Analytics serverless SQL pools. The data will be secured by subject area. Most queries will include data from the current year or current

  month.

  Which folder structure should you recommend to support fast queries and simplified folder security?

  A. /{SubjectArea}/{DataSource}/{DD}/{MM}/{YYYY}/{FileData}_{YYYY}_{MM}_{DD}.csv

  B. /{DD}/{MM}/{YYYY}/{SubjectArea}/{DataSource}/{FileData}_{YYYY}_{MM}_{DD}.csv

  C. /{YYYY}/{MM}/{DD}/{SubjectArea}/{DataSource}/{FileData}_{YYYY}_{MM}_{DD}.csv

  D. /{SubjectArea}/{DataSource}/{YYYY}/{MM}/{DD}/{FileData}_{YYYY}_{MM}_{DD}.csv

  Correct Answer: D

  There's an important reason to put the date at the end of the directory structure. If you want to lock down certain regions or subject matters to users/groups, then you can easily do so with the POSIX permissions. Otherwise, if there was a need to restrict a certain security group to viewing just the UK data or certain planes, with the date structure in front a separate permission would be required for numerous directories under every hour directory. Additionally, having the date structure in front would exponentially increase the number of directories as time went on.

  Note: In IoT workloads, there can be a great deal of data being landed in the data store that spans across numerous products, devices, organizations, and customers. It's important to pre-plan the directory layout for organization, security, and efficient processing of the data for down-stream consumers. A general template to consider might be the following layout:

  {Region}/{SubjectMatter(s)}/{yyyy}/{mm}/{dd}/{hh}/

• Question 310:

  You have a table in an Azure Synapse Analytics dedicated SQL pool. The table was created by using the following Transact-SQL statement.

  

  You need to alter the table to meet the following requirements:

  Ensure that users can identify the current manager of employees.

  Support creating an employee reporting hierarchy for your entire company.

  Provide fast lookup of the managers' attributes such as name and job title.

  Which column should you add to the table?

  A. [ManagerEmployeeID] [int] NULL

  B. [ManagerEmployeeID] [smallint] NULL

  C. [ManagerEmployeeKey] [int] NULL

  D. [ManagerName] [varchar](200) NULL

  Correct Answer: C

  We need an extra column to identify the Manager. Use the data type as the EmployeeKey column, an int column.

  Reference: https://docs.microsoft.com/en-us/analysis-services/tabular-models/hierarchies-ssas-tabular