DP-600: Microsoft Fabric Analytics Engineer Associate Practice Exam

What the DP-600 exam covers

• Maintain a data analytics solution207 questions
• Prepare data352 questions
• Implement and manage semantic models207 questions

Free DP-600 sample questions

A sample of 10 questions with answers and explanations. Sign up free to practice all 766.

1. Question 1Maintain a data analytics solution

   You have a Microsoft Fabric workspace at Contoso. A business user must be able to view existing Power BI reports in the workspace but must not create, edit, or delete any items. You want to follow the principle of least privilege. Which workspace role should you assign?

   • AViewerCorrect
   • BContributor
   • CMember
   • DAdmin
   ✓ Correct answer: A

   The Viewer role grants read-only access to the content of a workspace, letting users open and interact with reports and dashboards without modifying anything. Contributor, Member, and Admin all include the ability to create or edit items, which exceeds what a report consumer needs.

   Why the other options are wrong

   • BContributor can create, edit, and delete items in the workspace, which is more than a report viewer requires.
   • CMember can add other users and reshare items in addition to editing content, exceeding least privilege.
   • DAdmin has full control including deleting the workspace, far beyond viewing reports.
2. Question 2Maintain a data analytics solution

   An analyst at Litware hides a sensitive column in the model's Report view so it drops off the field list, and a colleague warns this is not security. Why is hiding not a security control?

   • AHidden columns are still queryable through DAX or the XMLA endpointCorrect
   • BHidden columns are removed only after the next model refresh
   • CHiding can be applied to measures but never to columns
   • DHidden columns still appear in the Data view data grid
   ✓ Correct answer: A

   A hidden column is merely removed from the default field list, but it remains present and can be returned by a DAX query or a tool connected over the XMLA endpoint. To actually block access you must apply object-level security.

   Why the other options are wrong

   • BHiding takes effect immediately and is unrelated to a data refresh.
   • CBoth columns and measures can be hidden, so this statement is incorrect.
   • DWhether it shows in Data view is not the point; the column is still queryable.
3. Question 3Maintain a data analytics solution

   When a developer saves ContosoSales as a .pbip, Power BI Desktop creates a ContosoSales.pbip file alongside project folders. Which folders hold the definitions?

   • AA Dataflow folder and a Pipeline folder.
   • BA Dashboard folder and a Dataset folder.
   • CA Notebook folder and a Lakehouse folder.
   • DA Report folder and a SemanticModel folder.Correct
   ✓ Correct answer: D

   Alongside the .pbip file, Power BI Desktop generates a report folder and a semantic model folder that contain the PBIR and TMDL definition files respectively. These folders are what you place under source control.

   Why the other options are wrong

   • ADataflows and pipelines are Fabric items, not parts of a .pbip project.
   • BA .pbip does not create dashboard or dataset folders.
   • CNotebooks and lakehouses are Fabric workspace items, not .pbip folders.
4. Question 4Prepare data

   After you configure mirroring of an Azure SQL Database in Fabric, how is the mirrored data made available for analytics?

   • AAs read-only Delta tables in OneLake with a SQL analytics endpoint.Correct
   • BAs a live DirectQuery connection to the source operational database only.
   • CAs CSV files under the mirrored item Files section only.
   • DAs a KQL database created inside a new eventhouse item.
   ✓ Correct answer: A

   A mirrored database continuously replicates its data into OneLake as Delta tables and provides a read-only SQL analytics endpoint over them. The tables can be queried with T-SQL and consumed by Direct Lake semantic models. This makes the operational data analytics-ready without building pipelines.

   Why the other options are wrong

   • BMirroring materializes Delta data in OneLake rather than only proxying live queries to the source.
   • CMirrored data is exposed as Delta tables, not merely as CSV files.
   • DMirroring does not create a KQL database; that is an eventhouse construct.
5. Question 5Prepare data

   A query in the Fabrikam Foods warehouse returns no rows when filtering WHERE Country = 'usa', although the data stores 'USA'. What explains this?

   • AString literals must be written with double quotes in Fabric
   • BThe column was created as NVARCHAR
   • CWHERE clauses are unsupported on text columns
   • DThe warehouse uses a case-sensitive collation by defaultCorrect
   ✓ Correct answer: D

   Fabric warehouses default to the case-sensitive collation Latin1_General_100_BIN2_UTF8, so 'usa' does not match stored 'USA'. You can normalize case with UPPER or LOWER, or create the warehouse with a case-insensitive collation. Quote style, NVARCHAR, and WHERE support are not the cause.

   Why the other options are wrong

   • AT-SQL string literals use single quotes, and quoting is not the issue.
   • BNVARCHAR is not the cause, and Fabric uses UTF-8 VARCHAR.
   • CWHERE clauses work fine on text columns.
6. Question 6Prepare data

   You have a Fabric warehouse table named Events with columns DeviceId, EventTime, and Status. You need to return only the most recent row for each DeviceId. Which approach should you use?

   • AUse ROW_NUMBER() OVER (PARTITION BY DeviceId ORDER BY EventTime DESC) and keep rows numbered 1Correct
   • BUse ROW_NUMBER() OVER (ORDER BY EventTime DESC) and keep rows numbered 1
   • CUse DENSE_RANK() OVER (PARTITION BY EventTime ORDER BY DeviceId DESC) and keep rows numbered 1
   • DUse COUNT(*) OVER (PARTITION BY DeviceId) and keep rows numbered 1
   ✓ Correct answer: A

   Partitioning by DeviceId restarts the numbering for each device, and ordering by EventTime descending assigns 1 to the newest event within each device. Keeping only the rows numbered 1 returns the most recent row per DeviceId.

   Why the other options are wrong

   • BWithout a partition, the numbering is global, so only one overall latest row is numbered 1.
   • CPartitioning by EventTime and ordering by DeviceId does not isolate the newest row per device.
   • DCOUNT does not assign a per-row sequence, so filtering on the value 1 is meaningless here.
7. Question 7Prepare data

   You need a DAX query that returns the 10 stores with the highest total revenue from a semantic model. Which function returns that subset of rows ranked by the revenue expression?

   • ATOPNCorrect
   • BRANKX
   • CSAMPLE
   • DFIRSTNONBLANK
   ✓ Correct answer: A

   TOPN takes a row count, a table, and an order-by expression, returning that many rows ranked by the expression. TOPN(10, Store, [Revenue], DESC) returns the ten highest-revenue stores. You typically add ORDER BY to guarantee the display order of the returned rows.

   Why the other options are wrong

   • BRANKX returns a rank number per row rather than a filtered subset of the top rows.
   • CSAMPLE returns an evenly distributed sample of rows, not the highest-ranked ones.
   • DFIRSTNONBLANK returns a single value from the first non-blank row of a column, not the top ten rows.
8. Question 8Implement and manage semantic models

   In a well-designed semantic model, why are surrogate key columns that are used only for relationships typically hidden from report view?

   • ATo keep the Fields pane clean and stop users from placing meaningless keys on visuals.Correct
   • BTo reduce the model's memory footprint by not storing the key values at all.
   • CTo encrypt the key values so that report consumers are unable to read them.
   • DTo prevent the key columns from participating in their table relationships.
   ✓ Correct answer: A

   Surrogate keys carry no analytical meaning, so hiding them declutters the Fields pane and stops report authors from accidentally summarizing or grouping by a key. Hiding a column does not remove it from memory, and the key still functions in the relationship. Hiding is a usability and governance choice, not encryption.

   Why the other options are wrong

   • BHiding a column does not stop it from being stored; the key still occupies memory.
   • CHiding is not encryption; it only removes the column from the default field list.
   • DHidden key columns still participate in their relationships; hiding does not disable the relationship.
9. Question 9Implement and manage semantic models

   Using the DAX window functions, you need a measure returning Total Sales for the immediately preceding month in a visual ordered by month, without a marked date table. Which function retrieves the prior month's row relative to the current one?

   • AOFFSET(-1, ...) with an ORDERBY on the month columnCorrect
   • BINDEX(-1, ...) with an ORDERBY on the month column
   • CWINDOW(-1, ABS, -1, ABS, ...) with an ORDERBY on the month column
   • DPREVIOUSMONTH applied to the month column
   ✓ Correct answer: A

   OFFSET navigates by a relative delta within an ordering, so OFFSET(-1, ...) with ORDERBY on month returns the previous month's row. Wrapping it in CALCULATE evaluates Total Sales in that shifted context. Window functions rely on ordering rather than a date table.

   Why the other options are wrong

   • BINDEX returns an absolute position (for example the first or last row), not a relative previous row.
   • CWINDOW returns a range of rows; a single prior row is an OFFSET, not a two-bound window.
   • DPREVIOUSMONTH is a time intelligence function that requires a marked date table.
10. Question 10Implement and manage semantic models

    A measure references SUM(Sales[Amount]) in three separate places within a single expression, and it is slow on a large fact table. Which change reduces redundant work the most?

    • AWrap each SUM in a separate CALCULATE function call.
    • BReplace each SUM with SUMX over the Sales table.
    • CConvert the measure into a calculated column instead.
    • DStore the SUM in a variable and reuse the variable.Correct
    ✓ Correct answer: D

    A DAX variable (VAR) is evaluated a single time and its stored result is reused everywhere the variable is referenced. Computing SUM(Sales[Amount]) once and reusing it avoids re-evaluating the same aggregation three times. Using variables to avoid repeated evaluation is a fundamental DAX optimization.

    Why the other options are wrong

    • AWrapping each SUM in CALCULATE adds context transitions and still evaluates the aggregation three times.
    • BSUMX is an iterator that is generally slower than SUM and does not remove the repeated evaluations.
    • CA calculated column is computed per row at refresh and cannot replace a measure that reacts to filter context.

Related Microsoft resources

• DP-600 study guideKey concepts
• Microsoft practice examsAll Microsoft
• Certification pathWhere this fits
• Certification exam guides & tipsBlog
• Plans & pricingFree & paid
• SC-200 practice examRelated
• AZ-140 practice examRelated
• AZ-900 practice examRelated

DP-600 practice exam FAQ