Ingest and transform data Study Guide — DP-700

Ingest and Transform Data

This domain covers the design and implementation of data ingestion and transformation pipelines in Microsoft Fabric. Microsoft publishes all three DP-700 domains at 30–35% each (not exact point values) — this domain is roughly tied with the other two, not the single largest. Questions test your ability to choose the right loading pattern, select the correct Fabric service for a given workload, configure pipelines, write or interpret transformation code (PySpark, T-SQL, KQL), and handle real-world data quality issues including duplicates, nulls, and late-arriving events.

Full vs. Incremental Loads; Dimensional Model Loading; Streaming Load Patterns

Full vs. Incremental Loads

Watermark pattern: Store the maximum LastModified or surrogate key value from the previous run. On the next run, filter source rows WHERE LastModified > @watermark. This is the standard incremental pattern in Fabric pipelines.

Dimensional Model Loading

• Slowly Changing Dimension Type 1 (SCD1): Overwrite the existing attribute. No history preserved.
• SCD Type 2 (SCD2): Add a new row with EffectiveDate, ExpiryDate, and IsCurrent flag. History preserved. Requires surrogate key in fact table.
• SCD Type 3: Add a "previous value" column. Limited history; rarely tested as primary pattern.
• Fact table loading order: Always load dimensions first to resolve surrogate keys before loading facts.
• Late-arriving dimensions: Insert a placeholder (dummy) dimension row with a surrogate key; update attributes when the real record arrives.

Streaming Load Patterns

• Append-only: Every event becomes a new row. Used for immutable event logs (telemetry, IoT).
• Upsert stream: Incoming events update existing rows (e.g., device last-known state). Requires stateful processing.
• Micro-batch: Spark Structured Streaming default. Small batches processed at intervals (trigger interval or processingTime).
• Continuous processing: Near-true streaming; Spark experimental. Not the default exam answer.

Common traps

• Choosing full load for a 500-million-row fact table refreshed hourly — incremental is required.
• Assuming SCD2 is the default; the exam will describe "no history needed" explicitly for SCD1.
• Confusing "micro-batch" (Spark Structured Streaming) with "true streaming" — Spark's default is micro-batch.
• Late-arriving data in a dimensional model should use a dummy dimension row first, not fail the load.

Choosing Data Stores; Dataflows Gen2 vs. Notebooks vs. KQL vs. T-SQL

Data Store Selection

Transformation Tool Selection

Key distinction: The Lakehouse SQL Analytics Endpoint is read-only — you cannot run DML (INSERT, UPDATE, DELETE) through it. To modify data in a Lakehouse via SQL, you must use a Warehouse or a Notebook.

Scenario: A data engineering team needs to join three large Delta tables, apply business rules in Python, and write results back to a Lakehouse. → Notebook (PySpark). Dataflows Gen2 lacks Python; T-SQL endpoint is read-only; KQL is built for time-series/log workloads on Kusto-backed data, not Delta-table star-schema joins.

Common traps

• Using Dataflows Gen2 when the transformation requires Python/custom logic — not possible.
• Using the SQL Analytics Endpoint (read-only) to INSERT into a Lakehouse table.
• Choosing KQL for a standard relational star-schema transformation over Delta tables — KQL can join, but it's designed for Kusto-backed time-series/log data, not general-purpose relational modeling.
• Dataflows Gen2 has an output destination; it is not just a query tool — it writes to a sink.