Microsoft Certified: Fabric Data Engineer Associate (DP-700) Study Guide

Microsoft Certified: Fabric Data Engineer Associate (DP-700) Study Guide

Issuing body: Microsoft Jurisdiction: Global (vendor certification — not a legal license to practice) Level: Associate Prerequisites: None formal; SQL/Python/Spark and data engineering fundamentals recommended. (Source: catalogue record data-ai-ml-cert-010.) Renewal: 1 year (free annual renewal assessment).

1. What this exam is

DP-700 is the Fabric-era data engineering credential: the successor to the retired DP-203 (retired 2025-03-31). It tests designing and operating data solutions on Microsoft Fabric — the unified SaaS analytics platform (OneLake, lakehouses, warehouses, Power BI, Data Factory, Synapse-derived runtimes). Where DP-203 tested Azure Synapse + Data Factory + Databricks-on-Azure as separate services, DP-700 tests the integrated Fabric experience: one copy of data in OneLake, multiple compute engines over it. (Note: DP-600 is the Fabric Analytics Engineer cert — related but distinct; don't confuse them.)

2. Pathway and exam format

• Typical path: DP-900 (Azure Data Fundamentals) → SQL/Python/Spark fluency → hands-on Fabric (trial capacity or F-SKU) → DP-700. DP-203 holders: the concepts transfer, but the Fabric service model (OneLake, shortcuts, Direct Lake) is new material.
• Format: Microsoft associate pattern — typically 40–60 questions, ~100 minutes, 700/1000 pass; verify current specifics on learn.microsoft.com (marked unverifiable where not confirmed in the catalogue pass). Free yearly renewal assessment.
• The exam's mental model: "one copy of data, many engines." Every question asks which Fabric workload (lakehouse, warehouse, eventstream, data pipeline, notebook, KQL database...) serves a requirement over data in OneLake.

3. Knowledge domains (with theory)

Public blueprint (approximate): Plan and implement data ingestion/processing (~30%), Implement and manage OneLake/lakehouses/warehouses (~30%), Monitor, optimize, secure (~25%), Data governance (~15%). Verify against the official skills outline.

The Fabric architecture (the theory everything hangs on)

OneLake: the single logical data lake for the tenant (built on ADLS Gen2, open Delta-Parquet format). Shortcuts (pointer to external data — ADLS, S3, Dataverse — query without copying) and mirroring (continuous replication from Azure SQL/Cosmos DB/Snowflake into OneLake Delta tables — the zero-ETL answer). The exam's core insight: shortcuts avoid data duplication (query in place); mirroring creates a managed replica (for performance/isolation). Choose by latency/freshness needs.

Compute engines over OneLake:

• Lakehouse (Spark): the data-engineering workspace — notebooks, Spark job definitions, Delta tables. Medallion architecture (bronze/silver/gold) is the expected pattern.
• Warehouse (SQL): T-SQL over Delta tables, the data-warehousing workload. Direct Lake (Power BI reads Delta directly — no import/refresh for large models) vs DirectQuery vs Import — the BI serving decision.
• Eventstream / Real-Time Intelligence: streaming ingestion (Eventstream sources) → KQL databases (Eventhouse) for high-volume telemetry; Activator for event-driven actions (the "alert me when X" answer).
• Data Factory pipelines: orchestration (Copy activity, dataflows Gen2); notebooks for code-first transforms.
• Power BI: the serving layer (Direct Lake for Fabric-scale models).

Capacity model: Fabric runs on capacities (F-SKUs for PAYG, P-SKUs premium, trial). Capacity Units (CUs) meter compute; throttling/smoothing (burstable operations smoothed over time — understand that heavy jobs borrow against future capacity). The exam tests cost/capacity reasoning: right-size the F-SKU, pause when idle, monitor CU consumption.

Ingestion and processing

Batch: Data Factory Copy (100+ connectors), shortcuts (zero-copy), mirroring (zero-ETL continuous). Streaming: Eventstream (sources: custom app, Azure Event Hubs...) → lakehouse streaming tables / KQL DB; Spark Structured Streaming in notebooks for complex stream processing. Change feeds: mirror Azure SQL/Cosmos for CDC.

Medallion (bronze → silver → gold): bronze (raw, append-only, schema-on-read), silver (cleaned, validated, conformed — dedup, schema enforcement), gold (business aggregates, star schemas for BI). The exam expects medallion literacy: every pipeline question maps to these layers.

Delta Lake (the format theory): ACID transactions on data lakes (time travel, schema enforcement/evolution, OPTIMIZE/Z-ORDER, VACUUM). Why it matters: concurrent readers/writers without corruption, exactly-once streaming sinks, schema evolution without rewrites. Know: Delta vs plain Parquet (ACID + time travel + DML), liquid clustering/Z-ordering for file-skipping, V-Order (Fabric's Parquet optimization for Power BI reads).

Warehousing and serving

• Warehouse vs Lakehouse SQL endpoint: the warehouse is the governed T-SQL serving layer (star schemas, secured views); the lakehouse SQL endpoint is ad-hoc. The exam's distinction: curated BI serving → warehouse; exploration/engineering → lakehouse.
• Direct Lake: Power BI reads Delta tables directly (no scheduled refresh, no import memory limits) — the answer for large Fabric models. Fallback: DirectQuery (live SQL) or Import (small models).
• Semantic models: relationships, DAX measures, row-level security (RLS) in Power BI — the serving-layer security.

Monitoring, optimization, security, governance

• Monitor Hub + capacity metrics app: CU consumption, throttling events, job durations. Optimization: V-Order, partitioning (by date), Z-order/clustering on filter columns, file compaction (small-files problem — the #1 lakehouse perf killer), autoscale/spark pool sizing.
• Security: workspace roles (Admin/Member/Contributor/Viewer), OneLake data access roles, item-level sharing, sensitivity labels (Purview), private links, managed VNet, customer-managed keys (CMEK).
• Governance: Purview integration (catalog, lineage, classification), endorsement/certification (promote trusted items), deployment pipelines (dev → test → prod for Fabric items — ALM for analytics), data lineage views.

4. Key numbers and formulas

• Medallion storage math: bronze ≈ raw volume; silver ≈ 0.5–1× (dedup/compression); gold ≈ 0.1–0.3× (aggregates). Budget OneLake storage accordingly (storage is cheap; the compute to rebuild is the cost).
• Small-files rule: target 128 MB–1 GB files in Delta tables; thousands of KB-sized files = metadata/query-planning meltdown. OPTIMIZE/compaction is the fix.
• Capacity sizing: monitor CU seconds per workload; size F-SKU so p95 utilization <80% (headroom for bursts); smoothing means a 10-min spike borrows from the next hours — chronic >100% = throttling.
• Partitioning: partition by date (the filter column); Z-order by the next 1–2 high-cardinality filter columns. Same theory as BigQuery clustering.
• Freshness budgeting: mirroring lag (minutes) + transform time + serving refresh = end-to-end; Direct Lake removes the refresh term.

5. Worked examples

Example 1 — The canonical Fabric pipeline. Scenario: E-commerce events (50k/day) + SQL Server orders DB; need real-time ops dashboard + daily finance reporting + analyst ad-hoc. Solution: Eventstream (events) → lakehouse bronze (streaming table); mirror the SQL Server (orders → OneLake Delta, continuous); notebooks (Spark) bronze → silver (validate, dedupe, conform) → gold (daily aggregates, star schema in the warehouse); Power BI Direct Lake on gold for finance (no refresh), Real-Time dashboard on the KQL DB for ops (sub-minute), lakehouse SQL endpoint for analysts. OneLake holds one copy; three engines serve three latencies.

Example 2 — Shortcut vs mirror vs copy. Scenario: 20 TB of historical data in ADLS Gen2 (another team owns it); analysts need to query it from Fabric. Solution: Shortcut (query in place — no copy, no egress within Azure, always fresh). If they needed to transform it heavily or the source had throttling limits → copy into OneLake (Copy activity, one-time + incremental). If it were an operational DB needing continuous sync → mirror. The decision: ownership + freshness + transformation need. The trap: copying 20 TB "just in case" (storage + pipeline + staleness for nothing).

Example 3 — Small-files meltdown. Scenario: Streaming lakehouse table has 200k files averaging 50 KB; queries take 20× longer than expected. Solution: Enable auto-compaction / scheduled OPTIMIZE on the Delta table (bin-pack to ~128 MB–1 GB); for streaming: increase the trigger interval or add a batch compaction job; consider partitioning by date to bound file counts per partition. Root cause: each file = metadata + open cost; 200k files means the query planner drowns before reading data. Verify: table details (file count/avg size) before/after.

Example 4 — Capacity throttling. Scenario: Month-end finance jobs get throttled; CU consumption spikes to 150% of F64 capacity for 6 hours. Solution: Short-term: stagger job schedules (smoothing borrows across time — spread the load), scale to F128 for the close week (PAYG F-SKUs can scale up/down), optimize the heaviest jobs (V-Order, partition pruning). Long-term: reserve capacity if the pattern is monthly (predictable → committed), or split interactive vs batch onto separate capacities (noisy-neighbor isolation). Monitor: capacity metrics app, throttling events per workload.

Example 5 — Governance for a regulated report. Scenario: A financial report needs certified data, full lineage, and PII masking for regional analysts. Solution: Endorsement: certify the gold dataset (promotion → certification workflow). Lineage: Purview/Fabric lineage view (source → bronze → silver → gold → report) as the audit artifact. PII: sensitivity labels + column masking / RLS in the warehouse and Power BI (regional analysts see only their region). Deployment pipeline: dev → test → prod workspaces so the report can't be edited in prod directly. The exam's governance answer is always: endorse + lineage + label + pipeline.

6. Standards and regulations cross-reference

• GDPR — erasure across OneLake (Delta DML deletes + VACUUM, shortcut sources, mirrored replicas), Purview data map for DSAR fulfillment, regional capacity placement for residency.
• DAMA-DMBOK — Purview/Fabric governance maps to DMBOK knowledge areas (the vocabulary for governance conversations).
• ISO/IEC 27001 — workspace roles + data-access roles (access control), audit logs (Fabric admin monitoring), CMEK (cryptography).
• PCI DSS v4.0.1 — tokenize PAN before OneLake (never land raw PAN in bronze), scope the cardholder data items, sensitivity labels + masking.
• SOC 2 — deployment pipelines (change control for analytics artifacts), access reviews, audit evidence from admin monitoring.
• EU AI Act — when Fabric feeds regulated AI: lineage + quality gates as the data-provenance artifacts.

7. Pitfalls, traps, and exam strategy

Trap patterns:

1. The DP-203 trap: answering with Synapse/Data Factory v1 patterns when Fabric has a native workload (Eventstream, mirroring, Direct Lake). Think Fabric-first.
2. The copy trap: copying data that could be a shortcut (duplication + staleness for no benefit).
3. The warehouse-vs-lakehouse trap: curated BI serving → warehouse; engineering/exploration → lakehouse. The SQL endpoint is not the warehouse.
4. The Direct Lake trap: Direct Lake needs Delta tables with V-Order in OneLake — plain Parquet or shortcuts without Delta won't qualify. Know the prerequisites.
5. The small-files trap: streaming into Delta without compaction = slow death. Always have a compaction story.
6. The capacity trap: F-SKU sizing from peak instead of p95 + smoothing; forgetting that throttling is deferred, not denied (smoothing borrows from the future).

Strategy: for each scenario, map requirements → Fabric workloads (the workload-choice questions are the bulk of the exam). Memorize: OneLake primitives (shortcuts vs mirroring vs copy), the engine for each latency (KQL/Eventhouse for real-time, warehouse + Direct Lake for BI, lakehouse for engineering), Delta Lake features (ACID, time travel, OPTIMIZE, VACUUM), medallion layers, and the security model (workspace roles → data-access roles → RLS/masking). The DP-700 exam rewards integration thinking: how the workloads compose over one copy of data.

8. Practice problems

P1. A team copies 5 TB daily from ADLS (owned by another team) into OneLake for reporting. The source updates continuously; reports are stale by a day. Improve it. Answer: Replace the copy with a shortcut (query in place — always fresh, zero copy, zero pipeline). If transformations are needed, create a Fabric view/materialized layer over the shortcut (silver/gold in OneLake referencing the shortcut bronze). Only copy if: the source throttles queries, cross-region latency is unacceptable, or you need point-in-time snapshots (then: incremental copy with timestamps).

P2. Finance needs sub-second Power BI on a 2 TB sales table refreshed hourly. Import mode fails (memory limits). What's the Fabric answer and its prerequisites? Answer: Direct Lake: Power BI reads the Delta table directly (no import, no refresh schedule). Prerequisites: table in OneLake as Delta with V-Order optimization, on a sufficient capacity (F64+ for 2 TB interactive), fallback... ensure the semantic model uses Direct Lake mode. If Direct Lake isn't available (capacity too small), fallback: DirectQuery against the warehouse (slower) or aggregated import (summary table).

P3. A streaming table accumulates 50k files/day. Queries are slow and OPTIMIZE can't keep up. Redesign. Answer: (1) Increase micro-batch interval (larger files per commit). (2) Partition by hour/date (bounds file counts per partition; queries prune). (3) Scheduled OPTIMIZE per partition (not whole table). (4) Consider a two-tier pattern: streaming bronze (many small files, short retention) → hourly batch compaction into silver (few large files, long retention). The lesson: streaming write patterns and analytic read patterns need different file layouts — medallion exists for this reason.

P4. Design GDPR erasure for a Fabric estate: bronze (Delta), silver (Delta), warehouse gold, Power BI dataset, mirrored SQL source. Answer: (1) Source: delete in the mirrored SQL DB (mirror propagates — verify lag). (2) Bronze/silver: Delta DELETE WHERE user_id (partition-pruned), then VACUUM to hard-delete (time travel retains data otherwise — set retention appropriately). (3) Warehouse: DML delete + verify. (4) Power BI: Direct Lake reflects the source automatically (no stale import); clear any caches. (5) Orchestrate via pipeline; log steps (audit). (6) Prevent recurrence: tokenize at ingestion (mapping table = the erasure point).

P5. Two teams share an F64 capacity; team A's month-end jobs starve team B's dashboards (throttling). Give three mitigations in order of effort. Answer: (1) Schedule isolation: move batch to off-hours (smoothing + no contention — cheapest). (2) Capacity split: separate capacities (or F128 for the batch team during close) — noisy-neighbor isolation. (3) Optimize: V-Order, partition pruning, and query tuning on the heavy jobs (reduce CU burn permanently). Monitor per-workload CU in the capacity metrics app to verify.

P6. A KQL database (Eventhouse) vs a lakehouse streaming table: when does each win for IoT telemetry at 1M events/sec? Answer: KQL/Eventhouse: sub-second ad-hoc analytics on raw high-volume telemetry (the engine is built for it — columnar, time-series optimized); keep hot data here (days–weeks). Lakehouse streaming table: when the data needs Spark ML/feature engineering or long retention with Delta governance; cheaper for warm/cold. The pattern: Eventhouse for hot real-time analytics, lakehouse for the curated/long-term story — many estates run both over the same Eventstream.

Original ETS reference writing, verified against public sources on 2026-09-30. Thin sourcing is flagged inline. Not legal advice — confirm current editions with the issuing body.