I85PREP
⌘ K
DP-500 study materials
DP-500 study resources

DP-500 practice questions

One question at a time. Build your DP-500 understanding.

First 50 of 200 questions. Each one lists the choices, the correct answer, and an explanation. The checker at the top of this page saves progress in this browser when it is available.

Continue in the practice tool above to load more questions, 50 at a time.

Question 1Design and implement enterprise-scale data analytics solutions

A team wants Power BI semantic models to be deployed and governed as code across development, test, and production workspaces. Which Azure service should hold the semantic model definitions, reports, and pipelines in a Git-based lifecycle?

  1. Power BI Premium capacity without version control
  2. Microsoft Fabric workspace connected to a Git repository Correct answer.
  3. Azure Synapse Analytics workspace with Git integration
  4. Azure Data Factory with linked templates

Explanation

Fabric workspaces support native Git integration, so items such as semantic models, reports, and pipelines are versioned in a repository and promoted through deployment pipelines. Synapse Git integration covers Synapse artifacts only, and ADF linked templates deploy pipelines but not semantic models or reports.

Question 2Design and implement enterprise-scale data analytics solutions

An analytics solution must let a data engineer query a Delta table in OneLake from Spark while a separate team reads the same table through the SQL analytics endpoint. What architecture characteristic makes this possible?

  1. Spark and T-SQL share one query engine that serializes all reads
  2. The SQL analytics endpoint materializes a nightly snapshot of every Delta table
  3. The table is copied into two different storage accounts during ingestion
  4. OneLake stores a single physical copy that multiple engines read through their own endpoints Correct answer.

Explanation

OneLake keeps one logical copy of the data, and different compute engines such as Spark and the SQL analytics endpoint read the same Delta files. This avoids duplicating storage and keeps consumers consistent with the same underlying table.

Question 3Design and implement enterprise-scale data analytics solutions

A company needs a lakehouse where business analysts can create semantic models over curated tables and where data engineers can run Spark notebooks against the same storage. Which Fabric workload combination fits?

  1. Blob Storage containers mounted as network drives
  2. An Azure SQL Database with PolyBase external tables
  3. A Fabric lakehouse with a default semantic model over its Delta tables Correct answer.
  4. A standalone Azure Databricks workspace with no Fabric tenant link

Explanation

A lakehouse stores Delta tables in OneLake and automatically exposes a default semantic model, letting analysts build reports while engineers use notebooks over the same tables. This is the standard Fabric lakehouse pattern for combined engineering and BI.

Question 4Design and implement enterprise-scale data analytics solutions

A solution must enforce row-level security so that regional managers only see their own region's rows in a shared semantic model. Where should the RLS roles and DAX filter expressions be defined?

  1. In the gateway data source credentials
  2. In the source SQL view using a WHERE clause per manager
  3. In the Power BI semantic model using role definitions with DAX table filters Correct answer.
  4. In the Fabric workspace role assignment for the report

Explanation

Semantic model roles with DAX table filter expressions enforce row-level security consistently for every report built on the model. Source views and workspace roles do not filter rows per user within a shared model.

Question 5Design and implement enterprise-scale data analytics solutions

An organization wants to separate development, test, and production content while controlling who can publish to production. Which Fabric capability provides staged promotion with configurable rules?

  1. Git branches merged manually
  2. Deployment pipelines with stage rules Correct answer.
  3. Workspace roles assigned per environment
  4. Capacity settings per workspace

Explanation

Deployment pipelines define stages such as development, test, and production and support deployment rules that let you change data source parameters per stage. Access to a stage can be restricted so only authorized users deploy to production.

Question 6Design and implement enterprise-scale data analytics solutions

A data architect must choose a storage format so that Delta Lake time travel and ACID transactions work for tables consumed by Spark and the SQL analytics endpoint. Which format should the tables use?

  1. CSV files partitioned by date
  2. Parquet files without a transaction log
  3. JSON lines files
  4. Delta Parquet tables Correct answer.

Explanation

Delta Lake tables store Parquet data with a transaction log that enables ACID guarantees and time travel, and Fabric engines including Spark and the SQL analytics endpoint can read them. Plain Parquet, CSV, and JSON lack the transaction log.

Question 7Design and implement enterprise-scale data analytics solutions

A team needs a single Fabric item that stores files and folders for raw ingestion and also exposes Delta tables for downstream SQL queries. Which item type should they create?

  1. A warehouse
  2. A mirrored database
  3. A KQL database
  4. A lakehouse Correct answer.

Explanation

A lakehouse provides a Files area for raw file storage plus managed Delta tables queryable through the SQL analytics endpoint. Warehouses are T-SQL-only, KQL databases serve real-time analytics, and mirrored databases replicate external sources.

Question 8Design and implement enterprise-scale data analytics solutions

A BI team wants to reduce refresh cost by avoiding repeated imports of the same large fact table used by five semantic models. Which design pattern should they adopt?

  1. Five separate import models each refreshing the fact table
  2. Importing the fact table into each report's local cache
  3. DirectQuery against the source for each report
  4. A shared semantic model that other reports and models connect to Correct answer.

Explanation

A shared semantic model centralizes storage and refresh of the fact table so multiple reports reuse one dataset instead of duplicating imports. This reduces refresh load and keeps definitions consistent across consumers.

Question 9Design and implement enterprise-scale data analytics solutions

A Fabric warehouse must serve queries from a reporting tool that uses T-SQL and expects multi-table transactions. Which capability of the warehouse supports this requirement?

  1. T-SQL surface with ACID transactions across tables Correct answer.
  2. KQL query language with materialized views
  3. Notebook-only data modification
  4. Delta tables accessed only through Spark

Explanation

The Fabric warehouse exposes a T-SQL surface and supports multi-table ACID transactions, which reporting tools relying on T-SQL expect. Lakehouse Delta tables are primarily modified through Spark or the SQL analytics endpoint, which is read-oriented for T-SQL consumers.

Question 10Design and implement enterprise-scale data analytics solutions

An enterprise wants near real-time analytics on streaming event data with sub-second query latency over billions of rows. Which Fabric workload is purpose-built for this?

  1. A lakehouse with nightly batch refresh
  2. A Power BI import semantic model
  3. A real-time analytics KQL database Correct answer.
  4. A warehouse with clustered columnstore indexes

Explanation

Real-time analytics in Fabric uses KQL databases, which are optimized for high-volume streaming ingestion and low-latency time-series queries. Batch lakehouse refresh, warehouse indexing, and import models are not designed for sub-second streaming queries at that scale.

Question 11Design and implement enterprise-scale data analytics solutions

A security team requires that data in OneLake be encrypted with customer-managed keys rather than Microsoft-managed keys. Which Fabric feature should be configured?

  1. Private links for the workspace
  2. Row-level security roles
  3. Workspace-level sensitivity labels
  4. Customer-managed keys for the capacity Correct answer.

Explanation

Fabric supports customer-managed keys at the capacity level so encryption of data at rest uses keys the organization controls. Sensitivity labels classify content, RLS filters rows, and private links control network access rather than encryption keys.

Question 12Design and implement enterprise-scale data analytics solutions

A data engineer needs to orchestrate a pipeline that copies files into a lakehouse, then runs a notebook to transform them, then refreshes a semantic model. Which Fabric component sequences these activities?

  1. A scheduled notebook that calls all steps internally
  2. A Power BI scheduled refresh
  3. A Git commit trigger
  4. A data pipeline with copy, notebook, and semantic model refresh activities Correct answer.

Explanation

Fabric data pipelines orchestrate multiple activity types, including copy data, notebook execution, and semantic model refresh, with dependencies between them. This provides scheduling, monitoring, and error handling across the whole flow.

Question 13Design and implement enterprise-scale data analytics solutions

An organization must let external partners query a curated subset of data without granting them access to the full workspace. Which approach best limits exposure?

  1. Grant partners the workspace Admin role
  2. Publish the raw lakehouse to a public link
  3. Share a semantic model or app scoped to the curated data Correct answer.
  4. Provide the capacity admin credentials

Explanation

Sharing a semantic model or publishing an app exposes only the curated content and enforces permissions on that content. Workspace Admin, capacity credentials, and public links grant far broader access than partners require.

Question 14Design and implement enterprise-scale data analytics solutions

A team wants to monitor data quality by failing a pipeline when a transformed table contains null values in a required key column. Which design achieves this?

  1. Schedule a weekly manual review
  2. Disable pipeline error handling
  3. Rely on report users to notice missing keys
  4. Add a conditional activity or notebook assertion that throws an error when nulls are detected Correct answer.

Explanation

Pipelines can evaluate a condition or run notebook code that raises an error when the null check fails, which stops downstream activities. Automated assertions enforce quality at run time rather than depending on manual detection.

Question 15Design and implement enterprise-scale data analytics solutions

A company has an on-premises SQL Server that must be analyzed in Fabric without copying data, using change feed replication. Which Fabric feature fits?

  1. A lakehouse shortcut to the on-premises file share
  2. A KQL database with streaming ingestion
  3. A data pipeline with a one-time copy
  4. A mirrored database for SQL Server Correct answer.

Explanation

Mirroring for SQL Server continuously replicates changes into OneLake so the data can be analyzed without building copy pipelines. Shortcuts, one-time copies, and KQL streaming do not provide ongoing change replication from SQL Server.

Question 16Design and implement enterprise-scale data analytics solutions

A data architect needs to define reusable, version-controlled transformation logic that runs on Spark across multiple lakehouses. Which approach supports this requirement?

  1. Manual copy of notebooks between workspaces
  2. Ad hoc notebook cells edited per environment
  3. Hard-coded paths in each notebook
  4. Notebooks stored in a Git-integrated workspace and parameterized per environment Correct answer.

Explanation

Storing notebooks in a Git-integrated workspace versions the transformation logic, and parameters let the same notebook target different lakehouses per environment. This avoids drift and manual copying between environments.

Question 17Design and implement enterprise-scale data analytics solutions

A semantic model must combine a large imported dimension table with a very large fact table that must remain in the source for freshness. Which storage mode pattern should the architect use?

  1. DirectQuery both tables
  2. Composite model with import for the dimension and DirectQuery for the fact Correct answer.
  3. Import both tables
  4. Dual mode for both tables

Explanation

A composite model can import a small dimension while leaving the large fact table in DirectQuery, balancing freshness and performance. Importing both tables increases refresh cost, and DirectQuery on both pushes all query load to the source.

Question 18Design and implement enterprise-scale data analytics solutions

A team needs to ensure that only approved users can read specific columns containing personal data while other columns remain visible to all report consumers. Which mechanism should they use?

  1. Object-level security in the semantic model Correct answer.
  2. Workspace Contributor role
  3. Sensitivity labels alone
  4. Gateway role mapping

Explanation

Object-level security in the semantic model hides specific tables or columns from users who lack permission, while other columns remain visible. Workspace roles and labels do not selectively hide columns within a model.

Question 19Design and implement enterprise-scale data analytics solutions

An architect must design for cost control so that a Fabric capacity does not run at full compute when idle, while still allowing scheduled workloads to burst. Which approach is appropriate?

  1. Provision a second capacity for every workspace
  2. Disable all scheduled refreshes
  3. Use capacity auto-scale or pausing with scheduled resume for known workload windows Correct answer.
  4. Keep the capacity paused permanently

Explanation

Pausing capacity during idle periods and resuming for scheduled workload windows, or using auto-scale, aligns compute cost with demand while allowing bursts. Permanently pausing or disabling refreshes prevents required workloads from running.

Question 20Design and implement enterprise-scale data analytics solutions

A solution must provide a governed semantic layer where certified metrics are defined once and reused by many reports, with lineage visible to stewards. Which combination best meets this?

  1. DirectQuery reports against raw tables
  2. A shared semantic model in a governed workspace with lineage view enabled Correct answer.
  3. Spreadsheet extracts distributed by email
  4. Separate import models per report

Explanation

A shared semantic model in a governed workspace defines certified metrics once, and lineage view shows dependencies for stewards. Per-report models, raw DirectQuery, and spreadsheet extracts duplicate definitions and break governance.

Question 21Design and implement enterprise-scale data analytics solutions

A data engineer must implement incremental loading for a large Delta table in a lakehouse so only new rows are processed each run. Which design is most appropriate?

  1. Random sampling of rows each run
  2. Full overwrite of the table on every run
  3. Deleting and recreating the table each run
  4. Watermark-based reads using the maximum loaded key or timestamp, merged into the target Correct answer.

Explanation

Tracking a watermark such as the maximum loaded timestamp or key lets each run read only new rows and merge them into the target Delta table. Full overwrite, sampling, and recreation reprocess or lose data rather than incrementally loading.

Question 22Design and implement enterprise-scale data analytics solutions

An enterprise needs cross-workspace data access where a single lakehouse table is consumed by many workspaces without copying. Which OneLake feature enables this?

  1. Shortcuts that reference the table from other workspaces Correct answer.
  2. Exporting the table to CSV per workspace
  3. Emailing extracts to each team
  4. Duplicating the lakehouse per workspace

Explanation

OneLake shortcuts create references to data in other locations so consumers read the same physical data without copying it. Exporting, duplicating, or emailing extracts creates divergent copies and extra storage cost.

Question 23Design and implement enterprise-scale data analytics solutions

A security architect must ensure that data in a Fabric workspace is accessible only from the corporate network and not from the public internet. Which control should be applied?

  1. Private links for the workspace or tenant Correct answer.
  2. Sensitivity labels on items
  3. Row-level security roles
  4. Deployment pipeline stage rules

Explanation

Private links restrict access to Fabric and Power BI resources through a private endpoint on the corporate network, blocking public internet access. Labels, RLS, and stage rules address classification, row filtering, and promotion rather than network exposure.

Question 24Design and implement enterprise-scale data analytics solutions

A team must design a medallion architecture in a lakehouse with bronze, silver, and gold layers consumed by different teams. Which design decision best supports governance and performance?

  1. Store all layers in one unmanaged folder with no schema enforcement
  2. Allow any team to overwrite the gold layer directly
  3. Use separate Delta tables per layer with enforced schemas and documented ownership Correct answer.
  4. Skip the silver layer to reduce storage

Explanation

Separate Delta tables per layer with enforced schemas and clear ownership provide governed, queryable layers and prevent uncontrolled changes. A single unmanaged folder, open gold writes, and skipping refinement undermine quality and accountability.

Question 25Design and implement enterprise-scale data analytics solutions

An organization requires that semantic model deployments to production be validated automatically before release, including checks that measures reference existing columns. Which practice supports this?

  1. Manual review of screenshots before deployment
  2. Deploying directly from each developer's desktop
  3. Disabling deployment rules for production
  4. Automated validation using deployment pipelines with pre-deployment checks and model tests Correct answer.

Explanation

Deployment pipelines combined with automated checks and model tests validate that the model is consistent before promotion to production. Manual screenshots, direct desktop deployment, and disabled rules remove the automated safeguard.

Question 26Design and implement enterprise-scale data analytics solutions

A data architect is planning a Fabric tenant that will host hundreds of workspaces across several business units. The company wants to minimize the number of capacities to operate while still isolating billing per business unit. Which capacity design should the architect recommend?

  1. Purchase Power BI Premium per user licenses and rely on them to host all lakehouse and warehouse workloads.
  2. Create a single Fabric capacity and use workspace-level settings to split the bill across business units.
  3. Create one Fabric capacity per business unit so each unit receives its own billable resource and independent throttling limits. Correct answer.
  4. Assign all workspaces to a Fabric trial capacity and convert to paid only when usage grows.

Explanation

Fabric capacities are the unit of compute purchase and billing; a single capacity is billed as one Azure resource, so per-business-unit billing isolation requires separate capacities. Workspace settings do not split capacity billing. Trial capacities cannot be relied on for production enterprise workloads, and PPU licenses do not provide Fabric compute for lakehouse and warehouse items.

Question 27Design and implement enterprise-scale data analytics solutions

An architect must design a Fabric deployment strategy where development, test, and production each live in separate workspaces and content moves between them with parameterized data source rules. Which capability should be the foundation of this design?

  1. Exporting items as .pbix files and importing them into each environment.
  2. Git integration on the development workspace only, with manual re-creation of items in production.
  3. Deployment pipelines with workspace stages and deployment rules for data sources and parameters. Correct answer.
  4. One shared workspace used by all environments, separated by item name prefixes.

Explanation

Fabric deployment pipelines define ordered stages mapped to workspaces and support deployment rules that override data source and parameter values per stage. This gives repeatable promotion from development through production without manual re-creation. A single shared workspace cannot isolate environments, and .pbix export only covers a subset of item types.

Question 28Design and implement enterprise-scale data analytics solutions

A team wants to store curated Delta tables that analysts query from multiple workspaces while keeping the data physically in one place. Which Fabric construct should the architect select as the primary storage and compute boundary for those tables?

  1. A Power BI datamart in each consuming workspace.
  2. A collection of CSV files in OneLake accessed directly by each report.
  3. A Fabric warehouse for each consuming team.
  4. A single lakehouse in a shared workspace, with shortcuts or cross-workspace queries for consumers. Correct answer.

Explanation

A lakehouse stores Delta tables in OneLake and can be shared across workspaces by granting access or by creating shortcuts, avoiding copies. Warehouses and datamarts duplicate scope per team and CSV files lack transactional guarantees and schema enforcement. This keeps one physical copy with governed access.

Question 29Design and implement enterprise-scale data analytics solutions

An architect is choosing between a Fabric warehouse and a lakehouse for a workload that requires multi-table T-SQL transactions, stored procedures, and full T-SQL DML support. Which choice fits and why?

  1. Warehouse, because it provides full T-SQL surface area including multi-table transactions. Correct answer.
  2. Warehouse, because it stores data in CSV format that supports transactional writes.
  3. Lakehouse, because Delta tables always support multi-table transactions through Spark.
  4. Lakehouse, because its SQL analytics endpoint supports full T-SQL DML and transactions.

Explanation

The Fabric warehouse exposes a full T-SQL surface area, including multi-table transactions, stored procedures, and DML statements. The lakehouse SQL analytics endpoint is read-only over Delta tables and does not support multi-table transactions. Delta ACID guarantees apply per table, not across tables in a single transaction.

Question 30Design and implement enterprise-scale data analytics solutions

A data architect wants every new workspace in a Fabric tenant to inherit a standard set of labels and domain assignments automatically. Which governance feature should be used to enforce this at scale?

  1. Domains combined with tenant-level workspace settings and delegated admin roles. Correct answer.
  2. Sensitivity labels applied manually by each workspace admin.
  3. Capacity autoscale settings applied per workspace.
  4. Workspace identity with service principal credentials.

Explanation

Fabric domains group workspaces by business area and, combined with tenant settings and delegated admin roles, let governance be applied consistently across many workspaces. Manual labeling depends on each admin and is not enforced. Capacity autoscale and workspace identities address compute and authentication, not classification governance.

Question 31Design and implement enterprise-scale data analytics solutions

An architect must document how data flows from source systems through bronze, silver, and gold layers, including which item produces each table. Which Fabric artifact best captures this lineage at the workspace level?

  1. The workspace lineage view, which shows dependencies between items such as pipelines, lakehouses, and reports. Correct answer.
  2. The capacity metrics app, which reports compute consumption per item.
  3. The sensitivity label audit log, which records label changes.
  4. The OneLake file explorer, which lists folder contents.

Explanation

The lineage view in a workspace visualizes dependencies among items, showing which pipelines and notebooks feed which lakehouses and reports, which is the basis for documenting medallion flow. Capacity metrics report resource consumption, label audits track classification changes, and file explorer only shows storage layout.

Question 32Design and implement enterprise-scale data analytics solutions

A company wants a single governed place where certified metrics such as total revenue and active customers are defined once and reused by every report in the tenant. Which design should the architect choose?

  1. Define the measures in a warehouse view and require report authors to retype the DAX.
  2. Publish a shared semantic model with the certified measures and have reports connect to it. Correct answer.
  3. Recreate the measures in each report to keep reports independent.
  4. Store the measure definitions in a shared notebook that analysts copy.

Explanation

A shared semantic model centralizes measure definitions so every connected report uses identical DAX and certification can be applied. Copying definitions into reports or notebooks guarantees drift. A warehouse view can hold SQL logic but does not deliver reusable DAX measures to report authors.

Question 33Design and implement enterprise-scale data analytics solutions

An architect needs to give a large group of analysts read access to a lakehouse without managing individual permissions. Which approach follows Fabric security best practice?

  1. Share the workspace with each analyst individually.
  2. Assign analysts to a security group and grant that group the Viewer role on the workspace. Correct answer.
  3. Provide analysts with the workspace admin role so they can self-manage access.
  4. Grant each analyst direct table-level SQL permissions in the lakehouse.

Explanation

Role-based access through Microsoft Entra security groups scales cleanly: membership is managed centrally and the group receives a workspace role such as Viewer. Individual grants and per-user sharing create maintenance overhead, and the Admin role grants far more privilege than read access requires.

Question 34Design and implement enterprise-scale data analytics solutions

A data architect is designing a lakehouse where the silver layer must enforce schema and reject malformed records before they reach gold. Which approach best fits this requirement?

  1. Land raw files in bronze, then apply schema enforcement and validation in a silver transformation that writes Delta tables. Correct answer.
  2. Skip bronze and load source systems straight into the semantic model.
  3. Store silver data as untyped JSON so any shape is accepted.
  4. Write all source files directly into gold tables and clean them later.

Explanation

The medallion pattern keeps bronze as raw landing, applies cleansing, typing, and validation in silver, and publishes trusted aggregates to gold. Schema enforcement belongs in the silver transformation so bad records are rejected before gold consumption. Writing raw data to gold or skipping layers defeats the quality boundary.

Question 35Design and implement enterprise-scale data analytics solutions

An architect must ensure that a Fabric capacity does not incur charges while idle during nights and weekends, but must resume automatically for scheduled morning loads. Which configuration meets this requirement?

  1. Set the capacity SKU to the smallest size permanently.
  2. Enable autoscale so the capacity scales to zero when idle.
  3. Pause the capacity on a schedule and resume it before the load window using automation. Correct answer.
  4. Rely on smoothing to absorb idle periods without pausing.

Explanation

Fabric capacities can be paused so billing for compute stops, and they can be resumed programmatically or on a schedule before workloads run. Autoscale adjusts size but does not stop billing when idle. Choosing a small SKU reduces cost but still bills continuously, and smoothing only spreads burst usage for throttling purposes.

Question 36Design and implement enterprise-scale data analytics solutions

A data architect needs to let a partner team query a curated set of tables in a lakehouse without exposing the rest of the workspace. Which mechanism should be used?

  1. Create a separate workspace containing shortcuts to the curated tables and grant the partner the Viewer role there. Correct answer.
  2. Share the lakehouse SQL analytics endpoint connection string with the partner.
  3. Add the partner to the workspace Admin role with a conditional access policy.
  4. Grant the partner the Contributor role on the workspace.

Explanation

A dedicated workspace with OneLake shortcuts to only the curated tables, plus the Viewer role for partners, limits visibility to the intended data. Contributor and Admin roles expose the whole workspace and allow modification. Sharing a connection string does not grant or restrict authorization by itself.

Question 37Design and implement enterprise-scale data analytics solutions

An architect is evaluating Direct Lake semantic models against Import models for a very large fact table that must reflect source updates with minimal latency. Which characteristic of Direct Lake is the deciding factor?

  1. Direct Lake bypasses capacity compute entirely, so no SKU is required.
  2. Direct Lake reads Delta Parquet files directly from OneLake without importing a copy. Correct answer.
  3. Direct Lake requires a scheduled refresh to copy data into the semantic model.
  4. Direct Lake only works with CSV files stored in a warehouse.

Explanation

Direct Lake queries Delta tables in OneLake directly, avoiding a duplicated in-memory import while still providing fast query performance and fresher data. It does not require scheduled imports and does not work over CSV. It still consumes capacity compute like other Fabric workloads.

Question 38Design and implement enterprise-scale data analytics solutions

A data architect must design a workspace structure so that a team of engineers can build pipelines while analysts can only consume published reports. Which workspace design achieves least privilege?

  1. Separate engineering and analytics workspaces, with engineers as Contributors in engineering and analysts as Viewers in analytics. Correct answer.
  2. One workspace where engineers are Admins and analysts are Viewers.
  3. One workspace where everyone is a Member to simplify collaboration.
  4. One workspace per analyst, each with Admin rights over their own copy.

Explanation

Separating build and consume workspaces lets each group receive only the role it needs: engineers build in the engineering workspace, and analysts get read access to published content in the analytics workspace. Combining roles in one workspace or granting Member broadly violates least privilege, and per-analyst Admin copies create sprawl and inconsistency.

Question 39Design and implement enterprise-scale data analytics solutions

An architect must ensure that a notebook writing to a gold Delta table does not create duplicate rows when a pipeline retries after a transient failure. Which design pattern should be used?

  1. Disable pipeline retries so failures are handled manually.
  2. Rely on the pipeline retry setting to skip the notebook on the second attempt.
  3. Use an idempotent MERGE on a business key, or overwrite a partition for the affected date. Correct answer.
  4. Append all incoming rows and deduplicate later in the report layer.

Explanation

Idempotent writes such as MERGE on a stable key, or partition overwrite for the batch date, make reruns safe because the same input produces the same final table state. Plain appends duplicate rows on retry. Retry settings and manual handling do not make the write itself idempotent.

Question 40Design and implement enterprise-scale data analytics solutions

A data architect is defining a star schema in a Fabric warehouse for a sales fact table with several dimensions. Which design choice best supports fast aggregation and simple DAX?

  1. Store all attributes in one wide denormalized table with no relationships.
  2. Create a separate fact table for each report to simplify each report's model.
  3. Model a star schema with conformed dimensions and a single fact table at a consistent grain. Correct answer.
  4. Snowflake the dimensions into many normalized tables to reduce redundancy.

Explanation

A star schema with conformed dimensions and a fact table at a declared grain gives predictable relationships, efficient aggregation, and simple DAX. Snowflaking adds joins, a fully denormalized table loses relationship clarity, and per-report fact tables duplicate data and fragment metrics.

Question 41Design and implement enterprise-scale data analytics solutions

An architect must provide near real-time ingestion of events into a lakehouse table with exactly-once semantics from an Eventstream. Which Fabric feature should be used for the sink?

  1. An Eventstream destination that writes to a lakehouse table in Delta format. Correct answer.
  2. A pipeline with a copy activity triggered by a tumbling window.
  3. A Dataflow Gen2 with a scheduled refresh every 15 minutes.
  4. A notebook that polls the event source on a timer.

Explanation

Eventstream can write streaming events directly to a lakehouse table in Delta format, providing low-latency ingestion with checkpointing that supports exactly-once processing. Scheduled Dataflows, tumbling-window copies, and polling notebooks introduce latency and do not provide streaming checkpoint semantics.

Question 42Design and implement enterprise-scale data analytics solutions

A data architect needs to enforce that only rows matching a user's region are visible when analysts query a semantic model, without creating one model per region. Which approach should be used?

  1. Implement row-level security with DAX filter expressions and map users to regions in a security table. Correct answer.
  2. Create a separate semantic model and workspace for each region.
  3. Rely on workspace Viewer role to restrict rows automatically.
  4. Apply column-level security on the region column.

Explanation

Row-level security in a semantic model uses DAX filters, typically driven by a security mapping table and USERPRINCIPALNAME, so each user sees only their region from one model. Per-region models duplicate maintenance, column-level security hides columns rather than rows, and workspace roles do not filter rows.

Question 43Design and implement enterprise-scale data analytics solutions

An architect must choose how to expose a large lakehouse table to a semantic model so that freshness is high but the model does not hold a full in-memory copy. Which combination is most appropriate?

  1. DirectQuery against the lakehouse SQL analytics endpoint.
  2. Composite model with the fact table imported and dimensions in DirectQuery.
  3. Direct Lake over the Delta table in OneLake. Correct answer.
  4. Import mode with a full refresh every hour.

Explanation

Direct Lake reads Delta Parquet directly from OneLake, giving high freshness without a full imported copy and with better performance than DirectQuery for large tables. Hourly import duplicates data and lags, DirectQuery against the SQL endpoint adds translation overhead, and importing the fact table recreates the copy problem.

Question 44Design and implement enterprise-scale data analytics solutions

A data architect must design a Fabric environment where a central team owns shared bronze and silver layers, while business units build gold models in their own workspaces. Which pattern supports this with minimal data duplication?

  1. Each business unit copies bronze and silver data into its own workspace.
  2. Business units query source systems directly for their gold models.
  3. All business units share a single workspace with Contributor rights for everyone.
  4. The central team publishes shared lakehouses, and business units create shortcuts to silver tables for their gold models. Correct answer.

Explanation

Shortcuts in OneLake let business-unit workspaces reference the central silver tables without copying data, so each unit owns its gold layer while governance stays with the central team. Copying duplicates storage and lineage, a single shared workspace blurs ownership, and querying sources directly bypasses the curated layers.

Question 45Design and implement enterprise-scale data analytics solutions

An architect is designing a Fabric solution that must support both Spark-based engineering and T-SQL reporting over the same curated data. Which design minimizes duplication while meeting both needs?

  1. Store curated data only as Parquet files without a table abstraction.
  2. Store curated data only in a warehouse and require engineers to use T-SQL.
  3. Store curated data as Delta tables in a lakehouse so Spark reads and writes them and the SQL analytics endpoint serves T-SQL queries. Correct answer.
  4. Maintain a separate warehouse copy and a lakehouse copy kept in sync by pipelines.

Explanation

Delta tables in a lakehouse are readable and writable by Spark and are automatically exposed through the SQL analytics endpoint for T-SQL consumers, so one physical copy serves both engines. Duplicating into a warehouse doubles storage and sync effort, and raw Parquet without tables lacks catalog and SQL access.

Question 46Design and implement enterprise-scale data analytics solutions

A data architect must guarantee that a nightly gold aggregation reflects a consistent snapshot even if silver tables are being updated concurrently by another pipeline. Which technique should be used?

  1. Read silver tables without any isolation and accept partial results.
  2. Use Delta time travel to read a specific version of each silver table for the aggregation run. Correct answer.
  3. Disable concurrent writes to silver during the entire night.
  4. Copy silver tables to CSV before aggregating.

Explanation

Delta time travel lets the aggregation read a pinned table version, giving a consistent snapshot even while writers commit new versions. Reading without isolation can mix versions, blocking all writers harms throughput, and exporting to CSV loses transactional guarantees and adds latency.

Question 47Design and implement enterprise-scale data analytics solutions

An architect must design capacity governance so that a single runaway notebook cannot consume the whole Fabric capacity and starve executive reports. Which combination of controls is most effective?

  1. Increase the SKU size whenever usage spikes without monitoring.
  2. Give every workspace its own capacity so no sharing occurs.
  3. Use capacity metrics to identify heavy consumers, apply workspace-level assignment to a separate capacity or surge protection, and set autonomy settings such as optional throttling. Correct answer.
  4. Rely on the smoothing policy alone to protect interactive reports.

Explanation

Fabric provides capacity metrics for identifying heavy consumers and autonomy settings that control surge protection and throttling behavior for background workloads, protecting interactive experiences. Isolating noisy workloads onto separate capacities also helps. Smoothing alone does not prevent a runaway job, and blind SKU increases raise cost without addressing the cause.

Question 48Design and implement enterprise-scale data analytics solutions

A data architect must implement a security model where users in one country cannot see data from another country even when they query a shared lakehouse through the SQL analytics endpoint. Which layered design is most robust?

  1. Store data per country in separate lakehouses and grant access per country, or use row-level security in a semantic model over a shared table. Correct answer.
  2. Apply a workspace Viewer role to all users and hide the country column.
  3. Encrypt the lakehouse with a customer-managed key so users cannot read other countries.
  4. Grant all users read access to the lakehouse and rely on report filters to restrict country.

Explanation

Physical separation per country with per-country access, or row-level security in a semantic model, enforces the boundary at the data or model layer rather than trusting report authors. Report filters and hidden columns can be bypassed, and encryption at rest does not restrict which rows an authorized reader can query.

Question 49Design and implement enterprise-scale data analytics solutions

An architect is designing an enterprise deployment process where semantic model changes must pass automated validation, including DAX best-practice rules, before promotion to production. Which approach best fits Fabric?

  1. Rely on the workspace Viewer role to catch model defects during review.
  2. Enable Git integration only and assume commits to main are production-ready.
  3. Use deployment pipelines for promotion and integrate Best Practice Analyzer checks through external tooling in a CI process before deploying. Correct answer.
  4. Manually review each model in Desktop before copying it to production.

Explanation

Deployment pipelines handle stage promotion, while automated validation such as Best Practice Analyzer rules can run in a CI process against the model metadata before the production stage deploys. Manual review is inconsistent, committing to main does not validate, and Viewer roles provide no quality checks.

Question 50Design and implement enterprise-scale data analytics solutions

A data architect must choose a partitioning strategy for a very large gold Delta table that is almost always queried by order date range. Which strategy best supports pruning and efficient incremental writes?

  1. Partition by every column used in any filter.
  2. Partition by order date at an appropriate granularity such as month, and optionally use liquid clustering for other predicates. Correct answer.
  3. Do not partition and rely on full table scans.
  4. Partition by a high-cardinality customer ID column.

Explanation

Partitioning on the dominant filter column at a sensible granularity enables file pruning for date-range queries and supports efficient partition overwrite for incremental loads. High-cardinality keys create too many small files, no partitioning forces full scans, and over-partitioning multiplies metadata and small-file overhead.