44 free Microsoft DP-600 practice questions from the CertStash pack of 222, each with the correct answer and a full explanation. The rule is the same for every exam: ten free on the product page, 44 free here, all 222 in the pack, and each step includes the one before it.
Get all 222 questions (US$39) · Download the first 10 as a PDF · Read the study guide
Question 1
You are the administrator of a Fabric workspace that contains a lakehouse named Lakehouse1. Lakehouse1 contains the following tables: Table1: A Delta table created by using a shortcut Table2: An external table created by using Spark Table3: A managed table You plan to connect to Lakehouse1 by using its SQL endpoint. What will you be able to do after connecting to Lakehouse1?
Show answer and explanation
Correct answer: A. Read Table3.
When connecting to a lakehouse via its SQL endpoint, you can only read managed tables. Table3 is a managed table created and maintained by Fabric, making it readable through the SQL endpoint. Shortcuts (Table1) and external tables created by Spark (Table2) are not accessible through SQL endpoints, as they are not part of the managed table schema. Updates to any tables through SQL endpoints are not permitted; SQL endpoint connections are read-only.
Why the other options are wrong
- B. SQL endpoint connections are read-only and do not permit updates to any data.
- C. External tables created using Spark are not accessible through SQL endpoints.
- D. Delta tables created by shortcuts are not directly accessible through SQL endpoint queries; shortcuts are not included in the SQL schema.
Question 2
You have a Fabric tenant that contains a warehouse. You use a dataflow to load a new dataset from OneLake to the warehouse. You need to add a PowerQuery step to identify the maximum values for the numeric columns. Which function should you include in the step?
Show answer and explanation
Correct answer: D. Table.Profile
Table.Profile returns a table of statistics for every column in the input, including Min, Max, Average, Count and NullCount. Adding it as a step gives the maximum value of each numeric column in one call. This is the Power Query function designed for this kind of column profiling.
Why the other options are wrong
- A. Table.MaxN returns the top N rows based on a comparison criterion, not the maximum value of each column.
- B. Table.Max returns the single row with the largest value for one comparison criterion, not the maximum of every numeric column.
- C. Table.Range returns a set of rows starting at a given offset and does not calculate maximum values.
Question 3
You have a Fabric tenant that contains a machine learning model registered in a Fabric workspace. You need to use the model to generate predictions by using the PREDICT function in a Fabric notebook. Which two languages can you use to perform model scoring? Each correct answer presents a complete solution. NOTE: Each correct answer is worth one point.
Show answer and explanation
Correct answer: C, D. Spark SQL; PySpark
The PREDICT function in Fabric notebooks can be used with Spark SQL and PySpark to generate predictions from registered machine learning models. Spark SQL allows inline prediction queries within SQL statements, while PySpark provides programmatic access to the PREDICT function for model scoring operations. Both languages integrate with Fabric's ML runtime to execute registered models.
Why the other options are wrong
- A. T-SQL is not available for use in Fabric notebooks for model scoring operations.
- B. DAX is a Power BI language and cannot be used in Fabric notebooks for PREDICT function operations.
Question 4
You are analyzing the data in a Fabric notebook. You have a Spark DataFrame assigned to a variable named df. You need to use the Chart view in the notebook to explore the data manually. Which function should you run to make the data available in the Chart view?
Show answer and explanation
Correct answer: D. display
The display() function is the correct method to make Spark DataFrame data available in the Chart view of a Fabric notebook. This function renders the data in an interactive visual format that enables manual exploration through charting and filtering capabilities. The display() function is specifically designed to surface data for interactive visualization in notebooks.
Why the other options are wrong
- A. displayHTML is used for rendering HTML content, not for displaying DataFrames in Chart view.
- B. show is not a standard Fabric notebook function for making data available in Chart view.
- C. write is used for persisting data to external storage, not for displaying data in Chart view.
Question 5
You have a Fabric tenant that contains a Microsoft Power BI report named Report1. Report1 includes a Python visual. Data displayed by the visual is grouped automatically and duplicate rows are NOT displayed. You need all rows to appear in the visual. What should you do?
Show answer and explanation
Correct answer: C. Add a unique field to each row.
Power BI passes data to a Python visual after grouping identical rows, so duplicate rows are removed before the script runs. Adding a field with a unique value for every row, such as an index column, makes each row distinct. All rows are then passed to the Python script and displayed.
Why the other options are wrong
- A. Referencing columns by index in the Python code does not change the data Power BI passes to the script, which has already been deduplicated.
- B. The Sort by column property only controls sort order and does not stop Power BI from grouping duplicate rows.
- D. Changing the Summarize by property does not stop Power BI from removing duplicate rows before passing data to the Python visual.
Question 6
You have a Fabric tenant named Tenant1 that contains a workspace named WS1. WS1 uses a capacity named C1 and contains a dataset named DS1. You need to ensure read-write access to DS1 is available by using XMLA endpoint. What should be modified first?
Show answer and explanation
Correct answer: C. the C1 settings
XMLA endpoint read-write access must be enabled at the capacity level (C1) first, before it can be used by datasets within workspaces assigned to that capacity. This is a prerequisite configuration that allows the infrastructure to support XMLA read-write operations for all datasets using that capacity.
Why the other options are wrong
- A. Dataset settings do not control XMLA endpoint availability; this is determined at the capacity level.
- B. Workspace settings do not enable XMLA endpoint functionality; capacity-level configuration is required.
- D. Tenant settings do not directly control XMLA endpoint access for specific datasets or workspaces.
Question 7
You have a Fabric tenant that contains a workspace named Workspace1. Workspace1 is assigned to a Fabric capacity. You need to recommend a solution to provide users with the ability to create and publish custom Direct Lake semantic models by using external tools. The solution must follow the principle of least privilege. Which three actions in the Fabric Admin portal should you include in the recommendation? Each correct answer presents part of the solution. NOTE: Each correct answer is worth one point.
Show answer and explanation
Correct answer: A, C, D. From the Tenant settings, set Allow XMLA Endpoints and Analyze in Excel with on-premises datasets to Enabled; From the Tenant settings, select Users can edit data model in the Power BI service; From the Capacity settings, set XMLA Endpoint to Read Write.
To enable users to create and publish custom Direct Lake semantic models using external tools while following least privilege: (A) XMLA Endpoints must be enabled at the tenant level to allow external tools to connect and publish semantic models; (C) Data model editing in the Power BI service must be enabled to allow creation of semantic models; (D) The capacity XMLA Endpoint must be set to Read Write to permit semantic model creation and modification through external tools. These three settings work together to enable the required functionality without unnecessary permissions.
Why the other options are wrong
- B. Guest user access is not required for internal users to create Direct Lake semantic models.
- E. Creating Fabric items is a separate capability not specifically needed for Direct Lake model creation via external tools.
- F. Publish to Web is for sharing reports publicly and is unrelated to Direct Lake semantic model creation.
Question 8
You are creating a semantic model in Microsoft Power BI Desktop. You plan to make bulk changes to the model by using the Tabular Model Definition Language (TMDL) extension for Microsoft Visual Studio Code. You need to save the semantic model to a file. Which file format should you use?
Show answer and explanation
Correct answer: A. PBIP
The PBIP (Power BI Project) file format is the correct format for saving semantic models when working with TMDL (Tabular Model Definition Language) in Visual Studio Code. PBIP projects use TMDL as their underlying structure, enabling bulk edits and version control capabilities for semantic models.
Why the other options are wrong
- B. PBIX is the legacy binary format for Power BI Desktop files and does not support TMDL-based editing.
- C. PBIT is a Power BI template format used for distributing parameterized reports, not for TMDL editing.
- D. PBIDS is a Power BI data source file format for connection specifications, not for semantic model storage.
Question 9
You plan to deploy Microsoft Power BI items by using Fabric deployment pipelines. You have a deployment pipeline that contains three stages named Development, Test, and Production. A workspace is assigned to each stage. You need to provide Power BI developers with access to the pipeline. The solution must meet the following requirements: Ensure that the developers can deploy items to the workspaces for Development and Test. Prevent the developers from deploying items to the workspace for Production. Follow the principle of least privilege. Which three levels of access should you assign to the developers? Each correct answer presents part of the solution. NOTE: Each correct answer is worth one point.
Show answer and explanation
Correct answer: B, D, E. Admin access to the deployment pipeline; Viewer access to the Production workspace; Contributor access to the Development and Test workspaces
To allow developers to deploy to Development and Test workspaces while preventing Production deployment: (B) Admin access to the deployment pipeline gives developers the ability to perform deployments across stages they have access to; (D) Viewer access to the Production workspace prevents developers from deploying there while allowing visibility; (E) Contributor access to the Development and Test workspaces enables developers to deploy items to these stages. This combination provides necessary permissions for Development and Test while restricting Production access.
Why the other options are wrong
- A. Build permission on production semantic models is not a deployment pipeline access control and does not prevent production deployment.
- C. Viewer access to Development and Test workspaces is insufficient; Contributor access is required to deploy items.
- F. Contributor access to Production workspace would allow deployment to Production, violating the requirement to prevent it.
Question 10
You have a Fabric tenant that contains a semantic model. The model uses Direct Lake mode. You suspect that some DAX queries load unnecessary columns into memory. You need to identify the frequently used columns that are loaded into memory. What are two ways to achieve the goal? Each correct answer presents a complete solution. NOTE: Each correct answer is worth one point.
Show answer and explanation
Correct answer: B, C. Use the Vertipaq Analyzer tool; Query the $System.DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS dynamic management view (DMV).
Vertipaq Analyzer tool is specifically designed to analyze column usage and memory consumption in Direct Lake models. The $System.DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS DMV provides detailed information about which columns are loaded into memory and their storage characteristics, allowing identification of frequently used columns.
Why the other options are wrong
- A. Analyze in Excel feature is for data exploration and visualization, not for identifying memory usage patterns.
- D. DISCOVER_MEMORYGRANT DMV relates to query memory allocation, not column-level memory consumption in Direct Lake mode.
What people say about the full DP-600 pack 5.0 from 5
Question 11
You are analyzing customer purchases in a Fabric notebook by using PySpark. You have the following DataFrames: transactions: Contains five columns named transaction_id, customer_id, product_id, amount, and date and has 10 million rows, with each row representing a transaction. customers: Contains customer details in 1,000 rows and three columns named customer_id, name, and country. You need to join the DataFrames on the customer_id column. The solution must minimize data shuffling. You write the following code. from pyspark.sql import functions as F results = Which code should you run to populate the results DataFrame?
Show answer and explanation
Correct answer: A. transactions.join(F.broadcast(customers), transactions.customer_id == customers.customer_id)
The broadcast join using F.broadcast(customers) is the correct choice to minimize data shuffling. Since the customers DataFrame has only 1,000 rows, broadcasting it to all executor nodes is far more efficient than shuffling the 10 million row transactions table. This approach avoids the expensive shuffle operation that would occur with a standard join.
Why the other options are wrong
- B. Adding .distinct() doesn't address the shuffling problem and adds unnecessary computation.
- C. A standard join without broadcast will cause the larger transactions table to be shuffled across executors, which is inefficient.
- D. A crossJoin followed by a where clause is extremely inefficient and will cause a cartesian product before filtering.
Question 12
You have a Fabric tenant that contains a data pipeline. You need to ensure that the pipeline runs every four hours on Mondays and Fridays. To what should you set Repeat for the schedule?
Show answer and explanation
Correct answer: D. Hourly
Setting Repeat to Hourly allows you to configure a schedule that runs every four hours on specific days of the week. When you select Hourly recurrence, you can specify the interval (4 hours) and then select which days of the week the pipeline should execute, enabling the exact requirement of running every four hours on Mondays and Fridays.
Why the other options are wrong
- A. Daily recurrence does not provide the granularity needed to run every four hours within a single day.
- B. By the minute recurrence is too fine-grained and would not efficiently support a four-hour interval pattern.
- C. Weekly recurrence runs once per week on selected days, not multiple times within those days at four-hour intervals.
Question 13
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen. You have a Fabric tenant that contains a new semantic model in OneLake. You use a Fabric notebook to read the data into a Spark DataFrame. You need to evaluate the data to calculate the min, max, mean, and standard deviation values for all the string and numeric columns. Solution: You use the following PySpark expression: df.explain() Does this meet the goal?
Show answer and explanation
Correct answer: B. No
The df.explain() method displays the query execution plan for a DataFrame, not statistical summaries. It shows how Spark will execute a query, including operations like scans, filters, and joins, but does not calculate min, max, mean, or standard deviation values. This does not meet the goal of evaluating statistical properties.
Why the other options are wrong
- A. explain() is not designed for statistical analysis and produces query plan output instead.
Question 14
You have a Fabric tenant that contains a lakehouse named Lakehouse1. Lakehouse1 contains a subfolder named Subfolder1 that contains CSV files. You need to convert the CSV files into the delta format that has V-Order optimization enabled. What should you do from Lakehouse explorer?
Show answer and explanation
Correct answer: A. Use the Load to Tables feature.
The Load to Tables feature in Lakehouse explorer automatically converts CSV files into delta format tables with V-Order optimization enabled. This feature handles the format conversion and optimization in a single operation without requiring manual shortcut creation.
Why the other options are wrong
- B. Creating a shortcut in the Files section does not convert CSV to delta format; it only creates a reference to the files.
- C. Creating a shortcut in the Tables section references existing tables but does not convert CSV files to delta format.
- D. The Optimize feature applies optimization to existing tables but does not perform CSV to delta format conversion.
Question 15
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen. You have a Fabric tenant that contains a new semantic model in OneLake. You use a Fabric notebook to read the data into a Spark DataFrame. You need to evaluate the data to calculate the min, max, mean, and standard deviation values for all the string and numeric columns. Solution: You use the following PySpark expression: df.summary() Does this meet the goal?
Show answer and explanation
Correct answer: A. Yes
The df.summary() method returns a DataFrame containing summary statistics including count, mean, stddev, min, and max for all numeric columns and applies to string columns as well where applicable. This directly meets the requirement to calculate min, max, mean, and standard deviation values for the columns in the DataFrame.
Why the other options are wrong
- B. summary() is specifically designed for statistical analysis and does meet the stated goal.
Question 16
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen. You have a Fabric tenant that contains a new semantic model in OneLake. You use a Fabric notebook to read the data into a Spark DataFrame. You need to evaluate the data to calculate the min, max, mean, and standard deviation values for all the string and numeric columns. Solution: You use the following PySpark expression: df.show() Does this meet the goal?
Show answer and explanation
Correct answer: B. No
The df.show() method displays sample rows from the DataFrame, not statistical calculations. It provides a visual preview of data but does not compute min, max, mean, or standard deviation values. This does not meet the goal of calculating statistical summaries.
Why the other options are wrong
- A. show() only displays raw data rows, not statistical metrics.
Question 17
You have a Fabric workspace named Workspace1 that contains a lakehouse named Lakehouse1. In Workspace1, you create a data pipeline named Pipeline1. You have CSV files stored in an Azure Storage account. You need to add an activity to Pipeline1 that will copy data from the CSV files to Lakehouse1. The activity must support Power Query M formula language expressions. Which type of activity should you add?
Show answer and explanation
Correct answer: A. Dataflow
A Dataflow activity in a Fabric data pipeline supports Power Query M formula language expressions and can copy and transform data from various sources including Azure Storage CSV files into a lakehouse. Dataflows provide a low-code, visual interface for data preparation while leveraging the power of M language for advanced transformations. Notebooks require code, Script activities are for Spark or Python, and Copy data is a basic activity without M formula support.
Why the other options are wrong
- B. Notebook activities require code programming rather than M formula expressions.
- C. Script activities execute Spark or Python code, not Power Query M expressions.
- D. Copy data activity provides basic data movement without Power Query M formula language support.
Question 18
You have a Fabric tenant that uses a Microsoft Power BI Premium capacity. You need to enable scale-out for a semantic model. What should you do first?
Show answer and explanation
Correct answer: C. At the semantic model level, set Large dataset storage format to On.
To enable scale-out for a semantic model, the Large dataset storage format must be set to On at the semantic model level. This setting enables the model to use Premium features that support scale-out, allowing the dataset to exceed single-node capacity limitations and distribute processing across multiple nodes.
Why the other options are wrong
- A. Setting Large dataset storage format to Off would disable scale-out capabilities, not enable them.
- B. Enabling Create and Metrics is unrelated to semantic model scale-out functionality.
- D. Enabling Data Activator is a separate feature for alerting and does not enable scale-out for semantic models.
Question 19
You have an Azure Repos repository named Repo1 and a Fabric-enabled Microsoft Power BI Premium capacity. The capacity contains two workspaces named Workspace1 and Workspace2. Git integration is enabled at the workspace level. You plan to use Microsoft Power BI Desktop and Workspace1 to make version-controlled changes to a semantic model stored in Repo1. The changes will be built and deployed to Workspace2 by using Azure Pipelines. You need to ensure that report and semantic model definitions are saved as individual text files in a folder hierarchy. The solution must minimize development and maintenance effort. In which file format should you save the changes?
Show answer and explanation
Correct answer: A. PBIP
PBIP (Power BI Project) format saves report and semantic model definitions as individual text files organized in a folder hierarchy, making it ideal for version control, branching, and Azure Pipelines deployment. This format was specifically designed for Git integration and collaborative development scenarios. PBIX is a binary format unsuitable for version control, PBIDS is a data source file, and PBIT is a template format.
Why the other options are wrong
- B. PBIDS is a data source definition file, not a project format for storing model definitions.
- C. PBIT is a Power BI template format and does not support individual text files in a folder hierarchy.
- D. PBIX is a binary format that cannot be effectively version-controlled and does not create individual text files.
Question 20
You have a Fabric tenant that contains a warehouse named Warehouse1. Warehouse1 contains two schemas name schema1 and schema2 and a table named schema1.city. You need to make a copy of schema1.city in schema2. The solution must minimize the copying of data. Which T-SQL statement should you run?
Show answer and explanation
Correct answer: C. CREATE TABLE schema2.city AS CLONE OF schema1.city;
The CREATE TABLE AS CLONE OF statement is specifically designed in Fabric warehouses to create a metadata-only copy of a table structure without copying the actual data, minimizing data movement. This is the most efficient approach when only the table structure is needed in a different schema.
Why the other options are wrong
- A. INSERT INTO requires reading and writing all data, copying the entire dataset.
- B. SELECT INTO copies all data from the source table to the new location.
- D. CREATE TABLE AS SELECT copies all data from the source table to the new location.
Question 21
You have a Fabric tenant that contains a complex semantic model. The model is based on a star schema and contains many tables, including a fact table named Sales. You need to create a diagram of the model. The diagram must contain only the Sales table and related tables. What should you use from Microsoft Power BI Desktop?
Show answer and explanation
Correct answer: C. Model view
Model view in Power BI Desktop provides a visual diagram of table relationships in a semantic model. This view allows you to see the Sales table and its related tables through their relationships, making it ideal for understanding the structure of a star schema without displaying unrelated tables.
Why the other options are wrong
- A. Data categories are used to classify column data types, not for creating model diagrams.
- B. Data view shows the actual data content within tables, not the model structure.
- D. DAX query view is used to write and execute DAX queries, not to visualize model relationships.
Question 22
You have a Fabric tenant that contains a warehouse. Several times a day, the performance of all warehouse queries degrades. You suspect that Fabric is throttling the compute used by the warehouse. What should you use to identify whether throttling is occurring?
Show answer and explanation
Correct answer: D. the Microsoft Fabric Capacity Metrics app
The Microsoft Fabric Capacity Metrics app is specifically designed to monitor and identify capacity throttling in Fabric workloads. It provides detailed insights into compute consumption, throttling events, and capacity usage patterns that help diagnose performance degradation caused by throttling. While DMVs and the Monitoring hub can provide some insights, the Capacity Metrics app is the dedicated tool for identifying throttling specifically.
Why the other options are wrong
- A. Capacity settings show current configuration but don't provide metrics to identify whether throttling is actively occurring.
- B. The Monitoring hub provides general monitoring but the Capacity Metrics app is the specialized tool for throttling diagnostics.
- C. DMVs can show query execution details but are not the primary tool for identifying capacity throttling at the tenant level.
Question 23
You have a Fabric workspace that contains a DirectQuery semantic model. The model queries a data source that has 500 million rows. You have a Microsoft Power Bi report named Report1 that uses the model. Report1 contains visuals on multiple pages. You need to reduce the query execution time for the visuals on all the pages. What are two features that you can use? Each correct answer presents a complete solution, NOTE: Each correct answer is worth one point.
Show answer and explanation
Correct answer: A, B. user-defined aggregations; automatic aggregation
To reduce query execution time for DirectQuery models: (A) User-defined aggregations allow you to create pre-aggregated tables that DirectQuery can route smaller queries to, significantly reducing execution time for common aggregations; (B) Automatic aggregation similarly creates optimized aggregation tables automatically that reduce query load. Both features work by reducing the dataset size and complexity of queries sent to the source, which is effective for large datasets with millions of rows.
Why the other options are wrong
- C. Query caching is not a standard DirectQuery feature for reducing execution time; DirectQuery queries are not cached by default.
- D. OneLake integration is a data storage feature and does not directly reduce query execution time for DirectQuery models.
Question 24
You have a Fabric tenant that contains a lakehouse named Lakehouse1. Lakehouse1 contains an unpartitioned table named Table1. You plan to copy data to Table1 and partition the table based on a date column in the source data. You create a Copy activity to copy the data to Table1. You need to specify the partition column in the Destination settings of the Copy activity. What should you do first?
Show answer and explanation
Correct answer: D. From the Destination tabs, set Mode to Overwrite.
Before specifying a partition column in the Destination settings of a Copy activity, the table must be set to Overwrite mode. This mode allows the Copy activity to restructure the unpartitioned table into a partitioned table based on the specified date column from the source data.
Why the other options are wrong
- A. Append mode would add data to the existing table without allowing schema or partitioning changes.
- B. Selecting the partition column is the action you want to perform, but it requires the prerequisite of setting the correct mode first.
- C. Enable partition discovery on the Source tab is used for detecting existing partitions in source data, not for setting up new partitions on the destination.
Question 25
You have a Fabric tenant that contains a warehouse. You are designing a star schema model that will contain a customer dimension. The customer dimension table will be a Type 2 slowly changing dimension (SCD). You need to recommend which columns to add to the table. The columns must NOT already exist in the source. Which three types of columns should you recommend? Each correct answer presents part of the solution. NOTE: Each correct answer is worth one point.
Show answer and explanation
Correct answer: C, D, E. an effective end date and time; a surrogate key; an effective start date and time
A Type 2 slowly changing dimension tracks changes over time by maintaining historical records. Required columns include a surrogate key (D) as the primary key for each version of a dimension record, an effective start date and time (E) marking when a record became active, and an effective end date and time (C) marking when it was superseded. These three columns enable temporal tracking of dimension changes.
Why the other options are wrong
- A. A foreign key is not typically added to the dimension table itself; foreign keys exist in fact tables.
- B. A natural key already exists in the source data and is not added; it may be used to identify which records change.
Question 26
You have a Fabric tenant that contains a lakehouse named Lakehouse1. Lakehouse1 contains a Delta table that has one million Parquet files. You need to remove files that were NOT referenced by the table during the past 30 days. The solution must ensure that the transaction log remains consistent, and the ACID properties of the table are maintained. What should you do?
Show answer and explanation
Correct answer: D. Run the VACUUM command.
The VACUUM command removes unreferenced files from a Delta table while maintaining ACID properties and transaction log consistency. It is specifically designed for cleanup operations on Delta tables and respects the retention period (default 30 days). OPTIMIZE with Z-order or V-order is for performance tuning, not cleanup. OneLake file explorer deletion would bypass Delta's transaction log and compromise ACID guarantees.
Why the other options are wrong
- A. Deleting files through OneLake file explorer bypasses the Delta transaction log and violates ACID properties.
- B. OPTIMIZE with Z-order is a performance optimization technique, not a cleanup operation for unreferenced files.
- C. OPTIMIZE with V-order is a performance optimization technique for columnar storage, not for removing unreferenced files.
Question 27
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen. You have a Fabric tenant that contains a lakehouse named Lakehouse1. Lakehouse1 contains a Delta table named Customer. When you query Customer, you discover that the query is slow to execute. You suspect that maintenance was NOT performed on the table. You need to identify whether maintenance tasks were performed on Customer. Solution: You run the following Spark SQL statement: REFRESH TABLE customer Does this meet the goal?
Show answer and explanation
Correct answer: B. No
REFRESH TABLE customer refreshes the metadata cache and reloads the table definition, but it does not identify whether maintenance tasks like OPTIMIZE or VACUUM have been performed on the table. This command is used to refresh the table's metadata after external changes, not to audit maintenance history. To identify maintenance tasks, you would need to check the Delta transaction log or query the table history.
Why the other options are wrong
- A. REFRESH TABLE only reloads metadata and does not provide information about past maintenance operations.
Question 28
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen. You have a Fabric tenant that contains a lakehouse named Lakehouse1. Lakehouse1 contains a Delta table named Customer. When you query Customer, you discover that the query is slow to execute. You suspect that maintenance was NOT performed on the table. You need to identify whether maintenance tasks were performed on Customer. Solution: You run the following Spark SQL statement: DESCRIBE HISTORY customer Does this meet the goal?
Show answer and explanation
Correct answer: A. Yes
The DESCRIBE HISTORY command displays the transaction log of a Delta table, showing all operations performed on the table including OPTIMIZE and VACUUM maintenance tasks. This reveals whether maintenance was performed and when, helping identify if optimization operations were skipped, which could explain slow query performance.
Why the other options are wrong
- B. DESCRIBE HISTORY is the correct command for checking Delta table maintenance history.
Question 29
You have a Fabric tenant. You are creating an Azure Data Factory pipeline. You have a stored procedure that returns the number of active customers and their average sales for the current month. You need to add an activity that will execute the stored procedure in a warehouse. The returned values must be available to the downstream activities of the pipeline. Which type of activity should you add?
Show answer and explanation
Correct answer: D. Lookup
The Lookup activity is designed to execute queries or stored procedures against a data source and return result sets that can be used by downstream activities in the pipeline. It captures the output values and makes them available for subsequent activity reference, which is exactly what is needed to execute a stored procedure and pass its results forward.
Why the other options are wrong
- A. Switch is a control activity for conditional branching, not for executing stored procedures.
- B. Copy data is designed for transferring data between sources and sinks, not for executing stored procedures and capturing results.
- C. Append variable adds values to variables but is not designed to execute stored procedures.
Question 30
You have a Fabric tenant that contains a semantic model. You need to prevent report creators from populating visuals by using implicit measures. What are two tools that you can use to achieve the goal? Each correct answer presents a complete solution. NOTE: Each correct answer is worth one point.
Show answer and explanation
Correct answer: A, B. Microsoft Power BI Desktop; Tabular Editor
Power BI Desktop allows model creators to configure data modeling options and disable implicit measures at the model level. Tabular Editor provides direct access to the model's metadata and settings, enabling the configuration to prevent implicit measure creation through model properties.
Why the other options are wrong
- C. SQL Server Management Studio is used for SQL Server management, not for configuring Power BI semantic model settings.
- D. DAX Studio is a query and diagnostic tool for DAX analysis, not a tool for configuring model-level restrictions on implicit measures.
Question 31
You have a Fabric tenant that contains a lakehouse. You plan to use a visual query to merge two tables. You need to ensure that the query returns all the rows in both tables. Which type of join should you use?
Show answer and explanation
Correct answer: B. full outer
A full outer join returns all rows from both tables, matching rows where the join condition is met and including unmatched rows from both sides. This is the only join type that preserves all data from both tables. Inner join returns only matching rows, left/right outer joins return all from one table and matching from the other, and anti joins return non-matching rows only.
Why the other options are wrong
- A. Inner join returns only rows that match in both tables, excluding unmatched rows.
- C. Left outer join returns all rows from the left table and matching rows from the right, excluding right-only rows.
- D. Right anti join returns only rows from the right table that do not match the left table.
- E. Right outer join returns all rows from the right table and matching rows from the left, excluding left-only rows.
- F. Left anti join returns only rows from the left table that do not match the right table.
Question 32
You have a Fabric workspace named Workspace1 and an Azure SQL database. You plan to create a dataflow that will read data from the database, and then transform the data by performing an inner join. You need to ignore spaces in the values when performing the inner join. The solution must minimize development effort. What should you do?
Show answer and explanation
Correct answer: B. Merge the queries by using fuzzy matching.
Fuzzy matching in Power Query (used by Fabric dataflows) allows for approximate string matching that ignores minor differences like spaces when performing merge operations. The Merge operation combines two tables, while Fuzzy Matching specifically handles variations in text values. This minimizes development effort by providing built-in functionality rather than requiring manual data cleaning or lookup tables.
Why the other options are wrong
- A. Append combines rows from tables, not suitable for inner joins.
- C. Lookup tables would require additional manual creation and development effort.
- D. Lookup tables would require additional manual creation and development effort.
Question 33
You have a Fabric tenant that contains a lakehouse named Lakehouse1. You need to prevent new tables added to Lakehouse1 from being added automatically to the default semantic model of the lakehouse. What should you configure?
Show answer and explanation
Correct answer: A. the SQL analytics endpoint settings
The SQL analytics endpoint settings control which tables are automatically included in the default semantic model of a lakehouse. This is where you configure the behavior of new tables being automatically added to or excluded from the semantic model.
Why the other options are wrong
- B. Semantic model settings do not control automatic inclusion of new tables.
- C. Workspace settings do not provide table-level semantic model controls.
- D. Lakehouse1 settings do not provide the granular control needed for semantic model table inclusion behavior.
Question 34
You have a Fabric tenant that contains a semantic model named Model1. Model1 uses Import mode. Model1 contains a table named Orders. Orders has 100 million rows and the following fields. You need to reduce the memory used by Model1 and the time it takes to refresh the model. Which two actions should you perform? Each correct answer presents part of the solution. NOTE: Each correct answer is worth one point.
Show answer and explanation
Correct answer: A, D. Split OrderDateTime into separate date and time columns; Replace TotalSalesAmount with a measure.
To reduce memory usage and refresh time in an Import mode semantic model, you should replace TotalSalesAmount with a measure (D) because measures are calculated on-demand during query time and consume no storage in the model, whereas calculated columns are materialized during refresh and stored in memory. Additionally, splitting OrderDateTime into separate date and time columns (A) reduces memory consumption by allowing more efficient compression and storage of individual components rather than storing the full datetime object, which is particularly important for 100 million rows. Converting Quantity to Text and replacing TotalQuantity with a calculated column would both increase memory usage and refresh time.
Why the other options are wrong
- B. TotalQuantity is already a measure according to the table description, and replacing it with a calculated column would consume additional memory storage.
- C. Converting Quantity from Integer to Text data type would significantly increase memory consumption per row across 100 million rows, worsening performance rather than improving it.
Question 35
You have a Fabric tenant that contains a lakehouse. You plan to query sales data files by using the SQL endpoint. The files will be in an Amazon Simple Storage Service (Amazon S3) storage bucket. You need to recommend which file format to use and where to create a shortcut. Which two actions should you include in the recommendation? Each correct answer presents part of the solution. NOTE: Each correct answer is worth one point.
Show answer and explanation
Correct answer: D, E. Create a shortcut in the Tables section; Use the delta format.
The delta format is the optimal choice for querying through SQL endpoints in lakehouses because it provides full ACID support and optimized query performance. Creating a shortcut in the Tables section automatically exposes the external data as queryable tables through the SQL endpoint, enabling direct SQL queries against the Amazon S3 data.
Why the other options are wrong
- A. Creating a shortcut in the Files section exposes files for data pipelines but not for SQL endpoint queries.
- B. Parquet format can be queried but is not the optimal format for SQL endpoint access in lakehouses; delta is preferred.
- C. CSV format lacks the structured format and indexing capabilities needed for efficient SQL endpoint querying.
Question 36
You have a Fabric tenant that contains a warehouse. The warehouse uses row-level security (RLS). You create a Direct Lake semantic model that uses the Delta tables and RLS of the warehouse. When users interact with a report built from the model, which mode will be used by the DAX queries?
Show answer and explanation
Correct answer: A. DirectQuery
When a Direct Lake semantic model uses row-level security, the DAX queries will execute in DirectQuery mode. This is because RLS rules must be evaluated against the live data source to ensure row-level restrictions are properly applied in real-time, which requires DirectQuery mode rather than Direct Lake's typical caching behavior.
Why the other options are wrong
- B. Dual mode is not used; RLS requires DirectQuery evaluation against live data.
- C. Direct Lake mode alone cannot be used when RLS is active, as DirectQuery is needed for security evaluation.
- D. Import mode would not work with the warehouse's RLS requirements, which need real-time evaluation.
Question 37
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen. You have a Fabric tenant that contains a lakehouse named Lakehouse1. Lakehouse1 contains a Delta table named Customer. When you query Customer, you discover that the query is slow to execute. You suspect that maintenance was NOT performed on the table. You need to identify whether maintenance tasks were performed on Customer. Solution: You run the following Spark SQL statement: EXPLAIN TABLE customer Does this meet the goal?
Show answer and explanation
Correct answer: B. No
EXPLAIN TABLE customer is not a valid Spark SQL statement that provides maintenance history information. The EXPLAIN command in Spark SQL is used with SELECT statements to show query execution plans, not to audit table maintenance. To identify maintenance tasks performed, you would need to query the Delta transaction log using DESCRIBE HISTORY or similar Delta-specific commands.
Why the other options are wrong
- A. EXPLAIN TABLE is not a valid syntax for auditing maintenance tasks and does not retrieve maintenance history.
Question 38
You have a Fabric tenant that contains a lakehouse named Lakehouse’. Lakehouse1 contains a table named Tablet. You are creating a new data pipeline. You plan to copy external data to Table’. The schema of the external data changes regularly. You need the copy operation to meet the following requirements: Replace Table1 with the schema of the external data. Replace all the data in Table1 with the rows in the external data. You add a Copy data activity to the pipeline. What should you do for the Copy data activity?
Show answer and explanation
Correct answer: B. From the Destination tab, set Table action to Overwrite.
Setting the Table action to Overwrite in the Destination tab of the Copy activity will replace both the schema and all data in Table1 with the external data. This meets both requirements: replacing the table schema to match the external data and replacing all rows with new data from the source.
Why the other options are wrong
- A. Adding additional columns from the Source tab does not address schema replacement or data replacement requirements.
- C. Enable staging is a performance optimization for data movement, not related to schema or data replacement.
- D. Enable partition discovery is used for discovering partitions in source data structure, not for schema replacement.
- E. Recursively option is for scanning nested folder structures, not for schema or data replacement operations.
Question 39
You have a Fabric tenant that contains two workspaces named Workspace1 and Workspace2. Workspace1 contains a lakehouse named Lakehouse1. Workspace2 contains a lakehouse named Lakehouse2. Lakehouse1 contains a table named dbo. Sales. Lakehouse2 contains a table named dbo. Customers. You need to ensure that you can write queries that reference both dbo. Sales and dbo. Customers in the same SQL query without making additional copies of the tables. What should you use?
Show answer and explanation
Correct answer: A. a shortcut
A shortcut creates a reference to a table in another lakehouse without duplicating data, allowing queries to reference tables across different workspaces seamlessly. This is the native Fabric feature designed for exactly this scenario of accessing tables from multiple lakehouses in a single query.
Why the other options are wrong
- B. A dataflow would require creating a copy of the data, not just referencing it.
- C. A view would still require the tables to be in the same location or workspace to be referenced.
- D. A managed table would create a copy of the data rather than simply referencing the existing tables.
Question 40
Note: This section contains one or more sets of questions with the same scenario and problem. Each question presents a unique solution to the problem. You must determine whether the solution meets the stated goals. More than one solution in the set might solve the problem. It is also possible that none of the solutions in the set solve the problem. After you answer a question in this section, you will NOT be able to return. As a result, these questions do not appear on the Review Screen. Your network contains an on-premises Active Directory Domain Services (AD DS) domain named contoso.com that syncs with a Microsoft Entra tenant by using Microsoft Entra Connect. You have a Fabric tenant that contains a semantic model. You enable dynamic row-level security (RLS) for the model and deploy the model to the Fabric service. You query a measure that includes the USERNAME() function, and the query returns a blank result. You need to ensure that the measure returns the user principal name (UPN) of a user. Solution: You add user objects to the list of synced objects in Microsoft Entra Connect. Does this meet the goal?
Show answer and explanation
Correct answer: B. No
Adding user objects to Microsoft Entra Connect sync will not resolve the USERNAME() function returning blank in a semantic model with dynamic RLS. The issue is that USERNAME() returns the user principal name from the Fabric service context, and this requires proper configuration of the RLS expressions and user authentication in the Fabric semantic model itself, not changes to directory sync settings.
Why the other options are wrong
- A. This solution does not address the core issue of USERNAME() function context within the Fabric semantic model, making it ineffective for returning the UPN.
Question 41
You need to create a data loading pattern for a Type 1 slowly changing dimension (SCD). Which two actions should you include in the process? Each correct answer presents part of the solution. NOTE: Each correct answer is worth one point.
Show answer and explanation
Correct answer: A, D. Update rows when the non-key attributes have changed; Insert new records when the natural key is a new value in the table.
Type 1 slowly changing dimensions handle changes by updating existing rows in place when non-key attributes change (Option A), and by inserting new rows only when the natural key itself is new to the dimension table (Option D). Type 1 does not track history, so it doesn't create new rows for attribute changes, nor does it maintain effective date ranges.
Why the other options are wrong
- B. Type 1 does not insert new rows when existing natural keys have attribute changes; it updates the existing row.
- C. Type 1 does not maintain effective date ranges; it simply overwrites old values without historical tracking.
Question 42
You have a Fabric tenant that contains a warehouse. A user discovers that a report that usually takes two minutes to render has been running for 45 minutes and has still not rendered. You need to identify what is preventing the report query from completing. Which dynamic management view (DMV) should you use?
Show answer and explanation
Correct answer: A. sys.dm_exec_requests
Fabric Data Warehouse supports sys.dm_exec_requests, which shows each active request with its status, wait type, blocking session and elapsed time. This reveals what is preventing the long-running report query from completing. Fabric Data Warehouse uses the standard sys.dm_exec_* views rather than the legacy dedicated SQL pool DMVs.
Why the other options are wrong
- B. sys.dm_exec_sessions lists sessions and their users but does not show the status, waits or blocking of individual requests.
- C. sys.dm_exec_connections shows connection details such as client address and protocol, not what a running query is waiting on.
- D. sys.dm_pdw_exec_requests belongs to Azure Synapse dedicated SQL pools and is not available in Fabric Data Warehouse.
Question 43
You have a Fabric tenant that contains 30 CSV files in OneLake. The files are updated daily. You create a Microsoft Power BI semantic model named Model1 that uses the CSV files as a data source. You configure incremental refresh for Model1 and publish the model to a Premium capacity in the Fabric tenant. When you initiate a refresh of Model1, the refresh fails after running out of resources. What is a possible cause of the failure?
Show answer and explanation
Correct answer: D. Query folding is NOT occurring.
When incremental refresh fails due to running out of resources with CSV files and no query folding, the problem is that query folding is NOT occurring. Without query folding, the entire dataset from all 30 CSV files is loaded into memory during the refresh process, consuming excessive resources. Query folding would push operations to the data source level, reducing the data loaded into Power BI.
Why the other options are wrong
- A. Query folding occurring would actually reduce resource consumption, not cause a failure.
- B. Only refresh complete days is a valid incremental refresh setting and would not cause resource exhaustion.
- C. XMLA Endpoint read-only setting does not affect refresh operations or resource consumption.
- E. The delta type of the partition column changing would cause data inconsistencies, not resource exhaustion failures.
Question 44
You have source data in a folder on a local computer. You need to create a solution that will use Fabric to populate a data store. The solution must meet the following requirements: Support the use of dataflows to load and append data to the data store. Ensure that Delta tables are V-Order optimized and compacted automatically. Which type of data store should you use?
Show answer and explanation
Correct answer: A. a lakehouse
A lakehouse is the correct data store for this scenario because it natively supports dataflows for loading and appending data, and it automatically provides Delta table V-Order optimization and compaction. Lakehouses are specifically designed in Fabric to handle these requirements with built-in support for both structured and unstructured data management, making them ideal for populating data stores from local sources.
Why the other options are wrong
- B. Azure SQL Database does not support dataflows in the same way and does not work with Delta tables or V-Order optimization.
- C. A warehouse is optimized for analytical queries but does not automatically optimize and compact Delta tables with V-Order, and dataflow integration is different from lakehouse capabilities.
- D. A KQL database is designed for time-series and log data analysis, not for general data store population with dataflows and Delta table optimization.
That was 44 of 222.
The full Microsoft DP-600 pack has all 222 questions, each with the answer, the explanation and why the other options are wrong, plus a questions-only copy for timed runs. US$39, paid once, with free monthly updates and a pass-or-your-money-back guarantee.
Not ready yet? Get the first 58 free as one PDF by email. Get the Half Pack →