exam questions

Exam DP-500 All Questions

View all questions & answers for the DP-500 exam

Exam DP-500 topic 1 question 53 discussion

Actual exam question from Microsoft's DP-500
Question #: 53
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 defining an external table for the Parquet files and updating the query to use the table.
Does this meet the goal?

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

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
Az301301X
Highly Voted 2 years, 6 months ago
You are using an Azure Synapse Analytics serverless SQL pool to query a collection of Apache Parquet files by using automatic schema inference. So, a View would do the trick, not an Ext Table, hence my answer would be NO. The correct in my opinion is a View.
upvoted 10 times
...
Sri966
Most Recent 1 year, 5 months ago
Selected Answer: B
NO is the answer
upvoted 1 times
...
manolet
1 year, 11 months ago
I choose B (NO) in the best practices reference it only mentions to Use CETAS to enhance query performance and joins: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/best-practices-serverless-sql-pool
upvoted 1 times
...
Alborz
1 year, 11 months ago
Selected Answer: A
Defining an external table for the Parquet files and updating the query to use the table can help reduce I/O reads and tempdb usage in Azure Synapse Analytics serverless SQL pool. By creating an external table, you are registering the Parquet files as a table in the database, which allows you to access the data directly without the need for OPENROWSET and schema inference. This can lead to improved query performance and reduced I/O and tempdb usage. Using an external table can also simplify query development and improve data access efficiency. The table's metadata is maintained in the data catalog, which can help optimize query execution.
upvoted 2 times
manolet
1 year, 11 months ago
I don't find your explanation in the best practice reference page for serverless sql pool: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/best-practices-serverless-sql-pool
upvoted 1 times
...
...
Eltooth
2 years ago
Selected Answer: B
Bis correct answer.
upvoted 1 times
...
Sudhamisha
2 years, 1 month ago
Correct answer is B
upvoted 1 times
...
Saffar
2 years, 5 months ago
Selected Answer: B
I think it's B
upvoted 2 times
...
cherious
2 years, 6 months ago
Selected Answer: A
Can't recommend it definitely but I would go for the yes because it is considered optimal to always use external table in Serverless SQL pool. Native external tables that you can use to read and export data in various data formats such as CSV and Parquet. Native external tables are available in serverless SQL pools, and they are in public preview in dedicated SQL pools. Writing/exporting data using CETAS and the native external tables is available only in the serverless SQL pool, but not in the dedicated SQL pools. The native external tables are the recommended solution in the pools where they are generally available. If you need to access external data, always use the native tables in serverless pools. In dedicated pools, you should switch to the native tables for reading Parquet files once they are in GA. Source: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop
upvoted 2 times
cherious
2 years, 6 months ago
I have change my mind. I have read that external tables are useful for smaller datasets such as reference data or dimensions. The answer would be no Source: https://www.serverlesssql.com/optimisation/external-tables-vs-views-which-to-use/
upvoted 6 times
...
...
Az301301X
2 years, 6 months ago
Selected Answer: B
No in my opinion.
upvoted 3 times
...
nbagchi
2 years, 6 months ago
Selected Answer: A
We can define the schema in an External Table Check this: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop#create-external-table
upvoted 2 times
...
AT96
2 years, 6 months ago
Correct answer is A
upvoted 2 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 ...