DP-200 practice test

1. Question 1 · 1

   HOTSPOT - You have an Azure SQL database that contains a table named Employee. Employee contains sensitive data in a decimal (10,2) column named Salary. You need to ensure that nonprivileged users can view the table data, but Salary must display a number from 0 to 100. What should you configure? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Hot Area:

   Choose an option for each prompt.

   

   Permission:

   • • CONNECT
   • • SELECT
   • • UNMASK

   Mask:

   • • Custom text
   • • Default
   • • Random number

   Read explanation

   Box 1: SELECT - Users with SELECT permission on a table can view the table data. Columns that are defined as masked, will display the masked data. Incorrect: Grant the UNMASK permission to a user to enable them to retrieve unmasked data from the columns for which masking is defined. The CONTROL permission on the database includes both the ALTER ANY MASK and UNMASK permission. Box 2: Random number - Random number: Masking method, which generates a random number according to the selected boundaries and actual data types. If the designated boundaries are equal, then the masking function is a constant number.

2. Question 2 · 1

   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?

   Choose one answer.

   • Round-robin distributed table
   • Hash-distributed table
   • Replicated table
   • External table

3. Question 3 · 10

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

   Choose one answer.

   • 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

   Read explanation

   Scenario: Tiers 6 through 8 must have unexpected resource storage usage immediately reported to data engineers. 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.

4. Question 4 · 11

   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.

   Choose all answers that apply.

   • Number of requests
   • Number of requests exceeded capacity
   • End to end observed read latency at the 99 th percentile
   • Session consistency
   • Data + Index storage consumed
   • Avg Throughput/s

   Read explanation

   Scenario: 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. With Azure Cosmos DB, you pay for the throughput you provision and the storage you consume on an hourly basis. While you estimate the number of RUs per second to provision, consider the following factors: Item size: As the size of an item increases, the number of RUs consumed to read or write the item also increases.

5. Question 5 · 13

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

   Choose one answer.

   • Cosmos DB
   • Azure Synapse Analytics
   • Azure SQL Database

   Read explanation

   Scenario: Transparent data encryption (TDE) must be enabled on all data stores, whenever possible. The database for Mechanical Workflow must be moved to Azure Synapse Analytics. Incorrect Answers: A: Cosmos DB does not support TDE.

6. Question 6 · 14

   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.

   Choose all answers that apply.

   • Define an output to Cosmos DB.
   • 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.

   Read explanation

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

7. Question 7 · 3

   You have an Azure SQL server named Server1 that hosts two development databases named DB1 and DB2. You have an administrative workstation that has an IP address of 192.168.8.8. The development team at your company has an IP addresses in the range of 192.168.8.1 to 192.168.8.5. You need to set up firewall rules to meet the following requirements: ✑ Allows connection from your workstation to both databases. ✑ The development team must be able connect to DB1 but must be prevented from connecting to DB2. ✑ Web services running in Azure must be able to connect to DB1 but must be prevented from connecting to DB2. Which three actions should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

   Choose all answers that apply.

   • Create a firewall rule on DB1 that has a start IP address of 192.168.8.1 and an end IP address of 192.168.8.5.
   • Create a firewall rule on DB1 that has a start and end IP address of 0.0.0.0.
   • Create a firewall rule on Server1 that has a start IP address of 192.168.8.1 and an end IP address of 192.168.8.5.
   • Create a firewall rule on DB1 that has a start and end IP address of 192.168.8.8.
   • Create a firewall rule on Server1 that has a start and end IP address of 192.168.8.8.

8. Question 8 · 3

   You have an Azure Blob storage account. The storage account has an alert that is configured to indicate when the Availability metric falls below 100 percent. You receive an alert for the Availability metric. The logs for the storage account show that requests are failing because of a ServerTimeoutError error. What does ServerTimeoutError indicate?

   Choose one answer.

   • Read and write storage requests exceeded capacity.
   • A transient server timeout occurred while the service was moved to a different partition to load balance requests.
   • A client application attempted to perform an operation and did not have valid credentials.
   • There was excessive network latency between a client application and the storage account.

9. Question 9 · 6

   A company has an Azure SQL data warehouse. They want to use PolyBase to retrieve data from an Azure Blob storage account and ingest into the Azure SQL data warehouse. The files are stored in parquet format. The data needs to be loaded into a table called XYZ_sales. Which of the following actions need to be performed to implement this requirement? (Choose four.)

   Choose all answers that apply.

   • Create an external file format that would map to the parquet-based files
   • Load the data into a staging table
   • Create an external table called XYZ_sales_details
   • Create an external data source for the Azure Blob storage account
   • Create a master key on the database
   • Configure Polybase to use the Azure Blob storage account

   Read explanation

   There is an article on github as part of the Microsoft documentation that provides details on how to load data into an Azure SQL data warehouse from an Azure Blob storage account. The key steps are: Creating a master key in the database. Creating an external data source for the Azure Blob storage account: 3. Create a master key for the MySampleDataWarehouse database. You only need to create a master key once per database. CREATE MASTER KEY; 4. Run the following CREATE EXTERNAL DATA SOURCE statement to define the location of the Azure blob. This is the location of the external taxi cab data. To run a command that you have appended to the query window, highlight the commands you wish to run and click Execute. Next you load the data. But it is always beneficial to load the data into a staging table first: Load the data into your data warehouse. This section uses the external tables you just defined to load the sample data from Azure Storage Blob to SQL Data Warehouse. [!NOTE] This tutorial loads the data directly into the final table. In a production environment, you will usually use CREATE TABLE AS SELECT to load into a staging table. While data is in the staging table you can perform any necessary transformations. To append the data in the staging table to a production table, you can use the INSERT...SELECT statement. For more information, see Inserting data into a production table. Since this is clearly provided in the documentation, all other options are incorrect.

Paid access to all 9 questions

Each purchase applies to this certification. Prices are one-time payments, not monthly subscriptions.