Question 1

You have an Azure subscription that contains an Azure data factory named ADF1.
From Azure Data Factory Studio, you build a complex data pipeline in ADF1.
You discover that the Save button is unavailable and there are validation errors that prevent the
pipeline from being published.
You need to ensure that you can save the logic of the pipeline.
Solution: You export ADF1 as an Azure Resource Manager (ARM) template.

Accepted Answer

B

Explanation: :

Question 2

You need to implement the surrogate key for the retail store table. The solution must meet the sales transaction dataset requirements. What should you create?

Accepted Answer

A

Explanation: Scenario: Implement a surrogate key to account for changes to the retail store addresses. A surrogate key on a table is a column with a unique identifier for each row. The key is not generated from the table data. Data modelers like to create surrogate keys on their tables when they design data warehouse models. You can use the IDENTITY property to achieve this goal simply and effectively without affecting load performance. Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehousetables-identity

Question 3

HOTSPOT
You are building an Azure Stream Analytics job to identify how much time a user spends interacting
with a feature on a webpage.
The job receives events based on user actions on the webpage. Each row of data represents an
event. Each event has a type of either 'start' or 'end'.
You need to calculate the duration between start and end events.
How should you complete the query? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
DP-203 practice exam questions

Accepted Answer

Explanation: https://kxbjsyuhceggsyvxdkof.supabase.co/storage/v1/object/public/file-images/DP-203/page_85_img_1.jpg Box 1: DATEDIFF DATEDIFF function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate. Syntax: DATEDIFF ( datepart , startdate, enddate ) Box 2: LAST The LAST function can be used to retrieve the last event within a specific condition. In this example, the condition is an event of type Start, partitioning the search by PARTITION BY user and feature. This way, every user and feature is treated independently when searching for the Start event. LIMIT DURATION limits the search back in time to 1 hour between the End and Start events. Example: SELECT [user], feature, DATEDIFF( second, LAST(Time) OVER (PARTITION BY [user], feature LIMIT DURATION(hour, 1) WHEN Event = 'start'), Time) as duration FROM input TIMESTAMP BY Time WHERE Event = 'end' Reference: https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-stream-analytics-querypatterns

Question 4

You have an Azure Synapse Analytics dedicated SQL Pool1. Pool1 contains a partitioned fact table
named dbo.Sales and a staging table named stg.Sales that has the matching table and partition
definitions.
You need to overwrite the content of the first partition in dbo.Sales with the content of the same
partition in stg.Sales. The solution must minimize load times.
What should you do?

Accepted Answer

A

Explanation: :

Question 5

You need to design a data retention solution for the Twitter feed data records. The solution must
meet the customer sentiment analytics requirements.
Which Azure Storage functionality should you include in the solution?

Accepted Answer

B

Explanation: Scenario: Purge Twitter feed data records that are older than two years. Data sets have unique lifecycles. Early in the lifecycle, people access some data often. But the need for access often drops drastically as the data ages. Some data remains idle in the cloud and is rarely accessed once stored. Some data sets expire days or months after creation, while other data sets are actively read and modified throughout their lifetimes. Azure Storage lifecycle management offers a rule-based policy that you can use to transition blob data to the appropriate access tiers or to expire data at the end of the data lifecycle. Reference: https://docs.microsoft.com/en-us/azure/storage/blobs/lifecycle-management-overview

Question 6

You have an Azure Storage account and a data warehouse in Azure Synapse Analytics in the UK South
region.
You need to copy blob data from the storage account to the data warehouse by using Azure Data
Factory. The solution must meet the following requirements:
Ensure that the data remains in the UK South region at all times.
Minimize administrative effort.
Which type of integration runtime should you use?

Accepted Answer

A

Explanation: https://kxbjsyuhceggsyvxdkof.supabase.co/storage/v1/object/public/file-images/DP-203/page_95_img_1.jpg Reference: https://docs.microsoft.com/en-us/azure/data-factory/concepts-integration-runtime

Question 7

You plan to implement an Azure Data Lake Storage Gen2 container that will contain CSV files. The
size of the files will vary based on the number of events that occur per hour.
File sizes range from 4.KB to 5 GB.
You need to ensure that the files stored in the container are optimized for batch processing.
What should you do?

Accepted Answer

D

Explanation: Avro supports batch and is very relevant for streaming. Note: Avro is framework developed within Apache’s Hadoop project. It is a row-based storage format which is widely used as a serialization process. AVRO stores its schema in JSON format making it easy to read and interpret by any program. The data itself is stored in binary format by doing it compact and efficient. Reference: https://www.adaltas.com/en/2020/07/23/benchmark-study-of-different-file-format/

Question 8

You need to design a solution that will process streaming data from an Azure Event Hub and output
the data to Azure Data Lake Storage. The solution must ensure that analysts can interactively query
the streaming data.
What should you use?

Accepted Answer

C

Explanation: Apache Spark Structured Streaming is a fast, scalable, and fault-tolerant stream processing API. You can use it to perform analytics on your streaming data in near real-time. With Structured Streaming, you can use SQL queries to process streaming data in the same way that you would process static data. Azure Event Hubs is a scalable real-time data ingestion service that processes millions of data in a matter of seconds. It can receive large amounts of data from multiple sources and stream the prepared data to Azure Data Lake or Azure Blob storage. Azure Event Hubs can be integrated with Spark Structured Streaming to perform the processing of messages in near real-time. You can query and analyze the processed data as it comes by using a Structured Streaming query and Spark SQL. Reference: https://k21academy.com/microsoft-azure/data-engineer/structured-streaming-withazure-event-hubs/

Question 9

You are designing a dimension table for a data warehouse. The table will track the value of the
dimension attributes over time and preserve the history of the data by adding new rows as the data
changes.
Which type of slowly changing dimension (SCD) should use?

Accepted Answer

C

Explanation: Type 2 - Creating a new additional record. In this methodology all history of dimension changes is kept in the database. You capture attribute change by adding a new row with a new surrogate key to the dimension table. Both the prior and new rows contain as attributes the natural key(or other durable identifier). Also 'effective date' and 'current indicator' columns are used in this method. There could be only one record with current indicator set to 'Y'. For 'effective date' columns, i.e. start_date and end_date, the end_date for current record usually is set to value 9999-12-31. Introducing changes to the dimensional model in type 2 could be very expensive database operation so it is not recommended to use it in dimensions where a new attribute could be added in the future. https://www.datawarehouse4u.info/SCD-Slowly-Changing-Dimensions.html

Question 10

HOTSPOT
You have an enterprise data warehouse in Azure Synapse Analytics that contains a table named
FactOnlineSales. The table contains data from the start of 2009 to the end of 2012.
You need to improve the performance of queries against FactOnlineSales by using table partitions.
The solution must meet the following requirements:
Create four partitions based on the order date.
Ensure that each partition contains all the orders places during a given calendar year.
How should you complete the T-SQL command? To answer, select the appropriate options in the
answer area.
NOTE: Each correct selection is worth one point.
DP-203 practice exam questions

Accepted Answer

Explanation: https://kxbjsyuhceggsyvxdkof.supabase.co/storage/v1/object/public/file-images/DP-203/page_151_img_1.jpg Range Left or Right, both are creating similar partition but there is difference in comparison For example: in this scenario, when you use LEFT and 20100101,20110101,20120101 Partition will be, datecol 20100101 and datecol 20110101 and datecol 20120101 But if you use range RIGHT and 20100101,20110101,20120101 Partition will be, datecol =20100101 and datecol =20110101 and datecol =20120101 In this example, Range RIGHT will be suitable for calendar comparison Jan 1st to Dec 31st Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-partition-function-transactsql?view=sql-server-ver15

Question 11

DRAG DROP
You need to create a partitioned table in an Azure Synapse Analytics dedicated SQL pool.
How should you complete the Transact-SQL statement? To answer, drag the appropriate values to the
correct targets. Each 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.
DP-203 practice exam questions

Accepted Answer

Explanation: https://kxbjsyuhceggsyvxdkof.supabase.co/storage/v1/object/public/file-images/DP-203/page_82_img_2.jpg Box 1: DISTRIBUTION Table distribution options include DISTRIBUTION = HASH ( distribution_column_name ), assigns each row to one distribution by hashing the value stored in distribution_column_name. Box 2: PARTITION Table partition options. Syntax: PARTITION ( partition_column_name RANGE [ LEFT | RIGHT ] FOR VALUES ( [ boundary_value [,...n] ] )) Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-azure-sql-data-warehouse?

Question 12

You are designing a streaming data solution that will ingest variable volumes of data.
You need to ensure that you can change the partition count after creation.
Which service should you use to ingest the data?

Accepted Answer

A

Explanation: You can't change the partition count for an event hub after its creation except for the event hub in a dedicated cluster. Reference: https://docs.microsoft.com/en-us/azure/event-hubs/event-hubs-features

Question 13

DRAG DROP You have an Azure subscription that contains an Azure Data Lake Storage Gen2 account named storage1. Storage1 contains a container named container1. Container1 contains a directory named directory1. Directory1 contains a file named file1. You have an Azure Active Directory (Azure AD) user named User1 that is assigned the Storage Blob Data Reader role for storage1. You need to ensure that User1 can append data to file1. The solution must use the principle of least privilege. Which permissions should you grant? To answer, drag the appropriate permissions to the correct resources. Each permission 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. https://kxbjsyuhceggsyvxdkof.supabase.co/storage/v1/object/public/file-images/DP-203/page_266_img_1.jpg

Accepted Answer

Explanation: Box 1: Execute If you are granting permissions by using only ACLs (no Azure RBAC), then to grant a security principal read or write access to a file, you'll need to give the security principal Execute permissions to the root folder of the container, and to each folder in the hierarchy of folders that lead to the file. Box 2: Execute On Directory: Execute (X): Required to traverse the child items of a directory Box 3: Write On file: Write (W): Can write or append to a file. Reference: https://docs.microsoft.com/en-us/azure/storage/blobs/data-lake-storage-access-control

Question 14

DRAG DROP You need to implement versioned changes to the integration pipelines. The solution must meet the data integration requirements. In which order should you perform the actions? To answer, move all actions from the list of actions to the answer area and arrange them in the correct order. https://kxbjsyuhceggsyvxdkof.supabase.co/storage/v1/object/public/file-images/DP-203/page_12_img_1.jpg

Accepted Answer

Question 15

DRAG DROP You have an Azure Data Lake Storage Gen2 account that contains a JSON file for customers. The file contains two attributes named FirstName and LastName. You need to copy the data from the JSON file to an Azure Synapse Analytics table by using Azure Databricks. A new column must be created that concatenates the FirstName and LastName values. You create the following components: A destination table in Azure Synapse An Azure Blob storage container A service principal Which five actions should you perform in sequence next in is Databricks notebook? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order. https://kxbjsyuhceggsyvxdkof.supabase.co/storage/v1/object/public/file-images/DP-203/page_89_img_1.jpg

Accepted Answer

Explanation: 1) mount onto DBFS 2) read into data frame 3) transform data frame 4) specify temporary folder 5) write the results to table in in Azure Synapse https://docs.databricks.com/data/data-sources/azure/azure-datalake-gen2.html https://docs.microsoft.com/en-us/azure/databricks/scenarios/databricks-extract-load-sql-datawarehouse

Free Microsoft Azure DP-203 Actual Exam Questions