Microsoft Microsoft Certifications DP-203 Questions & Answers

• Question 171:

  You have an Azure Synapse Analytics dedicated SQL pool named Pool1. Pool1 contains a table named table1.

  You load 5 TB of data into table1.

  You need to ensure that columnstore compression is maximized for table1.

  Which statement should you execute?

  A. DBCC INDEXDEFRAG (pool1, table1)

  B. DBCC DBREINDEX (table1)

  C. ALTER INDEX ALL on table1 REORGANIZE

  D. ALTER INDEX ALL on table1 REBUILD

  Correct Answer: D

  Columnstore and columnstore archive compression

  Columnstore tables and indexes are always stored with columnstore compression. You can further reduce the size of columnstore data by configuring an additional compression called archival compression. To perform archival compression,

  SQL Server runs the Microsoft XPRESS compression algorithm on the data. Add or remove archival compression by using the following data compression types:

  Use COLUMNSTORE_ARCHIVE data compression to compress columnstore data with archival compression.

  Use COLUMNSTORE data compression to decompress archival compression. The resulting data continue to be compressed with columnstore compression.

  To add archival compression, use ALTER TABLE (Transact-SQL) or ALTER INDEX (Transact-SQL) with the REBUILD option and DATA COMPRESSION = COLUMNSTORE_ARCHIVE.

  Reference: https://learn.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression

• Question 172:

  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?

  A. Data IO percentage

  B. Local tempdb percentage

  C. Cache used percentage

  D. DWU percentage

  Correct Answer: C

  Monitor and troubleshoot slow query performance by determining whether your workload is optimally leveraging the adaptive cache for dedicated SQL pools.

  Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-how-to-monitor-cache

• Question 173:

  A company uses Azure Stream Analytics to monitor devices.

  The company plans to double the number of devices that are monitored.

  You need to monitor a Stream Analytics job to ensure that there are enough processing resources to handle the additional load.

  Which metric should you monitor?

  A. Early Input Events

  B. Late Input Events

  C. Watermark delay

  D. Input Deserialization Errors

  Correct Answer: C

  There are a number of resource constraints that can cause the streaming pipeline to slow down. The watermark delay metric can rise due to:

  1.

  Not enough processing resources in Stream Analytics to handle the volume of input events.

  2.

  Not enough throughput within the input event brokers, so they are throttled.

  3.

  Output sinks are not provisioned with enough capacity, so they are throttled. The possible solutions vary widely based on the flavor of output service being used.

  Reference: https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-time-handling

• Question 174:

  You have an Azure Synapse Analytics dedicated SQL pool.

  You run PDW_SHOWSPACEUSED(dbo,FactInternetSales'); and get the results shown in the following table.

  

  Which statement accurately describes the dbo,FactInternetSales table?

  A. The table contains less than 1,000 rows.

  B. All distribution contain data.

  C. The table is skewed.

  D. The table uses round-robin distribution.

  Correct Answer: C

  Data skew means the data is not distributed evenly across the distributions.

  Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute

• Question 175:

  You have an Azure subscription linked to an Azure Active Directory (Azure AD) tenant that contains a service principal named ServicePrincipal1. The subscription contains an Azure Data Lake Storage account named adls1. Adls1 contains a folder named Folder2 that has a URI of https://adls1.dfs.core.windows.net/container1/Folder1/Folder2/.

  ServicePrincipal1 has the access control list (ACL) permissions shown in the following table.

  

  You need to ensure that ServicePrincipal1 can perform the following actions:

  1.

  Traverse child items that are created in Folder2.

  2.

  Read files that are created in Folder2.

  The solution must use the principle of least privilege.

  Which two permissions should you grant to ServicePrincipal1 for Folder2? Each correct answer presents part of the solution.

  NOTE: Each correct selection is worth one point.

  A. Access - Read

  B. Access - Write

  C. Access - Execute

  D. Default-Read

  E. Default - Write

  F. Default - Execute

  Correct Answer: DF

  Execute (X) permission is required to traverse the child items of a folder.

  There are two kinds of access control lists (ACLs), Access ACLs and Default ACLs. Access ACLs: These control access to an object. Files and folders both have Access ACLs. Default ACLs: A "template" of ACLs associated with a folder that determine the Access ACLs for any child items that are created under that folder. Files do not have Default ACLs.

  Reference: https://docs.microsoft.com/en-us/azure/data-lake-store/data-lake-store-access-control

• Question 176:

  You have an Azure data factory named ADF1.

  You currently publish all pipeline authoring changes directly to ADF1.

  You need to implement version control for the changes made to pipeline artifacts. The solution must ensure that you can apply version control to the resources currently defined in the UX Authoring canvas for ADF1.

  Which two actions should you perform? Each correct answer presents part of the solution.

  NOTE: Each correct selection is worth one point.

  A. From the UX Authoring canvas, select Set up code repository.

  B. Create a Git repository.

  C. Create a GitHub action.

  D. Create an Azure Data Factory trigger.

  E. From the UX Authoring canvas, select Publish.

  F. From the UX Authoring canvas, run Publish All.

  Correct Answer: AB

  To implement version control for changes made to pipeline artifacts in ADF1 while ensuring that version control can be applied to the resources currently defined in the UX Authoring canvas, you should perform the following two actions:

  A. From the UX Authoring canvas, select Set up code repository: This will allow you to configure ADF1 to integrate with a version control system such as Git, which will enable you to track changes made to pipeline artifacts over time.

  B. Create a Git repository: This will provide the version control system needed to track changes made to pipeline artifacts in ADF1.

  Therefore, options A and B are the correct answers.

  C, D, E, and F are not relevant to implementing version control for changes made to pipeline artifacts in ADF1.

• Question 177:

  You have an activity in an Azure Data Factory pipeline. The activity calls a stored procedure in a data warehouse in Azure Synapse Analytics and runs daily.

  You need to verify the duration of the activity when it ran last.

  What should you use?

  A. activity runs in Azure Monitor

  B. Activity log in Azure Synapse Analytics

  C. the sys.dm_pdw_wait_stats data management view in Azure Synapse Analytics

  D. an Azure Resource Manager template

  Correct Answer: A

  Reference: https://docs.microsoft.com/en-us/azure/data-factory/monitor-visually

• Question 178:

  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 have an Azure Data Lake Storage account that contains a staging zone.

  You need to design a daily process to ingest incremental data from the staging zone, transform the data by executing an R script, and then insert the transformed data into a data warehouse in Azure Synapse Analytics.

  Solution: You use an Azure Data Factory schedule trigger to execute a pipeline that executes an Azure Databricks notebook, and then inserts the data into the data warehouse.

  Does this meet the goal?

  A. Yes

  B. No

  Correct Answer: B

  If you need to transform data in a way that is not supported by Data Factory, you can create a custom activity, not an Azure Databricks notebook, with your own data processing logic and use the activity in the pipeline. You can create a custom activity to run R scripts on your HDInsight cluster with R installed.

  Reference: https://docs.microsoft.com/en-US/azure/data-factory/transform-data

• Question 179:

  You have an Azure subscription that contains an Azure Blob Storage account named storage1 and an Azure Synapse Analytics dedicated SQL pool named Pool1. You need to store data in storage1. The data will be read by Pool1. The solution must meet the following requirements:

  1.

  Enable Pool1 to skip columns and rows that are unnecessary in a query.

  2.

  Automatically create column statistics.

  3.

  Minimize the size of files. Which type of file should you use?

  A. JSON

  B. Parquet

  C. Avro

  D. CSV

  Correct Answer: B

  Automatic creation of statistics is turned on for Parquet files. For CSV files, you need to create statistics manually until automatic creation of CSV files statistics is supported.

  Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-statistics

• Question 180:

  You have an enterprise data warehouse in Azure Synapse Analytics named DW1 on a server named Server1.

  You need to determine the size of the transaction log file for each distribution of DW1.

  What should you do?

  A. On DW1, execute a query against the sys.database_files dynamic management view.

  B. From Azure Monitor in the Azure portal, execute a query against the logs of DW1.

  C. Execute a query against the logs of DW1 by using the Get-AzOperationalInsightsSearchResult PowerShell cmdlet.

  D. On the master database, execute a query against the sys.dm_pdw_nodes_os_performance_counters dynamic management view.

  Correct Answer: A

  For information about the current log file size, its maximum size, and the autogrow option for the file, you can also use the size, max_size, and growth columns for that log file in sys.database_files.

  Reference: https://docs.microsoft.com/en-us/sql/relational-databases/logs/manage-the-size-of-the-transaction-log-file