DP-700 flashcards

Implement and manage an analytics solution

• Fabric admins control tenant features and create domains; capacity admins decide whether workspaces may create custom pools; workspace admins configure Spark, OneLake and Airflow settings within those guardrails.
• Starter pools use fixed medium, pre-warmed nodes and start in seconds; custom pools let you pick node size, autoscale and single-node, but take about three minutes to start.
• Pools hold compute only: libraries, runtime and Spark properties live in environments, and session timeout lives on the Jobs tab.
• 'Customized workspace pools' (capacity) gates custom pools; 'Customize compute configuration for items' (workspace) gates item-level compute overrides — and Airflow custom pools.
• High concurrency is on by default and shares a session only for the same user, default lakehouse, compute configuration and libraries; five notebooks per session by default.
• The Jobs tab sets the 20-minute default session timeout, optimistic vs reserved-maximum job admission, and a maximum lifetime for user jobs (system maintenance jobs are exempt).
• A default domain picks up unassigned and future workspaces of listed admins without overriding existing assignments; domains never change access.
• Shortcut caching covers GCS, S3, S3-compatible and gateway shortcuts (1–28 days, reset on access, files over 1 GB skipped); OneLake diagnostics logs access to a lakehouse on the same capacity.
• Airflow starter pools shut down after 20 idle minutes; custom Airflow pools stay on until paused; DAG packages go in Apache Airflow requirements.

• Git integration = source control for item definitions; deployment pipelines = staged promotion between workspaces — neither one ever moves data.
• Only the development workspace is Git-connected; test and production receive content exclusively through the deployment pipeline.
• Git integration works with cloud providers only (Azure DevOps Services, GitHub, GitHub Enterprise Cloud); only a workspace Admin connects, using Read on the repo, and committing needs write access in Git.
• Commit pushes selected changes and is disabled while updates are pending; Update pulls the whole branch; Undo reverts selected items to the last sync. Conflicts resolve with Accept incoming changes / Keep current content, or in Git via Checkout new branch.
• Branch out leaves the source workspace alone and builds a new workspace from the last commit; Checkout new branch and Switch branch re-point the current workspace and need Admin unless the Contributor branch-change setting is on.
• A Git-connected Warehouse is committed as a database project of .sql files; SqlPackage Publish applies a dacpac, Script only generates the diff for a specific target, Extract makes a dacpac, Export makes a bacpac with data.
• Pipelines have 2–10 stages fixed at creation; a workspace on Fabric capacity joins only one pipeline; deploying needs pipeline admin plus Contributor in both workspaces, and works in either direction.
• Deployment rules (data source, parameter, default lakehouse) exist for specific items only, are set on a non-first target stage by the item owner, and apply from the next deployment; Direct Lake models need a data source rule.
• Variable libraries give per-stage values to pipelines, notebooks, Dataflow Gen2, shortcuts and copy jobs; value sets deploy, but the active value set is chosen once in each stage.

• 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 workspace settings, Git connection and other Admins; Members add people, share and edit OneLake security roles; Contributors build but cannot manage access; Viewers read through SQL, and through OneLake only via a OneLake security role.
• Sharing always grants Read (connect only); "Read all data using SQL" adds ReadData, "Read all data using Apache Spark" adds ReadAll — withhold ReadAll to keep consumers behind SQL security.
• T-SQL RLS filters every user, including dbo and workspace Admin/Member/Contributor, so exemptions go in the predicate; CLS makes SELECT * fail when any column is not granted.
• RLS, CLS, OLS and masking live only in the SQL engine: Spark, OneLake API and shortcut reads bypass them, and warehouse SQL security is never carried into OneLake.
• Dynamic data masking hides values from users without UNMASK; Admin/Member/Contributor see real values, ALTER ANY MASK manages masks without revealing them, and it is not encryption.
• OneLake security roles are grant-only, can carry row and column rules, and restrict only Viewers and Read/ReadAll users; DefaultReader gives ReadAll users everything until you narrow it.
• The SQL analytics endpoint defaults to delegated identity (SQL permissions, RLS, masking); user identity mode enforces OneLake roles, ignores table GRANTs, drops masking, and switching it deletes SQL roles and cancels queries.
• Sensitivity labels apply to whole items and protect only supported exports (not .csv); Certified means quality-reviewed, Master data means core reference data, and both need admin-specified users.
• Fabric audit logs (Purview) record control-plane activity; OneLake diagnostics, enabled per workspace to a same-capacity lakehouse, records data access through OneLake APIs from that point on.

• 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.
• A pipeline's control flow comes from activities (ForEach, If Condition, Switch, Until, Lookup, Get Metadata, Invoke pipeline, Fail) joined by Upon success, failure, completion and skip conditions.
• Parameters are read-only inputs supplied by the caller for a run; variables are mutable state the run itself sets and appends to.
• Metadata-driven design — a Lookup over a control table feeding a ForEach of parameterized activities — beats duplicating near-identical pipelines.
• Notebooks cannot use the on-premises data gateway: land gateway-only data with a Copy activity, then transform it in a notebook if the logic needs code.
• Dataflow Gen2 can also call REST APIs and write lakehouse Delta tables, so the Dataflow-vs-notebook choice turns on logic complexity and skills; user data functions are lightweight Python, not a distributed engine.
• A pipeline can have several schedules (up to 20), each passing its own parameter values; weekly recurrence selects weekdays and several times; failure notifications email on failed scheduled runs only.
• Event triggers are saved as Activator (Reflex) items and fire on OneLake, Azure Blob Storage, job or workspace item events; read event data with the null-safe @pipeline()?.TriggerEvent?.FileName.
• Lookup output is output.firstRow with "First row only" selected and output.value (an array) when cleared; a Notebook's value is output.result.exitValue, a string that needs int() for numeric tests.
• ForEach Sequential and Batch count control loop concurrency; loops cannot nest (use a child pipeline); Append Variable collects results into an array variable, which Set Variable cannot do.
• Multiple dependencies are ANDed and later steps are skipped after a failure: handle errors from the last step's On failure + On skip paths, and add a Fail activity when a handled failure must still fail the run.
• Invoke pipeline passes values only through child parameters; without Wait on completion the child runs in parallel and its failure does not fail the parent.
• runMultiple runs a notebook DAG in one shared session; high concurrency for pipelines shares a session across Notebook activities with the same session tag (same user, lakehouse, compute and libraries; up to five notebooks, then another session starts); parameterized %%configure in the first cell sets a per-run default lakehouse.

Ingest and transform data

• Full loads replace 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 between the old watermark and a new upper bound read from the source before the copy, and saves that bound only on the copy's success path — never the clock, never the last row written.
• Late-committing transactions need a lookback window, and the overlap needs a keyed MERGE so re-read rows are not duplicated.
• Copy job incremental copy tracks state itself and resumes from the last successful run (append, merge, overwrite); Dataflow Gen2 incremental refresh replaces whole date buckets per query.
• Deduplicate a change set to the last change per key by source sequence number before MERGE; Delta change data feed reports only changes made to the Delta table itself.
• Deletes need CDC, soft-delete handling in the MERGE, WHEN NOT MATCHED BY SOURCE for full snapshots, or key reconciliation.
• SCD Type 1 overwrites, Type 2 adds a surrogate-keyed version with effective dates, Type 3 keeps one previous value, Type 0 never changes; facts find their Type 2 version by business key and fact date.
• Streaming loads are append-only; corrections arrive as new events, and an Eventhouse materialized view (take_any by key) deduplicates across batches where an update policy cannot.

• 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. A Warehouse can read Lakehouse and mirrored tables by three-part name and INSERT ... SELECT the result into its own tables, but cannot modify them.
• 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 that read the target live: only Tables-section shortcuts to Delta data are discovered as tables; deleting the shortcut leaves the data, but deleting files inside it deletes them at the target; internal shortcuts authorize the calling user at the target.
• Firewalled ADLS Gen2/Blob needs trusted workspace access (workspace identity, resource instance rule, F SKU, Storage Blob Data role); shortcut caching (1-28 days, S3/S3-compatible/GCS/gateway, files up to 1 GB) cuts egress.
• Mirroring keeps a one-way, read-only Delta replica of supported databases with no pipelines; the capture mechanism differs by source (Cosmos DB needs continuous backup and uses no RUs), open mirroring takes change files for any other source, and an on-premises SQL Server needs a data gateway.
• 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 (Sequential off, Batch count capped for fragile sources) feeding notebooks or stored procedures is the canonical batch design.
• GROUP BY, groupBy().agg(), and summarize ... by are the same operation; WHERE filters rows before grouping and HAVING filters groups after; store sums and counts (not averages) in pre-aggregated tables; join SCD2 dimensions on the current or point-in-time version to avoid fan-out.
• Dedupe with row_number (with a unique tie-breaker) or arg_max, never dropDuplicates after orderBy or rank with ties; reduce the batch before MERGE; remember aggregates ignore nulls; scope lookback rewrites with replaceWhere.

• Match the engine to the workload: Eventstream for no-code capture, enrichment, and routing; Spark structured streaming for code-first logic, environment Python packages, and stateful work into Delta tables; KQL in an Eventhouse for interactive time-series analytics, dashboards, and alerting. Dataflow Gen2 is batch and a Warehouse is neither an Eventstream destination nor a Real-Time Dashboard source.
• Eventstream operators: Filter, Manage fields, Aggregate, Group by (windows), Expand (one row per array element), Union, Join (stream-to-stream or against Lakehouse reference data with periodic refresh, inner or left outer), and SQL. Filtering before an Eventhouse needs the event-processing-before-ingestion mode.
• Derived streams are published to the Real-Time hub for other teams to subscribe to; a custom endpoint destination serves external apps over Event Hubs, AMQP, or Kafka.
• Native tables give the fastest, freshest KQL queries; OneLake shortcuts query external Delta data in place; Query acceleration indexes and caches a shortcut for near-native speed, costed by its hot caching period, with external-table limits (no materialized views) and a freshness delay unsuitable for seconds-level alerts.
• Spark structured streaming needs a stable, per-query checkpointLocation (offsets, commit log, state); withWatermark guarantees data within the threshold is counted and bounds state; append is the default and needs a watermark for aggregations, and the Delta sink takes only append or complete — upsert changing aggregates with foreachBatch and MERGE.
• In KQL, bin() omits empty buckets while make-series fills them (with an explicit from/to range) for series_* functions such as series_decompose_anomalies; update policies live on the target table and need IsTransactional for transform-and-discard; materialized views (arg_max for latest-per-key) precompute aggregates, unlike stored functions or the results cache.
• Window choice is scenario-driven: tumbling = fixed and non-overlapping, hopping = fixed and overlapping on a clock-aligned schedule (size = lookback, hop = frequency), sliding = any span of the given length, session = variable length ended by an inactivity gap and grouped by the entity.

Monitor and optimize an analytics solution

• The Monitoring hub (Monitor hub in the new preview) is the cross-workspace triage list for job runs; it drills into item run details but does not analyse capacity or monitor streams.
• Activity input shows the configuration with expressions already evaluated; output shows results. Copy activity details give rows read/written, throughput and per-stage duration, which Delta table history cannot.
• Succeeded with fewer rows written than read means fault tolerance skipped rows; they are recorded only when logging is enabled.
• Rerun the entire pipeline, from the failed activity, or from a selected earlier activity when that earlier step produced the bad data. Use the Gantt view for overlaps and Upstream/Downstream runs for which run triggered which.
• An On fail path that succeeds turns a failure into a Succeeded run; end it with a Fail activity if run-level alerts must still fire. On completion runs after successes too.
• Semantic model refresh history records the type (Scheduled, On demand, Via API), keeps retries inside one entry, and splits Data from Query Cache; refresh failure emails cover scheduled refreshes only, four consecutive failures disable a schedule, and two months without visits pause it.
• Eventstream backlog and watermark delay mean lag; conversion, deserialization and runtime errors mean failures. Capacity throttling escalates from interactive delay to interactive rejection to background rejection.
• Alert with schedule failure notifications for scheduled runs, Activator on Job events for every run, an Outlook/Teams activity for activity-level error details, and workspace monitoring plus a KQL queryset for workspace-wide custom rules.
• Prevent silent downstream failures by orchestrating dependent steps (load, dataflow, model refresh) in one pipeline instead of racing separate schedules.

• 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 and the input/output JSON, pair a realistic timeout (default 12 hours) with retries for transient faults, rerun from the failed activity, and drill into a child notebook through its Spark application detail page.
• Expressions must match the output shape (Lookup firstRow vs value, Get Metadata childItems); dependencies on several activities are ANDed, a skipped leaf takes its parent outcome, and one handler on the last activity with On fail + On skip catches a whole chain.
• Dataflow Gen2 diagnosis is table-by-table and phase-by-phase: fix unconvertible values in the query (type + replace errors), and give a gateway outbound TCP 1433 for staged reads.
• Notebook driver OOM means data was pulled local (Arrow speeds the transfer but rows still land on the driver; pandas UDFs keep logic distributed); libraries belong in environments, pipeline parameters need exact names, and HTTP 430 means Spark capacity is exhausted.
• Eventhouse: .show ingestion failures (14 days) gives ErrorCode, source path and Permanent/Transient; permanent failures need a fix and re-ingestion, non-transactional update policies fail silently into the target, and broken JSON mappings ingest nulls with no failure.
• Eventstreams fail at their edges: a regenerated Event Hubs key breaks the stored connection while producers keep sending; schema changes break the destination.
• Warehouse: snapshot isolation only, table-level write conflicts (24556/24706) need retries; constraints are NONCLUSTERED NOT ENFORCED and types like money, datetime and nvarchar need replacements. The SQL analytics endpoint is read-only.
• Shortcut errors depend on the identity used: external and delegated shortcuts fail for everyone on the connection, passthrough shortcuts on the caller's target permission; a not-found while other shortcuts on the same credential still work means the target path moved.

• Small active files are the top Lakehouse killer: OPTIMIZE compacts them; optimize write and auto compaction prevent them; auto compaction runs inside the writing job, so strict-latency streams use a separately scheduled OPTIMIZE.
• V-Order speeds Direct Lake and SQL reads but is off by default in new workspaces (writeHeavy profile); table property and session settings affect future writes only, and OPTIMIZE ... VORDER rewrites existing files.
• VACUUM removes unreferenced data files (7-day default, which caps time travel); Delta log retention (30 days) trims log entries and is a separate setting.
• Partition only on low-cardinality filter columns with partitions near the target file size; otherwise cluster. Z-Order and liquid clustering only take effect when OPTIMIZE runs, and liquid clustering cannot be combined with partitioning.
• Spark: broadcast small sides (auto below 10 MB, hint above), let AQE split skewed sort-merge joins, salt skewed aggregations, cache reused DataFrames, enable the native engine (watch for fallback), and use high concurrency sessions when start-up dominates.
• Warehouse: batch loads instead of trickle inserts, fresh statistics after big loads, no SELECT *, right-sized and matching types (size Spark strings), and judge queries on warm runs.
• Result set caching is on by default and returns stored results for identical repeat queries, but runtime constants like GETDATE(), small tables and large results make a query ineligible; result_cache_hit shows 0/1/2.
• Pipelines: copy less (incremental), then copy faster: ForEach batch count for many items, intelligent throughput optimization for compute, copy parallelism plus Dynamic range partitioning for one big table.
• Eventstream throughput level sizes the stream; filter per destination. In Eventhouse, caching is speed and retention is existence; update policies reshape each batch, materialized views keep aggregations current.
• Direct Lake on SQL endpoints falls back to DirectQuery on views and guardrail breaches (5,000 files per table at F64 and F128); compact tables and materialize views instead of scaling up.

Yes — the whole DP-700 deck is free and needs no account, as is every DP-700 revision lesson. Create a free account to save progress and drill practice questions; full-length mock exams are a one-time unlock per certification.