DP-600 domain - 46% of the exam

Prepare Data

Prepare Data is 46% of the DP-600 exam. These are the objectives it covers, each with practice questions, with every answer explained.

The domain in numbers

126
Practice questions
9
Objectives
84
Medium
42
Hard

Where people go wrong

  • Copying data that a OneLake shortcut can reference

    The wrong answers load a second copy of data that already exists in OneLake or in external storage: a Dataflows Gen2 load, a Copy activity, COPY INTO, or a notebook that saves the result. Each copy goes stale between scheduled runs and adds a refresh to maintain. A OneLake shortcut stores only a reference, so queries read the source in place, see the latest writes, and need no refresh job.

    See it in a sample question
  • Matching the wrong gateway to the source network

    The wrong answers pair a source with a connection path that cannot reach it, or add a gateway it does not need. A virtual network data gateway reaches Azure private endpoints inside a virtual network, so it cannot bridge to a physical on-premises server. A cloud connection reaches only sources with a public endpoint, and a personal gateway serves one user. An on-premises data gateway in standard mode, installed inside the corporate network, is the shared bridge. A publicly reachable Azure SQL Database needs only a cloud connection.

    See it in a sample question
  • Treating the SQL analytics endpoint as writable

    The wrong answers expect the Lakehouse SQL analytics endpoint to run INSERT, UPDATE or write stored procedures, or offer a writer role, transaction syntax or partitioning as the way to unlock writes. The endpoint serves read-only T-SQL over Delta tables. Writes belong in a Warehouse, which has full read-write T-SQL, or in a Spark notebook or Dataflows Gen2 that rewrites the Delta table.

    See it in a sample question
  • Confusing view, function and stored procedure

    The wrong answers give the job to the wrong T-SQL object. A view holds one SELECT and takes no arguments. A stored procedure runs ordered DML such as truncate, insert and merge, but it is called with EXEC, so its result cannot sit in a FROM clause or be joined. A scalar function returns one value. A table-valued function takes parameters and returns rows for a FROM clause, but cannot modify other tables.

    See it in a sample question
  • Scattering credentials instead of using one shared connection

    The wrong answers keep a separate credential for each author or dataflow, embed a key in a parameter, or delete and recreate a broken connection. Separate and embedded credentials leave copies to edit, and a recreated connection forces every dependent item to be repointed. One shareable cloud connection holds the credential once, and updating its stored credential repairs every Dataflows Gen2 item bound to it, without rebuilding any of them.

    See it in a sample question

Objectives in this domain

What to study

The official documents this domain's practice questions cite most.

Sample questions from this domain

Free samplePrepare Datahard

An analytics engineer must make a large Delta table that lives in an Azure Data Lake Storage Gen2 container queryable from a "Lakehouse" in "Microsoft Fabric". The source team continues to write to that container hourly, the data must not be copied or duplicated into "OneLake", and queries must always reflect the latest writes. Which ingestion approach meets the requirement?

  • ABuild a "Dataflows Gen2" query that reads the container and loads the rows into a managed Delta table in the "Lakehouse" on an hourly schedule.
  • BConfigure a data pipeline Copy activity that moves the Delta files into the "Lakehouse" Files area each hour using an incremental watermark column.
  • CCreate a "OneLake" shortcut in the "Lakehouse" that points at the Azure Data Lake Storage Gen2 location holding the Delta table. Correct
  • DAuthor a notebook that runs COPY INTO against the storage path to bulk-load the Delta table into a "Lakehouse" table on each trigger.
Use a "OneLake" shortcut to expose external Delta data live in a "Lakehouse" without copying or scheduling refreshes. A shortcut stores only a pointer to the external storage location, so the data is never physically moved into "OneLake" and reads always pass through to the current source files, satisfying a no-copy, always-fresh requirement that any load-based method cannot.

Why A is wrong: A dataflow physically loads rows into "OneLake", which duplicates the data and leaves the copy stale between scheduled runs, breaking the no-copy and always-current requirements.

Why B is wrong: A Copy activity physically duplicates the files into "OneLake" and only refreshes on schedule, so it violates both the no-duplication rule and the always-current requirement.

Why C is correct: A shortcut is a reference, so the table appears in the "Lakehouse" without copying data and every query reads through to the current files the source team writes.

Why D is wrong: COPY INTO ingests by writing the rows into a target table, which copies the data into "OneLake" and leaves it stale until the next run rather than referencing the source live.

Free samplePrepare Datamedium

An analytics engineer needs a "Dataflows Gen2" connection to read from an Azure SQL Database that has its public network access disabled and is reachable only through a private endpoint inside an Azure virtual network. No data centre or physical server is involved. Which connectivity option meets the requirement with the least operational overhead?

  • ARe-enable public network access on the Azure SQL Database so a standard cloud connection can reach it directly without provisioning any gateway at all.
  • BProvision a virtual network data gateway for the tenant, associate it with the virtual network, and bind the connection to that gateway. Correct
  • CInstall an on-premises data gateway on a virtual machine inside the same Azure virtual network and bind the connection to that gateway.
  • DUse a shareable cloud connection with the firewall exception list set to allow the published Fabric service tag so the private endpoint is bypassed.
Reach an Azure source restricted to a virtual network from Fabric by using a managed virtual network data gateway rather than a host-based gateway. The virtual network data gateway is provisioned and patched by Microsoft and injects into a customer virtual network, so it reaches private-endpoint sources without the engineer running and maintaining a gateway machine, minimising operational overhead.

Why A is wrong: Re-opening public access weakens the security posture the private endpoint was created to enforce, so it solves connectivity by undoing the requirement rather than respecting it.

Why B is correct: The virtual network data gateway is a Microsoft-managed gateway that connects into an Azure virtual network, reaching a private-endpoint source without you maintaining any host, which is the lowest-overhead fit.

Why C is wrong: An on-premises data gateway on a virtual machine would work but means patching and running a host yourself, which is more operational overhead than the managed option for a network-bound Azure source.

Why D is wrong: A cloud connection cannot route into a virtual network whose public access is disabled, and a service tag firewall rule does not apply when no public endpoint exists.

Free samplePrepare Datamedium

A finance team needs a Fabric store where stored procedures perform multi-table inserts and updates that must all succeed or all roll back together, with full read-write T-SQL and transactional consistency across tables. The data is fully structured and no Spark engineering or raw file landing is required. Which "Microsoft Fabric" store best meets this need?

  • AA "Warehouse", because it offers full read-write T-SQL with multi-table transactions that commit or roll back together, fitting structured finance processing. Correct
  • BA "Lakehouse", because its SQL analytics endpoint runs read-write T-SQL stored procedures with multi-table transactions that commit or roll back together for finance.
  • CAn "Eventhouse", because it provides full read-write T-SQL with multi-table transactions that commit or roll back together for structured finance processing.
  • DA "semantic model", because it runs read-write T-SQL stored procedures with multi-table transactions that commit or roll back together for structured finance data.
Choose a Warehouse when a structured workload needs read-write T-SQL and multi-table transactions that commit or roll back together. A Warehouse is Fabric's fully transactional relational store supporting read-write T-SQL, so stored procedures performing multi-table inserts and updates commit or roll back atomically, which a read-only Lakehouse endpoint or a KQL Eventhouse cannot provide.

Why A is correct: A "Warehouse" is the Fabric relational store with full read-write T-SQL and transactional support, so multi-table writes inside a stored procedure commit or roll back as one unit as the team requires.

Why B is wrong: A "Lakehouse" SQL analytics endpoint is read-only and cannot run write stored procedures or multi-table transactions, so it does not meet a transactional read-write requirement.

Why C is wrong: An "Eventhouse" is a KQL-based real-time store, not a transactional T-SQL relational store, so it does not provide the multi-table commit and rollback behaviour finance needs.

Why D is wrong: A "semantic model" is a read analytics layer queried with DAX and cannot execute write T-SQL stored procedures or transactions, so it cannot serve as a transactional store.

Free samplePrepare Datamedium

In a "Warehouse", an analytics engineer wants one reusable object that accepts a region code as an argument and returns a filtered set of rows for that region, so callers can write FROM dbo.SalesByRegion('WA') and join the result. Which object meets this need with the least duplication of logic?

  • AA view over the sales table, then ask each caller to add their own WHERE clause on region whenever they query the view for a particular region code.
  • BA stored procedure that takes the region code and returns the matching rows, which callers run with EXEC before reading the rows it produces for that region.
  • CA scalar function that takes the region code and returns a single aggregated value for that region, which callers reference inside their SELECT list expressions.
  • DA table-valued function that takes the region code as a parameter and returns the filtered rows, which callers place directly in a FROM clause and join. Correct
Use a table-valued function when a reusable query needs a parameter and must return a joinable row set that callers place directly in the FROM clause. A table-valued function combines a view's joinable row-set output with a procedure's ability to take arguments, so it is the right object when callers must pass a value and still treat the result like a table in a FROM clause.

Why A is wrong: A view cannot accept arguments, so pushing the region filter onto every caller duplicates the predicate and fails the requirement to pass the region as a parameter.

Why B is wrong: A stored procedure is invoked with EXEC and its result set cannot be placed in a FROM clause or joined, so it does not give the inline table behaviour the caller syntax requires.

Why C is wrong: A scalar function returns one value, not a set of rows to join, so it cannot satisfy a caller that needs a parameterised multi-row result in the FROM clause.

Why D is correct: A table-valued function accepts parameters and returns a row set usable in the FROM clause, which is precisely the parameterised, joinable, reusable object the requirement describes.

Free samplePrepare Datamedium

Several analytics engineers each build "Dataflows Gen2" items that read from the same Azure Data Lake Storage account. The data platform team wants one set of credentials governed centrally, so that individual authors do not each enter and store their own credentials for that storage account. How should the connection be set up to meet this goal?

  • AHave each engineer create a personal cloud connection to the storage account and document the shared credential so everyone types the same secret manually.
  • BStore the storage account key in a workspace parameter and reference that parameter inside each dataflow query so the credential travels with the item.
  • CCreate one shareable cloud connection to the storage account, set its credentials once, and grant the engineers permission to use that shared connection. Correct
  • DBind every dataflow to an on-premises data gateway and let the gateway hold one credential that all the cloud storage queries pass through centrally.
Govern one credential for a shared cloud source by creating a single shareable connection and granting users permission to use it. A shareable cloud connection stores the credential once at the connection level and is shared by permission, so multiple authors source the same data without each holding a copy of the secret, which centralises credential management.

Why A is wrong: Personal connections per author scatter copies of the same secret and create no central control point, so the credential cannot be governed or rotated in one place.

Why B is wrong: A parameter is not a credential store and embedding a key in item logic exposes the secret in the query, which is poor governance rather than a managed connection.

Why C is correct: A single shareable connection holds the credential centrally and is granted to users, so every author binds to it without re-entering secrets, which is exactly the central governance the team wants.

Why D is wrong: An on-premises data gateway is for private network sources, not a public cloud storage account, so routing cloud traffic through it adds an unnecessary component.

Other domains in this exam

See also the DP-600 cert hub, the study guide, and the cheat sheet.

Examworthy is not affiliated with or endorsed by Microsoft. Original, blueprint-aligned practice material only.