Microsoft Microsoft Certifications DP-203 Questions & Answers

• Question 151:

  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.

  Select and Place:

  

  Correct Answer:

  

  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 152:

  DRAG DROP

  You need to create an Azure Data Factory pipeline to process data for the following three departments at your company: Ecommerce, retail, and wholesale. The solution must ensure that data can also be processed for the entire company.

  How should you complete the Data Factory data flow script? 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.

  Select and Place:

  

  Correct Answer:

  

  The conditional split transformation routes data rows to different streams based on matching conditions. The conditional split transformation is similar to a CASE decision structure in a programming language. The transformation evaluates expressions, and based on the results, directs the data row to the specified stream.

  

  Box 1: dept=='ecommerce', dept=='retail', dept=='wholesale' First we put the condition. The order must match the stream labeling we define in Box 3.

  Syntax:

  split(

  disjoint: {true | false}

  ) ~> @(stream1, stream2, ..., )

  Box 2: discount : false

  disjoint is false because the data goes to the first matching condition. All remaining rows matching the third condition go to output stream all.

  Box 3: ecommerce, retail, wholesale, all

  Label the streams

• Question 153:

  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:

  1.

  A destination table in Azure Synapse

  2.

  An Azure Blob storage container

  3.

  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.

  Select and Place:

  

  Correct Answer:

  

  Step 1: Mount the Data Lake Storage onto DBFS

  Begin with creating a file system in the Azure Data Lake Storage Gen2 account.

  Step 2: Read the file into a data frame.

  You can load the json files as a data frame in Azure Databricks.

  Step 3: Perform transformations on the data frame.

  Step 4: Specify a temporary folder to stage the data Specify a temporary folder to use while moving data between Azure Databricks and Azure Synapse.

  Step 5: Write the results to a table in Azure Synapse.

  You upload the transformed data frame into Azure Synapse. You use the Azure Synapse connector for Azure Databricks to directly upload a dataframe as a table in a 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-data-warehouse

• Question 154:

  DRAG DROP

  You have an Azure Active Directory (Azure AD) tenant that contains a security group named Group1. You have an Azure Synapse Analytics dedicated SQL pool named dw1 that contains a schema named schema1.

  You need to grant Group1 read-only permissions to all the tables and views in schema1. The solution must use the principle of least privilege.

  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.

  NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.

  Select and Place:

  

  Correct Answer:

  

  Step 1: Create a database role named Role1 and grant Role1 SELECT permissions to schema

  You need to grant Group1 read-only permissions to all the tables and views in schema1.

  Place one or more database users into a database role and then assign permissions to the database role.

  Step 2: Assign Rol1 to the Group database user

  Step 3: Assign the Azure role-based access control (Azure RBAC) Reader role for dw1 to Group1

  Reference:

  https://docs.microsoft.com/en-us/azure/data-share/how-to-share-from-sql

• Question 155:

  DRAG DROP

  You have an Azure Stream Analytics job that is a Stream Analytics project solution in Microsoft Visual Studio. The job accepts data generated by IoT devices in the JSON format.

  You need to modify the job to accept data generated by the IoT devices in the Protobuf format.

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

  Select and Place:

  

  Correct Answer:

  

  Step 1: Add an Azure Stream Analytics Custom Deserializer Project (.NET) project to the solution. Create a custom deserializer

  1.

  Open Visual Studio and select File > New > Project. Search for Stream Analytics and select Azure Stream Analytics Custom Deserializer Project (.NET). Give the project a name, like Protobuf Deserializer.

  2.

  In Solution Explorer, right-click your Protobuf Deserializer project and select Manage NuGet Packages from the menu. Then install the Microsoft.Azure.StreamAnalytics and Google.Protobuf NuGet packages.

  3.

  Add the MessageBodyProto class and the MessageBodyDeserializer class to your project.

  4.

  Build the Protobuf Deserializer project.

  Step 2: Add .NET deserializer code for Protobuf to the custom deserializer project Azure Stream Analytics has built-in support for three data formats: JSON, CSV, and Avro. With custom .NET deserializers, you can read data from other

  formats such as Protocol Buffer, Bond and other user defined formats for both cloud and edge jobs.

  

  Step 3: Add an Azure Stream Analytics Application project to the solution Add an Azure Stream Analytics project In Solution Explorer, right-click the Protobuf Deserializer solution and select Add > New Project. Under Azure Stream Analytics > Stream Analytics, choose Azure Stream Analytics Application. Name it ProtobufCloudDeserializer and select OK. Right-click References under the ProtobufCloudDeserializer Azure Stream Analytics project. Under Projects, add Protobuf Deserializer. It should be automatically populated for you.

• Question 156:

  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.

  Select and Place:

  

  Correct Answer:

  

  Step 1: Create an Azure Storage account that has a lifecycle policy

  To automate common data management tasks, Microsoft created a solution based on Azure Data Factory. The service, Data Lifecycle Management, makes frequently accessed data available and archives or purges other data according to

  retention policies. Teams across the company use the service to reduce storage costs, improve app performance, and comply with data retention policies.

  Step 2: Create a Log Analytics workspace that has Data Retention set to 120 days.

  Data Factory stores pipeline-run data for only 45 days. Use Azure Monitor if you want to keep that data for a longer time. With Monitor, you can route diagnostic logs for analysis to multiple different targets, such as a Storage Account: Save

  your diagnostic logs to a storage account for auditing or manual inspection. You can use the diagnostic settings to specify the retention time in days.

  Step 3: From Azure Portal, add a diagnostic setting.

  Step 4: Send the data to a log Analytics workspace,

  Event Hub: A pipeline that transfers events from services to Azure Data Explorer.

  Keeping Azure Data Factory metrics and pipeline-run data.

  Configure diagnostic settings and workspace.

  Create or add diagnostic settings for your data factory.

  In the portal, go to Monitor. Select Settings > Diagnostic settings.

  Select the data factory for which you want to set a diagnostic setting.

  If no settings exist on the selected data factory, you're prompted to create a setting. Select Turn on diagnostics.

  Give your setting a name, select Send to Log Analytics, and then select a workspace from Log Analytics Workspace.

  Select Save.

  Reference:

  https://docs.microsoft.com/en-us/azure/data-factory/monitor-using-azure-monitor

• Question 157:

  You plan to create a dimension table in Azure Synapse Analytics that will be less than 1 GB.

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

  1.

  Provide the fastest Query time.

  2.

  Minimize data movement during queries.

  Which type of table should you use?

  A. hash distributed

  B. heap

  C. replicated

  D. round-robin

  Correct Answer: C

  A replicated table has a full copy of the table accessible on each Compute node. Replicating a table removes the need to transfer data among Compute nodes before a join or aggregation. Since the table has multiple copies, replicated tables work best when the table size is less than 2 GB compressed. 2 GB is not a hard limit.

  Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data- warehouse/design- guidance-for-replicated-tables

• Question 158:

  You are creating an Apache Spark job in Azure Databricks that will ingest JSON-formatted data.

  You need to convert a nested JSON string into a DataFrame that will contain multiple rows.

  Which Spark SQL function should you use?

  A. explode

  B. filter

  C. coalesce

  D. extract

  Correct Answer: A

  Convert nested JSON to a flattened DataFrame

  You can to flatten nested JSON, using only $"column.*" and explode methods.

  Note: Extract and flatten

  Use $"column.*" and explode methods to flatten the struct and array types before displaying the flattened DataFrame.

  Scala

  display(DF.select($"id" as "main_id",$"name",$"batters",$"ppu",explode($"topping")) // Exploding the topping column using explode as it is an array type withColumn("topping_id",$"col.id") // Extracting topping_id from col using DOT form

  withColumn("topping_type",$"col.type") // Extracting topping_tytpe from col using DOT form drop($"col")

  select($"*",$"batters.*") // Flattened the struct type batters tto array type which is batter drop($"batters") select($"*",explode($"batter"))

  drop($"batter")

  withColumn("batter_id",$"col.id") // Extracting batter_id from col using DOT form withColumn ("battter_type",$"col.type") // Extracting battter_type from col using DOT form drop($"col") )

  Reference: https://learn.microsoft.com/en-us/azure/databricks/kb/scala/flatten-nested- columns- dynamically

• Question 159:

  You have an Azure Synapse Analytics dedicated SQL pool named pool1.

  You plan to implement a star schema in pool1 and create a new table named DimCustomer by using the following code.

  

  You need to ensure that DimCustomer has the necessary columns to support a Type 2 slowly changing dimension (SCD).

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

  A. [HistoricalSalesPerson] [nvarchar] (256) NOT NULL

  B. [EffectiveEndDate] [datetime] NOT NULL

  C. [PreviousModifiedDate] [datetime] NOT NULL

  D. [RowID] [bigint] NOT NULL

  E. [EffectiveStartDate] [datetime] NOT NULL

  Correct Answer: BE

  "For the SCD Type 2, we need to include three more attributes such as StartDate, EndDate and IsCurrent"

  IsCurrentRow is already present! ... ;-)

  CustomerKey (in reality is the RowID that many guys wants to add here),

  effectiveEndDate will probably set to: 31.12.9999, (to justify the not null).

  https://www.sqlshack.com/implementing-slowly-changing-dimensions-scds-in-data-warehouses/

• Question 160:

  You have an Azure Data Factory pipeline named pipeline1 that is invoked by a tumbling window trigger named Trigger1. Trigger1 has a recurrence of 60 minutes.

  You need to ensure that pipeline1 will execute only if the previous execution completes successfully.

  How should you configure the self-dependency for Trigger1?

  A. offset: "-00:01:00" size: "00:01:00"

  B. offset: "01:00:00" size: "-01:00:00"

  C. offset: "01:00:00" size: "01:00:00"

  D. offset: "-01:00:00" size: "01:00:00"

  Correct Answer: D

  Tumbling window self-dependency properties

  In scenarios where the trigger shouldn't proceed to the next window until the preceding window is successfully completed, build a self-dependency. A self-dependency trigger that's dependent on the success of earlier runs of itself within the

  preceding hour will have the properties indicated in the following code.

  Example code:

  "name": "DemoSelfDependency",

  "properties": {

  "runtimeState": "Started",

  "pipeline": {

  "pipelineReference": {

  "referenceName": "Demo",

  "type": "PipelineReference"

  }

  },

  "type": "TumblingWindowTrigger",

  "typeProperties": {

  "frequency": "Hour",

  "interval": 1,

  "startTime": "2018-10-04T00:00:00Z",

  "delay": "00:01:00",

  "maxConcurrency": 50,

  "retryPolicy": {

  "intervalInSeconds": 30

  },

  "dependsOn": [

  {

  "type": "SelfDependencyTumblingWindowTriggerReference", "size": "01:00:00", "offset": "-01:00:00"

  }

  ]

  }

  }

  }

  Reference: https://docs.microsoft.com/en-us/azure/data-factory/tumbling-window-trigger-dependency