Microsoft Implementing Analytics Solutions Using Microsoft Fabric - DP-600 Exam Practice Test
You have a Fabric workspace named Workspace1 and an Azure SQL database.
You plan to create a dataflow that will read data from the database, and then transform the data by performing an inner join. You need to ignore spaces in the values when performing the inner join The solution must minimize development effort. What should you do?
You plan to create a dataflow that will read data from the database, and then transform the data by performing an inner join. You need to ignore spaces in the values when performing the inner join The solution must minimize development effort. What should you do?
Correct Answer: B
Explanation: Only visible for ExamsLabs members. You can sign-up / login (it's free).
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: B
Explanation: Only visible for ExamsLabs members. You can sign-up / login (it's free).
You have a Fabric tenant that contains two workspaces named Woritspace1 and Workspace2. Workspace1 contains a lakehouse named Lakehouse1. Workspace2 contains a lakehouse named Lakehouse2. Lakehouse!
contains a table named dbo.Sales. Lakehouse2 contains a table named dbo.Customers.
You need to ensure that you can write queries that reference both dbo.Sales and dbo.Customers in the same SQL query without making additional copies of the tables.
What should you use?
contains a table named dbo.Sales. Lakehouse2 contains a table named dbo.Customers.
You need to ensure that you can write queries that reference both dbo.Sales and dbo.Customers in the same SQL query without making additional copies of the tables.
What should you use?
Correct Answer: D
Explanation: Only visible for ExamsLabs members. You can sign-up / login (it's free).
You have a Fabric tenant named Tenant1 that contains a workspace named WS1. WS1 uses a capacity named C1 and contains a dawset named DS1. You need to ensure read-write access to DS1 is available by using the XMLA endpoint. What should be modified first?
Correct Answer: A
You have a Fabric warehouse that contains two tables named DimDate and Trips.
DimDate contains the following fields.
Trips contains the following fields.
You need to compare the average miles per trip for statutory holidays versus non-statutory holidays.
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.
DimDate contains the following fields.
Trips contains the following fields.
You need to compare the average miles per trip for statutory holidays versus non-statutory holidays.
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:
Comprehensive Detailed Explanation
Step 1: Requirement
We need to compare the average miles per trip for:
Statutory holidays (when IsHoliday = 1 )
Non-statutory holidays (when IsHoliday = 0 )
Step 2: Formula for average miles per trip
Average miles per trip = total miles ÷ number of trips
Total miles # SUM(t.tripDistance)
Number of trips # COUNT(t.tripID)
So the calculation is:
( SUM (t.tripDistance) / COUNT (t.tripID)) AS MilesPerTrip
Step 3: Grouping
We need a comparison by holiday status .
So we must group the results by:
GROUP BY d.IsHoliday
This ensures we get two rows: one for IsHoliday = 1 and one for IsHoliday = 0 .
Step 4: Final Query
SELECT
d.IsHoliday,
( SUM (t.tripDistance) / COUNT (t.tripID)) AS MilesPerTrip
FROM DimDate d
INNER JOIN Trips t ON d.DateID = t.DateID
GROUP BY d.IsHoliday;
Why This is Correct
The formula ensures average miles per trip .
Grouping ensures comparison between holidays vs non-holidays .
Efficient aggregation, minimal computation.
References
Aggregate functions in T-SQL
GROUP BY clause
You have a Fabric tenant that contains customer churn data stored as Parquet files in OneLake. The data contains details about customer demographics and product usage.
You create a Fabric notebook to read the data into a Spark DataFrame. You then create column charts in the notebook that show the distribution of retained customers as compared to lost customers based on geography, the number of products purchased, age. and customer tenure.
Which type of analytics are you performing?
You create a Fabric notebook to read the data into a Spark DataFrame. You then create column charts in the notebook that show the distribution of retained customers as compared to lost customers based on geography, the number of products purchased, age. and customer tenure.
Which type of analytics are you performing?
Correct Answer: D
Explanation: Only visible for ExamsLabs members. You can sign-up / login (it's free).
You have a Fabric workspace named Workspace1 that contains an eventstream named Eventstream1.
Eventstream1 reads data from an Azure event hub named Eventhub1.
Eventhub1 contains the following columns.
Name
Data type
MachineId
Int
Payload
Dynamic
Datetime
Datetime
Location
String
You need to add a continuous percentile calculation to the Payload column. The solution must minimize development effort.
What should you do?
Eventstream1 reads data from an Azure event hub named Eventhub1.
Eventhub1 contains the following columns.
Name
Data type
MachineId
Int
Payload
Dynamic
Datetime
Datetime
Location
String
You need to add a continuous percentile calculation to the Payload column. The solution must minimize development effort.
What should you do?
Correct Answer: C
You have a Fabric warehouse that contains the following data.
The data has the following characteristics:
* Each customer is assigned a unique CustomerlD value.
* Each customer is associated to a single SalesRegion value.
* Each customer is associated to a single CustomerAddress value.
* The Customer table contains 5 million rows.
* All foreign key values are non-null.
You need to create a view to denormalize the data into a customer dimension that contains one row per distinct CustomerlD value. The solution must minimize query processing time and resources.
How should you complete the T-SQL statement? To answer, select the appropriate options in the answer area.
The data has the following characteristics:
* Each customer is assigned a unique CustomerlD value.
* Each customer is associated to a single SalesRegion value.
* Each customer is associated to a single CustomerAddress value.
* The Customer table contains 5 million rows.
* All foreign key values are non-null.
You need to create a view to denormalize the data into a customer dimension that contains one row per distinct CustomerlD value. The solution must minimize query processing time and resources.
How should you complete the T-SQL statement? To answer, select the appropriate options in the answer area.
Correct Answer:
Explanation:
The goal is to denormalize the data into a customer dimension with one row per distinct CustomerID, minimizing query processing time and resources. Since each customer is associated with a single SalesRegion and a single CustomerAddress, and all foreign key values are non-null, a left outer join is appropriate to ensure all customers from the Customer table are included, even if there are any unexpected missing addresses (though the problem states each customer has a single address). However, given the non-null foreign key constraint, an inner join could also work, but left outer join is safer and aligns with denormalization to ensure completeness.
The join condition should link the Customer table (C) with the CustomerAddress table (CA) using CustomerID, as this ensures each customer ' s address data is included.
The join with SalesRegion (SR) on SalesRegionID is already included in the query, and since each customer has a single SalesRegion, it fits the denormalization requirement.
The where CA.AddressType = ' Main Office ' filter ensures only the main office address is included, which is a valid constraint for a customer dimension.
Thus, the completed T-SQL statement would effectively denormalize the data while minimizing resource use by leveraging the existing relationships and constraints.
You have a Fabric tenant named Tenant! that contains a lakehouse named Lakehouse1.
You need to add data to Lakehouse1 from a CSV file in an Azure Storage account outside of Fabric. The solution must minimize development effort.
What should you use to add the data?
You need to add data to Lakehouse1 from a CSV file in an Azure Storage account outside of Fabric. The solution must minimize development effort.
What should you use to add the data?
Correct Answer: D
Explanation: Only visible for ExamsLabs members. You can sign-up / login (it's free).
You have a Fabric tenant that contains two workspaces named Workspace1 and Workspace2.
Workspace1 is used as the development environment.
Workspace2 is used as the production environment.
Each environment uses a different storage account.
Workspace1 contains a Dataflow Gen2 named Dataflow1. The data source of Dataflow1 is a CSV file in blob storage.
You plan to implement a deployment pipeline to deploy items from Workspace1 to Workspace2.
You need to ensure that the data source references the correct location in the production environment.
What should you do?
Workspace1 is used as the development environment.
Workspace2 is used as the production environment.
Each environment uses a different storage account.
Workspace1 contains a Dataflow Gen2 named Dataflow1. The data source of Dataflow1 is a CSV file in blob storage.
You plan to implement a deployment pipeline to deploy items from Workspace1 to Workspace2.
You need to ensure that the data source references the correct location in the production environment.
What should you do?
Correct Answer: A
Explanation: Only visible for ExamsLabs members. You can sign-up / login (it's free).
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 plan to deploy Microsoft Power BI items by using Fabric deployment pipelines. You have a deployment pipeline that contains three stages named Development, Test, and Production. A workspace is assigned to each stage.
You need to provide Power BI developers with access to the pipeline. The solution must meet the following requirements:
Ensure that the developers can deploy items to the workspaces for Development and Test.
Prevent the developers from deploying items to the workspace for Production.
Ensure that developers can view items in Production.
Follow the principle of least privilege.
Which three levels of access should you assign to the developers? Each correct answer presents part of the solution.
NOTE: Each correct answer is worth one point.
You need to provide Power BI developers with access to the pipeline. The solution must meet the following requirements:
Ensure that the developers can deploy items to the workspaces for Development and Test.
Prevent the developers from deploying items to the workspace for Production.
Ensure that developers can view items in Production.
Follow the principle of least privilege.
Which three levels of access should you assign to the developers? Each correct answer presents part of the solution.
NOTE: Each correct answer is worth one point.
Correct Answer: A,C,F
You have a Fabric tenant that contains a workspace named Workspace1. Workspace1 contains a data pipeline named Pipeline1 and a lakehouse named Lakehouse1.
You perform the following actions:
Create a workspace named Workspace2.
Create a deployment pipeline named DeployPipeline1 that will deploy items from Workspace1 to Workspace2.
Add a folder named Folder1 to Workspace1.
Move Lakehouse1 to Folder1.
Run DeployPipeline1.
Which structure will Workspace2 have when DeployPipeline1 is complete?
You perform the following actions:
Create a workspace named Workspace2.
Create a deployment pipeline named DeployPipeline1 that will deploy items from Workspace1 to Workspace2.
Add a folder named Folder1 to Workspace1.
Move Lakehouse1 to Folder1.
Run DeployPipeline1.
Which structure will Workspace2 have when DeployPipeline1 is complete?
Correct Answer: D
Explanation: Only visible for ExamsLabs members. You can sign-up / login (it's free).