nerdexam
Microsoft

DP-500 · Question #80

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…

The correct answer is B. No. The correct answer 'No' indicates that the proposed, but truncated, solution failed to sufficiently address potential query issues for UTF-8 encoded strings using automatic schema inference and default collation.

Query and transform data

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 in the following table. You need to recommend changes to the queries to reduce I/O reads and tempdb usage. Does this meet the goal?

Options

  • AYes
  • BNo

How the community answered

(39 responses)
  • A
    28% (11)
  • B
    72% (28)

Why each option

The correct answer 'No' indicates that the proposed, but truncated, solution failed to sufficiently address potential query issues for UTF-8 encoded strings using automatic schema inference and default collation.

AYes
BNoCorrect

Without the full solution, it is inferred that the proposed change did not adequately resolve issues like incorrect data type mapping (e.g., VARCHAR instead of NVARCHAR), string truncation, or improper sorting/comparison that can arise when relying solely on automatic schema inference and default collation for diverse UTF-8 data. Explicitly defining the schema with appropriate NVARCHAR types and a suitable collation in the OPENROWSET WITH clause is often essential for correctness and optimal performance in such scenarios.

Concept tested: Serverless SQL pool data type inference and collation for UTF-8 data

Source: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-parquet-files#query-specific-columns

Topics

#Azure Synapse Analytics#Serverless SQL Pool#Schema Inference#Query Optimization

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full DP-500 Practice