Implementing an Azure Data Solution DP-200 Exam Questions Updated

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

How should you configure Azure Data Factory?

Use a tumbling schedule trigger

Use an event-based trigger

Use a schedule trigger

Use manual execution

2. 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.

3. Topic 2, Contoso Ltd

Overview

Current environment

Contoso relies on an extensive partner network for marketing, sales, and distribution. Contoso uses external companies that manufacture everything from the actual pharmaceutical to the packaging.

The majority of the company’s data reside in Microsoft SQL Server database.

Application databases fall into one of the following tiers:

The company has a reporting infrastructure that ingests data from local databases and partner services. Partners services consists of distributors, wholesales, and retailers across the world. The company performs daily, weekly, and monthly reporting.

Requirements

Tier 3 and Tier 6 through Tier 8 application must use database density on the same server and Elastic pools in a cost-effective manner.

Applications must still have access to data from both internal and external applications keeping the data encrypted and secure at rest and in transit.

A disaster recovery strategy must be implemented for Tier 3 and Tier 6 through 8 allowing for failover in the case of server going offline.

Selected internal applications must have the data hosted in single Microsoft Azure SQL Databases.

– Tier 1 internal applications on the premium P2 tier

– Tier 2 internal applications on the standard S4 tier

The solution must support migrating databases that support external and internal application to Azure SQL Database. The migrated databases will be supported by Azure Data Factory pipelines for the continued movement, migration and updating of data both in the cloud and from local core business systems and repositories.

Tier 7 and Tier 8 partner access must be restricted to the database only.

In addition to default Azure backup behavior, Tier 4 and 5 databases must be on a backup strategy that performs a transaction log backup eve hour, a differential backup of databases every day and a full back up every week.

Back up strategies must be put in place for all other standalone Azure SQL Databases using Azure SQL-provided backup storage and capabilities.

Databases

Contoso requires their data estate to be designed and implemented in the Azure Cloud. Moving to the cloud must not inhibit access to or availability of data.

Databases:

Tier 1 Database must implement data masking using the following masking logic:

Tier 2 databases must sync between branches and cloud databases and in the event of conflicts must be set up for conflicts to be won by on-premises databases.

Tier 3 and Tier 6 through Tier 8 applications must use database density on the same server and Elastic pools in a cost-effective manner.

Applications must still have access to data from both internal and external applications keeping the data encrypted and secure at rest and in transit.

A disaster recovery strategy must be implemented for Tier 3 and Tier 6 through 8 allowing for failover in the case of a server going offline.

Selected internal applications must have the data hosted in single Microsoft Azure SQL Databases.

– Tier 1 internal applications on the premium P2 tier

– Tier 2 internal applications on the standard S4 tier

Reporting

Security and monitoring

Security

A method of managing multiple databases in the cloud at the same time is must be implemented to streamlining data management and limiting management access to only those requiring access.

Monitoring

Monitoring must be set up on every database. Contoso and partners must receive performance reports as part of contractual agreements.

Tiers 6 through 8 must have unexpected resource storage usage immediately reported to data engineers.

The Azure SQL Data Warehouse cache must be monitored when the database is being used.

A dashboard monitoring key performance indicators (KPIs) indicated by traffic lights must be created and displayed based on the following metrics:

Existing Data Protection and Security compliances require that all certificates and keys are internally managed in an on-premises storage.

You identify the following reporting requirements:

– Azure Data Warehouse must be used to gather and query data from multiple internal and external databases

– Azure Data Warehouse must be optimized to use data from a cache

– Reporting data aggregated for external partners must be stored in Azure Storage and be made available during regular business hours in the connecting regions

– Reporting strategies must be improved to real time or near real time reporting cadence to improve competitiveness and the general supply chain

– Tier 9 reporting must be moved to Event Hubs, queried, and persisted in the same Azure region as the company’s main office

– Tier 10 reporting data must be stored in Azure Blobs

Issues

Team members identify the following issues:

– Both internal and external client application run complex joins, equality searches and group-by clauses. Because some systems are managed externally, the queries will not be changed or optimized by Contoso

– External partner organization data formats, types and schemas are controlled by the partner companies

– Internal and external database development staff resources are primarily SQL developers familiar with the Transact-SQL language.

– Size and amount of data has led to applications and reporting solutions not performing are required speeds

– Tier 7 and 8 data access is constrained to single endpoints managed by partners for access

– The company maintains several legacy client applications. Data for these applications remains isolated form other applications. This has led to hundreds of databases being provisioned on a per application basis

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

Which integration runtime should you use?

self-hosted integration runtime

Azure-SSIS Integration Runtime

.NET Common Language Runtime (CLR)

Azure integration runtime

4. Validate configuration results and deploy the solution

Does the solution meet the goal?

Yes

No

5. Validate configuration results and deploy the solution

Does the solution meet the goal?

Yes

No

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

extended events for average storage percentage that emails data engineers

an alert rule to monitor CPU percentage in databases that emails data engineers

an alert rule to monitor CPU percentage in elastic pools that emails data engineers

an alert rule to monitor storage percentage in databases that emails data engineers

an alert rule to monitor storage percentage in elastic pools that emails data engineers

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

RequestSteps

DmsWorkers

SqlRequests

ExecRequests

8. 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.

9. 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.

10. 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.

11. 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.

Azure Notification Hub

Transact-SQL statements

Azure Cache for Redis

Apache Kafka statements

Azure Event Grid

Azure Stream Analytics

12. Validate configuration results and deploy the solution

Does the solution meet the goal?

Yes

No

13. 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.

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?

Decrease parallelism for the copy activities.

Increase that data integration units.

Configure the copy activities to use staged copy.

Configure the copy activities to perform compression.

14. 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.

15. 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.

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

the sp_update stored procedure

automatic tuning

Query Store

the dbcc checkdb command

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

a copy activity that uses a stored procedure as a source

a copy activity that contains schema mappings

a delete activity that has logging enabled

a filter activity that has a condition

18. 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.

19. 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.

20. 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.

Number of requests

Number of requests exceeded capacity

End to end observed read latency at the 99th percentile

Session consistency

Data + Index storage consumed

Avg Troughput/s

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

Cosmos DB

SQL Data Warehouse

SQL Database

22. 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.

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

What should you use?

SQL Database Advisor

extended events

Query Performance Insight

automatic tuning

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

Concurrent users

SU% Utilization

Data Conversion Errors

CPU % utilization

24. 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.

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

Query the sys.resource_stats dynamic management view.

Review the Query Performance Insights for SALESD

Query the sys.dm_os_wait_stats dynamic management view.

Review the auditing information of SALESD

26. 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.

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

Tumbling

Hopping

Sliding

Session

28. 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.

Define an output to Cosmos D

Define a query that contains a JavaScript user-defined aggregates (UDA) function.

Define a reference input.

Define a transformation query.

Define an output to Azure Data Lake Storage Gen2.

Define a stream input.

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

What should you configure in the backup policy?

weekly long-term retention backups that are retained for three weeks

failover groups

a point-in-time restore

geo-replication

30. Topic 5, Misc Questions

You use Azure Stream Analytics to receive Twitter data from Azure Event Hubs and to output the data to an Azure Blob storage account.

You need to output the count of tweets during the last five minutes every five minutes. Each tweet must only be counted once.

Which windowing function should you use?

a five-minute Session window

a five-minute Sliding window

a five-minute Tumbling window

a five-minute Hopping window that has one-minute hop

31. 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 plan to create an Azure Databricks workspace that has a tiered structure.

The workspace will contain the following three workloads:

✑ A workload for data engineers who will use Python and SQL

✑ A workload for jobs that will run notebooks that use Python, Spark, Scala, and SQL

✑ A workload that data scientists will use to perform ad hoc analysis in Scala and R

The enterprise architecture team at your company identifies the following standards for Databricks environments:

✑ The data engineers must share a cluster.

✑ The job cluster will be managed by using a request process whereby data scientists and data engineers provide packaged notebooks for deployment to the cluster.

✑ All the data scientists must be assigned their own cluster that terminates automatically after 120 minutes of inactivity. Currently, there are three data scientists.

You need to create the Databrick clusters for the workloads.

Solution: You create a Standard cluster for each data scientist, a Standard cluster for the data engineers, and a High Concurrency cluster for the jobs.

Does this meet the goal?

Yes

No

32. You have a SQL pool in Azure Synapse that contains a table named dbo.Customers. The table contains 9 column name Email.

You need to prevent nonadministrative users from seeing the full email addresses in the Email column. The users must see values in a format of [email protected] instead.

What should you do?

From Microsoft SQL Server Management Studio, set an email mask on the Email column.

From the Azure portal, set a mask on the Email column.

From Microsoft SQL Server Management studio, grant the SELECT permission to the users for all the columns in the dbo.Customers table except Email.

From the Azure portal, set a sensitivity classification of Confidential for the Email column.

33. 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 ensure that you can recover any blob data from an Azure Storage account named storage10543936 up to 10 days after the data is deleted.

To complete this task, sign in to the Azure portal.

34. You have an enterprise data warehouse in Azure Synapse Analytics.

You need to monitor the data warehouse to identify whether you must scale up to a higher service level to accommodate the current workloads.

Which is the best metric to monitor? More than one answer choice may achieve the goal. Select the BEST answer.

CPU percentage

DWU used

DWU percentage

Data IO percentage

35. You are designing an enterprise data warehouse in Azure Synapse Analytics. You plan to load millions of rows of data into the data warehouse each day.

You must ensure that staging tables are optimized for data loading.

You need to design the staging tables.

What type of tables should you recommend?

Round-robin distributed table

Hash-distributed table

Replicated table

External table

36. HOTSPOT

You are implementing mapping data flows in Azure Data Factory to convert daily logs of taxi records into aggregated datasets.

You configure a data flow and receive the error shown in the following exhibit.

You need to resolve the error.

Which setting should you configure? To answer, select the appropriate setting in the answer area.

37. 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 are developing a solution that will use Azure Stream Analytics. The solution will accept an Azure Blob storage file named Customers. The file will contain both in-store and online customer details. The online customers will provide a mailing address.

You have a file in Blob storage named LocationIncomes that contains median incomes based on location. The file rarely changes.

You need to use an address to look up a median income based on location. You must output the data to Azure SQL Database for immediate use and to Azure Data Lake Storage Gen2 for long-term retention.

Solution: You implement a Stream Analytics job that has two streaming inputs, one query, and two outputs.

Does this meet the goal?

Yes

No

38. 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: 10277521

You need to create an Azure SQL database named db3 on an Azure SQL server named SQL10277521. Db3 must use the Sample (AdventureWorksLT) source.

To complete this task, sign in to the Azure portal.

39. DRAG DROP

You have an Azure data factory.

You need to ensure that pipeline-run data is retained for 120 days. The solution must ensure that you can query the data by using the Kusto query language.

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. NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.

40. 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: 10277521

You need to ensure that an email notification is sent to [email protected] if a suspicious login to an Azure SQL database named db2 is detected.

To complete this task, sign in to the Azure portal.

41. You develop data engineering solutions for a company.

You need to ingest and visualize real-time Twitter data by using Microsoft Azure.

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

Event Grid topic

Azure Stream Analytics Job that queries Twitter data from an Event Hub

Azure Stream Analytics Job that queries Twitter data from an Event Grid

Logic App that sends Twitter posts which have target keywords to Azure

Event Grid subscription

Event Hub instance

42. You manage a solution that uses Azure HDInsight clusters.

You need to implement a solution to monitor cluster performance and status.

Which technology should you use?

Azure HDInsight .NET SDK

Azure HDInsight REST API

Ambari REST API

Azure Log Analytics

Ambari Web UI

43. You have a SQL pool in Azure Synapse.

You discover that some queries fail or take a long time to complete.

You need to monitor for transactions that have rolled back.

Which dynamic management view should you query?

sys.dm_pdw_nodes_tran_database_transactions

sys.dm_pdw_waits

sys.dm_pdw_request_steps

sys.dm_pdw_exec_sessions

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

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

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

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

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

45. 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: 10277521

You need to replicate db1 to a new Azure SQL server named REPL10277521 in the Central Canada region.

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 ends this section of the exam.

To complete this task, sign in to the Azure portal.

46. DRAG DROP

You have an Azure SQL database named DB1 in the Each US 2 region.

You need to build a secondary geo-replicated copy of DB1 in the West US region on a new server.

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.

47. You develop data engineering solutions for a company.

A project requires the deployment of data to Azure Data Lake Storage.

You need to implement role-based access control (RBAC) so that project members can manage the Azure Data Lake Storage resources.

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

Assign Azure AD security groups to Azure Data Lake Storage.

Configure end-user authentication for the Azure Data Lake Storage account.

Configure service-to-service authentication for the Azure Data Lake Storage account.

Create security groups in Azure Active Directory (Azure AD) and add project members.

Configure access control lists (ACL) for the Azure Data Lake Storage account.

48. 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.

49. HOTSPOT

You need to collect application metrics, streaming query events, and application log messages for an Azure Databrick cluster.

Which type of library and workspace should you implement? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

50. 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: 10277521

You need to generate an email notification to [email protected] if the available storage in an Azure Cosmos DB database named cosmos10277521 is less than 100,000,000 bytes.

To complete this task, sign in to the Azure portal.

51. You have an enterprise data warehouse in Azure Synapse Analytics named DW1 on a server named Server1.

You need to verify whether the size of the transaction log file for each distribution of DW1 is smaller than 160 GB.

What should you do?

On the master database, execute a query against the sys.dm_pdw_nodes_os_performance_counters dynamic management view.

From Azure Monitor in the Azure portal, execute a query against the logs of DW1.

On DW1, execute a query against the sys.database_files dynamic management view.

Execute a query against the logs of DW1 by using the Get-AzOperationalInsightSearchResult PowerShell cmdlet.

52. HOTSPOT

You are processing streaming data from vehicles that pass through a toll booth.

You need to use Azure Stream Analytics to return the license plate, vehicle make, and hour the last vehicle passed during each 10-minute window.

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

53. 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 have an Azure subscription that contains an Azure Storage account.

You plan to implement changes to a data storage solution to meet regulatory and compliance standards.

Every day, Azure needs to identify and delete blobs that were NOT modified during the last 100 days.

Solution: You apply an Azure Blob storage lifecycle policy.

Does this meet the goal?

Yes

No

54. Load the data using the INSERT…SELECT statement

Does the solution meet the goal?

Yes

No

55. You are creating a new notebook in Azure Databricks that will support R as the primary language but will also support Scola and SQL.

Which switch should you use to switch between languages?

%<language>

\[<language>]

\(<language>)

@<Language>

56. DRAG DROP

Your company plans to create an event processing engine to handle streaming data from Twitter.

The data engineering team uses Azure Event Hubs to ingest the streaming data.

You need to implement a solution that uses Azure Databricks to receive the streaming data from the Azure Event Hubs.

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

57. DRAG DROP

You are developing a solution to visualize multiple terabytes of geospatial data.

The solution has the following requirements:

• Data must be encrypted.

• Data must be accessible by multiple resources on Microsoft Azure.

You need to provision storage for the solution.

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

58. DRAG DROP

You are responsible for providing access to an Azure Data Lake Storage Gen2 account.

Your user account has contributor access to the storage account, and you have the application ID access key. You plan to use PolyBase to load data into Azure SQL data warehouse.

You need to configure PolyBase to connect the data warehouse to the storage account.

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

59. 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 increase the Request Units (RUs) for the database.

Does this meet the goal?

Yes

No

60. 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 an Azure Storage account that contains 100 GB of files. The files contain text

and numerical values. 75% of the rows contain description data that has an average length of 1.1 MB.

You plan to copy the data from the storage account to an enterprise data warehouse in Azure Synapse Analytics.

You need to prepare the files to ensure that the data copies quickly.

Solution: You convert the files to compressed delimited text files.

Does this meet the goal?

Yes

No

61. 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 change the partition key to include ProductName.

Does this meet the goal?

Yes

No

62. 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: 10277521

You plan to query db3 to retrieve a list of sales customers. The query will retrieve several columns that include the email address of each sales customer.

You need to modify db3 to ensure that a portion of the email addresses is hidden in the query results.

To complete this task, sign in to the Azure portal.

63. HOTSPOT

You have an Azure SQL database that contains a table named Customer.

Customer contains the columns shown in the following table.

You plan to implement a dynamic data mask for the Customer_Phone column.

The mask must meet the following requirements:

✑ The first six numerals of the customer phone numbers must be masked.

✑ The last four digits of the customer phone numbers must be visible.

✑ Hyphens must be preserved and displayed.

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

64. A company plans to use Azure SQL Database to support a mission-critical application.

The application must be highly available without performance degradation during maintenance windows.

You need to implement the solution.

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

Premium service tier

Virtual machine Scale Sets

Basic service tier

SQL Data Sync

Always On availability groups

Zone-redundant configuration

65. You plan to create a dimension table in Azure Data Warehouse that will be less than 1 GB.

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

Provide the fastest query time.

Minimize data movement.

Which type of table should you use?

hash distributed

heap

replicated

round-robin

66. 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

Your company’s compliance policy states that administrators must be able to review a list of the database object changes that occurred in an Azure SQL database named db2 during the last 100 days.

You need to modify your Azure environment to meet the compliance policy requirements.

To complete this task, sign in to the Azure portal.

67. You manage an enterprise data warehouse in Azure Synapse Analytics.

Users report slow performance when they run commonly used queries. Users do not report

performance changes for infrequently used queries.

You need to monitor resource utilization to determine the source of the performance issues.

Which metric should you monitor?

Data Warehouse Units (DWU) used

DWU limit

Cache hit percentage

Data IO percentage

68. HOTSPOT

You have an Azure data factory that has two pipelines named PipelineA and PipelineB.

PipelineA has four activities as shown in the following exhibit.

PipelineB has two activities as shown in the following exhibit.

You create an alert for the data factory that uses Failed pipeline runs metrics for both pipelines and all failure types.

The metric has the following settings:

✑ Operator: Greater than

✑ Aggregation type: Total

✑ Threshold value: 2

✑ Aggregation granularity (Period): 5 minutes

✑ Frequency of evaluation: Every 5 minutes

Data Factory monitoring records the failures shown in the following table.

For each of the following statements, select yes if the statement is true. Otherwise, select no. NOTE: Each correct answer selection is worth one point.

69. You have a SQL pool in Azure Synapse.

A user reports that queries against the pool take longer than expected to complete.

You need to add monitoring to the underlying storage to help diagnose the issue.

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

Cache used percentage

DWU Limit

Snapshot Storage Size

Active queries

Cache hit percentage

70. You plan to build a structured streaming solution in Azure Databricks. The solution will count new events in five-minute intervals and report only events that arrive during the interval. The output will be sent to a Delta Lake table.

Which output mode should you use?

complete

update

append

71. 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 plan to create an Azure Databricks workspace that has a tiered structure.

The workspace will contain the following three workloads:

✑ A workload for data engineers who will use Python and SQL

✑ A workload for jobs that will run notebooks that use Python, Spark, Scala, and SQL

✑ A workload that data scientists will use to perform ad hoc analysis in Scala and R

The enterprise architecture team at your company identifies the following standards for Databricks environments:

✑ The data engineers must share a cluster.

✑ The job cluster will be managed by using a request process whereby data scientists and data engineers provide packaged notebooks for deployment to the cluster.

✑ All the data scientists must be assigned their own cluster that terminates automatically after 120 minutes of inactivity. Currently, there are three data scientists.

You need to create the Databrick clusters for the workloads.

Solution: You create a High Concurrency cluster for each data scientist, a High Concurrency cluster for the data engineers, and a Standard cluster for the jobs.

Does this meet the goal?

Yes

No

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

Azure Cosmos DB

Azure Event Hubs

Azure Blob storage

Azure IoT Hub

73. HOTSPOT

A company is deploying a service-based data environment. You are developing a solution to process this data.

The solution must meet the following requirements:

✑ Use an Azure HDInsight cluster for data ingestion from a relational database in a different cloud service

✑ Use an Azure Data Lake Storage account to store processed data

✑ Allow users to download processed data

You need to recommend technologies for the solution.

Which technologies should you use? To answer, select the appropriate options in the answer area.

74. An application will use Microsoft Azure Cosmos DB as its data solution. The application will use the Cassandra API to support a column-based database type that uses containers to store items.

You need to provision Azure Cosmos DB.

Which container name and item name should you use? Each correct answer presents part of the solutions. NOTE: Each correct answer selection is worth one point.

table

collection

graph

entities

rows

75. You develop data engineering solutions for a company.

You must integrate the company’s on-premises Microsoft SQL Server data with Microsoft Azure SQL

Database. Data must be transformed incrementally.

You need to implement the data integration solution.

Which tool should you use to configure a pipeline to copy data?

Use the Copy Data tool with Blob storage linked service as the source

Use Azure PowerShell with SQL Server linked service as a source

Use Azure Data Factory UI with Blob storage linked service as a source

Use the .NET Data Factory API with Blob storage linked service as the source

76. You have an Azure SQL database that contains a table named Customer. Customer.

Contains the columns shown in the following table.

You apply a masking rule as shown in the following table.

Which users can view the email addresses of the customers?

Server administrators and all users who are granted the UNMASK permission to the Customer_Email column only.

All users who are granted the UNMASK permission to the Customer_Email column only.

Server administrators only.

Server administrators and all users who are granted the SELECT permission to the Customer_Email column only.

77. 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.

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.

78. 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.

79. 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.

80. 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.

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

How should you configure Azure Data Factory?