DP-700: Microsoft Fabric Data Engineer Associate Study Guide
The DP-700 exam certifies your ability to implement and manage data engineering solutions on Microsoft Fabric, spanning lakehouses, warehouses, Data Factory pipelines, Spark notebooks, and Real-Time Intelligence. It is aimed at data engineers who ingest, transform, and serve analytics data on OneLake, and who administer, secure, and optimize those solutions. Expect hands-on scenarios covering T-SQL, PySpark/Spark SQL, KQL, capacity management, and monitoring.
Reviewed Jul 2026.
Domain 1: Implement and manage an analytics solution
- OneLake is the single, unified, tenant-wide logical data lake for all Fabric data, often described as 'OneDrive for data'; lakehouse tables and warehouse data are automatically persisted as Delta Parquet in OneLake.
- OneLake shortcuts reference external or internal data in place without copying it, supporting targets such as ADLS Gen2, Amazon S3, Google Cloud Storage, Dataverse, S3-compatible endpoints, and other OneLake locations.
- An internal OneLake shortcut can expose warehouse Delta tables to a lakehouse or notebook (and vice versa) without duplicating the data.
- OneLake exposes ADLS Gen2-compatible APIs, so existing ADLS tools and drivers can read and write OneLake paths directly.
- Fabric capacities are purchased as F SKUs (F2, F4, F8, up to F2048), billed by capacity units (CUs) with pay-as-you-go or reserved pricing; bursting and smoothing absorb short spikes.
- A workspace maps to exactly one capacity, while a single capacity can host many workspaces; reassigning a workspace requires Workspace Admin plus rights on the target capacity.
- The Workspace Member role can view, create, edit, and delete items and add other users, meeting access-management needs without full Admin or owner control such as deleting the workspace itself.
- A new lakehouse automatically includes a SQL analytics endpoint and a default semantic model; the SQL endpoint supports read queries plus views and functions but not table DML or DDL writes.
- The Fabric warehouse is the T-SQL, fully transactional relational store built on OneLake, in contrast to the read-only lakehouse SQL endpoint.
- Direct Lake mode reads Delta Parquet directly from OneLake, combining import-like query speed with DirectQuery-like data freshness and no scheduled refresh.
- Domains are logical groupings of workspaces around a business area (for example Finance or Sales), supporting data mesh, delegated domain admins, and domain-scoped discovery and governance settings.
- Org-wide feature enablement lives in the Admin portal Tenant settings; scoping a setting to specific security groups enables controlled, phased feature rollout.
- Fabric Git integration supports Azure DevOps (Azure Repos) and GitHub; Commit pushes workspace changes to the connected branch and Update pulls incoming changes.
- Mirroring continuously replicates supported external databases (including Snowflake, Azure SQL Database, and Azure Cosmos DB) into OneLake as Delta tables, and 'Load to Tables' converts files into managed Delta tables with an inferred schema.
Domain 2: Ingest and transform data
- COPY INTO is the bulk loader for Fabric Warehouse, ingesting CSV and Parquet from ADLS Gen2 or Blob storage with support for column-list mapping to select and align source fields.
- In COPY INTO for CSV, FIRSTROW is a 1-based row number for the first row to load, so FIRSTROW = 2 skips a single header line rather than ingesting it as data.
- FIELDQUOTE identifies the text qualifier (default double quote) that wraps field values so delimiters inside a quoted value are treated as data, not separators.
- MAXERRORS sets how many row-level conversion failures are tolerated before the load aborts, and ERRORFILE specifies a folder path to persist rejected rows and an error-reason file.
- COPY INTO accepts the asterisk wildcard directly in the FROM storage path, so one statement can match many files (for example all files matching orders_2025*.parquet).
- For publicly readable storage, omit the CREDENTIAL clause and the load runs using anonymous or the caller's Entra identity as appropriate.
- COPY INTO performs no inline per-column transformation, so the standard pattern is to land data into a staging table, then run INSERT ... SELECT or CTAS applying CAST/CONVERT to shape the final schema.
- CTAS (CREATE TABLE AS SELECT) is the preferred, highly parallel bulk-transform mechanism, but it fails if the target table already exists.
- A single MERGE statement handles insert, update, and delete via its WHEN MATCHED and WHEN NOT MATCHED clauses, but errors if multiple source rows would act on the same target row, so dedupe the source first.
- ROW_NUMBER() partitioned by the business key and ordered by a timestamp lets you keep only one latest row per key when deduplicating a source.
- Fabric Warehouse supports multi-statement transactions with BEGIN, COMMIT, and ROLLBACK, making patterns such as delete-then-insert atomic with rollback safety.
- Cross-warehouse and warehouse-to-lakehouse queries within the same workspace use three-part names (database.schema.table) to join across SQL endpoints.
- Fabric auto-creates single-column statistics on demand, while manual control remains via UPDATE STATISTICS ... WITH FULLSCAN to refresh a named statistic across all rows.
- Stage typed data as strings first, normalize null tokens and clean values with SQL, keep statistics current, and TRUNCATE TABLE to quickly empty staging tables between loads.
Domain 3: Monitor and optimize an analytics solution
- The Monitoring hub lets you open a pipeline run detail, select the failed activity, and read its input, output, and error message directly so you can pinpoint the failure without re-running.
- The Monitoring hub provides filters for item type (for example Notebook), status (for example Failed), and time range, which combine to return exactly the runs you need without manual scanning.
- The Copy activity monitoring detail exposes rows read from the source and rows written to the sink, letting you reconcile record counts and detect filtering, truncation, or fault-tolerance skips.
- Expanding a ForEach activity in the pipeline run detail shows each iteration individually, so you can see which iteration failed within a loop.
- The Capacity Metrics app Compute page plots a time-series utilization (ribbon) chart of CU consumption against the capacity's CU limit, and peaks near or above 100 percent identify contention periods.
- In the Capacity Metrics app, timepoint detail ranks the operations contributing CU at a chosen moment and attributes usage to the originating workspace and item.
- Interactive CU (queries, report interactions) is smoothed over seconds to minutes, while background CU (refreshes, pipeline and notebook jobs) is smoothed over a longer window up to 24 hours, so background usage appears spread across time.
- Throttling escalates in stages: interactive delay first (adding latency when projected overload exceeds roughly 10 minutes), then interactive rejection, then background rejection; bursting plus smoothing delays the onset.
- Data Activator (Activator) monitors streaming or report data and triggers an action when a defined condition on an object's metric is met.
- In Activator, adding a sustained-duration requirement and using a becomes-true transition prevents brief flapping from firing the same alert repeatedly.
- Use Set alert on a Power BI visual to create a Data Activator rule with a condition and action; Activator handles data-quality condition alerts, while a pipeline failure notification handles operational failures.
- Semantic model Refresh history shows each attempt's status, timing, and detailed error, and Dataflow Gen2 refresh history exposes per-refresh status and individual query durations.
- The Spark UI stage and task view reveals per-task durations and shuffle read/write, exposing data skew where one task runs far longer than its peers.
- Notebook run history lists prior runs with their durations and statuses, making it easy to spot performance regressions across executions.
DP-700 exam tips
- Know the difference between the lakehouse SQL analytics endpoint (read-only, supports views and functions) and the Fabric warehouse (full T-SQL with transactional DML/DDL) - the exam tests when to use each.
- Memorize COPY INTO options precisely: FIRSTROW is 1-based (=2 skips one header), FIELDQUOTE sets the text qualifier, and MAXERRORS with ERRORFILE handles reject tolerance and logging.
- For deduplication and slowly changing data, expect ROW_NUMBER() partitioned by key plus MERGE; remember MERGE fails on duplicate source rows targeting the same row, so dedupe first.
- Understand CU smoothing and throttling order: background CU smooths over up to 24 hours, and throttling escalates interactive delay, then interactive rejection, then background rejection.
- Match the monitoring tool to the task - Monitoring hub for run and activity errors, Capacity Metrics app for CU and top consumers, Refresh history for semantic models and dataflows, and Data Activator for condition-based alerts.
Study guide FAQ
What is the difference between a Fabric lakehouse and a warehouse?
A lakehouse stores files and Delta tables and is queried through a read-only SQL analytics endpoint plus Spark notebooks, while a warehouse is a fully transactional T-SQL relational store on OneLake that supports DML, DDL, and multi-statement transactions. Both persist data as Delta Parquet in OneLake, and you can query across them within a workspace using three-part names.
When should I use OneLake shortcuts instead of copying data?
Use a shortcut when you want to reference data in place - whether it lives in another OneLake location, ADLS Gen2, Amazon S3, Google Cloud Storage, or Dataverse - so it is queryable without duplication. Shortcuts avoid storage and pipeline overhead and keep a single source of truth, which is ideal for medallion layers and cross-workspace sharing.
How does Fabric capacity throttling work?
Fabric smooths CU consumption over time - interactive operations over seconds to minutes and background jobs over up to 24 hours - and applies progressive throttling when a capacity borrows too far into the future. It begins with interactive delay, then interactive rejection, then background rejection, and the Capacity Metrics app helps you find the peaks and top-consuming operations.
What is Direct Lake mode and why does it matter?
Direct Lake mode lets a semantic model read Delta Parquet directly from OneLake, giving near import-level query speed without importing or scheduling refreshes, while reflecting fresh data like DirectQuery. It is a key reason lakehouse and warehouse data can power fast Power BI reports without extra copies.