DP-700 ingest and transform data explained
Ingest and transform data is worth 30–35% of DP-700. It covers three areas: loading patterns, batch ingestion and transformation, and streaming. The exam asks you to choose an approach and to read or complete SQL, PySpark and KQL, so you need both judgement and syntax.
Loading patterns
Full versus incremental. A full load replaces everything and is simple, but its cost grows with the table. An incremental load moves only new or changed rows, usually using a watermark (a stored value such as the highest modified date or key already loaded) or change data capture. Choose full for small reference data; incremental when a large table changes slowly.
Preparing for a dimensional model. Load dimension tables before fact tables so facts can look up surrogate keys. Slowly changing dimensions type 2 keep history by closing the old row and inserting a new one, which in Delta is done with a MERGE.
Streaming loads write continuously into a destination, such as an Eventstream into a lakehouse or an Eventhouse.
Ingest and transform batch data
Choosing a store and a tool
| Choose | When |
|---|---|
| Lakehouse | Files and Delta tables, Spark-first work, mixed structured and unstructured data |
| Warehouse | T-SQL-first work with full SQL semantics for analytics |
| Eventhouse | Time-series, logs and streaming analytics with KQL |
For transformation: Dataflows Gen2 for low-code, notebooks for code at scale, KQL for data in an Eventhouse, T-SQL for data in a warehouse.
Getting data in
- Pipelines with the Copy activity (and Copy job) move data from many sources.
- OneLake shortcuts point at data in another location (for example ADLS Gen2, Amazon S3 or another lakehouse) so it appears in place without copying.
- Mirroring continuously replicates a supported operational database into OneLake as Delta tables.
Transforming
Know the PySpark and SQL patterns: joins, groupBy with aggregates, denormalizing into wide tables, and cleaning.
- Duplicates:
dropDuplicatesremoves exact duplicates on chosen columns but does not control which row survives. To keep the latest row per key, userow_number()over a window partitioned by key and ordered by timestamp descending, then keep row 1. - Missing data:
fillna,dropnaor default values, depending on meaning. - Late-arriving data: facts that arrive before their dimension row are loaded against an inferred or unknown member and corrected later; in streams, a watermark defines how late is too late.
Ingest and transform streaming data
| Engine | Strength |
|---|---|
| Eventstream | No-code ingest and routing of events, with light transformations |
| Eventhouse with KQL | Low-latency time-series and log queries over streaming data |
| Spark structured streaming | Complex PySpark logic and Delta sinks, running in a notebook |
Two Real-Time Intelligence choices appear in the outline: native tables versus OneLake shortcuts (data ingested into the Eventhouse versus left in the lakehouse), and query acceleration for shortcuts, which indexes and caches shortcut data so KQL queries run faster.
Windowing
| Window | Behaviour |
|---|---|
| Tumbling | Fixed size, no overlap, every event in exactly one window |
| Hopping | Fixed size that advances by a smaller hop, so windows overlap |
| Sliding | Emits a result when an event enters or leaves the window |
| Session | Groups events until a gap of inactivity |
| Snapshot | Groups events with identical timestamps |
In KQL a tumbling aggregation is summarize count() by bin(Timestamp, 5m). In Spark, window(col, '5 minutes') is tumbling, window(col, '10 minutes', '5 minutes') is hopping, and withWatermark bounds state for late data.
Example questions
Question 1. A lakehouse team needs to read Parquet files in an ADLS Gen2 container from Spark notebooks, without copying them and without duplicating storage cost. What should they create?
- A. A nightly Copy activity into the Files area
- B. A mirrored database for the storage account
- C. A OneLake shortcut to the container
- D. A Dataflow Gen2 that loads the files into a table
Show answer
Answer: C
A OneLake shortcut exposes the external folder inside the lakehouse so Spark can read it in place. A Copy activity would duplicate the data. A mirrored database replicates a supported operational database rather than a file container. A Dataflow Gen2 transforms and loads data and also copies it.
Want more questions like this? Full DP-700 practice tests →
Question 2. A Delta table holds multiple rows per CustomerId with different UpdatedAt values. You need exactly one row per customer: the one with the latest UpdatedAt. Which PySpark approach is correct?
- A. df.dropDuplicates([‘CustomerId’])
- B. Use row_number() over a window partitioned by CustomerId ordered by UpdatedAt descending and keep row 1
- C. df.groupBy(‘CustomerId’).max()
- D. df.orderBy(‘UpdatedAt’).limit(1)
Show answer
Answer: B
Row_number over a window partitioned by CustomerId and ordered by UpdatedAt descending, keeping row 1, deterministically keeps the latest row. dropDuplicates on CustomerId keeps an arbitrary row. Grouping by CustomerId and taking max of all columns can mix values from different rows. Sorting and taking limit 1 returns a single row for the whole table.
Want more questions like this? Full DP-700 practice tests →
Question 3. A KQL query must count events per device in non-overlapping five-minute intervals. Which expression gives tumbling windows?
- A. summarize count() by DeviceId, bin(Timestamp, 5m)
- B. join kind=inner (Devices) on Timestamp
- C. top 5 by Timestamp
- D. sample 5
Show answer
Answer: A
Using summarize with bin(Timestamp, 5m) assigns each event to exactly one fixed five-minute bucket, which is a tumbling window. A join with a time filter does not create buckets. Top 5 returns the five largest rows. Sample 5 returns five random rows.
Want more questions like this? Full DP-700 practice tests →
How this domain is tested
Expect decision questions (which engine, which store, which window) and short code completion. Practise by building the same aggregation in KQL and in Spark. Next, monitor and optimize.