Microsoft Fabric DP-600 (Analytics Engineer Associate) – Complete Prep Guide

A consolidated, scenario-based study guide covering all four DP-600 exam skill areas plus deeper dives into Real-Time Intelligence, Mirroring, capacity management, and T-SQL/Warehouse specifics. 30 scenario questions total.

Section 1: Plan, Implement & Manage a Solution for Data Analytics

Q1. Your organization wants separate environments for development, test, and production Fabric items, with controlled promotion between them. How do you set this up?

A: Use Fabric Deployment Pipelines. Create a pipeline with Dev/Test/Prod stages, assign a workspace to each stage, and deploy items (reports, semantic models, lakehouses, notebooks) forward through the pipeline. Deployment rules let you swap parameters per stage — e.g., pointing Prod reports at the Prod lakehouse automatically instead of Dev.

Q2. A team wants their Fabric items in source control with proper branching, and to sync changes back into a workspace. What feature do you use, and what’s a key limitation to mention?

A: Git integration (Azure DevOps or GitHub) connected at the workspace level. Each supported item is serialized to a folder with a .json/.platform definition, enabling PRs and version history. Limitation worth naming in interviews: not every Fabric item type has full Git support yet, and binary/large semantic model changes can be harder to diff meaningfully than code-based items like notebooks.

Q3. Finance wants to guarantee predictable Fabric capacity costs during month-end close, when workloads spike heavily. What are your levers?

A:

• Capacity scaling — resize the Fabric capacity SKU (F64, F128, etc.) up before month-end, down after.
• Workspace-to-capacity assignment — isolate heavy workloads onto their own capacity so they don’t starve other workspaces.
• Surge protection / smoothing — understand Fabric’s background operation smoothing (spreading spiky background jobs like Spark/refreshes over a rolling window) so short bursts don’t trigger throttling.
• Monitor via the Fabric Capacity Metrics app to see CU consumption trends before deciding to scale.

Q4. A security team wants to know who can access what across all Fabric workspaces in the tenant, and enforce that certain data never leaves the org’s certified sources. What do you point them to?

A: Microsoft Purview integration (sensitivity labels, data lineage, and the Fabric admin portal’s tenant settings) plus workspace roles (Admin/Member/Contributor/Viewer) for access control, and endorsement (Certified/Promoted) to signal trusted, governed datasets. Sensitivity labels applied at the item level flow through to Power BI reports built on that data.

Section 2: Prepare and Serve Data

Q5. You need to ingest data from an on-prem SQL Server into Fabric on a schedule, with minimal code. What do you use?

A: Data Factory pipelines in Fabric (Copy Activity) with an on-premises data gateway installed to bridge the on-prem SQL Server to the cloud. Schedule the pipeline via triggers. For transformation-heavy loads with a low-code UI, Dataflows Gen2 is the alternative — pipelines are better for orchestration and scheduling across multiple activities, Dataflows Gen2 for Power Query–style transforms.

Q6. Your team wants to query data that physically lives in an existing ADLS Gen2 storage account, without copying it into OneLake. How?

A: OneLake shortcuts. A shortcut is a reference/pointer to external data (ADLS Gen2, S3, another Lakehouse) that makes it appear as if the data is inside your Lakehouse, without duplicating storage or requiring a copy job. Shortcuts support both internal (cross-workspace OneLake) and external (ADLS/S3) sources.

Q7. Should you use a Lakehouse or a Warehouse for a given workload — how do you decide, and what’s the trade-off an interviewer wants to hear?

A:

The nuance to say out loud: both ultimately store data as Delta Parquet in OneLake, so a Warehouse and a Lakehouse’s SQL endpoint can query each other — the decision is about which engine and developer experience fits the team, not about the underlying storage format being different.

Q8. A notebook-based transformation is timing out at large scale. What Spark-level and Fabric-level levers do you check first?

A:

• Spark pool/session sizing — is autoscale or the node size sufficient for the data volume?
• Partitioning — is the source data partitioned in a way that lets Spark parallelize reads, or is it one giant file causing a single-task bottleneck?
• V-Order optimization — Fabric’s default write optimization for Delta tables; confirm it’s not disabled if downstream Direct Lake/SQL performance matters.
• Caching intermediate results if the notebook reuses the same DataFrame multiple times instead of recomputing it.

Section 3: Implement and Manage Semantic Models

Q9. Your semantic model queries a 500M-row Fact table directly via Direct Lake, but one visual keeps falling back to DirectQuery and getting slow. How do you diagnose it?

A: Use the Direct Lake fallback / Execution Metrics (available via the semantic model’s monitoring or Log Analytics integration) to see which queries fell back and why. Common fallback triggers: calculated columns/tables not supported in Direct Lake, certain complex DAX patterns, or the table exceeding row/column guardrails for the capacity SKU. Fix by moving logic upstream (into the Lakehouse/Warehouse as a proper column) instead of a model-level calculated column.

Q10. Business users keep building duplicate versions of the same “Total Sales” measure across different reports. How does Fabric help standardize this?

A: Build a single shared semantic model (a certified/endorsed one), and have report authors connect via Live Connection or DirectQuery to the semantic model instead of importing raw tables again. Pair this with a documented, endorsed model so it shows up as “Certified” in the OneLake data hub, steering users toward reuse instead of reinvention.

Q11. You need row-level security that also affects the Lakehouse SQL endpoint, not just Power BI reports. What’s different about implementing RLS at this layer vs semantic-model RLS?

A: RLS defined inside a Power BI semantic model only protects report/DAX queries against that model. To secure the underlying SQL endpoint or Warehouse itself, you need T-SQL-based security — CREATE SECURITY POLICY with a filter predicate function (Row-Level Security in the Warehouse), which applies regardless of which tool queries the data. If both a semantic model and direct SQL access exist, both layers need RLS configured — one doesn’t automatically protect the other.

Section 4: Explore and Analyze Data

Q12. A report author wants to add a running total and a rank, purely inside the visual, without writing a model-level measure. What Fabric/Power BI feature fits?

A: Visual calculations (introduced for Power BI in Fabric) — DAX-like formulas written directly on a visual, referencing its own aggregated fields (e.g., RUNNINGSUM([Sales]), RANK), without needing to create a reusable model measure. Useful for one-off visual-specific logic that doesn’t belong in the shared semantic model.

Q13. Leadership wants natural-language Q&A over the semantic model, plus AI-assisted DAX/report authoring. What do you enable and what’s the prerequisite?

A: Copilot in Power BI / Fabric. Prerequisite: the semantic model needs good metadata — clear table/column names, descriptions, and marking a proper Date table — because Copilot’s answer quality depends heavily on how well-modeled and well-documented the semantic model is, not just on enabling the feature.

Real-Time Intelligence

Q14. IoT sensor data is streaming in continuously and needs to be queryable within seconds, with alerts if a value crosses a threshold. What Fabric components fit?

A: Eventstreams to ingest the streaming data (from Event Hubs, IoT Hub, or Kafka), landing it into a KQL Database (Kusto). Query it with KQL for near-real-time analytics, and use Data Activator (Reflex) to trigger alerts/actions when values cross thresholds — without writing custom polling code.

Q15. Someone asks: “Why not just use a Lakehouse for streaming data instead of a KQL Database?” How do you answer?

A: A Lakehouse is optimized for batch/micro-batch analytical workloads on Delta Parquet; a KQL Database is purpose-built for high-velocity time-series and log-style data with sub-second ingestion-to-query latency and native time-series functions (series_decompose, make-series, etc.). You can land streaming data into a Lakehouse via Spark Structured Streaming, but for true real-time dashboards and anomaly detection, KQL is the better-fit engine — this is a “right tool for the latency requirement” answer the exam likes.

Mirroring

Q16. Your organization has a large Azure SQL Database and Cosmos DB used operationally. Leadership wants near-real-time reporting on this data in Fabric without building and maintaining ETL pipelines. What feature solves this?

A: Fabric Mirroring. It continuously replicates data from supported sources (Azure SQL DB, Cosmos DB, Snowflake, and others) into OneLake as Delta tables, automatically and near-real-time, without you building a Copy Activity/pipeline. It’s a no-ETL, low-maintenance path specifically meant for “I just want this operational data queryable in Fabric,” as opposed to Data Factory pipelines, which fit more complex/transformational ingestion.

Capacity, Throttling & Monitoring

Q17. A Fabric capacity (F64) is showing “interactive delay” and eventually rejects new interactive requests during a heavy afternoon of report usage plus a large Spark job. What’s happening, and how do you respond in the moment vs long-term?

A: This is capacity throttling — Fabric uses Capacity Units (CUs) shared across all workloads on that capacity, and once consumption exceeds the SKU’s limit, background jobs get delayed/smoothed first, then interactive operations get throttled if pressure continues.

• In the moment: pause or reschedule non-urgent background jobs (large Spark/dataflow refreshes), or temporarily scale the capacity up.
• Long-term: move heavy Spark/data engineering workloads to a separate capacity from interactive Power BI usage, so one doesn’t starve the other, and set up capacity metrics alerts to catch this before users notice.

Q18. How do you explain “smoothing” and “bursting” in Fabric capacity to someone who’s never seen the concept?

A: Fabric doesn’t charge/consume CUs the instant a job runs — many background operations (like large dataflow or Spark jobs) have their CU consumption smoothed across a rolling 24-hour window instead of hitting all at once. Bursting lets a capacity temporarily exceed its baseline CU limit for short spikes, borrowing against future capacity — up to a limit — before throttling kicks in. The practical takeaway for architecture decisions: a capacity can absorb short spikes gracefully, but sustained overuse will eventually throttle interactive users.

Q19. A workspace admin wants a single place to see all running/failed Spark jobs, pipeline runs, and dataflow refreshes across the tenant. What do they use?

A: The Fabric Monitoring Hub — a centralized view across workspaces (that the user has access to) showing status, duration, and history of pipeline runs, notebook/Spark jobs, dataflow refreshes, and semantic model refreshes, without needing to open each item individually.

Semantic Model Performance & Refresh

Q20. A large Import-mode semantic model takes over an hour to refresh nightly, but only ~2% of rows actually change per day. How do you fix this within Fabric?

A: Incremental refresh. Define a RangeStart/RangeEnd partitioning policy on the Date column so only the recent partition (e.g., last few days) refreshes fully, while historical partitions stay untouched unless explicitly reprocessed. This can be combined with Detect data changes (using a watermark column) to skip refreshing partitions where nothing actually changed, further cutting refresh time.

Q21. When would you choose Import mode over Direct Lake, even in 2026 with Direct Lake being the “modern default” for Fabric-native data?

A: Import still wins when:

• The model needs calculated columns/tables or complex DAX patterns not supported in Direct Lake (forcing fallback anyway).
• Data volume is small enough that Import’s performance is already excellent and refresh cost is trivial.
• The source isn’t in OneLake/Delta format and isn’t worth mirroring/shortcutting just for this one model.

The exam-favored framing: Direct Lake is best when data is already Delta-native and large-scale with a need for freshness; Import is still legitimate for smaller, stable models where its DAX flexibility outweighs Direct Lake’s storage-mode benefits.

Data Sharing & Governance

Q22. An external partner organization needs read access to one specific table in your Lakehouse, but must not see anything else in the workspace. What’s the least-privilege way to do this?

A: OneLake data sharing (external sharing at the item/table level) or a shortcut scoped to just that table, rather than granting workspace-level Viewer access. Workspace roles grant access to everything in the workspace; item-level/table-level sharing keeps the blast radius limited to exactly what’s needed — the least-privilege answer the exam is testing for.

Q23. How does endorsement (Promoted vs Certified) actually affect governance, beyond just a badge in the UI?

A: Endorsement is a discoverability and trust signal in the OneLake data hub — Certified items typically require admin/designated-certifier approval and signal “this is the source of truth,” steering users toward reusing a governed semantic model instead of building a parallel one. It doesn’t change security/access by itself (that’s still workspace roles + item permissions + RLS) — endorsement and security are separate, complementary controls, which is a distinction the exam likes to test.

Quick recap table for the blog

Use this table as a quick-reference carousel slide or a cheat-sheet download lead magnet for the blog.

Section 5: T-SQL & Warehouse Scenarios

Q24. A colleague writes a stored procedure in the Fabric Warehouse the same way they would in SQL Server, using a scalar UDF and SELECT INTO. It fails. What’s likely going on?

A: The Fabric Warehouse is built on a distributed engine (Polaris) and doesn’t support the full T-SQL surface area of SQL Server/Azure SQL DB. Notable gaps to know for the exam: no scalar user-defined functions, no SELECT INTO (use CREATE TABLE AS SELECT / CTAS instead), no triggers, and limited support for some cursor-based/procedural patterns. The exam-favored mindset: Warehouse is T-SQL-compatible for querying, not a 1:1 SQL Server replacement for procedural code.

Q25. You need to create a new table populated from a complex query, and want it to be fast and log-minimal in the Warehouse. What statement do you use?

A: CTAS — CREATE TABLE AS SELECT. It’s the primary pattern in Fabric Warehouse for materializing query results into a new table efficiently, replacing the SELECT INTO pattern from SQL Server since that isn’t supported.

CREATE TABLE dbo.SalesSummary AS
SELECT Region, SUM(SalesAmount) AS TotalSales
FROM dbo.Sales
GROUP BY Region;

Q26. Two Warehouses in different workspaces need to be joined in a single query for a cross-department report. Is this possible, and how?

A: Yes — cross-database queries within Fabric Warehouse/SQL analytics endpoints are supported using three-part naming (database.schema.table), as long as both databases are in the same Fabric capacity/tenant context. This is a notable Fabric-specific capability worth contrasting with classic Azure SQL DB, where cross-database queries historically needed elastic query or linked servers.

SELECT s.Region, s.SalesAmount, b.BudgetAmount
FROM SalesWarehouse.dbo.Sales AS s
JOIN BudgetWarehouse.dbo.Budget AS b
    ON s.Region = b.Region;

Q27. A compliance requirement says certain users must only see rows for their assigned department when querying the Warehouse directly via SSMS or any SQL client — not just through Power BI. How do you implement this at the SQL layer?

A: T-SQL Row-Level Security using CREATE SECURITY POLICY with an inline table-valued function as the filter predicate:

CREATE FUNCTION dbo.fn_DeptFilter(@Dept AS NVARCHAR(50))
RETURNS TABLE
AS RETURN SELECT 1 AS Result
WHERE @Dept = USER_NAME() OR IS_MEMBER('DeptAdmins') = 1;

CREATE SECURITY POLICY DeptSecurityPolicy
ADD FILTER PREDICATE dbo.fn_DeptFilter(Department)
ON dbo.Sales
WITH (STATE = ON);

This enforces security at the engine level regardless of client tool — the same distinction from Part 2’s semantic-model-RLS-vs-SQL-RLS question, but now with the actual syntax.

Q28. A nightly load into the Warehouse needs to insert, update, and delete rows in a target table based on a staging table, in a single efficient operation. What T-SQL pattern do you use?

A: MERGE, supported in Fabric Warehouse for exactly this upsert pattern:

MERGE INTO dbo.DimCustomer AS target
USING dbo.StagingCustomer AS source
ON target.CustomerID = source.CustomerID
WHEN MATCHED THEN
    UPDATE SET target.Region = source.Region
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CustomerID, Region) VALUES (source.CustomerID, source.Region)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

Interviewers may ask you to flag a caveat: MERGE can have subtle behavior differences and performance considerations at scale compared to separate INSERT/UPDATE/DELETE statements — worth testing on real data volume before assuming it’s always the fastest option.

Q29. Someone asks why their Warehouse table’s statistics seem out of date, hurting query plans after a large load. What do you check?

A: Fabric Warehouse can auto-create and auto-update statistics, but after very large bulk loads it’s worth explicitly checking/updating them:

UPDATE STATISTICS dbo.Sales;

Also confirm AUTO_CREATE_STATISTICS behavior wasn’t disabled and that the load didn’t bypass normal paths in a way that skipped stats refresh. This is a “know the levers, don’t just say restart the query” answer the exam rewards.

Q30. A query against the Warehouse SQL endpoint of a Lakehouse returns stale results right after a Spark job wrote new data. Why, and what do you do?

A: The SQL analytics endpoint over a Lakehouse has a sync process from the Delta Lake files to the SQL-queryable metadata, and there can be a short lag after Spark writes complete. If it’s still stale beyond the expected sync window, a manual refresh of the SQL endpoint metadata (via the UI “Refresh” or the endpoint’s sync mechanism) resolves it. Worth stating explicitly in an answer: this lag is a known characteristic of Lakehouse SQL endpoints and isn’t a bug — it’s a distinction from a Warehouse, where writes via T-SQL are immediately queryable with no separate sync step.

Warehouse vs Lakehouse SQL Endpoint — one more pass