DP-700 cheat sheet

Implement and manage an analytics solution

Configure Microsoft Fabric Workspace Settings: Spark, Domains, OneLake, Airflow

• Tenant admins enable features and create domains; capacity admins control compute limits and whether custom pools are allowed; workspace admins configure their own workspace within those guardrails.
• Starter pools give session starts in seconds from pre-warmed nodes; custom pools give node family, size, and autoscale control but cold-start in minutes.
• Workspace-level custom Spark pools require the capacity admin to allow customized workspace pools first.
• Environments package runtime version, libraries, and Spark properties; set one as the workspace default instead of pip-installing in notebook cells.
• High concurrency mode shares one Spark session across a user's compatible notebooks and across pipeline notebook activities, cutting startup time and capacity use.
• Domains group workspaces by business area: tenant admins create domains, domain admins manage them, contributors assign workspaces, and a workspace belongs to one domain.
• Deleted workspaces keep their OneLake data through an admin-configurable retention period and can be restored before it lapses.
• Apache Airflow jobs are tenant-gated managed Airflow; pick a custom always-on pool when production DAGs cannot wait for a starter pool to warm up.

Fabric Lifecycle Management: Git Integration and Deployment Pipelines

• Git integration = source control for item definitions; deployment pipelines = staged promotion between workspaces — neither one ever moves data.
• A workspace connects to exactly one branch (Azure DevOps or GitHub) and a folder within it; only a workspace admin can connect or disconnect.
• Commit pushes workspace changes to the branch, Update pulls branch changes in; conflicts are resolved per item by choosing the workspace or Git version, or by merging in Git.
• Branch out creates a feature branch plus an isolated workspace in one step; changes return to the shared workspace through a pull request, then Update.
• SQL database projects version Warehouse schema as .sql files, build to a dacpac, and deploy repeatably with SqlPackage; schema compare detects drift.
• Deployment pipelines support two to ten stages with one workspace per stage; the compare view shows added, different, and missing items before you deploy.
• Deployment rules (data source, parameter, and notebook default Lakehouse rules) live on target-stage items and apply from the next deployment onward.
• Standard pattern: only the development workspace is Git-connected; test and production receive content exclusively through the deployment pipeline.

Fabric Security and Governance: Workspace Roles, RLS, and OneLake Access

• Fabric security stacks three layers: workspace roles (what you can do), item permissions (which items), and data-level security (which rows, columns, and files).
• Only Admins manage the workspace and other Admins; Members can share and manage lower roles; Contributors create and edit but cannot share; Viewers read through the SQL analytics endpoint only.
• Item sharing splits into Read (connect), ReadData (all tables via SQL), and ReadAll (raw files via OneLake/Spark) — withhold ReadAll to keep consumers behind SQL security.
• RLS (CREATE SECURITY POLICY + predicate function), CLS (column-scoped GRANT), and OLS (GRANT/DENY on objects) are enforced only in the Warehouse and SQL analytics endpoint — Spark and OneLake file reads bypass them.
• Dynamic data masking obfuscates query output for users without UNMASK; it is not encryption and does not stop inference through predicates.
• OneLake data access roles add folder- and file-level security inside a Lakehouse, scoping what ReadAll and Spark users can reach.
• Sensitivity labels (Microsoft Purview) classify and follow data downstream; endorsement is Promoted (any writer), Certified and Master data (admin-authorized users only).
• Fabric audit logs live in the unified Microsoft Purview audit log — the go-to answer for "who shared/changed/accessed this item."

Fabric Orchestration: Dataflow Gen2 vs Pipeline vs Notebook, and Triggers

• Dataflow Gen2 = low-code Power Query transforms with rich connectors; pipeline = control flow, Copy activity, and orchestration; notebook = code-first Spark. They compose: pipelines invoke both.
• Pipelines orchestrate with ForEach, If/Switch, Until, Lookup, Get Metadata, Invoke Pipeline, and dependency conditions (success/failure/completion) that double as error-handling paths.
• Schedules fire on a fixed cadence per item; event-based triggers (Activator/Real-Time hub) fire pipelines on OneLake or Azure Blob Storage file events, passing file name and path as trigger metadata.
• Pipeline parameters are read-only run inputs (@pipeline().parameters.x); variables are mutable run state (@variables('x')); expressions compose functions like concat, utcnow, formatDateTime, and activity outputs.
• Notebooks receive values via base parameters overriding a designated parameters cell, and return results via notebookutils.notebook.exit, read as @activity('Name').output.result.exitValue.
• %%configure lets a pipeline parameterize a notebook's Spark session and default Lakehouse, so one notebook serves dev and prod.
• Metadata-driven orchestration (Lookup + ForEach + parameterized activities) beats copying near-identical pipelines; new sources become control-table rows.
• notebookutils.notebook.runMultiple runs a DAG of notebooks in one shared Spark session — cheaper than many pipeline Notebook activities, at the cost of coarser per-step monitoring and retries.

Ingest and transform data

Fabric Loading Patterns: Full, Incremental, and Dimensional Model Loads

• Full loads rebuild the target every run — simple and idempotent, right for small or change-opaque sources; incremental loads process only changed rows and are mandatory at scale.
• A watermark load filters the source on a high-water-mark column and stores the new maximum after each run — taken from the extracted data, not the clock — but it cannot detect hard deletes.
• Change Data Capture surfaces inserts, updates, and deletes from the source's log; apply change sets to Delta tables with MERGE, deduplicating per key first so multiple matches don't fail the merge.
• Dimensional preparation means declaring the fact grain, splitting facts from dimensions, conforming shared dimensions, and joining facts to dimensions through surrogate keys.
• SCD Type 1 overwrites and loses history; Type 2 expires the current row and inserts a new surrogate-keyed row so facts keep the attribute values that were true at the time.
• Load dimensions before facts, and handle late-arriving dimension members by inserting inferred placeholder rows that a later Type 1 update completes.
• Streaming loading patterns are append-only through bronze and silver medallion layers; row-level updates only reappear at gold aggregation — never at the landing edge.

Batch Ingestion in Fabric: Data Stores, Shortcuts, Mirroring, and Pipelines

• Store choice is workload plus language: Lakehouse for Spark and files, Warehouse for full T-SQL DML and multi-table transactions, Eventhouse for telemetry queried with KQL, semantic model as the BI layer — and the Lakehouse SQL analytics endpoint is read-only.
• Tool choice follows persona and scale: Dataflows Gen2 for low-code Power Query users, notebooks for large or complex code-first transforms, T-SQL for Warehouse-centric SQL teams, KQL for shaping event data in an Eventhouse.
• Shortcuts are zero-copy pointers — internal to other OneLake items, external to ADLS Gen2 or Dataverse; table shortcuts need Delta format, and access still honors the target's permissions.
• Mirroring keeps a near-real-time, read-only Delta replica of Azure SQL Database, Cosmos DB, or Snowflake in OneLake with no pipelines to build; it replaces watermark-and-merge extracts for supported sources.
• Pick the access pattern by ownership of freshness: shortcut = read the source live, mirror = managed replica, pipeline copy = scheduled snapshot you control and can transform on the way in.
• Pipelines copy and orchestrate but don't transform row internals — a metadata-driven ForEach + parameterized Copy activity feeding notebooks or stored procedures is the canonical batch design.
• GROUP BY, groupBy().agg(), and summarize ... by are the same operation in T-SQL, PySpark, and KQL; denormalizing with joins is the standard silver-to-gold step.
• Dedupe with row_number/arg_max keeping the latest row per key, quarantine bad records instead of silently dropping them, and absorb late-arriving data with lookback windows over an idempotent MERGE load.

Streaming Data in Fabric: Eventstreams, Spark, and Real-Time Intelligence

• Match the engine to the workload: Eventstream for no-code capture and routing, Spark structured streaming for code-first complex transformation into Delta tables, KQL in an Eventhouse for interactive time-series analytics, dashboards, and alerting.
• Eventstreams connect sources (Azure Event Hubs, Azure IoT Hub, CDC, custom endpoints) through no-code operators — filter, manage fields, aggregate, group by, join, union, expand — to destinations: Eventhouse, Lakehouse, derived stream, custom endpoint, or Activator.
• Native tables give the fastest KQL performance for data ingested into the Eventhouse; OneLake shortcuts query external Delta/Parquet data in place without copying it, at lower performance.
• Query acceleration indexes and caches a OneLake shortcut in the background for near-native query performance without duplicating the data — at extra capacity cost; standard shortcuts stay cheap but read files at query time.
• Spark structured streaming needs a checkpointLocation for fault tolerance, withWatermark to bound late data and state, and the right output mode: append for new finalized rows, complete to rewrite an aggregation, update for changed rows only.
• In KQL, summarize with bin() buckets events into fixed intervals, make-series plus series_* functions handle time-series analysis and anomaly detection, update policies transform data at ingestion, and materialized views keep aggregates fresh.
• Window choice is scenario-driven: tumbling = fixed and non-overlapping (each event in exactly one window), hopping = fixed and overlapping on a schedule, sliding = fixed but emitting only when contents change, session = variable length ended by an inactivity gap.

Monitor and optimize an analytics solution

Monitor Fabric Items: Monitoring Hub, Refresh History, and Activator Alerts

• The Monitoring hub is the cross-workspace triage surface for job run history — pipelines, Dataflow Gen2, notebooks, Spark job definitions, and semantic model refreshes.
• Pipeline diagnostics live at the activity level: status, input/output JSON, error codes, retries, and rerun-from-failed-activity.
• Dataflow Gen2 refresh history breaks each run down by table and activity, and identifies whether the failure was in evaluation, staging, or the load to the destination.
• For Spark, the Monitoring hub tells you a run failed; cell output and driver/executor logs tell you why it errored; the Spark UI (jobs, stages, tasks) tells you why it was slow.
• Semantic model refresh history distinguishes scheduled from on-demand refreshes, and model settings include built-in failure notification emails.
• Activator (Reflex) rules turn Real-Time hub sources — including Fabric job events — into alerts and actions: email, Teams messages, or starting a Fabric item.
• Continuous items (Eventstreams, mirroring) are monitored in their own editors, and capacity-wide utilization and throttling questions resolve to the Capacity Metrics app.
• Prevent silent downstream failures by orchestrating dependent steps in one pipeline instead of racing independent schedules.

Identify and Resolve Fabric Errors: Pipelines, Dataflows, Notebooks, and More

• Classify every failure into one of five classes — authentication, connectivity, schema/data, resource, or logic — and fix the class, not the symptom.
• Pipelines fail at the activity level: read the error details and evaluated input JSON, reserve retry policies for transient classes, and rerun from the failed activity.
• Dataflow Gen2 diagnosis is table-by-table and phase-by-phase: evaluation errors point at the query or source, load errors point at destination mapping, types, or permissions.
• Notebook out-of-memory splits by location: driver OOM means data was pulled local (collect, toPandas); executor OOM means skew or oversized partitions — repartition before scaling up.
• Eventhouse ingestion problems are diagnosed with .show ingestion failures; permanent failures (mapping, schema, format) require a change, transient ones are retried by the service.
• Eventstreams fail at their edges: source connectivity/auth on the way in, schema mismatch with the Eventhouse or Lakehouse destination on the way out — use data preview, data insights, and runtime logs.
• The Lakehouse SQL analytics endpoint is read-only — write attempts fail by design; Warehouse conflicts under snapshot isolation are resolved by retrying and separating writers.
• Shortcut errors are always about the target or the credentials: external shortcuts fail on their connection, internal shortcuts on the caller's permission at the target, and moved or deleted targets break the path.

Optimizing Performance in Fabric: Lakehouse, Spark, Warehouse and Queries

• The small-file problem is the top Lakehouse performance killer: fix it with OPTIMIZE bin compaction, keep it fixed with scheduled maintenance, and reclaim old files with VACUUM.
• V-Order is a write-time Parquet optimization that dramatically speeds Direct Lake and SQL reads — the standard trade of slightly slower writes for much faster reads.
• Partition on low-cardinality filter columns; use Z-Order or liquid clustering for high-cardinality columns where partitioning would create too many small files.
• Spark tuning = right-sized partitions, broadcast small join tables to avoid shuffles, cache only reused DataFrames, fix skew (salting, AQE), and enable the native execution engine.
• Warehouse tuning = accurate statistics, never SELECT *, realistic narrow data types, result-set caching for repeat queries, and minimizing data movement between nodes.
• Pipelines get faster with ForEach parallel execution and batch count, Copy activity parallelism and partitioned reads, staging where the destination needs bulk loads, and incremental instead of full loads.
• Eventhouse caching policy controls speed (hot SSD window), retention policy controls existence; materialized views and update policies shift query cost to ingestion time.
• Direct Lake reads Delta tables in OneLake with near-Import speed and no refresh copies — choose it when data is in OneLake; its performance depends on compacted, V-Ordered tables.