Free Microsoft DP-600 Practice Test Questions MCQs

Explanation:
The goal is to ensure that a DAX measure returns the User Principal Name (UPN) of the currently logged-in user within the Fabric service, especially after enabling Dynamic RLS. The current measure uses USERNAME(), which is returning a blank result, a common issue when RLS is enabled and the engine's authentication flow does not correctly pass the UPN to the DAX context.

USERNAME(): In the Power BI/Fabric service, this function typically returns the UPN (e.g., user@contoso.com) when Dynamic RLS is configured. However, if it returns blank, it indicates a configuration or deployment issue, or that the context is not being correctly passed.

USEROBJECT(): This function returns a string representing the Object ID of the current user from Microsoft Entra ID (a long GUID string like xxxxxxxx-xxxx-...), not the User Principal Name (UPN).

Conclusion: Replacing the function with USEROBJECT() will return the Object ID (GUID), not the UPN (e.g., user@contoso.com), thus failing to meet the stated goal.

Correct Option:

B. No
Function Purpose: The USEROBJECT() function returns the unique Object ID (GUID) of the user from Microsoft Entra ID, which is a globally unique identifier for the user.

Goal Failure: The requirement is to return the User Principal Name (UPN). Since USEROBJECT() returns the GUID, the solution fails to provide the required UPN string. The correct solution would involve fixing the RLS implementation to ensure USERNAME() works or explicitly using the custom data column configured for RLS, not changing to USEROBJECT().

Incorrect Option:

A. Yes
Incorrect Assumption: This assumes that the User Object ID (GUID) is an acceptable substitute for the UPN or that USEROBJECT() somehow returns the UPN. This is incorrect based on the function's definition.

Reference:
Microsoft Learn: USERNAME and USEROBJECT functions (DAX).

Explanation:
The goal is to identify whether maintenance tasks were performed on a Delta table in a Microsoft Fabric Lakehouse. Maintenance tasks such as OPTIMIZE and VACUUM are recorded in the Delta transaction log and can be reviewed through table history. The proposed solution, however, executes a metadata refresh operation, not a diagnostic or historical query.

Correct Option:

B. No
REFRESH TABLE customer only refreshes the table metadata and cached information so that Spark recognizes the latest data files. It does not return any information about past operations or maintenance tasks. Therefore, it cannot be used to determine whether maintenance activities were previously performed on the Delta table.

Incorrect Option:

A. Yes
This option is incorrect because REFRESH TABLE does not expose Delta Lake transaction history. It neither reports on operations such as OPTIMIZE or VACUUM nor provides performance-related diagnostics. As a result, it does not meet the requirement of identifying whether maintenance tasks occurred.

Reference:
Microsoft Learn – Delta Lake table maintenance in Microsoft Fabric

Microsoft Learn – REFRESH TABLE (Spark SQL)

Explanation:
The scenario requires determining whether table maintenance operations were performed on a Delta table in a Microsoft Fabric Lakehouse. Delta Lake records all table-level operations, including maintenance tasks, in its transaction log. By querying this log, you can review historical actions and confirm whether optimization or cleanup activities were executed, which directly supports troubleshooting performance issues.

Correct Option:

A. Yes
Running DESCRIBE HISTORY customer in Spark SQL returns the full transaction history of the Delta table, including operations such as OPTIMIZE, VACUUM, MERGE, and UPDATE. This allows you to verify whether maintenance tasks were executed and when they occurred. Therefore, this command directly meets the goal of identifying maintenance activity on the Customer table.

Incorrect Option:

B. No
This option is incorrect because DESCRIBE HISTORY is specifically designed to expose Delta table operational metadata. It provides insight into table maintenance and data modification actions. Claiming it does not meet the goal ignores the purpose and capabilities of Delta Lake’s transaction history.

Reference:
Microsoft Learn – Delta Lake table history in Microsoft Fabric

Microsoft Learn – DESCRIBE HISTORY (Delta Lake)

Explanation:
Creation Command: The requirement is to create a new table (dbo.POSCustomers) based on a query result. The most efficient and standard way to achieve this in Fabric/Synapse T-SQL environments is using the CTAS pattern: CREATE TABLE [name] AS SELECT [query].

Source Reference: In Microsoft Fabric, objects (like tables) in a Lakehouse's SQL Analytics Endpoint are exposed as distinct databases accessible from other Fabric items (like a Warehouse). To query across these boundaries (from Warehouse1 to Lakehouse1), you must explicitly qualify the source table using the three-part naming convention: LakehouseName.Schema.TableName.

Incorrect Option Explanations:

First Placeholder:
CREATE TABLE dbo.POSCustomers: This only creates an empty table structure without loading data, failing the requirement to create the table by querying the customer data.

CREATE TABLE dbo.POSCustomers AS CLONE OF: The CLONE OF command is used for creating a zero-copy clone of an existing table's schema and data in Delta Lake (Fabric/Synapse). While technically possible, it's not the correct pattern for selecting a subset of columns (postalcode, category) and creating a new table from a source query.

Second Placeholder:
FROM dbo.Customer: This would attempt to query a table named Customer within the current database (Warehouse1), failing to access the table located in Lakehouse1.

FROM dbo.POSCustomers: This refers to the table being created, which would lead to a recursive error or failure.

Reference:
Microsoft Learn: Querying data in the Fabric Warehouse using T-SQL; Microsoft Learn: Cross-database queries in Fabric.

Explanation:
The KEEPFILTERS function is often used to optimize CALCULATE expressions, especially when dealing with complex or nested filter contexts. When CALCULATE is used, it typically overrides any existing filters on the specified columns. However, when the filter uses a nested CALCULATE or complex filter expressions, the engine might spend time merging or reconciling the filter contexts. KEEPFILTERS ensures that new filters passed to CALCULATE are intersected with, rather than replacing, the existing filter context. This approach can simplify the engine's work when managing the filter interaction between the outer measure context and the inner CALCULATE filter, potentially leading to faster execution, especially in complex nesting scenarios.

Correct Option:

A. when the filter function uses a nested calculate function
Optimization: KEEPFILTERS changes the way filters interact within CALCULATE. Instead of the new filter replacing the old context, KEEPFILTERS forces an intersection between the two.

Complex Context: When a nested CALCULATE is present, the filter context is already complex. Using KEEPFILTERS prevents unnecessary merging or overwriting operations that the DAX engine would otherwise perform, simplifying the evaluation of the filter expression.

Performance Gain: By avoiding the full context transition overwrite and simplifying the filter intersection logic, especially in measures with multiple nested CALCULATE calls, performance can often be improved compared to using a basic FILTER expression that must be reconciled.

Incorrect Option:

B. when the filter function references a column from a single table that uses Import mode
Simplicity: A simple filter on a single column in an Import mode table is highly optimized by the DAX engine. Using KEEPFILTERS or FILTER in this basic scenario often results in negligible performance difference, and sometimes KEEPFILTERS can add minor overhead without significant benefit.

C. when the filter function references columns from multiple tables
Purpose: While KEEPFILTERS works with cross-table filters, its primary performance benefit comes from optimizing the interaction of filter contexts within CALCULATE, not just the number of tables involved. Filtering across multiple tables itself is a complex operation, and KEEPFILTERS does not inherently speed up the relational filtering process outside of its specific context modification role within CALCULATE.

D. when the filter function references a measure
Measure Use: A filter that references a measure typically relies on the calculation engine to evaluate the measure's result for every row, which is computationally expensive regardless of whether FILTER or KEEPFILTERS is used. Using KEEPFILTERS does not optimize the row-by-row measure calculation; it only changes how the resulting filter interacts with the existing context.

Reference:
DAX Guide: KEEPFILTERS function; Microsoft Documentation on DAX evaluation context and filter propagation.

Explanation:
The goal is to implement conditional logic to categorize the UnitPrice column into a new column called PriceRange. The CASE expression is the standard T-SQL tool for this purpose, handling multiple, sequential conditions.

Correct Option Selections:

PriceRange = CASE:
Explanation: The CASE keyword initiates the conditional expression, allowing you to define multiple WHEN...THEN... clauses to categorize data based on specific criteria. * Correct Option: CASE must be used as the first keyword to apply the required conditional logic across the UnitPrice column and generate the PriceRange value.

Incorrect Option:
Keywords like BEGIN, IF, or WHILE are for procedural programming flow control, not for defining a conditional column expression within a SELECT statement.

[2] '$250+': ELSE
Explanation: The requirement states, "In all other instances, PriceRange is '$250+'." The ELSE clause catches any value that did not satisfy the preceding WHEN conditions (i.e., UnitPrice $\ge 250$), ensuring all remaining rows are correctly categorized as '$250+$'.

Correct Option:
ELSE correctly implements the catch-all requirement for the remaining high-value prices.

Incorrect Option:
Placing CASE or END here would result in a syntax error, as ELSE is required before the final default value.

[3] (Last line before FROM): END
Explanation: The END keyword is mandatory in T-SQL to signify the conclusion of the entire CASE expression, after which the new column is aliased (implicitly in this syntax) or named.

Correct Option:
END is necessary for the T-SQL statement to be syntactically correct and complete the PriceRange column definition.

Incorrect Option: BEGIN or IF are incorrect as they do not serve to close the CASE expression.

Reference:
Microsoft Learn: CASE (Transact-SQL) documentation explains its syntax, including the necessary use of CASE, WHEN, THEN, ELSE, and END.

Explanation:
In a Fabric Lakehouse, the default semantic model (also known as the automatic dataset) is generated from the SQL analytics endpoint, not directly from the Lakehouse's Spark-managed tables. The SQL endpoint provides a read-only T-SQL interface and automatically creates a corresponding Power BI dataset. To control whether new tables are automatically added to this default semantic model, you must modify the settings of the SQL analytics endpoint.

Correct Option:

D. the SQL analytics endpoint settings:
This is the correct location. Within the SQL analytics endpoint settings for the lakehouse, you will find an option labeled "Add new tables to the default dataset" (or similar). Disabling this setting will prevent any new tables created in the lakehouse from being automatically added to the default semantic model, giving you manual control.

Incorrect Option:

A. the semantic model settings:
Once a semantic model exists, you can manage its tables and properties there, but you cannot prevent the automatic addition of new lakehouse tables at its source from this pane. This setting controls the endpoint's behavior, not the dataset's.

B. the Lakehouse1 settings:
The Lakehouse settings manage properties like the OneLake path and Spark configurations. They do not contain controls for the default semantic model or the SQL endpoint's automatic synchronization behavior.

C. the workspace settings:
Workspace settings govern permissions, Git integration, and item types allowed. They do not control the automatic synchronization between a specific lakehouse's tables and its SQL endpoint's default dataset.

Reference:
Microsoft Learn documentation, "Manage the default dataset for a lakehouse" or "SQL analytics endpoint in Microsoft Fabric," specifies that the automatic addition of new tables to the default dataset is configured via the SQL analytics endpoint settings.

Explanation:
The original query (CALCULATE( COUNTROWS('Order Item') ) > 0) counts all related rows in the 'Order Item' table for each customer to check for existence. While functional, COUNTROWS can be inefficient for simple existence checks as it forces a full scan and count of all matching rows. A more optimized pattern is to use a function designed for existence checking, which can short-circuit after finding the first matching row.

Correct Option:

A. Yes:
Replacing line 4 with NOT ( ISEMPTY ( CALCULATETABLE ( 'Order Item' ) ) ) is a valid performance optimization. The CALCULATETABLE('Order Item') returns a table filtered to the current customer's context. ISEMPTY() then checks if that filtered table is empty. This logic is semantically identical to COUNTROWS(...) > 0 but is often executed more efficiently by the engine, as it can stop scanning after confirming the presence of at least one row, potentially reducing execution time.

Incorrect Option:

B. No:
This answer would be incorrect because the proposed change is a known DAX performance optimization technique for existence checks. It preserves the exact same logical result (filtering customers with at least one order) while using a more efficient function, which directly meets the goal of reducing execution time.

Reference:
DAX performance guidance from sources like SQLBI recommends using ISEMPTY() over COUNTROWS() > 0 for existence checks because the storage engine can optimize ISEMPTY to avoid scanning all rows, leading to faster query performance, especially on large tables.

Explanation:
The original query (line 4) uses CALCULATE( COUNTROWS('Order Item') ) > 0 to filter customers who have at least one related order. This is logically equivalent to checking for the existence of related rows. The proposed solution changes this to NOT ( CALCULATE( COUNTROWS('Order Item') ) < 9 ). This new condition filters for customers who have 9 or more orders (COUNTROWS >= 9), which is a different and more restrictive logical condition. It does not optimize the same query; it changes its business result.

Correct Option:

B. No:
The solution does not meet the goal. The goal is to reduce the execution time of the query while preserving its logic. The proposed code changes the filter logic from "customers with any orders" to "customers with 9 or more orders," which would return a different set of customers and is therefore not a valid performance optimization for the original query.

Incorrect Option:

A. Yes:
This would only be correct if the code change both improved performance and returned the same result set. Since the logic is altered, it is not a correct optimization for the given query.

Reference:
Performance tuning in DAX focuses on rewriting logic for efficiency (e.g., using COUNTROWS vs EXISTS or optimizing filter context) without altering the business logic. This solution changes the output, making it incorrect for the stated goal. A true performance fix would involve techniques like using EXISTS or ensuring proper relationship filtering.

Explanation:
For a Direct Lake semantic model, you need to monitor which columns are being materialized from the Delta table into memory (VertiPaq) during query execution. This requires tools that can expose the internal storage engine activity and column usage, not just query performance. The goal is to identify column-level materialization, not just overall query patterns.

Correct Option:

A. Use the Analyze in Excel feature:
This connects Excel to the semantic model via a live connection and uses the Performance Analyzer in Power BI Desktop (when connected) to trace queries. It can capture DAX queries and show which columns are referenced, helping infer which ones are being pulled into memory.

C. Query the $system.discovered_storage_table_column_segments dynamic management view (DMV):
This DMV provides detailed, low-level storage engine information for tabular models. It lists columns and segments that are loaded into memory (VertiPaq), along with metrics like segment size and cardinality. Querying this DMV directly shows which columns are physically materialized.

Incorrect Option:

B. Use the Vertipaq Analyzer tool:
VertiPaq Analyzer is a third-party tool used primarily with Power BI Desktop (.pbix) files to analyze the on-disk structure and compression of a loaded model. It cannot connect to or analyze a live Direct Lake semantic model hosted in the Fabric service.

D. Query the discover_history dynamic management view (DMV):
There is no standard DMV named discover_history. The DMV for monitoring query history and resource usage is typically DISCOVER_COMMANDS or $SYSTEM.DISCOVER_COMMANDS. Even these show command execution details, not the specific column-level storage information required to see which columns are loaded into memory.

Reference:
Microsoft documentation on monitoring Direct Lake performance and using Tabular Object Model (TOM) DMVs lists DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS as the key DMV for viewing column storage segments in memory. "Analyze in Excel" is a standard method for generating query traces against a live model.