DP-203 practice questions 111–120

1. Question 111 · 370 in bank

   HOTSPOT - You have an Azure data factory named adf1 that contains a pipeline named ExecProduct. ExecProduct contains a data flow named Product. The Product data flow contains the following transformations: 1. WeeklyData: A source that points to a CSV file in an Azure Data Lake Storage Gen2 account with 20 columns 2. ProductColumns: A select transformation that selects from WeeklyData six columns named ProductID, ProductDescr, ProductSubCategory, ProductCategory, ProductStatus, and ProductLastUpdated 3. ProductRows: An aggregate transformation 4. ProductList: A sink that outputs data to an Azure Synapse Analytics dedicated SQL pool The Aggregate settings for ProductRows are configured as 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

   Explanation

   The irs no new aggregated measurement. You're grouping by ProductID, so that makes one column. All the other columns that are not 'ProductID' are grouped by choosing their first row. So you will obtain the same number of columns, like a SELECT DISTINCT.

   Source: Adapted from community discussion — Elxaxe

   References

   • Microsoft Learn

   

   Next question

2. Question 112 · 370 in bank

   You manage an enterprise data warehouse in Azure Synapse Analytics. Users report slow performance when they run commonly used queries. Users do not report performance changes for infrequently used queries. You need to monitor resource utilization to determine the source of the performance issues. Which metric should you monitor?

   • ADWU limit
   • BCache hit percentage
   • CLocal tempdb percentage
   • DData IO percentage

   Show answer

   Answer: B

   Explanation

   B. Cache hit percentage should be correct since it only affects common used queries, which should be saved and loaded from cache.

   Source: Adapted from community discussion — Vanq69

   References

   • Microsoft Learn

   Next question

3. Question 113 · 370 in bank

   HOTSPOT - You have an Azure Synapse Analytics serverless SQL pool. You have an Apache Parquet file that contains 10 columns. You need to query data from the file. The solution must return only two columns. How should you complete the query? To answer, select the appropriate options in the answer area. 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

   References

   • Microsoft Learn

   

   Next question

4. Question 114 · 370 in bank

   You have an Azure Synapse Analytics workspace that contains an Apache Spark pool named SparkPool1. SparkPool1 contains a Delta Lake table named SparkTable1. You need to recommend a solution that supports Transact-SQL queries against the data referenced by SparkTable1. The solution must ensure that the queries can use partition elimination. What should you include in the recommendation?

   • Aa partitioned table in a dedicated SQL pool
   • Ba partitioned view in a dedicated SQL pool
   • Ca partitioned index in a dedicated SQL pool
   • Da partitioned view in a serverless SQL pool

   Show answer

   Answer: D

   Explanation

   D is correct. "The OPENROWSET function is not supported in dedicated SQL pools in Azure Synapse." so it eliminates A,B and C. Ref: https://learn.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?view=sql-server-ver16 Only the partitioned view in the serverless sql pool is correct since "External tables in serverless SQL pools do not support partitioning on Delta Lake format. Use Delta partitioned views instead of tables if you have partitioned Delta Lake data sets." Ref: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/create-use-external-tables#delta-tables-on-partitioned-folders

   Source: Adapted from community discussion — metiii

   References

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

   Next question

5. Question 115 · 370 in bank

   You are designing a sales transactions table in an Azure Synapse Analytics dedicated SQL pool. The table will contain approximately 60 million rows per month and will be partitioned by month. The table will use a clustered column store index and round-robin distribution. Approximately how many rows will there be for each combination of distribution and partition?

   • A1 million
   • B5 million
   • C20 million
   • D60 million

   Show answer

   Answer: A

   Explanation

   Answer is A. Each partition has one million rows( minimum). *A partition with 1 distribution will have 1M rows * Partition with 2 distribution will have 2M rows ----' Partition with 60 distributions will have 60M rows ....Partition with n-distribution will have n ×1M rows = n M rows So in general, if there is p-partitions and d--distributions .then the combination of P and d describes the total rows or records (Q) which can be mathematical represented below Q = 1000000 * p *d For each combination of partition and distribution, P = 1 and d = 1 Therefore , The total rows,Q = 1000000*1*1= 1M. N.B .1M rows is the minimum per partion and distribution required for a table compression and optimization in table partitioning

   Source: Adapted from community discussion — moneytime

   Next question

6. Question 116 · 370 in bank

   Select 2

   You have an Azure Synapse Analytics workspace. You plan to deploy a lake database by using a database template in Azure Synapse. Which two elements are included in the template? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

   • Arelationships
   • Bdata formats
   • Clinked services
   • Dtable permissions
   • Etable definitions

   Show answer

   Answer: AE

   Explanation

   Correct, AE. Only table definition and their relationship is included in the template. The rest of the options should be configured Ref: https://learn.microsoft.com/en-us/azure/synapse-analytics/database-designer/create-lake-database-from-lake-database-templates

   Source: Adapted from community discussion — metiii

   References

   • Microsoft Learn

   Next question

7. Question 117 · 370 in bank

   What should you do to improve high availability of the real-time data processing solution?

   • ADeploy a High Concurrency Databricks cluster.
   • BDeploy an Azure Stream Analytics job and use an Azure Automation runbook to check the status of the job and to start the job if it stops.
   • CSet Data Lake Storage to use geo-redundant storage (GRS).
   • DDeploy identical Azure Stream Analytics jobs to paired regions in Azure.

   Show answer

   Answer: D

   Explanation

   There is a request 'Minimize number of Azure services'. With https://docs.microsoft.com/en-us/azure/event-hubs/event-hubs-capture-overview Event capture, data can be stored in DL without using Stream Analytics. In this case just Reional redundancy for DL would be needed.

   Source: Adapted from community discussion — petulda

   References

   • Microsoft Learn

   Next question

8. Question 118 · 370 in bank

   HOTSPOT - You plan to create a real-time monitoring app that alerts users when a device travels more than 200 meters away from a designated location. You need to design an Azure Stream Analytics job to process the data for the planned app. The solution must minimize the amount of code developed and the number of technologies used. What should you include in the Stream Analytics job? 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

   Input type: Stream - You can process real-time IoT data streams with Azure Stream Analytics. Function: Geospatial - With built-in geospatial functions, you can use Azure Stream Analytics to build applications for scenarios such as fleet management, ride sharing, connected cars, and asset tracking. Note: In a real-world scenario, you could have hundreds of these sensors generating events as a stream. Ideally, a gateway device would run code to push these events to Azure Event Hubs or Azure IoT Hubs. Reference: https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-get-started-with-azure-stream-analytics-to-process-data-from-iot-devices https://docs.microsoft.com/en-us/azure/stream-analytics/geospatial-scenarios

   Source: Adapted from community discussion — MrWood47

   References

   • Microsoft Learn

   

   Next question

9. Question 119 · 370 in bank

   DRAG DROP - You have an Apache Spark DataFrame named temperatures. A sample of the data is shown in the following table.

   

   You need to produce the following table by using a Spark SQL query.

   

   How should you complete the query? 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:

   

   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: PIVOT - PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. And PIVOT runs aggregations where they're required on any remaining column values that are wanted in the final output. Incorrect Answers: UNPIVOT carries out the opposite operation to PIVOT by rotating columns of a table-valued expression into column values. Box 2: CAST - If you want to convert an integer value to a DECIMAL data type in SQL Server use the CAST() function. Example: SELECT - CAST(12 AS DECIMAL(7,2) ) AS decimal_value; Here is the result: decimal_value 12.00 Reference: https://learnsql.com/cookbook/how-to-convert-an-integer-to-a-decimal-in-sql-server/ https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot

   Source: Adapted from community discussion — jongert

   

   Next question

10. Question 120 · 370 in bank

    HOTSPOT - You have an Azure Blob storage account that contains a folder. The folder contains 120,000 files. Each file contains 62 columns. Each day, 1,500 new files are added to the folder. You plan to incrementally load five data columns from each new file into an Azure Synapse Analytics workspace. You need to minimize how long it takes to perform the incremental loads. What should you use to store the files and in which format? To answer, select the appropriate options in the answer area. 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

    Explanation

    Time partitioning is correct as the fastest way to load only new files, but requires that the timeslice information be part of the file or folder name (https://learn.microsoft.com/en-us/azure/data-factory/tutorial-incremental-copy-overview) However, Parquet is the correct file format since it's a columnar format

    Source: Adapted from community discussion — ababatunde_hs

    References

    • Microsoft Learn

    

    Next question

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

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