DP-500 Question #24: Real Exam Question with Answer & Explanation

Question

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. After you answer a question in this question, you will NOT be able to return to it. As a result, these questions will not appear in the review screen. You are using an Azure Synapse Analytics serverless SQL pool to query a collection of Apache Parquet files by using automatic schema inference. The files contain more than 40 million rows of UTF-8-encoded business names, survey names, and participant counts. The database is configured to use the default collation. The queries use OPENROWSET and infer the schema shown in the following table. name | system_type_name | max_length businessName | varchar(8000) | 8000 surveyName | varchar(8000) | 8000 participants | int | 4 You need to recommend changes to the queries to reduce I/O reads and tempdb usage. Solution: You recommend using OPENROWSET WITH to explicitly define the collation for businessName and surveyName as Latin1_General_100_BIN2_UTF8. Does this meet the goal?

Options

• AYes
• BNo

Explanation

Querying Apache Parquet files containing UTF-8 encoded text and numeric data using a Synapse serverless SQL pool with automatic schema inference and OPENROWSET is achievable, even with a large number of rows and default collation.

Common mistakes.

• B. The scenario described is a standard and supported use case for Azure Synapse Analytics serverless SQL pools, demonstrating its capability to handle large Parquet files with schema inference and UTF-8 data.

Concept tested. Azure Synapse serverless SQL pool capabilities with Parquet and schema inference

Reference. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-parquet-files

No community discussion yet for this question.