DP-203 Practice Questions — Page 27

Question 261

You have an on-premises data warehouse that includes the following fact tables. Both tables have the following columns: DateKey, ProductKey, RegionKey.

There are 120 unique product keys and 65 unique region keys.

Queries that use the data warehouse take a long time to complete.

You plan to migrate the solution to use Azure Synapse Analytics. You need to ensure that the Azure-based solution optimizes query performance and minimizes processing skew.

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

NOTE: Each correct selection is worth one point

Hot Area:

Question 262

Open question ↗

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?

A.JOIN
B.WHERE
C.DISTINCT
D.GROUP BY

Question 263

Open question ↗

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.

A.Add a pass-through query.
B.Increase the number of streaming units.
C.Add a temporal analytic function.
D.Scale out the query by using PARTITION BY.
E.Convert the query to a reference query.

Question 264

Open question ↗

You have an Azure Synapse Analytics dedicated SQL pool named Pool1 and a database named DB1. DB1 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?

A.Connect to the built-in pool and query sys.dm_pdw_nodes_db_partition_stats.
B.Connect to the built-in pool and run DBCC CHECKALLOC.
C.Connect to Pool1 and query sys.dm_pdw_node_status.
D.Connect to Pool1 and query sys.dm_pdw_nodes_db_partition_stats.

Question 265

Open question ↗

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?

A.Connect to Pool1 and DBCC PDW_SHOWSPACEUSED.
B.Connect to the built-in pool and run DBCC PDW_SHOWSPACEUSED.
C.Connect to the built-in pool and run DBCC CHECKALLOC.
D.Connect to the built-in pool and query sys.dm_pdw_sys_info.

Question 266

Open question ↗

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.

A.Reregister the Azure Storage resource provider.
B.Create a storage policy that is scoped to a container.
C.Reregister the Microsoft Data Lake Store resource provider.
D.Create a storage policy that is scoped to a container prefix filter.
E.Register the query acceleration feature.

Question 267

Open question ↗

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?

A.Connect to Pool1 and run DBCC PDW_SHOWSPACEUSED.
B.Connect to the built-in pool and run DBCC PDW_SHOWSPACEUSED.
C.Connect to Pool1 and run DBCC CHECKALLOC.
D.Connect to the built-in pool and query sys.dm_pdw_sys_info.

Question 268

Open question ↗

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?

A.Change the Copy activity setting to Binary Copy.
B.Lower the degree of copy parallelism.
C.Add an explicit mapping.
D.Enable fault tolerance to skip incompatible rows.

Question 269

Open question ↗

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?

A.Azure HDInsight
B.Azure Data Factory
C.Azure Data Lake Storage
D.Azure Databricks

Question 270

Open question ↗

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.

A.the clone command
B.Z-Ordering
C.Apache Spark caching
D.dynamic file pruning (DFP)