Microsoft Microsoft Certifications DP-203 Questions & Answers

• Question 91:

  HOTSPOT

  You plan to develop a dataset named Purchases by using Azure Databricks. Purchases will contain the following columns:

  1.

  ProductID

  2.

  ItemPrice

  3.

  LineTotal

  4.

  Quantity

  5.

  StoreID

  6.

  Minute

  7.

  Month

  8.

  Hour

  9.

  Year 10.Day

  You need to store the data to support hourly incremental load pipelines that will vary for each Store ID. The solution must minimize storage costs.

  How should you complete the code? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: partitionBy

  We should overwrite at the partition level.

  Example:

  df.write.partitionBy("y","m","d")

  .mode(SaveMode.Append)

  .parquet("/data/hive/warehouse/db_name.db/" + tableName)

  Box 2: ("StoreID", "Year", "Month", "Day", "Hour", "StoreID")

  Box 3: parquet("/Purchases")

  Reference:

  https://intellipaat.com/community/11744/how-to-partition-and-write-dataframe-in-spark-without-deleting-partitions-with-no-new-data

• Question 92:

  HOTSPOT

  You have an Azure Data Lake Storage Gen2 account named account1 that stores logs as shown in the following table.

  

  You do not expect that the logs will be accessed during the retention periods.

  You need to recommend a solution for account1 that meets the following requirements:

  1.

  Automatically deletes the logs at the end of each retention period

  2.

  Minimizes storage costs

  What should you include in the recommendation? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: Store the infrastructure logs in the Cool access tier and the application logs in the Archive access tier For infrastructure logs: Cool tier - An online tier optimized for storing data that is infrequently accessed or modified. Data in the cool tier should be stored for a minimum of 30 days. The cool tier has lower storage costs and higher access costs compared to the hot tier.

  For application logs: Archive tier - An offline tier optimized for storing data that is rarely accessed, and that has flexible latency requirements, on the order of hours. Data in the archive tier should be stored for a minimum of 180 days.

  Box 2: Azure Blob storage lifecycle management rules Blob storage lifecycle management offers a rule-based policy that you can use to transition your data to the desired access tier when your specified conditions are met. You can also use lifecycle management to expire data at the end of its life.

  Reference: https://docs.microsoft.com/en-us/azure/storage/blobs/access-tiers-overview

• Question 93:

  HOTSPOT - (Topic 3)

  You have an Azure subscription that contains the following resources:

  1.

  An Azure Active Directory (Azure AD) tenant that contains a security group named Group1.

  2.

  An Azure Synapse Analytics SQL pool named Pool1.

  You need to control the access of Group1 to specific columns and rows in a table in Pool1

  Which Transact-SQL commands should you use? To answer, select the appropriate options in the answer area.

  NOTE: Each appropriate options in the answer area.

  Hot Area:

  

  Correct Answer:

  

  Description automatically generated Box 1: GRANT You can implement column-level security with the GRANT T-SQL statement.

  Box 2: CREATE SECURITY POLICY Implement Row Level Security by using the CREATE SECURITY POLICY Transact-SQL statement

• Question 94:

  HOTSPOT

  You are building an Azure Synapse Analytics dedicated SQL pool that will contain a fact table for transactions from the first half of the year 2020.

  You need to ensure that the table meets the following requirements:

  1.

  Minimizes the processing time to delete data that is older than 10 years

  2.

  Minimizes the I/O for queries that use year-to-date values

  How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: PARTITION

  RANGE RIGHT FOR VALUES is used with PARTITION.

  Part 2: [TransactionDateID]

  Partition on the date column.

  Example: Creating a RANGE RIGHT partition function on a datetime column

  The following partition function partitions a table or index into 12 partitions, one for each month of a year's worth of values in a datetime column.

  CREATE PARTITION FUNCTION [myDateRangePF1] (datetime)

  AS RANGE RIGHT FOR VALUES ('20030201', '20030301', '20030401', '20030501', '20030601', '20030701', '20030801', '20030901', '20031001', '20031101', '20031201');

  Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-partition-function-transact-sql

• Question 95:

  HOTSPOT

  You are designing an Azure Stream Analytics solution that receives instant messaging data from an Azure event hub.

  You need to ensure that the output from the Stream Analytics job counts the number of messages per time zone every 15 seconds.

  How should you complete the Stream Analytics query? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: timestamp by

  Box 2: TUMBLINGWINDOW

  Tumbling window functions are used to segment a data stream into distinct time segments and perform a function against them, such as the example below. The key differentiators of a Tumbling window are that they repeat, do not overlap,

  and an event cannot belong to more than one tumbling window.

  

• Question 96:

  HOTSPOT

  You are implementing Azure Stream Analytics windowing functions.

  Which windowing function should you use for each requirement? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point

  Hot Area:

  

  Correct Answer:

  

• Question 97:

  HOTSPOT

  You store files in an Azure Data Lake Storage Gen2 container. The container has the storage policy shown in the following exhibit.

  

  Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

• Question 98:

  HOTSPOT

  Which Azure Data Factory components should you recommend using together to import the daily inventory data from the SQL server to Azure Data Lake Storage?

  To answer,select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

• Question 99:

  HOTSPOT

  You need to implement an Azure Databricks cluster that automatically connects to Azure Data Lake Storage Gen2 by using Azure Active Directory (Azure AD) integration.

  How should you configure the new cluster? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: High Concurrency

  Enable Azure Data Lake Storage credential passthrough for a high-concurrency cluster.

  Incorrect:

  Support for Azure Data Lake Storage credential passthrough on standard clusters is in Public Preview.

  Standard clusters with credential passthrough are supported on Databricks Runtime 5.5 and above and are limited to a single user.

  Box 2: Azure Data Lake Storage Gen1 Credential Passthrough You can authenticate automatically to Azure Data Lake Storage Gen1 and Azure Data Lake Storage Gen2 from Azure Databricks clusters using the same Azure Active Directory

  (Azure AD) identity that you use to log into Azure Databricks. When you enable your cluster for Azure Data Lake Storage credential passthrough, commands that you run on that cluster can read and write data in Azure Data Lake Storage

  without requiring you to configure service principal credentials for access to storage.

  References:

  https://docs.azuredatabricks.net/spark/latest/data-sources/azure/adls-passthrough.html

• Question 100:

  HOTSPOT

  You have the following Azure Stream Analytics query.

  

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

  Hot Area:

  

  Correct Answer:

  

  Box 1: Yes

  You can now use a new extension of Azure Stream Analytics SQL to specify the number of partitions of a stream when reshuffling the data.

  The outcome is a stream that has the same partition scheme. Please see below for an example:

  WITH step1 AS (SELECT * FROM [input1] PARTITION BY DeviceID INTO 10), step2 AS (SELECT * FROM [input2] PARTITION BY DeviceID INTO 10)

  SELECT * INTO [output] FROM step1 PARTITION BY DeviceID UNION step2 PARTITION BY DeviceID

  Note: The new extension of Azure Stream Analytics SQL includes a keyword INTO that allows you to specify the number of partitions for a stream when performing reshuffling using a PARTITION BY statement.

  Box 2: Yes

  When joining two streams of data explicitly repartitioned, these streams must have the same partition key and partition count.

  Box 3: Yes

  10 partitions x six SUs = 60 SUs is fine.

  Note: Remember, Streaming Unit (SU) count, which is the unit of scale for Azure Stream Analytics, must be adjusted so the number of physical resources available to the job can fit the partitioned flow. In general, six SUs is a good number to

  assign to each partition. In case there are insufficient resources assigned to the job, the system will only apply the repartition if it benefits the job.