DP-600 Practice Exam: 40 Free Exam Questions with Answers
Prepare for the DP-600: Fabric Analytics Engineer Associate exam with 40 free exam questions. Covers governance, data prep, T-SQL, and semantic models.
I passed the DP-600 in the last few weeks, and this DP-600 practice exam is the study tool I wish I’d had the week before. Every question from this mock comes from Microsoft’s published skills outline and the topics that actually came up on the day, rewritten in my own words.
Last time, I wrote a practice test for the AI-901, readers said the questions felt close to the live exam, I then decided to write this practice test for the DP-600. If you are also looking to pass the AI-901, you can find it here: AI-901 Practice Exam: Free Practice Test.
⚠️ Note: These are original practice questions for the DP-600, based on my own exam experience. Microsoft refreshes this exam periodically, so use this for preparation, not as a guarantee.
The DP-600 sits behind the Microsoft Certified: Fabric Analytics Engineer Associate credential, and you need 700 out of 1000 to pass. Microsoft organizes the skills into three official areas, but data preparation carries almost half the exam, so I’ve split it in two. That leaves four buckets, and they line up with what the exam really drills:
Maintain a data analytics solution: 10 questions
Prepare and serve data: 12 questions
Query and analyze data with T-SQL, KQL, and DAX: 13 questions
Implement and manage semantic models: 5 questions
Quick tip: In my DP-600 exam, I had a lot of T-SQL questions, I highly recommend you to review your T-SQL basics so you can quickly understand the queries and not check the MS Learn Documentation (Yes, you get access to MS Learn documentation during this exam).
How this works? Substack has no quiz tool, so all 40 questions of this DP-600 mock test come first, grouped into four sections. The answer key sits at the bottom of each section.
Maintain a data analytics solution - DP-600 Practice Exam
1. Your analytics team must place all its semantic models and reports under version control that supports branching. The tenant is fully Azure based, and the solution must minimize setup and maintenance. What should you do?
A. Store the semantic models and reports in Azure Data Lake Storage Gen2
B. Connect the workspace to a GitHub repository
C. Connect the workspace to an Azure Repos repository
D. Store the semantic models and reports in OneDrive for Business
2. You manage eight workspaces that all belong to the same business area. You need to group them logically so they can be filtered together in the OneLake data hub. What should you use?
A. A workspace app
B. A domain
C. A deployment pipeline
D. A OneLake shortcut
3. Two security groups need read access to the same Lakehouse. Group 1 reads the data through the SQL analytics endpoint, and Group 2 reads it through Lakehouse Explorer. Following least privilege, which workspace roles should you assign, in that order?
A. Viewer, then Viewer
B. Viewer, then Contributor
C. Contributor, then Viewer
D. Member, then Contributor
4. (Choose THREE.) You have a deployment pipeline with development, test, and production stages, each assigned a workspace. Developers must deploy to development and test but not to production, following least privilege. Which three levels of access should you assign?
A. Build permission on the production semantic models
B. Admin access to the deployment pipeline
C. Viewer access to the development and test workspaces
D. Viewer access to the production workspace
E. Contributor access to the development and test workspaces
F. Contributor access to the production workspace
5. You plan to make bulk edits to a semantic model with the TMDL extension in Visual Studio Code, and you need report and model definitions saved as individual text files in a folder hierarchy for Git. Which file format should you save from Power BI Desktop?
A. .pbix
B. .pbit
C. .pbip
D. .pbids
6. A user named analyst1 must be able to truncate tables in the Sales schema only, and nowhere else. Following least privilege, which T-SQL statement should you run?
A. GRANT CONTROL ON SCHEMA::Sales TO analyst1
B. GRANT ALTER ON SCHEMA::Sales TO analyst1
C. GRANT EXECUTE ON SCHEMA::Sales TO analyst1
D. GRANT SELECT ON SCHEMA::Sales TO analyst1
7. (Choose TWO.) You are preparing a tenant for a proof of concept. Only the project team should be able to trial paid features and create Fabric items, following least privilege. Which two actions should you take in the Fabric admin portal?
A. Enable “Users can try Microsoft Fabric paid features” for the entire organization
B. Enable “Users can try Microsoft Fabric paid features” for specific security groups
C. Enable “Allow guest users to access Microsoft Fabric” for specific security groups
D. Enable “Users can create Fabric items” and exclude specific security groups
E. Enable “Users can create Fabric items” for specific security groups
8. You need to enable read/write access to a semantic model through the XMLA endpoint. Which settings should you modify first?
A. The semantic model settings
B. The workspace settings
C. The capacity settings
D. The tenant settings
9. Several times a day, every query against your warehouse slows down at once. You suspect Fabric is throttling the capacity. What should you use to confirm whether throttling is happening?
A. The capacity settings
B. The Monitoring hub
C. Dynamic management views (DMVs)
D. The Microsoft Fabric Capacity Metrics app
10. (Choose THREE.) Users must be able to create and publish custom Direct Lake semantic models with external tools, following least privilege. Which three actions should you include?
A. In tenant settings, enable “Allow XMLA endpoints and Analyze in Excel with on-premises datasets”
B. In tenant settings, enable “Allow guest users to access Microsoft Fabric”
C. In tenant settings, enable “Users can edit data models in the Power BI service”
D. In capacity settings, set the XMLA endpoint to Read Write
E. In tenant settings, enable “Users can create Fabric items”
F. In tenant settings, enable “Publish to web”
Answer key, Section 1: 1-C, 2-B, 3-B, 4-B and D and E, 5-C, 6-B, 7-B and E, 8-C, 9-D, 10-A and D and E
Prepare and serve data - DP-600 Practice Test
11. Your team needs a new data store for a proof of concept. The data includes semi-structured and unstructured files, and the store must support read access through both T-SQL and Spark. Which type of data store should you recommend?
A. A warehouse
B. A lakehouse
C. An eventhouse
D. An external Hive metastore
12. (Choose TWO.) You plan to query sales files with the SQL analytics endpoint of a Lakehouse. The files sit in an Amazon S3 bucket. Which two actions should you include so the files are queryable through the endpoint?
A. Create the shortcut in the Files section
B. Use the Parquet format
C. Use the CSV format
D. Create the shortcut in the Tables section
E. Use the Delta format
13. You add a Copy data activity to a pipeline to load external data into an existing Lakehouse table. The source schema changes regularly, and each run must replace both the table’s schema and all of its rows. What should you configure on the Copy data activity?
A. On the Source tab, add additional columns
B. On the Destination tab, set the table action to Overwrite
C. On the Settings tab, enable staging
D. On the Source tab, enable partition discovery
14. A subfolder in your Lakehouse holds several CSV files. You need to convert them to the Delta format with V-Order optimization enabled. What should you do from Lakehouse Explorer?
A. Use the Load to Tables feature
B. Create a shortcut in the Files section
C. Create a shortcut in the Tables section
D. Use the Optimize feature
15. Requirements state that data engineers must use low-code tools to ingest customer data into the data store. Which option should you recommend?
A. A stored procedure
B. A pipeline that contains a KQL activity
C. A Spark notebook
D. A dataflow
16. You need to make sure the data-loading activities in a workspace run in a specific sequence, one after another. The solution must minimize effort. What should you do?
A. Create a dataflow that has multiple steps and schedule the dataflow
B. Create and schedule a Spark notebook
C. Create and schedule a Spark job definition
D. Create a pipeline that has dependencies between activities and schedule the pipeline
17. A pipeline has two activities that run in sequence. You need to make sure a failure of the first activity does not stop the second one from running. Which conditional path should you configure between them?
A. Upon Failure
B. Upon Completion
C. Upon Skip
D. Upon Success
18. You are building a pipeline that must run a stored procedure returning the count of active customers, and the returned value must be available to downstream activities. Which type of activity should you add?
A. Switch
B. Copy data
C. Append variable
D. Lookup
19. Two lakehouses live in different workspaces, one with a table named dbo.sales and the other with dbo.customers. You need to reference both tables in the same SQL query without making extra copies of the data. What should you use?
A. A shortcut
B. A dataflow
C. A view
D. A managed table
20. (Choose TWO.) You need to populate a date dimension in the data store. There is no existing source for the dates, and the dimension must contain physically stored rows. Which two approaches meet the goal?
A. Populate the date dimension table by using a dataflow
B. Populate the date dimension table by using a Copy activity in a pipeline
C. Populate the date dimension view by using T-SQL
D. Populate the date dimension table by using a stored procedure activity in a pipeline
21. (Choose THREE.) You are building a customer dimension as a type 2 slowly changing dimension in a warehouse. Which three column types should you add that do not already exist in the source?
A. A foreign key
B. A natural key
C. An effective end date and time
D. A surrogate key
E. An effective start date and time
22. You need to copy a table named schema1.City into schema2, and the solution must minimize the amount of data copied. Which T-SQL statement should you run?
A. CREATE TABLE schema2.City AS SELECT * FROM schema1.City
B. SELECT * INTO schema2.City FROM schema1.City
C. CREATE TABLE schema2.City AS CLONE OF schema1.City
D. INSERT INTO schema2.City SELECT * FROM schema1.City
Answer key, Section 2: 11-B, 12-D and E, 13-B, 14-A, 15-D, 16-D, 17-B, 18-D, 19-A, 20-A and D, 21-C and D and E, 22-C
Query and analyze data - DP-600 Practice assessment
23. You have a warehouse table named staging_sales. You need a T-SQL query that returns 2023 data showing product ID and product name, and only rows whose summarized amount is greater than 10,000. Which approach is correct?
A. Group by product and filter the summarized amount with a HAVING clause
B. Filter the summarized amount with a WHERE clause
C. Filter a non-aggregated column with a HAVING clause
D. Reference the summarized amount by its column alias in the HAVING clause
24. A table named customers in the stage schema holds every update from a CRM, so there can be several rows per customer. You need to return the customer ID, name, postal code, and last updated time of the most recent row for each customer. What should you use?
A. Apply RANK() partitioned by customer ID and keep the rows where it equals 1
B. Group by customer ID and apply MAX() to every other column
C. Apply ROW_NUMBER() partitioned by customer ID and keep the rows where it equals 1
D. Use SELECT DISTINCT on customer ID
25. A row in your warehouse has three price columns: list_price, wholesale_price, and agent_price. You need a column that returns the highest of the three values for each row. Which T-SQL function should you use?
A. MAX
B. COALESCE
C. GREATEST
D. CHOOSE
26. For the same table, you need a column that returns agent_price if it exists, otherwise wholesale_price, and otherwise list_price. Which function fits this fallback logic?
A. COALESCE
B. GREATEST
C. IIF
D. CHOOSE
27. You need to add a column that returns the first day of the month for each order_date value. Which T-SQL function should you use?
A. DATEPART
B. DATEFROMPARTS
C. DATE_BUCKET
D. DATE_TRUNC
28. In a visual query, you merge two tables and need the result to include all rows from both tables. Which join type should you use?
A. Inner
B. Full outer
C. Left outer
D. Right anti
29. You need a DAX query, run through the XMLA endpoint, that returns a table of stores opened since December 1, 2023. How should you build it?
A. Start with SELECT and add a WHERE clause
B. Use CALCULATE without EVALUATE
C. Use DEFINE and EVALUATE, filtering the stores table with FILTER
D. Reference a single measure with no EVALUATE
30. You need a DAX query, run through the XMLA endpoint, that returns the total sales for the same period last year as a single value. How should you complete it?
A. SUMMARIZECOLUMNS with a WHERE clause
B. FILTER over the date table only
C. CALCULATETABLE returning a table
D. CALCULATE with SAMEPERIODLASTYEAR
31. You need to return the 10 highest-value orders, and if several orders tie at the 10th position, all of them must be included. Which clause should you use?
A.
SELECT TOP 10 PERCENT ... ORDER BY order_value DESCB.
SELECT ... ORDER BY order_value DESC OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLYC.
SELECT TOP (10) WITH TIES ... ORDER BY order_value DESCD.
SELECT DISTINCT TOP (10) ... ORDER BY order_value DESC
32. You need a 3-day rolling total of daily_sales, meaning each row sums the current day and the two days before it, ordered by sales_date. Which expression is correct?
A.
SUM(daily_sales) OVER (ORDER BY sales_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)B.
SUM(daily_sales) OVER (ORDER BY sales_date RANGE BETWEEN 2 FOLLOWING AND CURRENT ROW)C.
SUM(daily_sales) OVER (PARTITION BY sales_date)D.
SUM(daily_sales) OVER (ORDER BY sales_date ROWS UNBOUNDED PRECEDING)
33. For each month, you need to show the current month’s revenue next to the previous month’s revenue in the same row. Which window function should you use?
A.
LEAD(revenue) OVER (ORDER BY month)B.
FIRST_VALUE(revenue) OVER (ORDER BY month)C.
LAG(revenue) OVER (ORDER BY month)D.
ROW_NUMBER() OVER (ORDER BY month)
34. You need to return rows 21 through 40 of a result set ordered by created_date, for a paged report. Which clause should you use?
A.
SELECT TOP (40) ... ORDER BY created_dateB.
ORDER BY created_date OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLYC.
ORDER BY created_date OFFSET 21 ROWS FETCH NEXT 40 ROWS ONLYD.
WHERE ROW_NUMBER() BETWEEN 21 AND 40
35. You need to divide customers into four equally sized groups based on total_spend, ranked from highest to lowest, so you can label each quartile. Which function should you use?
A.
NTILE(4) OVER (ORDER BY total_spend DESC)B.
RANK() OVER (ORDER BY total_spend DESC)C.
PERCENT_RANK() OVER (ORDER BY total_spend DESC)D.
ROW_NUMBER() OVER (ORDER BY total_spend DESC)
Answer key, Section 3: 23-A, 24-C, 25-C, 26-A, 27-D, 28-B, 29-C, 30-D, 31-C, 32-A, 33-C, 34-B, 35-A
Implement and manage semantic models - DP-600 Practice Exam
36. You create a Direct Lake semantic model over the Delta tables of a warehouse, and that warehouse uses row-level security. When users interact with a report built on the model, which mode do the DAX queries use?
A. Direct Lake
B. Dual
C. Import
D. DirectQuery
37. (Choose TWO.) A DirectQuery semantic model queries a source with 500 million rows, and a report built on it has slow visuals across several pages. Which two features can you use to reduce query execution time?
A. User-defined aggregations
B. Automatic aggregations
C. Query caching
D. OneLake integration
38. You have a custom Direct Lake semantic model with 1 billion rows, and you connect to it with Tabular Editor through the XMLA endpoint. You need to make sure user queries always use Direct Lake mode and never fall back. What should you do?
A. From Model, configure the default mode option
B. From Partitions, configure the mode option
C. From Model, configure the storage location option
D. From Model, configure the Direct Lake behavior option
39. (Choose TWO.) An import-mode model contains an Orders table with 100 million rows. You need to reduce both the memory the model uses and the time it takes to refresh. Which two actions should you perform?
A. Split OrderDateTime into separate date and time columns
B. Replace TotalQuantity with a calculated column
C. Convert Quantity to the text data type
D. Replace TotalSalesAmount with a measure
40. You are building a calculation group and need a calculation item that shifts the selected date context to month-to-date. How should you complete the DAX expression?
A.
GENERATEwithSELECTEDVALUEB.
CALCULATEwithSELECTEDMEASUREandDATESMTDC.
COMBINEVALUESwithSELECTEDMEASURED.
FILTERwithDATESMTD
Answer key, Section 4: 36-D, 37-A and B, 38-D, 39-A and D, 40-B