DP-203 practice questions 321–330

1. Question 321 · 370 in bank

   You have a partitioned table in an Azure Synapse Analytics dedicated SQL pool. You need to design queries to maximize the benefits of partition elimination. What should you include in the Transact-SQL queries?

   • AJOIN
   • BWHERE
   • CDISTINCT
   • DGROUP BY

   Show answer

   Answer: B

   Explanation

   Why ?? Because When you add the "WHERE" clause to your T-SQL query it allows the query optimizer accesses only the relevant partitions to satisfy the filter criteria of the query - which is what partition elimination is all about.

   Source: Adapted from community discussion — okechi

   References

   • Microsoft Learn

   Next question

2. Question 322 · 370 in bank

   Select 2

   You have an Azure Stream Analytics query. The query returns a result set that contains 10,000 distinct values for a column named clusterID. You monitor the Stream Analytics job and discover high latency. You need to reduce the latency. Which two actions should you perform? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point.

   • AAdd a pass-through query.
   • BIncrease the number of streaming units.
   • CAdd a temporal analytic function.
   • DScale out the query by using PARTITION BY.
   • EConvert the query to a reference query.

   Show answer

   Answer: BD

   Explanation

   To reduce latency in an Azure Stream Analytics job with a query returning a result set containing 10,000 distinct values for a column named clusterID, you should perform the following actions: B. Increase the number of streaming units: Increasing the number of streaming units allocates more resources to your Stream Analytics job, allowing it to handle higher data volumes and processing loads. By increasing the streaming units, you can improve the job's throughput and reduce latency. D. Scale out the query by using PARTITION BY: Using the PARTITION BY clause in your query allows you to distribute the workload across multiple partitions or parallel processes. By partitioning the data based on relevant criteria, such as clusterID in this case, you can distribute the processing load and reduce latency by enabling parallel processing.

   Source: Adapted from community discussion — vctrhugo

   Next question

3. Question 323 · 370 in bank

   You have an Azure Synapse Analytics dedicated SQL pool named Pool1. Pool1 contains a fact table named Table1. You need to identify the extent of the data skew in Table1. What should you do in Synapse Studio?

   • AConnect to Pool1 and DBCC PDW_SHOWSPACEUSED.
   • BConnect to the built-in pool and run DBCC PDW_SHOWSPACEUSED.
   • CConnect to the built-in pool and run DBCC CHECKALLOC.
   • DConnect to the built-in pool and query sys.dm_pdw_sys_info.

   Show answer

   Answer: A

   Explanation

   Answer is A A quick way to check for data skew is to use DBCC PDW_SHOWSPACEUSED. The following SQL code returns the number of table rows that are stored in each of the 60 distributions. For balanced performance, the rows in your distributed table should be spread evenly across all the distributions. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute

   Source: Adapted from community discussion — igormmpinto

   References

   • Microsoft Learn

   Next question

4. Question 324 · 370 in bank

   Select 2

   You use Azure Data Lake Storage Gen2. You need to ensure that workloads can use filter predicates and column projections to filter data at the time the data is read from disk. Which two actions should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

   • AReregister the Azure Storage resource provider.
   • BCreate a storage policy that is scoped to a container.
   • CReregister the Microsoft Data Lake Store resource provider.
   • DCreate a storage policy that is scoped to a container prefix filter.
   • ERegister the query acceleration feature.

   Show answer

   Answer: DE

   Explanation

   E. Register the query acceleration feature. D. Create a storage policy that is scoped to a container prefix filter. To filter data at the time it is read from disk, you need to use the query acceleration feature of Azure Data Lake Storage Gen2. To enable this feature, you need to register the query acceleration feature in your Azure subscription. In addition, you can use storage policies scoped to a container prefix filter to specify which files and directories in a container should be eligible for query acceleration. This can be used to optimize the performance of the queries by only considering a subset of the data in the container.

   Source: Adapted from community discussion — Sima_al

   References

   • Microsoft Learn

   Next question

5. Question 325 · 370 in bank

   You have an Azure Data Lake Storage Gen2 account that contains two folders named Folder1 and Folder2. You use Azure Data Factory to copy multiple files from Folder1 to Folder2. You receive the following error. Operation on target Copy_sks failed: Failure happened on 'Sink' side. ErrorCode=DelimitedTextMoreColumnsThanDefined, 'Type=Microsoft.DataTransfer.Common.Snared.HybridDeliveryException, Message=Error found when processing 'Csv/Tsv Format Text' source '0_2020_11_09_11_43_32.avro' with row number 53: found more columns than expected column count 27., Source=Microsoft.DataTransfer.Comnon,' What should you do to resolve the error?

   • AChange the Copy activity setting to Binary Copy.
   • BLower the degree of copy parallelism.
   • CAdd an explicit mapping.
   • DEnable fault tolerance to skip incompatible rows.

   Show answer

   Answer: A

   Explanation

   Correct answer is A. We are just copying files between folders. Selecting binary copy, ADF will not check schema. With D we would discard data With C we would change file contents

   Source: Adapted from community discussion — Yemeral

   References

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

   Next question

6. Question 326 · 370 in bank

   You have an Azure data factory. You need to examine the pipeline failures from the last 60 days. What should you use?

   • Athe Activity log blade for the Data Factory resource
   • Bthe Monitor & Manage app in Data Factory
   • Cthe Resource health blade for the Data Factory resource
   • DAzure Monitor

   Show answer

   Answer: D

   Explanation

   Activity logs show only activities, e.g., trigger the pipeline, stop the pipeline, ... Resource health check shows only the healthiness of the resource. The monitor app indeed contains the pipeline run failure information. But it keep the data only for 45 days.

   Source: Adapted from community discussion — erssiws

   Next question

7. Question 327 · 370 in bank

   A company plans to use Apache Spark analytics to analyze intrusion detection data. You need to recommend a solution to analyze network and system activity data for malicious activities and policy violations. The solution must minimize administrative efforts. What should you recommend?

   • AAzure HDInsight
   • BAzure Data Factory
   • CAzure Data Lake Storage
   • DAzure Databricks

   Show answer

   Answer: D

   Explanation

   By leveraging Azure Databricks, you can easily perform advanced analytics on the intrusion detection data using Spark's powerful distributed processing capabilities. Databricks provides an interactive and collaborative environment where you can write Spark code, explore and visualize data, and build machine learning models. It also integrates with popular data sources, including Azure Data Lake Storage, for efficient data ingestion and processing.

   Source: Adapted from community discussion — vctrhugo

   References

   • Microsoft Learn

   Next question

8. Question 328 · 370 in bank

   HOTSPOT - You have an Azure Synapse Analytics dedicated SQL pool. You need to monitor the database for long-running queries and identify which queries are waiting on resources. Which dynamic management view should you use for each requirement? To answer, select the appropriate options in the answer area. NOTE: Each correct answer 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 sys.dm_pdw_lock_waits view is specific to SQL Server and is used to monitor lock waits and lock resources in regular SQL Server environments, not in Azure Synapse Analytics dedicated SQL pools. My answers are: 1. sys.dm_pdw_exec_requests 2. sys.dm_pdw_waits There is a similar question in the microsoft official practice assessment and the explaination is the following: The sys.dm_pdw_waits view holds information about all wait stats encountered during the execution of a request or query, including locks and waits on a transmission queue

   Source: Adapted from community discussion — auwia

   References

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

   

   Next question

9. Question 329 · 370 in bank

   You have an Azure Data Factory pipeline named pipeline1 that includes a Copy activity named Copy1. Copy1 has the following configurations: • The source of Copy1 is a table in an on-premises Microsoft SQL Server instance that is accessed by using a linked service connected via a self-hosted integration runtime. • The sink of Copy1 uses a table in an Azure SQL database that is accessed by using a linked service connected via an Azure integration runtime. You need to maximize the amount of compute resources available to Copy1. The solution must minimize administrative effort. What should you do?

   • AScale out the self-hosted integration runtime.
   • BScale up the data flow runtime of the Azure integration runtime and scale out the self-hosted integration runtime.
   • CScale up the data flow runtime of the Azure integration runtime.

   Show answer

   Answer: A

   Explanation

   I would answer A. https://learn.microsoft.com/en-us/azure/data-factory/concepts-integration-runtime Copying between a cloud data source and a data source in a private network: if either the source or sink linked service points to a self-hosted IR, the copy activity is executed on the self-hosted IR.

   Source: Adapted from community discussion — BillMyl

   References

   • Microsoft Learn

   Next question

10. Question 330 · 370 in bank

    Select 2

    You are designing a solution that will use tables in Delta Lake on Azure Databricks. You need to minimize how long it takes to perform the following: • Queries against non-partitioned tables • Joins on non-partitioned columns Which two options should you include in the solution? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

    • Athe clone command
    • BZ-Ordering
    • CApache Spark caching
    • Ddynamic file pruning (DFP)

    Show answer

    Answer: BD

    Explanation

    Dynamic file pruning, can significantly improve the performance of many queries on Delta Lake tables. Dynamic file pruning is especially efficient for non-partitioned tables, or for joins on non-partitioned columns. The performance impact of dynamic file pruning is often correlated to the clustering of data so consider using Z-Ordering to maximize the benefit.

    Source: Adapted from community discussion — vctrhugo

    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 321. 370 questions available.