Overview
Domain 2 is the hands-on core of DP-700, weighted at 30–35%, and it has the most bullets of any skill area. Microsoft groups it into three functional groups. Loading patterns covers full and incremental loads, preparing data for a dimensional model, and a loading pattern for streaming data.
Batch ingestion and transformation asks you to choose a data store and a transformation tool, create shortcuts, implement mirroring, ingest with pipelines, and transform with PySpark, SQL and KQL, including denormalizing, grouping, aggregating, and handling duplicate, missing and late-arriving data. Streaming covers choosing a streaming engine, processing with eventstreams, Spark structured streaming and KQL, and creating windowing functions. You need to read and reason about code in all three languages, not only click through designers.
How it's tested
Domain 2 items describe sources, volumes, latency and team skills, then ask how to land and shape the data. Expect code completion and ordering tasks in PySpark, T-SQL and KQL, plus design choices between copying, shortcutting or mirroring data and between batch and streaming engines.
-
Copy, shortcut or mirror
Data lives outside Fabric, and you decide whether to copy it with a pipeline, reference it with a shortcut, or replicate it with mirroring.
-
Complete the transform
A PySpark, T-SQL or KQL snippet has a gap, and you pick the function, clause or option that deduplicates, aggregates or merges correctly.
-
Pick the window
Events arrive continuously, and you choose a tumbling, hopping, sliding, session or snapshot window for the reporting rule described.
A retailer's orders table in Azure SQL Database must be queryable in Fabric within minutes, with no custom code and no change to the source application. A nightly pipeline copy misses the latency target, and a shortcut does not support Azure SQL Database as a target. Mirroring replicates the tables into OneLake as Delta in near real time and exposes a SQL analytics endpoint, so it fits.
Functional groups 3
Official functional groups from the Microsoft outline, in outline order.
2.1 Design and implement loading patternsFull and incremental loads, dimensional preparation and streaming load design.
- What it means
- This group covers choosing and building full or incremental loads, preparing source data for a star schema of facts and dimensions, and designing how streaming data lands in Fabric, for example through an eventstream into an eventhouse or lakehouse.
- What to understand
- A full load replaces the target each run and suits small or unreliable-delta sources. An incremental load moves only changes using a high-watermark column such as LastModified, change data capture, or Delta change data feed, then applies them with MERGE to upsert. Dimensional preparation means creating surrogate keys, choosing slowly changing dimension handling (type 1 overwrites, type 2 adds a row with effective dates and a current flag), and loading dimensions before facts so keys resolve. Medallion layers of bronze, silver and gold often structure these loads in lakehouses. Streaming loads need a decision on where hot data lands and how it reaches analytical tables.
- How it shows up
- Expect a choice of watermark, MERGE logic or SCD type, or ordering the steps of a dimensional load.
- Common confusion
- Loading facts before dimensions, which leaves fact rows without valid surrogate keys.
2.2 Ingest and transform batch dataStores, tools, shortcuts, mirroring, pipelines and transformation techniques.
- What it means
- This group covers choosing among lakehouse, warehouse, eventhouse and other stores, choosing dataflows, notebooks or T-SQL for transformation, creating shortcuts, implementing mirroring, ingesting with pipelines, transforming with PySpark, SQL and KQL, denormalizing, grouping and aggregating, and handling duplicate, missing and late-arriving data.
- What to understand
- A lakehouse suits Spark and mixed files plus Delta tables; a warehouse suits T-SQL teams needing full DML and multi-table transactions; an eventhouse suits time-series and telemetry queried with KQL. Dataflows Gen2 give low-code Power Query, notebooks give PySpark scale, and T-SQL suits warehouse stored procedures. Shortcuts reference data in OneLake, ADLS Gen2, Amazon S3 and others without copying. Mirroring continuously replicates supported databases such as Azure SQL Database, Azure Cosmos DB and Snowflake into Delta tables. Deduplicate with dropDuplicates or ROW_NUMBER, fill gaps with fillna or COALESCE, and handle late dimensions with inferred members updated later.
- How it shows up
- Expect a scenario naming a source, team skill and latency, then asking for store, tool, ingestion method or code.
- Common confusion
- Assuming a shortcut copies data; it is a pointer, so the source system's availability and permissions still apply.
2.3 Ingest and transform streaming dataStreaming engines, eventstreams, structured streaming, KQL and windows.
- What it means
- This group covers choosing among eventstreams, Spark structured streaming and KQL for real-time processing, processing events with each, and writing windowing functions that aggregate events over time.
- What to understand
- Eventstreams are no-code: connect sources such as Azure Event Hubs, IoT Hub or database CDC, apply filter, manage fields, aggregate, group by, join or union, and route to a lakehouse, eventhouse, Activator or derived stream. Spark structured streaming uses readStream and writeStream with a checkpointLocation for exactly-once recovery, an output mode of append, update or complete, and withWatermark to bound late events. In an eventhouse, update policies transform rows on ingestion and materialized views keep aggregates current. Tumbling windows are fixed and non-overlapping, hopping windows overlap, sliding windows emit on events, session windows close after inactivity, and snapshot windows group events with the same timestamp.
- How it shows up
- Expect an engine choice based on skills and latency, or a window type matched to a reporting rule.
- Common confusion
- Choosing a tumbling window when results must overlap, such as a five-minute average refreshed every minute, which needs hopping.
High-yield concepts
The terms, rules and formulas this domain turns into questions most often.
-
High watermark
Stored maximum change value used to select new rows.
- Remember
- Update it only after a successful load.
- Tested as
- Incremental load questions.
- Trap
- Updating the watermark before the copy completes.
-
MERGE
Upsert statement in Delta Lake and Fabric Warehouse T-SQL.
- Remember
- Matched rows update, unmatched rows insert.
- Tested as
- Incremental and SCD questions.
- Trap
- Using INSERT and creating duplicates.
-
SCD type 2
Keeps history by adding rows with validity dates.
- Remember
- Needs surrogate keys and a current flag.
- Tested as
- Dimensional questions.
- Trap
- Overwriting the attribute, which is type 1.
-
Shortcut
OneLake pointer to data stored elsewhere.
- Remember
- No copy; source permissions and availability apply.
- Tested as
- Ingestion choice questions.
- Trap
- Expecting a snapshot copy.
-
Mirroring
Near real-time replication of a database into OneLake Delta tables.
- Remember
- Read-only SQL analytics endpoint over mirrored data.
- Tested as
- Operational source questions.
- Trap
- Writing back through the mirrored endpoint.
-
Dataflow Gen2
Power Query low-code transformation with output destinations.
- Remember
- Fits analyst-style teams and many connectors.
- Tested as
- Tool choice questions.
- Trap
- Choosing it for very large Spark-scale jobs.
-
Copy job and Copy activity
Pipeline-based data movement from many sources.
- Remember
- Copy job adds simple incremental copy.
- Tested as
- Ingestion questions.
- Trap
- Using a notebook just to copy files.
-
dropDuplicates
PySpark method that removes duplicate rows, optionally by key columns.
- Remember
- Pass subset columns to dedupe on business keys.
- Tested as
- Code questions.
- Trap
- Calling distinct when only key columns matter.
-
Inferred member
Placeholder dimension row for a late-arriving dimension key.
- Remember
- Updated when the real dimension row arrives.
- Tested as
- Late data questions.
- Trap
- Dropping fact rows with unknown keys.
-
checkpointLocation
Storage path tracking structured streaming progress.
- Remember
- Required for recovery and exactly-once sinks.
- Tested as
- Spark streaming questions.
- Trap
- Omitting it and reprocessing data after restart.
-
withWatermark
Bounds how late events can arrive in Spark streaming.
- Remember
- Allows state cleanup for windowed aggregates.
- Tested as
- Late event questions.
- Trap
- Expecting unlimited lateness without growing state.
-
Update policy
Eventhouse rule that transforms data on ingestion into another table.
- Remember
- Runs a KQL function per batch.
- Tested as
- KQL ingestion questions.
- Trap
- Confusing it with a materialized view.
-
Hopping window
Fixed-size window that advances by a smaller hop.
- Remember
- Windows overlap; events count more than once.
- Tested as
- Windowing questions.
- Trap
- Picking tumbling for overlapping results.
-
Session window
Groups events until a gap of inactivity.
- Remember
- Window size varies.
- Tested as
- User activity questions.
- Trap
- Expecting fixed window lengths.
Side by side
Concepts candidates mix up, compared on the point that decides the answer.
Getting external data into Fabric
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| Pipeline copy | Physically copies data on a schedule | Full control of timing and format | Too slow for near real-time needs |
| Shortcut | References data in place | No duplication or movement | Source outage breaks queries |
| Mirroring | Continuous replication of a database | Near real-time, no code | Only supported sources |
Transformation tools
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| Dataflow Gen2 | Power Query in the browser | Low-code, many connectors | Struggles with very large volumes |
| Notebook | PySpark, Spark SQL or Scala | Scales and handles complex logic | Overkill for simple shaping |
| T-SQL | Warehouse procedures and queries | Familiar to SQL teams, transactional | Cannot write lakehouse tables |
| KQL | Queries and update policies in an eventhouse | Time-series and log analytics | Using it for relational modeling |
Window types
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| Tumbling | Fixed, contiguous, no overlap | Each event counted once | Using it for rolling averages |
| Hopping | Fixed size, overlapping hops | Rolling aggregates | Confusing hop and size |
| Sliding | Emits when events enter or leave | Event-driven output | Expecting regular output times |
| Session | Closes after inactivity gap | Variable length | Expecting a fixed duration |
| Snapshot | Groups events sharing a timestamp | No duration | Treating it as a time range |
Exam traps
Each trap is an answer that looks right. Learn the clue that should change your mind.
-
Shortcut for a SQL database
- What candidates pick
- Create a shortcut to an Azure SQL Database table.
- Why it looks right
- Shortcuts avoid copies, which sounds efficient.
- Why it's wrong
- Shortcuts target storage locations such as OneLake, ADLS Gen2 and S3; operational databases are brought in with mirroring or copy.
Clue to watch for The source is a relational database rather than files.
-
Warehouse writes to lakehouse
- What candidates pick
- Use T-SQL in the warehouse to insert into lakehouse tables.
- Why it looks right
- Both expose SQL endpoints.
- Why it's wrong
- The lakehouse SQL analytics endpoint is read-only; write lakehouse tables with Spark, pipelines or dataflows.
Clue to watch for The target is a lakehouse table.
-
Distinct for business keys
- What candidates pick
- Use distinct to remove duplicate customers with different timestamps.
- Why it looks right
- Distinct removes duplicates.
- Why it's wrong
- Rows differing in any column survive distinct; dropDuplicates on key columns or ROW_NUMBER is needed.
Clue to watch for Duplicates share a key but differ elsewhere.
-
Dataflow for huge volumes
- What candidates pick
- Use Dataflow Gen2 for billions of rows with complex joins.
- Why it looks right
- It is the easiest tool.
- Why it's wrong
- Notebooks with Spark scale better for heavy transformations; dataflows suit simpler, low-code shaping.
Clue to watch for Very large volume and a team comfortable with code.
-
Drop late facts
- What candidates pick
- Filter out fact rows whose dimension key is not found.
- Why it looks right
- It keeps referential integrity.
- Why it's wrong
- Dropping loses data; an inferred member keeps the fact and is updated when the dimension arrives.
Clue to watch for The requirement says no fact rows may be lost.
-
Complete output mode for appends
- What candidates pick
- Use complete mode for a stream writing new events to a Delta table.
- Why it looks right
- Complete sounds thorough.
- Why it's wrong
- Complete mode rewrites the full aggregate each trigger; append mode suits new rows without aggregation updates.
Clue to watch for Rows are only added, never changed.
Answer framework
Use these steps to choose and build an ingestion and transformation path in a DP-700 scenario.
- Classify the source and latency: files or databases, batch or streaming, and whether minutes, hours or a day is acceptable.
- Decide how data arrives: shortcut to leave it in place, mirroring for supported databases, pipeline or Copy job for copies, eventstream for events.
- Choose the store by query language and workload: lakehouse for Spark, warehouse for T-SQL transactions, eventhouse for telemetry with KQL.
- Choose the tool by team skill and scale: Dataflow Gen2 for low-code, notebooks for large Spark work, T-SQL for warehouse logic.
- Apply data quality rules: watermarks and MERGE for changes, deduplication, null handling, inferred members, and watermarks or windows for streams.
Practice question
Want more like this? The DP-700 practice test has questions for every domain.
Checklist
0 of 14
Tick each one you can do without notes. Your ticks stay in this browser.
Study strategy
Plan the most time for this domain because it carries the widest set of bullets and tests code in three languages. Start with batch work in a lakehouse: land a CSV with a pipeline, convert it to Delta in a notebook, then build an incremental load with a watermark table and MERGE. Add an SCD type 2 dimension so you can explain each column it needs.
Repeat the same transformation once in Dataflow Gen2 and once in warehouse T-SQL. Noting where each tool feels natural is exactly the judgment the exam asks for. Create a shortcut to external storage and set up mirroring from an Azure SQL Database if you can, then compare how each behaves when the source changes.
For streaming, use the sample data sources in an eventstream, route them to an eventhouse, and write KQL with summarize and bin. Then build a small structured streaming job with a checkpoint and watermark. Draw the five window types on paper until you can name each one from a short description.
FAQ
How much of DP-700 is Domain 2, Ingest and transform data?
Domain 2, Ingest and transform data, is weighted at 30–35% by Microsoft. It has three functional groups: loading patterns, batch ingestion and transformation, and streaming ingestion and transformation.
Which languages do I need for Domain 2?
Microsoft lists PySpark, SQL and KQL. Expect to read and complete short code fragments in each.
What is the difference between a shortcut and mirroring?
A shortcut points to files or tables in storage without copying them. Mirroring continuously replicates a supported database into OneLake Delta tables.
Can I write to a lakehouse with T-SQL?
No. The lakehouse SQL analytics endpoint is read-only. Use Spark, pipelines or dataflows to write lakehouse tables, or use a warehouse for T-SQL writes.
How should late-arriving dimension data be handled?
Insert an inferred member so fact rows keep a valid key, then update that dimension row when the real data arrives.
Which streaming engine should I choose?
Use eventstreams for no-code routing and simple transforms, Spark structured streaming for code-heavy logic, and KQL in an eventhouse for fast time-series analysis.
Do I need to know Azure Data Factory or Synapse for this domain?
Not as separate services. DP-700 tests Fabric pipelines, dataflows, notebooks and Real-Time Intelligence items, which replace the Synapse-centric topics in the retired DP-203.
Other Data & Analytics certifications
Where the same subject shows up in other exams. Cards marked "Same subject" open the matching domain.
Test your Domain 2 knowledge
You have covered loading patterns, stores and tools, shortcuts, mirroring, batch transforms and streaming windows. Test whether you can pick the right path and complete the code.
Keep going
- Training Training Built on your Weak areas
- Practice test Questions by domain, with explanations
- Mock exam Full length, timed like the real exam
- Readiness test Find your weakest domain in minutes
- Cheat sheet Key terms and formulas on one page
- Ask the DP-700 AI tutor Get any concept explained your way