DP-700 domain - 33% of the exam

Ingest and Transform Data

Ingest and Transform Data is 33% of the DP-700 exam. These are the objectives it covers, each with practice questions, with every answer explained.

The domain in numbers

87
Practice questions
10
Objectives
18
Medium
69
Hard

Where people go wrong

  • Appending where a keyed upsert is needed

    The wrong answers append each batch, or assume Delta will reject repeats, and so leave several rows for one key. Delta Lake gives transactions and versioning but does not enforce primary-key uniqueness, so redelivered or changed rows pile up. The correct answers reduce the batch to one row per key with a window ordered on the latest timestamp, then MERGE on the business key, updating matched rows and inserting the rest. Re-running a batch then converges on one current row per key.

    See it in a sample question
  • Swapping shortcuts and mirroring

    The wrong answers pick the capability for the wrong kind of source. Mirroring is offered for file buckets in Amazon S3 or Google Cloud Storage, and shortcuts are offered for transactional databases such as Azure SQL Database and Azure Cosmos DB. Mirroring replicates a supported operational database into OneLake as Delta tables and follows its changes continuously. A OneLake shortcut reads files or tables in place at their storage location, so no second copy is held and reads see the current contents.

    See it in a sample question
  • Expecting writes through the Lakehouse SQL analytics endpoint

    The wrong answers choose the Lakehouse SQL analytics endpoint for work that needs T-SQL INSERT, UPDATE, DELETE, MERGE or stored procedures. That endpoint is read-only, so those statements fail however it is permissioned. Writes to Lakehouse Delta tables come from Spark. A Microsoft Fabric Warehouse is the store with a full read-write T-SQL surface, including multi-table transactions and stored procedures, and one T-SQL query in it can join its own tables to a Lakehouse and persist the result.

    See it in a sample question
  • Handling late events without a watermark

    The wrong answers treat late-arriving events with a longer trigger interval, complete output mode, ingestion-time windows, more memory or repartitioning. None of these decides when an event-time window is final. A watermark on the event-time column sets the lateness bound: events inside it are still counted, windows past it are finalised, and their state is released. Complete mode keeps all state, and deleting the checkpoint replays the source, so neither bounds lateness.

    See it in a sample question
  • Confusing tumbling, hopping and session windows

    The wrong answers pick a window type whose boundaries do not match the requirement. A tumbling window is fixed and non-overlapping, so each event lands in one block, but it cannot give a rolling total refreshed more often than its length, and it splits a burst across boundaries. A hopping or sliding window overlaps when its slide is shorter than its size, so an event counts in several windows: right for a ten-minute total refreshed every minute, wrong for fixed blocks. A session window closes after a gap of inactivity.

    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 sampleIngest and Transform Datahard

A bronze Delta table in a Microsoft Fabric Lakehouse receives an hourly extract from a webhook source that occasionally redelivers the same event, so the same EventId can appear several times with identical column values. The team needs the silver table to keep exactly one row per EventId, and when an EventId recurs the row with the latest IngestedUtc should win. Using a Spark notebook, which approach best deduplicates the data?

# bronze rows: EventId, payload, IngestedUtc (duplicates possible)
# goal: one row per EventId, latest IngestedUtc wins
  • AApply dropDuplicates on the EventId column alone, which keeps one arbitrary row per EventId and discards the rest before writing to the silver table.
  • BUse a window partitioned by EventId ordered by IngestedUtc descending, assign a row number, and keep only the rows where the row number equals one before writing. Correct
  • CGroup by EventId and take the maximum IngestedUtc per key, then join that result back to the source on both EventId and IngestedUtc to select the winning row.
  • DAppend every extract to the silver table and rely on Delta Lake to discard duplicate EventId values automatically because the table is stored in Delta format.
Deduplicate to one row per key with a deterministic tie-break using a window ranked on the recency column, not a plain dropDuplicates. Keeping the latest row per key needs a deterministic ordering, which a window partitioned by EventId and ordered by IngestedUtc descending provides: the newest row becomes row number one for each key, so filtering to row number one yields exactly one winning row, whereas dropDuplicates keeps an arbitrary row and Delta Lake never enforces key uniqueness.

Why A is wrong: dropDuplicates on EventId removes duplicates but keeps an arbitrary surviving row, so it cannot guarantee the latest IngestedUtc wins; the tie-break the requirement states is left to chance, which is the trap here.

Why B is correct: Partitioning by EventId and ranking by IngestedUtc descending makes the latest row rank one for each key, and filtering to rank one keeps exactly one deterministic winning row per EventId in a single pass, which is what the requirement asks.

Why C is wrong: This returns the latest row per EventId, but if two redeliveries share the maximum IngestedUtc the join emits both, reintroducing duplicates; a window with a deterministic ordering is the cleaner single-pass answer.

Why D is wrong: Delta Lake does not enforce primary-key uniqueness, so appending simply stores every redelivered row; the format gives transactions and versioning, not automatic deduplication, so duplicates would accumulate.

Free sampleIngest and Transform Datamedium

A partner publishes a large dataset of Parquet files in an Amazon S3 bucket and updates it several times a day. A Microsoft Fabric Lakehouse must query the latest files for analytics, the team wants to avoid cross-cloud egress and storage costs from duplicating the data into OneLake, and reads should reflect the partner's most recent updates. Which approach best meets these requirements?

  • AUse a Data Factory copy activity to pull the Amazon S3 files into a Lakehouse Delta table on a recurring schedule for the analysts to query.
  • BEnable Fabric mirroring for the Amazon S3 bucket so the partner's files are continuously replicated into OneLake as Delta tables.
  • CConfigure an hourly Dataflows Gen2 refresh that reads the Amazon S3 files and materialises them as a Lakehouse table for analytics.
  • DCreate a OneLake shortcut from the Lakehouse to the Amazon S3 bucket, so Fabric reads the partner's files in place rather than copying them into OneLake. Correct
Use a OneLake shortcut to Amazon S3 to query cross-cloud files in place, avoiding duplication, egress, and staleness from copy jobs. A OneLake shortcut supports Amazon S3 as a target and resolves reads against the bucket in place, so the files are never duplicated into OneLake and queries pick up the partner's latest updates; this avoids the repeated cross-cloud egress and storage cost that any scheduled copy or refresh into OneLake would incur.

Why A is wrong: A recurring copy duplicates the dataset into OneLake and incurs cross-cloud egress on every run, breaking the no-duplication goal and leaving reads stale between scheduled copies.

Why B is wrong: Mirroring replicates supported operational databases rather than an S3 file bucket, and it would still maintain a duplicated copy, so it neither applies to this source nor avoids the duplication cost.

Why C is wrong: An hourly refresh copies the files into OneLake, paying egress each run and leaving data up to an hour behind, so it fails both the no-duplication and the freshness requirements.

Why D is correct: A OneLake shortcut to S3 reads the partner's files in place, so no data is duplicated into OneLake, egress from repeated full copies is avoided, and each query reflects the bucket's most recent files.

Free sampleIngest and Transform Datahard

A finance team will build a curated reporting layer in Microsoft Fabric where downstream processes issue multi-statement T-SQL transactions: INSERT, UPDATE, and DELETE across several related tables that must commit or roll back together. The team works entirely in T-SQL and does not use Spark. Which Fabric data store best supports this write workload?

  • AA Microsoft Fabric Lakehouse queried through its SQL analytics endpoint, because the endpoint runs the team's INSERT, UPDATE, and DELETE statements directly against the Delta tables.
  • BA Microsoft Fabric Lakehouse written to by Spark, because PySpark MERGE and DELETE on Delta tables give the team the transactional writes their T-SQL processes need.
  • CA Microsoft Fabric Eventhouse with a KQL database, because KQL update policies apply the inserts, updates, and deletes across the related tables in one committed batch.
  • DA Microsoft Fabric Warehouse, because it offers a full read-write T-SQL surface with multi-table transactions that commit or roll back as a unit. Correct
Choose a Warehouse when a T-SQL team needs a full read-write surface with multi-table transactional INSERT, UPDATE, and DELETE. The requirement combines a T-SQL-only team with multi-statement transactions that span several tables and must commit or roll back together; only the Warehouse provides a full read-write T-SQL engine with that transactional support, while the Lakehouse SQL analytics endpoint is read-only and the Eventhouse targets KQL telemetry.

Why A is wrong: The Lakehouse SQL analytics endpoint is read-only for T-SQL; it cannot run INSERT, UPDATE, or DELETE against the Delta tables, so it cannot serve a transactional write workload despite presenting a familiar T-SQL surface.

Why B is wrong: Delta on a Lakehouse does support atomic table operations, but the writes happen through Spark, not T-SQL, and the team does not use Spark; this changes the tool, not just the store, so it does not meet the stated requirement.

Why C is wrong: An Eventhouse targets append-heavy telemetry and KQL, not curated multi-table T-SQL transactions; update policies transform incoming data rather than provide the cross-table transactional T-SQL writes the finance team requires.

Why D is correct: A Warehouse exposes a complete read-write T-SQL engine with multi-statement, multi-table transaction support, which is exactly what a T-SQL team needs to insert, update, and delete across related tables atomically.

Free sampleIngest and Transform Datahard

An Eventstream in Microsoft Fabric ingests telemetry into an Eventhouse, and a Spark structured streaming job aggregates event counts into one-minute tumbling windows for a dashboard. Because of mobile connectivity gaps, events sometimes arrive with an EventTime several minutes earlier than their ingestion time. The team wants windows to wait a bounded grace period for stragglers before being treated as final, while discarding events that arrive beyond that bound. Which mechanism best expresses this requirement?

  • ADefine a watermark on EventTime with a bounded allowed lateness, so each window stays open for that grace period and events later than the bound are dropped. Correct
  • BAggregate the windows on ingestion time instead of EventTime so every event always lands in the window that is open when it is received.
  • CWiden every tumbling window from one minute to fifteen minutes so any event delayed by a few minutes still falls inside the same larger window.
  • DBuffer all events for an hour in a staging table and replay them in EventTime order once the buffer is full, then aggregate the reordered stream.
Use an event-time watermark with bounded allowed lateness to admit stragglers within a grace period and discard events beyond it. Aggregating on EventTime requires deciding when a window is complete despite stragglers, and a watermark with bounded allowed lateness does this: it holds each window open for the grace period, then finalises it and drops later events, whereas switching to ingestion time mis-buckets delayed events and widening windows merely coarsens the reporting granularity.

Why A is correct: A watermark on EventTime with a bounded allowed-lateness interval keeps each window open for the grace period to admit stragglers, then finalises it and discards events beyond the bound, which matches the stated late-arrival handling exactly.

Why B is wrong: Bucketing on ingestion time guarantees no straggler is late, but it places delayed events in the wrong minute relative to when they actually occurred, which corrupts the event-time aggregation the dashboard reports.

Why C is wrong: Coarser windows hide some lateness, but they change the dashboard's reporting granularity from one minute to fifteen and still mis-bucket events delayed beyond the wider span; the lateness problem is bounded, not solved.

Why D is wrong: Hour-long buffering and replay would order events, but it adds large latency unsuited to a live dashboard and an unbounded wait; a watermark gives the same bounded tolerance with far less delay and clear finalisation.

Free sampleIngest and Transform Datahard

A team ingests clickstream events into an Eventhouse and must compute the count of events per device for each fixed five-minute block, where every event belongs to exactly one block and the blocks never overlap. They want to express this aggregation in KQL over the event timestamp. Which windowing function fits a non-overlapping fixed-interval count of this kind?

  • AA hopping window of size five minutes that advances by one minute, so each event is counted in several overlapping windows for a smoother per-device trend.
  • BA tumbling window that buckets each event into a single fixed five-minute interval, so every event contributes to exactly one non-overlapping window. Correct
  • CA session window that groups a device's events while they keep arriving and closes only after a gap of inactivity longer than five minutes elapses.
  • DA snapshot taken at each five-minute mark that counts only the events present at that instant rather than aggregating the interval between marks.
A tumbling window assigns each event to exactly one contiguous, equal-length, non-overlapping interval, matching a fixed per-block count. Tumbling windows divide the time axis into back-to-back intervals of equal width with no overlap and no gaps, so a single event is assigned to one and only one window; this is the correct model for counting events per fixed five-minute block, whereas hopping windows overlap and session windows are sized by inactivity gaps.

Why A is wrong: A hopping window with a hop smaller than its size overlaps, so an event falls into multiple windows and is counted more than once; that breaks the rule that every event belongs to exactly one block.

Why B is correct: A tumbling window partitions time into contiguous, equal-length, non-overlapping intervals, so each event lands in exactly one bucket; that is precisely the fixed five-minute block count the requirement describes.

Why C is wrong: A session window is gap-driven and produces variable-length windows per device, not fixed five-minute blocks; its boundaries depend on inactivity rather than the calendar interval the requirement fixes.

Why D is wrong: Counting events present at an instant captures a point, not the events accumulated across the interval; the requirement is a per-interval count, so an instantaneous snapshot omits everything that arrived between marks.

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.