CertifyDeckDP-700 · CHEAT SHEET
Microsoft Certified: Fabric Data Engineer Associate
Key concepts grouped by official exam weight.
Download as PDF (free, no sign-up)1. Implement and manage an analytics solution
30–35%Configure Microsoft Fabric workspace settings
- Workspace admins choose the default pool (starter or custom), a default environment with runtime and libraries, and job admission: optimistic by default or reserving maximum cores. They also set the session timeout, a maximum job lifetime that only cancels user-submitted jobs, and high concurrency, which shares sessions for the same user.
- A domain groups workspaces by business area; when a workspace is assigned, all its items inherit the domain, but access still depends on roles and permissions. Domain contributors must be workspace admins and assign the workspace from its settings. A default domain assigns the unassigned workspaces of specified users and those they create later, without changing existing assignments.
- The OneLake tab of the workspace settings turns on shortcut caching, which stores files read from S3, GCS or gateway shortcuts to reduce egress, with a configurable retention and a reset option. It also enables OneLake diagnostics, which writes data access events as JSON to a lakehouse on the same capacity and can be made immutable.
- Workspace settings define the default Apache Airflow runtime. The starter pool starts instantly and is deprovisioned when unused, suited to development; a custom pool is always on until paused, with Large or Small node size, autoscale and extra nodes for more concurrent DAGs. If compute customization for items is turned off, the starter pool is used.
Implement lifecycle management in Fabric
- Git integration works per workspace, connected to one branch and folder in Azure DevOps or GitHub. Only the admin connects or disconnects; contributors commit and update, and the admin can let them switch branches. Update always brings the whole branch, and commits are blocked while incoming changes exist. Each developer should work in a branched workspace.
- SDK-style database projects store the schema as .sql files, build into a .dacpac and publish with DacFx or SqlPackage; generate the script before publishing and use BlockOnPossibleDataLoss in production. For a Git-connected SQL database in Fabric, Fabric maintains the project file, each update builds and publishes, and post-deployment scripts manage static data.
- A deployment pipeline assigns one workspace per stage and copies metadata, not data, between stages. Paired items are overwritten keeping their ID and URL; unpaired items are duplicated. Rules (data source, parameter, or default lakehouse for notebooks) are defined in the target stage. Deploying requires pipeline admin plus Contributor in both workspaces.
Configure security and governance
- Workspace roles (Admin, Member, Contributor and Viewer) are assigned to users or groups and apply to all items; with several groups the highest permission wins. Only Admin deletes the workspace or adds admins; Member adds lower roles; Contributor writes items and reads data through Spark; Viewer only reads through T-SQL unless a OneLake security role grants more.
- Besides workspace roles, you can share a single item: sharing always grants Read, which lets the recipient discover and open the item and connect to the SQL endpoint, and you can add ReadData (T-SQL queries), ReadAll (Spark and OneLake), Edit or Share. Item permissions and workspace roles are evaluated separately, so removing one doesn't remove the other.
- In the warehouse and SQL endpoint, object-level security uses GRANT, REVOKE and DENY; column-level security uses a GRANT SELECT that lists columns; and row-level security uses CREATE SECURITY POLICY with an inline function as filter predicate. In OneLake, security roles grant access to folders and tables: permissions inherit downward and users can traverse parent folders without seeing siblings.
- Dynamic data masking hides values in query results without changing stored data; it is defined with MASKED WITH and the default(), email(), random() and partial() functions. Users with UNMASK or CONTROL, including the Admin, Member and Contributor roles, see real data. It doesn't stop inference through filters, so combine it with column-level or row-level security.
- Microsoft Purview sensitivity labels are applied to any Fabric item from its header or settings, with edit permissions on the item. They propagate through inheritance upon creation (for example, from a lakehouse to a notebook) and downstream inheritance, which never overwrites manual labels. Tied to protection policies, they block access for anyone not listed in the policy.
Orchestrate processes
- Dataflow Gen2 is the low-code Power Query option for preparing and transforming data; a pipeline Copy activity moves large volumes without code; and a notebook runs Spark code with open-source libraries for complex transformations. Pipelines also orchestrate dataflows and notebooks with schedules, loops, conditions and failure paths.
- A pipeline starts on demand, from schedules (several per pipeline, each with a frequency and a required start and end) or from event triggers on file, job or workspace events, which rely on Eventstream and Activator. Job events such as ItemJobSucceeded chain items without a central orchestrator. A scheduled notebook runs under the identity of whoever created or last updated the schedule.
- The Notebook activity passes base parameters that override the values in the notebook's parameters cell. notebookutils.notebook.run runs a child notebook and returns its exit value, always as a string; runMultiple runs several in parallel with a DAG of dependencies. In expressions, @{ } interpolates inside text and always returns a string, and @item() gives the current ForEach element.
2. Ingest and transform data
30–35%Design and implement loading patterns
- A full load copies everything on every run; an incremental load copies everything once and then only new or changed data. Copy job supports watermark (inserts and updates only) or CDC (deletes too) and the Append, Merge, Overwrite or SCD Type 2 methods. In pipelines the watermark pattern uses Lookup, Copy and a stored procedure; Dataflow Gen2 replaces date buckets.
- The ETL process loads staging, then dimensions in dependency order and facts last, looking up each dimension's surrogate key. Dimensions with generated surrogate keys are never truncated; changes are applied as SCD type 1, 2 or 3 and source deletions as soft deletes. When a member is missing, the fact gets an Unknown member or an inferred member.
- An eventstream can load an eventhouse through direct ingestion, or through processing before ingestion when operators are applied, or a lakehouse, which enforces the first record's schema and doesn't suit CDC data. Spark Structured Streaming writes to Delta in append mode with a checkpoint and a trigger for larger batches; production uses a Spark job definition with retries.
Ingest and transform batch data
- A lakehouse suits Spark and data of any type; its SQL analytics endpoint is read-only. A warehouse is chosen for T-SQL, structured data and multi-table transactions. An eventhouse serves streaming events analysed with KQL, and its OneLake availability is opt-in. SQL database in Fabric covers OLTP and Cosmos DB in Fabric covers NoSQL.
- Dataflow Gen2 provides no-code or low-code Power Query transformations, suited to analysts and low to medium volumes. Spark notebooks handle any data type and complex logic in code. In a KQL database, an update policy transforms data as it is ingested. In a warehouse, T-SQL (INSERT...SELECT, CTAS) transforms data with set-based operations.
- A OneLake shortcut points to internal or external data without copying it. In Tables it can only be created at the top level and, when the target is Delta and the name has no spaces, it is recognized as a table; Files does no table discovery. The most restrictive permission of shortcut and target paths applies, and deleting a shortcut leaves the target data intact.
- Mirroring continuously replicates databases (Azure SQL, SQL Server, Cosmos DB, Snowflake…) into Delta tables in OneLake with a read-only SQL analytics endpoint. Metadata mirroring (Azure Databricks) uses shortcuts without copying data, and open mirroring lets an application land files with __rowMarker__ and a _metadata.json with keyColumns in the landing zone.
- Pipelines combine the Copy activity with control activities: Lookup reads configuration or watermarks (output.firstRow or output.value), ForEach iterates over collections with item(), Stored procedure saves the new watermark and Invoke pipeline reuses other pipelines. They run on demand, on schedules or from event triggers, and one Copy activity supports only one on-premises gateway.
Ingest and transform streaming data
- Eventstream ingests, applies lightweight transformations and routes events without code to destinations such as Eventhouse, lakehouse or Activator. Spark Structured Streaming uses PySpark code for complex transformations, checkpoints and Delta sinks, either continuously or with the available-now trigger. In an eventhouse, update policies transform data in KQL at ingestion time.
- Native eventhouse tables store ingested, indexed data, give the lowest latency and support update policies and materialized views. OneLake shortcuts query data in place with external_table(), without copying it, and query acceleration caches them to approach native performance. OneLake availability exposes KQL tables in Delta format to other engines.
- A standard OneLake shortcut in a KQL database is queried with external_table() without moving data, but it is slower than a native table. Query acceleration caches and indexes the Delta data for a hot period in days (or based on HotDateTimeColumn), giving ingested-like speed at an SSD storage cost. It behaves like an external table: no materialized views.
- Eventstream transforms events with no-code operators (Filter, Manage fields, Aggregate, Group by, Expand, Union, Join) or with a SQL operator based on the Stream Analytics language, which supports several outputs through SELECT ... INTO. Only lakehouse, eventhouse (processing before ingestion), derived stream and Activator accept preingestion operators; other destinations go through a derived stream.
- Structured Streaming treats a stream as a table that rows are appended to; writeStream.format("delta") with checkpointLocation and toTable writes to lakehouse tables. Trigger intervals and Optimized Write reduce small files, foreachBatch enables MERGE upserts and watermarks bound state. In production, use Spark job definitions with a retry policy.
3. Monitor and optimize an analytics solution
30–35%Monitor Fabric items
- Ingestion is monitored in several places: the Monitoring hub and run detail view for pipelines and Copy jobs (rows read and written, incremental bounds), Data insights and Runtime logs in Eventstream, Monitor replication for mirrored databases, and workspace monitoring, which stores logs such as EventhouseIngestionResultsLogs that you query with KQL.
- Transformations are monitored per engine: Dataflow Gen2 through refresh history (per-activity statistics and detailed logs), Spark through the Monitor hub, Recent runs and the application detail page (Jobs, Logs, Resources, Item snapshots and a Structured Streaming tab), and warehouses through queryinsights views such as long_running_queries.
- A semantic model's refresh history shows each automatic attempt, the data and query cache phases and refreshes completed with warnings. The Monitoring hub and the Semantic Model Refresh Detail page centralize status, errors and execution metrics, and workspace monitoring stores operation logs you query with KQL. Only scheduled refreshes send failure emails.
- To alert on one activity, add an Outlook or Teams activity after it; for a whole workspace, workspace monitoring writes ItemJobEventLogs and an Activator rule on a KQL queryset reports any failure. Activator sends one alert per returned record, and rules are created stopped and only evaluate newly ingested data.
Identify and resolve errors
- The run detail view shows each activity's error and lets you rerun from the failed activity. Deactivated activities never run and have no error field. The troubleshooting guide explains codes such as 2002, 2003 and LSROBOTokenFailure, and workspace monitoring stores pipeline and activity runs but not error details.
- Refresh history shows tables and activities with their errors, and detailed Mashup engine logs are downloaded from each run. Destination validation rules (DestinationQueryNotFound, DestinationQueryChallenge, DestinationQueryHasUnsupportedScript) have specific fixes, and with a gateway, reading staged data uses TDS over port 1433.
- Notebook errors are diagnosed with the Spark UI (Executors and Stages tabs) and the driver logs from the Monitor hub. Exit code 137 means the container ran out of memory, collect() or toPandas() overload the driver, and spark.executor.* settings belong in an initial %%configure cell, not in spark.conf.set().
- In an eventhouse, .show ingestion failures lists ingestion command failures with Details and FailureKind, and filtering on OriginatesFromUpdatePolicy reveals update policy failures. With IsTransactional false the data lands only in the source. E_QUERY_RESULT_SET_TOO_LARGE is fixed by reducing the result, and E_RUNAWAY_QUERY by more memory per iterator or shuffle.
- Each eventstream node offers Data insights, with metrics such as deserialization or conversion errors, watermark delay and backlogged events, and Runtime logs, with messages you can filter by severity. Workspace monitoring adds tables you can query with KQL, but Log Eventstream activity must be enabled on each eventstream.
Optimize performance
- OPTIMIZE compacts small files; with ZORDER BY it colocates values for file skipping and with VORDER it applies V-Order. Auto compaction runs OPTIMIZE after writes only when fragmentation is detected, and adaptive target file size adjusts file size as the table grows. VACUUM removes unreferenced files, and OPTIMIZE FULL reclusters after liquid clustering keys change.
- To speed up pipelines, ForEach without Sequential processes items in parallel and Batch count limits how many run at once. In the Copy activity, Intelligent throughput optimization allocates resources, Degree of copy parallelism sets the threads, and SQL partition options split the read. Copy job with incremental copy (watermark or CDC) avoids reloading whole tables.
- In a Fabric warehouse, load in batches with COPY INTO or a single INSERT instead of row-by-row inserts, which fragment files; compaction runs automatically. V-Order is on by default and disabling it is irreversible. Use right-sized data types (varchar(n), integers, date types) and OPTION (FORCE DISTRIBUTED PLAN) when non-scalable operations are reported.
- In an Eventhouse, the caching policy keeps hot data on SSD and the retention policy decides how long data stays queryable. Materialized views store aggregations and materialized_view() queries only the materialized part; query acceleration speeds up OneLake shortcuts. In Eventstream, the event throughput setting scales sources and destinations.
- The native execution engine speeds up Spark queries over Parquet and Delta without code changes and falls back to the JVM for unsupported work such as structured streaming or JSON. High concurrency mode shares one session among notebooks with the same user and settings. Resource profiles (writeHeavy, readHeavyForSpark, readHeavyForPBI) apply read- or write-oriented settings.