Pass Implementing an Azure Data Solution DP-200 Exam With The Latest Study Guide

1. Topic 1, Proseware Inc



Background

Proseware, Inc, develops and manages a product named Poll Taker. The product is used for delivering public opinion polling and analysis.



Polling data comes from a variety of sources, including online surveys, house-to-house interviews, and booths at public events.



Polling data

Polling data is stored in one of the two locations:

- An on-premises Microsoft SQL Server 2019 database named PollingData

- Azure Data Lake Gen 2



Data in Data Lake is queried by using PolyBase



Poll metadata

Each poll has associated metadata with information about the poll including the date and number of respondents. The data is stored as JSON.



Phone-based polling

Security

- Phone-based poll data must only be uploaded by authorized users from authorized devices

- Contractors must not have access to any polling data other than their own

- Access to polling data must set on a per-active directory user basis



Data migration and loading

- All data migration processes must use Azure Data Factory

- All data migrations must run automatically during non-business hours

- Data migrations must be reliable and retry when needed



Performance

After six months, raw polling data should be moved to a storage account. The storage must be available in the event of a regional disaster. The solution must minimize costs.



Deployments

- All deployments must be performed by using Azure DevOps. Deployments must use templates used in multiple environments

- No credentials or secrets should be used during deployments



Reliability

All services and processes must be resilient to a regional Azure outage.



Monitoring

All Azure services must be monitored by using Azure Monitor. On-premises SQL Server performance must be monitored.



You need to ensure that phone-based poling data can be analyzed in the PollingData database.

How should you configure Azure Data Factory?

2. DRAG DROP

You need to provision the polling data storage account.

How should you configure the storage account? To answer, drag the appropriate Configuration Value to the correct Setting. Each Configuration 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.

3. DRAG DROP

You need to ensure that phone-based polling data can be analyzed in the PollingData database.

Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer are and arrange them in the correct order.

4. HOTSPOT

You need to ensure phone-based polling data upload reliability requirements are met.

How should you configure monitoring? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

5. HOTSPOT

You need to ensure that Azure Data Factory pipelines can be deployed.

How should you configure authentication and authorization for deployments? To answer, select the appropriate options in the answer choices. NOTE: Each correct selection is worth one point.

6. HOTSPOT

You need to ensure polling data security requirements are met.

Which security technologies should you use? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

7. Validate configuration results and deploy the solution

Does the solution meet the goal?

8. You need to set up Azure Data Factory pipelines to meet data movement requirements.

Which integration runtime should you use?

9. Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some questions sets might have more than one correct solution, while others might not have a correct solution.

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

You need setup monitoring for tiers 6 through 8.

What should you configure?

10. Validate configuration results and deploy the solution

Does the solution meet the goal?

11. DRAG DROP

You need to set up access to Azure SQL Database for Tier 7 and Tier 8 partners.

Which three actions should you perform in sequence? To answer, move the appropriate three actions from the list of actions to the answer area and arrange them in the correct order.

12. HOTSPOT

You need set up the Azure Data Factory JSON definition for Tier 10 data.

What should you use? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

13. HOTSPOT

You need to mask tier 1 data.

Which functions should you use? To answer, select the appropriate option in the answer area. NOTE: Each correct selection is worth one point.

14. Validate configuration results and deploy the solution

Does the solution meet the goal?

15. You need to process and query ingested Tier 9 data.

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

16. Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some questions sets might have more than one correct solution, while others might not have a

correct solution.

After you answer a question in this section, you will NOT be able to return to it. As a result, these

questions will not appear in the review screen.

You need to implement diagnostic logging for Data Warehouse monitoring.

Which log should you use?

17. Topic 3, Litware, inc



Overview

General Overview

Litware, Inc. is an international car racing and manufacturing company that has 1,000 employees. Most employees are located in Europe. The company supports racing teams that complete in a worldwide racing series.



Physical Locations

Litware has two main locations: a main office in London, England, and a manufacturing plant in Berlin, Germany.



During each race weekend, 100 engineers set up a remote portable office by using a VPN to connect the datacenter in the London office. The portable office is set up and torn down in approximately 20 different countries each year.



Existing environment

Race Central

During race weekends, Litware uses a primary application named Race Central. Each car has several sensors that send real-time telemetry data to the London datacentre. The data is used for real-time tracking of the cars.



Race Central also sends batch updates to an application named Mechanical Workflow by using Microsoft SQL Server Integration Services (SSIS).



The telemetry data is sent to a MongoDB database. A custom application then moves the data to databases in SQL Server 2017. The telemetry data in MongoDB has more than 500 attributes. The application changes the attribute names when the data is moved to SQL Server 2017.



The database structure contains both OLAP and OLTP databases.



Mechanical Workflow

Mechanical Workflow is used to track changes and improvements made to the cars during their lifetime.

Currently, Mechanical Workflow runs on SQL Server 2017 as an OLAP system.

Mechanical Workflow has a table named Table1 that is 1 TB. Large aggregations are performed on a single column of Table1.



Requirements

Planned Changes

Litware is in the process of rearchitecting its data estate to be hosted in Azure. The company plans to decommission the London datacentre and move all its applications to an Azure datacenter.



Technical Requirements

Litware identifies the following technical requirements:

- Data collection for Race Central must be moved to Azure Cosmos DB and Azure SQL Database. The data must be written to the Azure datacenter closest to each race and must converge in the least amount of time.

- The query performance of Race Central must be stable, and the administrative time it takes to perform optimizations must be minimized.

- The database for Mechanical Workflow must be moved to Azure SQL Data Warehouse.

- Transparent data encryption (TDE) must be enabled on all data stores, whenever possible.

- An Azure Data Factory pipeline must be used to move data from Cosmos DB to SQL Database for Race Central. If the data load takes longer than 20 minutes, configuration changes must be made to Data Factory.

- The telemetry data must migrate toward a solution that is native to Azure.

- The telemetry data must be monitored for performance issues. You must adjust the Cosmos DB Request Units per second (RU/s) to maintain a performance SLA while minimizing the cost of the RU/s.



Data Masking Requirements

During race weekends, visitors will be able to enter the remote portable offices. Litware is concerned that some proprietary information might be exposed.

The company identifies the following data masking requirements for the Race Central data that will be stored in SQL Database:

- Only show the last four digits of the values in a column named SuspensionSprings.

- Only show a zero value for the values in a column named ShockOilWeight.



HOTSPOT

Which masking functions should you implement for each column to meet the data masking requirements? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

18. What should you implement to optimize SQL Database for Race Central to meet the technical requirements?

19. HOTSPOT

Which masking functions should you implement for each column to meet the data masking requirements? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

20. HOTSPOT

You need to build a solution to collect the telemetry data for Race Control.

What should you use? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

21. HOTSPOT

You are building the data store solution for Mechanical Workflow.

How should you configure Table1? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

22. On which data store you configure TDE to meet the technical requirements?

23. What should you include in the Data Factory pipeline for Race Central?

24. You are monitoring the Data Factory pipeline that runs from Cosmos DB to SQL Database for Race Central.

You discover that the job takes 45 minutes to run.

What should you do to improve the performance of the job?

25. Which two metrics should you use to identify the appropriate RU/s for the telemetry data? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

26. Topic 4, ADatum Corporation



Case study

Overview

ADatum Corporation is a retailer that sells products through two sales channels: retail stores and a website.



Existing Environment

ADatum has one database server that has Microsoft SQL Server 2016 installed. The server hosts three mission-critical databases named SALESDB, DOCDB, and REPORTINGDB.



SALESDB collects data from the stored and the website.



DOCDB stored documents that connect to the sales data in SALESDB. The documents are stored in two different JSON formats based on the sales channel.



REPORTINGDB stores reporting data and contains server columnstore indexes. A daily process creates reporting data in REPORTINGDB from the data in SALESDB. The process is implemented as a SQL Server Integration Services (SSIS) package that runs a stored procedure from SALESDB.



Requirements

Planned Changes

ADatum plans to move the current data infrastructure to Azure.

The new infrastructure has the following requirements:

- Migrate SALESDB and REPORTINGDB to an Azure SQL database.

- Migrate DOCDB to Azure Cosmos DB.

- The sales data including the documents in JSON format, must be gathered as it arrives and analyzed online by using Azure Stream Analytics. The analytic process will perform aggregations that must be done continuously, without gaps, and without overlapping.

- As they arrive, all the sales documents in JSON format must be transformed into one consistent format.

- Azure Data Factory will replace the SSIS process of copying the data from SALESDB to REPORTINGDB.



Technical Requirements

The new Azure data infrastructure must meet the following technical requirements:

- Data in SALESDB must encrypted by using Transparent Data Encryption (TDE). The encryption must use your own key.

- SALESDB must be restorable to any given minute within the past three weeks.

- Real-time processing must be monitored to ensure that workloads are sized properly based on actual usage patterns.

- Missing indexes must be created automatically for REPORTINGDB.

- Disk IO, CPU, and memory usage must be monitored for SALESDB.



You need to implement event processing by using Stream Analytics to produce consistent JSON documents.

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

27. DRAG DROP

You need to replace the SSIS process by using Data Factory.

Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

28. DRAG DROP

You need to implement the encryption for SALESDB.

Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

29. Which windowing function should you use to perform the streaming aggregation of the sales data?

30. How should you monitor SALESDB to meet the technical requirements?

31. Which counter should you monitor for real-time processing to meet the technical requirements?

32. You need to implement event processing by using Stream Analytics to produce consistent JSON documents.

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

You need to ensure that the missing indexes for REPORTINGDB are added.

What should you use?

33. You need to configure a disaster recovery solution for SALESDB to meet the technical requirements.

What should you configure in the backup policy?

34. Topic 5, Misc Questions



CORRECT TEXT

Use the following login credentials as needed:

Azure Username: xxxxx

Azure Password: xxxxx

The following information is for technical support purposes only:

Lab Instance: 10543936





You need to double the available processing resources available to an Azure SQL data warehouse named datawarehouse.

To complete this task, sign in to the Azure portal. NOTE: This task might take several minutes to complete. You can perform other tasks while the task completes or end this section of the exam.

35. Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.

After you answer a question in this scenario, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

You have a container named Sales in an Azure Cosmos DB database. Sales has 120 GB of data. Each entry in Sales has the following structure.





The partition key is set to the OrderId attribute.

Users report that when they perform queries that retrieve data by ProductName, the queries take longer than expected to complete.

You need to reduce the amount of time it takes to execute the problematic queries.

Solution: You create a lookup collection that uses ProductName as a partition key.

Does this meet the goal?

36. 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?

37. You have an Azure Cosmos DB database that uses the SQL API.

You need to delete stale data from the database automatically.

What should you use?

38. You need to develop a pipeline for processing data.

The pipeline must meet the following requirements.

• Scale up and down resources for cost reduction.

• Use an in-memory data processing engine to speed up ETL and machine learning operations.

• Use streaming capabilities.

• Provide the ability to code in SQL, Python, Scala, and R.

• Integrate workspace collaboration with Git.

What should you use?

39. You develop data engineering solutions for a company. The company has on-premises Microsoft SQL Server databases at multiple locations.

The company must integrate data with Microsoft Power BI and Microsoft Azure Logic Apps.

The solution must avoid single points of failure during connection and transfer to the cloud.

The solution must also minimize latency.

You need to secure the transfer of data between on-premises databases and Microsoft Azure.

What should you do?

40. DRAG DROP

You plan to create a new single database instance of Microsoft Azure SQL Database.

The database must only allow communication from the data engineer’s workstation. You must connect directly to the instance by using Microsoft SQL Server Management Studio.

You need to create and configure the Database.

Which three Azure PowerShell cmdlets should you use to develop the solution? To answer, move the appropriate cmdlets from the list of cmdlets to the answer area and arrange them in the correct order.