DP-700 — Microsoft Fabric Data Engineer Associate Quick Reference

This Quick Reference 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.

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.

Microsoft Fabric mental model

Core hierarchy and terms

Fabric item selection matrix

Lakehouse, warehouse, and SQL endpoint distinctions

Files vs tables in a lakehouse

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]

Pattern selection

Ingestion and orchestration quick reference

Ingestion method decision table

Pipeline design checklist

Example pipeline expression pattern:

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

Incremental load reference

Transformation reference

Transformation tool selection

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.

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

• 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.

DevOps and lifecycle management

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

Warehouse and SQL optimization

Spark optimization quick checks

Monitoring and troubleshooting

Where to look first

Troubleshooting decision table

High-yield DP-700 distinctions

Exam scenario playbook

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?

Practical next step

Use this Quick Reference as a checklist while you work through DP-700 practice scenarios. For each scenario, force yourself to identify the Fabric item, data movement pattern, security boundary, monitoring point, and likely optimization before checking the answer.