DP-600 Practice Test

Implementing Analytics Solutions Using Microsoft Fabric

DP-600 overview

  • 200 practice questions
  • 32 flashcards
  • 4 study guide topics
  • 39 key terms

What you'll study

  • Plan, implement, and manage a solution for data analytics13%
  • Prepare and serve data43%
  • Implement and manage semantic models22%
  • Explore and analyze data22%

The Microsoft Certified: Fabric Analytics Engineer Associate credential is an intermediate-level certification for professionals who design, create, and deploy enterprise-scale data analytics solutions on Microsoft Fabric. It sits at the intersection of data engineering and data analysis: candidates prepare and enrich data for analysis, secure and maintain analytics assets, and implement and manage semantic models. Day to day, that means working with lakehouses, warehouses, notebooks, dataflows, pipelines, semantic models, and reports, while applying analytics best practices such as version control and deployment. The role also requires partnership with solution architects, data engineers, data scientists, AI engineers, database administrators, and Power BI analysts, plus hands-on skill in SQL, DAX, and PySpark. The exam, DP-600, is proctored, lasts 100 minutes, may include interactive components, and is scored pass or fail against a 700 threshold; it is offered in English, Japanese, Chinese (Simplified), German, French, Spanish, and Portuguese (Brazil). Skills are weighted unevenly, with Prepare and serve data taking the largest share (40–45%), followed by semantic models and exploratory analysis at 20–25% each, and solution planning at 10–15%. Preparation should combine the official study guide with hands-on Fabric experience, since the exam favors practical implementation over recall.

Common mistakes

Designs a solution around Direct Lake, deployment pipelines, or a multi-GB semantic model while leaving the workspace on Pro/Shared capacity, and never checks the capacity requirement.

Candidates study the feature list for each item in isolation (model mode, ALM tooling) and treat the hosting capacity as an afterthought, because in smaller environments 'it just worked' when everything was in one Pro workspace.

What to do instead

Make capacity selection the first decision, not the last. Enumerate the features the scenario requires (Direct Lake, large semantic models, XMLA write, Fabric notebooks/pipelines) and map each to what it demands of the workspace; note that Pro workspaces impose size limits on semantic models and that Direct Lake requires a Fabric capacity. If the scenario says 'we only have Pro', rule out the Fabric-only options before scoring them.

Treats Git integration and deployment pipelines as competing alternatives and picks one, so the candidate cannot describe how a change travels from a developer workspace to test and production with environment-specific values.

Both are presented as ALM features in Fabric, so candidates memorise two definitions instead of learning how the features compose; they also confuse a Git branch with a pipeline stage.

What to do instead

Learn the division of labour: Git integration connects a workspace to a branch for per-item versioning and collaboration, while deployment pipelines promote entire workspaces across stages and apply rules (data source rules, parameter rules) to swap environment-specific values. In a combined answer, source control happens via Git and environment promotion happens via pipelines.

Answers a 'consolidate data from ADLS Gen2/S3/another workspace without duplicating storage or waiting for a refresh' scenario with a Copy activity or Dataflow Gen2.

Shortcuts are a OneLake-specific concept with no equivalent in the classic Azure data stack, so candidates fall back to the ingestion pattern they already know and default every source-integration question to copy.

What to do instead

Memorise the shortcut decision rule: a shortcut is a zero-copy pointer to data that already exists (ADLS Gen2, S3, other lakehouses/warehouses, Dataverse) and only makes data available, it does not transform it. Choose copy activities, pipelines, or dataflows when you need transformation, format conversion to Delta, schema enforcement, or scheduling. Also recall that shortcuts can point across workspaces, which is the standard answer for 'share without copying'.

Plans to run INSERT/UPDATE/DELETE or MERGE statements against the SQL analytics endpoint of a lakehouse to clean up or correct data.

The SQL analytics endpoint accepts T-SQL and exposes tables and views exactly like a database, so candidates assume it is a full T-SQL surface and treat it as a warehouse.

What to do instead

Anchor the rule: the lakehouse SQL analytics endpoint is read-only over Delta tables; it exposes data and lets you create views and manage permissions, but data changes go through Spark notebooks, dataflows, or pipelines writing Delta. When the scenario demands full T-SQL DML, stored procedures, and multi-table transactions, that is the trigger to choose a warehouse rather than a lakehouse.

Writes Delta tables repeatedly from notebooks and then blames capacity size when queries get slow, never proposing compaction, V-Order, or a partitioning strategy.

Small-file accumulation and layout problems are invisible in the notebook authoring experience, so nothing in the workflow prompts the candidate to think about table maintenance; optimisation questions get answered with 'scale up the SKU'.

What to do instead

Attach a maintenance answer to every notebook-write scenario: OPTIMIZE to compact small files, V-Order to improve read performance for the engine, partitioning aligned to how the data is filtered, and a scheduled maintenance step in the pipeline. If a question mentions degraded query performance after incremental loads, suspect file proliferation before capacity.

Selects Direct Lake by default on the grounds that it 'combines import performance with DirectQuery freshness', without verifying that the sources are Delta tables in OneLake and that the model will actually stay in Direct Lake mode.

Direct Lake is heavily marketed and candidates memorise the headline benefit but not the preconditions or fallback behaviour, so they answer freshness questions with Direct Lake even when the scenario hints at a source that will force a fallback.

What to do instead

Learn the preconditions and the fallback triggers: Direct Lake reads Delta tables in OneLake in a Fabric workspace; user queries fall back to DirectQuery when the model cannot be held in memory or when the model uses features incompatible with Direct Lake. For small data with a batch refresh window, Import is usually the safer, more defensible answer; reserve Direct Lake for large OneLake Delta sources that need near-real-time freshness.

Builds a snowflake or one very wide table and sets relationships to bi-directional cross-filter 'so the filters flow', rather than modelling a proper star schema.

Bi-directional filtering silences the symptom (filters 'not working', ambiguity errors avoided by luck) faster than restructuring the model, and candidates are wary of a question that seems to demand more modelling work than necessary.

What to do instead

Default to dimensions plus a fact table with single-direction one-to-many relationships from dimension to fact; restrict bi-directional to deliberate many-to-many bridge designs and be able to say why. Resolve genuine ambiguity with correct granularity and keys, and use inactive relationships plus USERELATIONSHIP for role-playing dimensions such as order date versus ship date.

Writes a calculated column containing SUM(...) to show a total, or wraps the fact table in FILTER(ALL(Fact), ...) inside a measure, instead of using CALCULATE with filter modifiers.

Spreadsheet and table habits carry over, and row context versus filter context is the most common conceptual gap; a calculated column visibly produces the right-looking numbers on small samples.

What to do instead

Separate the two: static row-level values are calculated columns, aggregations that must respond to slicers are measures. Use CALCULATE with boolean filters or REMOVEFILTERS/KEEPFILTERS rather than iterating the fact table, use DIVIDE for safe division, and confirm the result set in DAX Query View before committing to a design.

Ships a model where time intelligence measures return blanks and proposes adding missing dates into the fact table or writing offset logic by hand to compensate.

The requirement for a contiguous, marked date table is a modelling prerequisite rather than a DAX rule, so no error points at the date table; the failure appears only as silent blanks in the report.

What to do instead

Always create a dedicated date dimension with exactly one row per calendar date, no gaps, of Date data type, mark it as the date table, and relate it to each fact. Then use the built-in time intelligence functions (TOTALYTD, SAMEPERIODLASTYEAR, DATEADD); if measures still return blanks, check for gaps, duplicate dates, or a relationship that is missing or inactive.

When asked to verify a measure or hand back a result set for review, creates a throwaway report page or exports to Excel instead of querying the semantic model with DAX.

Candidates prepare for this exam as report builders, so their instinct is to prove a number visually; DAX Query View and EVALUATE-based querying are newer surfaces that get skipped during study.

What to do instead

Use DAX Query View to run EVALUATE queries directly against the model, preview and edit measures in place, and inspect results without building visuals. Practise the typical tasks: return a table of values for a measure across a dimension, define and test a new measure with the DEFINE syntax, and use INFO functions to inspect model metadata.

Assumes natural-language Q&A and Copilot will answer business questions over any model, including a raw bronze-layer model with technical column names such as CUST_ID or AMT1.

Copilot and Q&A are presented as turnkey features, so candidates treat them as purely a licensing switch and overlook that answer quality is a function of model quality.

What to do instead

Link NL querying to model hygiene: readable table and column names, hidden key and technical columns, synonyms, a clean star schema with relationships already defined, and a filled-in linguistic schema. In a scenario where Q&A gives poor results, propose model cleanup and synonyms before proposing a different tool, and remember Copilot also depends on the feature being enabled in the right capacity and tenant.

Exam requirements can change. Confirm current details with the exam provider.

DP-600 practice

200 questions. Each one lists the choices, the correct answer, and an explanation. With JavaScript, this list opens one question at a time. The checker at the top of this page saves progress in this browser when it is available.

  1. Question 1Plan, implement, and manage a solution for data analytics

    A service principal must call the Microsoft Fabric REST APIs to create workspaces. What must a Fabric administrator configure?

    1. Enable the 'Service principals can use Fabric APIs' tenant setting for a security group. Correct answer.
    2. Enable the 'Users can create Fabric items' tenant setting for the service principal's group.
    3. Assign the principal as a capacity admin on the Fabric capacity in the Azure portal.
    4. Grant the principal the Fabric Administrator role in the Microsoft 365 admin center and consent to the Fabric API.

    Explanation

    Fabric API access for service principals is controlled by a developer tenant setting that must be enabled and scoped to a security group containing the principal. After that, the principal still needs workspace or item permissions to perform operations. Administrative Microsoft 365 roles are unrelated to this API gate.

  2. Question 2Plan, implement, and manage a solution for data analytics

    Which workspace role is required to add or remove workspace members and to delete the workspace?

    1. Contributor
    2. Admin Correct answer.
    3. Member
    4. Viewer

    Explanation

    Workspace roles are Admin, Member, Contributor, and Viewer. Only Admin can manage membership and delete the workspace, while Members can add users to lower roles and share items.

  3. Question 3Plan, implement, and manage a solution for data analytics

    A team wants to create lakehouses, notebooks, and pipelines in a new workspace. What is required for those items to work?

    1. Each team member must hold a Power BI Pro license and nothing else.
    2. Each team member must hold a Power BI Premium Per User license and nothing else.
    3. The tenant must have a Microsoft 365 E5 subscription assigned to the team.
    4. The workspace must be assigned to a Fabric capacity or a Power BI Premium capacity. Correct answer.

    Explanation

    Fabric workloads such as lakehouses, notebooks, pipelines, and warehouses run on capacity, so the workspace must be backed by Fabric capacity or Power BI Premium capacity. Pro and PPU licenses govern Power BI content consumption rather than Fabric compute.

  4. Question 4Plan, implement, and manage a solution for data analytics

    A deployment pipeline moves a semantic model from Development to Test. The model must connect to a different database in each stage. What should you configure?

    1. A separate pipeline per stage so that items can be copied manually.
    2. A workspace-level parameter that is edited after each deployment.
    3. A Git branch per stage and merge the branches after each deployment.
    4. A deployment rule on the semantic model that sets the parameter value for the target stage. Correct answer.

    Explanation

    Deployment rules allow parameter values and data source connections to differ per stage so that one item definition can target stage-specific resources. Rules are defined per item and per target stage inside the pipeline.

  5. Question 5Plan, implement, and manage a solution for data analytics

    Which source control systems can a Fabric workspace be connected to through native Git integration?

    1. Any Git provider that exposes an HTTPS endpoint.
    2. GitHub repositories only.
    3. Azure DevOps Git repositories and GitHub repositories. Correct answer.
    4. Azure DevOps Git repositories only.

    Explanation

    Fabric Git integration natively supports Azure DevOps Git repositories and GitHub repositories connected at the workspace level. A workspace is connected to one repository and one branch at a time.

  6. Question 6Plan, implement, and manage a solution for data analytics

    A user must be able to apply the Certified endorsement to semantic models without being a Fabric administrator. What is required?

    1. Membership in the Fabric Administrator role.
    2. The certify permission granted by a Fabric admin. Correct answer.
    3. Contributor role on every workspace that contains semantic models.
    4. A sensitivity label applied to the model.

    Explanation

    Promoted endorsement can be applied by anyone with write permission on the item, but Certified endorsement requires the certify permission. A Fabric admin grants that permission through the Certification tenant setting to selected users or groups.

  7. Question 7Plan, implement, and manage a solution for data analytics

    What is the primary purpose of Fabric domains?

    1. They determine which capacity a workspace's items are billed to.
    2. They control which users may sign in to the Fabric portal.
    3. They group workspaces by business area for delegated governance. Correct answer.
    4. They replace workspaces as the security boundary for Fabric items.

    Explanation

    Domains group workspaces along business or data-mesh lines so that Fabric admins can delegate administration to domain admins. Workspaces remain the security and permission boundary, and capacity assignment is handled separately.

  8. Question 8Plan, implement, and manage a solution for data analytics

    Which statement describes an OneLake shortcut?

    1. It encrypts data at rest in the target lakehouse.
    2. It copies source data into OneLake and refreshes the copy on a schedule.
    3. It stores a reference to data in another location without copying it. Correct answer.
    4. It materializes a physical table in the lakehouse for each source file.

    Explanation

    A shortcut is a virtual pointer to data held in another OneLake location or in supported external storage such as ADLS Gen2 or Amazon S3. Because the data is not copied, no additional OneLake storage is consumed and no refresh schedule is needed.

  9. Question 9Plan, implement, and manage a solution for data analytics

    Users report slow interactive queries and several scheduled refreshes are being rejected during peak hours. Which tool should you open first to confirm that the capacity is being throttled?

    1. The lineage view of the affected workspace.
    2. The Monitoring hub activity list for the capacity.
    3. The Microsoft Fabric Capacity Metrics app. Correct answer.
    4. The Fabric admin REST API.

    Explanation

    The Capacity Metrics app reports utilization over time together with throttling events and the interactive versus background breakdown, which identifies what is overloading the capacity. Monitoring hub and lineage show item activity and dependencies rather than capacity pressure.

  10. Question 10Plan, implement, and manage a solution for data analytics

    A lakehouse is labeled 'Highly Confidential'. A new semantic model imports data from that lakehouse. What happens to the label?

    1. The downstream item inherits the label from the item it consumes. Correct answer.
    2. The downstream item is blocked from being created without a new label.
    3. Labels apply only to Power BI semantic models and reports, not to lakehouses.
    4. Every new item in the workspace receives the label automatically.

    Explanation

    Sensitivity labels propagate to downstream items that are built on labeled data, and when several labels apply the most restrictive one wins. This keeps protection consistent without manually labeling every derived item.

  11. Question 11Plan, implement, and manage a solution for data analytics

    A workspace must read from an ADLS Gen2 account that is protected by a firewall, without storing credentials in the item. Which workspace feature enables this?

    1. The workspace identity, a service principal attached to the workspace. Correct answer.
    2. The user account that workspace members use to sign in to the portal.
    3. The workspace Admin role, which is extended to manage data source access.
    4. The credentials stored on an on-premises data gateway bound to the workspace.

    Explanation

    A workspace identity is a service principal created for the workspace and used for scenarios such as trusted workspace access to firewall-protected ADLS Gen2. Access is granted to the identity in the storage account instead of to individual users.

  12. Question 12Plan, implement, and manage a solution for data analytics

    An administrator wants one view that lists recent notebook, pipeline, and refresh runs with their status across several workspaces. Which feature provides this?

    1. The OneLake explorer.
    2. The capacity settings page in the admin portal.
    3. The Monitoring hub. Correct answer.
    4. The workspace lineage view.

    Explanation

    The Monitoring hub shows recent activity across the workspaces a user can access, including notebook, pipeline, and refresh runs with status, duration, and submitter. Lineage and capacity settings pages serve different purposes.

  13. Question 13Plan, implement, and manage a solution for data analytics

    A Fabric admin wants capacity admins to control one specific tenant setting for their own capacities while the tenant default stays centrally managed. What should the admin configure?

    1. Assign the capacity admins the Fabric Administrator role.
    2. Create a security group and add it to each workspace as Admin.
    3. Delegate that tenant setting to capacity admins in the Fabric admin portal. Correct answer.
    4. Enable the setting tenant-wide and ask capacity admins to behave carefully.

    Explanation

    Individual tenant settings can be delegated so that capacity admins manage the setting within their own capacities while the tenant-level default remains under central control. Delegation is configured per setting rather than by granting a broader role.

  14. Question 14Plan, implement, and manage a solution for data analytics

    A third-party modeling tool must be able to read and write a semantic model through the XMLA endpoint. Which configuration is required?

    1. Enable the XMLA endpoints tenant setting for a shared-capacity workspace.
    2. Grant Build permission and connect with any client that can issue DAX queries.
    3. Enable the XMLA endpoints tenant setting for a capacity-backed workspace. Correct answer.
    4. Publish the model to a Pro workspace with the large model storage format enabled.

    Explanation

    XMLA endpoints are available only for workspaces on Fabric or Premium capacity, and the tenant setting that allows XMLA endpoints must be enabled. Read/write connections additionally require the write-enabled option and suitable permissions on the model.

  15. Question 15Plan, implement, and manage a solution for data analytics

    Which items does Fabric Git integration keep synchronized with the connected repository?

    1. Only semantic models and reports.
    2. Only items that were created after the workspace was connected to Git.
    3. Only the item types that Fabric Git integration supports. Correct answer.
    4. Every item in the workspace, including preview item types that lack source control support.

    Explanation

    Git integration tracks only the item types that support source control; other items remain in the workspace but are untouched by commit and update operations. The supported list grows over time and is documented per item type.

  16. Question 16Plan, implement, and manage a solution for data analytics

    Which approach best isolates development, test, and production content in Fabric?

    1. A single workspace with a folder for each environment.
    2. A separate capacity allocated to each developer.
    3. Separate workspaces per stage connected by a deployment pipeline. Correct answer.
    4. Git branches that report consumers open directly.

    Explanation

    Deployment pipelines promote content between workspaces that represent stages, so production content and permissions stay isolated from in-progress development. Folders and branches share the same workspace permissions and therefore do not isolate environments.

  17. Question 17Plan, implement, and manage a solution for data analytics

    A team needs full T-SQL DML support, multi-table transactions, and T-SQL security over Delta tables in OneLake. Which Fabric item should they choose?

    1. A lakehouse with shortcuts to the Delta files.
    2. A KQL database.
    3. A mirrored database.
    4. A warehouse. Correct answer.

    Explanation

    A Fabric warehouse exposes a full T-SQL surface with DML, multi-table transactions, and T-SQL security over Delta-formatted tables. A lakehouse T-SQL endpoint offers read and limited write capabilities rather than full transactional DML.

  18. Question 18Plan, implement, and manage a solution for data analytics

    A user must be able to query one semantic model but must not see any other item in the workspace. What is the least-privilege approach?

    1. Assign the Viewer role on the workspace.
    2. Share the individual semantic model with the user. Correct answer.
    3. Assign the Contributor role on the workspace.
    4. Add the user to the security group that the workspace uses.

    Explanation

    Sharing an individual item grants permission only on that item, so the rest of the workspace stays invisible to the user. Workspace roles grant access across all items in the workspace and are therefore broader than needed.

  19. Question 19Plan, implement, and manage a solution for data analytics

    Which statement describes the Microsoft Fabric trial?

    1. It grants a Power BI Pro license that does not expire.
    2. It can be shared with any number of users indefinitely.
    3. It provides unlimited capacity for twelve months.
    4. It provides a 60-day trial Fabric capacity for the individual user. Correct answer.

    Explanation

    The Fabric trial gives a single user a 60-day trial capacity that behaves like a Fabric capacity for building and testing items. At the end of the period the trial expires and can be converted into a paid capacity.

  20. Question 20Plan, implement, and manage a solution for data analytics

    A Fabric admin must allow only members of the DataPlatform security group to create Fabric items while everyone else continues to view existing content. What should the admin do?

    1. Remove the Viewer role from all other users.
    2. Scope the 'Users can create Fabric items' tenant setting to that security group. Correct answer.
    3. Set every workspace to read-only in workspace settings.
    4. Disable the Fabric trial for the whole tenant.

    Explanation

    The 'Users can create Fabric items' tenant setting can be enabled for everyone, scoped to specific security groups, or disabled. Scoping it to one group prevents creation by others while leaving read access to shared content unaffected.

  21. Question 21Plan, implement, and manage a solution for data analytics

    Which statement about assigning workspaces to Fabric domains is correct?

    1. Workspaces are assigned to domains by the capacity administrator.
    2. A workspace can be assigned to only one domain. Correct answer.
    3. A workspace can belong to several domains at the same time.
    4. A workspace must belong to a domain before it can use capacity.

    Explanation

    A workspace belongs to at most one domain, and workspaces that are never assigned fall under the default domain. Domain assignment is a governance grouping that is independent of capacity assignment.

  22. Question 22Plan, implement, and manage a solution for data analytics

    A team wants a continuously updated copy of an Azure SQL Database in OneLake without authoring pipelines or schedules. Which Fabric feature should they use?

    1. A OneLake shortcut to the Azure SQL Database.
    2. An eventstream with a custom application endpoint.
    3. Database mirroring. Correct answer.
    4. A copy activity pipeline that runs every five minutes.

    Explanation

    Mirroring continuously replicates a source database into OneLake as Delta tables with no pipeline or schedule to maintain. Shortcuts reference external storage that Fabric can read directly, which does not include relational database tables.

  23. Question 23Plan, implement, and manage a solution for data analytics

    Scheduled refreshes of a semantic model that reads from an on-premises SQL Server fail in the Fabric service, and on-premises connectivity is mandatory. What should you do?

    1. Create a OneLake shortcut to the on-premises SQL Server.
    2. Install a standard on-premises data gateway and add the SQL Server data source. Correct answer.
    3. Enable the XMLA endpoint on the workspace.
    4. Install a personal gateway on the author's machine and use it for all refreshes.

    Explanation

    A standard on-premises data gateway gives the Fabric service a managed, highly available path to on-premises sources and can be shared across users and items. Personal gateways are intended for single-user scenarios and are not appropriate for shared production refreshes.

  24. Question 24Plan, implement, and manage a solution for data analytics

    What does surge protection do on a Fabric capacity?

    1. It pauses billing for the capacity during peak hours.
    2. It automatically adds capacity units to the SKU whenever usage exceeds the purchased limit.
    3. It holds back background jobs while interactive usage is over capacity limits. Correct answer.
    4. It moves workloads to another capacity in the same region.

    Explanation

    Surge protection activates when interactive usage exceeds the capacity limits and delays or rejects background operations so that interactive queries and reports can still complete. Fabric does not scale capacity automatically as part of surge protection; scaling is a separate administrative action.

  25. Question 25Plan, implement, and manage a solution for data analytics

    You commit changes in a Fabric workspace, and a colleague later pushes commits to the same branch. The workspace still shows the older item definitions. What should you do?

    1. Export the items and import them again manually.
    2. Delete the workspace and recreate it from the branch.
    3. Disconnect and reconnect the workspace to the repository.
    4. Run Update from Git in the workspace source control pane. Correct answer.

    Explanation

    Update from Git pulls the current repository state into the workspace, including commits made by others. Disconnecting or recreating the workspace is unnecessary and would discard items that are not tracked by source control.

  26. Question 26Plan, implement, and manage a solution for data analytics

    A Fabric administrator pauses the Fabric capacity that backs several workspaces over a weekend. What happens to those workspaces while the capacity is paused?

    1. Items in those workspaces are unavailable and scheduled jobs fail. Correct answer.
    2. Only workspace administration is blocked; queries and notebooks keep running.
    3. Scheduled jobs queue up and run automatically once capacity resumes.
    4. Items stay queryable in read-only mode using cached results.

    Explanation

    All Fabric compute runs on a capacity, so pausing it stops the compute that items in its assigned workspaces depend on. Users cannot open or query the items during the pause, and background activities such as scheduled refreshes or pipeline runs fail rather than completing. When the capacity is resumed, the workspaces become available again, but work that was attempted during the pause is not replayed automatically.

  27. Question 27Prepare and serve data

    A Spark notebook in a Fabric lakehouse runs df.write.saveAsTable("sales"). In which format are the table's files stored?

    1. CSV files
    2. JSON Lines files
    3. Parquet files with no transaction log
    4. Delta Lake Correct answer.

    Explanation

    Lakehouse tables in Microsoft Fabric are always Delta tables: the write creates Parquet data files plus a Delta transaction log in the Tables area of OneLake. The transaction log provides ACID transactions, time travel, and schema enforcement, and it is what the SQL analytics endpoint reads.

  28. Question 28Prepare and serve data

    You must query Parquet files that already sit in an ADLS Gen2 account from a Fabric lakehouse, without copying or duplicating the data. What should you create?

    1. A dataflow that copies the files hourly
    2. An eventstream that ingests the files continuously
    3. A mirrored database of the storage account
    4. A shortcut to the ADLS Gen2 location Correct answer.

    Explanation

    A OneLake shortcut is a zero-copy reference that makes data in external storage appear as a folder in the lakehouse, so queries read the original files in place. No data movement or scheduled copy job is involved.

  29. Question 29Prepare and serve data

    Which semantic model storage mode reads Delta tables directly from OneLake without importing data and without translating queries to the source?

    1. Direct Lake mode Correct answer.
    2. Dual mode over the lakehouse
    3. DirectQuery over the SQL analytics endpoint
    4. Import mode with scheduled refresh

    Explanation

    Direct Lake loads Delta Parquet columns into memory on demand and queries them directly, so there is no import refresh and no query translation against the source engine. This gives near-import performance over large OneLake tables.

  30. Question 30Prepare and serve data

    In a medallion architecture, which layer typically holds validated, deduplicated, and conformed data that multiple downstream consumers can reuse?

    1. Bronze
    2. Gold
    3. Silver Correct answer.
    4. Landing zone

    Explanation

    The silver layer stores cleansed, deduplicated, and conformed data that is still reasonably normalized so several consumers can reuse it. Gold is the curated, business-ready layer, while bronze or landing holds raw ingested data.

  31. Question 31Prepare and serve data

    A user must be able to create lakehouses and notebooks in a workspace but must not be able to add or remove workspace members. Which workspace role should you assign?

    1. Contributor Correct answer.
    2. Viewer, which grants read-only access
    3. Admin, which manages permissions
    4. Member, which allows sharing items

    Explanation

    The Contributor role allows a user to create and edit items in the workspace but not to manage workspace membership or settings. Only Admins can add or remove members and change workspace permissions.

  32. Question 32Prepare and serve data

    What does running VACUUM on a Delta table do?

    1. Compacts many small files into a smaller number of larger files
    2. Recomputes column statistics used by the query optimizer
    3. Removes unreferenced files past the retention period Correct answer.
    4. Converts Parquet files into the Delta Lake format

    Explanation

    VACUUM physically deletes data files that are no longer referenced by the Delta log and are older than the configured retention threshold. Files newer than the retention window are protected so that readers and time travel are not broken.

  33. Question 33Prepare and serve data

    Which pipeline activity copies data from a source dataset to a destination dataset?

    1. Copy Correct answer.
    2. Set variable
    3. ForEach
    4. Lookup

    Explanation

    The Copy activity is the data movement activity in Fabric data pipelines; it reads a source dataset and writes to a sink dataset. Lookup, ForEach, and Set variable are control-flow activities that do not move data.

  34. Question 34Prepare and serve data

    Which endorsement level can only be granted by users that a Fabric administrator has authorized for that purpose?

    1. Master data
    2. Verified
    3. Promoted
    4. Certified Correct answer.

    Explanation

    Certification is the highest endorsement level and requires a user who has been granted certification rights by a Fabric administrator. Promotion is self-service and can be applied by any user with write permission on the item.

  35. Question 35Prepare and serve data

    A 2 TB Delta sales table has columns SaleId (unique), OrderDate (six years of dates), CustomerName, and Notes. Most queries filter on a date range. Which column should you use as the partition key?

    1. OrderDate Correct answer.
    2. SaleId
    3. CustomerName
    4. Notes

    Explanation

    Partition on a column that is frequently used in filters and has moderate cardinality, such as a date, so queries can skip entire folders. A unique column creates an enormous number of tiny partitions, and high-cardinality text columns produce the same problem.

  36. Question 36Prepare and serve data

    A lakehouse table is optimized with OPTIMIZE sales ZORDER BY (CustomerId). What is the main benefit?

    1. It enforces a unique constraint on the CustomerId column
    2. It removes duplicate CustomerId values from every file
    3. It converts CustomerId into a numeric surrogate key at read time
    4. Related rows are co-located so more files can be skipped Correct answer.

    Explanation

    Z-ORDER reorganizes data during OPTIMIZE so that rows with similar CustomerId values are clustered into the same files. Queries that filter on CustomerId can then use file statistics to skip most files instead of scanning the whole table.

  37. Question 37Prepare and serve data

    A pipeline must load only the rows that changed since the previous successful run. Which pattern should you implement?

    1. Full load with a truncate-and-reload step
    2. Keep a watermark value and filter the source by modified date Correct answer.
    3. Change Data Capture on the source table, read through the SQL analytics endpoint
    4. Use a ForEach activity with a fixed batch size

    Explanation

    The watermark pattern stores the last successfully loaded timestamp (for example in a pipeline variable or a control table) and filters the source by the modified date using that value. After a successful load, the watermark is updated so the next run starts where the last one ended.

  38. Question 38Prepare and serve data

    You configure a Dataflow Gen2 query with a lakehouse data destination. What is created in the lakehouse when the dataflow runs?

    1. A CSV file in the Files area only
    2. A Delta table Correct answer.
    3. A shortcut to the dataflow definition
    4. A SQL view over the source system

    Explanation

    Writing a Dataflow Gen2 output to a lakehouse creates a Delta table in the Tables area of the lakehouse. The table can then be queried from the SQL analytics endpoint, notebooks, or a semantic model.

  39. Question 39Prepare and serve data

    A source file gains a new column, and an append to an existing Delta table fails because of the schema mismatch. Which Spark option allows the append to add the new column to the table schema?

    1. optimizeWrite enabled
    2. overwriteSchema set to true
    3. autoCompact enabled
    4. mergeSchema set to true Correct answer.

    Explanation

    Setting mergeSchema to true during a write allows Delta to evolve the table schema by adding the new column instead of failing. overwriteSchema replaces the schema and is appropriate only when the whole table is being rewritten.

  40. Question 40Prepare and serve data

    You need near real-time replication of an Azure SQL Database into OneLake without building or scheduling pipelines. Which Fabric feature should you use?

    1. A Copy activity with a tumbling window trigger
    2. Dataflow Gen2 with a refresh every 15 minutes
    3. Mirroring Correct answer.
    4. An eventstream with a custom app source

    Explanation

    Database mirroring continuously replicates the source database into OneLake as Delta tables with near real-time latency and no pipeline authoring. The replicated tables are available to the SQL analytics endpoint and to Direct Lake models.

  41. Question 41Prepare and serve data

    A semantic model must import a small Product dimension but query a very large FactSales table in DirectQuery. What should you configure?

    1. A Direct Lake model that points at the lakehouse
    2. A live connection to a shared Power BI dataset
    3. Import mode with incremental refresh for FactSales
    4. A composite model with mixed storage modes Correct answer.

    Explanation

    A composite model allows different tables in the same model to use different storage modes, so the dimension can be imported while the fact table stays in DirectQuery. Relationships between the two storage modes are supported.

  42. Question 42Prepare and serve data

    Which T-SQL statement applies inserts, updates, and deletes from a staging table to a warehouse dimension table in a single pass?

    1. MERGE Correct answer.
    2. UPDATE with a correlated subquery
    3. TRUNCATE TABLE followed by INSERT
    4. INSERT INTO ... SELECT

    Explanation

    MERGE compares a source and a target on a join key and applies the matching insert, update, or delete actions in one statement. This is the standard way to implement upserts into a warehouse dimension.

  43. Question 43Prepare and serve data

    A user runs an INSERT against a lakehouse table through the SQL analytics endpoint and receives an error. What explains the failure?

    1. The endpoint presents Delta tables as read-only Correct answer.
    2. The lakehouse must be attached to a Premium capacity
    3. The table must be converted to Parquet first
    4. The user needs db_owner on the endpoint

    Explanation

    The SQL analytics endpoint exposes lakehouse Delta tables as read-only T-SQL objects; writes must go through Spark notebooks, dataflows, pipelines, or the lakehouse load tools. Granting a higher database role does not enable writes.

  44. Question 44Prepare and serve data

    A lakehouse item has a sensitivity label applied. What happens to a Power BI report built on a semantic model that reads that lakehouse?

    1. The report is automatically moved to another workspace
    2. Only the report owner sees a warning banner
    3. Downstream items inherit the same label Correct answer.
    4. The label is dropped because reports cannot inherit labels

    Explanation

    Sensitivity labels propagate downstream, so items built on labelled data, such as semantic models and reports, inherit the label. This keeps protection consistent across the data estate without manual labelling of each item.

  45. Question 45Prepare and serve data

    You must ingest continuous device telemetry and land it in a lakehouse table with minimal latency. Which Fabric item should you use?

    1. A Dataflow Gen2 with a scheduled refresh
    2. A notebook run by a tumbling window trigger
    3. A data pipeline with a 15-minute schedule
    4. An eventstream with a lakehouse destination Correct answer.

    Explanation

    An eventstream consumes streaming data from sources such as Event Hubs or IoT devices and can write it to a lakehouse destination as a Delta table. This provides continuous, low-latency ingestion rather than scheduled batches.

  46. Question 46Prepare and serve data

    You want consumers of a Direct Lake semantic model to see only data from specific folders within a lakehouse. Which feature should you use?

    1. SQL analytics endpoint permissions
    2. OneLake data access roles Correct answer.
    3. Row-level security in the semantic model
    4. Workspace roles assigned as Viewer

    Explanation

    OneLake data access roles grant or restrict access at the folder level inside a lakehouse, and Direct Lake respects those role assignments when reading Delta files. This lets you scope which tables a model or user can consume.

  47. Question 47Prepare and serve data

    You disable V-Order for a Spark session, write a table, then re-enable V-Order and append more rows. What is the result?

    1. The append fails because V-Order is a table-level property
    2. The table becomes unreadable by the SQL analytics endpoint
    3. Only the new files are V-Ordered Correct answer.
    4. The whole table is rewritten with V-Order applied

    Explanation

    V-Order is applied by the Fabric engines at write time, so the setting in effect during each write determines the layout of the files produced by that write. Previously written files keep their original layout until a rewrite such as OPTIMIZE occurs.

  48. Question 48Prepare and serve data

    A Direct Lake semantic model begins returning slower results after a large Delta table is updated, and queries fall back to DirectQuery. Which condition explains the fallback?

    1. The query or column count exceeds Direct Lake guardrails Correct answer.
    2. The model has not been refreshed for more than 24 hours
    3. The workspace is assigned to a Fabric trial capacity
    4. The Delta table was converted to Parquet without a transaction log

    Explanation

    Direct Lake serves queries from in-memory column segments, but falls back to DirectQuery when a query cannot be satisfied that way, for example when it exceeds capacity guardrails or uses unsupported features. Watching for fallback events lets you tune the model or capacity to keep queries in Direct Lake mode.

  49. Question 49Prepare and serve data

    A deployment pipeline promotes a semantic model from Dev to Test, but the Test copy must point at a different database. What should you configure?

    1. A separate workspace for every stage
    2. A sensitivity label on the semantic model
    3. An XMLA read/write endpoint for the workspace
    4. A deployment rule on the Test stage Correct answer.

    Explanation

    Deployment rules let you change a parameter or data source for a specific stage, such as pointing the Test copy at the Test database, while the same item definition moves through the pipeline. Rules are defined per stage on the supported item types.

  50. Question 50Prepare and serve data

    A Delta table receives many small files from frequent streaming writes. You want to compact the files and reclaim space from old versions immediately, but the retention period is 7 days. What is the correct expectation?

    1. Drop and recreate the table to reset the transaction log
    2. Run OPTIMIZE; VACUUM only removes files past the retention window Correct answer.
    3. Set delta.logRetentionDuration to zero and then run OPTIMIZE on the table
    4. Set VACUUM retention to zero hours so every previous version is deleted first

    Explanation

    OPTIMIZE compacts small files into larger ones, while VACUUM can only delete files that are unreferenced and older than the retention threshold, which is 7 days by default. Space from recently written versions is therefore released only after the retention window elapses.

  51. Question 51Prepare and serve data

    A semantic model imports a fact table containing 10 years of data, but only the most recent 30 days change. Which approach reduces refresh time while keeping all history queryable?

    1. Increase the refresh parallelism setting
    2. Use incremental refresh with RangeStart and RangeEnd parameters Correct answer.
    3. Switch the table to DirectQuery
    4. Split the table into one table per calendar year and refresh each in turn

    Explanation

    Incremental refresh partitions the table by the RangeStart and RangeEnd parameters and refreshes only the partitions that fall in the refresh window, leaving older partitions untouched. History remains available in the model while each refresh processes only recent data.

  52. Question 52Prepare and serve data

    Which storage format does a Fabric lakehouse use for tables created in the Tables section?

    1. JSON documents indexed by the lakehouse SQL analytics endpoint
    2. Apache Avro files with a separate metadata database
    3. Delta Lake tables (Parquet files plus a transaction log) Correct answer.
    4. CSV files with an accompanying schema definition

    Explanation

    Lakehouse tables are Delta Lake tables: Parquet data files plus a _delta_log transaction log kept in OneLake. Both the Spark runtime and the SQL analytics endpoint read that same Delta format, which is why one table can be queried from multiple engines.

  53. Question 53Prepare and serve data

    You need to run Spark code on a recurring schedule without an interactive notebook session. Which Fabric item should you create?

    1. A Spark Job Definition Correct answer.
    2. A Dataflow Gen2 query
    3. An Eventstream
    4. A Copy activity in a pipeline

    Explanation

    A Spark Job Definition packages a main definition file plus referenced libraries and runs it against a lakehouse on a schedule or on demand. It exists precisely for unattended Spark workloads that do not need an interactive session.

  54. Question 54Prepare and serve data

    Which workspace role can create and edit items but cannot share items or grant permissions to others?

    1. Contributor - creates and edits items, no sharing Correct answer.
    2. Member - creates items and shares them with others
    3. Admin - full control including permissions
    4. Viewer - read-only access to items

    Explanation

    Contributor can create, edit, and delete workspace items and run pipelines, but cannot share items or manage workspace access. Sharing items and adding users requires the Member or Admin role.

  55. Question 55Prepare and serve data

    What does a OneLake shortcut do when it is created in a lakehouse?

    1. It references data in another location without copying it Correct answer.
    2. It creates a semantic model over the source data
    3. It copies the source files into OneLake and continuously synchronizes them
    4. It imports only the table schema into the lakehouse without any data

    Explanation

    A shortcut is a symbolic link stored in OneLake that points to data held elsewhere, such as another lakehouse, ADLS Gen2, or S3. Reads are served from the original location, so no data is duplicated and no copy job is required.

  56. Question 56Prepare and serve data

    Which authoring experience is used to build Dataflow Gen2 queries in Fabric?

    1. Spark notebook with PySpark
    2. Power Query Online Correct answer.
    3. SSMS connected to the warehouse
    4. T-SQL editor in the SQL analytics endpoint

    Explanation

    Dataflow Gen2 is authored in Power Query Online, the same low-code experience used across Microsoft data products. It generates M queries and can publish results to lakehouses, warehouses, and other destinations.

  57. Question 57Prepare and serve data

    Where does a lakehouse expose managed tables and where do raw files belong?

    1. Only in the Files section as .parquet files
    2. In the Tables section; raw files stay in Files Correct answer.
    3. In the SQL analytics endpoint only, not in OneLake
    4. In the warehouse item that is created with the lakehouse

    Explanation

    The lakehouse has a Tables section for managed Delta tables and a Files section for unmanaged files of any format. Tables created by Spark or Dataflow Gen2 appear under Tables and are automatically available through the SQL analytics endpoint.

  58. Question 58Prepare and serve data

    What is the defining characteristic of a Direct Lake semantic model in Fabric?

    1. It translates every query into T-SQL against the SQL analytics endpoint
    2. It imports a compressed copy of the Delta files into the model at refresh
    3. It requires an on-premises data gateway for every query
    4. It reads Delta files directly from OneLake without importing data Correct answer.

    Explanation

    Direct Lake loads column segments from Delta Parquet files in OneLake on demand, so there is no import and no scheduled data copy. Queries are served by the VertiPaq engine against those files, which gives import-like performance over large tables.

  59. Question 59Prepare and serve data

    You need a pipeline to run automatically every night at 2:00 AM. What should you configure on the pipeline?

    1. Manual refresh of the semantic model
    2. A schedule trigger on the pipeline Correct answer.
    3. Event-based trigger on file arrival
    4. A tumbling window trigger with a 24-hour window

    Explanation

    A schedule trigger runs a pipeline at fixed times and recurrence, such as daily at 02:00. It is the correct mechanism for time-based orchestration and can be set to a specific time zone.

  60. Question 60Prepare and serve data

    You create an ADLS Gen2 shortcut in a lakehouse and enable caching on it. What is the effect?

    1. The source files are deleted after the first read
    2. Cached copies of shortcut data are stored in OneLake and refreshed on a schedule Correct answer.
    3. Caching disables the shortcut and converts the data into a managed Delta table
    4. The shortcut becomes read-only in the source storage account

    Explanation

    Shortcut caching keeps a copy of the external files in OneLake so Fabric engines do not have to reach across the network for every read. The cache is refreshed periodically, which trades some freshness for faster Spark and SQL reads.

  61. Question 61Prepare and serve data

    Which query returns the contents of the Delta table sales as it existed at version 5?

    1. SELECT * FROM sales VERSION AS OF 5 Correct answer.
    2. SELECT * FROM sales WITH (SNAPSHOT = 5)
    3. SELECT * FROM sales HISTORY 5
    4. SELECT * FROM sales AT VERSION 5

    Explanation

    Delta time travel uses the VERSION AS OF (or TIMESTAMP AS OF) clause after the table name to read a prior snapshot. The transaction log retains version history, so the query returns the table exactly as it was at that version.

  62. Question 62Prepare and serve data

    A notebook writes a DataFrame using mode('overwrite') together with option('overwriteSchema','true'). What is the result on the existing Delta table?

    1. The table schema is replaced and the data is overwritten Correct answer.
    2. The write fails because Delta never allows schema changes on overwrite
    3. Only rows with matching columns are written and others are dropped
    4. The existing schema is kept and new columns are silently ignored

    Explanation

    Overwrite mode replaces the table contents, and overwriteSchema additionally allows the table schema to be replaced by the schema of the incoming DataFrame. Without that option, overwriting with an incompatible schema fails.

  63. Question 63Prepare and serve data

    What does enabling schemas on a lakehouse allow you to do?

    1. It removes the Files section
    2. It allows tables to be organized into schemas Correct answer.
    3. It requires the SQL analytics endpoint to be recreated
    4. It converts the lakehouse into a warehouse

    Explanation

    With schemas enabled, lakehouse tables can be grouped into named schemas instead of all landing in a single flat namespace, and dbo is the default schema. This makes it easier to separate layers such as bronze, silver, and gold within one lakehouse.

  64. Question 64Prepare and serve data

    Which T-SQL statement is designed to bulk load Parquet or CSV files from a folder into a Fabric warehouse table?

    1. SELECT INTO
    2. COPY INTO Correct answer.
    3. BULK INSERT
    4. OPENROWSET

    Explanation

    COPY INTO reads files from OneLake or Azure storage and loads them into a warehouse table in a single high-throughput statement. It is the recommended ingestion pattern for file-based loads into a warehouse.

  65. Question 65Prepare and serve data

    What is the purpose of enabling staging on a Dataflow Gen2 query?

    1. It caches the dataflow definition so other workspaces can reuse the queries
    2. It converts the dataflow into a pipeline
    3. It stores a copy of the destination table for backup
    4. It writes intermediate results to a lakehouse to improve performance Correct answer.

    Explanation

    Staging materializes intermediate query results in a lakehouse so that later steps do not repeatedly re-run upstream transformations against the source. This reduces the number of passes over source systems and improves reliability and speed for complex queries.

  66. Question 66Prepare and serve data

    A Delta table is partitioned by OrderDate. A query filters on OrderDate = '2024-05-01'. What is the main benefit of the partitioning?

    1. Delta automatically clusters rows by OrderDate at write time
    2. The query runs in Direct Lake mode
    3. Only the files in the matching partition need to be read Correct answer.
    4. The query is rewritten to use the SQL analytics endpoint cache

    Explanation

    Partitioning physically separates data into folders per partition value, so a filter on the partition column lets the engine skip every non-matching folder. This is partition pruning and it reduces both files scanned and data read.

  67. Question 67Prepare and serve data

    You enable change data feed on a Delta table. What does this allow downstream processes to do?

    1. Enforce schema validation on new writes
    2. Recover deleted files after VACUUM
    3. Read row-level changes between table versions Correct answer.
    4. Query only the most recent version of the table and nothing else

    Explanation

    Change data feed records row-level inserts, updates, and deletes per version so consumers can read the delta of changes between two versions. This supports incremental downstream processing without full reloads.

  68. Question 68Prepare and serve data

    A pipeline must write output to a folder whose path includes the date supplied when the pipeline is run. Which approach should you use?

    1. A separate pipeline for each date
    2. Dynamic content referencing a pipeline parameter Correct answer.
    3. A hard-coded path in each activity
    4. An event-based trigger that reads the file name and full folder path

    Explanation

    Dynamic content expressions can reference pipeline parameters at run time and substitute them into activity properties such as folder paths. This keeps a single pipeline reusable for any date passed by the caller or schedule.

  69. Question 69Prepare and serve data

    When a Dataflow Gen2 query writes to a lakehouse table, which update methods can you choose?

    1. Merge or upsert
    2. Append or replace Correct answer.
    3. Insert or delete
    4. Truncate only

    Explanation

    The lakehouse destination in Dataflow Gen2 supports appending rows to an existing table or replacing the table contents. Replace drops and recreates the table, while append adds the new rows to the existing data.

  70. Question 70Prepare and serve data

    A Copy activity must load source columns into destination columns with different names. Where is this configured?

    1. In the Mapping settings of the Copy activity Correct answer.
    2. In the destination lakehouse's SQL analytics endpoint
    3. In the pipeline's parameters
    4. In the source dataset schema only

    Explanation

    The Mapping tab of the Copy activity explicitly pairs each source column with a destination column, which is how renamed columns are handled. Without explicit mapping, columns are matched by name and mismatches cause failures or missing values.

  71. Question 71Prepare and serve data

    In Dataflow Gen2, what does query folding mean?

    1. Transformations are pushed to the source as a native query Correct answer.
    2. Multiple queries are merged into one dataflow
    3. The dataflow runs only when the destination is refreshed
    4. The query results are folded into a single Parquet file

    Explanation

    Query folding translates Power Query transformations into a native query that the source system executes, so filtering and aggregation happen close to the data. Steps that cannot be translated, such as custom logic, break folding and force processing in the Power Query engine.

  72. Question 72Prepare and serve data

    A Delta table has a high-cardinality CustomerId column used in most filters, and traditional partitioning does not perform well. Which Delta feature should you use?

    1. ZORDER BY on every column
    2. Bucketing by CustomerId with 5000 buckets
    3. Partitioning by CustomerId
    4. Liquid clustering keyed on CustomerId Correct answer.

    Explanation

    Liquid clustering uses clustering keys that let Delta skip irrelevant files without creating a folder per value, so it suits high-cardinality columns. The keys can be changed later without rewriting the entire table layout.

  73. Question 73Prepare and serve data

    A lakehouse contains a shortcut to an ADLS Gen2 folder of Parquet files that has no Delta transaction log. A Direct Lake semantic model points to that shortcut. What is the outcome?

    1. Direct Lake cannot use it because the source is not a Delta table Correct answer.
    2. The shortcut is converted to Delta automatically on first query
    3. The model imports the Parquet files during refresh and works normally without changes
    4. Performance degrades but queries succeed through DirectQuery

    Explanation

    Direct Lake requires Delta tables because it uses the transaction log to identify the current set of Parquet files. Plain Parquet folders without a Delta log cannot be used and must be converted to Delta tables first.

  74. Question 74Prepare and serve data

    In a Fabric warehouse you need one T-SQL query that joins a warehouse table with a table in a lakehouse in the same workspace. What should you use?

    1. A four-part name pointing to the OneLake DFS endpoint
    2. OPENROWSET over the Delta folder with a shared access signature
    3. A linked server to the lakehouse
    4. Three-part naming to the lakehouse SQL analytics endpoint Correct answer.

    Explanation

    Cross-database queries in a warehouse use three-part naming of the form database.schema.table, and the lakehouse's SQL analytics endpoint is addressable as a database in the same workspace. This lets a single T-SQL statement join warehouse and lakehouse tables.

  75. Question 75Prepare and serve data

    A scheduled process must parse deeply nested JSON with a third-party Python library and write the result as a Delta table. Which approach is appropriate?

    1. An eventstream with a transformation
    2. A Copy activity with a JSON source
    3. A notebook run by a pipeline Correct answer.
    4. A Dataflow Gen2 query with a custom connector

    Explanation

    Notebooks run Spark and can install and import third-party Python libraries, so they can handle custom parsing logic that Power Query cannot express. A pipeline adds the orchestration needed to run the notebook on a schedule.

  76. Question 76Prepare and serve data

    A user can open a lakehouse that contains a shortcut to a table in another workspace, but querying through the shortcut returns an access error. What is the most likely cause?

    1. Shortcuts can only be queried by workspace admins
    2. The lakehouse schemas are disabled
    3. The shortcut must be refreshed manually before it can be queried
    4. The user has no Read permission on the target item Correct answer.

    Explanation

    A shortcut stores only a pointer, so reads are evaluated against the source item and the caller needs permission on that source. Granting access to the lakehouse that hosts the shortcut does not grant access to the data it points to.

  77. Question 77Prepare and serve data

    A team needs to query Delta tables in a Fabric lakehouse by using T-SQL without copying the data. Which lakehouse feature should they use?

    1. SQL analytics endpoint Correct answer.
    2. Files folder for raw documents
    3. Eventstream
    4. Spark job definition

    Explanation

    A lakehouse automatically exposes a SQL analytics endpoint that presents Delta tables as read-only T-SQL objects. The endpoint queries the same OneLake data, so no copy is required.

  78. Question 78Prepare and serve data

    Fabric warehouse tables are stored in OneLake in which format?

    1. JSON documents
    2. Delta Parquet files Correct answer.
    3. CSV files
    4. SQL Server MDF files

    Explanation

    A Fabric warehouse stores its tables in OneLake as Delta Parquet, which lets Fabric engines read the same data. It does not use SQL Server MDF files or plain CSV or JSON as the table store.

  79. Question 79Prepare and serve data

    Which Fabric item provides a low-code Power Query experience for shaping data before it is loaded?

    1. KQL database for real-time queries
    2. Dataflow Gen2 Correct answer.
    3. Notebook
    4. Pipeline

    Explanation

    Dataflow Gen2 provides Power Query Online for low-code data transformation and can load results into Fabric destinations. Notebooks and pipelines require more code or orchestration, and KQL databases serve real-time queries.

  80. Question 80Prepare and serve data

    What is the main benefit of a OneLake shortcut in a lakehouse?

    1. It converts CSV files to Delta tables automatically.
    2. It references data without copying it. Correct answer.
    3. It copies source data into the lakehouse for durability.
    4. It encrypts the source data at rest.

    Explanation

    A shortcut is a OneLake reference to data in another location, so consumers read the original data without creating a copy. It does not convert formats or encrypt data by itself.

  81. Question 81Prepare and serve data

    Which language is used by default for Spark code in a Fabric notebook?

    1. T-SQL
    2. PySpark Correct answer.
    3. KQL
    4. DAX

    Explanation

    Fabric notebooks run Spark and default to PySpark for cell code. T-SQL, KQL, and DAX are query languages used by other Fabric experiences.

  82. Question 82Prepare and serve data

    Which pipeline activity moves data from a source to a destination in one step?

    1. Wait
    2. Lookup
    3. ForEach
    4. Copy activity Correct answer.

    Explanation

    The Copy activity is the pipeline activity that reads from a source and writes to a destination. ForEach, Lookup, and Wait provide control flow or metadata retrieval, not data movement.

  83. Question 83Prepare and serve data

    Which Fabric item is designed for real-time analytics over streaming data by using KQL?

    1. Warehouse
    2. Dataflow Gen2
    3. Eventhouse Correct answer.
    4. Lakehouse

    Explanation

    An Eventhouse hosts KQL databases for real-time analytics over streaming and telemetry data. Lakehouses and warehouses use Spark and T-SQL, while Dataflow Gen2 is for batch transformation.

  84. Question 84Prepare and serve data

    What is the purpose of a pipeline parameter?

    1. To set the pipeline's owner.
    2. To store the pipeline's run history.
    3. To define the pipeline's schedule.
    4. To pass values at run time. Correct answer.

    Explanation

    Parameters let the same pipeline accept different values on each run, such as a date or file path. They are not used for run history, ownership, or schedule definition.

  85. Question 85Prepare and serve data

    Which Fabric feature provides near real-time read access to an Azure SQL Database with minimal ETL development?

    1. Copy activity every 15 minutes
    2. Mirroring Correct answer.
    3. An Import mode semantic model
    4. Dataflow Gen2 with scheduled refresh

    Explanation

    Mirroring continuously replicates an Azure SQL Database into OneLake as Delta tables with near real-time latency and minimal pipeline development. Scheduled dataflows or Copy activities introduce batch delays and more design work, and Import mode copies data into a semantic model.

  86. Question 86Prepare and serve data

    A pipeline must run a T-SQL statement against a Fabric warehouse after a load completes. Which activity should be added?

    1. Script activity Correct answer.
    2. Notebook activity
    3. Copy activity
    4. Wait activity

    Explanation

    The Script activity executes T-SQL statements against a warehouse or SQL endpoint. Copy moves data, Notebook runs Spark code, and Wait pauses execution.

  87. Question 87Prepare and serve data

    A notebook needs to run another notebook and share its variables. Which magic command should be used?

    1. %ls
    2. %run Correct answer.
    3. %%sql
    4. %pip

    Explanation

    %run executes another notebook inline so variables and definitions are available to the calling notebook. %pip installs packages, %%sql runs a SQL cell, and %ls lists files.

  88. Question 88Prepare and serve data

    Users need a reusable, read-only query over warehouse tables that hides sensitive columns. Which object should be created?

    1. A view Correct answer.
    2. A table
    3. A stored procedure
    4. A scalar function

    Explanation

    A view stores a query definition and returns current data when queried, and it can exclude sensitive columns from the exposed result. Stored procedures perform actions, tables store rows, and scalar functions return a single value.

  89. Question 89Prepare and serve data

    A notebook appends a DataFrame that contains a new column to an existing Delta table. The write must keep existing rows and add the new column. Which option should be included?

    1. partitionBy the new column
    2. mergeSchema true Correct answer.
    3. overwriteSchema true
    4. replaceWhere on the new column

    Explanation

    With mergeSchema set to true, an append can add new columns to the Delta table while keeping existing data. overwriteSchema replaces the schema, and partitioning or replaceWhere do not handle additive schema evolution.

  90. Question 90Prepare and serve data

    Which shortcut target is an item inside the same Fabric tenant?

    1. OneLake lakehouse Correct answer.
    2. Amazon S3
    3. Google Cloud Storage
    4. ADLS Gen2

    Explanation

    An internal shortcut can point to another OneLake location such as a lakehouse in the same tenant. ADLS Gen2, Amazon S3, and Google Cloud Storage are external shortcut targets.

  91. Question 91Prepare and serve data

    Which T-SQL statement creates a new warehouse table directly from a query result in one step?

    1. MERGE INTO target
    2. CREATE TABLE AS SELECT Correct answer.
    3. INSERT INTO target SELECT from source
    4. UPDATE target FROM source

    Explanation

    CREATE TABLE AS SELECT (CTAS) creates a new table and populates it from a query in a single statement. INSERT requires an existing table, UPDATE modifies rows, and MERGE performs upserts.

  92. Question 92Prepare and serve data

    Which PySpark statement writes a DataFrame named df as a managed Delta table named sales in a lakehouse?

    1. df.write.format("delta").saveAsTable("sales") Correct answer.
    2. spark.sql("CREATE TABLE sales")
    3. df.write.csv("/lakehouse/default/Tables/sales", header=True)
    4. df.save("sales")

    Explanation

    The Delta writer with saveAsTable creates a managed table in the lakehouse metastore. The CSV write creates files rather than a table, df.save is not a valid managed-table write, and the SQL statement lacks a column definition.

  93. Question 93Prepare and serve data

    What does V-Order do when Fabric writes Parquet files?

    1. It encrypts files at rest.
    2. It optimizes the file layout. Correct answer.
    3. It compresses files with gzip.
    4. It sorts rows by primary key.

    Explanation

    V-Order is a write-time optimization that arranges Parquet data to improve read performance for engines such as Power BI. It is not compression, encryption, or a primary-key sort.

  94. Question 94Prepare and serve data

    Which pipeline activity retrieves a single value from a dataset for use by later activities?

    1. Wait
    2. Copy activity
    3. Lookup Correct answer.
    4. ForEach

    Explanation

    The Lookup activity returns a dataset row or scalar value that later activities can reference. ForEach iterates, Copy moves data, and Wait delays.

  95. Question 95Prepare and serve data

    Which Fabric item ingests, transforms, and routes streaming events to a destination?

    1. Dataflow Gen2
    2. Pipeline
    3. Eventstream Correct answer.
    4. Notebook

    Explanation

    Eventstream captures streaming events, optionally transforms them, and routes them to destinations such as an Eventhouse. Dataflow Gen2, notebooks, and pipelines are batch-oriented orchestration or transformation tools.

  96. Question 96Prepare and serve data

    A pipeline must run a Copy activity once for each item in an array of table names. Which activity should wrap the Copy activity?

    1. Until
    2. Switch
    3. If Condition
    4. ForEach Correct answer.

    Explanation

    The ForEach activity loops over an array and runs inner activities once per item. If Condition branches, Switch selects cases, and Until loops until a condition is true.

  97. Question 97Prepare and serve data

    A Fabric warehouse must restrict rows returned to each user based on their department. A security policy will enforce the filter. Which object must the policy reference?

    1. A column master key
    2. A table-valued function Correct answer.
    3. A stored procedure with a filter parameter
    4. A scalar function

    Explanation

    Row-level security in a Fabric warehouse uses a security policy that calls an inline table-valued function to evaluate the predicate. Stored procedures, scalar functions, and column master keys do not provide the predicate function required by the policy.

  98. Question 98Prepare and serve data

    Which statement about the %%configure magic command in a Fabric notebook is true?

    1. It must be the first command. Correct answer.
    2. It can be placed in any cell of the notebook.
    3. It runs a SQL query.
    4. It installs Python packages.

    Explanation

    %%configure sets Spark session configuration and must be the first command in the notebook so it applies before the session starts. It does not install packages or run SQL.

  99. Question 99Prepare and serve data

    A Direct Lake semantic model queries a large Delta table. When does the model switch to DirectQuery mode?

    1. When it exceeds Direct Lake guardrails Correct answer.
    2. When the table has more than one partition
    3. When the Delta table uses V-Order
    4. When the report uses a slicer

    Explanation

    Direct Lake reads Delta files directly, but it falls back to DirectQuery when the model exceeds capacity guardrails or encounters unsupported conditions. Partition count, V-Order, and slicers do not by themselves trigger fallback.

  100. Question 100Prepare and serve data

    Which KQL command ingests a file into a KQL database table?

    1. .drop table
    2. .ingest into Correct answer.
    3. .create table
    4. .show tables

    Explanation

    The .ingest into command loads data from a source into a KQL table. .create table and .drop table change schema, and .show tables returns metadata.

  101. Question 101Prepare and serve data

    A Delta table in a lakehouse has many small files after frequent appends. Which command compacts them without changing the table data?

    1. DESCRIBE HISTORY
    2. FSCK
    3. OPTIMIZE Correct answer.
    4. VACUUM

    Explanation

    OPTIMIZE compacts small Delta files into larger files to improve read performance without changing table contents. VACUUM removes unreferenced files, DESCRIBE HISTORY shows versions, and FSCK checks file integrity.

  102. Question 102Prepare and serve data

    A lakehouse contains a shortcut that points to a folder in an Azure Data Lake Storage Gen2 account. Where is the folder's data physically stored?

    1. In the ADLS Gen2 account it targets Correct answer.
    2. In the workspace's attached Fabric warehouse
    3. In the lakehouse's Files section inside OneLake
    4. In the lakehouse's Tables section as Delta files

    Explanation

    A shortcut is a zero-copy pointer: it stores only the reference, and reads and writes are redirected to the target location. Data is copied into OneLake only when you explicitly copy or load it.

  103. Question 103Prepare and serve data

    Which Fabric item provides a relational T-SQL surface that supports multi-table transactions and T-SQL statements such as INSERT, UPDATE, and DELETE?

    1. A warehouse Correct answer.
    2. A KQL database
    3. A lakehouse with a SQL analytics endpoint
    4. An eventhouse

    Explanation

    A Fabric warehouse is a relational engine that supports the full T-SQL surface, including DML and multi-table transactions. The SQL analytics endpoint of a lakehouse is read-only and does not accept DML.

  104. Question 104Prepare and serve data

    Which Delta Lake operation permanently removes data files that are no longer referenced by the table's transaction log?

    1. OPTIMIZE
    2. MERGE
    3. VACUUM Correct answer.
    4. FSCK REPAIR TABLE

    Explanation

    VACUUM deletes unreferenced files that are older than the retention threshold, reclaiming storage space. It is a cleanup operation, not a rewrite of the active table files.

  105. Question 105Prepare and serve data

    A large Delta fact table in a lakehouse is almost always filtered by a transaction date column. Which design most reduces the amount of data scanned by queries?

    1. Store the table as a managed table in the Tables section
    2. Enable V-Order on the table
    3. Partition the table on the transaction date column Correct answer.
    4. Increase the number of small files in each folder

    Explanation

    Partitioning physically groups rows by key so the engine can skip folders that cannot match a date filter. Pruning depends on the physical layout, not on compression settings or managed versus external storage.

  106. Question 106Prepare and serve data

    A team needs a custom Python library available to every notebook run in a workspace without installing it in each session. What should you create and attach?

    1. A shortcut to the wheel file in the lakehouse
    2. A %%configure magic command in each notebook
    3. A pipeline that installs the library first
    4. A workspace environment with the library added Correct answer.

    Explanation

    A Fabric environment defines the Spark runtime together with its libraries and can be attached to notebooks or set as the workspace default, so packages are available in every session. Per-session installs do not persist.

  107. Question 107Prepare and serve data

    Users in a group must be able to query a warehouse column but see only a masked value, while a small group of analysts must see the real value. Which Fabric feature should you use?

    1. Column-level security using GRANT and DENY
    2. Row-level security on the table
    3. Dynamic data masking with an UNMASK grant Correct answer.
    4. A stored procedure that returns selected columns

    Explanation

    Dynamic data masking returns a masked value to users without the UNMASK permission and the actual value to users who have it. Row-level security filters rows, and column-level security blocks access to the column entirely.

  108. Question 108Prepare and serve data

    A Dataflow Gen2 query applies an expensive transformation, and several downstream queries reference the result. Which option should you enable to avoid recomputing it?

    1. Enable staging Correct answer.
    2. Enable query folding
    3. Enable incremental refresh
    4. Enable fast copy

    Explanation

    Enabling staging writes the query's output to an intermediate store so downstream queries read that result instead of re-running the transformation. Microsoft recommends it for queries referenced more than once.

  109. Question 109Prepare and serve data

    A pipeline must send an email only when the preceding Copy activity fails. How should the email activity be configured?

    1. Add an If condition that checks a pipeline status variable
    2. Add the email activity and give it an On completion dependency
    3. Add the email activity and give it an On failure dependency Correct answer.
    4. Add the email activity in a parallel branch with no dependency

    Explanation

    Precedence constraints determine whether an activity runs based on the outcome of the activity it depends on. An On failure dependency runs the email activity only when the upstream Copy activity fails.

  110. Question 110Prepare and serve data

    Which capability does the SQL analytics endpoint of a lakehouse provide?

    1. Query acceleration for KQL tables in an eventhouse
    2. Read-write T-SQL with multi-table transactions
    3. Read-only T-SQL queries over Delta tables Correct answer.
    4. Automatic ingestion of files into Delta tables

    Explanation

    The SQL analytics endpoint exposes the lakehouse's Delta tables as read-only views that can be queried with T-SQL. Data changes are made through notebooks or pipelines, not through the endpoint.

  111. Question 111Prepare and serve data

    A lakehouse must let a group read only two of its Delta tables while hiding the rest of the item. What should you configure?

    1. OneLake data access roles scoped to the two tables Correct answer.
    2. A shortcut to the two tables shared with the group
    3. A workspace role of Contributor for the group
    4. Row-level security on the two tables

    Explanation

    OneLake data access roles define which folders or tables inside a lakehouse a principal can read and can restrict access that workspace roles would otherwise grant broadly. Workspace roles alone grant access to the whole item.

  112. Question 112Prepare and serve data

    A semantic model over a large lakehouse table must load only the last two years of data and refresh incrementally each day. Which storage mode should the model use?

    1. DirectQuery against the SQL analytics endpoint
    2. Import mode with incremental refresh configured Correct answer.
    3. Direct Lake on the lakehouse
    4. Dual mode with the table marked as a dimension

    Explanation

    Incremental refresh is an import-mode capability: historical partitions are stored and only the recent range is refreshed on each run. Direct Lake reads Delta files directly and does not apply incremental refresh policies.

  113. Question 113Implement and manage semantic models

    In a Power BI semantic model, a relationship is created between a dimension table on the one side and a fact table on the many side. By default, which cross-filter direction is applied?

    1. Single direction, from the many side to the one side
    2. Both directions
    3. No filtering until the relationship is manually activated
    4. Single direction, from the one side to the many side Correct answer.

    Explanation

    A new one-to-many relationship filters in a single direction, from the table on the one side toward the table on the many side. Filter context flows down that path during queries, so a dimension filter restricts the fact table but not the reverse.

  114. Question 114Implement and manage semantic models

    Which model object is computed during data refresh, stored in the model, and contributes to the size of the model?

    1. A hierarchy
    2. A relationship
    3. A calculated column Correct answer.
    4. A measure

    Explanation

    A calculated column is evaluated for every row at refresh time, and its values are stored and compressed like any other column, which increases model size. Measures are evaluated at query time and store only their definitions.

  115. Question 115Implement and manage semantic models

    Which DAX function returns the sign-in name of the user querying the semantic model and is commonly used inside row-level security expressions?

    1. CUSTOMDATA
    2. USERPRINCIPALNAME Correct answer.
    3. USEROBJECTID
    4. USERNAME

    Explanation

    USERPRINCIPALNAME returns the user principal name, such as an email address, of the identity running the query. That value can be compared to a column in the model to build dynamic security rules.

  116. Question 116Implement and manage semantic models

    A model contains a Date table related to a Sales table. Which configuration is required for time intelligence functions such as SAMEPERIODLASTYEAR to return correct results?

    1. Create a calculated column for each date attribute used in visuals
    2. Mark the table as a date table using its date column Correct answer.
    3. Set the relationship cross-filter direction to Both
    4. Disable auto date/time and delete all date hierarchies

    Explanation

    Time intelligence functions rely on a contiguous, unbroken range of dates that the model recognizes as the date table. Marking the table as a date table with its date column establishes that requirement.

  117. Question 117Implement and manage semantic models

    A table in a semantic model uses DirectQuery storage mode. What happens when a report visual queries that table?

    1. The table is refreshed on a schedule and served from the in-memory cache
    2. The query is translated and sent to the source system, and results are returned to the report Correct answer.
    3. The full table is loaded into memory on first use and reused for later queries
    4. Only the first 1000 rows are cached in the model and the rest are queried live

    Explanation

    DirectQuery leaves the data in the source and forwards a query each time the table is used. No copy of the rows is held in the model, so results reflect the current state of the source.

  118. Question 118Implement and manage semantic models

    Which DAX expression creates a calculated table that contains a single column of distinct product colors from the Product table?

    1. SUMMARIZE ( Product[Color] )
    2. CALCULATETABLE ( Product[Color] )
    3. CALCULATE ( DISTINCTCOUNT ( Product[Color] ) )
    4. DISTINCT ( Product[Color] ) Correct answer.

    Explanation

    DISTINCT accepts a single column and returns a one-column table of its unique values, which is valid output for a calculated table. The other expressions either supply arguments of the wrong type or return a scalar.

  119. Question 119Implement and manage semantic models

    A measure divides total sales by total cost, but some rows have a cost of zero. Which DAX function returns BLANK instead of raising an error when the denominator is zero?

    1. QUOTIENT
    2. ROUND
    3. DIVIDE Correct answer.
    4. ABS

    Explanation

    DIVIDE performs safe division and returns BLANK, or an optional alternate result, when the denominator is zero or blank. The standard division operator raises an error in that situation.

  120. Question 120Implement and manage semantic models

    Which model feature limits the tables and columns that a group of report authors sees in the field list, without deleting anything from the model?

    1. A hierarchy
    2. A calculation group
    3. A perspective Correct answer.
    4. Row-level security

    Explanation

    A perspective is a curated subset of model objects that can be exposed to a group of report authors. The hidden objects remain in the model and stay available to other perspectives.

  121. Question 121Implement and manage semantic models

    A model contains the measure Total Sales. You need a measure that shows each product's sales as a percentage of sales for all products, ignoring any slicer selection on the Product table. Which DAX pattern achieves this?

    1. DIVIDE ( [Total Sales], [Total Sales] )
    2. DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], KEEPFILTERS ( Product ) ) )
    3. DIVIDE ( [Total Sales], SUMX ( Product, [Total Sales] ) )
    4. DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], REMOVEFILTERS ( Product ) ) ) Correct answer.

    Explanation

    REMOVEFILTERS clears filters on the Product table inside CALCULATE, so the denominator always evaluates across all products. Dividing the filtered numerator by that denominator yields each product's share of the grand total.

  122. Question 122Implement and manage semantic models

    A model has an active relationship between Sales and Date on OrderDate and an inactive relationship on ShipDate. Which function activates the inactive relationship inside a measure?

    1. TREATAS
    2. RELATED
    3. CROSSFILTER
    4. USERELATIONSHIP Correct answer.

    Explanation

    USERELATIONSHIP tells CALCULATE to use a specified inactive relationship for the duration of the calculation. It lets one model serve multiple role-playing date relationships without duplicating tables.

  123. Question 123Implement and manage semantic models

    You configure incremental refresh on a large fact table. Which requirement must the model satisfy?

    1. The table must be marked as a date table
    2. The model must define parameters named RangeStart and RangeEnd with a Date/Time data type Correct answer.
    3. The table must use DirectQuery storage mode
    4. The table must have a numeric surrogate key column

    Explanation

    Incremental refresh works by filtering the source query through two parameters named exactly RangeStart and RangeEnd, typed as Date/Time. The service uses those parameters to partition the table and refresh only the relevant ranges.

  124. Question 124Implement and manage semantic models

    What is the primary purpose of an aggregation table in a semantic model?

    1. To compress the detail table using a columnstore index
    2. To enforce row-level security on the detail table
    3. To replace the detail table once the detailed data is no longer required
    4. To answer queries from pre-computed summaries at a coarser grain Correct answer.

    Explanation

    An aggregation table stores pre-computed values at a higher grain, such as by month and category, so the engine can satisfy queries from the smaller table. Queries that need finer detail are redirected to the detail table.

  125. Question 125Implement and manage semantic models

    Which DAX expression correctly returns the sum of quantity multiplied by unit price for each row of the Sales table?

    1. SUM ( Sales[Quantity] * Sales[UnitPrice] )
    2. CALCULATE ( SUM ( Sales[Quantity] ), Sales[UnitPrice] )
    3. SUM ( Sales[Quantity] ) * SUM ( Sales[UnitPrice] )
    4. SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] ) Correct answer.

    Explanation

    SUMX creates a row context over the Sales table and evaluates the multiplication for each row before adding the results. This returns the correct total of line-level amounts.

  126. Question 126Implement and manage semantic models

    You need to apply a filter derived from a disconnected table of selected product keys to the Sales table. Which function transfers that filter to the Sales column?

    1. USERELATIONSHIP
    2. RELATED
    3. SAMEPERIODLASTYEAR
    4. TREATAS Correct answer.

    Explanation

    TREATAS takes the values of a table expression and applies them as a filter on a column of another table, even when no physical relationship exists. It is the standard way to propagate filters from disconnected tables.

  127. Question 127Implement and manage semantic models

    In a composite model, a table is configured with dual storage mode. What does dual mode enable?

    1. The table is queried from the source for every visual refresh
    2. The table is refreshed twice per day automatically
    3. The table is held in memory and is never queried from the source
    4. The table can behave as either import or DirectQuery, depending on the query Correct answer.

    Explanation

    Dual tables are stored in memory like import tables but can also act as a source for DirectQuery queries in a composite model. This avoids expensive remote joins when related tables are queried live.

  128. Question 128Implement and manage semantic models

    What capability does a calculation group add to a semantic model?

    1. It defines reusable row-level security rules for multiple roles
    2. It replaces measures with calculated columns to improve compression
    3. It groups measures into display folders in the field list
    4. It applies a set of reusable calculation items to any measure in the model Correct answer.

    Explanation

    A calculation group holds calculation items, each containing a DAX expression that is applied to whichever measure is used with it. This lets one definition deliver variants such as year-to-date or currency conversion across many measures.

  129. Question 129Implement and manage semantic models

    A model contains a DateTime column that is used only to group visuals by date. Which change most reduces the size of the model?

    1. Replace the DateTime column with a Date column and relate it to a date table Correct answer.
    2. Create a hierarchy that includes the DateTime column
    3. Change the column data type to Text
    4. Add a calculated column that extracts the hour from the timestamp

    Explanation

    A DateTime column stores a distinct value for every timestamp, which produces very high cardinality. Truncating to a Date value in the source, or relating to a date table, sharply lowers cardinality and therefore model size.

  130. Question 130Implement and manage semantic models

    Two tables each contain duplicate values in the join key, so they cannot be related directly on that key. Which approach produces a valid relationship between them?

    1. Create a relationship on the key and set the cross-filter direction to Both
    2. Create a calculated column that concatenates the two key columns
    3. Create a one-to-one relationship on the key column
    4. Create a bridge table with unique keys and relate both tables to it Correct answer.

    Explanation

    A bridge table holds each key once, allowing one-to-many relationships from the bridge to each of the two tables. Filtering then flows through the bridge to resolve the many-to-many pattern.

  131. Question 131Implement and manage semantic models

    A measure must ignore the relationship between Sales and Product while it is being evaluated. Which CALCULATE modifier accomplishes this?

    1. ALL ( Product )
    2. CROSSFILTER ( Sales[ProductKey], Product[ProductKey], NONE ) Correct answer.
    3. KEEPFILTERS ( Product )
    4. REMOVEFILTERS ( Sales )

    Explanation

    CROSSFILTER with the NONE direction disables the relationship during the calculation so filters no longer propagate across it. ALL and REMOVEFILTERS clear filters on a table but leave the relationship itself active.

  132. Question 132Implement and manage semantic models

    Which statement about a variable declared with VAR in a DAX measure is correct?

    1. A variable is evaluated once and its result is reused within the expression Correct answer.
    2. A variable can be referenced by other measures in the model
    3. A variable can be reassigned multiple times within the same expression
    4. A variable name must begin with an underscore

    Explanation

    A VAR is evaluated a single time in its scope, and later expressions in the same measure reuse the stored result. This improves readability and avoids repeating expensive calculations.

  133. Question 133Implement and manage semantic models

    A calculated column on the Sales table is defined as CALCULATE ( SUM ( Product[Price] ) ). What does CALCULATE do in this expression?

    1. It converts the row context of the calculated column into an equivalent filter context Correct answer.
    2. It creates a relationship between the Sales and Product tables
    3. It pushes the expression to the source system for evaluation
    4. It removes every filter that is applied to the Product table

    Explanation

    CALCULATE performs context transition, replacing the current row context with an equivalent filter context before evaluating its expression. The Product row matching the current Sales row therefore filters the SUM.

  134. Question 134Implement and manage semantic models

    Two calculation groups each contain an item that filters the same column, and both groups apply to the same measure. Which item takes effect?

    1. The item from the calculation group created first in the model
    2. The item from the calculation group with the lower precedence value
    3. Both items, because their filters are intersected
    4. The item from the calculation group with the higher precedence value Correct answer.

    Explanation

    When calculation items from different groups modify the same column filters, the Precedence property decides the conflict. The group with the higher precedence value is applied first and wins for that column.

  135. Question 135Implement and manage semantic models

    A table in a published semantic model uses an incremental refresh policy that creates historical and incremental partitions. A modeler wants to refresh only the most recent partition. What must be true?

    1. The table must be partitioned by an incremental refresh policy and the model accessed through an XMLA read/write endpoint Correct answer.
    2. The table must be switched to DirectQuery storage mode
    3. Each partition must contain fewer than one million rows
    4. The model must be hosted in a workspace without an XMLA endpoint

    Explanation

    Partition-level operations require the table to be partitioned by an incremental refresh policy and the model to be reachable through the XMLA read/write endpoint on a Premium or Fabric capacity. Tools such as Tabular Editor or TMSL scripts can then refresh a single partition.

  136. Question 136Implement and manage semantic models

    A measure uses CALCULATE with ALLSELECTED ( Product ). How does ALLSELECTED differ from ALL ( Product ) in that expression?

    1. ALLSELECTED can be used only with time intelligence functions
    2. ALLSELECTED and ALL behave identically inside CALCULATE
    3. ALLSELECTED removes filters from slicers but keeps filters applied inside the visual
    4. ALLSELECTED keeps filters applied outside the visual while ignoring filters applied inside it Correct answer.

    Explanation

    ALLSELECTED restores the filter context that comes from outside the visual, such as slicers and other visuals, while clearing filters originating inside the visual. ALL clears all filters on the table regardless of where they came from.

  137. Question 137Implement and manage semantic models

    A row-level security role filters the Customer table. The relationship between Customer and Sales filters in a single direction from Sales to Customer. Which change makes the security filter also restrict rows in Sales?

    1. Change the relationship cardinality to many-to-many
    2. Mark the Customer table as a date table
    3. Add the Sales table to the role definition with no DAX expression
    4. Enable bidirectional cross-filtering on the Customer to Sales relationship Correct answer.

    Explanation

    Security filters travel along relationships in their configured filter direction. With filtering flowing only from Sales to Customer, a Customer filter cannot reach Sales, so enabling bidirectional filtering on that relationship allows the restriction to propagate.

  138. Question 138Implement and manage semantic models

    A table in a semantic model uses DirectQuery storage mode. How is data handled when a report visual queries that table?

    1. Rows are cached in memory for 24 hours and then queried from the source system.
    2. Data is stored in the model file but compressed column-by-column at query time.
    3. All rows are copied into the model during refresh and queries read from the in-memory cache.
    4. Queries are sent to the source system; row data is not stored in the model. Correct answer.

    Explanation

    DirectQuery tables keep no imported row data in the model; each visual query is translated and passed to the source system, and only the result set is returned. Refreshes affect metadata rather than copied rows, so source performance and availability directly affect report performance.

  139. Question 139Implement and manage semantic models

    A Product table has exactly one row per ProductKey. A Sales table has many rows for each ProductKey. You create a single relationship between the two tables. Which cardinality should the relationship use?

    1. Many-to-one Correct answer.
    2. One-to-one
    3. One-to-many with Product on the many side
    4. Many-to-many

    Explanation

    The side whose key column contains unique values is the one side, and the side with repeated key values is the many side, so the relationship is many-to-one from Sales to Product. Power BI displays it as one-to-many with the dimension on the one side.

  140. Question 140Implement and manage semantic models

    Which semantic model feature restricts the rows a report consumer can see, based on the identity of the person viewing the report?

    1. Perspectives
    2. Sensitivity labels
    3. Row-level security Correct answer.
    4. Object-level security

    Explanation

    Row-level security defines DAX filters per role, and those filters are applied to the model when a user in that role opens a report. It limits which rows are returned, whereas other model features limit columns, field-list visibility, or document protection.

  141. Question 141Implement and manage semantic models

    You have a date dimension table and want the built-in time intelligence functions to rely on it. Which model setting should you configure?

    1. Create a hierarchy on the date column.
    2. Mark the table as a date table. Correct answer.
    3. Disable the Auto date/time option for the model.
    4. Set the storage mode of the table to dual.

    Explanation

    Marking a table as a date table designates its date column as the model's time reference, which time intelligence functions such as DATESYTD and SAMEPERIODLASTYEAR use to generate correct date ranges. The table must contain one contiguous row per date with no gaps or duplicates.

  142. Question 142Implement and manage semantic models

    Which DAX function generates a contiguous sequence of dates from a start date to an end date without referencing an existing date column?

    1. DATEVALUE
    2. GENERATESERIES
    3. CALENDAR Correct answer.
    4. DATESBETWEEN

    Explanation

    CALENDAR takes a start date and an end date and returns a single-column table containing every date in that range, which makes it suitable for building a date table from scratch. DATESBETWEEN instead filters an existing date column.

  143. Question 143Implement and manage semantic models

    What is the purpose of enabling the large semantic model storage format in Power BI?

    1. It automatically converts imported tables to dual storage mode.
    2. It stores the model in DirectQuery mode so no data is kept in memory.
    3. It lets the model grow beyond the default 10 GB size limit. Correct answer.
    4. It enables automatic creation of aggregation tables for all fact tables.

    Explanation

    The large semantic model storage format is a model-level setting that removes the default size ceiling so a model on Premium, PPU, or Fabric capacity can grow past 10 GB. It does not change storage mode or create aggregations; the model still uses Import or the modes you configure.

  144. Question 144Implement and manage semantic models

    A one-to-many relationship filters from Customer (one side) to Sales (many side). You change the cross-filter direction of that relationship to Both. What is the primary effect?

    1. The relationship automatically becomes many-to-many and needs a bridge table.
    2. Sales can now filter Customer, which can create ambiguous filter paths. Correct answer.
    3. Measures that use Customer columns ignore filters that come from the Sales table.
    4. Row-level security defined on Customer no longer filters the Sales table.

    Explanation

    Setting cross-filter direction to Both lets filters propagate from the many side back to the one side, which is what enables Sales selections to affect Customer. The trade-off is slower queries and the risk of ambiguous filter paths when multiple bidirectional or inactive relationships exist.

  145. Question 145Implement and manage semantic models

    You need a value that is computed once during refresh and stored on each row so that users can place it on a slicer. Which approach should you use?

    1. A field parameter table
    2. A calculated column Correct answer.
    3. A measure that uses CALCULATE
    4. A calculation group item

    Explanation

    A calculated column is evaluated during processing and its results are stored in the model, so it behaves like a normal column that can be used in slicers, rows, and relationships. Measures are evaluated at query time and cannot be used as slicer fields.

  146. Question 146Implement and manage semantic models

    You are writing DAX queries against a semantic model that must return grouped results together with measure values. Which function should you use?

    1. CROSSJOIN
    2. SUMMARIZE
    3. ROW
    4. SUMMARIZECOLUMNS Correct answer.

    Explanation

    SUMMARIZECOLUMNS is designed to group by columns and evaluate measures in the resulting filter context, and it is optimized for query workloads. SUMMARIZE predates it and is known to produce incorrect results or unexpected performance when measures are added to it.

  147. Question 147Implement and manage semantic models

    You need a measure that returns total sales for the corresponding dates of the previous year, using the marked date table. Which function should you pass to CALCULATE?

    1. PREVIOUSYEAR ( 'Date'[Date] )
    2. SAMEPERIODLASTYEAR ( 'Date'[Date] ) Correct answer.
    3. PARALLELPERIOD ( 'Date'[Date], -1, YEAR )
    4. EDATE ( 'Date'[Date], -12 )

    Explanation

    SAMEPERIODLASTYEAR shifts the current filter on the date column back by exactly one year, preserving the same period length, so it returns a like-for-like comparison. It requires a marked date table with a contiguous date column.

  148. Question 148Implement and manage semantic models

    What does a field parameter allow report authors to do in a semantic model?

    1. Restrict which tables and columns appear in the field list for a group of users.
    2. Enable a single table to be queried in DirectQuery while the rest of the model is imported.
    3. Group measures into items that change their calculation logic at query time.
    4. Let viewers switch the field or measure a visual uses by using a slicer. Correct answer.

    Explanation

    A field parameter is a calculated table of field names and expressions that a slicer can control, so report consumers can swap the column or measure used by a visual at view time. It changes which field is used, not how a measure is calculated.

  149. Question 149Implement and manage semantic models

    You enable Assume referential integrity on a relationship where the fact table uses DirectQuery storage. What is the effect?

    1. It automatically changes the relationship cardinality to many-to-many.
    2. It lets the source database use an inner join instead of an outer join. Correct answer.
    3. It creates a surrogate key column in the fact table during every refresh.
    4. It validates that every foreign key exists and blocks refresh when one does not.

    Explanation

    Assume referential integrity tells the engine that every foreign key in the fact table has a matching value in the dimension table, so it can generate an inner join rather than an outer join. This improves source query performance but produces incorrect results if the assumption is false.

  150. Question 150Implement and manage semantic models

    You need a row-level security role that limits each user to the regions mapped to that user in a security mapping table. Which DAX approach implements this?

    1. Create a separate role with a hard-coded value for each report consumer.
    2. Use USERNAME ( ) against a login column stored in the Sales table.
    3. Filter a user mapping table on USERPRINCIPALNAME ( ). Correct answer.
    4. Apply CROSSFILTER to force bidirectional filtering between Sales and Region.

    Explanation

    USERPRINCIPALNAME returns the user principal name of the person querying the model, so filtering a mapping table by that value produces dynamic row-level security that scales to many users. The mapping table must be related to the table being secured.

  151. Question 151Implement and manage semantic models

    A CALCULATE expression applies a filter on Product[Color] equal to "Red". You want any existing filters on Product[Color] in the report to remain in effect. Which function should wrap the filter?

    1. REMOVEFILTERS ( Product[Color] )
    2. ALL ( Product[Color] )
    3. KEEPFILTERS ( [Color] = "Red" ) Correct answer.
    4. TREATAS with a single-column table

    Explanation

    KEEPFILTERS intersects the new filter argument with the existing filter context on the same column instead of replacing it, so only rows that satisfy both conditions survive. Without it, filters in CALCULATE overwrite existing filters on that column.

  152. Question 152Implement and manage semantic models

    A table expression uses VALUES on a dimension column. Under what condition can VALUES return an extra blank row that DISTINCT does not return?

    1. When unmatched foreign keys cause a blank row to exist. Correct answer.
    2. When a slicer on that column has exactly one value selected.
    3. When the column contains null values.
    4. When the table contains more than one million rows.

    Explanation

    When a relationship exists and the fact table contains foreign keys with no match in the dimension, the engine adds a blank row to the dimension table, and VALUES includes that blank row. DISTINCT returns only the distinct values physically present in the column.

  153. Question 153Implement and manage semantic models

    You build an aggregation table that stores total sales by date and product category and relate it to the Date and Product tables. When will queries use this aggregation table?

    1. Only when the fact table is stored in DirectQuery mode rather than imported mode.
    2. When the query groups by columns and uses measures at the aggregation's grain. Correct answer.
    3. Whenever the query groups by any column from any of the related dimension tables.
    4. Only when the query also references an item from a calculation group on the model.

    Explanation

    An aggregation is used only when the query can be answered from the aggregation table, meaning the grouping columns are at or above its granularity and the measures map to the aggregation's summarized values. Grouping by a column below that grain forces the query to the detail table.

  154. Question 154Implement and manage semantic models

    Two tables are connected by more than one relationship, and the filter direction on those relationships results in the engine reporting an ambiguous filter path. How should you resolve it?

    1. Delete the inactive relationship that exists between the two tables.
    2. Convert the fact table from Import storage to DirectQuery storage mode.
    3. Add a bridge table and configure a many-to-many cardinality relationship.
    4. Set one relationship to single cross-filter direction. Correct answer.

    Explanation

    Ambiguity occurs when filters can reach a table through more than one bidirectional path, so the engine cannot determine which path to use. Restricting one of the relationships to single-direction propagation leaves exactly one path for filters to travel.

  155. Question 155Implement and manage semantic models

    You must refresh only the most recent partition of an incrementally refreshed table in the Power BI service, without triggering a full model refresh. Which capability do you use?

    1. Use the Refresh now command on the semantic model in the workspace.
    2. Shorten the archive window in the incremental refresh policy and refresh.
    3. Use the XMLA endpoint to process the selected partition. Correct answer.
    4. Configure a scheduled refresh on the semantic model in the Power BI service.

    Explanation

    The XMLA endpoint exposes the tabular model to external tools, which can issue a process command against a single partition. This requires Premium, PPU, or Fabric capacity and read-write XMLA enabled for the workspace.

  156. Question 156Implement and manage semantic models

    A table visual shows product names from the Product table and a measure defined as SUM ( Sales[Amount] ). Every row of the visual shows the same total. What explains this behavior?

    1. No filter path exists between the visual's table and the aggregated table. Correct answer.
    2. The table used in the visual must be marked as a date table for filtering.
    3. The measure was created as a quick measure instead of a DAX expression.
    4. The measure uses CALCULATE without any filter arguments.

    Explanation

    A measure is evaluated in the filter context produced by the visual's rows; if no relationship connects the visual's table to the table being aggregated, that context never restricts Sales, so every row returns the same value. Creating the relationship establishes the filter path that makes the measure slice per product.

  157. Question 157Explore and analyze data

    Which KQL operator filters the rows of a table so that only rows satisfying a Boolean condition are returned?

    1. extend
    2. where Correct answer.
    3. summarize
    4. project

    Explanation

    The where operator evaluates a predicate against every row and passes through only the rows for which the predicate is true, which is the standard filtering mechanism for tabular expressions in KQL. Shaping columns, aggregating, and adding computed columns are handled by other operators.

  158. Question 158Explore and analyze data

    Which KQL operator groups records and computes aggregate values such as counts and averages?

    1. project-away
    2. summarize Correct answer.
    3. extend
    4. order by

    Explanation

    summarize collapses the input into one row per unique combination of the values named in its by clause and calculates the aggregate functions listed after it. The other operators shape columns or sort rows without producing aggregates.

  159. Question 159Explore and analyze data

    In a T-SQL query run against a Fabric warehouse, which clause restricts the result set to a specified number of rows?

    1. TOP Correct answer.
    2. ROWNUM
    3. LIMIT
    4. SAMPLE

    Explanation

    T-SQL caps the number of returned rows with the TOP clause placed in the SELECT list, optionally combined with ORDER BY. LIMIT and ROWNUM belong to other database engines and are not valid T-SQL syntax.

  160. Question 160Explore and analyze data

    In a Fabric notebook, which function renders a DataFrame as a rich table with built-in chart and profiling options?

    1. display() Correct answer.
    2. show()
    3. collect()
    4. print()

    Explanation

    display() hands the DataFrame to the notebook's rich visualization renderer, which produces a formatted grid plus chart and summary views. The other calls only print text or return raw values to the driver.

  161. Question 161Explore and analyze data

    Which magic command makes the contents of a notebook cell run as a Spark SQL query?

    1. %%markdown
    2. %%python
    3. %%scala
    4. %%sql Correct answer.

    Explanation

    %%sql submits the cell text to the Spark SQL engine, so the statement returns a DataFrame that can be rendered or reused. The other magics switch the cell to a different language or to formatted text.

  162. Question 162Explore and analyze data

    Which keyword must appear in a DAX query so that the query returns a table result?

    1. TABLE
    2. SELECT
    3. RETURN
    4. EVALUATE Correct answer.

    Explanation

    A DAX query consists of an optional DEFINE block followed by exactly one EVALUATE statement whose table expression produces the result set. SELECT and RETURN are not DAX query keywords.

  163. Question 163Explore and analyze data

    In a lakehouse, how are Delta tables exposed for querying through the SQL analytics endpoint?

    1. As Parquet files that must be read with OPENROWSET and a full OneLake URL
    2. As writable tables that support INSERT, UPDATE, and DELETE statements
    3. As stored procedures that return a result set when executed
    4. As read-only tables that can be queried with T-SQL Correct answer.

    Explanation

    The SQL analytics endpoint publishes each Delta table in the lakehouse as a read-only table in the dbo schema, so it can be queried with T-SQL but not modified through the endpoint. Data changes are made with Spark or through the lakehouse, and the endpoint reflects them automatically.

  164. Question 164Explore and analyze data

    In a notebook, which pandas DataFrame method returns the first rows so that you can inspect the data quickly?

    1. tail()
    2. peek()
    3. head() Correct answer.
    4. sample()

    Explanation

    head() returns the first n rows of a DataFrame (five by default), which makes it a fast way to inspect structure and values. tail() returns the last rows and sample() returns a random subset.

  165. Question 165Explore and analyze data

    You need to add a calculated column TotalPrice = Price * Quantity to KQL results while keeping every existing column. Which operator do you use?

    1. parse
    2. extend Correct answer.
    3. project
    4. summarize

    Explanation

    extend appends new columns produced by an expression to the existing output while leaving all original columns in place. project replaces the column set, and summarize or parse change the shape of the rows rather than adding a computed column.

  166. Question 166Explore and analyze data

    You want to count events per 15-minute interval in a KQL query. Which function creates the interval value that you group by inside summarize?

    1. range()
    2. datetime_add()
    3. bin() Correct answer.
    4. ago()

    Explanation

    bin(value, 15m) rounds each timestamp down to a 15-minute bucket, and grouping by that expression inside summarize yields one count per interval. The other functions shift dates, generate sequences, or offset from the current time rather than creating fixed-size buckets.

  167. Question 167Explore and analyze data

    Which KQL aggregation returns an estimated number of unique values and is preferred over an exact distinct count on very large tables?

    1. count()
    2. dcount() Correct answer.
    3. count_distinct()
    4. make_set()

    Explanation

    dcount() estimates the cardinality of a column using a probabilistic algorithm, so it returns quickly and scales to very large inputs. count_distinct() computes an exact value and costs considerably more, while count() totals rows and make_set() returns the distinct values themselves.

  168. Question 168Explore and analyze data

    You must return only the most recent order row for each customer from a T-SQL query, including all the columns of that row. Which technique accomplishes this?

    1. Join the table to itself on CustomerID and keep the row that carries the older OrderDate
    2. Group the rows by CustomerID and return MAX(OrderDate) alongside any other columns in the same SELECT list
    3. Filter on ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) = 1 Correct answer.
    4. Select the distinct CustomerID and OrderDate pairs and keep whichever row is returned first

    Explanation

    A window function numbers each customer's rows from newest to oldest, so filtering the numbered result to 1 yields the complete most recent row per customer. The other patterns either return partial or duplicate rows, or pick the wrong row.

  169. Question 169Explore and analyze data

    You need to read a CSV file stored in OneLake from a T-SQL query in a Fabric warehouse. Which construct do you use?

    1. OPENROWSET with the BULK provider and FORMAT = 'CSV' Correct answer.
    2. BULK INSERT with a UNC path to the OneLake DFS endpoint
    3. xp_cmdshell with a command-line copy command
    4. OPENJSON with a path to the file

    Explanation

    Fabric warehouse and the SQL analytics endpoint support OPENROWSET(BULK ...) over OneLake URLs with FORMAT set to CSV or PARQUET, which returns the file contents as a rowset. The other constructs either target SQL Server file shares, parse string content, or run shell commands.

  170. Question 170Explore and analyze data

    You have a PySpark DataFrame named df in a notebook and want to query it with spark.sql(). What must you do first?

    1. Call df.registerAsTable("v")
    2. Call df.write.saveAsTable("v")
    3. Call spark.catalog.createTable(df, "v")
    4. Call df.createOrReplaceTempView("v") Correct answer.

    Explanation

    createOrReplaceTempView registers a session-scoped view over the DataFrame so that spark.sql() can reference it by name for the life of the session. Registering a permanent table writes catalog metadata, which is a different operation.

  171. Question 171Explore and analyze data

    Which Spark SQL statement returns the column names and data types of a table in the lakehouse?

    1. DESCRIBE TABLE Correct answer.
    2. SHOW TABLES
    3. LIST FILES
    4. EXPLAIN

    Explanation

    DESCRIBE TABLE returns schema metadata for the named table, including column names, data types, and comments. SHOW TABLES lists table names only, and EXPLAIN reports the query plan.

  172. Question 172Explore and analyze data

    Which DAX function is designed for building grouped summary tables in a DAX query and automatically omits combinations in which every measure evaluates to blank?

    1. SUMMARIZECOLUMNS Correct answer.
    2. SUMMARIZE
    3. ADDCOLUMNS
    4. GROUPBY

    Explanation

    SUMMARIZECOLUMNS groups by the supplied columns, evaluates the supplied measures, and drops groups whose measure values are all blank, which makes it the recommended grouping function for DAX queries. SUMMARIZE, GROUPBY, and ADDCOLUMNS do not perform that blank-row elimination on their own.

  173. Question 173Explore and analyze data

    A DAX query creates a single-column table variable of allowed product keys, and the Sales table has no relationship to that list. Which function applies the list as a filter on Sales[ProductKey]?

    1. CROSSFILTER
    2. TREATAS Correct answer.
    3. LOOKUPVALUE
    4. RELATED

    Explanation

    TREATAS marks the values of a table expression as filters over the named columns of the data model, which lets you filter a column that has no physical relationship. RELATED and LOOKUPVALUE retrieve values through relationships rather than applying a filter.

  174. Question 174Explore and analyze data

    Which statement correctly describes querying across items from a T-SQL query in a Fabric SQL analytics endpoint?

    1. Each SQL analytics endpoint is isolated and can query only the tables that belong to its own lakehouse
    2. Tables from other items must first be copied into the local lakehouse by a data pipeline
    3. Three-part names in the form database.schema.table can reference other warehouse or lakehouse items in the same workspace Correct answer.
    4. Cross-item queries work only when both items are attached to the same dedicated capacity and the user is a workspace admin

    Explanation

    Fabric supports cross-database queries inside a workspace: you qualify the item as database.schema.table, and the query succeeds as long as the caller has permissions on both items. No capacity restriction, copy step, or pipeline is involved.

  175. Question 175Explore and analyze data

    Which DAX function returns the top n rows of a table ordered by a supplied expression inside a DAX query?

    1. TOPN Correct answer.
    2. RANKX
    3. WINDOW
    4. OFFSET

    Explanation

    TOPN returns a table containing the requested number of rows, sorted by the order-by expression, and is commonly used with SUMMARIZECOLUMNS or CALCULATETABLE. RANKX returns a rank value per row rather than a table of rows.

  176. Question 176Explore and analyze data

    In KQL you must enrich a very large fact table with a small dimension table, and you want the small table broadcast to every node. Which operator is designed for this shape?

    1. union
    2. lookup Correct answer.
    3. join kind=inner
    4. materialize

    Explanation

    lookup is optimized for the fact-to-dimension pattern: it broadcasts the right-hand dimension table to all nodes and filters the left side, which is more efficient than a general join for that shape. union and materialize do not combine two tables by matching keys.

  177. Question 177Explore and analyze data

    A KQL query references the same expensive subquery three times. Which function stores the subquery result once so that it is computed a single time?

    1. toscalar()
    2. materialize() Correct answer.
    3. as()
    4. invoke()

    Explanation

    materialize() caches the result of its tabular expression for the lifetime of the query, so later references reuse the cached result instead of re-running the source. toscalar() converts a single-value result into a scalar but does not prevent repeated evaluation of a table expression.

  178. Question 178Explore and analyze data

    In Spark SQL you join a very large fact table to a small dimension table and want to avoid shuffling the fact table. Which hint do you add to the query?

    1. /*+ BROADCAST(dim) */ Correct answer.
    2. /*+ COALESCE(1) */
    3. /*+ REPARTITION(200) */
    4. /*+ MERGE(dim) */

    Explanation

    BROADCAST (also spelled BROADCASTJOIN or MAPJOIN) ships the small table to every executor and performs a broadcast hash join, which removes the shuffle of the large side. REPARTITION and COALESCE change partitioning but still leave a shuffle-based join.

  179. Question 179Explore and analyze data

    Which KQL operator runs a plugin such as autocluster or basket to surface patterns in a result set?

    1. invoke
    2. extend
    3. evaluate Correct answer.
    4. apply

    Explanation

    The evaluate operator hands the input tabular expression to a query plugin, and the plugin returns a new tabular result that highlights patterns or anomalies. extend and apply are ordinary tabular operators and do not invoke plugins.

  180. Question 180Explore and analyze data

    Which keyword combination correctly defines a measure and then returns a result within a single DAX query?

    1. CREATE MEASURE ... SELECT
    2. DEFINE MEASURE ... EVALUATE Correct answer.
    3. WITH MEASURE ... SELECT ... FROM
    4. DECLARE MEASURE ... RETURN

    Explanation

    A DAX query can begin with a DEFINE block that declares measures or variables and must end with an EVALUATE statement that produces the result table. CREATE, DECLARE, and WITH are not part of DAX query grammar.

  181. Question 181Explore and analyze data

    You want to query an earlier committed version of a Delta table from a Fabric notebook for comparison. Which option do you specify when reading the table?

    1. option("timeTravel", 3)
    2. option("snapshotVersion", 3)
    3. option("deltaVersion", 3)
    4. option("versionAsOf", 3) Correct answer.

    Explanation

    The Delta Lake reader accepts versionAsOf with an integer version or timestampAsOf with a timestamp to read a historical snapshot of the table. The other option names are not recognized by the Delta reader and are ignored.

  182. Question 182Explore and analyze data

    You write a DAX query and need a table result made of a single row with two named columns, Year and Amount, produced from scalar expressions. Which function should you use?

    1. ADDCOLUMNS
    2. ROW Correct answer.
    3. VALUES
    4. SELECTCOLUMNS

    Explanation

    ROW builds a one-row table from scalar expressions and lets you name each output column. Use it when the result is a set of constants or scalar calculations rather than rows taken from an existing table.

  183. Question 183Explore and analyze data

    A KQL query returns far more rows than you need for a quick inspection, and row order does not matter. Which operator should you add so that only a small number of rows is returned cheaply?

    1. project 10
    2. summarize count()
    3. take 10 Correct answer.
    4. top 10 by Timestamp desc

    Explanation

    take returns up to the requested number of rows without sorting the input, which makes it the cheapest way to limit a result for inspection. It does not guarantee which rows you receive.

  184. Question 184Explore and analyze data

    In a Fabric notebook you have a Spark DataFrame and want it rendered in the notebook output as an interactive table that supports sorting and switching to a chart view. Which call should you use?

    1. collect()
    2. show()
    3. print()
    4. display() Correct answer.

    Explanation

    display() is provided by the notebook runtime and renders the DataFrame in the rich table and chart viewer. show() only writes a plain text preview to the output area.

  185. Question 185Explore and analyze data

    You query a very large table in a Fabric warehouse and need the number of distinct customers for the whole table, while keeping processing low and accepting a small, bounded error. Which statement should you run?

    1. SELECT APPROX_COUNT_DISTINCT(CustomerKey) FROM Sales Correct answer.
    2. SELECT COUNT(DISTINCT CustomerKey) AS Customers FROM Sales
    3. SELECT COUNT_BIG(*) FROM Sales
    4. SELECT SUM(DISTINCT CustomerKey) FROM Sales

    Explanation

    APPROX_COUNT_DISTINCT uses a probabilistic algorithm, so it maintains far less state than an exact distinct count and returns a result with a small, bounded error. That trade-off suits very large tables where an estimate is acceptable.

  186. Question 186Explore and analyze data

    In a Fabric notebook you have a Spark DataFrame holding cleansed rows and want to persist it as a Delta table named sales_clean in the lakehouse. Which call performs the write?

    1. spark.sql("CREATE TABLE sales_clean AS SELECT * FROM df")
    2. df.write.mode("delta").save("sales_clean")
    3. df.saveAsTable("sales_clean", format="delta")
    4. df.write.format("delta").saveAsTable("sales_clean") Correct answer.

    Explanation

    The DataFrameWriter returned by df.write exposes format() to choose the Delta format and saveAsTable() to register the data as a managed table in the metastore. That combination creates the Delta table with the DataFrame's schema.

  187. Question 187Explore and analyze data

    You need the number of rows in a large PySpark DataFrame named df without pulling all the rows to the driver. Which expression returns that count?

    1. len(df)
    2. df.collect()
    3. df.count() Correct answer.
    4. df.show(truncate=False)

    Explanation

    count() is an aggregate action that runs on the cluster and returns a single number to the driver. It does not transfer the individual rows.

  188. Question 188Explore and analyze data

    In a DAX query you have a table variable named Products and need a result that keeps every column of that table and appends a calculated column named SalesAmount. Which function should you use?

    1. ROW
    2. SUMMARIZE
    3. SELECTCOLUMNS
    4. ADDCOLUMNS Correct answer.

    Explanation

    ADDCOLUMNS returns the input table with all of its existing columns plus the new calculated columns evaluated row by row. SELECTCOLUMNS is the projection function that returns only the columns you name.

  189. Question 189Explore and analyze data

    A KQL source table has a column Tags that holds a dynamic array of strings. You need one output row per tag value while keeping the other columns of each source row. Which operator should you use?

    1. mv-expand Correct answer.
    2. project-away
    3. summarize
    4. parse

    Explanation

    mv-expand takes a column that holds a dynamic array and produces one row per element, duplicating the values of the other columns. It is the standard way to flatten arrays into rows in KQL.

  190. Question 190Explore and analyze data

    You need to find KQL rows where a free-text column contains the word timeout as a complete term, and you want the query to benefit from term-level indexing. Which operator should you use?

    1. has Correct answer.
    2. matches regex
    3. startswith
    4. contains

    Explanation

    has performs a term-level match against the indexed terms in the column, so it matches whole words and is cheaper than raw text scanning. contains searches the raw string for a substring anywhere, including inside longer words.

  191. Question 191Explore and analyze data

    In a notebook you reuse the same expensive PySpark transformation several times within a single session. Which action keeps the computed partitions in memory for the later uses?

    1. df.explain()
    2. df.collect()
    3. df.cache() Correct answer.
    4. df.show(truncate=False)

    Explanation

    cache() marks the DataFrame to be kept in memory across actions in the session; the storage is filled when the first action runs and reused afterwards. Without it, each action recomputes the full lineage.

  192. Question 192Explore and analyze data

    A PySpark DataFrame has 2,000 partitions but the output should be written as 8 files, and you want to avoid a full shuffle of the data. Which method should you use?

    1. df.repartitionByRange(8)
    2. df.repartition(8)
    3. df.sortWithinPartitions(8)
    4. df.coalesce(8) Correct answer.

    Explanation

    coalesce() reduces the number of partitions by merging existing partitions on the same executor, so it narrows the data without a full shuffle. repartition() variants perform a shuffle across the cluster.

  193. Question 193Explore and analyze data

    You write a T-SQL query in a Fabric warehouse that must return each order row together with a running total of Amount per customer, accumulated in OrderDate order. Which construct computes that running total?

    1. GROUP BY CustomerKey WITH ROLLUP
    2. SUM(Amount) OVER (PARTITION BY CustomerKey ORDER BY OrderDate) Correct answer.
    3. CROSS APPLY with a correlated aggregate
    4. a self-join to a subquery that aggregates Amount by CustomerKey and OrderDate

    Explanation

    A windowed SUM with PARTITION BY and ORDER BY computes a cumulative value over the ordered rows while leaving the detail rows intact. Aggregation constructs collapse rows and therefore cannot return the individual orders.

  194. Question 194Explore and analyze data

    You write a T-SQL query in a Fabric warehouse and need one row per category holding a comma-separated list of its product names. Which function produces that list?

    1. STUFF(ProductName, 1, 1, ',')
    2. CONCAT(ProductName, ',')
    3. STRING_SPLIT(ProductName, ',') with CROSS APPLY
    4. STRING_AGG(ProductName, ',') Correct answer.

    Explanation

    STRING_AGG concatenates the values of a column across the grouped rows and inserts the specified separator between them. It is designed to produce a delimited list per group.

  195. Question 195Explore and analyze data

    You must query Parquet files that reside in an Azure Data Lake Storage Gen2 account from a Fabric lakehouse, and the data must not be copied. What should you create?

    1. A mirrored database
    2. An external table in a warehouse
    3. A copy activity in a data pipeline
    4. A OneLake shortcut in the lakehouse Correct answer.

    Explanation

    A OneLake shortcut is a reference that makes data in an external storage location appear as a folder in the lakehouse without copying it. The files remain in the source account and are queried in place.

  196. Question 196Explore and analyze data

    In a DAX query you need a measure that shows each product's sales as a percentage of sales for all products, regardless of any filter or slicer applied to the Product table. Which expression should you use?

    1. DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Customer[CustomerKey])))
    2. DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Product))) Correct answer.
    3. DIVIDE([Total Sales], [Total Sales])
    4. DIVIDE([Total Sales], SUM(Sales[Amount]))

    Explanation

    The denominator must be evaluated with the Product filters removed, which ALL(Product) does inside CALCULATE. Dividing by the numerator itself would always return 1, and removing Customer filters leaves the Product filter in place.

  197. Question 197Explore and analyze data

    A DAX query must restrict a large fact table to rows where Sales[Region] equals "West", a simple column comparison. Which approach gives the best query performance?

    1. Pass a Boolean filter argument to CALCULATETABLE Correct answer.
    2. Use GENERATE to iterate over the fact table
    3. Add the predicate to a CALCULATE inside a calculated column
    4. Wrap the table in FILTER with the predicate as its second argument

    Explanation

    A Boolean filter argument is translated into a storage-engine filter, so only the qualifying rows are scanned. FILTER is an iterator that evaluates the predicate row by row in the formula engine.

  198. Question 198Explore and analyze data

    A Spark query in a notebook runs slowly. You want to see the physical plan Spark will execute, including whether a broadcast exchange is used for a join. Which call should you make?

    1. df.printSchema()
    2. df.summary().show()
    3. df.describe().show()
    4. df.explain() Correct answer.

    Explanation

    explain() prints the analyzed, optimized and physical plans of the DataFrame, which shows exchanges, joins and other operators chosen by the optimizer. The other calls return schema or statistics rather than the plan.

  199. Question 199Explore and analyze data

    A KQL table has a free-text Message column in a fixed format such as 'user=alice action=login'. You need the user and action values extracted into two separate columns for every row. Which operator should you use?

    1. summarize by Message
    2. where Message has 'user='
    3. mv-expand Message
    4. parse with a string pattern that names the extracted columns Correct answer.

    Explanation

    parse matches a string against a pattern of constant text and wildcards and places each matched segment into the column named after it. It is the intended operator for splitting structured text into typed columns.

  200. Question 200Explore and analyze data

    In a DAX query you need an output column that gives each product a rank by its total sales, with the highest sales ranked 1. Which function should you use?

    1. TOPN
    2. RANK.EQ
    3. ROW_NUMBER
    4. RANKX Correct answer.

    Explanation

    RANKX evaluates an expression for every row of a table, compares the results against the current row's value and returns the rank, with ties handled consistently. It is the DAX ranking function and works with any measure or expression.

DP-600 flashcards

32 cards. Read the term and the definition here. The deck at the top of this page is optional.

Fabric capacity unit
The compute measure consumed by Fabric workloads; each SKU provides a fixed number of capacity units per second, and exceeding the limit triggers throttling of interactive and background operations.
Contributor workspace role
A workspace role that can create, edit, and delete items such as lakehouses and reports, but cannot add users to the workspace or change workspace-level permissions.
Deployment pipeline stages
Ordered environments, defaulting to Development, Test, and Production with up to ten allowed; deployment rules rebind data sources or parameters so each stage points at the right data.
Fabric domain
A governance grouping of workspaces managed by domain admins; a workspace can belong to only one domain, and domains may contain nested subdomains.
Certified endorsement
The higher of the two endorsement levels for Fabric and Power BI items; only users designated as certifiers by the tenant administrator can apply it to trusted, reviewed content.
Git integration
A workspace setting that binds the workspace to an Azure DevOps repository, branch, and folder so supported item definitions are serialized to text for version control.
Capacity Metrics app
A Power BI monitoring app installed by a capacity admin that reports capacity unit consumption, throttling events, and per-item and per-operation usage over time.
Pausing a capacity
An operation that suspends compute for a Fabric capacity, stopping compute billing and making hosted items unavailable; OneLake storage charges continue while the capacity is paused.
Lakehouse versus warehouse
A lakehouse stores Delta tables in OneLake and is queried by Spark plus a read-only SQL analytics endpoint, whereas a warehouse offers full read-write T-SQL with no Spark engine.
OneLake shortcut
A zero-copy reference that makes data in another OneLake location, ADLS Gen2, Amazon S3, Google Cloud Storage, or Dataverse appear as a folder without moving or duplicating it.
Dataflow Gen2
A Power Query Online based data preparation item that transforms data and writes results to a lakehouse, warehouse, or other destination, and can be orchestrated by a pipeline.
Medallion architecture layers
A layered lakehouse pattern where bronze holds raw ingested data, silver holds validated and cleaned tables, and gold holds business-level aggregated tables consumed by reports.
V-Order
A write-time Parquet optimization in Fabric that sorts, encodes, and compresses Delta files to speed up reads, particularly for Direct Lake; it is enabled by default.
OPTIMIZE with Z-ORDER
A Delta table maintenance command that compacts many small files and, when Z-ORDER BY is added, co-locates related rows so filtered queries can skip more files.
VACUUM command
A Delta maintenance operation that permanently deletes data files no longer referenced by the transaction log and older than the retention threshold, seven days by default.
Mirroring in Fabric
A feature that continuously replicates an external database into OneLake as read-only Delta tables with near-real-time latency, without placing read load on the source system.
Direct Lake mode
A semantic model storage mode that reads Delta Parquet files from OneLake without importing a copy, and falls back to DirectQuery when model guardrails are exceeded.
Row-level security role
A model role containing a DAX table filter expression that restricts which rows a user can see; members are assigned by email or Entra security group and tested with View as role.
Object-level security
A model security feature that hides entire tables or columns from a role so those objects do not appear in reports; it is configured through XMLA or Tabular Editor, not Desktop.
Calculation group
A model object holding reusable calculation items that are applied to any measure, commonly used to implement time intelligence; it is authored with Tabular Editor or XMLA.
Incremental refresh policy
A model setting that needs RangeStart and RangeEnd date/time parameters and defines the store period, the refresh period, and an optional column used to detect changed rows.
TMDL
Tabular Model Definition Language, a human-readable text representation of a semantic model used for source control, Git integration, and deployment between environments.
Measure versus calculated column
A measure is evaluated at query time from filter context and stores no data, while a calculated column is evaluated row by row during refresh and its results are persisted in the model.
Star schema design
A model layout where fact tables hold keys and numeric measures and dimension tables hold descriptive attributes, joined by one-to-many relationships from dimension to fact.
DAX query view
An editor in Power BI Desktop and the service that runs EVALUATE queries against a semantic model and can also define, update, or delete measures in the model.
Visual calculations
DAX expressions evaluated on the aggregated data of a single visual, using functions such as RUNNINGSUM, MOVINGAVERAGE, RANK, COLLAPSE, and EXPAND; model columns cannot be referenced directly.
Data Wrangler
A Fabric data exploration tool with a grid-based interface that applies transformations to lakehouse data and exports the equivalent reusable PySpark code.
SQL analytics endpoint
A read-only T-SQL surface over lakehouse Delta tables that supports views, inline table-valued functions, and stored procedures, often used to control access to underlying data.
KQL queryset
A Real-Time Intelligence item used to query KQL databases with operators such as where, summarize, bin(), and render, suited to telemetry and time-series analysis.
Quick measure
A Power BI authoring shortcut that generates DAX from a template such as aggregation, filter, or time intelligence after the author selects fields in a dialog.
Copilot in Power BI
A Fabric assistant feature that requires an F64 or P1-or-larger capacity and a tenant setting, and can draft DAX, explain report content, and summarize data.
Explore feature
A Power BI capability that opens a semantic model on an ad-hoc canvas where users build their own visuals from available fields without editing the published report.

DP-600 study guide

Plan, implement, and manage a solution for data analytics

Start with capacity and workspace design

Fabric analytics solutions begin with platform decisions that constrain everything later: capacity, workspace, and governance. A capacity (F SKU or P SKU) supplies compute; a workspace is a security and collaboration boundary that must be assigned to a capacity before most items can run. Know the workspace roles (Admin, Member, Contributor, Viewer) and how they interact with item-level permissions and OneLake data access. Deployment pipelines promote items across Dev/Test/Prod stages; they require capacity and support only certain item types, with rules for parameterization and autobinding. Git integration provides version control per workspace, but coverage is not universal and schema conflicts must be resolved. Governance concepts include domains, sensitivity labels, endorsement (promoted vs. certified), and the Capacity Metrics app for monitoring smoothing and throttling.

Traps: assuming a workspace includes compute; confusing a deployment pipeline with a data pipeline; expecting Git to deploy every artifact; and forgetting that capacities can be paused, which stops background jobs and refreshes. Another common trap is treating workspace roles as equivalent to dataset or warehouse permissions.

Rule of thumb: one capacity, separate workspaces for Dev/Test/Prod, Git-connected workspaces for source control, and a deployment pipeline for controlled promotion. Do not use Git alone as a release mechanism.

Prepare and serve data

Choose the store, then the movement pattern

Prepare and serve data in Fabric is mostly about picking the right engine and ingestion method. A lakehouse stores files and Delta tables in OneLake and is transformed with notebooks (PySpark) or Dataflows Gen2 (Power Query). Its SQL analytics endpoint is read-only for data modifications. A warehouse provides full T-SQL DDL/DML, stored procedures, and multi-table transactions. A KQL database serves high-volume, time-series, and real-time queries. Pipelines orchestrate Copy activities and notebooks; dataflows are for low-code transformation; shortcuts virtualize data without copying; mirroring replicates external databases. Organize layers as bronze/silver/gold.

Delta maintenance matters: OPTIMIZE compacts small files, V-Order improves read performance for Fabric engines, Z-ORDER co-locates related data, and VACUUM removes old files. For incremental loads, capture a watermark, land new files, then MERGE into the silver table in a notebook.

Traps: trying to INSERT into a lakehouse table through the SQL endpoint; treating shortcuts as copies (they are references with different lineage and permission behavior); using a dataflow when pipeline orchestration is needed; forgetting that V-Order is a write-time layout, not a query hint; and letting small files accumulate.

Worked example: a nightly pipeline copies CSV files to bronze, a notebook deduplicates and MERGEs into a silver Delta table keyed on order ID, then runs OPTIMIZE with V-Order on gold tables used by Direct Lake.

Implement and manage semantic models

Model for performance and reuse

Semantic models in Fabric are tabular models with a storage mode. Import copies data and is fastest but requires refresh. DirectQuery pushes queries to the source and can be slow. Direct Lake reads Delta/Parquet files in OneLake directly, giving import-like speed without refresh, but it can fall back to DirectQuery when framing, guardrails, or unsupported query patterns are hit. Composite models mix modes. Design a star schema with clean relationships, correct cardinality, and single-direction filters unless a specific need justifies otherwise.

Know the difference between calculated columns (row context, stored, refresh required) and measures (evaluated in filter context at query time). DAX essentials include CALCULATE, FILTER, DIVIDE, time intelligence, and SUMMARIZECOLUMNS. Calculation groups and field parameters reduce measure proliferation. Secure models with RLS roles and DAX filters, and optionally OLS with perspectives. Use incremental refresh, aggregations, and the Best Practice Analyzer. XMLA endpoints enable TMSL/TOM tooling and external tools.

Traps: assuming Direct Lake always avoids fallback; adding many-to-many or bi-directional relationships casually; using calculated columns for dynamic slicer logic; thinking RLS applies to workspace viewers without model roles; and forgetting that Direct Lake requires Delta-formatted tables in OneLake that the model can access.

Rule of thumb: prefer measures over calculated columns, enforce a star schema, and test Direct Lake fallback before promising performance.

Explore and analyze data

Query, visualize, and diagnose

Exploring and analyzing data in Fabric spans several surfaces. Use the SQL analytics endpoint for T-SQL over lakehouse tables, KQL querysets for real-time data, notebooks for PySpark profiling, and Data Wrangler for quick visual transforms. In Power BI, DAX query view runs EVALUATE queries against a semantic model and is ideal for validating measures; Performance Analyzer and query diagnostics show where time is spent. Visual exploration should start with profiling: row counts, distinct values, nulls, and distributions before building visuals.

DAX analysis depends on filter context. A measure like Total Sales = SUM(Sales[Amount]) returns different values by slicer, visual axis, or CALCULATE modifier. Use SUMMARIZECOLUMNS or a matrix visual to group; use DIVIDE for safe ratios. For Direct Lake models, check whether a visual triggered fallback to DirectQuery before blaming the visual.

Traps: putting a measure in a calculated column; expecting a measure to filter rows without CALCULATE; confusing EVALUATE query results with report visuals; and assuming a slow visual is a DAX problem when the bottleneck is the source query or model relationships.

Worked example: to find the top five products by sales, create a measure Total Sales, then EVALUATE TOPN(5, SUMMARIZECOLUMNS(Product[Name], "Sales", [Total Sales]), [Sales], DESC). Verify the measure in DAX query view, then build the visual.

DP-600 cheat sheet

Quick reference

Plan, implement, and manage a solution for data analytics

  • Fabric capacity SKUs run from F2 to F2048; F64 is roughly equivalent to Power BI Premium P1 and is the minimum capacity size that supports Copilot features.

  • Workspace roles are Admin, Member, Contributor, and Viewer; Contributor can create and edit items but cannot add users or change workspace permissions.

  • Deployment pipelines default to three stages (Development, Test, Production) and support up to ten; deployment rules rebind data sources or parameters per stage.

  • A workspace belongs to at most one domain; domains group workspaces for governance and may contain subdomains.

  • Endorsement levels are Promoted and Certified; Promoted can be set by any user with write permission on the item, while Certified requires a certifier designated by the tenant admin.

  • Git integration connects a workspace to an Azure DevOps repository and branch; supported item definitions are serialized to text for version control and ALM.

  • The Fabric Capacity Metrics app reports capacity unit (CU) consumption, throttling, and item-level usage, and must be installed once by a capacity admin.

  • Pausing a capacity stops compute charges and makes hosted items unavailable, while OneLake storage charges continue to accrue.

  • Sensitivity labels from Microsoft Purview propagate downstream, so a label applied to a lakehouse or warehouse carries through to reports built on it.

Quick reference

Prepare and serve data

  • A lakehouse stores Delta tables and files accessed through Spark and a read-only SQL analytics endpoint; a warehouse provides full T-SQL read/write with no Spark engine.

  • Shortcuts are zero-copy references to internal OneLake paths or external ADLS Gen2, Amazon S3, Google Cloud Storage, or Dataverse locations; data stays in the source.

  • Dataflows Gen2 use Power Query Online and write results to a lakehouse, warehouse, or other destination; a pipeline can orchestrate their execution.

  • Medallion layers: bronze holds raw ingested data, silver holds validated and cleaned tables, and gold holds business-level aggregated tables for consumption.

  • V-Order is a write-time Parquet optimization that sorts and compresses Delta files for faster reads, especially in Direct Lake, and is enabled by default in Fabric.

  • OPTIMIZE compacts small Delta files; adding Z-ORDER BY a column co-locates related rows to improve file skipping on filtered queries.

  • VACUUM deletes Delta data files no longer referenced by the transaction log and older than the retention threshold, which defaults to 7 days.

  • Mirroring continuously replicates a source database into OneLake as read-only Delta tables with near-real-time latency and no read load on the source.

  • Copy activity with a staging lakehouse is recommended for large warehouse ingestion, and COPY INTO T-SQL provides high-throughput bulk loads.

Quick reference

Implement and manage semantic models

  • Storage modes: Import copies data into memory, DirectQuery queries the source at run time, Dual can act as either, and Direct Lake reads Delta files in OneLake without a copy.

  • Direct Lake requires Delta Parquet tables (V-Order improves performance); when model guardrails are exceeded it falls back to DirectQuery unless fallback is disabled.

  • Row-level security roles are defined with DAX table filters; users are assigned by email or Microsoft Entra security group and can be tested with 'View as role'.

  • Object-level security hides tables or columns from a role and must be configured through the XMLA endpoint or Tabular Editor, not the Power BI Desktop UI.

  • Calculation groups bundle reusable calculation items applied to measures and are created with Tabular Editor or the XMLA endpoint.

  • Incremental refresh requires RangeStart and RangeEnd date/time parameters plus a policy that sets the store period, refresh period, and optional detect-changes column.

  • Measures are evaluated at query time and consume no model storage; calculated columns are computed during refresh and stored row by row in the model.

  • In a star schema, dimension tables relate one-to-many to fact tables; bidirectional cross-filtering is reserved for specific many-to-many or security patterns.

  • TMDL is the text-based tabular model definition language used for source control and ALM of semantic models in Fabric.

Quick reference

Explore and analyze data

  • DAX query view runs EVALUATE statements against a semantic model and can create, update, or delete measures using DEFINE and UPDATE MEASURE syntax.

  • Visual calculations operate only on the visual's data; functions include RUNNINGSUM, MOVINGAVERAGE, RANK, COLLAPSE, and EXPAND, and model columns cannot be referenced directly.

  • Data Wrangler explores and transforms lakehouse data in an interactive grid and exports the equivalent PySpark code for reuse in a notebook.

  • The SQL analytics endpoint exposes lakehouse Delta tables as read-only T-SQL objects and supports views, inline table-valued functions, and stored procedures for access control.

  • A KQL queryset queries Real-Time Intelligence KQL databases using operators such as where, summarize, bin(), and render.

  • Quick measures generate DAX from templates such as aggregation, filter, and time intelligence, based on fields selected in a dialog.

  • Copilot in Power BI requires an F64 or P1-or-larger capacity plus a tenant setting, and can draft DAX, explain content, and summarize reports.

  • The Explore feature opens a semantic model in an ad-hoc canvas so users can build visuals from fields without editing the underlying report.

  • Spark notebooks in Fabric support %%pyspark, %%sql, %%scala, and %%r magic commands to switch the execution language per cell.

DP-600 key terms

Capacity

A pool of compute resources in Microsoft Fabric, bought at a specific SKU, that powers every workload in the workspaces assigned to it. It can be scaled or paused, and usage is tracked in capacity units.

Related: Workspace, Workspace role
Workspace

A shared container in Fabric where items such as lakehouses, warehouses and semantic models are created and organized. Access is controlled through roles that grant admin, member, contributor or viewer rights.

Related: Capacity, Deployment pipeline, Workspace role
Domain

A logical grouping of workspaces that mirrors a business area of an organization, used to delegate administration and improve data discovery. Domains tag workspaces for governance; they do not move or copy items.

Related: Workspace, Endorsement
Deployment pipeline

A Fabric feature that promotes content through ordered stages, typically development, test and production, by comparing workspace items and copying them forward. It supports selective deployment and shows what differs between stages.

Related: Git integration, Workspace
Git integration

A workspace setting that connects Fabric items to a Git repository so changes are tracked as commits. Teams sync workspace updates to a branch and deploy from source control instead of editing only in the portal.

Related: Deployment pipeline, Workspace
Endorsement

A label on a Fabric or Power BI item that signals trust. Promoted is any user's informal mark, while Certified requires permission and identifies an approved, authoritative semantic model or report.

Related: Sensitivity label, Semantic model
Sensitivity label

A Microsoft Purview classification, such as Confidential, applied to Fabric items. Labels travel with exported files and can enforce encryption and access restrictions beyond workspace permissions.

Related: Endorsement, Workspace
Workspace role

One of Admin, Member, Contributor or Viewer, assigned to a user or group in a workspace, that decides whether the person can manage the workspace, edit items, or only view content.

Related: Workspace, Capacity
OneLake

The single, tenant-wide logical data lake behind Fabric. Each workspace gets its own folder, and all engines, including Spark, SQL, KQL and Power BI, read the same Delta, Parquet or CSV files stored there.

Related: Lakehouse, Shortcut, Delta Lake
Lakehouse

A Fabric item that combines Delta-table storage with a SQL analytics endpoint for read-only T-SQL queries. It supports Spark notebooks, pipelines and shortcuts over files held in OneLake.

Related: Warehouse, Delta Lake, Notebook
Warehouse

A Fabric item offering a full T-SQL relational engine with write support, multi-table transactions and cross-database queries over Delta tables stored in OneLake, aimed at structured, governed reporting data.

Related: Lakehouse, OneLake
Shortcut

A OneLake pointer to data held elsewhere, such as another lakehouse, ADLS Gen2, Amazon S3 or Dataverse, that appears as a local folder. Files are read in place; nothing is copied.

Related: OneLake, Mirroring
Mirroring

Continuous, near-real-time replication of an external source such as SQL Server, Snowflake or Azure SQL Database into OneLake as Delta tables, configured with little or no code.

Related: Shortcut, OneLake
Data pipeline

An orchestration item containing activities such as Copy, Notebook, Dataflow or Stored procedure, run on a schedule or trigger, that moves and transforms data. It is Fabric's counterpart to Azure Data Factory pipelines.

Related: Dataflow Gen2, Notebook
Dataflow Gen2

A Power Query-based transformation item that runs in the cloud and writes results to a lakehouse, warehouse, KQL database or other destination. It uses the Power Query Online editor and the M language.

Related: Data pipeline, Power Query
Notebook

An interactive document of Spark code in PySpark, Spark SQL, Scala or R, with markdown cells and an attached lakehouse, used for exploration and for loading or transforming Delta tables.

Related: Lakehouse, Spark
Delta Lake

An open table format that layers a transaction log over Parquet files, providing ACID transactions, schema enforcement and time travel. It is the default table format for Fabric lakehouses and warehouses.

Related: OneLake, Lakehouse, Medallion architecture
Medallion architecture

A layered data-design pattern in which bronze holds raw ingested data, silver holds cleansed and conformed tables, and gold holds business-ready star schemas. Each layer adds quality and structure.

Related: Lakehouse, Delta Lake, Star schema
KQL database

A Fabric item that stores data for the Kusto engine and is queried with Kusto Query Language. It is tuned for append-only event, log and telemetry data and supports streaming ingestion.

Related: Eventstream, OneLake
Eventstream

A Fabric item that captures real-time events from sources such as Event Hubs or Kafka, optionally reshapes them with a no-code processor, and routes the output to a destination item.

Related: KQL database, Lakehouse
Spark

The distributed compute engine behind Fabric notebooks and Spark job definitions. Fabric runs Spark sessions against lakehouses and reads and writes Delta tables stored in OneLake.

Related: Notebook, Lakehouse
Semantic model

A metadata layer defining tables, columns, relationships, measures and security that reports query. Formerly called a dataset, it can store data in Import, DirectQuery or Direct Lake mode.

Related: Direct Lake, Measure, Star schema
Direct Lake

A semantic model storage mode that reads Delta tables in OneLake directly, avoiding both import copies and generated SQL. It gives import-like speed with fresher data and falls back to DirectQuery when limits are exceeded.

Related: Semantic model, OneLake, Delta Lake
Import mode

A semantic model storage mode in which data is copied into an in-memory column store at refresh. Queries are fastest, but the data is only as current as the last successful refresh.

Related: DirectQuery, Incremental refresh, Semantic model
DirectQuery

A semantic model storage mode that leaves data at the source and translates each visual's query into native source queries at run time. It suits very large or fast-changing sources but can respond more slowly.

Related: Import mode, Direct Lake, Aggregation table
Star schema

A model design with a central fact table of measurable events joined to surrounding dimension tables of descriptive attributes. Relationships stay one-to-many, which keeps DAX fast and predictable.

Related: Semantic model, Relationship, Measure
Measure

A DAX expression evaluated at query time within the filter context of a visual, such as SUM(Sales[Amount]). Measures are not stored in the model and aggregate data dynamically.

Related: Calculated column, DAX, Semantic model
Calculated column

A DAX expression that adds a column to a table, computed row by row and stored during refresh. It increases model size and is best kept for slicing or building relationships.

Related: Measure, DAX
Relationship

A model link between two tables, usually one-to-many on a key column, that propagates filters from the dimension side to the fact side. Cardinality and cross-filter direction control how filters flow.

Related: Star schema, Row-level security
Row-level security

Model roles containing DAX filter expressions that limit which rows a user can see when querying a semantic model, applied to reports, Excel and other client tools.

Related: Object-level security, Relationship
Object-level security

A semantic model configuration that hides specified tables or columns from a role, so those users cannot query the hidden objects even through DAX or external tools.

Related: Row-level security, Semantic model
Incremental refresh

A refresh policy that partitions a table by a date range so only new or changed periods are processed while older partitions remain in place. It shortens refresh time and reduces source load.

Related: Import mode, Semantic model
Aggregation table

A pre-aggregated table mapped to columns of a detail table so Power BI answers summary queries from the smaller table and sends other queries to the detail source. It accelerates large models.

Related: DirectQuery, Import mode
XMLA endpoint

A read-write interface that lets external tools such as Tabular Editor, DAX Studio or SQL Server Management Studio connect to a semantic model for scripting, deployment and advanced administration. Write access needs Fabric or Premium capacity.

Related: Semantic model, DAX query view
DAX

The expression language of Power BI semantic models, used for measures, calculated columns, calculated tables and security filters. It works on tables and filter context rather than on individual cells.

Related: Measure, DAX query view
DAX query view

An interface in Power BI Desktop and Fabric for writing and running DAX queries against a model, previewing the results, and defining measures that can be published back to the model.

Related: DAX, Semantic model
Drillthrough

A report page configured with field wells so a user can right-click a data point and open a detail page filtered to that specific item.

Related: Semantic model
Power Query

The data-preparation engine and editor used in Dataflow Gen2 and Power BI Desktop. It records transformation steps in the M language and re-applies them on each refresh.

Related: Dataflow Gen2, Data pipeline
Gateway

A bridge that lets cloud services reach on-premises or private-network data sources, installed either in personal mode for one user or as an enterprise gateway on a server.

Related: Data pipeline, Semantic model

DP-600 study plan

8 weeks · About 6 hours per week

Week 1

Plan, implement, and manage a solution for data analytics & Prepare and serve data

  • Provision a Fabric capacity (trial or paid) and create a workspace bound to that capacity with Git integration enabled.

  • Create a lakehouse and a warehouse side by side, then write a decision rule for when each store is the right target.

  • Assign workspace roles (Admin, Member, Contributor, Viewer) for a three-person team and verify effective permissions per role.

  • Map five given workload scenarios to the correct Fabric component and license tier (capacity SKU vs. per-user).

Your checkpoint

A working workspace containing one lakehouse and one warehouse, documented role assignments, and a one-page scenario-to-component decision matrix covering five cases.

Week 2

Prepare and serve data

  • Build a Data pipeline with a Copy activity that lands three CSV sources into the lakehouse Files area.

  • Parameterize the pipeline and implement a watermark-based incremental load for one source table.

  • Create a Dataflow Gen2 that applies cleansing steps and writes results into a warehouse table.

  • Configure a OneLake shortcut to an external ADLS Gen2 container and confirm the data is queryable without copying.

  • Schedule the pipeline and confirm at least two successful scheduled runs in run history.

Your checkpoint

An orchestrated pipeline that incrementally loads three source tables with a watermark pattern, plus run history showing two green scheduled runs.

Week 3

Prepare and serve data

  • Write a PySpark notebook that promotes bronze data to silver with deduplication, schema enforcement, and type casting.

  • Implement a T-SQL stored procedure in the warehouse that builds a gold aggregation table.

  • Apply Delta maintenance: run OPTIMIZE with Z-ORDER on a high-cardinality column and record query timing before and after.

  • Chain the notebook and stored procedure inside one pipeline using activity dependencies and failure paths.

Your checkpoint

A bronze/silver/gold medallion pipeline running end to end, with a before/after timing table proving the Delta optimization improved a representative query.

Week 4

Prepare and serve data & Implement and manage semantic models

  • Create the default semantic model over the gold Delta table and mark a dedicated date table.

  • Reshape the model into a star schema: define fact grain, split dimensions, and hide surrogate keys from report view.

  • Select a storage mode (Direct Lake, Import, or DirectQuery) per table and justify each choice in writing.

  • Add data quality checks that fail the pipeline when null or duplicate key thresholds are breached.

Your checkpoint

A report-ready semantic model over the gold layer with a marked date table, at least four correct relationships, and a written storage-mode justification per table.

Week 5

Implement and manage semantic models

  • Author 12 or more DAX measures covering base aggregation, year-to-date, and year-over-year using the marked date table.

  • Build a calculation group that switches reporting currency or time period without duplicating measures.

  • Implement two RLS roles, one with a dynamic filter based on the signed-in user, and validate each with the View as role feature.

  • Configure scheduled refresh and verify the refresh history shows success with expected row counts.

Your checkpoint

A semantic model with 12+ tested measures, one working calculation group, and two RLS roles validated through View as role with filtered-result evidence.

Week 6

Implement and manage semantic models & Explore and analyze data

  • Write DAX queries in DAX query view that reproduce the key number behind each report visual and reconcile any mismatch.

  • Optimize at least two slow measures by reducing iterator scope or removing unnecessary context transitions.

  • Build a three-page Power BI report on the model with cross-filtering, drill-through, and a slicer synced across pages.

  • Query the lakehouse through the SQL analytics endpoint to confirm the report totals match the source aggregates.

Your checkpoint

A three-page report plus a DAX query view script set that reproduces every headline metric, with a short reconciliation note resolving all discrepancies.

Week 7

Explore and analyze data & Plan, implement, and manage a solution for data analytics

  • Configure a deployment pipeline that promotes the workspace across development, test, and production stages with a documented rollback step.

  • Set up Git integration on a feature branch, commit changes, and merge through a pull request to the main branch.

  • Review capacity consumption in the Capacity Metrics app and identify the top three consuming items.

  • Apply governance controls: sensitivity labels, endorsement, and a domain assignment for the production workspace.

Your checkpoint

A deployment pipeline promoting artifacts across three stages, a merged pull request, and a written monitoring and governance summary naming the top three capacity consumers and the rollback procedure.

Week 8

Plan, implement, and manage a solution for data analytics & Prepare and serve data & Implement and manage semantic models & Explore and analyze data

  • Complete a full-length timed practice assessment and score at least 80 percent overall and 70 percent in every domain.

  • Rebuild a minimal end-to-end solution (ingest, transform, model, report) in 90 minutes without notes.

  • Re-drill every missed objective and rewrite condensed notes for each weak area.

  • Walk the objective checklist for all four domains and close every remaining gap.

Your checkpoint

A timed practice score report at or above the 80/70 thresholds, a 90-minute unassisted rebuild completed, and a remediation list with zero open items.

Milestones

Week 2: Incremental pipeline loads three sources on schedule and run history shows two consecutive successes.

Week 4: Data flows from source file to a report-ready star-schema semantic model without manual steps, with storage modes justified in writing.

Week 6: Report totals reconciled against SQL analytics endpoint output, and at least two measures measurably faster after optimization.

Week 8: Timed practice assessment scores at least 80 percent overall and 70 percent or higher in each of the four domains.

DP-600 exam day

Your checklist

Government-issued photo ID

Bring a valid, unexpired government-issued photo ID. The name must match your exam registration exactly (including middle names/order). If the names differ, update your Microsoft certification profile before test day or you may be turned away. (needs_review: confirm with the provider which ID types are accepted and whether a second form of ID is required for your delivery mode.)

Appointment confirmation and registration email

Have your appointment/confirmation number and the registration email accessible on your phone or printed. You will need the registration ID if you contact support to reschedule or check in.

Certification profile linked to the same email

Confirm your Microsoft Learn/certification profile uses the same email you registered with, so the result and badge post automatically. Log in the day before to verify you can access the profile.

Workstation and system check (online proctored only)

Run the provider's system test on the exact computer you will use - webcam, microphone, speakers and browser compatibility. Do this at least 24 hours ahead so you have time to fix issues. (needs_review: confirm current system requirements and whether a personal hotspot/backup connection is permitted.)

Quiet, private, well-lit room

If testing remotely, use a closed room with a door, a cleared desk, no second monitor, no papers, phones away and no other people entering. The proctor will ask you to pan the camera around the room.

Stable wired-or-strong internet connection

Prefer a wired connection or a connection with no other heavy streaming on it. Know how to restart your router and how to reach provider support if the session drops.

Know the check-in window

Plan to start check-in well before your appointment time so photo capture, ID review and room scan do not eat into your exam clock. (needs_review: confirm with the provider the exact check-in window, e.g., how many minutes before the start time check-in opens and when it closes.)

No notes, no second screen, no smart devices

Study notes, phones, smart watches, tablets and additional monitors are not permitted in the testing environment. If scratch work is needed, use only the on-screen whiteboard/notepad the delivery platform provides. (needs_review: confirm with the provider which scratch-paper or whiteboard options your delivery mode allows.)

Targeted last-pass review list

Have a one-page list of the topics you keep missing ready for the final 72 hours - e.g., workspace roles vs. item permissions, lakehouse vs. warehouse design, Direct Lake behavior, star-schema modeling, DAX measures vs. calculated columns, row-level and object-level security, deployment pipelines, endorsements and sensitivity labels.

Take the official practice assessment under timed conditions

Use the free official practice assessment/learning path material to benchmark yourself, not question dumps - dumps violate the exam NDA and often teach wrong answers. Note weak domains and re-read the relevant product documentation.

Current skills outline printed or saved

Re-read the official skills measured outline the day before so you know which domains carry the most weight. (needs_review: confirm the current domain weightings on the Microsoft Learn study page - they change when the exam is updated.)

Rest, food and water plan

Sleep properly the night before and eat beforehand; the exam is long enough that hunger and fatigue cost you accuracy. (needs_review: confirm with the provider whether drinks or snacks are allowed at a test center or during a remote session.)

Plan the day

Final 72 hours

Stop collecting new material. Re-read the official skills outline, then spend your time only on your two or three weakest domains. Do one timed practice assessment per day and review every wrong answer against the official documentation rather than memorizing the question.

Final 72 hours (hands-on)

If you still have access to a Fabric trial or capacity, do the tasks by hand once: create a lakehouse and a warehouse, build a semantic model, add a measure, configure RLS, publish an app, and promote content through a deployment pipeline. Muscle memory beats reading for this exam.

Final 24 hours

Confirm the appointment date, time, time zone and delivery mode (test center vs. online proctored). Re-run the system test if remote, check your ID is valid and unexpired, and confirm travel time and parking if going to a test center.

Final 24 hours

Light review only - your one-page weak-topic list and a quick pass over key definitions. Stop studying early enough to get a full night's sleep; no late-night cramming.

Morning of

Eat, hydrate, and avoid starting any new topic. Re-check the appointment time in your local time zone, keep your ID and confirmation number at hand, and close every unnecessary application on the test machine.

Morning of (remote)

Clear the desk, remove the second monitor, silence and stow your phone out of reach after check-in, and tell household members not to enter the room. Start the check-in process early so photo, ID and room scan are done before your slot.

First minutes of the test

Read the instructions and the optional tutorial screens, then note the number of questions and the time budget. If the exam has a case-study section, skim the scenario once and then read the question before re-reading the exhibit detail you actually need.

During the test

Answer in two passes: take the easy and confident questions first, mark and move past anything that needs long reasoning, then return with your remaining time. Budget roughly a minute per question and check the clock at the halfway point.

During the test (question strategy)

Read for the qualifier - 'first', 'best', 'minimize development effort', 'least privilege'. Eliminate clearly wrong options, then choose between what is technically possible and what the requirement actually asks for. For drag-and-drop or drop-down items, re-check the order or selection after placing it.

Last 10 minutes

Confirm no questions are left unanswered and revisit only flagged items where you have a concrete reason to change an answer. Guess rather than leave blanks - an unanswered item cannot earn points.

After the test

Complete the post-exam survey and note your score report or any failing-domain feedback for a retake plan. Do not attempt to reconstruct or share the questions you saw - that violates the exam NDA.

Rules to check

  • Bring and present valid, unexpired government-issued photo ID with a name matching your registration; without acceptable ID you will not be admitted and your fee is not refunded. Confirm accepted ID forms and any second-ID requirement with the provider before test day.

  • You may not access notes, books, phones, smart watches, second monitors or any AI tools during the exam. Use only the scratch/whiteboard facility the delivery platform provides; confirm with the provider what is allowed in your delivery mode.

  • Remote sessions are proctored continuously: stay in webcam view, keep your face and hands visible, do not talk to anyone, and do not leave the camera's view except as the proctor instructs. Any violation can void your session.

  • Breaks are not part of the standard delivery for most role-based certification exams; if a break is offered, the clock generally keeps running. Confirm the break policy for your exam and delivery mode with the provider rather than assuming.

  • Exam content is confidential under the non-disclosure agreement you accept at the start. Do not use, share or discuss brain dumps or exam content - doing so can lead to score cancellation and loss of certifications.

  • Your result is reported as a scaled score rather than a raw percentage, and the passing score for this exam is 700 on that scale. Some delivered items may be unscored pretest items, so a few unusual questions do not mean you are failing.

  • If you need to reschedule, do it through the provider as early as possible - late changes usually incur a fee or forfeit the fee. Confirm the current reschedule/cancellation window with the provider at booking time.

  • If you do not pass, there is a mandatory waiting period before you can retake (confirm the exact interval with the provider), and you must pay for each attempt. Plan your retake around your weakest domain from the score report rather than re-reading everything.

DP-600 FAQ

How many DP-600 practice questions are available?

The practice set has 200 DP-600 questions. Each one includes the choices, the correct answer, and an explanation, and the set is free to read with no signup.

How many DP-600 flashcards are available?

The flashcard set has 32 DP-600 cards. Each card shows the term and its definition.

Which topics does the DP-600 study material cover?

The DP-600 study material has 4 sections, including Plan, implement, and manage a solution for data analytics, Prepare and serve data, and Implement and manage semantic models.

What format facts does the catalog list for the DP-600 exam?

The catalog lists a duration of 100 minutes and these item types: multiple choice and interactive components for the DP-600 exam.