DP-203 practice questions 161–170

1. Question 161 · 370 in bank

   HOTSPOT - You have an Azure Synapse Analytics pipeline named pipeline1 that has concurrency set to 1. To run pipeline1, you create a new trigger as 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.

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   

   Next question

2. Question 162 · 370 in bank

   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 placed 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. Hot Area:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   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, datecol>20100101 and datecol<=20110101, datecol>20110101 and datecol<=20120101, datecol>20120101 But if you use range RIGHT and 20100101,20110101,20120101 Partition will be, datecol<20100101, datecol>=20100101 and datecol<20110101, datecol>=20110101 and datecol<20120101, 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-transact-sql?view=sql-server-ver15

   Source: Adapted from community discussion — rzeng

   References

   • Microsoft Learn

   

   Next question

3. Question 163 · 370 in bank

   HOTSPOT - You have an Azure Data Factory pipeline that has the logic flow shown in the following exhibit.

   

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

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   

   Next question

4. Question 164 · 370 in bank

   DRAG DROP - You have an Azure subscription that contains an Azure Synapse Analytics workspace named Workspace1. Workspace1 contains an Apache Spark pool named spark1 and a pipeline named Pipeline1. You need to add an activity to Pipeline1 that will run a notebook. The solution must ensure that the activity overrides the value of a variable named inputFile when the notebook runs. Which five actions should you perform in sequence in Synapse Studio? To answer move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Actions in Order: Add a new notebook that declares and initializes inputFile. Create a notebook in Synapse Studio that includes logic and initializes the inputFile variable. Select Toggle parameter cell. Mark inputFile as a parameter by toggling the parameter cell in the notebook. This step ensures the variable is configurable and can accept external input. Publish the notebook. Save and publish the notebook to make it available for execution in the pipeline. Drag an activity from the Synapse section to Pipeline1 and link the activity to spark1. Add a Notebook activity from the Synapse section to the pipeline and configure it to use the published notebook. Link the activity to the spark1 Spark pool. Configure the Base parameters settings. Configure the Base parameters in the Notebook activity to override the value of the inputFile parameter at runtime.

   Source: Adapted from community discussion — a6cb3b0

   

   Next question

5. Question 165 · 370 in bank

   Select 2

   You need to implement a Type 3 slowly changing dimension (SCD) for product category data in an Azure Synapse Analytics dedicated SQL pool. You have a table that was created by using the following Transact-SQL statement.

   

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

   • A[EffectiveStartDate] [datetime] NOT NULL,
   • B[CurrentProductCategory] [nvarchar] (100) NOT NULL,
   • C[EffectiveEndDate] [datetime] NULL,
   • D[ProductCategory] [nvarchar] (100) NOT NULL,
   • E[OriginalProductCategory] [nvarchar] (100) NOT NULL,

   Show answer

   Answer: BE

   Explanation

   Why can't the name of the current ProductCategory be just "ProductCategory"? I would say that D and E could be also correct.

   Source: Adapted from community discussion — lukeonline

   References

   • Microsoft Learn
   • Microsoft Learn

   Next question

6. Question 166 · 370 in bank

   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 are designing an Azure Stream Analytics solution that will analyze Twitter data. You need to count the tweets in each 10-second window. The solution must ensure that each tweet is counted only once. Solution: You use a hopping window that uses a hop size of 10 seconds and a window size of 10 seconds. Does this meet the goal?

   • AYes
   • BNo

   Show answer

   Answer: A

   Explanation

   Unlike tumbling windows, hopping windows model scheduled overlapping windows. A hopping window specification consist of three parameters: the timeunit, the windowsize (how long each window lasts) and the hopsize (by how much each window moves forward relative to the previous one). Additionally, offsetsize may be used as an optional fourth parameter. Note that a tumbling window is simply a hopping window whose ‘hop’ is equal to its ‘size’.

   Source: Adapted from community discussion — medsimus

   References

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   Next question

7. Question 167 · 370 in bank

   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 are designing an Azure Stream Analytics solution that will analyze Twitter data. You need to count the tweets in each 10-second window. The solution must ensure that each tweet is counted only once. Solution: You use a hopping window that uses a hop size of 5 seconds and a window size 10 seconds. Does this meet the goal?

   • AYes
   • BNo

   Show answer

   Answer: B

   Explanation

   No, the hop size is not equal to window size, and to make a Hopping window the same as a Tumbling window, specify the hop size to be the same as the window size.

   Source: Adapted from community discussion — eng1

   References

   • Microsoft Learn

   Next question

8. Question 168 · 370 in bank

   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. Hot Area:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   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-query-patterns

   Source: Adapted from community discussion — vlad888

   References

   • Microsoft Learn
   • Microsoft Learn

   

   Next question

9. Question 169 · 370 in bank

   Select 2

   You are creating an Azure Data Factory data flow that will ingest data from a CSV file, cast columns to specified types of data, and insert the data into a table in an Azure Synapse Analytic dedicated SQL pool. The CSV file contains three columns named username, comment, and date. The data flow already contains the following: ✑ A source transformation. ✑ A Derived Column transformation to set the appropriate types of data. ✑ A sink transformation to land the data in the pool. You need to ensure that the data flow meets the following requirements: ✑ All valid rows must be written to the destination table. ✑ Truncation errors in the comment column must be avoided proactively. ✑ Any rows containing comment values that will cause truncation errors upon insert must be written to a file in blob storage. Which two actions should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

   • ATo the data flow, add a sink transformation to write the rows to a file in blob storage.
   • BTo the data flow, add a Conditional Split transformation to separate the rows that will cause truncation errors.
   • CTo the data flow, add a filter transformation to filter out rows that will cause truncation errors.
   • DAdd a select transformation to select only the rows that will cause truncation errors.

   Show answer

   Answer: AB

   Explanation

   You are right ! The job could be done by C only, BUT ! it has been asked for two(2) actions. No choice to add a SPLIT then another sink where to drop the bad records

   Source: Adapted from community discussion — Marcus1612

   References

   • Microsoft Learn

   Next question

10. Question 170 · 370 in bank

    Select 2

    A company has a real-time data analysis solution that is hosted on Microsoft Azure. The solution uses Azure Event Hub to ingest data and an Azure Stream Analytics cloud job to analyze the data. The cloud job is configured to use 120 Streaming Units (SU). You need to optimize performance for the Azure Stream Analytics job. Which two actions should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

    • AImplement event ordering.
    • BImplement Azure Stream Analytics user-defined functions (UDF).
    • CImplement query parallelization by partitioning the data output.
    • DScale the SU count for the job up.
    • EScale the SU count for the job down.
    • FImplement query parallelization by partitioning the data input.

    Show answer

    Answer: CF

    Explanation

    Should always aim for Embarrassingly parallel jobs (partitioning input, job and output) https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-parallelization Upping the computing power of a resource (SUs in this case) should never be classed as 'optimisation' like the question asks.

    Source: Adapted from community discussion — Jaws1990

    References

    • Microsoft Learn
    • Microsoft Learn

    Next question

Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page

DP-203 practice, question 161. 370 questions available.