# Free DP-600 practice questions

> 10 free DP-600 practice questions with the correct answer and an explanation.

Page: https://devopscodelab.com/exams/microsoft-dp-600/free-practice-questions/
Practice exams: https://devopscodelab.com/exams/microsoft-dp-600/

### Question 1 of 10

Your organization stores sales data in a SQL Server instance running in its own data center. A Dataflow Gen2 in Fabric must refresh from this database every night. Several data engineers will reuse the same connection for other dataflows and semantic models. Which gateway should you deploy?

- A. An on-premises data gateway in standard mode installed on a server that can reach the SQL Server instance
- B. An on-premises data gateway in personal mode installed on the workstation of the engineer who owns the dataflow
- C. No gateway, because Fabric reaches on-premises SQL Server directly through the OneLake shortcut connectors
- D. A virtual network (VNet) data gateway attached to a delegated subnet in the company's Azure virtual network

**Answer:** A

**Explanation:** A standard-mode on-premises data gateway is the shared, centrally managed option for on-premises sources and supports Dataflow Gen2, pipelines and semantic models. Personal mode serves one user and only Power BI semantic models, so it cannot be shared or used by dataflows. A VNet data gateway reaches Azure resources inside a virtual network, not a server in a private data center without network integration. OneLake shortcuts do not connect to on-premises SQL Server, so a gateway is still required.

### Question 2 of 10

A Fabric F32 capacity runs Spark jobs, warehouse queries, and Power BI reports. Every morning between 8:00 and 9:00, users see delays in interactive reports, and the Capacity Metrics app shows that background usage from overnight jobs is carried forward and throttling occurs. The company wants to keep the capacity size for the rest of the day and avoid long-term cost increases. What should you do?

- A. Move the Spark jobs to a separate F32 capacity that runs only at night, keeping the morning capacity dedicated to interactive Power BI reports
- B. Pause the capacity at 7:00 every morning and resume it at 8:00 so that the accumulated overage is cleared just before the business day begins
- C. Reschedule the heavy background jobs so that they finish earlier and spread across the night, reducing the carry-forward of smoothed usage
- D. Upgrade the capacity to F64 permanently so that the extra capacity units absorb both the overnight jobs and the morning interactive load

**Answer:** C

**Explanation:** Background operations are smoothed over 24 hours, so heavy late-night jobs carry usage into the morning; spreading them out and finishing earlier reduces the overlap with interactive peaks without adding cost. A second F32 capacity doubles cost. Pausing a capacity settles the accumulated overage as a charge immediately and makes it unavailable while paused, which is a workaround rather than a fix. A permanent upgrade increases cost all day, which the company wants to avoid.

### Question 3 of 10

You load customer data into a lakehouse and must implement a Type 2 slowly changing dimension in a PySpark notebook. Changed customers must have their current row closed and a new current row inserted. Which approach should you use in a single Delta merge?

- A. Overwrite the dimension with the latest source extract and add an IsCurrent column that is set to true for every row
- B. Merge on the business key with whenMatchedUpdateAll so that every changed attribute overwrites the current row in place
- C. Merge on the surrogate key with whenNotMatchedInsertAll so that each changed customer receives an additional row
- D. Stage each changed row twice, once keyed to expire the current row and once with a null key so it is inserted

**Answer:** D

**Explanation:** In a single merge, matched rows can only be updated, so the changed records are staged twice: once with the business key to expire the current row, and once with a null key so they fall into the not-matched branch and are inserted as the new current row. whenMatchedUpdateAll overwrites history, which is Type 1. Merging on the surrogate key never matches source rows because new surrogate keys are generated, so current rows are never closed. Overwriting with the latest extract discards history.

### Question 4 of 10

A dimension table in a Fabric warehouse tracks customers. When a customer moves to a new city, analysts must still see historical sales attributed to the city where the customer lived at the time of the sale. Which design should you implement?

- A. A Type 1 slowly changing dimension that overwrites the city column in place whenever the source system changes
- B. A degenerate dimension that stores the customer city directly in the fact table as a text column on every row
- C. A Type 2 slowly changing dimension that inserts a new row with a new surrogate key and valid-from and valid-to dates
- D. A Type 1 slowly changing dimension that adds a PreviousCity column that always holds the city before the latest change

**Answer:** C

**Explanation:** Type 2 keeps every historical version as a separate row with its own surrogate key, so each fact row links to the version that was current at sale time. Overwriting the value (Type 1) rewrites history to the latest city. A PreviousCity column (a Type 3-style approach) keeps only one prior value, not the full history. Copying the city into the fact table is denormalization that bloats the fact table and is not a dimension design.

### Question 5 of 10

A data pipeline must process a list of 30 country codes. For each country code, it must run the same notebook with the code passed as a parameter. Which pipeline design should you use?

- A. A Lookup activity that returns the codes, then an If Condition activity that runs the Notebook activity
- B. A Get Metadata activity that returns the codes, then an Until activity that runs a Notebook activity once
- C. A Set variable activity that stores the codes, then a single Notebook activity that receives the whole array as one string
- D. A Lookup activity that returns the codes, then a ForEach activity that contains a Notebook activity using item()

**Answer:** D

**Explanation:** Lookup returns the list and ForEach iterates over it, passing item() to the Notebook activity parameter for each code. Get Metadata reads file or folder properties, and an Until loop needs a manually maintained exit condition. If Condition branches once and does not iterate. Passing the array as one string pushes the iteration into the notebook rather than using the pipeline to run per country.

### Question 6 of 10

A lakehouse must always reflect the current state of an operational Azure SQL Database with near real-time latency. Analysts will query the replicated tables with T-SQL. You must minimize the effort to build and maintain the solution. What should you use?

- A. An eventstream with a SQL source
- B. Mirroring for Azure SQL Database
- C. A shortcut to the Azure SQL Database
- D. A scheduled Copy data activity

**Answer:** B

**Explanation:** Mirroring continuously replicates Azure SQL Database tables into OneLake as Delta tables with near real-time latency and exposes a SQL analytics endpoint, with almost no maintenance. A scheduled copy is batch, not near real time, and must be maintained. Eventstream database CDC sources can capture changes, but you would still have to build the processing that applies them to tables. Shortcuts point to storage locations, not to relational databases.

### Question 7 of 10

A composite model has a 2-billion-row Sales fact table in DirectQuery mode and an imported aggregation table summarized by Date, Product, and Store. Most report visuals group by month and product category. Which configuration lets queries hit the aggregation table instead of DirectQuery?

- A. Set the related dimension tables to DirectQuery storage mode and hide the aggregation table from report view
- B. Set the aggregation table to DirectQuery storage mode and create a relationship from it to each dimension table
- C. Set every dimension table to Import storage mode and add a calculated column for each aggregated measure
- D. Set the related dimension tables to Dual storage mode and map the aggregation columns in Manage aggregations

**Answer:** D

**Explanation:** Dimensions in Dual mode can act as Import or DirectQuery as needed, so queries can join them to the imported aggregation without a cross-source query, and Manage aggregations maps the summarized columns to the detail table. DirectQuery dimensions force queries to the source. An aggregation table in DirectQuery provides no in-memory speed-up. Import-only dimensions create limited relationships with the DirectQuery fact table, and calculated columns do not define aggregations.

### Question 8 of 10

You need a measure that returns the total sales for the current product category regardless of filters on Product within the category, so a matrix can show each product's share of its category. Which expression returns the denominator?

- A. CALCULATE([Sales], REMOVEFILTERS(Sales))
- B. CALCULATE([Sales], ALLSELECTED(Product[ProductName]))
- C. CALCULATE([Sales], ALL(Product[Category], Product[Subcategory]))
- D. CALCULATE([Sales], ALLEXCEPT(Product, Product[Category]))

**Answer:** D

**Explanation:** ALLEXCEPT removes all filters on the Product table except Category, so it returns the category total for each product row. ALL on Category and Subcategory removes those filters but keeps the product name filter, so it still returns the single product's sales. ALLSELECTED on product name restores the outer selection, which spans all visible products rather than the current category. Removing filters on Sales does not remove filters coming from the Product table.

### Question 9 of 10

A warehouse table was accidentally updated two hours ago with wrong prices. You need to compare current prices with the values as they were three hours ago by using a single SELECT statement, without restoring the warehouse. What should you use?

- A. A query against the table's Delta log in the Files folder
- B. A query against a zero-copy clone of the table created now
- C. A RESTORE operation to a system-created restore point
- D. A query with OPTION (FOR TIMESTAMP AS OF '<UTC time>')

**Answer:** D

**Explanation:** Warehouse time travel at the statement level uses the FOR TIMESTAMP AS OF query hint to read data as of a past point in time within the retention period. A clone created now reflects the current, already-wrong data unless created as of a past point. Restoring the warehouse replaces it entirely, which is not wanted. Warehouse tables are not queried through Delta log files in a Files folder.

### Question 10 of 10

A data analyst wants to explore a new lakehouse table in a notebook and get interactive column statistics and a summary of missing values with generated PySpark or pandas cleaning code. What should the analyst use?

- A. Dataflow Gen2
- B. Lineage view of the workspace
- C. Data Wrangler
- D. Spark UI

**Answer:** C

**Explanation:** Data Wrangler, launched from a notebook, shows column summaries such as missing values and distributions and generates pandas or PySpark code for the cleaning steps you apply. Lineage view shows dependencies between items. Dataflow Gen2 is a separate low-code tool and does not generate notebook code. The Spark UI shows job execution details.

Ready for a timed run? Take the free 15-question DP-600 mini exam: https://devopscodelab.com/app/exams/microsoft-dp-600/mini/

DevOpsCode Lab is not affiliated with Microsoft. DP-600 and related names are trademarks of their owners. We write every question ourselves; these are not real exam questions.
