Free Microsoft DP-600 Practice Exam with Questions & Answers

CTAS creates a new table from the results of a query, automatically inheriting the column types of the source tables. It is the simplest and most efficient way to create a point-in-time (PIT) table.

INSERT INTO requires a table to already exist.

CREATE MATERIALIZED VIEW keeps results up to date but is heavier and not required for PIT.

CLONE creates a shallow copy of an existing table, but we are joining two tables, so clone is not applicable.

Correct: A.

When you need to return all rows that are present in both tables, you use a full outer join. This type of join combines the results of both left and right outer joins and returns all rows from both tables, with matching rows from both sides where available. If there is no match, the result is NULL on the side of the join where there is no match.

Scenario Recap

Fabric tenant with workspaces: Development, Test, QA, Production.

All workspaces are already in Premium Per User (PPU) mode (which is required for deployment pipelines).

Task: Set up a release pipeline to move through Dev → Test → QA → Production.

Step 1: Create a deployment pipeline

The first step is always to create a new deployment pipeline in the Fabric/Power BI service. This establishes the structure for Dev → Test → Prod (and optionally QA).

Step 2: Create the QA stage

By default, pipelines have 3 stages (Dev, Test, Prod).

Since we need a QA stage in addition, we must explicitly add it as a stage to the pipeline.

Step 3: Assign workspaces

Once the pipeline structure is ready (with QA included), we assign each workspace to the corresponding stage:

Development → Development

Test → Test

QA → QA

Production → Production

Why Not the Other Actions?

Move each workspace to Pro license mode → Incorrect, because deployment pipelines require Premium capacity or PPU, and the workspaces already are in PPU mode.

Create a deployment rule → Deployment rules (e.g., parameter or data source rules) are optional for environment-specific changes, but not part of the initial required setup sequence.

References

Deployment pipelines overview

Assigning workspaces to deployment pipelines

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.

To identify whether throttling is occurring, you should use the Monitoring hub (B). This provides a centralized place where you can monitor and manage the health, performance, and reliability of your data estate, and see if the compute resources are being throttled. References = The use of the Monitoring hub for performance management and troubleshooting is detailed in the Azure Synapse Analytics documentation.

To view column information like the one shown in the exhibit in Power Query, you need to select the options that enable profiling and display quality and distribution details. These are: A. Enable column profile - This option turns on profiling for each column, showing statistics such as distinct and unique values. B. Show column quality details - It displays the column quality bar on top of each column showing the percentage of valid, error, and empty values. E. Show column value distribution - It enables the histogram display of value distribution for each column, which visualizes how often each value occurs.

Comprehensive Detailed Explanation

When assigning permissions to roles in a Fabric workspace for analytics workloads, you must align permissions with the responsibilities and tools typically used by each persona:

1. Data Engineers

Their primary tasks include building and transforming data pipelines, as well as making datasets usable by others.

The appropriate permission is Build Reports on the default dataset, because this allows them to create and manage reports using the curated semantic model while preparing data for analysts and scientists.

2. Data Analysts

Analysts work heavily with Apache Spark for exploratory analysis, cleansing, and shaping data.

The correct permission is Read All Apache Spark, as this grants them the ability to query and analyze Spark-based data directly without elevated permissions that are unnecessary for their role.

3. Data Scientists

Data scientists often use SQL analytics endpoints to run queries, train models, and integrate data into machine learning workflows.

The correct permission is Read All SQL analytics endpoint data, since it enables direct programmatic access to the SQL endpoint, which is needed for advanced modeling and experimentation.

Summary of Selections:

DataEngineers → Build Reports on the default dataset

DataAnalysts → Read All Apache Spark

DataScientists → Read All SQL analytics endpoint data

References (Microsoft Fabric – DP-600 exam scope):

Workspace roles and permissions in Microsoft Fabric

SQL analytics endpoint in Fabric

Apache Spark in Microsoft Fabric

The measure should use the AVERAGE function to calculate the average value.

It should reference the Response Value column from the 'Survey' table.

The 'Number of months' should be used to define the period for the average calculation.

To calculate the average overall satisfaction score using DAX, you would need to use the AVERAGE function on the response values related to satisfaction questions. The DATESINPERIOD function will help in calculating the rolling average over the last 12 months.

Scenario:

Semantic model contains a measure: TotalSalesAmount.

This measure returns sales revenue translated into a selected currency.

Requirement: Format the result using the correct currency symbol dynamically.

Analysis:

Dynamic format string in DAX is specifically designed for applying formats (like currency symbols) dynamically based on slicers or calculation context.

WINDOW function → used for windowing calculations, not formatting.

Linguistic schema → used for Q&A natural language, not for formatting.

Field parameter → used to swap fields/measures dynamically, not for formatting.

Correct Answer:

A. A dynamic format string

You should use CREATE VIEW to make the pricing group logic available for T-SQL queries.

The CASE statement should be used to determine the pricing group based on the list price.

The T-SQL statement should create a view that classifies products into pricing groups based on the list price. The CASE statement is the correct conditional logic to assign each product to the appropriate pricing group. This view will standardize the pricing group logic across different databases and semantic models.