Microsoft Microsoft Certifications DP-203 Questions & Answers

• Question 231:

  You have a SQL pool in Azure Synapse.

  A user reports that queries against the pool take longer than expected to complete.

  You need to add monitoring to the underlying storage to help diagnose the issue.

  Which two metrics should you monitor? Each correct answer presents part of the solution.

  NOTE: Each correct selection is worth one point.

  A. Cache used percentage

  B. DWU Limit

  C. Snapshot Storage Size

  D. Active queries

  E. Cache hit percentage

  Correct Answer: AE

  A: Cache used is the sum of all bytes in the local SSD cache across all nodes and cache capacity is the sum of the storage capacity of the local SSD cache across all nodes.

  E: Cache hits is the sum of all columnstore segments hits in the local SSD cache and cache miss is the columnstore segments misses in the local SSD cache summed across all nodes

  Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-concept-resource-utilization-query-activity

• Question 232:

  You are performing exploratory analysis of the bus fare data in an Azure Data Lake Storage Gen2 account by using an Azure Synapse Analytics serverless SQL pool. You execute the Transact-SQL query shown in the following exhibit.

  

  What do the query results include?

  A. Only CSV files in the tripdata_2020 subfolder.

  B. All files that have file names that beginning with "tripdata_2020".

  C. All CSV files that have file names that contain "tripdata_2020".

  D. Only CSV that have file names that beginning with "tripdata_2020".

  Correct Answer: D

• Question 233:

  You have an Azure Data Lake Storage account that has a virtual network service endpoint configured.

  You plan to use Azure Data Factory to extract data from the Data Lake Storage account.

  The data will then be loaded to a data warehouse in Azure Synapse Analytics by using PolyBase.

  Which authentication method should you use to access Data Lake Storage?

  A. shared access key authentication

  B. managed identity authentication

  C. account key authentication

  D. service principal authentication

  Correct Answer: B

  Reference: https://docs.microsoft.com/en-us/azure/data-factory/connector-azure-sql-data-warehouse#use-polybase-to-load-data-into-azure-sql-data-warehouse

• Question 234:

  You plan to implement an Azure Data Lake Storage Gen2 container that will contain CSV files. The size of the files will vary based on the number of events that occur per hour.

  File sizes range from 4.KB to 5 GB.

  You need to ensure that the files stored in the container are optimized for batch processing.

  What should you do?

  A. Compress the files.

  B. Merge the files.

  C. Convert the files to JSON

  D. Convert the files to Avro.

  Correct Answer: D

  Avro supports batch and is very relevant for streaming.

  Note: Avro is framework developed within Apache's Hadoop project. It is a row-based storage format which is widely used as a serialization process. AVRO stores its schema in JSON format making it easy to read and interpret by any program. The data itself is stored in binary format by doing it compact and efficient.

  Reference: https://www.adaltas.com/en/2020/07/23/benchmark-study-of-different-file-format/

• Question 235:

  You plan to ingest streaming social media data by using Azure Stream Analytics. The data will be stored in files in Azure Data Lake Storage, and then consumed by using Azure Datiabricks and PolyBase in Azure Synapse Analytics.

  You need to recommend a Stream Analytics data output format to ensure that the queries from Databricks and PolyBase against the files encounter the fewest possible errors. The solution must ensure that the tiles can be queried quickly and

  that the data type information is retained.

  What should you recommend?

  A. Parquet

  B. Avro

  C. CSV

  D. JSON

  Correct Answer: A

  The Avro format is great for data and message preservation.Avro schema with its support for evolution is essential for making the data robust for streaming architectures like Kafka, and with the metadata that schema provides, you can reason on the data. Having a schema provides robustness in providing meta-data about the data stored in Avro records which are self- documenting the data.

  References:http://cloudurable.com/blog/avro/index.html

• Question 236:

  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 Synapse Analytics dedicated SQL pool that contains a table named Table1.

  You have files that are ingested and loaded into an Azure Data Lake Storage Gen2 container named container1.

  You plan to insert data from the files in container1 into Table1 and transform the data. Each row of data in the files will produce one row in the serving layer of Table1.

  You need to ensure that when the source data files are loaded to container1, the DateTime is stored as an additional column in Table1.

  Solution: In an Azure Synapse Analytics pipeline, you use a Get Metadata activity that retrieves the DateTime of the files.

  Does this meet the goal?

  A. Yes

  B. No

  Correct Answer: B

  Instead use a serverless SQL pool to create an external table with the extra column. Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/create-use-external-tables

• Question 237:

  You have several Azure Data Factory pipelines that contain a mix of the following types of activities.

  1.

  Wrangling data flow

  2.

  Notebook

  3.

  Copy

  4.

  jar

  Which two Azure services should you use to debug the activities? Each correct answer presents part of the solution NOTE: Each correct selection is worth one point.

  A. Azure Machine Learning

  B. Azure Data Factory

  C. Azure Synapse Analytics

  D. Azure HDInsight

  E. Azure Databricks

  Correct Answer: BE

  B: Azure Data Factory: Azure Data Factory itself provides debugging capabilities for its activities. You can monitor and debug the execution of pipeline activities directly within the Azure Data Factory interface. It allows you to view activity run details, input/output data, logs, and diagnose any errors or issues encountered during execution.

  Use Azure Data Factory for rangling and Copy.

  E: Azure Databricks: Azure Databricks is a powerful analytics platform that integrates well with Azure Data Factory. You can use it to debug and analyze Notebook activities within the Data Factory pipelines. Azure Databricks provides an interactive environment to run and debug notebooks, allowing you to inspect intermediate data, execute code step-by-step, and troubleshoot any issues.

  Azure Databricks for Jar and Notbooks.

  Reference: https://learn.microsoft.com/en-us/azure/data-factory/iterative-development-debugging

• Question 238:

  You have a C# application that process data from an Azure IoT hub and performs complex transformations.

  You need to replace the application with a real-time solution. The solution must reuse as much code as possible from the existing application.

  A. Azure Databricks

  B. Azure Event Grid

  C. Azure Stream Analytics

  D. Azure Data Factory

  Correct Answer: C

  Azure Stream Analytics on IoT Edge empowers developers to deploy near-real-time analytical intelligence closer to IoT devices so that they can unlock the full value of device- generated data. UDF are available in C# for IoT Edge jobs Azure Stream Analytics on IoT Edge runs within the Azure IoT Edge framework. Once the job is created in Stream Analytics, you can deploy and manage it using IoT Hub.

  References: https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-edge

• Question 239:

  You configure monitoring for a Microsoft Azure SQL Data Warehouse implementation. The implementation uses PolyBase to load data from comma-separated value (CSV) files stored in Azure Data Lake Gen 2 using an external table.

  Files with an invalid schema cause errors to occur.

  You need to monitor for an invalid schema error.

  For which error should you monitor?

  A. EXTERNAL TABLE access failed due to internal error: 'Java exception raised on call to HdfsBridge_Connect: Error [com.microsoft.polybase.client.KerberosSecureLogin] occurred while accessing external files.'

  B. EXTERNAL TABLE access failed due to internal error: 'Java exception raised on call to HdfsBridge_Connect: Error [No FileSystem for scheme: wasbs] occurred while accessing external file.'

  C. Cannot execute the query "Remote Query" against OLE DB provider "SQLNCLI11": for linked server "(null)", Query aborted- the maximum reject threshold (o rows) was reached while regarding from an external source: 1 rows rejected out of total 1 rows processed.

  D. EXTERNAL TABLE access failed due to internal error: 'Java exception raised on call to HdfsBridge_Connect: Error [Unable to instantiate LoginClass] occurred while accessing external files.'

  Correct Answer: C

  Customer Scenario:

  SQL Server 2016 or SQL DW connected to Azure blob storage. The CREATE EXTERNAL TABLE DDL points to a directory (and not a specific file) and the directory contains files with different schemas.

  SSMS Error:

  Select query on the external table gives the following error:

  Msg 7320, Level 16, State 110, Line 14

  Cannot execute the query "Remote Query" against OLE DB provider "SQLNCLI11" for linked server "(null)". Query aborted-- the maximum reject threshold (0 rows) was reached while reading from an external source: 1 rows rejected out of

  total 1 rows processed.

  Possible Reason:

  The reason this error happens is because each file has different schema. The PolyBase external table DDL when pointed to a directory recursively reads all the files in that directory. When a column or data type mismatch happens, this error

  could be seen in SSMS.

  Possible Solution:

  If the data for each table consists of one file, then use the filename in the LOCATION section prepended by the directory of the external files. If there are multiple files per table, put each set of files into different directories in Azure Blob

  Storage and then you can point LOCATION to the directory instead of a particular file. The latter suggestion is the best practices recommended by SQLCAT even if you have one file per table.

• Question 240:

  You are designing a sales transactions table in an Azure Synapse Analytics dedicated SQL pool. The table will contains 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?

  A. 1 million

  B. 5 million

  C. 20 million

  D. 60 million

  Correct Answer: D

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