Microsoft Microsoft Certifications DP-203 Questions & Answers

• Question 201:

  You are designing a fact table named FactPurchase in an Azure Synapse Analytics dedicated SQL pool. The table contains purchases from suppliers for a retail store. FactPurchase will contain the following columns.

  

  FactPurchase will have 1 million rows of data added daily and will contain three years of data. Transact-SQL queries similar to the following query will be executed daily. SELECT SupplierKey, StockItemKey, COUNT(*) FROM FactPurchase WHERE DateKey >= 20210101 AND DateKey <= 20210131 GROUP By SupplierKey, StockItemKey Which table distribution will minimize query times?

  A. round-robin

  B. replicated

  C. hash-distributed on DateKey

  D. hash-distributed on PurchaseKey

  Correct Answer: D

  Hash-distributed tables improve query performance on large fact tables, and are the focus of this article. Round-robin tables are useful for improving loading speed.

  Reference:

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

• Question 202:

  You plan to create an Azure Data Factory pipeline that will include a mapping data flow. You have JSON data containing objects that have nested arrays.

  You need to transform the JSON-formatted data into a tabular dataset. The dataset must have one tow for each item in the arrays. Which transformation method should you use in the mapping data flow?

  A. unpivot

  B. flatten

  C. new branch

  D. alter row

  Correct Answer: B

  Use the flatten transformation to take array values inside hierarchical structures such as JSON and unroll them into individual rows. This process is known as denormalization.

  Reference: https://docs.microsoft.com/en-us/azure/data-factory/data-flow-flatten

• Question 203:

  You are designing the folder structure for an Azure Data Lake Storage Gen2 account. You identify the following usage patterns:

  1.

  Users will query data by using Azure Synapse Analytics serverless SQL pools and Azure Synapse Analytics serverless Apache Spark pods.

  2.

  Most queries will include a filter on the current year or week.

  3.

  Data will be secured by data source.

  You need to recommend a folder structure that meets the following requirements:

  1.

  Supports the usage patterns

  2.

  Simplifies folder security

  3.

  Minimizes query times

  Which folder structure should you recommend?

  A. \DataSource\SubjectArea\YYYY\WW\FileData_YYYY_MM_DD.parquet

  B. \DataSource\SubjectArea\YYYY-WW\FileData_YYYY_MM_DD.parquet

  C. DataSource\SubjectArea\WW\YYYY\FileData_YYYY_MM_DD.parquet

  D. \YYYY\WW\DataSource\SubjectArea\FileData_YYYY_MM_DD.parquet

  E. WW\YYYY\SubjectArea\DataSource\FileData_YYYY_MM_DD.parquet

  Correct Answer: A

  Data will be secured by data source. -> Use DataSource as top folder.

  Most queries will include a filter on the current year or week -> Use \YYYY\WW\ as subfolders.

  Common Use Cases

  A common use case is to filter data stored in a date (and possibly time) folder structure such as /YYYY/MM/DD/ or /YYYY/MM/YYYY-MM-DD/. As new data is generated/sent/copied/moved to the storage account, a new folder is created for

  each specific time period. This strategy organises data into a maintainable folder structure.

  Reference: https://www.serverlesssql.com/optimisation/azurestoragefilteringusingfilepath/

• Question 204:

  You have an Azure Storage account and a data warehouse in Azure Synapse Analytics in the UK South region.

  You need to copy blob data from the storage account to the data warehouse by using Azure Data Factory. The solution must meet the following requirements:

  1.

  Ensure that the data remains in the UK South region at all times.

  2.

  Minimize administrative effort.

  Which type of integration runtime should you use?

  A. Azure integration runtime

  B. Azure-SSIS integration runtime

  C. Self-hosted integration runtime

  Correct Answer: A

  Reference: https://docs.microsoft.com/en-us/azure/data-factory/concepts-integration-runtime

• Question 205:

  You are designing a highly available Azure Data Lake Storage solution that will include geo-zone-redundant storage (GZRS).

  You need to monitor for replication delays that can affect the recovery point objective (RPO).

  What should you include in the monitoring solution?

  A. 5xx: Server Error errors

  B. Average Success E2E Latency

  C. availability

  D. Last Sync Time

  Correct Answer: D

  Because geo-replication is asynchronous, it is possible that data written to the primary region has not yet been written to the secondary region at the time an outage occurs. The Last Sync Time property indicates the last time that data from the primary region was written successfully to the secondary region. All writes made to the primary region before the last sync time are available to be read from the secondary location. Writes made to the primary region after the last sync time property may or may not be available for reads yet.

  Reference: https://docs.microsoft.com/en-us/azure/storage/common/last-sync-time-get

• Question 206:

  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. All distributions data.

  B. The table contains less than 10,000 rows.

  C. The table uses round-robin distribution.

  D. The table is skewed.

  Correct Answer: D

  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 207:

  You have two fact tables named Flight and Weather. Queries targeting the tables will be based on the join between the following columns.

  

  You need to recommend a solution that maximizes query performance. What should you include in the recommendation?

  A. In the tables use a hash distribution of ArrivalDateTime and ReportDateTime.

  B. In the tables use a hash distribution of ArrivalAirportID and AirportID.

  C. In each table, create an IDENTITY column.

  D. In each table, create a column as a composite of the other two columns in the table.

  Correct Answer: B

  Hash-distribution improves query performance on large fact tables. Incorrect Answers:

  A: Do not use a date column for hash distribution. All data for the same date lands in the same distribution. If several users are all filtering on the same date, then only 1 of the 60 distributions do all the processing work.

• Question 208:

  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 Data Lake Storage

  B. Azure Databncks

  C. Azure HDInsight

  D. Azure Data Factory

  Correct Answer: B

  Three common analytics use cases with Microsoft Azure Databricks Recommendation engines, churn analysis, and intrusion detection are common scenarios that many organizations are solving across multiple industries. They require machine learning, streaming analytics, and utilize massive amounts of data processing that can be difficult to scale without the right tools. Recommendation engines, churn analysis, and intrusion detection are common scenarios that many organizations are solving across multiple industries. They require machine learning, streaming analytics, and utilize massive amounts of data processing that can be difficult to scale without the right tools.

  Note: Recommendation engines, churn analysis, and intrusion detection are common scenarios that many organizations are solving across multiple industries. They require machine learning, streaming analytics, and utilize massive amounts of data processing that can be difficult to scale without the right tools.

  Reference: https://azure.microsoft.com/es-es/blog/three-critical-analytics-use-cases-with-microsoft-azure-databricks

• Question 209:

  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. DWU percentage

  B. Cache hit percentage

  C. DWU limit

  D. Data IO percentage

  Correct Answer: B

  You can use Azure Monitor to view cache metrics to troubleshoot query performance.

  The key metrics for troubleshooting the cache are Cache hit percentage and Cache used percentage.

  Possible scenario: Your current working data set cannot fit into the cache which causes a low cache hit percentage due to physical reads. Consider scaling up your performance level and rerun your workload to populate the cache.

  Reference:

  https://docs.microsoft.com/da-dk/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-how-to-monitor-cache

• Question 210:

  You have an Azure Databricks resource.

  You need to log actions that relate to changes in compute for the Databricks resource.

  Which Databricks services should you log?

  A. clusters

  B. workspace

  C. DBFS

  D. SSH

  E. jobs

  Correct Answer: B

  Databricks provides access to audit logs of activities performed by Databricks users, allowing your enterprise to monitor detailed Databricks usage patterns. There are two types of logs:

  1.

  Workspace-level audit logs with workspace-level events.

  2.

  Account-level audit logs with account-level events.

  Reference: https://docs.databricks.com/administration-guide/account-settings/audit-logs.html