exam questions

Exam DP-500 All Questions

View all questions & answers for the DP-500 exam

Exam DP-500 topic 1 question 54 discussion

Actual exam question from Microsoft's DP-500
Question #: 54
Topic #: 1
[All DP-500 Questions]

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.

You need to recommend changes to the queries to reduce I/O reads and tempdb usage.
Solution: You recommend using OPENROWSET WITH to explicitly specify the maximum length for businessName and surveyName.
Does this meet the goal?

  • A. Yes
  • B. No
Show Suggested Answer Hide Answer
Suggested Answer: A 🗳️

Comments

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Fer079
Highly Voted 2 years, 2 months ago
Selected Answer: A
Parquet files don't contain metadata about maximum character column length. So serverless SQL pool infers it as varchar(8000). You can see an example like this question in the following link: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/best-practices-serverless-sql-pool#check-inferred-data-types
upvoted 9 times
solref
2 years, 1 month ago
It is exactly what I found! Thanks for sharing :)
upvoted 1 times
...
...
Alborz
Most Recent 1 year, 9 months ago
Selected Answer: B
Using OPENROWSET WITH to explicitly specify the maximum length for businessName and surveyName does not meet the goal of reducing I/O reads and tempdb usage in Azure Synapse Analytics serverless SQL pool. Specifying the maximum length using OPENROWSET WITH will only enforce a length constraint on the columns but will not directly impact I/O reads or tempdb usage.
upvoted 1 times
...
Samuel77
1 year, 10 months ago
I will select B
upvoted 2 times
...
Plb2
1 year, 10 months ago
Selected Answer: A
on 40M rows reducing the default varchar(8000) to a smaller size will improve I/O and tempDb.
upvoted 1 times
...
DarioReymago
2 years, 1 month ago
Selected Answer: B
by defaut inferred data types show varchar(8000) with or without WITH clause
upvoted 2 times
...
solref
2 years, 1 month ago
Selected Answer: A
Parquet files don't contain metadata about maximum character column length. So serverless SQL pool infers it as varchar(8000). You can optimize inferred data types, using WITH to specify max length https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/best-practices-serverless-sql-pool#check-inferred-data-types
upvoted 2 times
solref
2 years, 1 month ago
Answer= NO . I correct myself: The Schema definition is a best practice, but it doesnt explain a reduction of IO. Use proper collation reduces the I/O Data in a Parquet file is organized in row groups. Serverless SQL pool skips row groups based on the specified predicate in the WHERE clause, which reduces IO. The result is increased query performance. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/best-practices-serverless-sql-pool#check-inferred-data-types
upvoted 1 times
...
...
Maazi
2 years, 4 months ago
Selected Answer: B
You don't need to use OPENROWSET WITH when reading Parquet files. Ref: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-parquet-files
upvoted 4 times
ivanb94
2 years, 3 months ago
bc of the automatic schema that is even emphasized in the question scenario so I would definitely go with no as the correct answer.
upvoted 1 times
...
DS_newb
2 years, 1 month ago
Check the Explicitly specify schema, I would vote A
upvoted 1 times
...
...
nbagchi
2 years, 4 months ago
Selected Answer: B
Correct
upvoted 1 times
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
Loading ...
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago