Free Practice Questions for Microsoft DP-600 Exam

Supports complex calculated columns (with aggregate functions, calculated tables, MDX hierarchies).

Minimizes page rendering times.

Step 1 – Choosing the Mode

Direct Lake → Best for near real-time queries, avoids duplication, but has limitations (e.g., some complex calculated columns, MDX user hierarchies are not fully supported).

DirectQuery → Sends queries to the source each time. It supports complex expressions but is slow (not optimal for minimizing page rendering times).

Import → Data is loaded into VertiPaq in-memory engine, supports full DAX capabilities, calculated tables, MDX hierarchies, and provides fastest query performance.

→ Correct choice: Import.

Step 2 – Choosing Query Caching

Capacity default → Relies on the workspace/capacity setting.

Off → Disables caching, which could slow down report rendering.

On → Ensures queries are cached for faster page rendering times.

→ Correct choice: On.

Final Answer:

Mode: Import

Query Caching: On

To enable scale-out for a semantic model, you should first set Large dataset storage format to On (C) at the semantic model level. This configuration is necessary to handle larger datasets effectively in a scaled-out environment. References = Guidance on configuring large dataset storage formats for scale-out is available in the Power BI documentation.

Step 1 – Understanding star schema design

Fact tables → contain transactional, numeric, and foreign key columns (e.g., TransactionID, Date, SalesAmount, ProductID).

Dimension tables → contain descriptive attributes and business keys (e.g., ProductID, ProductName, ProductColor).

Step 2 – Column classification

TransactionID → Fact (surrogate key for the fact table).

Date → Goes into a Date Dimension (not into DimProduct).

ProductID → Business key → belongs in DimProduct.

ProductColor → Descriptive attribute → belongs in DimProduct.

ProductName → Descriptive attribute → belongs in DimProduct.

SalesAmount → Fact (measure, belongs in FactSales).

Step 3 – Select DimProduct columns

So DimProduct should include:

ProductID

ProductName

ProductColor

That matches option A.

For Type 2 Slowly Changing Dimensions (SCD):

MERGE is the standard T-SQL approach, allowing conditional UPDATE for existing rows and INSERT for new history records, in a single statement.

UPDATE + INSERT would require multiple steps and higher resource usage.

TRUNCATE + INSERT would destroy historical data, violating SCD rules.

CREATE TABLE AS SELECT is not suitable for incremental Type 2 handling.

Correct answer: B.

Comprehensive Detailed Explanation

Step 1: Reviewing the provided DAX expression

SWITCH(

SELECTEDVALUE(Metric[Metric]),

" # of Customers " , " # " ,

" Gross Margin % " , " 0.00% " ,

" #,### "

)

This is applied as a dynamic format string.

It changes the display formatting of the Value measure depending on the selected metric.

Step 2: Identify the feature

The screenshot shows Format = Dynamic in the Power BI Modeling tab.

This confirms that a dynamic format string has been applied.

So, for the first blank → dynamic format string.

Step 3: Identify the output formats

If the metric = # of Customers → format is " # " → whole numbers.

If the metric = Gross Margin % → format is " 0.00% " → percentages.

Fallback → " #,### " → also whole numbers.

Thus, the measure supports both whole numbers and percentages.

So, for the second blank → percentages or whole numbers.

References

Dynamic format strings in Power BI

SWITCH function in DAX

GRANT ALTER ON SCHEMA::schemaA TO User1;

The ALTER permission allows a user to modify the schema of an object, and granting ALTER on a schema will allow the user to perform operations like TRUNCATE TABLE on any object within that schema. It is the correct permission to grant to User1 for truncating tables in schemaA.

References =

GRANT Schema Permissions

Permissions That Can Be Granted on a Schema

We are designing a star schema for sales transactions. In a star schema:

Dimension tables contain descriptive attributes (e.g., Customer, Product, Date).

Fact tables contain measurable, quantitative data (e.g., SalesTransactionID, Quantity, UnitPrice).

Factless fact tables capture events without numeric measures.

Junk dimensions store miscellaneous low-cardinality attributes.

The requirement says dimension tables will use Type 2 Slowly Changing Dimensions (SCD) → so Customer and Date attributes belong to Dimension tables.

Field-by-field classification

CustomerCode

A business key (natural key) for the customer dimension.

Belongs in the Customer Dimension table.

Answer: Dimension.

CustomerName

A descriptive attribute of the customer.

Belongs in the Customer Dimension table.

Answer: Dimension.

SaleDate

A time attribute used for analysis.

Belongs in the Date Dimension table.

Answer: Dimension.

Final Answer:

CustomerCode → Dimension

CustomerName → Dimension

SaleDate → Dimension

The df.explain() method does not meet the goal of evaluating data to calculate statistical functions. It is used to display the physical plan that Spark will execute. References = The correct usage of the explain() function can be found in the PySpark documentation.

The Spark engine will read only the ' SalesOrderNumber ' , ' OrderDate ' , ' CustomerName ' , ' UnitPrice ' columns from Sales_raw.csv. - Yes

Removing the partition will reduce the execution time of the query. - No

Adding inferSchema= ' true ' to the options will increase the execution time of the query. - Yes

The code specifies the selection of certain columns, which means only those columns will be read into the DataFrame. Partitions in Spark are a way to optimize the execution of queries by organizing the data into parts that can be processed in parallel. Removing the partition could potentially increase the execution time because Spark would no longer be able to process the data in parallel efficiently. The inferSchema option allows Spark to automatically detect the column data types, which can increase the execution time of the initial read operation because it requires Spark to read through the data to infer the schema.