DP-600

Free DP-600 practice questions

10 questions from our DP-600 practice tests, each with the correct answer and a written explanation. Read them with the answers shown, or switch to quiz mode and answer first.

Back to the DP-600 practice exams

Question 1 of 10, Plan, implement, and manage a solution for data analytics

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?

  1. AAn on-premises data gateway in standard mode installed on a server that can reach the SQL Server instance Correct answer
  2. BAn on-premises data gateway in personal mode installed on the workstation of the engineer who owns the dataflow
  3. CNo gateway, because Fabric reaches on-premises SQL Server directly through the OneLake shortcut connectors
  4. DA virtual network (VNet) data gateway attached to a delegated subnet in the company's Azure virtual network

Answer and 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, Plan, implement, and manage a solution for data analytics

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?

  1. AMove the Spark jobs to a separate F32 capacity that runs only at night, keeping the morning capacity dedicated to interactive Power BI reports
  2. BPause 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
  3. CReschedule the heavy background jobs so that they finish earlier and spread across the night, reducing the carry-forward of smoothed usage Correct answer
  4. DUpgrade the capacity to F64 permanently so that the extra capacity units absorb both the overnight jobs and the morning interactive load

Answer and 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, Prepare and serve data

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?

  1. AOverwrite the dimension with the latest source extract and add an IsCurrent column that is set to true for every row
  2. BMerge on the business key with whenMatchedUpdateAll so that every changed attribute overwrites the current row in place
  3. CMerge on the surrogate key with whenNotMatchedInsertAll so that each changed customer receives an additional row
  4. DStage each changed row twice, once keyed to expire the current row and once with a null key so it is inserted Correct answer

Answer and 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, Prepare and serve data

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?

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

Answer and 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, Prepare and serve data

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?

  1. AA Lookup activity that returns the codes, then an If Condition activity that runs the Notebook activity
  2. BA Get Metadata activity that returns the codes, then an Until activity that runs a Notebook activity once
  3. CA Set variable activity that stores the codes, then a single Notebook activity that receives the whole array as one string
  4. DA Lookup activity that returns the codes, then a ForEach activity that contains a Notebook activity using item() Correct answer

Answer and 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, Prepare and serve data

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?

  1. AAn eventstream with a SQL source
  2. BMirroring for Azure SQL Database Correct answer
  3. CA shortcut to the Azure SQL Database
  4. DA scheduled Copy data activity

Answer and 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, Implement and manage semantic models

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?

  1. ASet the related dimension tables to DirectQuery storage mode and hide the aggregation table from report view
  2. BSet the aggregation table to DirectQuery storage mode and create a relationship from it to each dimension table
  3. CSet every dimension table to Import storage mode and add a calculated column for each aggregated measure
  4. DSet the related dimension tables to Dual storage mode and map the aggregation columns in Manage aggregations Correct answer

Answer and 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, Implement and manage semantic models

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?

  1. ACALCULATE([Sales], REMOVEFILTERS(Sales))
  2. BCALCULATE([Sales], ALLSELECTED(Product[ProductName]))
  3. CCALCULATE([Sales], ALL(Product[Category], Product[Subcategory]))
  4. DCALCULATE([Sales], ALLEXCEPT(Product, Product[Category])) Correct answer

Answer and 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, Explore and analyze data

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?

  1. AA query against the table's Delta log in the Files folder
  2. BA query against a zero-copy clone of the table created now
  3. CA RESTORE operation to a system-created restore point
  4. DA query with OPTION (FOR TIMESTAMP AS OF '<UTC time>') Correct answer

Answer and 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, Explore and analyze data

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?

  1. ADataflow Gen2
  2. BLineage view of the workspace
  3. CData Wrangler Correct answer
  4. DSpark UI

Answer and 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.