Jan-2022 Microsoft DP-201 Actual Questions and 100% Cover Real Exam Questions [Q16-Q36]

Jan-2022 Microsoft DP-201 Actual Questions and 100% Cover Real Exam Questions

DP-201 Free Exam Questions & Answers PDF Updated on Jan-2022

Certification Path

The Microsoft Certified Azure Data Engineer Associate Certification include DP-200 and DP-201 exams.

The benefit in Obtaining the DP-201 Exam Certification

After completion of Microsoft Certified Azure Data Engineer, Associate Certification candidates receive official confirmation from Microsoft that you are now fully certified in their chosen field. This can be now added to their CV, cover letters and job applications.
Becoming Microsoft Certified Azure Data Engineer Associate means one thing you are worth more to the company and therefore more to yourself in the form of an upgraded pay package. On average a Microsoft Certified Azure Data Engineer Associate member of staff is estimated to be worth 30% more to a company than their uncertified professionals.
Organization owners invest a lot in their employees when it comes to their training with the goal of making them quicker, more efficient, and more knowledgeable about their role. Certified Professional will reduce the time he spends on tasks, meaning he can get more done this could help reduce company downtime when repairing faults on a system or fixing hardware problems.
Candidates will get in-depth knowledge by completing the courses along with the access to revision materials for 6 months upon completion means they will have a wider skill set when it comes to the various technologies and systems than an uncertified professional. Certified Professional in this particular skill set is 74% more efficient when it comes to completing their tasks in a timely well-executed manner.
When Candidates applying for a job or looking to promotion in their current position, a Microsoft Certified Azure Data Engineer Associate certification in the field in which Candidates are applying will put you at the top of the list and make them a desirable candidate for employers.

NEW QUESTION 16
You are designing a data storage solution for a database that is expected to grow to 50 TB. The usage pattern is singleton inserts, singleton updates, and reporting.
Which storage solution should you use?

A. Azure Cosmos DB that uses the Gremlin API
B. Azure SQL Data Warehouse
C. Azure SQL Database Hyperscale
D. Azure SQL. Database elastic pools

Answer: C

Explanation:
Explanation
A Hyperscale database is an Azure SQL database in the Hyperscale service tier that is backed by the Hyperscale scale-out storage technology. A Hyperscale database supports up to 100 TB of data and provides high throughput and performance, as well as rapid scaling to adapt to the workload requirements. Scaling is transparent to the application - connectivity, query processing, etc. work like any other Azure SQL database.

NEW QUESTION 17
You need to design storage for the solution.
Which storage services should you recommend? To answer, select the appropriate configuration in the answer area.
NOTE: Each correct selection is worth one point.

Answer:

Explanation:

Explanation

Images: Azure Data Lake Storage
Scenario: Image data must be stored in a single data store at minimum cost.
Customer data: Azure Blob Storage
Scenario: Customer data must be analyzed using managed Spark clusters.
Spark clusters in HDInsight are compatible with Azure Storage and Azure Data Lake Storage.
Azure Storage includes these data services: Azure Blob, Azure Files, Azure Queues, and Azure Tables.
References:
https://docs.microsoft.com/en-us/azure/hdinsight/spark/apache-spark-overview

NEW QUESTION 18
You have a data model that you plan to implement in an Azure SQL data warehouse as shown in the following exhibit.

All the dimension tables will be less than 5 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.

Answer:

Explanation:

Explanation

Box 1: Replicated
Replicated tables are ideal for small star-schema dimension tables, because the fact table is often distributed on a column that is not compatible with the connected dimension tables. If this case applies to your schema, consider changing small dimension tables currently implemented as round-robin to replicated.
Box 2: Replicated
Box 3: Replicated
Box 4: Hash-distributed
For Fact tables use hash-distribution with clustered columnstore index. Performance improves when two hash tables are joined on the same distribution column.
References:
https://azure.microsoft.com/en-us/updates/reduce-data-movement-and-make-your-queries-more-efficient-with-th
https://azure.microsoft.com/en-us/blog/replicated-tables-now-generally-available-in-azure-sql-data-warehouse/

NEW QUESTION 19
You are designing a solution for a company. You plan to use Azure Databricks.
You need to recommend workloads and tiers to meet the following requirements:
* Provide managed clusters for running production jobs.
* Provide persistent clusters that support auto-scaling for analytics processes.
* Provide role-based access control (RBAC) support for Notebooks.
What should you recommend? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Answer:

Explanation:

Explanation:
Box 1: Data Engineering Only
Box 2: Data Engineering and Data Analytics
Box 3: Standard
Box 4: Data Analytics only
Box 5: Premium
Premium required for RBAC. Data Analytics Premium Tier provide interactive workloads to analyze data collaboratively with notebooks References:
https://azure.microsoft.com/en-us/pricing/details/databricks/

NEW QUESTION 20
Inventory levels must be calculated by subtracting the current day's sales from the previous day's final inventory.
Which two options provide Litware with the ability to quickly calculate the current inventory levels by store and product? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.

A. Consume the output of the event hub by using Azure Stream Analytics and aggregate the data by store and product. Output the resulting data into Databricks. Calculate the inventory levels in Databricks and output the data to Azure Blob storage.
B. Consume the output of the event hub by using Azure Stream Analytics and aggregate the data by store and product. Output the resulting data directly to Azure SQL Data Warehouse. Use Transact-SQL to calculate the inventory levels.
C. Consume the output of the event hub by using Databricks. Use Databricks to calculate the inventory levels and output the data to Azure SQL Data Warehouse.
D. Output Event Hubs Avro files to Azure Blob storage. Trigger an Azure Data Factory copy activity to run every 10 minutes to load the data into Azure SQL Data Warehouse. Use Transact-SQL to aggregate the data by store and product.
E. Output Event Hubs Avro files to Azure Blob storage. Use Transact-SQL to calculate the inventory levels by using PolyBase in Azure SQL Data Warehouse.

Answer: B,D

Explanation:
A: Azure Stream Analytics is a fully managed service providing low-latency, highly available, scalable complex event processing over streaming data in the cloud. You can use your Azure SQL Data Warehouse database as an output sink for your Stream Analytics jobs.
E: Event Hubs Capture is the easiest way to get data into Azure. Using Azure Data Lake, Azure Data Factory, and Azure HDInsight, you can perform batch processing and other analytics using familiar tools and platforms of your choosing, at any scale you need.
Note: Event Hubs Capture creates files in Avro format.
Captured data is written in Apache Avro format: a compact, fast, binary format that provides rich data structures with inline schema. This format is widely used in the Hadoop ecosystem, Stream Analytics, and Azure Data Factory.
Scenario: The application development team will create an Azure event hub to receive real-time sales data, including store number, date, time, product ID, customer loyalty number, price, and discount amount, from the point of sale (POS) system and output the data to data storage in Azure.
Reference:
https://docs.microsoft.com/bs-latn-ba/azure/sql-data-warehouse/sql-data-warehouse-integrate-azure-stream- analytics
https://docs.microsoft.com/en-us/azure/event-hubs/event-hubs-capture-overview Design data processing solutions Question Set 7

NEW QUESTION 21
You need to ensure that security policies for the unauthorized detection system are met.
What should you recommend? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Answer:

Explanation:

Explanation

Box 1: Blob storage
Configure blob storage for audit logs.
Scenario: Unauthorized usage of the Planning Assistance data must be detected as quickly as possible.
Unauthorized usage is determined by looking for an unusual pattern of usage.
Data used for Planning Assistance must be stored in a sharded Azure SQL Database.
Box 2: Web Apps
SQL Advanced Threat Protection (ATP) is to be used.
One of Azure's most popular service is App Service which enables customers to build and host web applications in the programming language of their choice without managing infrastructure. App Service offers auto-scaling and high availability, supports both Windows and Linux. It also supports automated deployments from GitHub, Visual Studio Team Services or any Git repository. At RSA, we announced that Azure Security Center leverages the scale of the cloud to identify attacks targeting App Service applications.
References:
https://azure.microsoft.com/sv-se/blog/azure-security-center-can-identify-attacks-targeting-azure-app-service-app

NEW QUESTION 22
You are planning the deployment of two separate Azure Cosmos DB databases named db1 and db2.
You need to recommend a deployment strategy that meets the following requirements:
* Costs for both databases must be minimized.
* Db1 must meet an SLA of 99.99% for both reads and writes.
* Db2 must meet an SLA of 99.99% for writes and 99.999% for reads.
Which deployment strategy should you recommend for each database? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Answer:

Explanation:

Explanation

Db1: A single read/write region
Db2: A single write region and multi read regions

References:
https://docs.microsoft.com/en-us/azure/cosmos-db/high-availability

NEW QUESTION 23
You need to ensure that performance requirements for Backtrack reports are met.
What should you recommend? To answer, drag the appropriate technologies to the correct locations. Each technology 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.

Answer:

Explanation:

Explanation

Box 1: Cosmos DB indexes
The report for Backtrack must execute as quickly as possible.
You can override the default indexing policy on an Azure Cosmos container, this could be useful if you want to tune the indexing precision to improve the query performance or to reduce the consumed storage.
Box 2: Cosmos DB TTL
This solution reports on all data related to a specific vehicle license plate. The report must use data from the SensorData collection. Users must be able to filter vehicle data in the following ways:
* vehicles on a specific road
* vehicles driving above the speed limit
Note: With Time to Live or TTL, Azure Cosmos DB provides the ability to delete items automatically from a container after a certain time period. By default, you can set time to live at the container level and override the value on a per-item basis. After you set the TTL at a container or at an item level, Azure Cosmos DB will automatically remove these items after the time period, since the time they were last modified.

NEW QUESTION 24
You design data engineering solutions for a company that has locations around the world. You plan to deploy a large set of data to Azure Cosmos DB.
The data must be accessible from all company locations.
You need to recommend a strategy for deploying the data that minimizes latency for data read operations and minimizes costs.
What should you recommend?

A. Use a single Azure Cosmos DB account. Enable multi-region writes.
B. Use multiple Azure Cosmos DB accounts. Enable multi-region writes.
C. Use a single Azure Cosmos DB account Configure data replication.
D. Use a single Azure Cosmos DB account. Enable geo-redundancy.
E. Use multiple Azure Cosmos DB accounts. For each account, configure the location to the closest Azure datacenter.

Answer: A

Explanation:
Explanation
With Azure Cosmos DB, you can add or remove the regions associated with your account at any time.
Multi-region accounts configured with multiple-write regions will be highly available for both writes and reads. Regional failovers are instantaneous and don't require any changes from the application.

References:
https://docs.microsoft.com/en-us/azure/cosmos-db/high-availability

NEW QUESTION 25
You plan to deploy an Azure SQL Database instance to support an application. You plan to use the DTU-based purchasing model.
Backups of the database must be available for 30 days and point-in-time restoration must be possible.
You need to recommend a backup and recovery policy.
What are two possible ways to achieve the goal? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.

A. Use the Basic tier and the default backup retention policy.
B. Use the Standard tier and the default backup retention policy.
C. Use the Standard tier and configure a long-term backup retention policy.
D. Use the Premium tier and the default backup retention policy.
E. Use the Premium tier and configure a long-term backup retention policy.

Answer: C,E

Explanation:
The default retention period for a database created using the DTU-based purchasing model depends on the service tier:
* Basic service tier is 1 week.
* Standard service tier is 5 weeks.
* Premium service tier is 5 weeks.
Incorrect Answers:
B: Basic tier only allows restore points within 7 days.
Reference:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-long-term-retention

NEW QUESTION 26
You need to design the Planning Assistance database.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
NOTE: Each correct selection is worth one point.

Answer:

Explanation:

Explanation

Box 1: No
Data used for Planning Assistance must be stored in a sharded Azure SQL Database.
Box 2: Yes
Box 3: Yes
Planning Assistance database will include reports tracking the travel of a single vehicle

NEW QUESTION 27
You need to recommend a storage solution to store flat files and columnar optimized files. The solution must meet the following requirements:
* Store standardized data that data scientists will explore in a curated folder.
* Ensure that applications cannot access the curated folder.
* Store staged data for import to applications in a raw folder.
* Provide data scientists with access to specific folders in the raw folder and all the content the curated folder.
Which storage solution should you recommend?

A. Azure Data Lake Storage Gen2
B. Azure Blob storage
C. Azure SQL Database
D. Azure Synapse Analytics

Answer: B

Explanation:
Azure Blob Storage containers is a general purpose object store for a wide variety of storage scenarios. Blobs are stored in containers, which are similar to folders.
Incorrect Answers:
C: Azure Data Lake Storage is an optimized storage for big data analytics workloads.
Reference:
https://docs.microsoft.com/en-us/azure/architecture/data-guide/technology-choices/data-storage

NEW QUESTION 28
You are planning a design pattern based on the Kappa architecture as shown in the exhibit.

Which Azure service should you use for each layer? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Answer:

Explanation:

References:
https://docs.microsoft.com/en-us/azure/architecture/data-guide/big-data/

NEW QUESTION 29
You are designing an Azure SQL Data Warehouse for a financial services company. Azure Active Directory will be used to authenticate the users.
You need to ensure that the following security requirements are met:
* Department managers must be able to create new database.
* The IT department must assign users to databases.
* Permissions granted must be minimized.
Which role memberships should you recommend? To answer, drag the appropriate roles to the correct groups. Each role 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.

Answer:

Explanation:

Explanation:
Box 1: dbmanager
Members of the dbmanager role can create new databases.
Box 2: db_accessadmin
Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.
References:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins

NEW QUESTION 30
You are designing an Azure Cosmos DB database that will support vertices and edges.
Which Cosmos DB API should you include in the design?

A. Gremlin
B. SQL
C. Table
D. Cassandra

Answer: A

Explanation:
Explanation
The Azure Cosmos DB Gremlin API can be used to store massive graphs with billions of vertices and edges.
References:
https://docs.microsoft.com/en-us/azure/cosmos-db/graph-introduction

NEW QUESTION 31
You need to ensure that emergency road response vehicles are dispatched automatically.
How should you design the processing system? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Answer:

Explanation:

Explanation

Box1: API App

* Events generated from the IoT data sources are sent to the stream ingestion layer through Azure HDInsight Kafka as a stream of messages. HDInsight Kafka stores streams of data in topics for a configurable of time.
* Kafka consumer, Azure Databricks, picks up the message in real time from the Kafka topic, to process the data based on the business logic and can then send to Serving layer for storage.
* Downstream storage services, like Azure Cosmos DB, Azure SQL Data warehouse, or Azure SQL DB, will then be a data source for presentation and action layer.
* Business analysts can use Microsoft Power BI to analyze warehoused data. Other applications can be built upon the serving layer as well. For example, we can expose APIs based on the service layer data for third party uses.
Box 2: Cosmos DB Change Feed
Change feed support in Azure Cosmos DB works by listening to an Azure Cosmos DB container for any changes. It then outputs the sorted list of documents that were changed in the order in which they were modified.
The change feed in Azure Cosmos DB enables you to build efficient and scalable solutions for each of these patterns, as shown in the following image:

References:
https://docs.microsoft.com/bs-cyrl-ba/azure/architecture/example-scenario/data/realtime-analytics-vehicle-iot?vie

NEW QUESTION 32
You are planning the deployment of two separate Azure Cosmos DB databases named db1 and db2.
You need to recommend a deployment strategy that meets the following requirements:
* Costs for both databases must be minimized.
* Db1 must meet an SLA of 99.99% for both reads and writes.
* Db2 must meet an SLA of 99.99% for writes and 99.999% for reads.
Which deployment strategy should you recommend for each database? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Answer:

Explanation:

Explanation

Db1: A single read/write region
Db2: A single write region and multi read regions

References:
https://docs.microsoft.com/en-us/azure/cosmos-db/high-availability

NEW QUESTION 33
You are designing an audit strategy for an Azure SQL Database environment.
You need to recommend a solution to provide real-time notifications for potential security breaches. The solution must minimize development effort.
Which destination should you include in the recommendation?

A. Azure Log Analytics
B. Azure Event Hubs
C. Azure SQL Data Warehouse
D. Azure Blob storage

Answer: A

Explanation:
Explanation
Auditing for Azure SQL Database and SQL Data Warehouse tracks database events and writes them to an audit log in your Azure storage account, Log Analytics workspace or Event Hubs.
Alerts in Azure Monitor can identify important information in your Log Analytics repository. They are created by alert rules that automatically run log searches at regular intervals, and if results of the log search match particular criteria, then an alert record is created and it can be configured to perform an automated response.
References:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-auditing
https://docs.microsoft.com/en-us/azure/azure-monitor/learn/tutorial-response

NEW QUESTION 34
You discover that the highest chance of corruption or bad data occurs during nightly inventory loads.
You need to ensure that you can quickly restore the data to its state before the nightly load and avoid missing any streaming data.
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.

Answer:

Explanation:

Explanation

Step 1: Before the nightly load, create a user-defined restore point
SQL Data Warehouse performs a geo-backup once per day to a paired data center. The RPO for a geo-restore is 24 hours. If you require a shorter RPO for geo-backups, you can create a user-defined restore point and restore from the newly created restore point to a new data warehouse in a different region.
Step 2: Restore the data warehouse to a new name on the same server.
Step 3: Swap the restored database warehouse name.
References:
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/backup-and-restore
Topic 3, Case study 2
This is a case study. Case studies are not timed separately. You can use as much exam time as you would like to complete each case. However, there may be additional case studies and sections on this exam. You must manage your time to ensure that you are able to complete all questions included on this exam in the time provided.
To answer the questions included in a case study, you will need to reference information that is provided in the case study. Case studies might contain exhibits and other resources that provide more information about the scenario that is described in the case study. Each question is independent of the other question on this case study.
At the end of this case study, a review screen will appear. This screen allows you to review your answers and to make changes before you move to the next sections of the exam. After you begin a new section, you cannot return to this section.
To start the case study
To display the first question on this case study, click the button. Use the buttons in the left pane to explore the content of the case study before you answer the questions. Clicking these buttons displays information such as business requirements, existing environment, and problem statements. If the case study has an All Information tab, note that the information displayed is identical to the information displayed on the subsequent tabs. When you are ready to answer a question, click the button to return to the question.
Background
Current environment
The company has the following virtual machines (VMs):

Requirements
Storage and processing
You must be able to use a file system view of data stored in a blob.
You must build an architecture that will allow Contoso to use the DB FS filesystem layer over a blob store.
The architecture will need to support data files, libraries, and images. Additionally, it must provide a web-based interface to documents that contain runnable command, visualizations, and narrative text such as a notebook.
CONT_SQL3 requires an initial scale of 35000 IOPS.
CONT_SQL1 and CONT_SQL2 must use the vCore model and should include replicas. The solution must support 8000 IOPS.
The storage should be configured to optimized storage for database OLTP workloads.
Migration
* You must be able to independently scale compute and storage resources.
* You must migrate all SQL Server workloads to Azure. You must identify related machines in the on-premises environment, get disk size data usage information.
* Data from SQL Server must include zone redundant storage.
* You need to ensure that app components can reside on-premises while interacting with components that run in the Azure public cloud.
* SAP data must remain on-premises.
* The Azure Site Recovery (ASR) results should contain per-machine data.
Business requirements
* You must design a regional disaster recovery topology.
* The database backups have regulatory purposes and must be retained for seven years.
* CONT_SQL1 stores customers sales data that requires ETL operations for data analysis. A solution is required that reads data from SQL, performs ETL, and outputs to Power BI. The solution should use managed clusters to minimize costs. To optimize logistics, Contoso needs to analyze customer sales data to see if certain products are tied to specific times in the year.
* The analytics solution for customer sales data must be available during a regional outage.
Security and auditing
* Contoso requires all corporate computers to enable Windows Firewall.
* Azure servers should be able to ping other Contoso Azure servers.
* Employee PII must be encrypted in memory, in motion, and at rest. Any data encrypted by SQL Server must support equality searches, grouping, indexing, and joining on the encrypted data.
* Keys must be secured by using hardware security modules (HSMs).
* CONT_SQL3 must not communicate over the default ports
Cost
* All solutions must minimize cost and resources.
* The organization does not want any unexpected charges.
* The data engineers must set the SQL Data Warehouse compute resources to consume 300 DWUs.
* CONT_SQL2 is not fully utilized during non-peak hours. You must minimize resource costs for during non-peak hours.

NEW QUESTION 35
You discover that the highest chance of corruption or bad data occurs during nightly inventory loads.
You need to ensure that you can quickly restore the data to its state before the nightly load and avoid missing any streaming data.
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.