Microsoft Certified: Fabric Analytics Engineer Associate — Practice Set 1

Question 1

Your organization uses Microsoft Fabric. You need to ensure that only members of the Finance team can view sensitive financial data in a lakehouse. What should you implement?

Accepted Answer

Row-level security (RLS) restricts data access at the row level within a lakehouse or semantic model, ensuring that only authorized users see specific rows of data. Workspace-level controls govern who can access the entire workspace, not specific rows within an item.

Question 2

You are an analytics engineer working in Microsoft Fabric. You need to allow external stakeholders to discover and trust a published semantic model. Which action should you take?

Accepted Answer

Endorsing an item (Promoted or Certified) signals to consumers that the item is trustworthy and recommended for use. Sensitivity labels classify data for compliance purposes but do not directly aid discoverability.

Question 3

You are managing a Microsoft Fabric workspace that contains several lakehouses and semantic models. You need to track changes to workspace items using source control. What should you configure?

Accepted Answer

Microsoft Fabric supports Git integration at the workspace level, enabling version control for workspace items. This allows teams to track changes, collaborate, and roll back as needed. Deployment pipelines manage environment promotion, not source history.

Question 4

You need to promote a semantic model from development to production in Microsoft Fabric while maintaining separate environments. Which feature should you use?

Accepted Answer

Deployment pipelines in Microsoft Fabric allow you to define Development, Test, and Production stages and promote content between them with controlled releases. The XMLA endpoint allows programmatic access to semantic models but is not designed for stage-based promotion.

Question 5

You are an analytics engineer at a financial services company. After modifying a lakehouse schema, several downstream reports break. You need to quickly identify which semantic models and reports depend on the lakehouse before making further changes. What should you do?

Accepted Answer

Impact analysis in Microsoft Fabric lets you view all downstream dependencies of a lakehouse, warehouse, dataflow, or semantic model directly from the Fabric portal. This allows you to see which items will be affected before making structural changes.

Question 6

You manage several semantic models in Microsoft Fabric. You need to deploy updates to semantic models programmatically and support third-party tools for model management. What should you configure?

Accepted Answer

The XMLA endpoint provides read/write access to semantic models in Microsoft Fabric Premium or Fabric capacity, enabling programmatic deployment and management using tools like Tabular Editor, SQL Server Management Studio, or custom scripts.

Question 7

You are setting up item-level access controls in a Microsoft Fabric workspace. Which two access control mechanisms can be applied at the item level to restrict data visibility? Choose 2.

Accepted Answer

Column-level security (CLS) restricts access to specific columns in a warehouse or semantic model. Object-level security (OLS) restricts access to specific tables or columns within a semantic model. Both are item-level controls that limit data visibility to authorized users.

Question 8

You are building an analytics solution in Microsoft Fabric. You need to discover available data assets across your organization's OneLake storage. Which tool should you use?

Accepted Answer

The OneLake catalog in Microsoft Fabric is the built-in tool for discovering and browsing data assets stored in OneLake across the organization. It provides a unified view of lakehouses, warehouses, and other Fabric items.

Question 9

You are designing a data model for a Microsoft Fabric lakehouse. You need to structure the data to optimize query performance for reporting. Which schema design should you implement?

Accepted Answer

A star schema with a central fact table and denormalized dimension tables is the recommended design for analytical workloads in Microsoft Fabric lakehouses. It optimizes query performance by minimizing joins and is the foundation for efficient Power BI semantic models.

Question 10

You need to query real-time telemetry data stored in an Eventhouse in Microsoft Fabric. Which query language should you use?

Accepted Answer

Kusto Query Language (KQL) is the primary query language for Eventhouses in Microsoft Fabric (formerly Real-Time Analytics). KQL is optimized for fast queries on large volumes of time-series and telemetry data.

Question 11

You are transforming data in a Microsoft Fabric lakehouse. You need to add calculated business logic as reusable objects that can be called by other queries. What should you create?

Accepted Answer

Views and stored procedures created via the SQL analytics endpoint of a lakehouse allow you to encapsulate reusable business logic that can be called by other SQL queries. This is the standard approach for creating reusable transformations in a lakehouse.

Question 12

Your organization wants to integrate Eventhouse data directly into a Microsoft Fabric semantic model without duplicating storage. What should you implement?

Accepted Answer

OneLake integration for Eventhouse exposes KQL database data as Delta Parquet tables in OneLake, allowing semantic models and other Fabric items to access the data without additional data movement or duplication.

Question 13

You are an analytics engineer at a retail company. Your source data includes a customer table with duplicate customer IDs caused by a data entry error in the source system. You need to resolve this issue during data preparation in a Microsoft Fabric warehouse. What should you do?

Accepted Answer

Using T-SQL with ROW_NUMBER() partitioned by customer ID and ordered by a timestamp column is the standard approach for identifying and removing duplicates while retaining the most recent or relevant record. This ensures data integrity in the warehouse before downstream consumption.

Question 14

You are building a data preparation pipeline in Microsoft Fabric. You need to select appropriate tools for aggregating and joining data from multiple sources. Which two tools can be used to perform these transformations? Choose 2.

Accepted Answer

The Visual Query Editor provides a no-code interface for selecting, filtering, aggregating, and joining data within a lakehouse. T-SQL queries in a Fabric warehouse support the full range of SQL aggregation and join operations. Both are valid tools for data transformation in Fabric.

Question 15

You need to connect a Microsoft Fabric lakehouse to external data stored in Azure Data Lake Storage Gen2 without copying the data. Which feature should you use?

Accepted Answer

OneLake shortcuts allow a Microsoft Fabric lakehouse to reference data stored in external sources, including Azure Data Lake Storage Gen2, Amazon S3, and Google Cloud Storage, without physically copying the data into OneLake. The data is accessed in place via the shortcut.

Question 16

You are building a data warehouse in Microsoft Fabric. Your source system sends daily full snapshots. You need to implement a loading pattern that captures only new and changed records efficiently. What should you implement?

Accepted Answer

An incremental load using a watermark (such as a LastModified timestamp or surrogate key) is the standard pattern for efficiently loading only new and changed records, reducing processing time and resource consumption compared to full reloads.

Question 17

You are designing a data model for a Microsoft Fabric warehouse. Your dimension tables contain redundant attributes spread across multiple related tables. You need to optimize query performance. What should you do?

Accepted Answer

Denormalizing dimension tables into flat, wide tables (conforming to a star schema) reduces join complexity and improves query performance for analytical workloads. This is the recommended approach in Microsoft Fabric warehouses and lakehouses.

Question 18

You are querying a Microsoft Fabric lakehouse using DAX. You need to calculate a running total of sales by date. Which DAX function category is most appropriate?

Accepted Answer

DAX windowing functions such as WINDOW and OFFSET are designed for calculations that span ordered ranges of rows, making them suitable for running totals and period-over-period comparisons. Iterator functions like SUMX evaluate expressions row by row but do not natively support ordered window frames.

Question 19

You are an analytics engineer. Your team needs to merge customer records from two separate source systems in a Microsoft Fabric lakehouse. One system uses integer customer IDs and the other uses string-based GUIDs. You need to join these tables correctly. What should you do first?

Accepted Answer

Before merging or joining tables from different source systems, you must ensure that the join key columns have compatible data types. Converting both customer ID columns to a consistent type (e.g., VARCHAR or BIGINT) prevents implicit conversion errors and ensures accurate join results.

Question 20

You are designing a semantic model in Microsoft Fabric. You need to implement a pattern that handles many-to-many relationships between sales orders and products correctly. Which two approaches can be used? Choose 2.

Accepted Answer

Microsoft Fabric semantic models support two approaches for many-to-many relationships: (1) using a bridge/junction table to decompose the relationship into two one-to-many relationships, and (2) configuring a direct many-to-many cardinality relationship in the model. Both are valid depending on the scenario.

Question 21

You are building a semantic model in Microsoft Fabric and need to access data from a lakehouse without importing a copy into the model. The data volume is very large. Which storage mode should you choose?

Accepted Answer

Direct Lake mode is a Microsoft Fabric-specific storage mode that allows semantic models to read Delta Parquet files directly from OneLake without importing data into the model's in-memory cache. It combines the performance of Import mode with the freshness of Direct Query for large datasets stored in Fabric lakehouses or warehouses.

Question 22

You are optimizing DAX query performance in a large Microsoft Fabric semantic model. You need to avoid recalculating the same intermediate result multiple times within a measure. What should you use?

Accepted Answer

DAX variables declared with VAR store intermediate calculation results that can be reused within a measure without recalculation. This improves both readability and performance, especially when the same expression would otherwise be evaluated multiple times in a complex measure.

Question 23

You have a Microsoft Fabric semantic model using Direct Lake mode on a lakehouse. During peak hours, some queries fall back to Direct Query mode, causing performance degradation. You need to prevent this fallback behavior. What should you configure?

Accepted Answer

In Direct Lake mode, fallback behavior can be set to 'Error' to prevent queries from automatically falling back to Direct Query mode. This ensures consistent Direct Lake performance and surfaces issues that need to be resolved at the data or model level rather than silently degrading to slower query execution.

Question 24

You are an analytics engineer at a manufacturing company. Your semantic model contains five years of daily transaction data. Report visuals are slow to render because DAX queries must process millions of rows for every date range selected. You need to improve query performance for historical data while keeping recent data fast. What should you implement?

Accepted Answer

Incremental refresh partitions the semantic model's fact table by date ranges and loads only new or changed data rather than the full historical dataset. By defining a rolling refresh window, recent data is refreshed frequently while older historical partitions are retained in memory without re-processing, significantly improving both refresh and query performance.

Question 25

You are designing a composite model in Microsoft Fabric. You need to identify which two storage modes can be combined in a composite model. Choose 2.

Accepted Answer

A composite model in Microsoft Fabric allows tables to use different storage modes within the same semantic model. Import mode tables load data into the in-memory cache, while Direct Lake mode tables read directly from OneLake Delta Parquet files. Combining them allows different tables to use the most appropriate storage strategy.

DP-600 — Fabric Analytics Engineer Associate