[2023] New DP-500 exam dumps Use Updated Microsoft Exam
Verified DP-500 Dumps Q&As - DP-500 Test Engine with Correct Answers
Microsoft DP-500 exam is part of the Microsoft Certified: Azure Data Engineer Associate certification, which validates the candidate's expertise in designing and implementing data solutions using Azure. Designing and Implementing Enterprise-Scale Analytics Solutions Using Microsoft Azure and Microsoft Power BI certification is highly valued by employers looking for professionals who can use Azure services to build scalable data solutions that deliver business value. Candidates who pass the Microsoft DP-500 exam and earn the Azure Data Engineer Associate certification are prepared for roles such as data engineer, data architect, and business intelligence analyst.
NEW QUESTION # 76
You are optimizing a Power Bl data model by using DAX Studio.
You need to capture the query events generated by a Power Bl Desktop report.
What should you use?
- A. the DMV list
- B. a Server Timings trace
- C. an All Queries trace
- D. a Query Plan trace
Answer: C
Explanation:
Explanation
The All Queries trace in Dax Studio supports capturing the query events from all client tools (not just queries sent from DAX Studio like the Query Plan and Server Timings features do). The 'All Queries" trace is really useful when you wish to see the queries that are generated by a client tool like Power BI Desktop.
Reference: https://daxstudio.org/documentation/features/all-queries-trace/
NEW QUESTION # 77
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 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 open row set 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. No
- B. Yes
Answer: A
Explanation:
Explanation
Instead use Solution: You recommend using OPENROWSET WITH to explicitly define the collation for businessName and surveyName as Latin1_General_100_BIN2_UTF8.
Query Parquet files using serverless SQL pool in Azure Synapse Analytics.
Important
Ensure you are using a UTF-8 database collation (for example Latin1_General_100_BIN2_UTF8) because string values in PARQUET files are encoded using UTF-8 encoding. A mismatch between the text encoding in the PARQUET file and the collation may cause unexpected conversion errors. You can easily change the default collation of the current database using the following T-SQL statement: alter database current collate Latin1_General_100_BIN2_UTF8'.
Note: If you use the Latin1_General_100_BIN2_UTF8 collation you will get an additional performance boost compared to the other collations. The Latin1_General_100_BIN2_UTF8 collation is compatible with parquet string sorting rules. The SQL pool is able to eliminate some parts of the parquet files that will not contain data needed in the queries (file/column-segment pruning). If you use other collations, all data from the parquet files will be loaded into Synapse SQL and the filtering is happening within the SQL process. The Latin1_General_100_BIN2_UTF8 collation has additional performance optimization that works only for parquet and CosmosDB. The downside is that you lose fine-grained comparison rules like case insensitivity.
Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/query-parquet-files
NEW QUESTION # 78
You have the following code in an Azure Synapse notebook.
Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the code.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
Reference:
https://matplotlib.org/stable/gallery/lines_bars_and_markers/bar_stacked.html
https://matplotlib.org/stable/api/legend_api.html
NEW QUESTION # 79
You use Azure Synapse Analytics and Apache Spark notebooks to You need to use PySpark to gain access to the visual libraries. Which Python libraries should you use?
- A. Seaborn and TensorFlow
- B. TensorFlow only
- C. Seaborn only
- D. Matplotlib and TensorFlow
- E. Matplotlib only
- F. Matplotlib and Seaborn
Answer: F
Explanation:
Matplotlib
You can render standard plotting libraries, like Matplotlib, using the built-in rendering functions for each library.
Matplotlib is a plotting library for the Python programming language and its numerical mathematics extension NumPy.
Additional libraries
Beyond these libraries, the Azure Synapse Analytics Runtime also includes the following set of libraries that are often used for data visualization:
Seaborn
Seaborn is a Python data visualization library based on matplotlib. It provides a high-level interface for drawing attractive and informative statistical graphics.
Reference:
https://seaborn.pydata.org/
NEW QUESTION # 80
You are implementing a reporting solution that has the following requirements:
* Reports for external customers must support 500 concurrent requests. The data for these reports is
approximately 7 GB and is stored in Azure Synapse Analytics.
* Reports for the security team use data that must have local security rules applied at the database level to
restrict access. The data being reviewed is 2 GB.
Which storage mode provides the best response time for each group of users?
- A. Import for the external customers and DirectQuery for the security team.
- B. DirectQuery for the external customers and import for the security team.
- C. DirectQuery for the external customers and DirectQuery for the security team.
- D. Import for the external customers and import for the security team.
Answer: B
NEW QUESTION # 81
You are configuring an aggregation table as shown in the following exhibit.
The detail table is named FactSales and the aggregation table is named FactSales(Agg).
You need to aggregate SalesAmount for each store.
Which type of summarization should you use for SalesAmount and StoreKey? To answer, select the appropriate options in the answer area, NOTE: Each correct selection is worth one point.
Answer:
Explanation:
Explanation
Box 1: Sum
The Manage aggregations dialog shows a row for each column in the table, where you can specify the aggregation behavior. In the following example, queries to the Sales detail table are internally redirected to the Sales Agg aggregation table.
Box 2: GroupBy
Reference: https://docs.microsoft.com/en-us/power-bi/transform-model/aggregations-advanced
NEW QUESTION # 82
You are configuring an aggregation table as shown in the following exhibit.
The detail table is named FactSales and the aggregation table is named FactSales(Agg).
You need to aggregate SalesAmount for each store.
Which type of summarization should you use for SalesAmount and StoreKey? To answer, select the appropriate options in the answer area, NOTE: Each correct selection is worth one point.
Answer:
Explanation:
NEW QUESTION # 83
You have an Azure Synapse workspace named Workspace1.
You need to use PySpark in a notebook to read data from a SQL pool as an Apache Spark DataFrame and
display the top five
How should you complete the code? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
NEW QUESTION # 84
You have the following code in an Azure Synapse notebook.
Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the code.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
Explanation
Box 1: three scatterplots
Compare Plots
Example, Draw two plots on the same figure:
import matplotlib.pyplot as plt
import numpy as np
#day one, the age and speed of 13 cars:
x = np.array([5,7,8,7,2,17,2,9,4,11,12,9,6])
y = np.array([99,86,87,88,111,86,103,87,94,78,77,85,86])
plt.scatter(x, y)
#day two, the age and speed of 15 cars:
x = np.array([2,2,8,1,15,8,12,9,7,3,11,4,7,14,12])
y = np.array([100,105,84,105,90,99,90,95,94,100,79,112,91,80,85])
plt.scatter(x, y)
plt.show()
Result:
Chart, scatter chart Description automatically generated
Box 2: three marker symbols
One for each scatterplot. One default, and two defined.
Default is point.
v is triangle down.
^ is triangle up.
Reference: https://www.w3schools.com/python/matplotlib_scatter.asp
https://matplotlib.org/stable/api/markers_api.html
NEW QUESTION # 85
You have a Power Bl dataset that contains the following measures:
* Budget
* Actuals
* Forecast
You create a report that contains 10 visuals.
You need provide users with the ability to use a slicer to switch between the measures in two visuals only.
You create a dedicated measure named cg Measure switch.
How should you complete the DAX expression for the Actuals measure? To answer, drag the appropriate
values to the targets. Each value may be used once, more than once, or not at all. You may need to drag the
split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
NEW QUESTION # 86
You have the following Python code in an Apache Spark notebook.
Which type of chart will the code produce?
- A. a bar chart
- B. a pie chart
- C. a stacked bar chart
- D. an area chart
Answer: D
Explanation:
Explanation
The matplotlib.pyplot.fill_between function fills the area between two horizontal curves.
The curves are defined by the points (x, y1) and (x, y2). This creates one or multiple polygons describing the filled area.
Reference: https://matplotlib.org/3.5.0/api/_as_gen/matplotlib.pyplot.fill_between.html
NEW QUESTION # 87
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 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 open row set 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 a data source and view for the Parquet files. You recommend updating the query to use the view.
Does this meet the goal?
- A. No
- B. Yes
Answer: A
Explanation:
Explanation
Solution: You recommend using OPENROWSET WITH to explicitly specify the maximum length for businessName and surveyName.
The size of the varchar(8000) columns are too big. Better reduce their size.
A SELECT...FROM OPENROWSET(BULK...) statement queries the data in a file directly, without importing the data into a table. SELECT...FROM OPENROWSET(BULK...) statements can also list bulk-column aliases by using a format file to specify column names, and also data types.
Reference: https://docs.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql
NEW QUESTION # 88
You have a Power Bl dataset named Dataset1 that uses DirectQuery against an Azure SQL database named
DB1. DB1 is a transactional database in the third normal form.
You need to recommend a solution to minimize how long it takes to execute the query. The solution must
maintain the current functionality. What should you include in the recommendation?
- A. Remove the relationships from Dataset1.
- B. Create calculated columns in Dataset1.
- C. Denormalize the tables in DB1.
- D. Normalize the tables in DB1.
Answer: D
NEW QUESTION # 89
You have the following code in an Azure Synapse notebook.
Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the code.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
Explanation
Box 1: three scatterplots
Compare Plots
Example, Draw two plots on the same figure:
import matplotlib.pyplot as plt
import numpy as np
#day one, the age and speed of 13 cars:
x = np.array([5,7,8,7,2,17,2,9,4,11,12,9,6])
y = np.array([99,86,87,88,111,86,103,87,94,78,77,85,86])
plt.scatter(x, y)
#day two, the age and speed of 15 cars:
x = np.array([2,2,8,1,15,8,12,9,7,3,11,4,7,14,12])
y = np.array([100,105,84,105,90,99,90,95,94,100,79,112,91,80,85])
plt.scatter(x, y)
plt.show()
Result:
Chart, scatter chart Description automatically generated
Box 2: three marker symbols
One for each scatterplot. One default, and two defined.
Default is point.
v is triangle down.
^ is triangle up.
Reference: https://www.w3schools.com/python/matplotlib_scatter.asp
https://matplotlib.org/stable/api/markers_api.html
NEW QUESTION # 90
You need to recommend a solution to add new fields to the financial data Power Bl dataset with data from the Microsoft SQL Server data warehouse.
What should you include in the recommendation?
- A. the on-premises data gateway
- B. Site-to-Site VPN
- C. an XMLA endpoint
- D. Azure Purview
Answer: A
Explanation:
Refresh data from an on-premises SQL Server database
The SQL Server database must be accessed by Power BI through an on-premises data gateway.
You can install an on-premises data gateway on the same local computer as SQL Server (in production, it would typically be a different computer).
NEW QUESTION # 91
You are running a diagnostic against a query as shown in the following exhibit.
What can you identify from the diagnostics query?
- A. All the query steps are folding.
- B. The query is timing out.
- C. Some query steps are folding.
- D. Elevated permissions are being used to query records.
Answer: A
Explanation:
Understanding folding with Query Diagnostics
One of the most common reasons to use Query Diagnostics is to have a better understanding of what operations were 'pushed down' by Power Query to be performed by the back-end data source, which is also known as 'folding'. If we want to see what folded, we can look at what is the 'most specific' query, or queries, that get sent to the back-end data source. We can look at this for both ODATA and SQL.
NEW QUESTION # 92
You develop a solution that uses a Power Bl Premium capacity. The capacity contains a dataset that is expected to consume 50 GB of memory.
Which two actions should you perform to ensure that you can publish the model successfully to the Power Bl service? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
- A. Invoke a refresh to load historical data based on the incremental refresh policy.
- B. Increase the Max Offline Dataset Size setting.
- C. Restart the capacity.
- D. Publish an initial dataset that is less than 10 GB.
- E. Publish the complete dataset.
Answer: A,E
Explanation:
Enable large datasets
Steps here describe enabling large datasets for a new model published to the service. For existing datasets, only step 3 is necessary.
Create a model in Power BI Desktop. If your dataset will become larger and progressively consume more memory, be sure to configure Incremental refresh.
Publish the model as a dataset to the service.
In the service > dataset > Settings, expand Large dataset storage format, set the slider to On, and then select Apply.
Enable large dataset slider
Invoke a refresh to load historical data based on the incremental refresh policy. The first refresh could take a while to load the history. Subsequent refreshes should be faster, depending on your incremental refresh policy.
NEW QUESTION # 93
You have a Power Bl report that contains a card. The card displays the value for year-to-date revenue.
You need to ensure that screen reader users can read the value when initially interacting with the card. The value must stay updated as the dataset is refreshed.
What should you do?
- A. Convert the card into a text box.
- B. Populate the alt text with a static value.
- C. Populate the alt text by using conditional formatting with a DAX measure.
- D. Add the value to the chart title text.
Answer: C
NEW QUESTION # 94
You need to configure a source control solution for Azure Synapse Analytics. The solution must meet the
following requirements:
* Code must always be merged to the main branch before being published, and the main branch must be used
for publishing resource
* The workspace templates must be stored in the publish branch.
* A branch named dev123 will be created to support the development of a new feature.
What should you do? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
NEW QUESTION # 95
You need to configure the Sales Analytics workspace to meet the ad hoc reporting requirements.
What should you do?
- A. Create a deployment pipeline and grant the sales managers access to the pipeline.
- B. Create a PBIT file and distribute the file to the sales managers.
- C. Grant the sales managers the Build permission for the existing Power Bl datasets.
- D. Grant the sales managers admin access to the existing Power Bl workspace.
Answer: B
Explanation:
Allow sales managers to perform ad hoc sales reporting with minimal effort Power BI report templates contain the following information from the report from which they were generated:
Report pages, visuals, and other visual elements
The data model definition, including the schema, relationships, measures, and other model definition items All query definitions, such as queries, Query Parameters, and other query elements What is not included in templates is the report's data.
Report templates use the file extension .PBIT (compare to Power BI Desktop reports, which use the .PBIX extension).
Note: With Power BI Desktop, you can create compelling reports that share insights across your entire organization. With Power BI Desktop templates, you can streamline your work by creating a report template, based on an existing template, which you or other users in your organization can use as a starting point for a new report's layout, data model, and queries. Templates in Power BI Desktop help you jump-start and standardize report creation.
NEW QUESTION # 96
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 section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have the Power BI data model shown in the exhibit (Click the Exhibit tab.)
Users indicate that when they build reports from the data model, the reports take a long time to load.
You need to recommend a solution to reduce the load times of the reports.
Solution: You recommend denormalizing the data model.
Does this meet the goal?
- A. No
- B. Yes
Answer: B
Explanation:
Explanation
Denormalize For Performance.
Even though it might mean storing a bit of redundant data, schema denormalization can sometimes provide better query performance. The only question then becomes is the extra space used worth the performance benefit.
Reference: https://www.mssqltips.com/sqlservertutorial/3211/denormalize-for-performance/
NEW QUESTION # 97
You have a file named File1.txt that has the following characteristics:
* A header row
* Tab delimited values
* UNIX-style line endings
You need to read File1.txt by using an Azure Synapse Analytics serverless SQL pool.
Which query should you execute?
- A. Option B
- B. Option D
- C. Option A
- D. Option C
Answer: C
Explanation:
Explanation
Use FIELDTERMINATOR ='\t' for tab.
Use ROWTERMINATOR ='\0x0A ' for UNIX-style line endings
Use FIRSTROW= 2 for a header row
Note: Using Row Terminators
The row terminator can be the same character as the terminator for the last field. Generally, however, a distinct row terminator is useful. For example, to produce tabular output, terminate the last field in each row with the newline character (\n) and all other fields with the tab character (\t).
If you want to output a line feed character only (LF) as the row terminator - as is typical on Unix and Linux computers - use hexadecimal notation to specify the LF row terminator. For example:
bcp -r '0x0A'
FIRSTROW
FIRSTROW =first_row Specifies the number of the first row to load. The default is 1. This indicates the first row in the specified data file. The row numbers are determined by counting the row terminators. FIRSTROW is 1-based.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/import-export/specify-field-and-row-terminators-sql-se
https://docs.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql
NEW QUESTION # 98
You have a Power Bl dataset that has the query dependencies shown in the following exhibit.
Use the drop-down menus to select the answer choice that completes each statement based on the information
presented in the graphic.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
NEW QUESTION # 99
You use Advanced Editor in Power Query Editor to edit a query that references two tables named Sales and Commission. A sample of the data in the Sales table is shown in the following table.
A sample of the data in the Commission table is shown in the following table.
You need to merge the tables by using Power Query Editor without losing any rows in the Sales table.
How should you complete the query? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
NEW QUESTION # 100
......
Microsoft DP-500 certification exam is an essential certification for professionals who design and implement analytical solutions using Microsoft Azure and Microsoft Power BI. Designing and Implementing Enterprise-Scale Analytics Solutions Using Microsoft Azure and Microsoft Power BI certification validates the skills and knowledge required to design and implement big data batch processing and real-time processing solutions, as well as data storage solutions. Passing the DP-500 exam and earning the Microsoft Certified: Azure Data Engineer Associate certification enables professionals to demonstrate their expertise in designing and implementing enterprise-scale analytics solutions using Microsoft Azure and Microsoft Power BI.
Microsoft DP-500 certification exam is recognized worldwide as a leading certification for professionals who work with Microsoft Azure and Microsoft Power BI. Designing and Implementing Enterprise-Scale Analytics Solutions Using Microsoft Azure and Microsoft Power BI certification demonstrates a candidate's deep understanding of the latest technologies and best practices for designing, implementing, and maintaining large-scale data solutions using Microsoft's cloud-based platform.
Pass Your DP-500 Dumps as PDF Updated on 2023 With 150 Questions: https://www.fast2test.com/DP-500-premium-file.html
Microsoft DP-500 Real Exam Questions and Answers FREE: https://drive.google.com/open?id=1jlIoIWSnhzYOCUPKZpJMt90j8Wn0oI_c