Fabric Data Engineer Notes: land it, govern it, keep it fast

Domain 1: Workspace settings

Start from the workspace, not the individual notebook. Fabric workspace settings control how Spark runs, which domain the workspace belongs to, how OneLake is used, and whether Apache Airflow is part of the orchestration story.

Extra Designing a full multi-tenant capacity topology for an enterprise is broader than the associate focus. Configuring the named workspace settings and knowing why they matter is in scope.

Security and governance

Pick the control from the question’s grain: who can open the workspace, who can use an item, which rows or columns a consumer may see, and how sensitive data is labeled or masked.

Workspace Admin is not RLS. Masking is not CLS. Endorsement tells consumers which asset to trust; it does not replace permissions.

Validate the identity and the access path

A report restriction does not automatically protect the same data read through Spark or another endpoint. State whether the requirement concerns a semantic model, Warehouse SQL, or OneLake access, then test the actual consumer identity on that path. In OneLake, Admins, Members, and Contributors already have broad read/write access; adding a narrow OneLake security role does not restrict that broader workspace grant. A Viewer or item reader can receive granular data permissions. Also review overlapping roles such as DefaultReader. See OneLake data security.

Dynamic data masking changes query results, not the underlying stored value. A user with sufficiently broad query access may infer sensitive information, so masking alone is not a confidentiality boundary. When a consumer must not query a column, consider column permissions rather than merely displaying a partial value. See Fabric masking limitations.

Sensitivity labels classify content. Protection depends on the configured policies, item type, and access or export path; a label name alone does not prove that access is blocked. Distinguish label protection from Fabric protection policies and test effective permissions. See protected labels and policy-based access restrictions.

Domain 2: Load patterns and dimensional prep

Choose the load from how source data changes, then prepare the model shape the consumer needs.

Dimensional model prep means facts and dimensions consumers can join without inventing keys in every report. Grain belongs on the fact table. Surrogate keys and slowly changing dimensions appear when history must survive attribute changes. Extra Full Kimball methodology depth beyond what the study guide measures is background, not the center of every stem.

Incremental loading: a retry must not double the facts

Suppose yesterday’s successful load ended at source change marker 100. Today’s run captures a high marker of 140. Extract the bounded interval greater than 100 and up to 140 into a staging area associated with the run. The upper boundary prevents changes arriving during execution from silently changing the meaning of the batch. A timestamp is useful only if the source updates it reliably; a business date such as sale date is not a dependable arrival marker.

1. Capture the boundary. Save the old marker and the run’s proposed high marker. If timestamps can tie, use an appropriate tie-breaker or a deliberate overlap with deduplication. Validate the source’s change-tracking retention and reset behavior.
2. Stage and validate. Record source keys, change versions, and row counts. Quarantine invalid required keys and measure rejected rows. A job can technically succeed while dropping data that matters to the business.
3. Apply idempotently. Deduplicate the batch and merge or upsert by a stable business key and version. Appending the same batch twice is not idempotent. Capture deletions through tombstones, change tracking, or reconciliation when required.
4. Commit progress after success. Advance the stored marker only when the destination changes and required checks succeed. If a crash occurs after the merge but before marker persistence, replay the same interval safely. Coordinate concurrent runs so they do not race to advance the marker.

Failure exercise: a destination has accepted orders 101–120 when the connection fails. The marker is still 100. Retrying the same bounded batch should update or leave those orders once and apply the remainder. Advancing directly to 140 without confirming the destination would lose data; appending every staged row again would duplicate sales. If the source resets its change marker, detect the discontinuity and perform a controlled re-baseline rather than trusting a now-meaningless number.

Dimensional history changes the join

A Type 1 dimension overwrites an attribute when only its current value matters. A Type 2 dimension preserves versions with effective dates and a version key. If a store changes region, yesterday’s sale should join to the dimension version valid at the sale time when the report asks for historical region. Joining every fact to today’s region changes the business meaning. If the dimension arrives after the fact, use a documented unknown-member or deferred-resolution process and reconcile it later.

Lakehouse and Warehouse: the write boundary matters

A Lakehouse SQL analytics endpoint supports T-SQL queries over Delta tables, along with SQL views, functions, and stored procedures within its supported surface. Creating a procedure does not let it bypass the read-only boundary for Lakehouse table data. Use Spark to modify those tables. A Fabric Warehouse supports a different T-SQL write surface. See the Lakehouse SQL endpoint guide.

For a concrete choice, imagine a notebook that has already written a silver Delta table. An analyst can query it through the SQL endpoint. An engineer who must correct its rows should use the supported Lakehouse write path, not repeatedly retry an UPDATE through the read-only endpoint. If the primary requirement is relational table modification through T-SQL, evaluate Warehouse instead.

A shortcut to a folder of ordinary Parquet files does not automatically create a SQL-queryable Delta table. Distinguish Files from Tables, verify the target format and table registration, and check metadata synchronization. Confirm source permissions before diagnosing every missing table as an engine problem.

Transform languages and data quality

PySpark fits distributed DataFrame work in notebooks. T-SQL reads and modifies supported Warehouse tables. The Lakehouse SQL analytics endpoint reads Delta table data but cannot insert, update, or delete that data; use Spark for Lakehouse writes. KQL fits Eventhouse and Real-Time Intelligence analytics. The exam often pairs a language with a destination; answering “SQL” for a pure Eventhouse streaming stem is usually wrong.

• Denormalize — flatten joins into wider analytical tables when query simplicity beats storage purity.
• Aggregate — precompute totals or grains for consumers who never need the raw grain.
• Duplicates — decide whether to keep the latest event, merge keys, or fail the load.
• Missing data — impute, reject, or quarantine; silent nulls in a fact key break joins.
• Late data — define how windows and incremental watermarks treat arrivals after the cutoff.

Quality rules belong in the pipeline design, not only in a dashboard that notices wrong totals a week later.

Read the result: SQL, PySpark, and KQL worked examples

These fictional examples make the reasoning visible. They do not execute in this webpage or connect to your data. SQL and PySpark use a tiny order-change batch; KQL uses sensor events. Read the expected result before adapting a pattern to a Fabric development environment. No example writes or deletes a persistent table.

SQL: choose the latest order version before summing

Order 101 changed from 40 to 45. Order 102 appears twice with the same business values but different ingestion IDs. Order 103 has no customer key and is rejected by this example’s rule. Ranking must happen before aggregation; otherwise the report counts obsolete and repeated rows. Run this read-only query in a compatible T-SQL query editor.

WITH Changes AS (
  SELECT 101 AS OrderId, 1 AS CustomerId,
         1 AS Version, 1 AS IngestId, 40 AS Amount
  UNION ALL SELECT 101, 1, 2, 2, 45
  UNION ALL SELECT 102, 2, 1, 3, 30
  UNION ALL SELECT 102, 2, 1, 4, 30
  UNION ALL SELECT 103, NULL, 1, 5, 20
), Ranked AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY OrderId
    ORDER BY Version DESC, IngestId DESC
  ) AS rn
  FROM Changes
  WHERE CustomerId IS NOT NULL
)
SELECT CustomerId, SUM(Amount) AS Total
FROM Ranked
WHERE rn = 1
GROUP BY CustomerId
ORDER BY CustomerId;

Expected: customer 1 has 45; customer 2 has 30. Only two valid latest orders contribute, totaling 75. SUM over all five input rows would produce 165. IngestId is a deterministic tie-breaker for this fixture; conflicting records with the same business version need an explicit source-authority rule rather than blindly trusting arrival order. In a real pipeline, rejected records belong in an observable quarantine path, not an invisible discard.

PySpark: the same batch, with a window

Use a Fabric PySpark notebook with its existing Spark session. The example uses integer amounts for readability; production monetary data normally needs an agreed decimal precision and currency. A plain dropDuplicates on OrderId does not express the requirement to keep the highest version.

from pyspark.sql import functions as F
from pyspark.sql.window import Window

rows = [(101, 1, 1, 1, 40), (101, 1, 2, 2, 45),
        (102, 2, 1, 3, 30), (102, 2, 1, 4, 30),
        (103, None, 1, 5, 20)]
changes = spark.createDataFrame(
    rows, "OrderId int, CustomerId int, Version int, "
          "IngestId int, Amount int")
valid = changes.filter(F.col("CustomerId").isNotNull())
order = Window.partitionBy("OrderId").orderBy(
    F.col("Version").desc(), F.col("IngestId").desc())
latest = (valid.withColumn("rn", F.row_number().over(order))
          .filter(F.col("rn") == 1))
result = (latest.groupBy("CustomerId")
          .agg(F.sum("Amount").alias("Total"))
          .orderBy("CustomerId"))
result.show()

Expected: the same (1, 45) and (2, 30) rows as SQL. The partition key defines which records compete; ordering defines which version wins. This only deduplicates the current batch. A destination merge is still needed for safe replay across batches. See the Apache Spark row_number reference.

KQL: deduplicate events, then use event-time buckets

The sensor retries event e2. Its latest ingested copy should count once. Event e3 occurs at 10:06 but arrives at 10:12, so its event-time bucket starts at 10:05. All timestamps below are UTC. This is a finite query over sample rows, not a live streaming job.

datatable(EventId:string, DeviceId:string,
          EventTime:datetime, ArrivedAt:datetime, Temp:real)
[
  "e1", "A", datetime(2026-10-25T10:01:00Z),
             datetime(2026-10-25T10:01:05Z), 20.0,
  "e2", "A", datetime(2026-10-25T10:04:00Z),
             datetime(2026-10-25T10:04:05Z), 24.0,
  "e2", "A", datetime(2026-10-25T10:04:00Z),
             datetime(2026-10-25T10:07:00Z), 24.0,
  "e3", "A", datetime(2026-10-25T10:06:00Z),
             datetime(2026-10-25T10:12:00Z), 30.0
]
| summarize arg_max(ArrivedAt, *) by DeviceId, EventId
| summarize AvgTemp=avg(Temp), Events=count()
    by DeviceId, WindowStart=bin(EventTime, 5m)
| order by DeviceId asc, WindowStart asc

Expected: A at 10:00 has average 22 and count 2; A at 10:05 has average 30 and count 1. Without deduplication, e2 would bias the first average. This query includes e3 because the row is already present when the query runs; it does not configure a streaming lateness policy or recover an event discarded upstream. See Microsoft’s arg_max and bin references.

Streaming and Real-Time Intelligence

Eventstream ingests and routes continuous events in Fabric. Spark structured streaming processes streams in notebooks when Spark is the required engine. KQL queries and transforms data in Eventhouse. Windowing groups events by time so averages and counts have a defined interval.

A standard shortcut provides access to existing data. Eventhouse query acceleration for supported external Delta tables adds a cache and indexing work for faster queries. That consumes storage and compute; it is not a zero-cost toggle or a promise that data is never cached. Compare the query’s time range with the acceleration policy and check synchronization. A strict no-additional-copy requirement must consider caches too. See query acceleration for OneLake shortcuts.

Tumbling windows are fixed, non-overlapping intervals. A hopping window can overlap when its hop is shorter than its duration. Event time is when the event occurred; processing time is when the system handles it. A window length does not by itself define allowed lateness, deduplication, or recovery. In Spark streaming, checkpoints preserve progress and state, while watermarks help bound event-time state; sink behavior and replay logic still determine whether duplicates appear.

Resolve common Fabric errors

Optimize stores, jobs, and queries

Optimize the component the stem actually uses. Distinguish Warehouse query planning, Spark execution, Eventstream processing, and Lakehouse file layout; each has different evidence and tuning controls.

Extra Capacity SKU shopping and tenant-wide cost chargeback design sit outside most associate stems. Know which engine and store to tune first.

Diagnose before changing the workload

Record the failing item and run ID, error category, start time, source and destination, input/output counts, and last successful boundary. These facts distinguish a permissions failure from a performance problem. Retrying cannot repair a revoked credential, and more capacity cannot correct an invalid SQL write against a read-only endpoint.

OPTIMIZE, VACUUM, and query layout

For Delta Lakehouse tables, OPTIMIZE compacts active small files; VACUUM removes obsolete files beyond retention. Compaction does not immediately reclaim all old file storage. Plan retention around active readers, recovery, and historical-version requirements. Do not delete files manually or set retention to zero to make a demonstration look cleaner. See Fabric Delta maintenance.

For Spark, compare task durations, shuffle sizes, spills, and skew before changing executor resources. A single hot key can leave one task running after all others finish. A broadcast join is appropriate only if the small side safely fits the applicable limits; broadcasting the largest table can make the problem worse. Avoid assuming that caching every intermediate result or increasing every partition count helps.

For Warehouse, inspect query shape and statistics, and compare representative execution times before and after a change. Fabric is not a license to reuse every SQL Server index script or Synapse distribution design. The current Warehouse performance guide is the reference for that engine. For Eventhouse, inspect KQL filters, queried time ranges, and cache behavior; for Eventstream, separate source arrival, operator processing, and destination delays.

V-order and Z-ORDER are different: V-order optimizes Parquet file layout and encoding for read-heavy use; Z-ORDER colocates selected values to help data skipping. Both can add write or rewrite cost. Choose from the consumer’s actual access pattern and validate current runtime behavior. See Fabric’s V-order guidance.

Freshness is an end-to-end result

For an Import semantic model, inspect refresh history and completion time against the source load. For Direct Lake or DirectQuery, first identify the model’s access mode and its current source path rather than assuming the same scheduled-import failure. A successful upstream run does not prove that the report’s data is fresh. Alert on missing or late expected data as well as failed jobs, and include enough context for an operator to identify the broken dependency.

Worked scenario: nightly sales plus store telemetry

A retail team needs two paths into the same Fabric workspace. Point-of-sale files arrive every night as Parquet in an external lake folder. Store sensors emit temperature events continuously. Analysts need a dimensional sales model in a Lakehouse for morning reports, and operations needs a near-real-time KQL view of temperature spikes. Security requires that regional managers see only their stores’ rows.

Design choices that fit DP-700 distinctions

1. Sales landing. Use a pipeline on a schedule with an arrival or change marker when the source provides a reliable incremental boundary; sale date alone can miss late deliveries. Prefer a OneLake shortcut if the Parquet already lives in a reachable lake path and a copy is unnecessary. Prefer a copy or dataflow load when Fabric must own a curated Lakehouse table for transforms.
2. Sales transform. Build dimensions and a sales fact with Dataflow Gen2 or a notebook. Choose the notebook when PySpark joins and dedupe logic are heavy; choose Dataflow Gen2 when the reshape is tabular and connector-driven.
3. Telemetry path. Land events through Eventstream into Eventhouse native tables for KQL. Use windowing for five-minute average temperature. Define late-event behavior so a delayed reading still updates the window or is quarantined—state the rule explicitly.
4. Security. Apply RLS so regional managers filter on region. Workspace Contributor for engineers is not enough to answer the row filter requirement.
5. Lifecycle. Version supported item definitions and promote tested changes through deployment stages. Verify each item’s CI/CD support, connection mappings, and target permissions; deployment does not copy every dataset or secret.
6. Monitoring. Alert on pipeline failure and Eventstream ingestion lag. Also monitor the semantic model refresh that morning reports use; a green pipeline with a failed refresh can leave reports stale or unavailable.

Wrong alternatives to say out loud: treating Eventstream as the nightly Parquet tool; using workspace Admin instead of RLS; assuming a shortcut continuously mirrors like mirroring; optimizing Spark when the failure is a shortcut permission error.

Practice DP-700 reasoning without a Fabric capacity

You can complete these exercises on paper or in a local text editor. They need no paid Fabric capacity. Explain each answer in a sentence before opening the suggested answer.

Turn practice misses into a useful revision plan

After a practice session, write down the question ID, the decision you missed, and the clue you overlooked. Revisit the matching section above, then explain why the nearest alternative fails. For example, “RLS because managers share one model but not one region’s rows” is more actionable than “remember security.” Retry missed items after a break and also answer unfamiliar examples; recognizing yesterday’s option wording can inflate your apparent progress.

Finish by checking every objective in the official DP-700 study guide. The local bank uses an editorial allocation of 30 questions per domain (33.3% each) within the published 30–35% ranges, not an exact model of a live exam. Its raw percentage cannot be converted into Microsoft’s scaled passing score. Use the explanations and domain report to find gaps rather than treating a single result as a booking recommendation.

Frequently asked questions