DP-700 — Microsoft Fabric Data Engineer Associate Cheat Sheet

Last revised: September 16, 2026

Cheat sheet: DP-700 reference for Microsoft Fabric data engineering decisions, services, security, performance, and troubleshooting.

This Cheat Sheet is independent exam-prep support for candidates preparing for Microsoft Fabric Data Engineer Associate (DP-700). Use it to review service choices, implementation patterns, security boundaries, and common traps before practicing full scenarios.

Use the tables for a quick pre-exam check. Expand a topic’s notes for explanations, examples, and additional distinctions.

Scope and study context

The DP-700 exam is not just a syntax test. Expect scenario-style decisions about how to design, ingest, transform, secure, monitor, and optimize data engineering solutions in Microsoft Fabric. The strongest candidates can explain why a Fabric item or pattern is the right fit, not only what it is called.

This page supports IT Mastery practice with original practice questions. It is not affiliated with Microsoft.

After this Cheat Sheet, move into original practice questions in focused sets rather than immediately taking a full mock exam.

Drill	Goal
Fabric item selection	Build fast recognition of lakehouse, warehouse, pipeline, dataflow, notebook, shortcut
OneLake and Delta	Review tables/files, shortcuts, schema, optimization, medallion patterns
Ingestion	Practice full versus incremental loads, CDC/watermarks, copy activity, source constraints
Transformation	Compare Spark, SQL, and Dataflows Gen2 decisions
Security and governance	Separate workspace, item, SQL, model, and source permissions
Monitoring and troubleshooting	Diagnose failed runs, slow jobs, capacity issues, and stale data
End-to-end scenarios	Combine design, implementation, security, and operations in one case

Review method

For each missed question:

Write down the requirement you overlooked.
Identify the Fabric item or feature the question was really testing.
Note the wrong answer pattern that tempted you.
Re-answer a similar topic drill before moving on.
Read the detailed explanations, including why the distractors are wrong.

The goal is not memorizing answer letters. The goal is building a repeatable decision process for DP-700 scenarios.

DP-700 scope snapshot

DP-700 expects practical Microsoft Fabric data engineering judgment: choosing the right Fabric item, building ingestion and transformation flows, managing data in OneLake, securing analytics assets, and monitoring or optimizing workloads.

Area	What to be ready to do	High-yield exam angle
Analytics solution implementation	Workspaces, lakehouses, warehouses, semantic models, OneLake, domains, deployment patterns	Know which Fabric item owns data, compute, security, and serving behavior
Data ingestion and transformation	Pipelines, Dataflows Gen2, notebooks, Spark jobs, SQL transformations, incremental loads	Choose orchestration vs transformation tools correctly
Data management	Delta tables, shortcuts, medallion architecture, schemas, files, tables, refresh patterns	Know when data is copied, virtualized, mirrored, or transformed
Security and governance	Microsoft Entra ID, workspace roles, item permissions, SQL permissions, RLS, sensitivity labels, lineage	Distinguish coarse access, data-level access, and report-level restrictions
Monitoring and optimization	Pipeline runs, Spark jobs, SQL queries, Capacity Metrics, refresh failures, small files, partitioning	Diagnose symptoms before scaling capacity

Microsoft Fabric mental model

Core hierarchy and terms

Concept	Exam-ready meaning	Common trap
Tenant	Organization-level Microsoft Fabric environment	Tenant settings can enable, disable, or constrain features independently of workspace permissions
Capacity	Compute resource backing Fabric workloads	Slow workloads may be code, data layout, concurrency, or capacity pressure; do not assume one cause
Workspace	Collaboration and security boundary for Fabric items	Workspace roles are broad; use item/data permissions for finer control
Item	Fabric artifact such as lakehouse, warehouse, pipeline, notebook, dataflow, semantic model	Deploying an item usually does not deploy the underlying data
OneLake	Unified SaaS data lake for Fabric	OneLake is storage; Fabric items provide experiences and compute over it
Lakehouse	Delta-based data lake item for files, tables, Spark, and SQL analytics endpoint	SQL analytics endpoint is primarily for querying lakehouse tables, not full SQL-first data warehousing
Warehouse	SQL-first relational warehouse item in Fabric	Choose for T-SQL engineering and relational serving, not arbitrary raw file processing
Data pipeline	Orchestration item for movement and control flow	Pipelines coordinate work; they are not the best place for complex row-by-row transformations
Dataflow Gen2	Low-code Power Query-based ingestion and transformation	Good for connector-rich shaping; less ideal for complex code-first engineering
Notebook	Code-first Spark development item	Interactive state can hide missing setup; scheduled runs must be self-contained
Spark job definition	Production-style Spark job execution	Use when repeatable Spark execution matters more than notebook interactivity
Shortcut	OneLake reference to supported external or internal data	Shortcuts virtualize data; they do not automatically transform, cleanse, or copy it
Mirroring	Replication from supported operational sources into Fabric	Different from shortcuts: mirrored data is replicated for analytics scenarios
Semantic model	BI model used by Power BI/Fabric reports	RLS in a semantic model does not automatically secure raw lakehouse or warehouse access

Notes and examples

Fabric item selection matrix

Requirement	Prefer	Why	Avoid / watch for
Store raw files and Delta tables, transform with Spark	Lakehouse	File + table experience, notebooks, Delta, medallion-friendly	Do not expect full SQL warehouse behavior through the lakehouse SQL endpoint
SQL-first dimensional warehouse	Warehouse	T-SQL DDL/DML, relational schemas, SQL serving	Do not use it as a generic file landing zone
Low-code ingestion and transformations	Dataflow Gen2	Power Query, many connectors, data destinations	Complex orchestration belongs in pipelines
Copy data, run activities in order, branch, loop, schedule	Data pipeline	Control flow, copy activity, parameters, monitoring	Avoid embedding complex business logic directly in pipeline expressions
Code-first large-scale transformation	Notebook or Spark job definition	PySpark/SQL/Scala-style transformations and custom libraries	Do not rely on interactive notebook state in production
Near-real-time replication from supported databases	Mirroring	Reduces custom ingestion for supported sources	Not a substitute for arbitrary transformations or unsupported sources
Query operational telemetry or event streams	Eventstream/Eventhouse, when scenario requires Real-Time Intelligence	KQL/event-first analytics	Not the default choice for batch lakehouse/warehouse engineering
Avoid copying data from supported external storage	OneLake shortcut	Virtualized access from OneLake namespace	Source permissions, latency, and write support still matter
Serve Power BI with minimal import refresh over Fabric Delta tables	Direct Lake semantic model	Reads data from OneLake in supported scenarios	Model-level security is not raw data security

DP-700 mental model

Think in layers. Most exam scenarios can be solved by identifying the layer being tested.

Layer	What to recognize quickly	Common exam angle
Workspace and capacity	Workspaces contain Fabric items; capacity affects performance and throttling	Choose workspace roles, deployment approach, monitoring location
OneLake storage	Unified storage layer; lakehouse tables use open Delta/Parquet patterns	Avoid unnecessary copies, use shortcuts, manage files and tables
Ingestion	Copy activity, Dataflows Gen2, pipelines, mirroring, shortcuts	Choose between low-code, orchestration, bulk copy, or no-copy access
Transformation	Spark notebooks, Spark SQL, Dataflows Gen2, Warehouse T-SQL	Match transformation complexity to the right engine
Serving	Lakehouse SQL analytics endpoint, Warehouse, semantic model, Direct Lake patterns	Decide how data should be queried or consumed
Security and governance	Workspace roles, item permissions, SQL permissions, labels, lineage, Git/deployment	Separate access control, collaboration, and deployment concerns
Operations	Monitoring hub, run history, Spark UI/logs, capacity metrics, query diagnostics	Troubleshoot failures, optimize performance, control cost/capacity pressure

Lakehouse, warehouse, and SQL endpoint distinctions

Feature / decision	Lakehouse	Lakehouse SQL analytics endpoint	Warehouse
Primary persona	Data engineer / Spark engineer	SQL consumer over lakehouse tables	SQL data engineer / analyst
Storage	Files and Delta tables in OneLake	Queries Delta tables exposed by lakehouse	Relational warehouse data in OneLake-backed storage
Write path	Spark, Dataflows, pipelines, lakehouse UI	Generally read-oriented for lakehouse data	T-SQL DDL/DML and ELT
Best for	Bronze/silver/gold Delta, raw files, Spark transformations	BI/query access to curated lakehouse tables	Dimensional models, SQL transformations, SQL serving
Table organization	Tables area plus Files area	Exposes registered lakehouse tables	Schemas, tables, views, procedures as supported
Common exam cue	“Need raw files, notebooks, Delta, medallion”	“Need SQL access to lakehouse tables”	“Need T-SQL warehouse and relational modeling”
Common trap	Files are not automatically queryable as tables	Do not use it as the write engine for lakehouse tables	Do not treat it like Spark for semi-structured raw files

Notes and examples

Files vs tables in a lakehouse

Location	Use for	Exam note
Files	Raw or semi-structured files, landing zones, archives, unregistered data	Good for bronze landing, but not automatically a managed query table
Tables	Delta tables registered for Spark and SQL analytics	Use for curated data that downstream SQL/BI tools should query
Shortcuts	Referenced data from another OneLake location or supported external storage	Useful for data sharing and avoiding copies; still plan security and performance

Data architecture patterns

Medallion architecture reference

    flowchart LR
	    A[Sources] --> B[Bronze<br/>Raw landing]
	    B --> C[Silver<br/>Cleaned and conformed]
	    C --> D[Gold<br/>Business-ready model]
	    D --> E[Warehouse / Semantic model / Reports]

Notes and examples

Layer	Purpose	Typical Fabric implementation	Quality expectations
Bronze	Preserve source data with minimal changes	Lakehouse Files or Delta tables; pipeline copy; shortcuts; mirroring output	Traceability, ingestion metadata, no heavy business logic
Silver	Clean, standardize, deduplicate, conform	Spark notebooks/jobs, Dataflows Gen2, Delta MERGE	Data types, keys, deduplication, valid records
Gold	Business-ready facts/dimensions or aggregates	Lakehouse Delta tables or Warehouse tables	Star schema, semantic names, performance-ready
Serving	SQL/BI/ML consumption	Warehouse, SQL analytics endpoint, semantic model, Direct Lake	Security, relationships, measures, query performance

Pattern selection

Scenario	Recommended pattern
Multiple raw source systems with different formats	Land to bronze first, then standardize in silver
Re-runnable daily loads	Use pipeline parameters, watermarks, idempotent writes, and MERGE
BI model over curated Fabric tables	Gold Delta or warehouse tables plus semantic model
SQL team owns transformations	Warehouse with T-SQL ELT
Spark team owns transformations	Lakehouse with notebooks or Spark job definitions
Data must remain in external supported storage	Shortcut if virtualization is acceptable; copy if isolation/performance/history is needed

Ingestion and orchestration quick reference

Ingestion method decision table

Need	Choose	Why	Watch for
Scheduled copy from source to OneLake/warehouse	Data pipeline Copy activity	Operational control, monitoring, retries, parameters	Schema drift, credentials, gateway, incremental logic
Low-code source shaping	Dataflow Gen2	Power Query transformations and destinations	Refresh duration, folding behavior, complex logic
Complex file parsing or custom transformation	Notebook/Spark job	Code-level control and distributed processing	Dependency management and reproducibility
Avoid moving supported external data	Shortcut	Reduces duplication	Source availability, security, performance, unsupported write patterns
Replicate supported operational DB data	Mirroring	Simplifies near-real-time analytics ingestion	Confirm source support and downstream modeling approach
On-premises or private network source	Pipeline/dataflow with gateway or supported private connectivity	Secure access to non-public data	Credential scope and gateway health
Event telemetry	Eventstream/Eventhouse if real-time scenario	Stream-first processing	Do not force event tooling for simple batch ingestion

Notes and examples

Pipeline design checklist

Design concern	DP-700-ready approach
Parameters	Parameterize source path, target path, dates, environment, and load mode
Re-runs	Make activities idempotent; avoid duplicate inserts
Incremental loads	Use watermark columns, change tracking/CDC where available, or source-specific modified timestamps
Dependencies	Use activities for sequence, conditions, loops, and failure paths
Secrets	Store credentials in Fabric connections or approved secret mechanisms; do not hard-code
Observability	Capture run IDs, row counts, source extract time, and failure messages
Recovery	Use retry policies where appropriate, but fix non-transient data issues explicitly
Environment movement	Use deployment rules, parameters, or separate connections for dev/test/prod

Example pipeline expression pattern:

@concat('raw/orders/load_date=', formatDateTime(pipeline().parameters.LoadDate, 'yyyy-MM-dd'))

Incremental load reference

Technique	Use when	Implementation idea	Trap
Watermark	Source has reliable modified timestamp or increasing key	Store last successful watermark; extract rows greater than it	Late-arriving updates can be missed if watermark is advanced too early
Full reload	Small dimension or unstable source	Replace target or rebuild curated table	Expensive and risky for large facts
Append-only	Source only inserts immutable events	Append new records and partition by ingestion/event date	Duplicates require deduplication keys
Upsert/MERGE	Records can change	Match on business key or hash; update/insert target	Missing deletes unless source provides delete indicators
Snapshot comparison	Need detect changes without CDC	Compare current snapshot to previous snapshot	More compute and storage

Transformation reference

Transformation tool selection

Requirement	Best fit	Reason
Complex Python/PySpark logic	Notebook or Spark job definition	Full code control and scalable processing
SQL ELT and dimensional modeling	Warehouse	T-SQL-first development
Low-code shaping and connector transforms	Dataflow Gen2	Power Query experience
Orchestrate several transformations	Data pipeline	Sequence notebooks, dataflows, stored procedures, copy steps
Reusable production Spark execution	Spark job definition	Repeatable, less interactive than notebooks
Ad hoc exploration	Notebook	Interactive development, visualization, quick testing

Notes and examples

PySpark Delta patterns

Write a bronze table from raw files:

df = (
    spark.read
    .option("header", "true")
    .csv("Files/raw/orders/")
)

(
    df.write
    .format("delta")
    .mode("append")
    .saveAsTable("bronze_orders")
)

Deduplicate and write a silver table:

from pyspark.sql.functions import col, row_number
from pyspark.sql.window import Window

w = Window.partitionBy("OrderId").orderBy(col("ModifiedDate").desc())

silver = (
    spark.table("bronze_orders")
    .withColumn("rn", row_number().over(w))
    .filter(col("rn") == 1)
    .drop("rn")
)

(
    silver.write
    .format("delta")
    .mode("overwrite")
    .option("overwriteSchema", "true")
    .saveAsTable("silver_orders")
)

Upsert with Delta MERGE:

from delta.tables import DeltaTable

updates = spark.table("staging_customer_updates")
target = DeltaTable.forName(spark, "silver_customer")

(
    target.alias("t")
    .merge(updates.alias("s"), "t.CustomerId = s.CustomerId")
    .whenMatchedUpdateAll()
    .whenNotMatchedInsertAll()
    .execute()
)

T-SQL warehouse patterns

Use T-SQL when the scenario is SQL-first and the target is a Fabric Warehouse.

CREATE TABLE dbo.DimCustomer
(
    CustomerKey INT NOT NULL,
    CustomerId VARCHAR(50) NOT NULL,
    CustomerName VARCHAR(200) NULL,
    IsCurrent BIT NOT NULL
);

MERGE dbo.DimCustomer AS target
USING dbo.StageCustomer AS source
    ON target.CustomerId = source.CustomerId
WHEN MATCHED THEN
    UPDATE SET
        CustomerName = source.CustomerName,
        IsCurrent = 1
WHEN NOT MATCHED THEN
    INSERT (CustomerKey, CustomerId, CustomerName, IsCurrent)
    VALUES (source.CustomerKey, source.CustomerId, source.CustomerName, 1);

Exam trap: use Warehouse T-SQL for warehouse tables. Use Spark or supported lakehouse operations for lakehouse Delta table writes; do not assume every T-SQL DML pattern applies to the lakehouse SQL analytics endpoint.

Transformation tool selection

Need	Better fit	Watch for
Simple column selection, filtering, type changes	Dataflow Gen2	Query folding and source limitations
Reusable low-code data preparation	Dataflow Gen2	Destination settings and refresh behavior
Complex business rules at scale	Notebook	Spark performance, partitioning, shuffle, code quality
SQL warehouse transformations	Warehouse T-SQL	Do not apply SQL Server tuning assumptions blindly
Delta upsert into lakehouse table	Spark SQL/PySpark MERGE pattern	Correct keys and deduplication before merge
Orchestrate several transformations	Pipeline	Pipeline coordinates; heavy work should run in the right engine
Data quality checks	Notebook, SQL, or dataflow depending on design	Fail fast and log rejected records when required

Common transformation mistakes

Mistake	Better approach
Doing every transformation in a pipeline	Use pipelines for orchestration and call notebooks, dataflows, or SQL as needed
Using full overwrite when only a few rows changed	Use incremental load and merge/upsert
Partitioning by a unique ID	Partition by columns commonly used for pruning, often date or region-like columns
Ignoring duplicate keys before MERGE	Deduplicate and define deterministic conflict rules
Letting schema drift silently break downstream models	Validate schema and handle expected changes explicitly
Optimizing only compute but ignoring file layout	Optimize table layout, file sizes, and filters

Modeling and serving data

Dimensional modeling quick reference

Object	Purpose	Fabric implementation	Exam tip
Fact table	Numeric events or transactions	Gold lakehouse table or warehouse table	Keep grain explicit
Dimension table	Descriptive context	Gold lakehouse table or warehouse table	Use stable keys and business-friendly attributes
Degenerate dimension	Identifier stored in fact, such as order number	Fact column	Avoid unnecessary dimension table if no attributes
Slowly changing dimension Type 1	Overwrite old attributes	MERGE update	Use for corrections where history is not needed
Slowly changing dimension Type 2	Preserve history	Add effective dates/current flag/surrogate keys	Requires careful joins and current-row filters
Aggregate table	Precomputed summary	Gold table or warehouse table	Use for performance when detail is too large for repeated queries
Semantic model measure	Business calculation	Power BI/Fabric semantic model	Prefer measures for reusable business logic

Notes and examples

Serving option decision table

Consumer need	Choose	Why
BI over Fabric Delta tables with minimal refresh movement	Direct Lake semantic model	Uses OneLake-backed tables in supported scenarios
SQL analysts querying curated lakehouse data	Lakehouse SQL analytics endpoint	Familiar SQL query surface over lakehouse tables
SQL analysts building warehouse-style reports	Warehouse	Full SQL-first serving pattern
Reports need curated relationships, measures, RLS	Semantic model	Central BI model and security layer
Data scientists need feature data	Lakehouse tables/files	Spark-friendly access
External tools need SQL endpoint	Warehouse or lakehouse SQL analytics endpoint	Choose based on write/modeling needs

Direct Lake, Import, and DirectQuery

Mode	Choose when	Watch for
Direct Lake	Data is in supported Fabric/OneLake tables and you want low-latency BI without import refresh copies	Model design, permissions, and fallback behavior matter
Import	Need cached model performance, transformations, or sources not suitable for Direct Lake	Requires refresh planning
DirectQuery	Need live query passthrough to supported source	Source performance and query folding are critical

Security, permissions, and governance

Security layers

Layer	Controls	Use for	Common trap
Microsoft Entra ID	Users, groups, service principals	Identity foundation	Prefer groups over individual assignments
Tenant settings	Fabric feature availability and governance	Organization-wide controls	Workspace admins cannot override disabled tenant features
Capacity permissions	Who can use/administer capacity	Resource governance	Capacity access is not the same as data access
Workspace roles	Admin, Member, Contributor, Viewer-style collaboration	Broad item access and authoring	Too coarse for sensitive data segmentation
Item permissions	Sharing and access to specific Fabric items	Least-privilege item sharing	Item access may still require underlying data permissions
OneLake/data access controls	Folder/table/data access where supported	Granular lakehouse data control	Do not rely only on semantic model RLS for raw data protection
SQL permissions	GRANT/DENY-style database access where supported	Warehouse and SQL endpoint access	SQL access path can bypass report-only restrictions
Semantic model security	RLS/OLS-style BI restrictions	Report and model consumers	Does not automatically secure lakehouse files or warehouse tables
Sensitivity labels	Classification and protection metadata	Governance and compliance workflows	Labels identify/protect; they do not replace authorization design

Notes and examples

Workspace role exam cues

Cue	Likely answer
User must manage workspace settings and permissions	Workspace Admin role or delegated admin pattern
User must create and edit Fabric items	Contributor/Member-style access, depending on governance needs
User must only consume reports/data	Viewer or item-level sharing plus data permissions
External or app identity runs ingestion	Use supported service principal/workspace identity/connection pattern; avoid personal credentials
Need least privilege for one dataset/table	Use item/data/SQL permissions rather than broad workspace admin

Governance checklist

Use Microsoft Entra groups for repeatable access assignments.
Separate development, test, and production workspaces.
Use sensitivity labels and endorsement for discoverability and trust.
Review lineage to understand downstream impact before schema changes.
Use domains or workspace organization patterns when many teams share Fabric.
Store credentials in Fabric connections or approved secret stores.
Validate that shortcuts and mirrored data inherit or enforce the intended access path.
Remember that report security and raw data security are separate design concerns.

Access control layers

Layer	What it controls	Candidate trap
Workspace roles	Collaboration and broad access within a workspace	Workspace access is not the same as row-level data security
Item permissions	Access to specific Fabric items	Sharing an item may not grant every downstream data permission
SQL permissions	Database/warehouse object access	SQL permissions can differ from workspace collaboration roles
Semantic model security	RLS/OLS-style report consumption controls	Model security does not automatically secure raw lake files
Source permissions	Access to shortcut or external data source	A shortcut does not magically bypass source governance
Credentials/connections	How Fabric authenticates to sources	Do not embed secrets in notebooks or hard-code credentials

Governance concepts to review

Concept	Why it matters
Lineage	Understand upstream/downstream impact before changing tables, pipelines, or models
Sensitivity labels	Communicate and enforce data classification expectations
Endorsement/certification of assets	Helps users identify trusted assets
Git integration	Version control for supported Fabric items
Deployment pipelines	Promote content across dev/test/prod-style stages
Parameters and environment-specific settings	Avoid hard-coding workspace IDs, connection details, or paths
Least privilege	Grant only the access required for the user, service, or process

Security decision traps

Do not solve row-level restrictions by only assigning a Viewer workspace role.
Do not use broad workspace Admin access for routine pipeline execution.
Do not assume a user who can see a report should also access the raw lakehouse.
Do not hard-code credentials in notebooks or scripts.
Do not forget downstream access when sharing a report, SQL endpoint, or semantic model.

DevOps and lifecycle management

Capability	Use for	Exam note
Git integration	Versioning supported Fabric item definitions	Data is not versioned by Git integration
Deployment pipelines	Promote items across dev/test/prod	Configure environment-specific connections, parameters, and rules
Workspace separation	Isolate lifecycle stages	Avoid developing directly in production
Parameters	Change paths, dates, connection names, schemas	Critical for reusable pipelines and notebooks
Fabric environments	Manage Spark libraries/settings where supported	Helps avoid “works in my notebook” dependency issues
Lineage view	Impact analysis	Use before modifying shared tables or semantic models
Monitoring hub	Central run status visibility	Useful for operational troubleshooting

Common lifecycle traps:

Deployment moves supported item metadata, not all data.
Hard-coded lakehouse IDs, paths, or connection names break promotion.
Personal credentials can fail when the owner leaves or permissions change.
Notebook cell execution order can hide missing initialization.
Schema changes must be coordinated with SQL endpoints, semantic models, and reports.

Performance and optimization

Delta and lakehouse optimization

Symptom	Likely issue	Fix pattern
Slow scans over many tiny files	Small-file problem	Compact/optimize Delta tables; batch writes appropriately
Queries scan too much data	Poor partition/filter design	Partition selectively; filter early; avoid over-partitioning
BI slow on raw tables	Raw layout not serving-friendly	Build gold tables or warehouse model
Duplicate rows after retry	Non-idempotent append	Use load IDs, deduplication, and MERGE
Schema mismatch failures	Source drift or incorrect inference	Define schemas explicitly for critical pipelines
Spark job slow shuffle	Large joins/grouping	Repartition carefully, reduce columns, filter early, consider broadcast for small dimensions
Lakehouse table not visible to SQL	Data written only as files or unregistered Delta	Save/register as a table in the lakehouse Tables area
High latency from shortcut source	Remote read/source bottleneck	Copy or mirror data when performance/isolation matters

Notes and examples

Warehouse and SQL optimization

Area	Practical guidance
Data model	Prefer star schema for BI; avoid wide, ambiguous, highly normalized serving layers
Query shape	Select only needed columns, filter early, avoid unnecessary cross joins
ELT	Stage data, validate row counts, then merge/insert into curated tables
Statistics/metadata	Keep metadata current where supported by the engine
Concurrency	Monitor workload patterns before changing architecture
Capacity	Use Capacity Metrics to distinguish inefficient query design from resource pressure

Spark optimization quick checks

Check	Why it matters
Avoid reading entire bronze for small incremental updates	Reduces scan and shuffle
Persist/cache only when reused	Caching everything wastes memory
Control partition count after large shuffles	Too many or too few partitions hurts performance
Use explicit schemas for recurring files	Avoid expensive inference and inconsistent types
Use column pruning	Reading fewer columns reduces I/O
Use predicate pushdown-friendly filters	Helps Delta/Parquet skip data
Clean up old files carefully	Vacuum/retention choices affect rollback and time travel expectations

What to know

OneLake is the storage foundation for Fabric. Lakehouses organize data for data engineering workloads and expose data through both file/table structures and SQL query surfaces.

Concept	Review point	Trap
Files area	Good for raw or unstructured files	Files are not automatically the same as managed queryable tables
Tables area	Delta tables used for structured analytics	Table metadata and format matter; random files do not equal a governed table
Delta Lake	Transaction log, ACID-style table operations, schema handling, time travel concepts	Treating Delta as “just Parquet files” misses transaction and metadata behavior
Shortcuts	Logical references to data stored elsewhere	Shortcuts reduce copying but do not remove the need to understand permissions and source behavior
Medallion pattern	Bronze raw, silver cleaned, gold curated	It is an architecture pattern, not a substitute for clear security, quality, and lifecycle rules
Schema evolution	Controlled handling of changing columns/types	Blind schema drift can break downstream tables, reports, or queries
Small files	Too many tiny files hurt query performance	Compact/optimize instead of only adding more partitions
Partitioning	Helps when queries filter by partition columns	Over-partitioning high-cardinality columns can make performance worse

Bronze, silver, gold review

Layer	Purpose	Typical operations
Bronze	Preserve source-like data	Copy/load, append, basic metadata capture, source audit columns
Silver	Clean and standardize	Type conversion, deduplication, null handling, conforming names, CDC application
Gold	Serve business-ready analytics	Aggregation, dimensional modeling, star-schema-style tables, reporting-ready facts/dimensions

Common mistake: candidates choose a gold-layer serving pattern for raw ingestion requirements. Read whether the scenario asks for landing, cleansing, conforming, or serving.

Monitoring and troubleshooting

Where to look first

Workload	Primary places to check	What to inspect
Data pipeline	Run history, activity output, Monitor hub	Failed activity, error text, rows copied, duration, retry behavior
Dataflow Gen2	Refresh/run details	Connector errors, transformation step, destination write failure
Notebook/Spark job	Spark application details, driver/executor logs, notebook output	Failed cell, dependency error, skew, shuffle, memory pressure
Warehouse SQL	Query history/insights where available	Long-running query, blocking, inefficient joins, data volume
Semantic model	Refresh history, model settings, lineage	Source permission, Direct Lake behavior, schema changes
Capacity-wide issue	Capacity Metrics	Throttling, overload, high concurrency, noisy workloads
Security issue	Workspace/item/data permissions, SQL grants, Entra groups	Missing group membership or mismatched access path

Notes and examples

Troubleshooting decision table

Symptom	First question	Likely resolution
Pipeline succeeds but target has duplicates	Is the load idempotent?	Add keys, watermark, deduplication, or MERGE logic
Pipeline cannot reach source	Is source cloud, on-prem, private, or credential-restricted?	Configure supported gateway/private connectivity and credentials
Notebook runs manually but fails on schedule	Does it initialize everything?	Attach correct lakehouse, set parameters, install dependencies, avoid hidden state
SQL endpoint does not show new lakehouse data	Was data saved as a registered Delta table?	Write with saveAsTable or register table correctly
Report user sees denied data	Which layer denies access?	Check semantic model, item, SQL, and OneLake permissions separately
Direct Lake model behaves unexpectedly	Is the table/model mode supported and permissions valid?	Validate source tables, model design, and fallback/refresh settings
Spark job is slow only on large days	Is data skewed or partitioned poorly?	Inspect key distribution, filter early, repartition selectively
Warehouse query slows after schema/load change	Did data volume or query plan change?	Review query shape, table design, statistics/metadata, and capacity pressure
Shortcut data is unavailable	Is the external source accessible and authorized?	Check source credentials, network, and shortcut target
Dev deployment works but prod fails	Are environment-specific values hard-coded?	Use parameters, deployment rules, and prod connections

Where to look first

Problem	First checks
Pipeline failed	Run history, failed activity output, linked connection, parameters, source schema, permissions
Copy activity slow	Source throughput, network/gateway constraints, partitioning, file count, parallelism settings
Dataflow refresh failed	Step error, credentials, schema changes, query folding, destination configuration
Notebook failed	Spark logs, cell output, package/library issues, permissions, table path, schema conflict
Warehouse query slow	Query shape, filters, joins, data volume, table design, monitoring/query diagnostics
Capacity throttling or delays	Capacity metrics, concurrency, background jobs, refresh schedules
Users cannot access data	Workspace role, item permission, SQL permission, source/shortcut permission, semantic model permissions
Report/semantic model stale	Upstream pipeline status, refresh history, Direct Lake/semantic model configuration, table update timing

Optimization levers

Goal	Practical levers
Read less data	Select only required columns, filter early, use partition pruning
Move less data	Use incremental loads, shortcuts, and staging only when needed
Write better data	Use Delta tables, appropriate file sizes, compaction/optimization patterns
Reduce Spark cost	Avoid unnecessary shuffles, handle skew, cache selectively
Improve SQL serving	Model for common queries, avoid SELECT *, reduce joins where practical
Reduce failures	Add validation, retries where appropriate, idempotent loads, clear audit logs
Control capacity pressure	Stagger schedules, manage concurrency, monitor capacity usage

Delta table maintenance concepts

Concept	Purpose	Trap
Optimize/compaction	Reduce small-file overhead	Not a substitute for good ingestion design
V-Order-style optimization	Improve read performance for analytics workloads	Helps reads but does not fix incorrect logic
Vacuum	Remove old files no longer needed by retention rules	Can affect time travel/history expectations
Schema enforcement	Prevent unexpected incompatible writes	May require planned schema evolution
Time travel/history	Useful for audit and recovery scenarios	Retention and cleanup policies matter

High-yield DP-700 distinctions

Distinction	Remember
Pipeline vs notebook	Pipeline orchestrates; notebook transforms with code
Dataflow Gen2 vs pipeline	Dataflow transforms low-code; pipeline controls workflow and movement
Shortcut vs copy	Shortcut references data; copy creates a new physical copy
Shortcut vs mirroring	Shortcut virtualizes supported data; mirroring replicates supported operational data
Lakehouse vs warehouse	Lakehouse is Spark/Delta/file-friendly; warehouse is SQL-first
Lakehouse SQL endpoint vs warehouse	SQL endpoint queries lakehouse tables; warehouse is the SQL engineering store
Semantic model RLS vs data security	RLS restricts model/report queries, not necessarily direct raw data access
Git/deployment vs backup	Git/deployment handles item definitions; it is not a data backup strategy
Scaling capacity vs optimizing workload	Optimize data layout and queries before assuming more capacity is the right answer
Bronze vs gold	Bronze preserves raw history; gold is business-ready and serving-oriented

Exam scenario playbook

If the scenario says…	Think…
“Business analysts need SQL access to curated tables”	Warehouse or lakehouse SQL analytics endpoint depending on write/model ownership
“Data engineers need to process JSON/CSV at scale”	Lakehouse + Spark notebook/job
“Need a scheduled daily copy with parameters”	Data pipeline
“Need low-code transformations using Power Query”	Dataflow Gen2
“Need avoid duplicate rows during retry”	Idempotent design, keys, MERGE, load audit
“Need avoid copying external data”	Shortcut, if supported and performance/security are acceptable
“Need near-real-time replicated operational data”	Mirroring, if source is supported
“Need promote solution from dev to prod”	Deployment pipelines/Git + parameters/connections
“Need restrict report rows by user”	Semantic model RLS, plus underlying data permissions if users can access raw data
“Need diagnose slow workloads across many Fabric items”	Capacity Metrics first, then item-specific logs

Last-minute checklist

Can you explain when to choose lakehouse, warehouse, pipeline, dataflow, notebook, shortcut, and mirroring?
Can you describe bronze, silver, and gold responsibilities without mixing raw and serving layers?
Can you design an incremental load that survives retries?
Can you identify which permission layer controls a failed access scenario?
Can you distinguish semantic model security from OneLake/SQL data security?
Can you troubleshoot a failed pipeline, notebook, SQL query, or refresh from logs?
Can you name practical fixes for small files, poor partitioning, schema drift, and duplicate loads?
Can you promote Fabric items across environments without hard-coded dev values?

High-yield Fabric item decisions

Requirement in the scenario	Usually points to	Why
Store raw, curated, and analytics-ready data in open formats	Lakehouse	Best fit for Delta tables, Spark processing, medallion architecture
Build a relational data warehouse with T-SQL transformations and SQL serving	Warehouse	Strong fit for SQL-centric data engineering and BI serving
Orchestrate multiple steps with dependencies, parameters, retries, and schedules	Data pipeline	Pipelines coordinate work; they are not usually the heavy transformation engine
Perform low-code shaping, cleansing, and Power Query transformations	Dataflow Gen2	Good for analysts/data engineers who need repeatable low-code ETL
Perform complex code-based transforms, custom logic, ML-adjacent preparation, or Spark-scale processing	Notebook	Gives PySpark, Spark SQL, and code-level control
Access existing data without copying it	Shortcut	Logical access to data; useful when duplication is not required
Incrementally ingest changed records from a source	Pipeline plus watermark/CDC logic, often followed by MERGE	Avoid repeated full loads when only changes are needed
Replicate supported operational data into Fabric for analytics with minimal ETL	Mirroring	Useful when the source and latency requirements match the feature
Serve curated SQL tables to reporting users	Warehouse or Lakehouse SQL endpoint, depending on write/query needs	SQL endpoint is useful for querying lakehouse tables; Warehouse is better for SQL DML/warehouse design

Notes and examples

Fast elimination rule

If the question says:

Wording	Think first
“Schedule,” “retry,” “dependency,” “parameterize activities”	Pipeline
“Low-code,” “Power Query,” “combine and clean data visually”	Dataflow Gen2
“PySpark,” “custom library,” “large-scale transformation”	Notebook
“T-SQL warehouse,” “stored procedure,” “SQL DML”	Warehouse
“No data duplication,” “use existing data in place”	Shortcut
“Upsert,” “changed rows,” “incremental load”	Watermark/CDC plus MERGE
“Files are visible but not queryable as tables”	Register/create Delta tables or place data correctly as tables
“Users can open workspace but should not see all rows”	Workspace role alone is not enough; use item/SQL/model-level security as appropriate

Lakehouse versus Warehouse

Decision point	Lakehouse	Warehouse
Main strength	Open data engineering with Delta and Spark	SQL-centric relational data warehousing
Transformation style	Spark notebooks, Spark SQL, Dataflows Gen2, pipelines writing to lakehouse	T-SQL, SQL objects, stored procedures, warehouse modeling
Best for	Medallion architecture, open data lake patterns, mixed file/table workloads	Curated relational warehouse, SQL users, dimensional reporting
Query surface	SQL analytics endpoint for lakehouse tables	Warehouse SQL endpoint with stronger SQL DML orientation
Write expectation	Often write through Spark, pipelines, or dataflows	Write and transform with T-SQL patterns
Exam trap	Assuming the lakehouse SQL endpoint is the same as a full warehouse write engine	Using a warehouse when the requirement is open lake storage and Spark processing

A practical rule: if the requirement emphasizes Delta tables, notebooks, open files, and Spark, think Lakehouse. If it emphasizes T-SQL transformations, relational warehouse objects, and SQL-first serving, think Warehouse.

Ingestion patterns

Choose the right ingestion method

Scenario	Strong option	Why
Move data from a source into Fabric on a schedule	Pipeline with Copy activity	Built for orchestrated movement
Clean and reshape data with low-code transformations	Dataflow Gen2	Power Query-style data preparation
Ingest only new or changed rows	Pipeline with parameters/watermarks, CDC if available, then merge/upsert	Reduces load volume and avoids full reloads
Access data already stored in a supported external lake	Shortcut	Avoids duplicate storage and repeated copy jobs
Need complex parsing, enrichment, or custom libraries	Notebook	Code control and Spark scale
Need multiple activities with failure handling	Pipeline	Dependencies, conditions, retries, parameters
Need SQL-based transformation after load	Warehouse SQL or Spark SQL depending on target	Keep transformations close to the serving/storage design

Notes and examples

Incremental load essentials

For incremental ingestion, look for:

A reliable change indicator, such as modified timestamp, increasing key, version, or CDC feed.
A stored watermark from the last successful run.
A cutoff value for the current run.
A load step that brings only the eligible changes.
An upsert/merge step into the target.
Audit handling for failed runs so the watermark is not advanced incorrectly.

Common trap: updating the watermark before the target write succeeds. If the run fails after extraction but before merge, advancing the watermark can skip data.

Full load versus incremental load

Use full load when	Use incremental load when
Dataset is small	Dataset is large
Source lacks reliable change tracking	Source provides modified date, CDC, or versioning
Reload is simple and cheap	Reload would exceed time, capacity, or cost expectations
Target can be safely overwritten	Target must preserve history or avoid disruption
Data freshness requirements are loose	Frequent refresh is required

SQL, Spark, and Delta quick reminders

Spark/notebook patterns

Know when notebooks are appropriate:

Custom PySpark transformations.
Large-scale joins and aggregations.
Data cleansing that requires code.
Delta table maintenance.
Reusable engineering notebooks triggered by a pipeline.
Exploratory validation before productionizing a pipeline.

Notes and examples

Performance traps:

Symptom	Likely cause	Review response
Slow join	Large shuffle, skewed key, unnecessary columns	Filter early, select only needed columns, consider join strategy
Slow reads	Poor partitioning, many small files, no predicate pruning	Optimize layout and query filters
Slow writes	Too many output files or poor partition choice	Control repartitioning and table maintenance
Repeated expensive computation	Recomputing same intermediate data	Cache only when reused and beneficial
Job fails after schema change	Schema mismatch	Add explicit schema management and validation

SQL patterns

For Warehouse-oriented questions, expect SQL design and operations:

Pattern	Use when
CTAS-style creation	Building transformed tables from query results
Views	Abstracting query logic or serving controlled projections
Stored procedures	Encapsulating repeatable SQL transformations
MERGE/upsert	Applying changes from staging to target
Staging tables	Loading and validating before applying to curated tables
Star schema	Serving facts and dimensions for analytics

Common trap: assuming every SQL Server feature or index-tuning habit maps directly to Fabric Warehouse. Focus on Fabric-appropriate table design, query shape, data volume reduction, and monitoring.

Deployment and lifecycle

DP-700 scenarios may test whether you can move a Fabric solution safely from development to production.

Requirement	Review response
Track changes to notebooks, pipelines, or other supported items	Use Git integration where supported
Promote content between environments	Use deployment pipelines
Use different connections in dev/test/prod	Parameterize and remap settings during deployment
Avoid breaking production	Test in lower environment and validate dependencies
Understand impact of table changes	Use lineage and dependency review
Repeat infrastructure/configuration consistently	Use documented deployment patterns and avoid manual-only changes

Common mistake: treating deployment as only copying an item. Real deployment also includes connections, permissions, parameters, schedules, and downstream dependencies.

Common DP-700 scenario traps

Trap	Why it is wrong	Better thinking
“Use a notebook for everything”	Not every task needs custom code	Use pipelines for orchestration, dataflows for low-code, warehouse for SQL
“Use a pipeline for all transformations”	Pipelines coordinate work; heavy transforms belong elsewhere	Pipeline calls the right engine
“Copy data even when a shortcut would work”	Duplicates storage and introduces sync complexity	Use shortcuts when no-copy access meets requirements
“Use full refresh for a large changing source”	Wastes time and capacity	Use incremental ingestion and merge
“Grant workspace Admin to fix access”	Over-permissive and risky	Diagnose the correct permission layer
“Partition by high-cardinality column”	Creates too many partitions and small files	Partition by useful pruning columns
“Ignore failed-run watermark behavior”	Can skip records	Advance watermark only after successful target update
“Assume SQL endpoint equals Warehouse”	Lakehouse and Warehouse have different write/serving patterns	Match engine to requirement
“Optimize compute before data layout”	Bad layout can dominate performance	Fix file sizes, filters, partitions, and table design
“Promote items without remapping connections”	Dev settings can leak into prod	Parameterize and validate deployment settings

Quick decision checklist for exam questions

Before selecting an answer, identify:

Target storage: lakehouse, warehouse, external source through shortcut, or semantic model.
Transformation style: low-code, Spark/code, SQL, or orchestration-only.
Load pattern: full, incremental, CDC, streaming/near-real-time, or no-copy.
Security boundary: workspace, item, SQL object, semantic model, or source system.
Operational requirement: schedule, retry, monitoring, deployment, lineage, or capacity optimization.
Performance issue: compute, query shape, file layout, partitioning, source throughput, or concurrency.
Failure behavior: idempotency, watermark handling, duplicate handling, and auditability.

If two answers seem plausible, prefer the one that satisfies the requirement with the least unnecessary complexity.

Mini review prompts

Use these as a quick readiness check before starting a DP-700 question bank.

Prompt	Best answer direction
You need to orchestrate a copy, run a notebook, and then execute a SQL step on a schedule	Pipeline
You need low-code cleansing using Power Query-style steps	Dataflow Gen2
You need no-copy access to data already stored in a supported location	Shortcut
You need complex PySpark transformations and Delta table maintenance	Notebook
You need a SQL-first curated dimensional store	Warehouse
You need to apply only changed records from a source table	Incremental load with watermark/CDC and merge/upsert
A lakehouse has raw files but SQL users cannot query them as tables	Create/register proper tables or write to the Tables area in the correct format
A user can access a workspace but should only see certain rows	Apply the appropriate data/model-level security, not only a workspace role
A pipeline skipped records after a failed load	Review watermark update timing and idempotency
A Spark job is slow after thousands of tiny files were created	Compact/optimize table layout and review write pattern

Final readiness checklist

You are ready for heavier mock exam practice when you can quickly explain:

When to use a Lakehouse instead of a Warehouse.
When a pipeline should orchestrate rather than transform.
When Dataflow Gen2 is preferable to a notebook.
How shortcuts differ from copying data.
How to design incremental loads without skipping records.
How Delta tables, partitioning, compaction, and schema handling affect performance and reliability.
How workspace roles differ from item, SQL, source, and model-level permissions.
How Git integration and deployment pipelines support lifecycle management.
Where to look when a pipeline, dataflow, notebook, SQL query, or capacity is failing.
How to choose the simplest Fabric pattern that satisfies the scenario.

Put the review into practice

Study Plan

DP-700 — Microsoft Fabric Data Engineer Associate Cheat Sheet

Suggested topic drill order

Review method

DP-700 scope snapshot

Microsoft Fabric mental model

Core hierarchy and terms

Fabric item selection matrix

DP-700 mental model

Lakehouse, warehouse, and SQL endpoint distinctions

Files vs tables in a lakehouse

Data architecture patterns

Medallion architecture reference

Pattern selection

Ingestion and orchestration quick reference

Ingestion method decision table

Pipeline design checklist

Incremental load reference

Transformation reference

Transformation tool selection

PySpark Delta patterns

T-SQL warehouse patterns

Transformation tool selection

Common transformation mistakes

Modeling and serving data

Dimensional modeling quick reference

Serving option decision table

Direct Lake, Import, and DirectQuery

Security, permissions, and governance

Security layers

Workspace role exam cues

Governance checklist

Access control layers

Governance concepts to review

Security decision traps

DevOps and lifecycle management

Performance and optimization

Delta and lakehouse optimization

Warehouse and SQL optimization

Spark optimization quick checks

What to know

Bronze, silver, gold review

Monitoring and troubleshooting

Where to look first

Troubleshooting decision table

Where to look first

Optimization levers

Delta table maintenance concepts

High-yield DP-700 distinctions

Exam scenario playbook

Last-minute checklist

High-yield Fabric item decisions

Fast elimination rule

Lakehouse versus Warehouse

Ingestion patterns

Choose the right ingestion method

Incremental load essentials

Full load versus incremental load

SQL, Spark, and Delta quick reminders

Spark/notebook patterns

SQL patterns

Deployment and lifecycle

Common DP-700 scenario traps

Quick decision checklist for exam questions

Mini review prompts

Final readiness checklist

Put the review into practice

Browse Certification Practice Tests