DP-700 domain - 34% of the exam

Implement and Manage an Analytics Solution

Implement and Manage an Analytics Solution is 34% of the DP-700 exam. These are the objectives it covers, each with practice questions, with every answer explained.

The domain in numbers

84
Practice questions
8
Objectives
52
Medium
32
Hard

Where people go wrong

  • Granting the wrong workspace role

    The wrong answers reach for a role above what the task needs, or for Viewer or Contributor when the task needs more. Viewer is read-only, with access to run and refresh history. Contributor can create, edit and run items, including pipelines, but cannot manage workspace access. Member can add users and assign Member, Contributor and Viewer roles. Only Admin can delete the workspace or change its capacity, so a handover that withholds deletion needs Member.

    See it in a sample question
  • Swapping row-level, column-level and masking controls

    The wrong answers apply a control that sounds close but acts on a different thing. Row-level security uses a predicate and a security policy to filter rows while every column is still returned. Dynamic data masking only changes how a column value is displayed, for example a partial mask showing the last four characters, and never removes rows or blocks selection. Column-level security denies SELECT on the column, which also stops WHERE-clause inference against it.

    See it in a sample question
  • Replacing a success dependency with a time gap

    Where one step must run only after another succeeds, the wrong answers separate the steps by time: staggered schedules a few minutes apart, wider gaps, or a later fixed start time. A gap is a guess that fails when the first step runs long, and a slow or failed step does not stop the next from starting. A Data Factory pipeline with on-success dependencies, or an Invoke pipeline activity on the success path of the upstream pipeline, makes completion the condition.

    See it in a sample question
  • Matching the transformation artefact to the wrong workload

    The wrong answers assign the transformation to the artefact that cannot carry it. A Copy activity only moves data with light mapping, so it cannot split columns, merge lookups or flatten arrays. A notebook demands PySpark skills that analysts may lack. Dataflow Gen2 gives a visual Power Query surface for modest cleaning, but not for hundreds of gigabytes of nested JSON with custom Python scoring, which belongs in a Spark notebook.

    See it in a sample question
  • Reading a notebook or Lookup result from the wrong place

    The wrong answers expect a result to appear as a pipeline variable, or in a property that does not exist. A notebook cannot set pipeline variables, and a Lookup does not create one. A notebook returns its value through notebookutils.notebook.exit, read from the activity output as output.result.exitValue. A single-row Lookup is read through firstRow, and a per-iteration value reaches the notebook as a base parameter mapped to item().

    See it in a sample question

Objectives in this domain

Sample questions from this domain

Free sampleImplement and Manage an Analytics Solutionmedium

A data engineering team uses a Microsoft Fabric workspace that contains a Lakehouse, several pipelines and a semantic model. A platform support analyst must view refresh history, monitor pipeline runs and inspect item configuration across the whole workspace, but must not be able to edit, run or delete any item. Which single workspace role grants exactly the access the analyst needs while following least privilege?

  • AAssign the Viewer role, because it grants read access to workspace items and their run and refresh history without create, edit, run or delete rights. Correct
  • BAssign the Contributor role, because it lets the analyst open every item in the workspace and observe how each pipeline and refresh is configured.
  • CAssign the Member role, because membership lets the analyst monitor items and reshare the workspace with other read-only support staff later.
  • DAssign the Admin role, because an administrator can see all run history and item settings across every artifact in the workspace.
Map a read-only monitoring requirement to the Viewer workspace role rather than a write-capable role. The four built-in workspace roles form an escalating capability ladder. Viewer is the only role limited to reading items and their run and refresh history, with no rights to create, edit, run or delete, so it satisfies a monitoring requirement under least privilege.

Why A is correct: Viewer is the read-only workspace role: the analyst can view items and history but cannot modify, execute or remove anything, matching least privilege exactly.

Why B is wrong: Contributor can create, edit and run items including pipelines, which exceeds the read-only monitoring requirement and breaks least privilege.

Why C is wrong: Member can edit items and manage permissions, which is far broader than monitoring and grants write and sharing rights the analyst should not hold.

Why D is wrong: Admin can view everything but also delete the workspace and reassign roles, so it grossly overprovisions a read-only monitoring task.

Free sampleImplement and Manage an Analytics Solutionhard

A Microsoft Fabric Warehouse holds one Sales table that every regional manager queries through the same shared view. Each manager must see only the rows for their own region, but the column set returned must stay identical for all of them so existing reports do not break. The team wants the filter enforced inside the Warehouse itself rather than in each report. Which access control best meets this requirement?

  • AApply column-level security so each manager is granted access only to the region column, leaving the other Sales columns inaccessible to them.
  • BConfigure dynamic data masking on the region column so each manager sees a masked value for regions other than their own when they query Sales.
  • CDefine row-level security with a security predicate that filters Sales rows to the region mapped to the querying user, applied through a security policy on the table. Correct
  • DCreate one separate physical Sales table per region and grant each manager object-level access only to the table that matches their region.
Use row-level security with a predicate and security policy to filter rows per user while keeping the returned column set identical for everyone. Row-level security attaches a predicate function to the table through a security policy, and the Warehouse engine applies that predicate to every query so each user retrieves only the rows their identity permits. The column projection is untouched, so reports keep working, which distinguishes it from column-level security, masking, or physically splitting the table.

Why A is wrong: Column-level security restricts which columns a user can read, not which rows; it would change the returned column set and still expose every region's rows, so it solves the wrong half of the requirement.

Why B is wrong: Dynamic data masking obscures column values in the output but never removes rows, so every manager would still retrieve all regions' rows; masking hides data, it does not enforce row-level access.

Why C is correct: Row-level security uses an inline predicate function bound by a security policy to filter rows per user at query time; managers see only their region while every column is returned unchanged, which is exactly the per-row, same-shape requirement.

Why D is wrong: Splitting into per-region tables could isolate data but multiplies maintenance and breaks the single shared view; object-level security gates whole objects, which is heavier than the per-row filter the scenario asks for.

Free sampleImplement and Manage an Analytics Solutionmedium

A Microsoft Fabric pipeline named Ingest copies raw files into a Lakehouse, and a second pipeline named Transform builds curated tables. Both are scheduled daily at 03:00. On some days Transform begins before Ingest has finished and reads partial data, producing incorrect curated tables. The team wants Transform to start only after Ingest completes successfully each day, while keeping a single daily entry point. Which change best enforces the ordering?

  • ARemove Transform's own schedule and invoke Transform from Ingest using an Invoke pipeline activity placed after Ingest's copy step succeeds, scheduling only Ingest daily. Correct
  • BMove Transform's schedule to 03:30 so Ingest has thirty minutes to finish before Transform begins, giving a buffer that avoids the overlap on most days.
  • CKeep both daily schedules but add a tumbling window dependency so Transform's window waits on Ingest's window before each run starts.
  • DAdd a Data Activator reflex that watches the Lakehouse row count and starts Transform when the count stops increasing, indicating that Ingest has finished writing.
Enforce cross-pipeline ordering by invoking the dependent pipeline on success from the upstream one, rather than relying on a time gap or an inferred signal. An Invoke pipeline activity placed after the upstream step and conditioned on success starts the downstream pipeline only when the upstream run completes correctly, which is a true completion dependency; a fixed time buffer fails when runs overrun, and an inferred row-count signal cannot confirm successful completion.

Why A is correct: Chaining Transform after the copy step inside Ingest with an Invoke pipeline activity on success makes Transform run only when Ingest finishes correctly, and scheduling only Ingest keeps a single daily entry point.

Why B is wrong: A fixed time gap is a guess that breaks whenever Ingest runs long, so it reduces overlap by luck rather than enforcing the completion dependency the scenario requires.

Why C is wrong: Tumbling window dependencies coordinate windows of the same trigger family, but the two pipelines run on plain daily schedules here, so this does not express a clean completion gate between separately scheduled pipelines.

Why D is wrong: Inferring completion from a row count that has stopped rising is indirect and unreliable, since a pause in writes is not proof of success, whereas an explicit on-success chain reports completion directly.

Free sampleImplement and Manage an Analytics Solutionhard

A business analyst team owns a Microsoft Fabric Lakehouse and needs to clean a set of imported Excel and CSV files: trim whitespace, change column types, merge two lookup tables, and remove duplicate rows before the result lands in a Lakehouse table. The team has no Spark or T-SQL skills, the volumes are modest, and they want a reusable, refreshable artefact they can maintain visually. Which orchestration artefact best fits this work?

  • AA Data Factory pipeline using a single Copy activity, since Copy can read the source files and write the cleaned result straight into the destination Lakehouse table.
  • BA Spark notebook in PySpark that reads the files into DataFrames, applies the cleaning logic with DataFrame APIs, and writes the result to the Lakehouse table.
  • CA Dataflow Gen2 that imports the files through Power Query, applies the trims, type changes, merge, and de-duplication as steps, and sets the Lakehouse table as the data destination. Correct
  • DA Data Factory pipeline with a Stored procedure activity that runs T-SQL to clean and merge the data after a Copy activity stages the files.
Choose a Dataflow Gen2 when business users need reusable, visual, low-code transformations over modest data volumes. The decisive factors are no coding skills, modest volumes, and a visually maintainable reusable artefact; Dataflows Gen2 are built on Power Query, whose step-based UI covers trims, type changes, merges, and de-duplication and writes to a Lakehouse destination, whereas notebooks and stored procedures require code skills the team does not have.

Why A is wrong: A Copy activity moves and lightly maps data but cannot trim, retype, merge lookups, and de-duplicate inside one step; the pipeline would still need a separate transform artefact, so Copy alone does not meet the cleaning requirement.

Why B is wrong: A notebook can perform every transform, but it demands Spark coding skills the team does not have and gives a code-first artefact they cannot maintain visually, so it fails the stated low-code maintainability requirement.

Why C is correct: Dataflows Gen2 expose Power Query's visual, step-based transforms that exactly match these operations and write to a Lakehouse destination, giving the no-code team a reusable, refreshable artefact they can maintain without writing code.

Why D is wrong: Stored-procedure cleaning works but requires T-SQL the analysts lack and runs against a SQL endpoint rather than a visual surface, so it is heavier and less maintainable than the low-code option the scenario calls for.

Free sampleImplement and Manage an Analytics Solutionhard

A Microsoft Fabric pipeline first runs a Lookup activity named GetConfig that returns a single row containing a targetSchema value, and a later Copy data activity must write to a destination whose schema name comes from that returned row. The team wants the Copy activity's schema setting built from the Lookup result at run time rather than typed as a literal. Which dynamic expression supplies the schema name from the Lookup output?

  • A@activity('GetConfig').output.value.targetSchema to read the targetSchema field from the value array the Lookup activity produced.
  • B@pipeline().output.GetConfig.targetSchema to read the targetSchema field from the pipeline output produced by the Lookup activity.
  • C@variables('GetConfig').targetSchema to read the targetSchema field from the pipeline variable that the Lookup activity automatically populated.
  • D@activity('GetConfig').output.firstRow.targetSchema to read the targetSchema field from the single row the Lookup activity returned. Correct
Reference a single-row Lookup result downstream with activity('name').output.firstRow.field in a dynamic expression. When a Lookup activity returns one row, the pipeline exposes that row at activity('name').output.firstRow, so firstRow.targetSchema reads the field directly into a downstream setting; output.value is the multi-row array needing an index, pipeline() carries run metadata not activity outputs, and a Lookup never auto-creates a variable.

Why A is wrong: output.value is an array of all returned rows, so addressing targetSchema directly on it without an index is invalid; a single-row lookup result is read through firstRow, not value.

Why B is wrong: pipeline() exposes run metadata and parameters, not activity outputs, so it has no GetConfig output path; an upstream activity result is reached through activity('GetConfig').output.

Why C is wrong: A Lookup activity does not automatically create or populate a pipeline variable, so variables('GetConfig') does not exist; the result is available only through the activity output object.

Why D is correct: A Lookup configured for a single row exposes that row under output.firstRow, so activity('GetConfig').output.firstRow.targetSchema reads the returned field and feeds it into the Copy activity schema setting at run time.

Other domains in this exam

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

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