Free Microsoft DP-600 Practice Test & Real Exam Questions
You have a Fabric tenant that contains a workspace named Workspace1. Workspace1 contains a lakehouse named I.H1 and a warehouse named DW1. I.H1 contains a table named signindata that is in the dho schema.
You need to create a stored procedure in DW1 that deduplicates the data in the signindata table.
How should you complete the T-SQL statement? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
You need to create a stored procedure in DW1 that deduplicates the data in the signindata table.
How should you complete the T-SQL statement? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Correct Answer:
Explanation:
Scenario Recap
Fabric tenant # Workspace1
Contains:
Lakehouse LH1 (with table signindata in schema dho)
Warehouse DW1
Task: Create a stored procedure in DW1 that deduplicates rows in signindata.
Step 1: Stored procedure structure
In T-SQL, stored procedures begin with:
AS
BEGIN
-- logic
END
So the correct option for the first blank is BEGIN.
BEGIN DISTRIBUTED TRANSACTION is not needed because we are not spanning multiple servers or needing distributed transactions.
SET is not the right way to start the logic block.
Step 2: Deduplication logic
To remove duplicates from signindata, the query should return unique rows.
The simplest way is:
SELECT DISTINCT PersonID, FirstName, LastName
FROM dho.signindata;
Thus the correct choice for the second blank is DISTINCT.
GROUP BY could also deduplicate but is less efficient here since no aggregation is requested.
TOP 100 PERCENT WITH TIES is irrelevant.
Step 3: Final T-SQL stored procedure
CREATE PROCEDURE dbo.usp_GetPerson
AS
BEGIN
SELECT DISTINCT PersonID, FirstName, LastName
FROM dho.signindata;
END;
Why this is correct
BEGIN # correct stored procedure structure.
DISTINCT # ensures deduplication of rows from signindata.
References
CREATE PROCEDURE (Transact-SQL)
DISTINCT (Transact-SQL)
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 a Fabric tenant that contains a semantic model named Model1.
You discover that the following query performs slowly against Model1.
You need to reduce the execution time of the query.
Solution: You replace line 4 by using the following code:
Does this meet the goal?
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 a Fabric tenant that contains a semantic model named Model1.
You discover that the following query performs slowly against Model1.
You need to reduce the execution time of the query.
Solution: You replace line 4 by using the following code:
Does this meet the goal?
Correct Answer: A
Vote an answer
Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).
You have a Fabric tenant that contains a warehouse named DW1 and a lakehouse named LH1. DW1 contains a table named Sales.Product. LH1 contains a table named Sales.Orders.
You plan to schedule an automated process that will create a new point-in-time (PIT) table named Sales.
ProductOrder in DW1. Sales.ProductOrder will be built by using the results of a query that will join Sales.
Product and Sales.Orders.
You need to ensure that the types of columns in Sales. ProductOrder match the column types in the source tables. The solution must minimize the number of operations required to create the new table.
Which operation should you use?
You plan to schedule an automated process that will create a new point-in-time (PIT) table named Sales.
ProductOrder in DW1. Sales.ProductOrder will be built by using the results of a query that will join Sales.
Product and Sales.Orders.
You need to ensure that the types of columns in Sales. ProductOrder match the column types in the source tables. The solution must minimize the number of operations required to create the new table.
Which operation should you use?
Correct Answer: D
Vote an answer
Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).
You have a Fabric warehouse that contains a table named Staging.Sales. Staging.Sales contains the following columns.
You need to write a T-SQL query that will return data for the year 2023 that displays ProductID and ProductName arxl has a summarized Amount that is higher than 10,000. Which query should you use?
You need to write a T-SQL query that will return data for the year 2023 that displays ProductID and ProductName arxl has a summarized Amount that is higher than 10,000. Which query should you use?
Correct Answer: C
Vote an answer
Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).
You have a Fabric workspace named Workspace1 that contains a KQL database The database contains a table named Table1. Workspace1 is allocated to an F 128 capacity. Table1 contains 10 billion rows and 25 columns named d through c25. Column cl contains identifiers that range from 1 to 15 and are equally distributed.
You have the following query.
You modify the query as shown.
How will the execution time of the query be affected?
You have the following query.
You modify the query as shown.
How will the execution time of the query be affected?
Correct Answer: A
Vote an answer
You are implementing two dimension tables named Customers and Products in a Fabric warehouse.
You need to use slowly changing dimension (SCO) to manage the versioning of data. The solution must meet the requirements shown in the following table.
Which type of SCD should you use for each table? To answer, drag the appropriate SCD types to the correct tables. Each SCD type may be used once, more than once, or not at all. You may need to drag the split bar between panes o r scroll to view content.
NOTE: Each correct selection is worth one point.
You need to use slowly changing dimension (SCO) to manage the versioning of data. The solution must meet the requirements shown in the following table.
Which type of SCD should you use for each table? To answer, drag the appropriate SCD types to the correct tables. Each SCD type may be used once, more than once, or not at all. You may need to drag the split bar between panes o r scroll to view content.
NOTE: Each correct selection is worth one point.
Correct Answer:
Explanation:
For the Customers table, where the requirement is to create a new version of the row, you would use:
Type 2 SCD: This type allows for the creation of a new record each time a change occurs, preserving the history of changes over time.
For the Products table, where the requirement is to overwrite the existing value in the latest row, you would use:
Type 1 SCD: This type updates the record directly, without preserving historical data.
You have a Microsoft Power Bl semantic model that contains measures. The measures use multiple calculate functions and a filter function.
You are evaluating the performance of the measures.
In which use case will replacing the filter function with the keepfilters function reduce execution time?
You are evaluating the performance of the measures.
In which use case will replacing the filter function with the keepfilters function reduce execution time?
Correct Answer: D
Vote an answer
Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).
You have a Microsoft Power Bl semantic model that is used by your company ' s finance and sales departments for monthly reporting. The semantic model contains two explicit measures named sales Amount and Orders and a measure named KPI Title that returns text for dynamic visual titles.
You need to implement a calculation group named Time Intelligence that contains a column named Time Calculation. The solution must meet the following requirements
* Apply time-intelligence patterns to both explicit measures by using SELECTEDMEASURE().
* Format only the yoy% calculation item as a percentage.
What should you configure ' To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point
You need to implement a calculation group named Time Intelligence that contains a column named Time Calculation. The solution must meet the following requirements
* Apply time-intelligence patterns to both explicit measures by using SELECTEDMEASURE().
* Format only the yoy% calculation item as a percentage.
What should you configure ' To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point
Correct Answer:
Explanation:
You have a Fabric tenant that contains a lakehouse named LH1 and a notebook.
You have a Parquet file named invoice1 that contains a column named InvoiceDateKey of the timestamp data type.
You need to create a PySpark script that will import invoice1 and create a table named fact_sale in LH1. The solution must meet the following requirements:
* A new column named Year must be added to fact_sale.
* The values in Year must use the InvoiceDateKey column.
How should you complete the script? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
You have a Parquet file named invoice1 that contains a column named InvoiceDateKey of the timestamp data type.
You need to create a PySpark script that will import invoice1 and create a table named fact_sale in LH1. The solution must meet the following requirements:
* A new column named Year must be added to fact_sale.
* The values in Year must use the InvoiceDateKey column.
How should you complete the script? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Correct Answer:
Explanation:
You have Fabric tenant that contains a semantic model named Mode1. Model! contains the following tables.
You are designing a Microsoft Power B1 report named Report! that will deliver near-real-time (NRT) shipping data. Report1 will contain data about many shipped packages.
You need to ensure that each table is in the appropriate storage mode to support Report1. The solution must support near-realtime data delivery while maintaining high query performance.
Which storage mode should you select for each table? To answer, drag the appropriate storage modes to the correct tables. Each mode 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.
You are designing a Microsoft Power B1 report named Report! that will deliver near-real-time (NRT) shipping data. Report1 will contain data about many shipped packages.
You need to ensure that each table is in the appropriate storage mode to support Report1. The solution must support near-realtime data delivery while maintaining high query performance.
Which storage mode should you select for each table? To answer, drag the appropriate storage modes to the correct tables. Each mode 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.
Correct Answer:
Explanation:
You have a Fabric tenant that contains a semantic model.
You need to prevent report creators from populating visuals by using implicit measures.
What are two tools that you can use to achieve the goal? Each correct answer presents a complete solution.
NOTE: Each correct answer is worth one point.
You need to prevent report creators from populating visuals by using implicit measures.
What are two tools that you can use to achieve the goal? Each correct answer presents a complete solution.
NOTE: Each correct answer is worth one point.
Correct Answer: B,D
Vote an answer
Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).
