Practice Exams:

View All

Microsoft Fabric Analytics Engineer DP-600 Data Transformation And Analytical Shaping Practice Test

Skill 2.2 – 135 original questions

This Microsoft DP-600 practice test focuses on data transformation and analytical shaping through original scenario-based questions aligned to the active DP-600 skills measured as of July 21, 2026. Use the complete ExamSnap DP-600 collection for broader practice across Microsoft Fabric analytics lifecycle, data preparation, querying, and semantic modeling. For broader exam preparation, review the Microsoft DP-600 Exam Dumps page.

Instructions: Select the best answer for each question. Review the explanation after answering; each option includes a reason it is or is not the strongest choice for the scenario.

Question 1

A solution architect reviewing Trey Research’s marketing semantic model asks the customer insights team to publish a reusable virtual table that encapsulates a commonly used T-SQL SELECT without storing another copy of the data. Since least privilege must be preserved, which recommendation is strongest? Existing users should keep their current access.

Use an Eventhouse with a KQL database
Filter or remediate rows with missing mandatory keys before loading the curated layer
Precompute the required daily aggregate table or result
Create a SQL view
Add a calculated or derived column in the transformation layer

Correct answer: D

Why: A view encapsulates a reusable query and presents it as a virtual table. This directly matches the stated requirement.

Option review:

A: Eventhouse and KQL databases are optimized for high-volume real-time, log, and time-series analytics. This can be valid for ‘Choose between different data stores’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

B: Mandatory-key quality rules should be enforced before downstream joins and aggregations depend on those records. This can be valid for ‘Identify and resolve duplicate data, missing data, or null values’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

C: Pre-aggregation can reduce repeated scanning of detail rows when consumers consistently query a higher-level grain. This can be valid for ‘Aggregate data’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

D: A view encapsulates a reusable query and presents it as a virtual table. This directly matches the stated requirement.

E: A derived column enriches the dataset with business logic while preserving the source fields. This can be valid for ‘Enrich data by adding new columns or tables’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

Learning point: Create a SQL view

Question 2

The next sprint for Alpine Ski House’s supply-chain lakehouse includes a task to encapsulate reusable parameterized SQL logic that returns a value or table result. The acceptance criteria add that the rollout must support controlled validation. Which Fabric or Power BI action is appropriate? Existing users should keep their current access.

Precompute the required daily aggregate table or result
Use the Visual Query Editor to build the query graphically
Create a SQL function
Apply an explicit predicate that excludes the test rows
Append or union the row sets instead of joining them

Correct answer: C

Why: A function is appropriate for reusable parameterized logic that returns a value or table expression. This directly matches the stated requirement.

Option review:

A: Pre-aggregation can reduce repeated scanning of detail rows when consumers consistently query a higher-level grain. This can be valid for ‘Aggregate data’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

B: The Visual Query Editor provides a graphical experience for selecting, filtering, joining, and aggregating supported data. This can be valid for ‘Select, filter, and aggregate data by using the Visual query editor’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

C: A function is appropriate for reusable parameterized logic that returns a value or table expression. This directly matches the stated requirement.

D: A deterministic filter predicate is the direct way to prevent unwanted rows from reaching downstream analysis. This can be valid for ‘Filter data’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

E: Union or append combines rows from compatible schemas, whereas a join combines columns based on matching keys. This can be valid for ‘Merge or join data’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

Learning point: Create a SQL function

Question 3

Contoso is troubleshooting a design decision in the IoT telemetry solution. The desired end state is to run a reusable multi-statement T-SQL operation that performs procedural data-management steps; the team wants predictable performance and behavior. Which change should the BI platform team make? Existing users should keep their current access.

Create a stored procedure
Use a Fabric data pipeline Copy activity
Build it in the Visual Query Editor
Use a DAX query with functions such as SUMMARIZECOLUMNS and CALCULATE as appropriate
Apply an explicit predicate that excludes the test rows

Correct answer: A

Why: A stored procedure packages procedural multi-statement T-SQL operations for repeatable execution. This directly matches the stated requirement.

Option review:

A: A stored procedure packages procedural multi-statement T-SQL operations for repeatable execution. This directly matches the stated requirement.

B: Copy activity is suited to orchestrated bulk data movement into Fabric with scheduling and monitoring. This can be valid for ‘Ingest or access data as needed’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

C: The visual editor is intended for graphical query construction while still supporting common relational operations. This can be valid for ‘Select, filter, and aggregate data by using the Visual query editor’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

D: DAX queries and functions operate on semantic-model metadata, relationships, and filter context. This can be valid for ‘Select, filter, and aggregate data by using DAX’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

E: A deterministic filter predicate is the direct way to prevent unwanted rows from reaching downstream analysis. This can be valid for ‘Filter data’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

Learning point: Create a stored procedure

Question 4

For the finance reporting platform, Wingtip Toys has documented a business requirement to publish a reusable virtual table that encapsulates a commonly used T-SQL SELECT without storing another copy of the data. The security analytics team must meet it in a way where the choice should use a native Fabric capability. What is the best choice? No unrelated workspace or model permissions should be changed.

Use KQL operators such as where, summarize, and bin against the KQL database
Create a SQL view
Use a Fabric data pipeline Copy activity
Create and manage a shared Fabric connection for the data source
Identify the duplicate keys and deduplicate according to the authoritative business rule

Correct answer: B

Why: A view encapsulates a reusable query and presents it as a virtual table. This directly matches the stated requirement.

Option review:

A: KQL is optimized for filtering and aggregating log, telemetry, and time-series data in Eventhouse. This can be valid for ‘Select, filter, and aggregate data by using KQL’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

B: A view encapsulates a reusable query and presents it as a virtual table. This directly matches the stated requirement.

C: Copy activity is suited to orchestrated bulk data movement into Fabric with scheduling and monitoring. This can be valid for ‘Ingest or access data as needed’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

D: Fabric connections centralize connection details and credentials so supported items can reuse governed access. This can be valid for ‘Create a data connection’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

E: Deduplication must preserve the correct authoritative row while eliminating records that would inflate analytical results. This can be valid for ‘Identify and resolve duplicate data, missing data, or null values’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

Learning point: Create a SQL view

Question 5

A governance review of Proseware’s retail performance dashboard asks for evidence that the solution can encapsulate reusable parameterized SQL logic that returns a value or table result. Because the design should minimize duplicated data, which action should be approved? No unrelated workspace or model permissions should be changed.

Use Real-Time hub to browse and connect to real-time data sources
Use Direct Lake against the OneLake-backed data
Use a Fabric data pipeline Copy activity
Use an inner join on the matching business or surrogate key
Create a SQL function

Correct answer: E

Why: A function is appropriate for reusable parameterized logic that returns a value or table expression. This directly matches the stated requirement.

Option review:

A: Real-Time hub is the Fabric experience for discovering, connecting to, and managing real-time event streams and sources. This can be valid for ‘Discover data by using OneLake catalog and Real-Time hub’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

B: Direct Lake lets semantic models query OneLake Delta data with in-memory analytical performance without a traditional import copy. This can be valid for ‘Implement OneLake integration for Eventhouse and semantic models’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

D: An inner join returns only rows with matching keys on both sides. This can be valid for ‘Merge or join data’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

E: A function is appropriate for reusable parameterized logic that returns a value or table expression. This directly matches the stated requirement.

Learning point: Create a SQL function

Question 6

Before expanding the marketing semantic model, the customer insights team at Blue Yonder Airlines must run a reusable multi-statement T-SQL operation that performs procedural data-management steps. The rollout plan says that least privilege must be preserved. Which option most directly addresses the requirement? No unrelated workspace or model permissions should be changed.

Create a stored procedure
Write the required SQL query against the endpoint
Precompute the required daily aggregate table or result
Append or union the row sets instead of joining them
Use the Visual Query Editor to build the query graphically

Correct answer: A

Why: A stored procedure packages procedural multi-statement T-SQL operations for repeatable execution. This directly matches the stated requirement.

Option review:

A: A stored procedure packages procedural multi-statement T-SQL operations for repeatable execution. This directly matches the stated requirement.

B: The SQL analytics endpoint supports relational querying with T-SQL for selection, filtering, joins, and aggregation. This can be valid for ‘Select, filter, and aggregate data by using SQL’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

D: Union or append combines rows from compatible schemas, whereas a join combines columns based on matching keys. This can be valid for ‘Merge or join data’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

E: The Visual Query Editor provides a graphical experience for selecting, filtering, joining, and aggregating supported data. This can be valid for ‘Select, filter, and aggregate data by using the Visual query editor’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

Learning point: Create a stored procedure

Question 7

Fabrikam is redesigning its supply-chain lakehouse. The retail insights team must publish a reusable virtual table that encapsulates a commonly used T-SQL SELECT without storing another copy of the data. In addition, the rollout must support controlled validation. Which action is the best fit? The team will validate the change first in a nonproduction environment.

Create a DAX measure using the required aggregation and filter context logic
Use Dataflow Gen2 for the ingestion and transformation flow
Write the required SQL query against the endpoint
Append or union the row sets instead of joining them
Create a SQL view

Correct answer: E

Why: A view encapsulates a reusable query and presents it as a virtual table. This directly matches the stated requirement.

Option review:

A: DAX measures evaluate in semantic-model filter context and are the native way to define reusable analytical calculations. This can be valid for ‘Select, filter, and aggregate data by using DAX’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

B: Dataflow Gen2 provides a Power Query based low-code ingestion and transformation experience. This can be valid for ‘Ingest or access data as needed’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

C: The SQL analytics endpoint supports relational querying with T-SQL for selection, filtering, joins, and aggregation. This can be valid for ‘Select, filter, and aggregate data by using SQL’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

E: A view encapsulates a reusable query and presents it as a virtual table. This directly matches the stated requirement.

Learning point: Create a SQL view

Question 8

During a design review for Litware’s IoT telemetry solution, one requirement is non-negotiable: encapsulate reusable parameterized SQL logic that returns a value or table result. Because the team wants predictable performance and behavior, what should the BI platform team implement? The team will validate the change first in a nonproduction environment.

Convert the column to an appropriate date or datetime data type
Handle nulls explicitly with a null-replacement rule such as COALESCE
Create a SQL function
Use the OneLake catalog to discover and evaluate available data products
Create a fact table at a defined grain with separate dimension tables

Correct answer: C

Why: A function is appropriate for reusable parameterized logic that returns a value or table expression. This directly matches the stated requirement.

Option review:

A: Using the correct native type enables valid date comparisons, filtering, and time intelligence. This can be valid for ‘Convert column data types’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

B: Explicit null handling avoids treating missing values as valid zeroes unless the business rule specifically requires that replacement. This can be valid for ‘Identify and resolve duplicate data, missing data, or null values’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

C: A function is appropriate for reusable parameterized logic that returns a value or table expression. This directly matches the stated requirement.

D: The OneLake catalog is designed to discover governed Fabric data items across the organization. This can be valid for ‘Discover data by using OneLake catalog and Real-Time hub’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

E: A star schema places measurable events in a fact table and descriptive context in dimensions, simplifying analytics and improving model usability. This can be valid for ‘Implement a star schema for a lakehouse or warehouse’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

Learning point: Create a SQL function

Question 9

The security analytics team at Woodgrove Bank is preparing the next release of its finance reporting platform. They need to run a reusable multi-statement T-SQL operation that performs procedural data-management steps; the choice should use a native Fabric capability. Which choice most directly satisfies the requirement? The team will validate the change first in a nonproduction environment.

Use KQL project and where operators
Create a stored procedure
Filter or remediate rows with missing mandatory keys before loading the curated layer
Denormalize selected attributes into the analytical table
Create a DAX measure using the required aggregation and filter context logic

Correct answer: B

Why: A stored procedure packages procedural multi-statement T-SQL operations for repeatable execution. This directly matches the stated requirement.

Option review:

A: KQL provides concise operators for selecting columns and filtering event data. This can be valid for ‘Select, filter, and aggregate data by using KQL’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

B: A stored procedure packages procedural multi-statement T-SQL operations for repeatable execution. This directly matches the stated requirement.

C: Mandatory-key quality rules should be enforced before downstream joins and aggregations depend on those records. This can be valid for ‘Identify and resolve duplicate data, missing data, or null values’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

D: Denormalization trades additional storage and duplication for simpler reads and fewer joins in analytical workloads. This can be valid for ‘Denormalize data’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

E: DAX measures evaluate in semantic-model filter context and are the native way to define reusable analytical calculations. This can be valid for ‘Select, filter, and aggregate data by using DAX’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

Learning point: Create a stored procedure

Question 10

A production readiness review at Coho Winery found a gap in the retail performance dashboard. The remediation must publish a reusable virtual table that encapsulates a commonly used T-SQL SELECT without storing another copy of the data, and the design should minimize duplicated data. What is the most appropriate action? The solution must work with the current Fabric architecture rather than a parallel custom platform.

Use KQL project and where operators
Create a SQL view
Handle nulls explicitly with a null-replacement rule such as COALESCE
Use a Fabric data pipeline Copy activity
Build it in the Visual Query Editor

Correct answer: B

Why: A view encapsulates a reusable query and presents it as a virtual table. This directly matches the stated requirement.

Option review:

B: A view encapsulates a reusable query and presents it as a virtual table. This directly matches the stated requirement.

C: Explicit null handling avoids treating missing values as valid zeroes unless the business rule specifically requires that replacement. This can be valid for ‘Identify and resolve duplicate data, missing data, or null values’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

D: Copy activity is suited to orchestrated bulk data movement into Fabric with scheduling and monitoring. This can be valid for ‘Ingest or access data as needed’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

E: The visual editor is intended for graphical query construction while still supporting common relational operations. This can be valid for ‘Select, filter, and aggregate data by using the Visual query editor’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

Learning point: Create a SQL view

Question 11

For a new phase of the marketing semantic model, Adventure Works asks the customer insights team to encapsulate reusable parameterized SQL logic that returns a value or table result. The architecture decision record also states that least privilege must be preserved. Which approach should be selected? The solution must work with the current Fabric architecture rather than a parallel custom platform.

Convert the column to an appropriate date or datetime data type
Use a Fabric warehouse
Use Direct Lake against the OneLake-backed data
Create a SQL function
Use T-SQL with SELECT, JOIN, WHERE, GROUP BY, and aggregate functions as required

Correct answer: D

Why: A function is appropriate for reusable parameterized logic that returns a value or table expression. This directly matches the stated requirement.

Option review:

B: A warehouse is optimized for structured relational analytics and T-SQL based development in Fabric. This can be valid for ‘Choose between different data stores’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

C: Direct Lake lets semantic models query OneLake Delta data with in-memory analytical performance without a traditional import copy. This can be valid for ‘Implement OneLake integration for Eventhouse and semantic models’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

D: A function is appropriate for reusable parameterized logic that returns a value or table expression. This directly matches the stated requirement.

E: SQL is the native relational query language for warehouse and SQL analytics endpoint workloads. This can be valid for ‘Select, filter, and aggregate data by using SQL’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

Learning point: Create a SQL function

Question 12

Tailspin Toys is standardizing how the supply-chain lakehouse is managed. The immediate goal is to run a reusable multi-statement T-SQL operation that performs procedural data-management steps. Given that the rollout must support controlled validation, which option should the retail insights team choose? The solution must work with the current Fabric architecture rather than a parallel custom platform.

Use a DAX query with functions such as SUMMARIZECOLUMNS and CALCULATE as appropriate
Build it in the Visual Query Editor
Use a Fabric lakehouse
Create a stored procedure
Apply the row filter as early as practical in the query or transformation

Correct answer: D

Why: A stored procedure packages procedural multi-statement T-SQL operations for repeatable execution. This directly matches the stated requirement.

Option review:

A: DAX queries and functions operate on semantic-model metadata, relationships, and filter context. This can be valid for ‘Select, filter, and aggregate data by using DAX’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

B: The visual editor is intended for graphical query construction while still supporting common relational operations. This can be valid for ‘Select, filter, and aggregate data by using the Visual query editor’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

C: A lakehouse combines OneLake Delta storage with Spark-oriented engineering and a SQL analytics endpoint. This can be valid for ‘Choose between different data stores’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

D: A stored procedure packages procedural multi-statement T-SQL operations for repeatable execution. This directly matches the stated requirement.

E: Early filtering reduces the volume of data moved and processed while preserving only the rows required by the workload. This can be valid for ‘Filter data’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

Learning point: Create a stored procedure

Question 13

An internal audit of Fourth Coffee’s IoT telemetry solution identifies this requirement: publish a reusable virtual table that encapsulates a commonly used T-SQL SELECT without storing another copy of the data. The BI platform team also notes that the team wants predictable performance and behavior. What should they do? The design decision will be reviewed by both data engineering and BI owners.

Model the warehouse with conformed dimensions around fact tables
Group the data by month and region and calculate the required summary measures
Use the OneLake catalog to discover and evaluate available data products
Create a SQL view
Add a calculated or derived column in the transformation layer

Correct answer: D

Why: A view encapsulates a reusable query and presents it as a virtual table. This directly matches the stated requirement.

Option review:

A: Conformed dimensions and explicit fact grain are core star-schema practices for reliable analytical joins. This can be valid for ‘Implement a star schema for a lakehouse or warehouse’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

B: Aggregation changes detailed rows into grouped summaries at the required analytical grain. This can be valid for ‘Aggregate data’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

C: The OneLake catalog is designed to discover governed Fabric data items across the organization. This can be valid for ‘Discover data by using OneLake catalog and Real-Time hub’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

D: A view encapsulates a reusable query and presents it as a virtual table. This directly matches the stated requirement.

Learning point: Create a SQL view

Question 14

The finance reporting platform at Wide World Importers is moving from proof of concept to production. Before rollout, the security analytics team must encapsulate reusable parameterized SQL logic that returns a value or table result, while ensuring that the choice should use a native Fabric capability. Which action best meets both needs? The design decision will be reviewed by both data engineering and BI owners.

Use Direct Lake against the OneLake-backed data
Cast the column to the appropriate numeric type before aggregation
Enable the supported OneLake availability or integration for the Eventhouse data
Write the required SQL query against the endpoint
Create a SQL function

Correct answer: E

Why: A function is appropriate for reusable parameterized logic that returns a value or table expression. This directly matches the stated requirement.

Option review:

A: Direct Lake lets semantic models query OneLake Delta data with in-memory analytical performance without a traditional import copy. This can be valid for ‘Implement OneLake integration for Eventhouse and semantic models’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

B: Numeric calculations require a compatible numeric type and should avoid relying on implicit conversion. This can be valid for ‘Convert column data types’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

C: OneLake integration makes supported Eventhouse data discoverable and accessible through OneLake for cross-engine use. This can be valid for ‘Implement OneLake integration for Eventhouse and semantic models’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

D: The SQL analytics endpoint supports relational querying with T-SQL for selection, filtering, joins, and aggregation. This can be valid for ‘Select, filter, and aggregate data by using SQL’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

E: A function is appropriate for reusable parameterized logic that returns a value or table expression. This directly matches the stated requirement.

Learning point: Create a SQL function

Question 15

Northwind Traders has a change request for the retail performance dashboard: run a reusable multi-statement T-SQL operation that performs procedural data-management steps. The Fabric center of excellence wants a solution where the design should minimize duplicated data. Which implementation is most suitable? The design decision will be reviewed by both data engineering and BI owners.

Use the Visual Query Editor to build the query graphically
Model the warehouse with conformed dimensions around fact tables
Use KQL operators such as where, summarize, and bin against the KQL database
Use KQL project and where operators
Create a stored procedure

Correct answer: E

Why: A stored procedure packages procedural multi-statement T-SQL operations for repeatable execution. This directly matches the stated requirement.

Option review:

A: The Visual Query Editor provides a graphical experience for selecting, filtering, joining, and aggregating supported data. This can be valid for ‘Select, filter, and aggregate data by using the Visual query editor’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

B: Conformed dimensions and explicit fact grain are core star-schema practices for reliable analytical joins. This can be valid for ‘Implement a star schema for a lakehouse or warehouse’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

C: KQL is optimized for filtering and aggregating log, telemetry, and time-series data in Eventhouse. This can be valid for ‘Select, filter, and aggregate data by using KQL’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

D: KQL provides concise operators for selecting columns and filtering event data. This can be valid for ‘Select, filter, and aggregate data by using KQL’, but it does not directly satisfy the scenario requirement being tested under ‘Create views, functions, and stored procedures’.

E: A stored procedure packages procedural multi-statement T-SQL operations for repeatable execution. This directly matches the stated requirement.

Learning point: Create a stored procedure

Question 16

A solution architect reviewing Trey Research’s customer 360 model asks the finance analytics squad to derive a business classification from existing source fields during preparation. Since least privilege must be preserved, which recommendation is strongest? Existing users should keep their current access.

Build it in the Visual Query Editor
Configure the appropriate gateway and connection credentials
Use a Fabric warehouse
Use Dataflow Gen2 for the ingestion and transformation flow
Add a calculated or derived column in the transformation layer

Correct answer: E

Why: A derived column enriches the dataset with business logic while preserving the source fields. This directly matches the stated requirement.

Option review:

A: The visual editor is intended for graphical query construction while still supporting common relational operations. This can be valid for ‘Select, filter, and aggregate data by using the Visual query editor’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

B: An on-premises gateway provides the network bridge while the connection stores the credentials and source details. This can be valid for ‘Create a data connection’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

C: A warehouse is optimized for structured relational analytics and T-SQL based development in Fabric. This can be valid for ‘Choose between different data stores’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

D: Dataflow Gen2 provides a Power Query based low-code ingestion and transformation experience. This can be valid for ‘Ingest or access data as needed’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

E: A derived column enriches the dataset with business logic while preserving the source fields. This directly matches the stated requirement.

Learning point: Add a calculated or derived column in the transformation layer

Question 17

The next sprint for Alpine Ski House’s sales analytics solution includes a task to create a reusable reference table from source attributes to support downstream analytics. The acceptance criteria add that the rollout must support controlled validation. Which Fabric or Power BI action is appropriate? Existing users should keep their current access.

Configure the appropriate gateway and connection credentials
Create the additional dimension or lookup table during transformation
Create a fact table at a defined grain with separate dimension tables
Identify the duplicate keys and deduplicate according to the authoritative business rule
Create a DAX measure using the required aggregation and filter context logic

Correct answer: B

Why: Adding a curated table can enrich the model with reusable business context for downstream joins and analysis. This directly matches the stated requirement.

Option review:

A: An on-premises gateway provides the network bridge while the connection stores the credentials and source details. This can be valid for ‘Create a data connection’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

B: Adding a curated table can enrich the model with reusable business context for downstream joins and analysis. This directly matches the stated requirement.

C: A star schema places measurable events in a fact table and descriptive context in dimensions, simplifying analytics and improving model usability. This can be valid for ‘Implement a star schema for a lakehouse or warehouse’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

D: Deduplication must preserve the correct authoritative row while eliminating records that would inflate analytical results. This can be valid for ‘Identify and resolve duplicate data, missing data, or null values’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

Learning point: Create the additional dimension or lookup table during transformation

Question 18

Contoso is troubleshooting a design decision in the risk analytics environment. The desired end state is to derive a business classification from existing source fields during preparation; the team wants predictable performance and behavior. Which change should the operations data team make? No unrelated workspace or model permissions should be changed.

Use Direct Lake against the OneLake-backed data
Use the Visual Query Editor to build the query graphically
Use a left outer join from customers to orders
Add a calculated or derived column in the transformation layer
Apply an explicit predicate that excludes the test rows

Correct answer: D

Why: A derived column enriches the dataset with business logic while preserving the source fields. This directly matches the stated requirement.

Option review:

C: A left outer join retains all rows from the left input and adds matching rows from the right input when available. This can be valid for ‘Merge or join data’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

D: A derived column enriches the dataset with business logic while preserving the source fields. This directly matches the stated requirement.

Learning point: Add a calculated or derived column in the transformation layer

Question 19

For the service-operations warehouse, Wingtip Toys has documented a business requirement to create a reusable reference table from source attributes to support downstream analytics. The data governance group must meet it in a way where the choice should use a native Fabric capability. What is the best choice? No unrelated workspace or model permissions should be changed.

Precompute the required daily aggregate table or result
Create the additional dimension or lookup table during transformation
Use T-SQL with SELECT, JOIN, WHERE, GROUP BY, and aggregate functions as required
Apply an explicit predicate that excludes the test rows
Apply the row filter as early as practical in the query or transformation

Correct answer: B

Why: Adding a curated table can enrich the model with reusable business context for downstream joins and analysis. This directly matches the stated requirement.

Option review:

B: Adding a curated table can enrich the model with reusable business context for downstream joins and analysis. This directly matches the stated requirement.

C: SQL is the native relational query language for warehouse and SQL analytics endpoint workloads. This can be valid for ‘Select, filter, and aggregate data by using SQL’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

Learning point: Create the additional dimension or lookup table during transformation

Question 20

A governance review of Proseware’s executive reporting workspace asks for evidence that the solution can derive a business classification from existing source fields during preparation. Because the design should minimize duplicated data, which action should be approved? The team will validate the change first in a nonproduction environment.

Use KQL operators such as where, summarize, and bin against the KQL database
Create a OneLake shortcut to the existing data
Denormalize selected attributes into the analytical table
Use a Fabric data pipeline Copy activity
Add a calculated or derived column in the transformation layer

Correct answer: E

Why: A derived column enriches the dataset with business logic while preserving the source fields. This directly matches the stated requirement.

Option review:

B: A shortcut provides logical access to supported external or OneLake data without copying the underlying files. This can be valid for ‘Ingest or access data as needed’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

C: Denormalization trades additional storage and duplication for simpler reads and fewer joins in analytical workloads. This can be valid for ‘Denormalize data’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

E: A derived column enriches the dataset with business logic while preserving the source fields. This directly matches the stated requirement.

Learning point: Add a calculated or derived column in the transformation layer

Question 21

Before expanding the customer 360 model, the finance analytics squad at Blue Yonder Airlines must create a reusable reference table from source attributes to support downstream analytics. The rollout plan says that least privilege must be preserved. Which option most directly addresses the requirement? The team will validate the change first in a nonproduction environment.

Create the additional dimension or lookup table during transformation
Create a DAX measure using the required aggregation and filter context logic
Use the Visual Query Editor to build the query graphically
Model the warehouse with conformed dimensions around fact tables
Create a stored procedure

Correct answer: A

Why: Adding a curated table can enrich the model with reusable business context for downstream joins and analysis. This directly matches the stated requirement.

Option review:

A: Adding a curated table can enrich the model with reusable business context for downstream joins and analysis. This directly matches the stated requirement.

B: DAX measures evaluate in semantic-model filter context and are the native way to define reusable analytical calculations. This can be valid for ‘Select, filter, and aggregate data by using DAX’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

C: The Visual Query Editor provides a graphical experience for selecting, filtering, joining, and aggregating supported data. This can be valid for ‘Select, filter, and aggregate data by using the Visual query editor’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

D: Conformed dimensions and explicit fact grain are core star-schema practices for reliable analytical joins. This can be valid for ‘Implement a star schema for a lakehouse or warehouse’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

E: A stored procedure packages procedural multi-statement T-SQL operations for repeatable execution. This can be valid for ‘Create views, functions, and stored procedures’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

Learning point: Create the additional dimension or lookup table during transformation

Question 22

Fabrikam is redesigning its sales analytics solution. The analytics engineering team must derive a business classification from existing source fields during preparation. In addition, the rollout must support controlled validation. Which action is the best fit? The solution must work with the current Fabric architecture rather than a parallel custom platform.

Append or union the row sets instead of joining them
Build it in the Visual Query Editor
Create a DAX measure using the required aggregation and filter context logic
Create a SQL function
Add a calculated or derived column in the transformation layer

Correct answer: E

Why: A derived column enriches the dataset with business logic while preserving the source fields. This directly matches the stated requirement.

Option review:

A: Union or append combines rows from compatible schemas, whereas a join combines columns based on matching keys. This can be valid for ‘Merge or join data’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

C: DAX measures evaluate in semantic-model filter context and are the native way to define reusable analytical calculations. This can be valid for ‘Select, filter, and aggregate data by using DAX’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

D: A function is appropriate for reusable parameterized logic that returns a value or table expression. This can be valid for ‘Create views, functions, and stored procedures’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

E: A derived column enriches the dataset with business logic while preserving the source fields. This directly matches the stated requirement.

Learning point: Add a calculated or derived column in the transformation layer

Question 23

During a design review for Litware’s risk analytics environment, one requirement is non-negotiable: create a reusable reference table from source attributes to support downstream analytics. Because the team wants predictable performance and behavior, what should the operations data team implement? The solution must work with the current Fabric architecture rather than a parallel custom platform.

Use a Fabric warehouse
Use a DAX query with functions such as SUMMARIZECOLUMNS and CALCULATE as appropriate
Use an inner join on the matching business or surrogate key
Create the additional dimension or lookup table during transformation
Use KQL operators such as where, summarize, and bin against the KQL database

Correct answer: D

Why: Adding a curated table can enrich the model with reusable business context for downstream joins and analysis. This directly matches the stated requirement.

Option review:

A: A warehouse is optimized for structured relational analytics and T-SQL based development in Fabric. This can be valid for ‘Choose between different data stores’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

B: DAX queries and functions operate on semantic-model metadata, relationships, and filter context. This can be valid for ‘Select, filter, and aggregate data by using DAX’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

C: An inner join returns only rows with matching keys on both sides. This can be valid for ‘Merge or join data’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

D: Adding a curated table can enrich the model with reusable business context for downstream joins and analysis. This directly matches the stated requirement.

E: KQL is optimized for filtering and aggregating log, telemetry, and time-series data in Eventhouse. This can be valid for ‘Select, filter, and aggregate data by using KQL’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

Learning point: Create the additional dimension or lookup table during transformation

Question 24

The data governance group at Woodgrove Bank is preparing the next release of its service-operations warehouse. They need to derive a business classification from existing source fields during preparation; the choice should use a native Fabric capability. Which choice most directly satisfies the requirement? The design decision will be reviewed by both data engineering and BI owners.

Add a calculated or derived column in the transformation layer
Use a DAX query with functions such as SUMMARIZECOLUMNS and CALCULATE as appropriate
Use a Fabric lakehouse
Use the Visual Query Editor to build the query graphically
Append or union the row sets instead of joining them

Correct answer: A

Why: A derived column enriches the dataset with business logic while preserving the source fields. This directly matches the stated requirement.

Option review:

A: A derived column enriches the dataset with business logic while preserving the source fields. This directly matches the stated requirement.

D: The Visual Query Editor provides a graphical experience for selecting, filtering, joining, and aggregating supported data. This can be valid for ‘Select, filter, and aggregate data by using the Visual query editor’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

Learning point: Add a calculated or derived column in the transformation layer

Question 25

A production readiness review at Coho Winery found a gap in the executive reporting workspace. The remediation must create a reusable reference table from source attributes to support downstream analytics, and the design should minimize duplicated data. What is the most appropriate action? The design decision will be reviewed by both data engineering and BI owners.

Create the additional dimension or lookup table during transformation
Create a stored procedure
Use KQL operators such as where, summarize, and bin against the KQL database
Configure the appropriate gateway and connection credentials
Use an inner join on the matching business or surrogate key

Correct answer: A

Why: Adding a curated table can enrich the model with reusable business context for downstream joins and analysis. This directly matches the stated requirement.

Option review:

A: Adding a curated table can enrich the model with reusable business context for downstream joins and analysis. This directly matches the stated requirement.

B: A stored procedure packages procedural multi-statement T-SQL operations for repeatable execution. This can be valid for ‘Create views, functions, and stored procedures’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

D: An on-premises gateway provides the network bridge while the connection stores the credentials and source details. This can be valid for ‘Create a data connection’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

E: An inner join returns only rows with matching keys on both sides. This can be valid for ‘Merge or join data’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

Learning point: Create the additional dimension or lookup table during transformation

Question 26

For a new phase of the customer 360 model, Adventure Works asks the finance analytics squad to derive a business classification from existing source fields during preparation. The architecture decision record also states that least privilege must be preserved. Which approach should be selected? Existing users should keep their current access.

Add a calculated or derived column in the transformation layer
Use the OneLake catalog to discover and evaluate available data products
Denormalize selected attributes into the analytical table
Use a left outer join from customers to orders
Cast the column to the appropriate numeric type before aggregation

Correct answer: A

Why: A derived column enriches the dataset with business logic while preserving the source fields. This directly matches the stated requirement.

Option review:

A: A derived column enriches the dataset with business logic while preserving the source fields. This directly matches the stated requirement.

B: The OneLake catalog is designed to discover governed Fabric data items across the organization. This can be valid for ‘Discover data by using OneLake catalog and Real-Time hub’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

D: A left outer join retains all rows from the left input and adds matching rows from the right input when available. This can be valid for ‘Merge or join data’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

E: Numeric calculations require a compatible numeric type and should avoid relying on implicit conversion. This can be valid for ‘Convert column data types’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

Learning point: Add a calculated or derived column in the transformation layer

Question 27

Tailspin Toys is standardizing how the sales analytics solution is managed. The immediate goal is to create a reusable reference table from source attributes to support downstream analytics. Given that the rollout must support controlled validation, which option should the analytics engineering team choose? Existing users should keep their current access.

Use an Eventhouse with a KQL database
Create the additional dimension or lookup table during transformation
Filter or remediate rows with missing mandatory keys before loading the curated layer
Apply the row filter as early as practical in the query or transformation
Append or union the row sets instead of joining them

Correct answer: B

Why: Adding a curated table can enrich the model with reusable business context for downstream joins and analysis. This directly matches the stated requirement.

Option review:

B: Adding a curated table can enrich the model with reusable business context for downstream joins and analysis. This directly matches the stated requirement.

D: Early filtering reduces the volume of data moved and processed while preserving only the rows required by the workload. This can be valid for ‘Filter data’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

Learning point: Create the additional dimension or lookup table during transformation

Question 28

An internal audit of Fourth Coffee’s risk analytics environment identifies this requirement: derive a business classification from existing source fields during preparation. The operations data team also notes that the team wants predictable performance and behavior. What should they do? No unrelated workspace or model permissions should be changed.

Use a left outer join from customers to orders
Cast the column to the appropriate numeric type before aggregation
Use a Fabric lakehouse
Add a calculated or derived column in the transformation layer
Apply an explicit predicate that excludes the test rows

Correct answer: D

Why: A derived column enriches the dataset with business logic while preserving the source fields. This directly matches the stated requirement.

Option review:

A: A left outer join retains all rows from the left input and adds matching rows from the right input when available. This can be valid for ‘Merge or join data’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

D: A derived column enriches the dataset with business logic while preserving the source fields. This directly matches the stated requirement.

Learning point: Add a calculated or derived column in the transformation layer

Question 29

The service-operations warehouse at Wide World Importers is moving from proof of concept to production. Before rollout, the data governance group must create a reusable reference table from source attributes to support downstream analytics, while ensuring that the choice should use a native Fabric capability. Which action best meets both needs? No unrelated workspace or model permissions should be changed.

Use an Eventhouse with a KQL database
Model the warehouse with conformed dimensions around fact tables
Convert the column to an appropriate date or datetime data type
Create the additional dimension or lookup table during transformation
Use a Fabric data pipeline Copy activity

Correct answer: D

Why: Adding a curated table can enrich the model with reusable business context for downstream joins and analysis. This directly matches the stated requirement.

Option review:

C: Using the correct native type enables valid date comparisons, filtering, and time intelligence. This can be valid for ‘Convert column data types’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

D: Adding a curated table can enrich the model with reusable business context for downstream joins and analysis. This directly matches the stated requirement.

E: Copy activity is suited to orchestrated bulk data movement into Fabric with scheduling and monitoring. This can be valid for ‘Ingest or access data as needed’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

Learning point: Create the additional dimension or lookup table during transformation

Question 30

Northwind Traders has a change request for the executive reporting workspace: derive a business classification from existing source fields during preparation. The enterprise reporting group wants a solution where the design should minimize duplicated data. Which implementation is most suitable? The team will validate the change first in a nonproduction environment.

Denormalize selected attributes into the analytical table
Flatten the required lookup attributes into the reporting structure
Enable the supported OneLake availability or integration for the Eventhouse data
Use a Fabric data pipeline Copy activity
Add a calculated or derived column in the transformation layer

Correct answer: E

Why: A derived column enriches the dataset with business logic while preserving the source fields. This directly matches the stated requirement.

Option review:

A: Denormalization trades additional storage and duplication for simpler reads and fewer joins in analytical workloads. This can be valid for ‘Denormalize data’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

B: Flattening commonly consumed attributes can make read-oriented analytics simpler when update anomalies are controlled upstream. This can be valid for ‘Denormalize data’, but it does not directly satisfy the scenario requirement being tested under ‘Enrich data by adding new columns or tables’.

E: A derived column enriches the dataset with business logic while preserving the source fields. This directly matches the stated requirement.

Learning point: Add a calculated or derived column in the transformation layer

Question 31

A solution architect reviewing Trey Research’s marketing semantic model asks the customer insights team to design sales analytics so numeric events are analyzed by conformed date, product, and customer attributes. Since least privilege must be preserved, which recommendation is strongest? Existing users should keep their current access.

Use the OneLake catalog to discover and evaluate available data products
Create a fact table at a defined grain with separate dimension tables
Add a calculated or derived column in the transformation layer
Identify the duplicate keys and deduplicate according to the authoritative business rule
Create a OneLake shortcut to the existing data

Correct answer: B

Why: A star schema places measurable events in a fact table and descriptive context in dimensions, simplifying analytics and improving model usability. This directly matches the stated requirement.

Option review:

A: The OneLake catalog is designed to discover governed Fabric data items across the organization. This can be valid for ‘Discover data by using OneLake catalog and Real-Time hub’, but it does not directly satisfy the scenario requirement being tested under ‘Implement a star schema for a lakehouse or warehouse’.

B: A star schema places measurable events in a fact table and descriptive context in dimensions, simplifying analytics and improving model usability. This directly matches the stated requirement.

C: A derived column enriches the dataset with business logic while preserving the source fields. This can be valid for ‘Enrich data by adding new columns or tables’, but it does not directly satisfy the scenario requirement being tested under ‘Implement a star schema for a lakehouse or warehouse’.

E: A shortcut provides logical access to supported external or OneLake data without copying the underlying files. This can be valid for ‘Ingest or access data as needed’, but it does not directly satisfy the scenario requirement being tested under ‘Implement a star schema for a lakehouse or warehouse’.

Learning point: Create a fact table at a defined grain with separate dimension tables

Question 32

The next sprint for Alpine Ski House’s supply-chain lakehouse includes a task to preserve one consistent grain for transactions while reusing shared descriptive dimensions across subject areas. The acceptance criteria add that the rollout must support controlled validation. Which Fabric or Power BI action is appropriate? Existing users should keep their current access.

Use a Power BI semantic model
Model the warehouse with conformed dimensions around fact tables
Use KQL project and where operators
Create a DAX measure using the required aggregation and filter context logic
Use a Fabric warehouse

Correct answer: B

Why: Conformed dimensions and explicit fact grain are core star-schema practices for reliable analytical joins. This directly matches the stated requirement.

Option review:

A: A semantic model is the analytical serving layer for governed relationships, measures, and report consumption. This can be valid for ‘Choose between different data stores’, but it does not directly satisfy the scenario requirement being tested under ‘Implement a star schema for a lakehouse or warehouse’.

B: Conformed dimensions and explicit fact grain are core star-schema practices for reliable analytical joins. This directly matches the stated requirement.

C: KQL provides concise operators for selecting columns and filtering event data. This can be valid for ‘Select, filter, and aggregate data by using KQL’, but it does not directly satisfy the scenario requirement being tested under ‘Implement a star schema for a lakehouse or warehouse’.

D: DAX measures evaluate in semantic-model filter context and are the native way to define reusable analytical calculations. This can be valid for ‘Select, filter, and aggregate data by using DAX’, but it does not directly satisfy the scenario requirement being tested under ‘Implement a star schema for a lakehouse or warehouse’.

E: A warehouse is optimized for structured relational analytics and T-SQL based development in Fabric. This can be valid for ‘Choose between different data stores’, but it does not directly satisfy the scenario requirement being tested under ‘Implement a star schema for a lakehouse or warehouse’.

Learning point: Model the warehouse with conformed dimensions around fact tables

Question 33

Contoso is troubleshooting a design decision in the IoT telemetry solution. The desired end state is to design sales analytics so numeric events are analyzed by conformed date, product, and customer attributes; the team wants predictable performance and behavior. Which change should the BI platform team make? No unrelated workspace or model permissions should be changed.

Use an Eventhouse with a KQL database
Create a fact table at a defined grain with separate dimension tables
Use Direct Lake against the OneLake-backed data
Create a stored procedure
Use KQL operators such as where, summarize, and bin against the KQL database

Correct answer: B

Option review:

B: A star schema places measurable events in a fact table and descriptive context in dimensions, simplifying analytics and improving model usability. This directly matches the stated requirement.

D: A stored procedure packages procedural multi-statement T-SQL operations for repeatable execution. This can be valid for ‘Create views, functions, and stored procedures’, but it does not directly satisfy the scenario requirement being tested under ‘Implement a star schema for a lakehouse or warehouse’.

Learning point: Create a fact table at a defined grain with separate dimension tables

Question 34

For the finance reporting platform, Wingtip Toys has documented a business requirement to preserve one consistent grain for transactions while reusing shared descriptive dimensions across subject areas. The security analytics team must meet it in a way where the choice should use a native Fabric capability. What is the best choice? No unrelated workspace or model permissions should be changed.

Use the Visual Query Editor to build the query graphically
Model the warehouse with conformed dimensions around fact tables
Precompute the required daily aggregate table or result
Configure the appropriate gateway and connection credentials
Use a Fabric warehouse

Correct answer: B

Why: Conformed dimensions and explicit fact grain are core star-schema practices for reliable analytical joins. This directly matches the stated requirement.

Option review:

B: Conformed dimensions and explicit fact grain are core star-schema practices for reliable analytical joins. This directly matches the stated requirement.

Learning point: Model the warehouse with conformed dimensions around fact tables

Question 35

A governance review of Proseware’s retail performance dashboard asks for evidence that the solution can design sales analytics so numeric events are analyzed by conformed date, product, and customer attributes. Because the design should minimize duplicated data, which action should be approved? The team will validate the change first in a nonproduction environment.

Use a Fabric warehouse
Use Real-Time hub to browse and connect to real-time data sources
Filter or remediate rows with missing mandatory keys before loading the curated layer
Create a fact table at a defined grain with separate dimension tables
Use a DAX query with functions such as SUMMARIZECOLUMNS and CALCULATE as appropriate

Correct answer: D

Option review:

B: Real-Time hub is the Fabric experience for discovering, connecting to, and managing real-time event streams and sources. This can be valid for ‘Discover data by using OneLake catalog and Real-Time hub’, but it does not directly satisfy the scenario requirement being tested under ‘Implement a star schema for a lakehouse or warehouse’.

D: A star schema places measurable events in a fact table and descriptive context in dimensions, simplifying analytics and improving model usability. This directly matches the stated requirement.

E: DAX queries and functions operate on semantic-model metadata, relationships, and filter context. This can be valid for ‘Select, filter, and aggregate data by using DAX’, but it does not directly satisfy the scenario requirement being tested under ‘Implement a star schema for a lakehouse or warehouse’.

Learning point: Create a fact table at a defined grain with separate dimension tables

Question 36

Before expanding the marketing semantic model, the customer insights team at Blue Yonder Airlines must preserve one consistent grain for transactions while reusing shared descriptive dimensions across subject areas. The rollout plan says that least privilege must be preserved. Which option most directly addresses the requirement? The team will validate the change first in a nonproduction environment.

Use the OneLake catalog to discover and evaluate available data products
Append or union the row sets instead of joining them
Apply the row filter as early as practical in the query or transformation
Model the warehouse with conformed dimensions around fact tables
Add a calculated or derived column in the transformation layer

Correct answer: D

Why: Conformed dimensions and explicit fact grain are core star-schema practices for reliable analytical joins. This directly matches the stated requirement.

Option review:

B: Union or append combines rows from compatible schemas, whereas a join combines columns based on matching keys. This can be valid for ‘Merge or join data’, but it does not directly satisfy the scenario requirement being tested under ‘Implement a star schema for a lakehouse or warehouse’.

C: Early filtering reduces the volume of data moved and processed while preserving only the rows required by the workload. This can be valid for ‘Filter data’, but it does not directly satisfy the scenario requirement being tested under ‘Implement a star schema for a lakehouse or warehouse’.

D: Conformed dimensions and explicit fact grain are core star-schema practices for reliable analytical joins. This directly matches the stated requirement.

Learning point: Model the warehouse with conformed dimensions around fact tables

Question 37

Fabrikam is redesigning its supply-chain lakehouse. The retail insights team must design sales analytics so numeric events are analyzed by conformed date, product, and customer attributes. In addition, the rollout must support controlled validation. Which action is the best fit? The solution must work with the current Fabric architecture rather than a parallel custom platform.

Use a DAX query with functions such as SUMMARIZECOLUMNS and CALCULATE as appropriate
Create a fact table at a defined grain with separate dimension tables
Create and manage a shared Fabric connection for the data source
Create a stored procedure
Create a SQL function

Correct answer: B

Option review:

B: A star schema places measurable events in a fact table and descriptive context in dimensions, simplifying analytics and improving model usability. This directly matches the stated requirement.

C: Fabric connections centralize connection details and credentials so supported items can reuse governed access. This can be valid for ‘Create a data connection’, but it does not directly satisfy the scenario requirement being tested under ‘Implement a star schema for a lakehouse or warehouse’.

E: A function is appropriate for reusable parameterized logic that returns a value or table expression. This can be valid for ‘Create views, functions, and stored procedures’, but it does not directly satisfy the scenario requirement being tested under ‘Implement a star schema for a lakehouse or warehouse’.

Learning point: Create a fact table at a defined grain with separate dimension tables

Question 38

During a design review for Litware’s IoT telemetry solution, one requirement is non-negotiable: preserve one consistent grain for transactions while reusing shared descriptive dimensions across subject areas. Because the team wants predictable performance and behavior, what should the BI platform team implement? The solution must work with the current Fabric architecture rather than a parallel custom platform.

Model the warehouse with conformed dimensions around fact tables
Write the required SQL query against the endpoint
Flatten the required lookup attributes into the reporting structure
Denormalize selected attributes into the analytical table
Use a Fabric data pipeline Copy activity

Correct answer: A

Why: Conformed dimensions and explicit fact grain are core star-schema practices for reliable analytical joins. This directly matches the stated requirement.

Option review:

A: Conformed dimensions and explicit fact grain are core star-schema practices for reliable analytical joins. This directly matches the stated requirement.

C: Flattening commonly consumed attributes can make read-oriented analytics simpler when update anomalies are controlled upstream. This can be valid for ‘Denormalize data’, but it does not directly satisfy the scenario requirement being tested under ‘Implement a star schema for a lakehouse or warehouse’.

Learning point: Model the warehouse with conformed dimensions around fact tables

Question 39

The security analytics team at Woodgrove Bank is preparing the next release of its finance reporting platform. They need to design sales analytics so numeric events are analyzed by conformed date, product, and customer attributes; the choice should use a native Fabric capability. Which choice most directly satisfies the requirement? The design decision will be reviewed by both data engineering and BI owners.

Append or union the row sets instead of joining them
Write the required SQL query against the endpoint
Apply an explicit predicate that excludes the test rows
Use Direct Lake against the OneLake-backed data
Create a fact table at a defined grain with separate dimension tables

Correct answer: E

Option review:

C: A deterministic filter predicate is the direct way to prevent unwanted rows from reaching downstream analysis. This can be valid for ‘Filter data’, but it does not directly satisfy the scenario requirement being tested under ‘Implement a star schema for a lakehouse or warehouse’.

D: Direct Lake lets semantic models query OneLake Delta data with in-memory analytical performance without a traditional import copy. This can be valid for ‘Implement OneLake integration for Eventhouse and semantic models’, but it does not directly satisfy the scenario requirement being tested under ‘Implement a star schema for a lakehouse or warehouse’.

E: A star schema places measurable events in a fact table and descriptive context in dimensions, simplifying analytics and improving model usability. This directly matches the stated requirement.

Learning point: Create a fact table at a defined grain with separate dimension tables

Question 40

A production readiness review at Coho Winery found a gap in the retail performance dashboard. The remediation must preserve one consistent grain for transactions while reusing shared descriptive dimensions across subject areas, and the design should minimize duplicated data. What is the most appropriate action? The design decision will be reviewed by both data engineering and BI owners.

Apply the row filter as early as practical in the query or transformation
Convert the column to an appropriate date or datetime data type
Add a calculated or derived column in the transformation layer
Model the warehouse with conformed dimensions around fact tables
Create a OneLake shortcut to the existing data

Correct answer: D

Why: Conformed dimensions and explicit fact grain are core star-schema practices for reliable analytical joins. This directly matches the stated requirement.

Option review:

A: Early filtering reduces the volume of data moved and processed while preserving only the rows required by the workload. This can be valid for ‘Filter data’, but it does not directly satisfy the scenario requirement being tested under ‘Implement a star schema for a lakehouse or warehouse’.

B: Using the correct native type enables valid date comparisons, filtering, and time intelligence. This can be valid for ‘Convert column data types’, but it does not directly satisfy the scenario requirement being tested under ‘Implement a star schema for a lakehouse or warehouse’.

D: Conformed dimensions and explicit fact grain are core star-schema practices for reliable analytical joins. This directly matches the stated requirement.

Learning point: Model the warehouse with conformed dimensions around fact tables

Question 41

For a new phase of the marketing semantic model, Adventure Works asks the customer insights team to design sales analytics so numeric events are analyzed by conformed date, product, and customer attributes. The architecture decision record also states that least privilege must be preserved. Which approach should be selected? Existing users should keep their current access.

Build it in the Visual Query Editor
Use a left outer join from customers to orders
Create a fact table at a defined grain with separate dimension tables
Create a OneLake shortcut to the existing data
Handle nulls explicitly with a null-replacement rule such as COALESCE

Correct answer: C

Option review:

B: A left outer join retains all rows from the left input and adds matching rows from the right input when available. This can be valid for ‘Merge or join data’, but it does not directly satisfy the scenario requirement being tested under ‘Implement a star schema for a lakehouse or warehouse’.

C: A star schema places measurable events in a fact table and descriptive context in dimensions, simplifying analytics and improving model usability. This directly matches the stated requirement.

D: A shortcut provides logical access to supported external or OneLake data without copying the underlying files. This can be valid for ‘Ingest or access data as needed’, but it does not directly satisfy the scenario requirement being tested under ‘Implement a star schema for a lakehouse or warehouse’.

E: Explicit null handling avoids treating missing values as valid zeroes unless the business rule specifically requires that replacement. This can be valid for ‘Identify and resolve duplicate data, missing data, or null values’, but it does not directly satisfy the scenario requirement being tested under ‘Implement a star schema for a lakehouse or warehouse’.

Learning point: Create a fact table at a defined grain with separate dimension tables

Question 42

Tailspin Toys is standardizing how the supply-chain lakehouse is managed. The immediate goal is to preserve one consistent grain for transactions while reusing shared descriptive dimensions across subject areas. Given that the rollout must support controlled validation, which option should the retail insights team choose? Existing users should keep their current access.

Create a stored procedure
Apply an explicit predicate that excludes the test rows
Model the warehouse with conformed dimensions around fact tables
Enable the supported OneLake availability or integration for the Eventhouse data
Append or union the row sets instead of joining them

Correct answer: C

Why: Conformed dimensions and explicit fact grain are core star-schema practices for reliable analytical joins. This directly matches the stated requirement.

Option review:

A: A stored procedure packages procedural multi-statement T-SQL operations for repeatable execution. This can be valid for ‘Create views, functions, and stored procedures’, but it does not directly satisfy the scenario requirement being tested under ‘Implement a star schema for a lakehouse or warehouse’.

B: A deterministic filter predicate is the direct way to prevent unwanted rows from reaching downstream analysis. This can be valid for ‘Filter data’, but it does not directly satisfy the scenario requirement being tested under ‘Implement a star schema for a lakehouse or warehouse’.

C: Conformed dimensions and explicit fact grain are core star-schema practices for reliable analytical joins. This directly matches the stated requirement.

D: OneLake integration makes supported Eventhouse data discoverable and accessible through OneLake for cross-engine use. This can be valid for ‘Implement OneLake integration for Eventhouse and semantic models’, but it does not directly satisfy the scenario requirement being tested under ‘Implement a star schema for a lakehouse or warehouse’.

Learning point: Model the warehouse with conformed dimensions around fact tables

Question 43

An internal audit of Fourth Coffee’s IoT telemetry solution identifies this requirement: design sales analytics so numeric events are analyzed by conformed date, product, and customer attributes. The BI platform team also notes that the team wants predictable performance and behavior. What should they do? No unrelated workspace or model permissions should be changed.

Group the data by month and region and calculate the required summary measures
Use an Eventhouse with a KQL database
Handle nulls explicitly with a null-replacement rule such as COALESCE
Apply the row filter as early as practical in the query or transformation
Create a fact table at a defined grain with separate dimension tables

Correct answer: E

Option review:

A: Aggregation changes detailed rows into grouped summaries at the required analytical grain. This can be valid for ‘Aggregate data’, but it does not directly satisfy the scenario requirement being tested under ‘Implement a star schema for a lakehouse or warehouse’.

B: Eventhouse and KQL databases are optimized for high-volume real-time, log, and time-series analytics. This can be valid for ‘Choose between different data stores’, but it does not directly satisfy the scenario requirement being tested under ‘Implement a star schema for a lakehouse or warehouse’.

E: A star schema places measurable events in a fact table and descriptive context in dimensions, simplifying analytics and improving model usability. This directly matches the stated requirement.

Learning point: Create a fact table at a defined grain with separate dimension tables

Question 44

The finance reporting platform at Wide World Importers is moving from proof of concept to production. Before rollout, the security analytics team must preserve one consistent grain for transactions while reusing shared descriptive dimensions across subject areas, while ensuring that the choice should use a native Fabric capability. Which action best meets both needs? No unrelated workspace or model permissions should be changed.

Use Real-Time hub to browse and connect to real-time data sources
Use the OneLake catalog to discover and evaluate available data products
Create a SQL function
Flatten the required lookup attributes into the reporting structure
Model the warehouse with conformed dimensions around fact tables

Correct answer: E

Why: Conformed dimensions and explicit fact grain are core star-schema practices for reliable analytical joins. This directly matches the stated requirement.

Option review:

C: A function is appropriate for reusable parameterized logic that returns a value or table expression. This can be valid for ‘Create views, functions, and stored procedures’, but it does not directly satisfy the scenario requirement being tested under ‘Implement a star schema for a lakehouse or warehouse’.

D: Flattening commonly consumed attributes can make read-oriented analytics simpler when update anomalies are controlled upstream. This can be valid for ‘Denormalize data’, but it does not directly satisfy the scenario requirement being tested under ‘Implement a star schema for a lakehouse or warehouse’.

E: Conformed dimensions and explicit fact grain are core star-schema practices for reliable analytical joins. This directly matches the stated requirement.

Learning point: Model the warehouse with conformed dimensions around fact tables

Question 45

Northwind Traders has a change request for the retail performance dashboard: design sales analytics so numeric events are analyzed by conformed date, product, and customer attributes. The Fabric center of excellence wants a solution where the design should minimize duplicated data. Which implementation is most suitable? The team will validate the change first in a nonproduction environment.

Convert the column to an appropriate date or datetime data type
Create a fact table at a defined grain with separate dimension tables
Use the OneLake catalog to discover and evaluate available data products
Use Dataflow Gen2 for the ingestion and transformation flow
Use KQL operators such as where, summarize, and bin against the KQL database

Correct answer: B

Option review:

B: A star schema places measurable events in a fact table and descriptive context in dimensions, simplifying analytics and improving model usability. This directly matches the stated requirement.

Learning point: Create a fact table at a defined grain with separate dimension tables

Question 46

A solution architect reviewing Trey Research’s customer 360 model asks the finance analytics squad to reduce repeated joins for a read-heavy analytical workload where some duplicated descriptive data is acceptable. Since least privilege must be preserved, which recommendation is strongest? Existing users should keep their current access.

Denormalize selected attributes into the analytical table
Use the OneLake catalog to discover and evaluate available data products
Precompute the required daily aggregate table or result
Create a DAX measure using the required aggregation and filter context logic
Create a stored procedure

Correct answer: A

Why: Denormalization trades additional storage and duplication for simpler reads and fewer joins in analytical workloads. This directly matches the stated requirement.

Option review:

A: Denormalization trades additional storage and duplication for simpler reads and fewer joins in analytical workloads. This directly matches the stated requirement.

Learning point: Denormalize selected attributes into the analytical table

Question 47

The next sprint for Alpine Ski House’s sales analytics solution includes a task to simplify a reporting table by combining normalized lookup attributes that are always queried together. The acceptance criteria add that the rollout must support controlled validation. Which Fabric or Power BI action is appropriate? Existing users should keep their current access.

Use a Fabric data pipeline Copy activity
Use a DAX query with functions such as SUMMARIZECOLUMNS and CALCULATE as appropriate
Flatten the required lookup attributes into the reporting structure
Handle nulls explicitly with a null-replacement rule such as COALESCE
Use KQL operators such as where, summarize, and bin against the KQL database

Correct answer: C

Why: Flattening commonly consumed attributes can make read-oriented analytics simpler when update anomalies are controlled upstream. This directly matches the stated requirement.

Option review:

A: Copy activity is suited to orchestrated bulk data movement into Fabric with scheduling and monitoring. This can be valid for ‘Ingest or access data as needed’, but it does not directly satisfy the scenario requirement being tested under ‘Denormalize data’.

C: Flattening commonly consumed attributes can make read-oriented analytics simpler when update anomalies are controlled upstream. This directly matches the stated requirement.

D: Explicit null handling avoids treating missing values as valid zeroes unless the business rule specifically requires that replacement. This can be valid for ‘Identify and resolve duplicate data, missing data, or null values’, but it does not directly satisfy the scenario requirement being tested under ‘Denormalize data’.

Learning point: Flatten the required lookup attributes into the reporting structure

Question 48

Contoso is troubleshooting a design decision in the risk analytics environment. The desired end state is to reduce repeated joins for a read-heavy analytical workload where some duplicated descriptive data is acceptable; the team wants predictable performance and behavior. Which change should the operations data team make? No unrelated workspace or model permissions should be changed.

Denormalize selected attributes into the analytical table
Enable the supported OneLake availability or integration for the Eventhouse data
Append or union the row sets instead of joining them
Identify the duplicate keys and deduplicate according to the authoritative business rule
Use the OneLake catalog to discover and evaluate available data products

Correct answer: A

Why: Denormalization trades additional storage and duplication for simpler reads and fewer joins in analytical workloads. This directly matches the stated requirement.

Option review:

A: Denormalization trades additional storage and duplication for simpler reads and fewer joins in analytical workloads. This directly matches the stated requirement.

B: OneLake integration makes supported Eventhouse data discoverable and accessible through OneLake for cross-engine use. This can be valid for ‘Implement OneLake integration for Eventhouse and semantic models’, but it does not directly satisfy the scenario requirement being tested under ‘Denormalize data’.

C: Union or append combines rows from compatible schemas, whereas a join combines columns based on matching keys. This can be valid for ‘Merge or join data’, but it does not directly satisfy the scenario requirement being tested under ‘Denormalize data’.

E: The OneLake catalog is designed to discover governed Fabric data items across the organization. This can be valid for ‘Discover data by using OneLake catalog and Real-Time hub’, but it does not directly satisfy the scenario requirement being tested under ‘Denormalize data’.

Learning point: Denormalize selected attributes into the analytical table

Question 49

For the service-operations warehouse, Wingtip Toys has documented a business requirement to simplify a reporting table by combining normalized lookup attributes that are always queried together. The data governance group must meet it in a way where the choice should use a native Fabric capability. What is the best choice? No unrelated workspace or model permissions should be changed.

Flatten the required lookup attributes into the reporting structure
Create a stored procedure
Use the OneLake catalog to discover and evaluate available data products
Use Real-Time hub to browse and connect to real-time data sources
Use KQL project and where operators

Correct answer: A

Why: Flattening commonly consumed attributes can make read-oriented analytics simpler when update anomalies are controlled upstream. This directly matches the stated requirement.

Option review:

A: Flattening commonly consumed attributes can make read-oriented analytics simpler when update anomalies are controlled upstream. This directly matches the stated requirement.

D: Real-Time hub is the Fabric experience for discovering, connecting to, and managing real-time event streams and sources. This can be valid for ‘Discover data by using OneLake catalog and Real-Time hub’, but it does not directly satisfy the scenario requirement being tested under ‘Denormalize data’.

E: KQL provides concise operators for selecting columns and filtering event data. This can be valid for ‘Select, filter, and aggregate data by using KQL’, but it does not directly satisfy the scenario requirement being tested under ‘Denormalize data’.

Learning point: Flatten the required lookup attributes into the reporting structure

Question 50

A governance review of Proseware’s executive reporting workspace asks for evidence that the solution can reduce repeated joins for a read-heavy analytical workload where some duplicated descriptive data is acceptable. Because the design should minimize duplicated data, which action should be approved? The team will validate the change first in a nonproduction environment.

Use Real-Time hub to browse and connect to real-time data sources
Use KQL operators such as where, summarize, and bin against the KQL database
Write the required SQL query against the endpoint
Denormalize selected attributes into the analytical table
Create a DAX measure using the required aggregation and filter context logic

Correct answer: D

Why: Denormalization trades additional storage and duplication for simpler reads and fewer joins in analytical workloads. This directly matches the stated requirement.

Option review:

B: KQL is optimized for filtering and aggregating log, telemetry, and time-series data in Eventhouse. This can be valid for ‘Select, filter, and aggregate data by using KQL’, but it does not directly satisfy the scenario requirement being tested under ‘Denormalize data’.

D: Denormalization trades additional storage and duplication for simpler reads and fewer joins in analytical workloads. This directly matches the stated requirement.

Learning point: Denormalize selected attributes into the analytical table

Question 51

Before expanding the customer 360 model, the finance analytics squad at Blue Yonder Airlines must simplify a reporting table by combining normalized lookup attributes that are always queried together. The rollout plan says that least privilege must be preserved. Which option most directly addresses the requirement? The team will validate the change first in a nonproduction environment.

Use a Fabric data pipeline Copy activity
Use a Power BI semantic model
Use T-SQL with SELECT, JOIN, WHERE, GROUP BY, and aggregate functions as required
Apply an explicit predicate that excludes the test rows
Flatten the required lookup attributes into the reporting structure

Correct answer: E

Why: Flattening commonly consumed attributes can make read-oriented analytics simpler when update anomalies are controlled upstream. This directly matches the stated requirement.

Option review:

B: A semantic model is the analytical serving layer for governed relationships, measures, and report consumption. This can be valid for ‘Choose between different data stores’, but it does not directly satisfy the scenario requirement being tested under ‘Denormalize data’.

E: Flattening commonly consumed attributes can make read-oriented analytics simpler when update anomalies are controlled upstream. This directly matches the stated requirement.

Learning point: Flatten the required lookup attributes into the reporting structure

Question 52

Fabrikam is redesigning its sales analytics solution. The analytics engineering team must reduce repeated joins for a read-heavy analytical workload where some duplicated descriptive data is acceptable. In addition, the rollout must support controlled validation. Which action is the best fit? The solution must work with the current Fabric architecture rather than a parallel custom platform.

Denormalize selected attributes into the analytical table
Group the data by month and region and calculate the required summary measures
Use T-SQL with SELECT, JOIN, WHERE, GROUP BY, and aggregate functions as required
Use a Power BI semantic model
Add a calculated or derived column in the transformation layer

Correct answer: A

Why: Denormalization trades additional storage and duplication for simpler reads and fewer joins in analytical workloads. This directly matches the stated requirement.

Option review:

A: Denormalization trades additional storage and duplication for simpler reads and fewer joins in analytical workloads. This directly matches the stated requirement.

D: A semantic model is the analytical serving layer for governed relationships, measures, and report consumption. This can be valid for ‘Choose between different data stores’, but it does not directly satisfy the scenario requirement being tested under ‘Denormalize data’.

Learning point: Denormalize selected attributes into the analytical table

Question 53

During a design review for Litware’s risk analytics environment, one requirement is non-negotiable: simplify a reporting table by combining normalized lookup attributes that are always queried together. Because the team wants predictable performance and behavior, what should the operations data team implement? The solution must work with the current Fabric architecture rather than a parallel custom platform.

Flatten the required lookup attributes into the reporting structure
Create the additional dimension or lookup table during transformation
Use an Eventhouse with a KQL database
Create and manage a shared Fabric connection for the data source
Use the OneLake catalog to discover and evaluate available data products

Correct answer: A

Why: Flattening commonly consumed attributes can make read-oriented analytics simpler when update anomalies are controlled upstream. This directly matches the stated requirement.

Option review:

A: Flattening commonly consumed attributes can make read-oriented analytics simpler when update anomalies are controlled upstream. This directly matches the stated requirement.

B: Adding a curated table can enrich the model with reusable business context for downstream joins and analysis. This can be valid for ‘Enrich data by adding new columns or tables’, but it does not directly satisfy the scenario requirement being tested under ‘Denormalize data’.

C: Eventhouse and KQL databases are optimized for high-volume real-time, log, and time-series analytics. This can be valid for ‘Choose between different data stores’, but it does not directly satisfy the scenario requirement being tested under ‘Denormalize data’.

Learning point: Flatten the required lookup attributes into the reporting structure

Question 54

The data governance group at Woodgrove Bank is preparing the next release of its service-operations warehouse. They need to reduce repeated joins for a read-heavy analytical workload where some duplicated descriptive data is acceptable; the choice should use a native Fabric capability. Which choice most directly satisfies the requirement? The design decision will be reviewed by both data engineering and BI owners.

Denormalize selected attributes into the analytical table
Use KQL project and where operators
Convert the column to an appropriate date or datetime data type
Use an Eventhouse with a KQL database
Handle nulls explicitly with a null-replacement rule such as COALESCE

Correct answer: A

Why: Denormalization trades additional storage and duplication for simpler reads and fewer joins in analytical workloads. This directly matches the stated requirement.

Option review:

A: Denormalization trades additional storage and duplication for simpler reads and fewer joins in analytical workloads. This directly matches the stated requirement.

B: KQL provides concise operators for selecting columns and filtering event data. This can be valid for ‘Select, filter, and aggregate data by using KQL’, but it does not directly satisfy the scenario requirement being tested under ‘Denormalize data’.

D: Eventhouse and KQL databases are optimized for high-volume real-time, log, and time-series analytics. This can be valid for ‘Choose between different data stores’, but it does not directly satisfy the scenario requirement being tested under ‘Denormalize data’.

Learning point: Denormalize selected attributes into the analytical table

Question 55

A production readiness review at Coho Winery found a gap in the executive reporting workspace. The remediation must simplify a reporting table by combining normalized lookup attributes that are always queried together, and the design should minimize duplicated data. What is the most appropriate action? The design decision will be reviewed by both data engineering and BI owners.

Model the warehouse with conformed dimensions around fact tables
Use a DAX query with functions such as SUMMARIZECOLUMNS and CALCULATE as appropriate
Apply an explicit predicate that excludes the test rows
Use KQL operators such as where, summarize, and bin against the KQL database
Flatten the required lookup attributes into the reporting structure

Correct answer: E

Why: Flattening commonly consumed attributes can make read-oriented analytics simpler when update anomalies are controlled upstream. This directly matches the stated requirement.

Option review:

D: KQL is optimized for filtering and aggregating log, telemetry, and time-series data in Eventhouse. This can be valid for ‘Select, filter, and aggregate data by using KQL’, but it does not directly satisfy the scenario requirement being tested under ‘Denormalize data’.

E: Flattening commonly consumed attributes can make read-oriented analytics simpler when update anomalies are controlled upstream. This directly matches the stated requirement.

Learning point: Flatten the required lookup attributes into the reporting structure

Question 56

For a new phase of the customer 360 model, Adventure Works asks the finance analytics squad to reduce repeated joins for a read-heavy analytical workload where some duplicated descriptive data is acceptable. The architecture decision record also states that least privilege must be preserved. Which approach should be selected? Existing users should keep their current access.

Use Dataflow Gen2 for the ingestion and transformation flow
Use the Visual Query Editor to build the query graphically
Create a SQL function
Denormalize selected attributes into the analytical table
Create and manage a shared Fabric connection for the data source

Correct answer: D

Why: Denormalization trades additional storage and duplication for simpler reads and fewer joins in analytical workloads. This directly matches the stated requirement.

Option review:

A: Dataflow Gen2 provides a Power Query based low-code ingestion and transformation experience. This can be valid for ‘Ingest or access data as needed’, but it does not directly satisfy the scenario requirement being tested under ‘Denormalize data’.

D: Denormalization trades additional storage and duplication for simpler reads and fewer joins in analytical workloads. This directly matches the stated requirement.

E: Fabric connections centralize connection details and credentials so supported items can reuse governed access. This can be valid for ‘Create a data connection’, but it does not directly satisfy the scenario requirement being tested under ‘Denormalize data’.

Learning point: Denormalize selected attributes into the analytical table

Question 57

Tailspin Toys is standardizing how the sales analytics solution is managed. The immediate goal is to simplify a reporting table by combining normalized lookup attributes that are always queried together. Given that the rollout must support controlled validation, which option should the analytics engineering team choose? Existing users should keep their current access.

Use T-SQL with SELECT, JOIN, WHERE, GROUP BY, and aggregate functions as required
Create a SQL function
Apply an explicit predicate that excludes the test rows
Flatten the required lookup attributes into the reporting structure
Convert the column to an appropriate date or datetime data type

Correct answer: D

Why: Flattening commonly consumed attributes can make read-oriented analytics simpler when update anomalies are controlled upstream. This directly matches the stated requirement.

Option review:

A: SQL is the native relational query language for warehouse and SQL analytics endpoint workloads. This can be valid for ‘Select, filter, and aggregate data by using SQL’, but it does not directly satisfy the scenario requirement being tested under ‘Denormalize data’.

B: A function is appropriate for reusable parameterized logic that returns a value or table expression. This can be valid for ‘Create views, functions, and stored procedures’, but it does not directly satisfy the scenario requirement being tested under ‘Denormalize data’.

D: Flattening commonly consumed attributes can make read-oriented analytics simpler when update anomalies are controlled upstream. This directly matches the stated requirement.

E: Using the correct native type enables valid date comparisons, filtering, and time intelligence. This can be valid for ‘Convert column data types’, but it does not directly satisfy the scenario requirement being tested under ‘Denormalize data’.

Learning point: Flatten the required lookup attributes into the reporting structure

Question 58

An internal audit of Fourth Coffee’s risk analytics environment identifies this requirement: reduce repeated joins for a read-heavy analytical workload where some duplicated descriptive data is acceptable. The operations data team also notes that the team wants predictable performance and behavior. What should they do? No unrelated workspace or model permissions should be changed.

Denormalize selected attributes into the analytical table
Use Dataflow Gen2 for the ingestion and transformation flow
Create and manage a shared Fabric connection for the data source
Use KQL operators such as where, summarize, and bin against the KQL database
Group the data by month and region and calculate the required summary measures

Correct answer: A

Why: Denormalization trades additional storage and duplication for simpler reads and fewer joins in analytical workloads. This directly matches the stated requirement.

Option review:

A: Denormalization trades additional storage and duplication for simpler reads and fewer joins in analytical workloads. This directly matches the stated requirement.

E: Aggregation changes detailed rows into grouped summaries at the required analytical grain. This can be valid for ‘Aggregate data’, but it does not directly satisfy the scenario requirement being tested under ‘Denormalize data’.

Learning point: Denormalize selected attributes into the analytical table

Question 59

The service-operations warehouse at Wide World Importers is moving from proof of concept to production. Before rollout, the data governance group must simplify a reporting table by combining normalized lookup attributes that are always queried together, while ensuring that the choice should use a native Fabric capability. Which action best meets both needs? No unrelated workspace or model permissions should be changed.

Use a left outer join from customers to orders
Convert the column to an appropriate date or datetime data type
Use KQL operators such as where, summarize, and bin against the KQL database
Flatten the required lookup attributes into the reporting structure
Create a DAX measure using the required aggregation and filter context logic

Correct answer: D

Why: Flattening commonly consumed attributes can make read-oriented analytics simpler when update anomalies are controlled upstream. This directly matches the stated requirement.

Option review:

D: Flattening commonly consumed attributes can make read-oriented analytics simpler when update anomalies are controlled upstream. This directly matches the stated requirement.

Learning point: Flatten the required lookup attributes into the reporting structure

Question 60

Northwind Traders has a change request for the executive reporting workspace: reduce repeated joins for a read-heavy analytical workload where some duplicated descriptive data is acceptable. The enterprise reporting group wants a solution where the design should minimize duplicated data. Which implementation is most suitable? The team will validate the change first in a nonproduction environment.

Denormalize selected attributes into the analytical table
Use a DAX query with functions such as SUMMARIZECOLUMNS and CALCULATE as appropriate
Convert the column to an appropriate date or datetime data type
Create a DAX measure using the required aggregation and filter context logic
Write the required SQL query against the endpoint

Correct answer: A

Why: Denormalization trades additional storage and duplication for simpler reads and fewer joins in analytical workloads. This directly matches the stated requirement.

Option review:

A: Denormalization trades additional storage and duplication for simpler reads and fewer joins in analytical workloads. This directly matches the stated requirement.

E: The SQL analytics endpoint supports relational querying with T-SQL for selection, filtering, joins, and aggregation. This can be valid for ‘Select, filter, and aggregate data by using SQL’, but it does not directly satisfy the scenario requirement being tested under ‘Denormalize data’.

Learning point: Denormalize selected attributes into the analytical table

Question 61

A solution architect reviewing Trey Research’s marketing semantic model asks the customer insights team to produce one row per month and region from detailed sales transactions. Since least privilege must be preserved, which recommendation is strongest? Existing users should keep their current access.

Use an inner join on the matching business or surrogate key
Create and manage a shared Fabric connection for the data source
Group the data by month and region and calculate the required summary measures
Identify the duplicate keys and deduplicate according to the authoritative business rule
Enable the supported OneLake availability or integration for the Eventhouse data

Correct answer: C

Why: Aggregation changes detailed rows into grouped summaries at the required analytical grain. This directly matches the stated requirement.

Option review:

A: An inner join returns only rows with matching keys on both sides. This can be valid for ‘Merge or join data’, but it does not directly satisfy the scenario requirement being tested under ‘Aggregate data’.

B: Fabric connections centralize connection details and credentials so supported items can reuse governed access. This can be valid for ‘Create a data connection’, but it does not directly satisfy the scenario requirement being tested under ‘Aggregate data’.

C: Aggregation changes detailed rows into grouped summaries at the required analytical grain. This directly matches the stated requirement.

E: OneLake integration makes supported Eventhouse data discoverable and accessible through OneLake for cross-engine use. This can be valid for ‘Implement OneLake integration for Eventhouse and semantic models’, but it does not directly satisfy the scenario requirement being tested under ‘Aggregate data’.

Learning point: Group the data by month and region and calculate the required summary measures

Question 62

The next sprint for Alpine Ski House’s supply-chain lakehouse includes a task to reduce query work for a dashboard that always consumes daily totals rather than individual events. The acceptance criteria add that the rollout must support controlled validation. Which Fabric or Power BI action is appropriate? Existing users should keep their current access.

Use an inner join on the matching business or surrogate key
Create a fact table at a defined grain with separate dimension tables
Handle nulls explicitly with a null-replacement rule such as COALESCE
Precompute the required daily aggregate table or result
Use T-SQL with SELECT, JOIN, WHERE, GROUP BY, and aggregate functions as required

Correct answer: D

Why: Pre-aggregation can reduce repeated scanning of detail rows when consumers consistently query a higher-level grain. This directly matches the stated requirement.

Option review:

B: A star schema places measurable events in a fact table and descriptive context in dimensions, simplifying analytics and improving model usability. This can be valid for ‘Implement a star schema for a lakehouse or warehouse’, but it does not directly satisfy the scenario requirement being tested under ‘Aggregate data’.

D: Pre-aggregation can reduce repeated scanning of detail rows when consumers consistently query a higher-level grain. This directly matches the stated requirement.

Learning point: Precompute the required daily aggregate table or result

Question 63

Contoso is troubleshooting a design decision in the IoT telemetry solution. The desired end state is to produce one row per month and region from detailed sales transactions; the team wants predictable performance and behavior. Which change should the BI platform team make? No unrelated workspace or model permissions should be changed.

Use T-SQL with SELECT, JOIN, WHERE, GROUP BY, and aggregate functions as required
Append or union the row sets instead of joining them
Handle nulls explicitly with a null-replacement rule such as COALESCE
Group the data by month and region and calculate the required summary measures
Use a Fabric data pipeline Copy activity

Correct answer: D

Why: Aggregation changes detailed rows into grouped summaries at the required analytical grain. This directly matches the stated requirement.

Option review:

D: Aggregation changes detailed rows into grouped summaries at the required analytical grain. This directly matches the stated requirement.

Learning point: Group the data by month and region and calculate the required summary measures

Question 64

For the finance reporting platform, Wingtip Toys has documented a business requirement to reduce query work for a dashboard that always consumes daily totals rather than individual events. The security analytics team must meet it in a way where the choice should use a native Fabric capability. What is the best choice? No unrelated workspace or model permissions should be changed.

Use Direct Lake against the OneLake-backed data
Use a left outer join from customers to orders
Precompute the required daily aggregate table or result
Convert the column to an appropriate date or datetime data type
Write the required SQL query against the endpoint

Correct answer: C

Why: Pre-aggregation can reduce repeated scanning of detail rows when consumers consistently query a higher-level grain. This directly matches the stated requirement.

Option review:

C: Pre-aggregation can reduce repeated scanning of detail rows when consumers consistently query a higher-level grain. This directly matches the stated requirement.

D: Using the correct native type enables valid date comparisons, filtering, and time intelligence. This can be valid for ‘Convert column data types’, but it does not directly satisfy the scenario requirement being tested under ‘Aggregate data’.

Learning point: Precompute the required daily aggregate table or result

Question 65

A governance review of Proseware’s retail performance dashboard asks for evidence that the solution can produce one row per month and region from detailed sales transactions. Because the design should minimize duplicated data, which action should be approved? The team will validate the change first in a nonproduction environment.

Add a calculated or derived column in the transformation layer
Use KQL project and where operators
Use a Fabric lakehouse
Use a Fabric warehouse
Group the data by month and region and calculate the required summary measures

Correct answer: E

Why: Aggregation changes detailed rows into grouped summaries at the required analytical grain. This directly matches the stated requirement.

Option review:

A: A derived column enriches the dataset with business logic while preserving the source fields. This can be valid for ‘Enrich data by adding new columns or tables’, but it does not directly satisfy the scenario requirement being tested under ‘Aggregate data’.

D: A warehouse is optimized for structured relational analytics and T-SQL based development in Fabric. This can be valid for ‘Choose between different data stores’, but it does not directly satisfy the scenario requirement being tested under ‘Aggregate data’.

E: Aggregation changes detailed rows into grouped summaries at the required analytical grain. This directly matches the stated requirement.

Learning point: Group the data by month and region and calculate the required summary measures

Question 66

Before expanding the marketing semantic model, the customer insights team at Blue Yonder Airlines must reduce query work for a dashboard that always consumes daily totals rather than individual events. The rollout plan says that least privilege must be preserved. Which option most directly addresses the requirement? The team will validate the change first in a nonproduction environment.

Use an Eventhouse with a KQL database
Cast the column to the appropriate numeric type before aggregation
Write the required SQL query against the endpoint
Precompute the required daily aggregate table or result
Use Direct Lake against the OneLake-backed data

Correct answer: D

Why: Pre-aggregation can reduce repeated scanning of detail rows when consumers consistently query a higher-level grain. This directly matches the stated requirement.

Option review:

D: Pre-aggregation can reduce repeated scanning of detail rows when consumers consistently query a higher-level grain. This directly matches the stated requirement.

E: Direct Lake lets semantic models query OneLake Delta data with in-memory analytical performance without a traditional import copy. This can be valid for ‘Implement OneLake integration for Eventhouse and semantic models’, but it does not directly satisfy the scenario requirement being tested under ‘Aggregate data’.

Learning point: Precompute the required daily aggregate table or result

Question 67

Fabrikam is redesigning its supply-chain lakehouse. The retail insights team must produce one row per month and region from detailed sales transactions. In addition, the rollout must support controlled validation. Which action is the best fit? The solution must work with the current Fabric architecture rather than a parallel custom platform.

Group the data by month and region and calculate the required summary measures
Use a Fabric lakehouse
Flatten the required lookup attributes into the reporting structure
Enable the supported OneLake availability or integration for the Eventhouse data
Create the additional dimension or lookup table during transformation

Correct answer: A

Why: Aggregation changes detailed rows into grouped summaries at the required analytical grain. This directly matches the stated requirement.

Option review:

A: Aggregation changes detailed rows into grouped summaries at the required analytical grain. This directly matches the stated requirement.

B: A lakehouse combines OneLake Delta storage with Spark-oriented engineering and a SQL analytics endpoint. This can be valid for ‘Choose between different data stores’, but it does not directly satisfy the scenario requirement being tested under ‘Aggregate data’.

E: Adding a curated table can enrich the model with reusable business context for downstream joins and analysis. This can be valid for ‘Enrich data by adding new columns or tables’, but it does not directly satisfy the scenario requirement being tested under ‘Aggregate data’.

Learning point: Group the data by month and region and calculate the required summary measures

Question 68

During a design review for Litware’s IoT telemetry solution, one requirement is non-negotiable: reduce query work for a dashboard that always consumes daily totals rather than individual events. Because the team wants predictable performance and behavior, what should the BI platform team implement? The solution must work with the current Fabric architecture rather than a parallel custom platform.

Use a Fabric data pipeline Copy activity
Precompute the required daily aggregate table or result
Configure the appropriate gateway and connection credentials
Use KQL operators such as where, summarize, and bin against the KQL database
Use an inner join on the matching business or surrogate key

Correct answer: B

Why: Pre-aggregation can reduce repeated scanning of detail rows when consumers consistently query a higher-level grain. This directly matches the stated requirement.

Option review:

B: Pre-aggregation can reduce repeated scanning of detail rows when consumers consistently query a higher-level grain. This directly matches the stated requirement.

C: An on-premises gateway provides the network bridge while the connection stores the credentials and source details. This can be valid for ‘Create a data connection’, but it does not directly satisfy the scenario requirement being tested under ‘Aggregate data’.

Learning point: Precompute the required daily aggregate table or result

Question 69

The security analytics team at Woodgrove Bank is preparing the next release of its finance reporting platform. They need to produce one row per month and region from detailed sales transactions; the choice should use a native Fabric capability. Which choice most directly satisfies the requirement? The design decision will be reviewed by both data engineering and BI owners.

Use the Visual Query Editor to build the query graphically
Create a fact table at a defined grain with separate dimension tables
Use Real-Time hub to browse and connect to real-time data sources
Write the required SQL query against the endpoint
Group the data by month and region and calculate the required summary measures

Correct answer: E

Why: Aggregation changes detailed rows into grouped summaries at the required analytical grain. This directly matches the stated requirement.

Option review:

C: Real-Time hub is the Fabric experience for discovering, connecting to, and managing real-time event streams and sources. This can be valid for ‘Discover data by using OneLake catalog and Real-Time hub’, but it does not directly satisfy the scenario requirement being tested under ‘Aggregate data’.

E: Aggregation changes detailed rows into grouped summaries at the required analytical grain. This directly matches the stated requirement.

Learning point: Group the data by month and region and calculate the required summary measures

Question 70

A production readiness review at Coho Winery found a gap in the retail performance dashboard. The remediation must reduce query work for a dashboard that always consumes daily totals rather than individual events, and the design should minimize duplicated data. What is the most appropriate action? The design decision will be reviewed by both data engineering and BI owners.

Convert the column to an appropriate date or datetime data type
Precompute the required daily aggregate table or result
Write the required SQL query against the endpoint
Flatten the required lookup attributes into the reporting structure
Use a Fabric warehouse

Correct answer: B

Why: Pre-aggregation can reduce repeated scanning of detail rows when consumers consistently query a higher-level grain. This directly matches the stated requirement.

Option review:

B: Pre-aggregation can reduce repeated scanning of detail rows when consumers consistently query a higher-level grain. This directly matches the stated requirement.

Learning point: Precompute the required daily aggregate table or result

Question 71

For a new phase of the marketing semantic model, Adventure Works asks the customer insights team to produce one row per month and region from detailed sales transactions. The architecture decision record also states that least privilege must be preserved. Which approach should be selected? Existing users should keep their current access.

Create a fact table at a defined grain with separate dimension tables
Group the data by month and region and calculate the required summary measures
Apply the row filter as early as practical in the query or transformation
Use an inner join on the matching business or surrogate key
Handle nulls explicitly with a null-replacement rule such as COALESCE

Correct answer: B

Why: Aggregation changes detailed rows into grouped summaries at the required analytical grain. This directly matches the stated requirement.

Option review:

A: A star schema places measurable events in a fact table and descriptive context in dimensions, simplifying analytics and improving model usability. This can be valid for ‘Implement a star schema for a lakehouse or warehouse’, but it does not directly satisfy the scenario requirement being tested under ‘Aggregate data’.

B: Aggregation changes detailed rows into grouped summaries at the required analytical grain. This directly matches the stated requirement.

Learning point: Group the data by month and region and calculate the required summary measures

Question 72

Tailspin Toys is standardizing how the supply-chain lakehouse is managed. The immediate goal is to reduce query work for a dashboard that always consumes daily totals rather than individual events. Given that the rollout must support controlled validation, which option should the retail insights team choose? Existing users should keep their current access.

Apply the row filter as early as practical in the query or transformation
Use T-SQL with SELECT, JOIN, WHERE, GROUP BY, and aggregate functions as required
Flatten the required lookup attributes into the reporting structure
Precompute the required daily aggregate table or result
Use a Fabric warehouse

Correct answer: D

Why: Pre-aggregation can reduce repeated scanning of detail rows when consumers consistently query a higher-level grain. This directly matches the stated requirement.

Option review:

B: SQL is the native relational query language for warehouse and SQL analytics endpoint workloads. This can be valid for ‘Select, filter, and aggregate data by using SQL’, but it does not directly satisfy the scenario requirement being tested under ‘Aggregate data’.

D: Pre-aggregation can reduce repeated scanning of detail rows when consumers consistently query a higher-level grain. This directly matches the stated requirement.

Learning point: Precompute the required daily aggregate table or result

Question 73

An internal audit of Fourth Coffee’s IoT telemetry solution identifies this requirement: produce one row per month and region from detailed sales transactions. The BI platform team also notes that the team wants predictable performance and behavior. What should they do? No unrelated workspace or model permissions should be changed.

Use a Fabric warehouse
Identify the duplicate keys and deduplicate according to the authoritative business rule
Use KQL operators such as where, summarize, and bin against the KQL database
Use an inner join on the matching business or surrogate key
Group the data by month and region and calculate the required summary measures

Correct answer: E

Why: Aggregation changes detailed rows into grouped summaries at the required analytical grain. This directly matches the stated requirement.

Option review:

B: Deduplication must preserve the correct authoritative row while eliminating records that would inflate analytical results. This can be valid for ‘Identify and resolve duplicate data, missing data, or null values’, but it does not directly satisfy the scenario requirement being tested under ‘Aggregate data’.

E: Aggregation changes detailed rows into grouped summaries at the required analytical grain. This directly matches the stated requirement.

Learning point: Group the data by month and region and calculate the required summary measures

Question 74

The finance reporting platform at Wide World Importers is moving from proof of concept to production. Before rollout, the security analytics team must reduce query work for a dashboard that always consumes daily totals rather than individual events, while ensuring that the choice should use a native Fabric capability. Which action best meets both needs? No unrelated workspace or model permissions should be changed.

Use a Fabric warehouse
Precompute the required daily aggregate table or result
Write the required SQL query against the endpoint
Use the OneLake catalog to discover and evaluate available data products
Create a DAX measure using the required aggregation and filter context logic

Correct answer: B

Why: Pre-aggregation can reduce repeated scanning of detail rows when consumers consistently query a higher-level grain. This directly matches the stated requirement.

Option review:

B: Pre-aggregation can reduce repeated scanning of detail rows when consumers consistently query a higher-level grain. This directly matches the stated requirement.

Learning point: Precompute the required daily aggregate table or result

Question 75

Northwind Traders has a change request for the retail performance dashboard: produce one row per month and region from detailed sales transactions. The Fabric center of excellence wants a solution where the design should minimize duplicated data. Which implementation is most suitable? The team will validate the change first in a nonproduction environment.

Add a calculated or derived column in the transformation layer
Group the data by month and region and calculate the required summary measures
Create a stored procedure
Configure the appropriate gateway and connection credentials
Use a Fabric data pipeline Copy activity

Correct answer: B

Why: Aggregation changes detailed rows into grouped summaries at the required analytical grain. This directly matches the stated requirement.

Option review:

B: Aggregation changes detailed rows into grouped summaries at the required analytical grain. This directly matches the stated requirement.

C: A stored procedure packages procedural multi-statement T-SQL operations for repeatable execution. This can be valid for ‘Create views, functions, and stored procedures’, but it does not directly satisfy the scenario requirement being tested under ‘Aggregate data’.

Learning point: Group the data by month and region and calculate the required summary measures

Question 76

A solution architect reviewing Trey Research’s customer 360 model asks the finance analytics squad to keep every customer row while adding matching order data when it exists. Since least privilege must be preserved, which recommendation is strongest? Existing users should keep their current access.

Create a OneLake shortcut to the existing data
Use a left outer join from customers to orders
Use Dataflow Gen2 for the ingestion and transformation flow
Use a Power BI semantic model
Use an Eventhouse with a KQL database

Correct answer: B

Why: A left outer join retains all rows from the left input and adds matching rows from the right input when available. This directly matches the stated requirement.

Option review:

A: A shortcut provides logical access to supported external or OneLake data without copying the underlying files. This can be valid for ‘Ingest or access data as needed’, but it does not directly satisfy the scenario requirement being tested under ‘Merge or join data’.

B: A left outer join retains all rows from the left input and adds matching rows from the right input when available. This directly matches the stated requirement.

C: Dataflow Gen2 provides a Power Query based low-code ingestion and transformation experience. This can be valid for ‘Ingest or access data as needed’, but it does not directly satisfy the scenario requirement being tested under ‘Merge or join data’.

E: Eventhouse and KQL databases are optimized for high-volume real-time, log, and time-series analytics. This can be valid for ‘Choose between different data stores’, but it does not directly satisfy the scenario requirement being tested under ‘Merge or join data’.

Learning point: Use a left outer join from customers to orders

Question 77

The next sprint for Alpine Ski House’s sales analytics solution includes a task to return only records that have matching keys in both prepared datasets. The acceptance criteria add that the rollout must support controlled validation. Which Fabric or Power BI action is appropriate? Existing users should keep their current access.

Use Real-Time hub to browse and connect to real-time data sources
Use an inner join on the matching business or surrogate key
Use KQL operators such as where, summarize, and bin against the KQL database
Use a Fabric data pipeline Copy activity
Write the required SQL query against the endpoint

Correct answer: B

Why: An inner join returns only rows with matching keys on both sides. This directly matches the stated requirement.

Option review:

B: An inner join returns only rows with matching keys on both sides. This directly matches the stated requirement.

Learning point: Use an inner join on the matching business or surrogate key

Question 78

Contoso is troubleshooting a design decision in the risk analytics environment. The desired end state is to combine two similarly structured row sets into one result rather than matching columns by key; the team wants predictable performance and behavior. Which change should the operations data team make? Existing users should keep their current access.

Use a Power BI semantic model
Precompute the required daily aggregate table or result
Apply the row filter as early as practical in the query or transformation
Create and manage a shared Fabric connection for the data source
Append or union the row sets instead of joining them

Correct answer: E

Why: Union or append combines rows from compatible schemas, whereas a join combines columns based on matching keys. This directly matches the stated requirement.

Option review:

B: Pre-aggregation can reduce repeated scanning of detail rows when consumers consistently query a higher-level grain. This can be valid for ‘Aggregate data’, but it does not directly satisfy the scenario requirement being tested under ‘Merge or join data’.

E: Union or append combines rows from compatible schemas, whereas a join combines columns based on matching keys. This directly matches the stated requirement.

Learning point: Append or union the row sets instead of joining them

Question 79

For the service-operations warehouse, Wingtip Toys has documented a business requirement to keep every customer row while adding matching order data when it exists. The data governance group must meet it in a way where the choice should use a native Fabric capability. What is the best choice? No unrelated workspace or model permissions should be changed.

Use a left outer join from customers to orders
Use a Fabric data pipeline Copy activity
Apply the row filter as early as practical in the query or transformation
Use the Visual Query Editor to build the query graphically
Create a fact table at a defined grain with separate dimension tables

Correct answer: A

Why: A left outer join retains all rows from the left input and adds matching rows from the right input when available. This directly matches the stated requirement.

Option review:

A: A left outer join retains all rows from the left input and adds matching rows from the right input when available. This directly matches the stated requirement.

Learning point: Use a left outer join from customers to orders

Question 80

A governance review of Proseware’s executive reporting workspace asks for evidence that the solution can return only records that have matching keys in both prepared datasets. Because the design should minimize duplicated data, which action should be approved? No unrelated workspace or model permissions should be changed.

Use an inner join on the matching business or surrogate key
Model the warehouse with conformed dimensions around fact tables
Cast the column to the appropriate numeric type before aggregation
Use the OneLake catalog to discover and evaluate available data products
Write the required SQL query against the endpoint

Correct answer: A

Why: An inner join returns only rows with matching keys on both sides. This directly matches the stated requirement.

Option review:

A: An inner join returns only rows with matching keys on both sides. This directly matches the stated requirement.

C: Numeric calculations require a compatible numeric type and should avoid relying on implicit conversion. This can be valid for ‘Convert column data types’, but it does not directly satisfy the scenario requirement being tested under ‘Merge or join data’.

Learning point: Use an inner join on the matching business or surrogate key

Question 81

Before expanding the customer 360 model, the finance analytics squad at Blue Yonder Airlines must combine two similarly structured row sets into one result rather than matching columns by key. The rollout plan says that least privilege must be preserved. Which option most directly addresses the requirement? No unrelated workspace or model permissions should be changed.

Create a SQL function
Create a OneLake shortcut to the existing data
Use KQL operators such as where, summarize, and bin against the KQL database
Create the additional dimension or lookup table during transformation
Append or union the row sets instead of joining them

Correct answer: E

Why: Union or append combines rows from compatible schemas, whereas a join combines columns based on matching keys. This directly matches the stated requirement.

Option review:

A: A function is appropriate for reusable parameterized logic that returns a value or table expression. This can be valid for ‘Create views, functions, and stored procedures’, but it does not directly satisfy the scenario requirement being tested under ‘Merge or join data’.

D: Adding a curated table can enrich the model with reusable business context for downstream joins and analysis. This can be valid for ‘Enrich data by adding new columns or tables’, but it does not directly satisfy the scenario requirement being tested under ‘Merge or join data’.

E: Union or append combines rows from compatible schemas, whereas a join combines columns based on matching keys. This directly matches the stated requirement.

Learning point: Append or union the row sets instead of joining them

Question 82

Fabrikam is redesigning its sales analytics solution. The analytics engineering team must keep every customer row while adding matching order data when it exists. In addition, the rollout must support controlled validation. Which action is the best fit? The team will validate the change first in a nonproduction environment.

Use a DAX query with functions such as SUMMARIZECOLUMNS and CALCULATE as appropriate
Use a left outer join from customers to orders
Convert the column to an appropriate date or datetime data type
Use Dataflow Gen2 for the ingestion and transformation flow
Use Real-Time hub to browse and connect to real-time data sources

Correct answer: B

Why: A left outer join retains all rows from the left input and adds matching rows from the right input when available. This directly matches the stated requirement.

Option review:

B: A left outer join retains all rows from the left input and adds matching rows from the right input when available. This directly matches the stated requirement.

E: Real-Time hub is the Fabric experience for discovering, connecting to, and managing real-time event streams and sources. This can be valid for ‘Discover data by using OneLake catalog and Real-Time hub’, but it does not directly satisfy the scenario requirement being tested under ‘Merge or join data’.

Learning point: Use a left outer join from customers to orders

Question 83

During a design review for Litware’s risk analytics environment, one requirement is non-negotiable: return only records that have matching keys in both prepared datasets. Because the team wants predictable performance and behavior, what should the operations data team implement? The team will validate the change first in a nonproduction environment.

Use an inner join on the matching business or surrogate key
Use Direct Lake against the OneLake-backed data
Use a Fabric warehouse
Create a OneLake shortcut to the existing data
Use the OneLake catalog to discover and evaluate available data products

Correct answer: A

Why: An inner join returns only rows with matching keys on both sides. This directly matches the stated requirement.

Option review:

A: An inner join returns only rows with matching keys on both sides. This directly matches the stated requirement.

Learning point: Use an inner join on the matching business or surrogate key

Question 84

The data governance group at Woodgrove Bank is preparing the next release of its service-operations warehouse. They need to combine two similarly structured row sets into one result rather than matching columns by key; the choice should use a native Fabric capability. Which choice most directly satisfies the requirement? The team will validate the change first in a nonproduction environment.

Use Real-Time hub to browse and connect to real-time data sources
Use Direct Lake against the OneLake-backed data
Create a OneLake shortcut to the existing data
Create a SQL function
Append or union the row sets instead of joining them

Correct answer: E

Why: Union or append combines rows from compatible schemas, whereas a join combines columns based on matching keys. This directly matches the stated requirement.

Option review:

C: A shortcut provides logical access to supported external or OneLake data without copying the underlying files. This can be valid for ‘Ingest or access data as needed’, but it does not directly satisfy the scenario requirement being tested under ‘Merge or join data’.

E: Union or append combines rows from compatible schemas, whereas a join combines columns based on matching keys. This directly matches the stated requirement.

Learning point: Append or union the row sets instead of joining them

Question 85

A production readiness review at Coho Winery found a gap in the executive reporting workspace. The remediation must keep every customer row while adding matching order data when it exists, and the design should minimize duplicated data. What is the most appropriate action? The solution must work with the current Fabric architecture rather than a parallel custom platform.

Use a left outer join from customers to orders
Use a Fabric lakehouse
Create a OneLake shortcut to the existing data
Enable the supported OneLake availability or integration for the Eventhouse data
Cast the column to the appropriate numeric type before aggregation

Correct answer: A

Why: A left outer join retains all rows from the left input and adds matching rows from the right input when available. This directly matches the stated requirement.

Option review:

A: A left outer join retains all rows from the left input and adds matching rows from the right input when available. This directly matches the stated requirement.

Learning point: Use a left outer join from customers to orders

Question 86

For a new phase of the customer 360 model, Adventure Works asks the finance analytics squad to return only records that have matching keys in both prepared datasets. The architecture decision record also states that least privilege must be preserved. Which approach should be selected? The solution must work with the current Fabric architecture rather than a parallel custom platform.

Filter or remediate rows with missing mandatory keys before loading the curated layer
Write the required SQL query against the endpoint
Create a OneLake shortcut to the existing data
Use an inner join on the matching business or surrogate key
Use KQL project and where operators

Correct answer: D

Why: An inner join returns only rows with matching keys on both sides. This directly matches the stated requirement.

Option review:

A: Mandatory-key quality rules should be enforced before downstream joins and aggregations depend on those records. This can be valid for ‘Identify and resolve duplicate data, missing data, or null values’, but it does not directly satisfy the scenario requirement being tested under ‘Merge or join data’.

D: An inner join returns only rows with matching keys on both sides. This directly matches the stated requirement.

Learning point: Use an inner join on the matching business or surrogate key

Question 87

Tailspin Toys is standardizing how the sales analytics solution is managed. The immediate goal is to combine two similarly structured row sets into one result rather than matching columns by key. Given that the rollout must support controlled validation, which option should the analytics engineering team choose? The solution must work with the current Fabric architecture rather than a parallel custom platform.

Use a DAX query with functions such as SUMMARIZECOLUMNS and CALCULATE as appropriate
Create a DAX measure using the required aggregation and filter context logic
Build it in the Visual Query Editor
Use Real-Time hub to browse and connect to real-time data sources
Append or union the row sets instead of joining them

Correct answer: E

Why: Union or append combines rows from compatible schemas, whereas a join combines columns based on matching keys. This directly matches the stated requirement.

Option review:

E: Union or append combines rows from compatible schemas, whereas a join combines columns based on matching keys. This directly matches the stated requirement.

Learning point: Append or union the row sets instead of joining them

Question 88

An internal audit of Fourth Coffee’s risk analytics environment identifies this requirement: keep every customer row while adding matching order data when it exists. The operations data team also notes that the team wants predictable performance and behavior. What should they do? The design decision will be reviewed by both data engineering and BI owners.

Use a DAX query with functions such as SUMMARIZECOLUMNS and CALCULATE as appropriate
Convert the column to an appropriate date or datetime data type
Use a left outer join from customers to orders
Use a Fabric lakehouse
Use Real-Time hub to browse and connect to real-time data sources

Correct answer: C

Why: A left outer join retains all rows from the left input and adds matching rows from the right input when available. This directly matches the stated requirement.

Option review:

C: A left outer join retains all rows from the left input and adds matching rows from the right input when available. This directly matches the stated requirement.

D: A lakehouse combines OneLake Delta storage with Spark-oriented engineering and a SQL analytics endpoint. This can be valid for ‘Choose between different data stores’, but it does not directly satisfy the scenario requirement being tested under ‘Merge or join data’.

Learning point: Use a left outer join from customers to orders

Question 89

The service-operations warehouse at Wide World Importers is moving from proof of concept to production. Before rollout, the data governance group must return only records that have matching keys in both prepared datasets, while ensuring that the choice should use a native Fabric capability. Which action best meets both needs? The design decision will be reviewed by both data engineering and BI owners.

Enable the supported OneLake availability or integration for the Eventhouse data
Use the Visual Query Editor to build the query graphically
Configure the appropriate gateway and connection credentials
Use an inner join on the matching business or surrogate key
Use a Power BI semantic model

Correct answer: D

Why: An inner join returns only rows with matching keys on both sides. This directly matches the stated requirement.

Option review:

A: OneLake integration makes supported Eventhouse data discoverable and accessible through OneLake for cross-engine use. This can be valid for ‘Implement OneLake integration for Eventhouse and semantic models’, but it does not directly satisfy the scenario requirement being tested under ‘Merge or join data’.

D: An inner join returns only rows with matching keys on both sides. This directly matches the stated requirement.

E: A semantic model is the analytical serving layer for governed relationships, measures, and report consumption. This can be valid for ‘Choose between different data stores’, but it does not directly satisfy the scenario requirement being tested under ‘Merge or join data’.

Learning point: Use an inner join on the matching business or surrogate key

Question 90

Northwind Traders has a change request for the executive reporting workspace: combine two similarly structured row sets into one result rather than matching columns by key. The enterprise reporting group wants a solution where the design should minimize duplicated data. Which implementation is most suitable? The design decision will be reviewed by both data engineering and BI owners.

Group the data by month and region and calculate the required summary measures
Apply the row filter as early as practical in the query or transformation
Append or union the row sets instead of joining them
Create a fact table at a defined grain with separate dimension tables
Denormalize selected attributes into the analytical table

Correct answer: C

Why: Union or append combines rows from compatible schemas, whereas a join combines columns based on matching keys. This directly matches the stated requirement.

Option review:

B: Early filtering reduces the volume of data moved and processed while preserving only the rows required by the workload. This can be valid for ‘Filter data’, but it does not directly satisfy the scenario requirement being tested under ‘Merge or join data’.

C: Union or append combines rows from compatible schemas, whereas a join combines columns based on matching keys. This directly matches the stated requirement.

D: A star schema places measurable events in a fact table and descriptive context in dimensions, simplifying analytics and improving model usability. This can be valid for ‘Implement a star schema for a lakehouse or warehouse’, but it does not directly satisfy the scenario requirement being tested under ‘Merge or join data’.

E: Denormalization trades additional storage and duplication for simpler reads and fewer joins in analytical workloads. This can be valid for ‘Denormalize data’, but it does not directly satisfy the scenario requirement being tested under ‘Merge or join data’.

Learning point: Append or union the row sets instead of joining them

Question 91

A solution architect reviewing Trey Research’s marketing semantic model asks the customer insights team to prevent duplicate business keys from producing double-counted facts. Since least privilege must be preserved, which recommendation is strongest? Existing users should keep their current access.

Identify the duplicate keys and deduplicate according to the authoritative business rule
Use the Visual Query Editor to build the query graphically
Apply an explicit predicate that excludes the test rows
Model the warehouse with conformed dimensions around fact tables
Create and manage a shared Fabric connection for the data source

Correct answer: A

Why: Deduplication must preserve the correct authoritative row while eliminating records that would inflate analytical results. This directly matches the stated requirement.

Option review:

A: Deduplication must preserve the correct authoritative row while eliminating records that would inflate analytical results. This directly matches the stated requirement.

C: A deterministic filter predicate is the direct way to prevent unwanted rows from reaching downstream analysis. This can be valid for ‘Filter data’, but it does not directly satisfy the scenario requirement being tested under ‘Identify and resolve duplicate data, missing data, or null values’.

Learning point: Identify the duplicate keys and deduplicate according to the authoritative business rule

Question 92

The next sprint for Alpine Ski House’s supply-chain lakehouse includes a task to replace a missing optional numeric value with the approved business default while preserving valid zero values. The acceptance criteria add that the rollout must support controlled validation. Which Fabric or Power BI action is appropriate? Existing users should keep their current access.

Use an inner join on the matching business or surrogate key
Use Real-Time hub to browse and connect to real-time data sources
Handle nulls explicitly with a null-replacement rule such as COALESCE
Use T-SQL with SELECT, JOIN, WHERE, GROUP BY, and aggregate functions as required
Create a OneLake shortcut to the existing data

Correct answer: C

Why: Explicit null handling avoids treating missing values as valid zeroes unless the business rule specifically requires that replacement. This directly matches the stated requirement.

Option review:

B: Real-Time hub is the Fabric experience for discovering, connecting to, and managing real-time event streams and sources. This can be valid for ‘Discover data by using OneLake catalog and Real-Time hub’, but it does not directly satisfy the scenario requirement being tested under ‘Identify and resolve duplicate data, missing data, or null values’.

C: Explicit null handling avoids treating missing values as valid zeroes unless the business rule specifically requires that replacement. This directly matches the stated requirement.

D: SQL is the native relational query language for warehouse and SQL analytics endpoint workloads. This can be valid for ‘Select, filter, and aggregate data by using SQL’, but it does not directly satisfy the scenario requirement being tested under ‘Identify and resolve duplicate data, missing data, or null values’.

E: A shortcut provides logical access to supported external or OneLake data without copying the underlying files. This can be valid for ‘Ingest or access data as needed’, but it does not directly satisfy the scenario requirement being tested under ‘Identify and resolve duplicate data, missing data, or null values’.

Learning point: Handle nulls explicitly with a null-replacement rule such as COALESCE

Question 93

Contoso is troubleshooting a design decision in the IoT telemetry solution. The desired end state is to exclude incomplete records that cannot be analyzed because a mandatory key is missing; the team wants predictable performance and behavior. Which change should the BI platform team make? Existing users should keep their current access.

Use Real-Time hub to browse and connect to real-time data sources
Filter or remediate rows with missing mandatory keys before loading the curated layer
Enable the supported OneLake availability or integration for the Eventhouse data
Append or union the row sets instead of joining them
Create a stored procedure

Correct answer: B

Why: Mandatory-key quality rules should be enforced before downstream joins and aggregations depend on those records. This directly matches the stated requirement.

Option review:

B: Mandatory-key quality rules should be enforced before downstream joins and aggregations depend on those records. This directly matches the stated requirement.

Learning point: Filter or remediate rows with missing mandatory keys before loading the curated layer

Question 94

For the finance reporting platform, Wingtip Toys has documented a business requirement to prevent duplicate business keys from producing double-counted facts. The security analytics team must meet it in a way where the choice should use a native Fabric capability. What is the best choice? No unrelated workspace or model permissions should be changed.

Use the OneLake catalog to discover and evaluate available data products
Create a SQL view
Create a fact table at a defined grain with separate dimension tables
Apply an explicit predicate that excludes the test rows
Identify the duplicate keys and deduplicate according to the authoritative business rule

Correct answer: E

Why: Deduplication must preserve the correct authoritative row while eliminating records that would inflate analytical results. This directly matches the stated requirement.

Option review:

A: The OneLake catalog is designed to discover governed Fabric data items across the organization. This can be valid for ‘Discover data by using OneLake catalog and Real-Time hub’, but it does not directly satisfy the scenario requirement being tested under ‘Identify and resolve duplicate data, missing data, or null values’.

B: A view encapsulates a reusable query and presents it as a virtual table. This can be valid for ‘Create views, functions, and stored procedures’, but it does not directly satisfy the scenario requirement being tested under ‘Identify and resolve duplicate data, missing data, or null values’.

E: Deduplication must preserve the correct authoritative row while eliminating records that would inflate analytical results. This directly matches the stated requirement.

Learning point: Identify the duplicate keys and deduplicate according to the authoritative business rule

Question 95

A governance review of Proseware’s retail performance dashboard asks for evidence that the solution can replace a missing optional numeric value with the approved business default while preserving valid zero values. Because the design should minimize duplicated data, which action should be approved? No unrelated workspace or model permissions should be changed.

Use Direct Lake against the OneLake-backed data
Use KQL project and where operators
Cast the column to the appropriate numeric type before aggregation
Handle nulls explicitly with a null-replacement rule such as COALESCE
Use a Fabric lakehouse

Correct answer: D

Why: Explicit null handling avoids treating missing values as valid zeroes unless the business rule specifically requires that replacement. This directly matches the stated requirement.

Option review:

D: Explicit null handling avoids treating missing values as valid zeroes unless the business rule specifically requires that replacement. This directly matches the stated requirement.

E: A lakehouse combines OneLake Delta storage with Spark-oriented engineering and a SQL analytics endpoint. This can be valid for ‘Choose between different data stores’, but it does not directly satisfy the scenario requirement being tested under ‘Identify and resolve duplicate data, missing data, or null values’.

Learning point: Handle nulls explicitly with a null-replacement rule such as COALESCE

Question 96

Before expanding the marketing semantic model, the customer insights team at Blue Yonder Airlines must exclude incomplete records that cannot be analyzed because a mandatory key is missing. The rollout plan says that least privilege must be preserved. Which option most directly addresses the requirement? No unrelated workspace or model permissions should be changed.

Build it in the Visual Query Editor
Create and manage a shared Fabric connection for the data source
Use KQL project and where operators
Filter or remediate rows with missing mandatory keys before loading the curated layer
Append or union the row sets instead of joining them

Correct answer: D

Why: Mandatory-key quality rules should be enforced before downstream joins and aggregations depend on those records. This directly matches the stated requirement.

Option review:

C: KQL provides concise operators for selecting columns and filtering event data. This can be valid for ‘Select, filter, and aggregate data by using KQL’, but it does not directly satisfy the scenario requirement being tested under ‘Identify and resolve duplicate data, missing data, or null values’.

D: Mandatory-key quality rules should be enforced before downstream joins and aggregations depend on those records. This directly matches the stated requirement.

Learning point: Filter or remediate rows with missing mandatory keys before loading the curated layer

Question 97

Fabrikam is redesigning its supply-chain lakehouse. The retail insights team must prevent duplicate business keys from producing double-counted facts. In addition, the rollout must support controlled validation. Which action is the best fit? The team will validate the change first in a nonproduction environment.

Enable the supported OneLake availability or integration for the Eventhouse data
Identify the duplicate keys and deduplicate according to the authoritative business rule
Create a SQL view
Create a SQL function
Add a calculated or derived column in the transformation layer

Correct answer: B

Why: Deduplication must preserve the correct authoritative row while eliminating records that would inflate analytical results. This directly matches the stated requirement.

Option review:

B: Deduplication must preserve the correct authoritative row while eliminating records that would inflate analytical results. This directly matches the stated requirement.

C: A view encapsulates a reusable query and presents it as a virtual table. This can be valid for ‘Create views, functions, and stored procedures’, but it does not directly satisfy the scenario requirement being tested under ‘Identify and resolve duplicate data, missing data, or null values’.

Learning point: Identify the duplicate keys and deduplicate according to the authoritative business rule

Question 98

During a design review for Litware’s IoT telemetry solution, one requirement is non-negotiable: replace a missing optional numeric value with the approved business default while preserving valid zero values. Because the team wants predictable performance and behavior, what should the BI platform team implement? The team will validate the change first in a nonproduction environment.

Use a Fabric data pipeline Copy activity
Flatten the required lookup attributes into the reporting structure
Apply an explicit predicate that excludes the test rows
Handle nulls explicitly with a null-replacement rule such as COALESCE
Create a DAX measure using the required aggregation and filter context logic

Correct answer: D

Why: Explicit null handling avoids treating missing values as valid zeroes unless the business rule specifically requires that replacement. This directly matches the stated requirement.

Option review:

C: A deterministic filter predicate is the direct way to prevent unwanted rows from reaching downstream analysis. This can be valid for ‘Filter data’, but it does not directly satisfy the scenario requirement being tested under ‘Identify and resolve duplicate data, missing data, or null values’.

D: Explicit null handling avoids treating missing values as valid zeroes unless the business rule specifically requires that replacement. This directly matches the stated requirement.

Learning point: Handle nulls explicitly with a null-replacement rule such as COALESCE

Question 99

The security analytics team at Woodgrove Bank is preparing the next release of its finance reporting platform. They need to exclude incomplete records that cannot be analyzed because a mandatory key is missing; the choice should use a native Fabric capability. Which choice most directly satisfies the requirement? The team will validate the change first in a nonproduction environment.

Use a Fabric warehouse
Use a left outer join from customers to orders
Filter or remediate rows with missing mandatory keys before loading the curated layer
Precompute the required daily aggregate table or result
Enable the supported OneLake availability or integration for the Eventhouse data

Correct answer: C

Why: Mandatory-key quality rules should be enforced before downstream joins and aggregations depend on those records. This directly matches the stated requirement.

Option review:

B: A left outer join retains all rows from the left input and adds matching rows from the right input when available. This can be valid for ‘Merge or join data’, but it does not directly satisfy the scenario requirement being tested under ‘Identify and resolve duplicate data, missing data, or null values’.

C: Mandatory-key quality rules should be enforced before downstream joins and aggregations depend on those records. This directly matches the stated requirement.

D: Pre-aggregation can reduce repeated scanning of detail rows when consumers consistently query a higher-level grain. This can be valid for ‘Aggregate data’, but it does not directly satisfy the scenario requirement being tested under ‘Identify and resolve duplicate data, missing data, or null values’.

Learning point: Filter or remediate rows with missing mandatory keys before loading the curated layer

Question 100

A production readiness review at Coho Winery found a gap in the retail performance dashboard. The remediation must prevent duplicate business keys from producing double-counted facts, and the design should minimize duplicated data. What is the most appropriate action? The solution must work with the current Fabric architecture rather than a parallel custom platform.

Apply an explicit predicate that excludes the test rows
Use Dataflow Gen2 for the ingestion and transformation flow
Identify the duplicate keys and deduplicate according to the authoritative business rule
Create a fact table at a defined grain with separate dimension tables
Create a SQL function

Correct answer: C

Why: Deduplication must preserve the correct authoritative row while eliminating records that would inflate analytical results. This directly matches the stated requirement.

Option review:

A: A deterministic filter predicate is the direct way to prevent unwanted rows from reaching downstream analysis. This can be valid for ‘Filter data’, but it does not directly satisfy the scenario requirement being tested under ‘Identify and resolve duplicate data, missing data, or null values’.

C: Deduplication must preserve the correct authoritative row while eliminating records that would inflate analytical results. This directly matches the stated requirement.

E: A function is appropriate for reusable parameterized logic that returns a value or table expression. This can be valid for ‘Create views, functions, and stored procedures’, but it does not directly satisfy the scenario requirement being tested under ‘Identify and resolve duplicate data, missing data, or null values’.

Learning point: Identify the duplicate keys and deduplicate according to the authoritative business rule

Question 101

For a new phase of the marketing semantic model, Adventure Works asks the customer insights team to replace a missing optional numeric value with the approved business default while preserving valid zero values. The architecture decision record also states that least privilege must be preserved. Which approach should be selected? The solution must work with the current Fabric architecture rather than a parallel custom platform.

Convert the column to an appropriate date or datetime data type
Use an Eventhouse with a KQL database
Denormalize selected attributes into the analytical table
Handle nulls explicitly with a null-replacement rule such as COALESCE
Use the Visual Query Editor to build the query graphically

Correct answer: D

Why: Explicit null handling avoids treating missing values as valid zeroes unless the business rule specifically requires that replacement. This directly matches the stated requirement.

Option review:

B: Eventhouse and KQL databases are optimized for high-volume real-time, log, and time-series analytics. This can be valid for ‘Choose between different data stores’, but it does not directly satisfy the scenario requirement being tested under ‘Identify and resolve duplicate data, missing data, or null values’.

D: Explicit null handling avoids treating missing values as valid zeroes unless the business rule specifically requires that replacement. This directly matches the stated requirement.

Learning point: Handle nulls explicitly with a null-replacement rule such as COALESCE

Question 102

Tailspin Toys is standardizing how the supply-chain lakehouse is managed. The immediate goal is to exclude incomplete records that cannot be analyzed because a mandatory key is missing. Given that the rollout must support controlled validation, which option should the retail insights team choose? The solution must work with the current Fabric architecture rather than a parallel custom platform.

Apply an explicit predicate that excludes the test rows
Use KQL operators such as where, summarize, and bin against the KQL database
Filter or remediate rows with missing mandatory keys before loading the curated layer
Use an inner join on the matching business or surrogate key
Use Real-Time hub to browse and connect to real-time data sources

Correct answer: C

Why: Mandatory-key quality rules should be enforced before downstream joins and aggregations depend on those records. This directly matches the stated requirement.

Option review:

C: Mandatory-key quality rules should be enforced before downstream joins and aggregations depend on those records. This directly matches the stated requirement.

Learning point: Filter or remediate rows with missing mandatory keys before loading the curated layer

Question 103

An internal audit of Fourth Coffee’s IoT telemetry solution identifies this requirement: prevent duplicate business keys from producing double-counted facts. The BI platform team also notes that the team wants predictable performance and behavior. What should they do? The design decision will be reviewed by both data engineering and BI owners.

Use a Fabric warehouse
Create a fact table at a defined grain with separate dimension tables
Use an inner join on the matching business or surrogate key
Identify the duplicate keys and deduplicate according to the authoritative business rule
Use the OneLake catalog to discover and evaluate available data products

Correct answer: D

Why: Deduplication must preserve the correct authoritative row while eliminating records that would inflate analytical results. This directly matches the stated requirement.

Option review:

D: Deduplication must preserve the correct authoritative row while eliminating records that would inflate analytical results. This directly matches the stated requirement.

Learning point: Identify the duplicate keys and deduplicate according to the authoritative business rule

Question 104

The finance reporting platform at Wide World Importers is moving from proof of concept to production. Before rollout, the security analytics team must replace a missing optional numeric value with the approved business default while preserving valid zero values, while ensuring that the choice should use a native Fabric capability. Which action best meets both needs? The design decision will be reviewed by both data engineering and BI owners.

Use a Fabric data pipeline Copy activity
Use a Fabric warehouse
Use T-SQL with SELECT, JOIN, WHERE, GROUP BY, and aggregate functions as required
Handle nulls explicitly with a null-replacement rule such as COALESCE
Flatten the required lookup attributes into the reporting structure

Correct answer: D

Why: Explicit null handling avoids treating missing values as valid zeroes unless the business rule specifically requires that replacement. This directly matches the stated requirement.

Option review:

D: Explicit null handling avoids treating missing values as valid zeroes unless the business rule specifically requires that replacement. This directly matches the stated requirement.

E: Flattening commonly consumed attributes can make read-oriented analytics simpler when update anomalies are controlled upstream. This can be valid for ‘Denormalize data’, but it does not directly satisfy the scenario requirement being tested under ‘Identify and resolve duplicate data, missing data, or null values’.

Learning point: Handle nulls explicitly with a null-replacement rule such as COALESCE

Question 105

Northwind Traders has a change request for the retail performance dashboard: exclude incomplete records that cannot be analyzed because a mandatory key is missing. The Fabric center of excellence wants a solution where the design should minimize duplicated data. Which implementation is most suitable? The design decision will be reviewed by both data engineering and BI owners.

Create a OneLake shortcut to the existing data
Enable the supported OneLake availability or integration for the Eventhouse data
Create and manage a shared Fabric connection for the data source
Filter or remediate rows with missing mandatory keys before loading the curated layer
Create a stored procedure

Correct answer: D

Why: Mandatory-key quality rules should be enforced before downstream joins and aggregations depend on those records. This directly matches the stated requirement.

Option review:

C: Fabric connections centralize connection details and credentials so supported items can reuse governed access. This can be valid for ‘Create a data connection’, but it does not directly satisfy the scenario requirement being tested under ‘Identify and resolve duplicate data, missing data, or null values’.

D: Mandatory-key quality rules should be enforced before downstream joins and aggregations depend on those records. This directly matches the stated requirement.

Learning point: Filter or remediate rows with missing mandatory keys before loading the curated layer

Question 106

A solution architect reviewing Trey Research’s customer 360 model asks the finance analytics squad to ensure a text-formatted transaction date sorts and filters chronologically. Since least privilege must be preserved, which recommendation is strongest? Existing users should keep their current access.

Use a Power BI semantic model
Flatten the required lookup attributes into the reporting structure
Use a DAX query with functions such as SUMMARIZECOLUMNS and CALCULATE as appropriate
Convert the column to an appropriate date or datetime data type
Create a fact table at a defined grain with separate dimension tables

Correct answer: D

Why: Using the correct native type enables valid date comparisons, filtering, and time intelligence. This directly matches the stated requirement.

Option review:

C: DAX queries and functions operate on semantic-model metadata, relationships, and filter context. This can be valid for ‘Select, filter, and aggregate data by using DAX’, but it does not directly satisfy the scenario requirement being tested under ‘Convert column data types’.

D: Using the correct native type enables valid date comparisons, filtering, and time intelligence. This directly matches the stated requirement.

Learning point: Convert the column to an appropriate date or datetime data type

Question 107

The next sprint for Alpine Ski House’s sales analytics solution includes a task to perform numeric aggregation on a source column that arrived as text. The acceptance criteria add that the rollout must support controlled validation. Which Fabric or Power BI action is appropriate? Existing users should keep their current access.

Filter or remediate rows with missing mandatory keys before loading the curated layer
Create a SQL view
Cast the column to the appropriate numeric type before aggregation
Create a OneLake shortcut to the existing data
Use T-SQL with SELECT, JOIN, WHERE, GROUP BY, and aggregate functions as required

Correct answer: C

Why: Numeric calculations require a compatible numeric type and should avoid relying on implicit conversion. This directly matches the stated requirement.

Option review:

C: Numeric calculations require a compatible numeric type and should avoid relying on implicit conversion. This directly matches the stated requirement.

Learning point: Cast the column to the appropriate numeric type before aggregation

Question 108

Contoso is troubleshooting a design decision in the risk analytics environment. The desired end state is to ensure a text-formatted transaction date sorts and filters chronologically; the team wants predictable performance and behavior. Which change should the operations data team make? No unrelated workspace or model permissions should be changed.

Create a stored procedure
Convert the column to an appropriate date or datetime data type
Use a DAX query with functions such as SUMMARIZECOLUMNS and CALCULATE as appropriate
Build it in the Visual Query Editor
Group the data by month and region and calculate the required summary measures

Correct answer: B

Why: Using the correct native type enables valid date comparisons, filtering, and time intelligence. This directly matches the stated requirement.

Option review:

B: Using the correct native type enables valid date comparisons, filtering, and time intelligence. This directly matches the stated requirement.

D: The visual editor is intended for graphical query construction while still supporting common relational operations. This can be valid for ‘Select, filter, and aggregate data by using the Visual query editor’, but it does not directly satisfy the scenario requirement being tested under ‘Convert column data types’.

Learning point: Convert the column to an appropriate date or datetime data type

Question 109

For the service-operations warehouse, Wingtip Toys has documented a business requirement to perform numeric aggregation on a source column that arrived as text. The data governance group must meet it in a way where the choice should use a native Fabric capability. What is the best choice? No unrelated workspace or model permissions should be changed.

Handle nulls explicitly with a null-replacement rule such as COALESCE
Create a DAX measure using the required aggregation and filter context logic
Cast the column to the appropriate numeric type before aggregation
Use an Eventhouse with a KQL database
Create a SQL view

Correct answer: C

Why: Numeric calculations require a compatible numeric type and should avoid relying on implicit conversion. This directly matches the stated requirement.

Option review:

A: Explicit null handling avoids treating missing values as valid zeroes unless the business rule specifically requires that replacement. This can be valid for ‘Identify and resolve duplicate data, missing data, or null values’, but it does not directly satisfy the scenario requirement being tested under ‘Convert column data types’.

C: Numeric calculations require a compatible numeric type and should avoid relying on implicit conversion. This directly matches the stated requirement.

E: A view encapsulates a reusable query and presents it as a virtual table. This can be valid for ‘Create views, functions, and stored procedures’, but it does not directly satisfy the scenario requirement being tested under ‘Convert column data types’.

Learning point: Cast the column to the appropriate numeric type before aggregation

Question 110

A governance review of Proseware’s executive reporting workspace asks for evidence that the solution can ensure a text-formatted transaction date sorts and filters chronologically. Because the design should minimize duplicated data, which action should be approved? The team will validate the change first in a nonproduction environment.

Convert the column to an appropriate date or datetime data type
Use Dataflow Gen2 for the ingestion and transformation flow
Use T-SQL with SELECT, JOIN, WHERE, GROUP BY, and aggregate functions as required
Use KQL operators such as where, summarize, and bin against the KQL database
Use an inner join on the matching business or surrogate key

Correct answer: A

Why: Using the correct native type enables valid date comparisons, filtering, and time intelligence. This directly matches the stated requirement.

Option review:

A: Using the correct native type enables valid date comparisons, filtering, and time intelligence. This directly matches the stated requirement.

Learning point: Convert the column to an appropriate date or datetime data type

Question 111

Before expanding the customer 360 model, the finance analytics squad at Blue Yonder Airlines must perform numeric aggregation on a source column that arrived as text. The rollout plan says that least privilege must be preserved. Which option most directly addresses the requirement? The team will validate the change first in a nonproduction environment.

Cast the column to the appropriate numeric type before aggregation
Use T-SQL with SELECT, JOIN, WHERE, GROUP BY, and aggregate functions as required
Flatten the required lookup attributes into the reporting structure
Create and manage a shared Fabric connection for the data source
Create a stored procedure

Correct answer: A

Why: Numeric calculations require a compatible numeric type and should avoid relying on implicit conversion. This directly matches the stated requirement.

Option review:

A: Numeric calculations require a compatible numeric type and should avoid relying on implicit conversion. This directly matches the stated requirement.

Learning point: Cast the column to the appropriate numeric type before aggregation

Question 112

Fabrikam is redesigning its sales analytics solution. The analytics engineering team must ensure a text-formatted transaction date sorts and filters chronologically. In addition, the rollout must support controlled validation. Which action is the best fit? The solution must work with the current Fabric architecture rather than a parallel custom platform.

Use a Fabric data pipeline Copy activity
Convert the column to an appropriate date or datetime data type
Configure the appropriate gateway and connection credentials
Group the data by month and region and calculate the required summary measures
Use a DAX query with functions such as SUMMARIZECOLUMNS and CALCULATE as appropriate

Correct answer: B

Why: Using the correct native type enables valid date comparisons, filtering, and time intelligence. This directly matches the stated requirement.

Option review:

B: Using the correct native type enables valid date comparisons, filtering, and time intelligence. This directly matches the stated requirement.

D: Aggregation changes detailed rows into grouped summaries at the required analytical grain. This can be valid for ‘Aggregate data’, but it does not directly satisfy the scenario requirement being tested under ‘Convert column data types’.

Learning point: Convert the column to an appropriate date or datetime data type

Question 113

During a design review for Litware’s risk analytics environment, one requirement is non-negotiable: perform numeric aggregation on a source column that arrived as text. Because the team wants predictable performance and behavior, what should the operations data team implement? The solution must work with the current Fabric architecture rather than a parallel custom platform.

Use the OneLake catalog to discover and evaluate available data products
Cast the column to the appropriate numeric type before aggregation
Model the warehouse with conformed dimensions around fact tables
Use KQL operators such as where, summarize, and bin against the KQL database
Use T-SQL with SELECT, JOIN, WHERE, GROUP BY, and aggregate functions as required

Correct answer: B

Why: Numeric calculations require a compatible numeric type and should avoid relying on implicit conversion. This directly matches the stated requirement.

Option review:

B: Numeric calculations require a compatible numeric type and should avoid relying on implicit conversion. This directly matches the stated requirement.

C: Conformed dimensions and explicit fact grain are core star-schema practices for reliable analytical joins. This can be valid for ‘Implement a star schema for a lakehouse or warehouse’, but it does not directly satisfy the scenario requirement being tested under ‘Convert column data types’.

Learning point: Cast the column to the appropriate numeric type before aggregation

Question 114

The data governance group at Woodgrove Bank is preparing the next release of its service-operations warehouse. They need to ensure a text-formatted transaction date sorts and filters chronologically; the choice should use a native Fabric capability. Which choice most directly satisfies the requirement? The design decision will be reviewed by both data engineering and BI owners.

Convert the column to an appropriate date or datetime data type
Use Direct Lake against the OneLake-backed data
Apply the row filter as early as practical in the query or transformation
Use KQL project and where operators
Create a OneLake shortcut to the existing data

Correct answer: A

Why: Using the correct native type enables valid date comparisons, filtering, and time intelligence. This directly matches the stated requirement.

Option review:

A: Using the correct native type enables valid date comparisons, filtering, and time intelligence. This directly matches the stated requirement.

Learning point: Convert the column to an appropriate date or datetime data type

Question 115

A production readiness review at Coho Winery found a gap in the executive reporting workspace. The remediation must perform numeric aggregation on a source column that arrived as text, and the design should minimize duplicated data. What is the most appropriate action? The design decision will be reviewed by both data engineering and BI owners.

Handle nulls explicitly with a null-replacement rule such as COALESCE
Use Real-Time hub to browse and connect to real-time data sources
Precompute the required daily aggregate table or result
Use the OneLake catalog to discover and evaluate available data products
Cast the column to the appropriate numeric type before aggregation

Correct answer: E

Why: Numeric calculations require a compatible numeric type and should avoid relying on implicit conversion. This directly matches the stated requirement.

Option review:

E: Numeric calculations require a compatible numeric type and should avoid relying on implicit conversion. This directly matches the stated requirement.

Learning point: Cast the column to the appropriate numeric type before aggregation

Question 116

For a new phase of the customer 360 model, Adventure Works asks the finance analytics squad to ensure a text-formatted transaction date sorts and filters chronologically. The architecture decision record also states that least privilege must be preserved. Which approach should be selected? Existing users should keep their current access.

Enable the supported OneLake availability or integration for the Eventhouse data
Convert the column to an appropriate date or datetime data type
Create a DAX measure using the required aggregation and filter context logic
Use a Power BI semantic model
Use KQL project and where operators

Correct answer: B

Why: Using the correct native type enables valid date comparisons, filtering, and time intelligence. This directly matches the stated requirement.

Option review:

B: Using the correct native type enables valid date comparisons, filtering, and time intelligence. This directly matches the stated requirement.

Learning point: Convert the column to an appropriate date or datetime data type

Question 117

Tailspin Toys is standardizing how the sales analytics solution is managed. The immediate goal is to perform numeric aggregation on a source column that arrived as text. Given that the rollout must support controlled validation, which option should the analytics engineering team choose? Existing users should keep their current access.

Apply an explicit predicate that excludes the test rows
Cast the column to the appropriate numeric type before aggregation
Use a left outer join from customers to orders
Use the Visual Query Editor to build the query graphically
Use Direct Lake against the OneLake-backed data

Correct answer: B

Why: Numeric calculations require a compatible numeric type and should avoid relying on implicit conversion. This directly matches the stated requirement.

Option review:

B: Numeric calculations require a compatible numeric type and should avoid relying on implicit conversion. This directly matches the stated requirement.

Learning point: Cast the column to the appropriate numeric type before aggregation

Question 118

An internal audit of Fourth Coffee’s risk analytics environment identifies this requirement: ensure a text-formatted transaction date sorts and filters chronologically. The operations data team also notes that the team wants predictable performance and behavior. What should they do? No unrelated workspace or model permissions should be changed.

Create a OneLake shortcut to the existing data
Use a DAX query with functions such as SUMMARIZECOLUMNS and CALCULATE as appropriate
Flatten the required lookup attributes into the reporting structure
Convert the column to an appropriate date or datetime data type
Apply an explicit predicate that excludes the test rows

Correct answer: D

Why: Using the correct native type enables valid date comparisons, filtering, and time intelligence. This directly matches the stated requirement.

Option review:

D: Using the correct native type enables valid date comparisons, filtering, and time intelligence. This directly matches the stated requirement.

Learning point: Convert the column to an appropriate date or datetime data type

Question 119

The service-operations warehouse at Wide World Importers is moving from proof of concept to production. Before rollout, the data governance group must perform numeric aggregation on a source column that arrived as text, while ensuring that the choice should use a native Fabric capability. Which action best meets both needs? No unrelated workspace or model permissions should be changed.

Cast the column to the appropriate numeric type before aggregation
Create a OneLake shortcut to the existing data
Create a DAX measure using the required aggregation and filter context logic
Apply the row filter as early as practical in the query or transformation
Denormalize selected attributes into the analytical table

Correct answer: A

Why: Numeric calculations require a compatible numeric type and should avoid relying on implicit conversion. This directly matches the stated requirement.

Option review:

A: Numeric calculations require a compatible numeric type and should avoid relying on implicit conversion. This directly matches the stated requirement.

Learning point: Cast the column to the appropriate numeric type before aggregation

Question 120

Northwind Traders has a change request for the executive reporting workspace: ensure a text-formatted transaction date sorts and filters chronologically. The enterprise reporting group wants a solution where the design should minimize duplicated data. Which implementation is most suitable? The team will validate the change first in a nonproduction environment.

Use a Fabric warehouse
Convert the column to an appropriate date or datetime data type
Use KQL project and where operators
Create a DAX measure using the required aggregation and filter context logic
Precompute the required daily aggregate table or result

Correct answer: B

Why: Using the correct native type enables valid date comparisons, filtering, and time intelligence. This directly matches the stated requirement.

Option review:

B: Using the correct native type enables valid date comparisons, filtering, and time intelligence. This directly matches the stated requirement.

E: Pre-aggregation can reduce repeated scanning of detail rows when consumers consistently query a higher-level grain. This can be valid for ‘Aggregate data’, but it does not directly satisfy the scenario requirement being tested under ‘Convert column data types’.

Learning point: Convert the column to an appropriate date or datetime data type

Question 121

A solution architect reviewing Trey Research’s marketing semantic model asks the customer insights team to load only the current fiscal years from a much larger source table. Since least privilege must be preserved, which recommendation is strongest? Existing users should keep their current access.

Apply the row filter as early as practical in the query or transformation
Cast the column to the appropriate numeric type before aggregation
Configure the appropriate gateway and connection credentials
Precompute the required daily aggregate table or result
Create the additional dimension or lookup table during transformation

Correct answer: A

Why: Early filtering reduces the volume of data moved and processed while preserving only the rows required by the workload. This directly matches the stated requirement.

Option review:

A: Early filtering reduces the volume of data moved and processed while preserving only the rows required by the workload. This directly matches the stated requirement.

Learning point: Apply the row filter as early as practical in the query or transformation

Question 122

The next sprint for Alpine Ski House’s supply-chain lakehouse includes a task to remove test records from the curated analytical table based on an environment flag. The acceptance criteria add that the rollout must support controlled validation. Which Fabric or Power BI action is appropriate? Existing users should keep their current access.

Handle nulls explicitly with a null-replacement rule such as COALESCE
Identify the duplicate keys and deduplicate according to the authoritative business rule
Enable the supported OneLake availability or integration for the Eventhouse data
Apply an explicit predicate that excludes the test rows
Create a SQL function

Correct answer: D

Why: A deterministic filter predicate is the direct way to prevent unwanted rows from reaching downstream analysis. This directly matches the stated requirement.

Option review:

D: A deterministic filter predicate is the direct way to prevent unwanted rows from reaching downstream analysis. This directly matches the stated requirement.

Learning point: Apply an explicit predicate that excludes the test rows

Question 123

Contoso is troubleshooting a design decision in the IoT telemetry solution. The desired end state is to load only the current fiscal years from a much larger source table; the team wants predictable performance and behavior. Which change should the BI platform team make? No unrelated workspace or model permissions should be changed.

Add a calculated or derived column in the transformation layer
Use a Power BI semantic model
Apply the row filter as early as practical in the query or transformation
Identify the duplicate keys and deduplicate according to the authoritative business rule
Use an Eventhouse with a KQL database

Correct answer: C

Why: Early filtering reduces the volume of data moved and processed while preserving only the rows required by the workload. This directly matches the stated requirement.

Option review:

C: Early filtering reduces the volume of data moved and processed while preserving only the rows required by the workload. This directly matches the stated requirement.

Learning point: Apply the row filter as early as practical in the query or transformation

Question 124

For the finance reporting platform, Wingtip Toys has documented a business requirement to remove test records from the curated analytical table based on an environment flag. The security analytics team must meet it in a way where the choice should use a native Fabric capability. What is the best choice? No unrelated workspace or model permissions should be changed.

Use an Eventhouse with a KQL database
Write the required SQL query against the endpoint
Apply an explicit predicate that excludes the test rows
Cast the column to the appropriate numeric type before aggregation
Use an inner join on the matching business or surrogate key

Correct answer: C

Why: A deterministic filter predicate is the direct way to prevent unwanted rows from reaching downstream analysis. This directly matches the stated requirement.

Option review:

C: A deterministic filter predicate is the direct way to prevent unwanted rows from reaching downstream analysis. This directly matches the stated requirement.

D: Numeric calculations require a compatible numeric type and should avoid relying on implicit conversion. This can be valid for ‘Convert column data types’, but it does not directly satisfy the scenario requirement being tested under ‘Filter data’.

Learning point: Apply an explicit predicate that excludes the test rows

Question 125

A governance review of Proseware’s retail performance dashboard asks for evidence that the solution can load only the current fiscal years from a much larger source table. Because the design should minimize duplicated data, which action should be approved? The team will validate the change first in a nonproduction environment.

Use Direct Lake against the OneLake-backed data
Cast the column to the appropriate numeric type before aggregation
Apply the row filter as early as practical in the query or transformation
Denormalize selected attributes into the analytical table
Use a Fabric lakehouse

Correct answer: C

Why: Early filtering reduces the volume of data moved and processed while preserving only the rows required by the workload. This directly matches the stated requirement.

Option review:

C: Early filtering reduces the volume of data moved and processed while preserving only the rows required by the workload. This directly matches the stated requirement.

Learning point: Apply the row filter as early as practical in the query or transformation

Question 126

Before expanding the marketing semantic model, the customer insights team at Blue Yonder Airlines must remove test records from the curated analytical table based on an environment flag. The rollout plan says that least privilege must be preserved. Which option most directly addresses the requirement? The team will validate the change first in a nonproduction environment.

Identify the duplicate keys and deduplicate according to the authoritative business rule
Add a calculated or derived column in the transformation layer
Denormalize selected attributes into the analytical table
Apply an explicit predicate that excludes the test rows
Configure the appropriate gateway and connection credentials

Correct answer: D

Why: A deterministic filter predicate is the direct way to prevent unwanted rows from reaching downstream analysis. This directly matches the stated requirement.

Option review:

A: Deduplication must preserve the correct authoritative row while eliminating records that would inflate analytical results. This can be valid for ‘Identify and resolve duplicate data, missing data, or null values’, but it does not directly satisfy the scenario requirement being tested under ‘Filter data’.

B: A derived column enriches the dataset with business logic while preserving the source fields. This can be valid for ‘Enrich data by adding new columns or tables’, but it does not directly satisfy the scenario requirement being tested under ‘Filter data’.

D: A deterministic filter predicate is the direct way to prevent unwanted rows from reaching downstream analysis. This directly matches the stated requirement.

E: An on-premises gateway provides the network bridge while the connection stores the credentials and source details. This can be valid for ‘Create a data connection’, but it does not directly satisfy the scenario requirement being tested under ‘Filter data’.

Learning point: Apply an explicit predicate that excludes the test rows

Question 127

Fabrikam is redesigning its supply-chain lakehouse. The retail insights team must load only the current fiscal years from a much larger source table. In addition, the rollout must support controlled validation. Which action is the best fit? The solution must work with the current Fabric architecture rather than a parallel custom platform.

Create a stored procedure
Cast the column to the appropriate numeric type before aggregation
Apply the row filter as early as practical in the query or transformation
Use a Fabric data pipeline Copy activity
Configure the appropriate gateway and connection credentials

Correct answer: C

Why: Early filtering reduces the volume of data moved and processed while preserving only the rows required by the workload. This directly matches the stated requirement.

Option review:

C: Early filtering reduces the volume of data moved and processed while preserving only the rows required by the workload. This directly matches the stated requirement.

Learning point: Apply the row filter as early as practical in the query or transformation

Question 128

During a design review for Litware’s IoT telemetry solution, one requirement is non-negotiable: remove test records from the curated analytical table based on an environment flag. Because the team wants predictable performance and behavior, what should the BI platform team implement? The solution must work with the current Fabric architecture rather than a parallel custom platform.

Use the OneLake catalog to discover and evaluate available data products
Apply an explicit predicate that excludes the test rows
Use a Fabric data pipeline Copy activity
Use a Fabric warehouse
Identify the duplicate keys and deduplicate according to the authoritative business rule

Correct answer: B

Why: A deterministic filter predicate is the direct way to prevent unwanted rows from reaching downstream analysis. This directly matches the stated requirement.

Option review:

B: A deterministic filter predicate is the direct way to prevent unwanted rows from reaching downstream analysis. This directly matches the stated requirement.

Learning point: Apply an explicit predicate that excludes the test rows

Question 129

The security analytics team at Woodgrove Bank is preparing the next release of its finance reporting platform. They need to load only the current fiscal years from a much larger source table; the choice should use a native Fabric capability. Which choice most directly satisfies the requirement? The design decision will be reviewed by both data engineering and BI owners.

Use a Power BI semantic model
Apply the row filter as early as practical in the query or transformation
Use the Visual Query Editor to build the query graphically
Use an inner join on the matching business or surrogate key
Convert the column to an appropriate date or datetime data type

Correct answer: B

Why: Early filtering reduces the volume of data moved and processed while preserving only the rows required by the workload. This directly matches the stated requirement.

Option review:

B: Early filtering reduces the volume of data moved and processed while preserving only the rows required by the workload. This directly matches the stated requirement.

Learning point: Apply the row filter as early as practical in the query or transformation

Question 130

A production readiness review at Coho Winery found a gap in the retail performance dashboard. The remediation must remove test records from the curated analytical table based on an environment flag, and the design should minimize duplicated data. What is the most appropriate action? The design decision will be reviewed by both data engineering and BI owners.

Model the warehouse with conformed dimensions around fact tables
Use KQL operators such as where, summarize, and bin against the KQL database
Configure the appropriate gateway and connection credentials
Apply an explicit predicate that excludes the test rows
Use an Eventhouse with a KQL database

Correct answer: D

Why: A deterministic filter predicate is the direct way to prevent unwanted rows from reaching downstream analysis. This directly matches the stated requirement.

Option review:

D: A deterministic filter predicate is the direct way to prevent unwanted rows from reaching downstream analysis. This directly matches the stated requirement.

Learning point: Apply an explicit predicate that excludes the test rows

Question 131

For a new phase of the marketing semantic model, Adventure Works asks the customer insights team to load only the current fiscal years from a much larger source table. The architecture decision record also states that least privilege must be preserved. Which approach should be selected? Existing users should keep their current access.

Create a fact table at a defined grain with separate dimension tables
Use KQL operators such as where, summarize, and bin against the KQL database
Handle nulls explicitly with a null-replacement rule such as COALESCE
Create a SQL view
Apply the row filter as early as practical in the query or transformation

Correct answer: E

Why: Early filtering reduces the volume of data moved and processed while preserving only the rows required by the workload. This directly matches the stated requirement.

Option review:

D: A view encapsulates a reusable query and presents it as a virtual table. This can be valid for ‘Create views, functions, and stored procedures’, but it does not directly satisfy the scenario requirement being tested under ‘Filter data’.

E: Early filtering reduces the volume of data moved and processed while preserving only the rows required by the workload. This directly matches the stated requirement.

Learning point: Apply the row filter as early as practical in the query or transformation

Question 132

Tailspin Toys is standardizing how the supply-chain lakehouse is managed. The immediate goal is to remove test records from the curated analytical table based on an environment flag. Given that the rollout must support controlled validation, which option should the retail insights team choose? Existing users should keep their current access.

Create a SQL view
Use the Visual Query Editor to build the query graphically
Use KQL project and where operators
Create and manage a shared Fabric connection for the data source
Apply an explicit predicate that excludes the test rows

Correct answer: E

Why: A deterministic filter predicate is the direct way to prevent unwanted rows from reaching downstream analysis. This directly matches the stated requirement.

Option review:

A: A view encapsulates a reusable query and presents it as a virtual table. This can be valid for ‘Create views, functions, and stored procedures’, but it does not directly satisfy the scenario requirement being tested under ‘Filter data’.

E: A deterministic filter predicate is the direct way to prevent unwanted rows from reaching downstream analysis. This directly matches the stated requirement.

Learning point: Apply an explicit predicate that excludes the test rows

Question 133

An internal audit of Fourth Coffee’s IoT telemetry solution identifies this requirement: load only the current fiscal years from a much larger source table. The BI platform team also notes that the team wants predictable performance and behavior. What should they do? No unrelated workspace or model permissions should be changed.

Convert the column to an appropriate date or datetime data type
Build it in the Visual Query Editor
Apply the row filter as early as practical in the query or transformation
Use Direct Lake against the OneLake-backed data
Use an Eventhouse with a KQL database

Correct answer: C

Why: Early filtering reduces the volume of data moved and processed while preserving only the rows required by the workload. This directly matches the stated requirement.

Option review:

C: Early filtering reduces the volume of data moved and processed while preserving only the rows required by the workload. This directly matches the stated requirement.

Learning point: Apply the row filter as early as practical in the query or transformation

Question 134

The finance reporting platform at Wide World Importers is moving from proof of concept to production. Before rollout, the security analytics team must remove test records from the curated analytical table based on an environment flag, while ensuring that the choice should use a native Fabric capability. Which action best meets both needs? No unrelated workspace or model permissions should be changed.

Use an Eventhouse with a KQL database
Configure the appropriate gateway and connection credentials
Use a Fabric warehouse
Apply an explicit predicate that excludes the test rows
Use KQL operators such as where, summarize, and bin against the KQL database

Correct answer: D

Why: A deterministic filter predicate is the direct way to prevent unwanted rows from reaching downstream analysis. This directly matches the stated requirement.

Option review:

D: A deterministic filter predicate is the direct way to prevent unwanted rows from reaching downstream analysis. This directly matches the stated requirement.

Learning point: Apply an explicit predicate that excludes the test rows

Question 135

Northwind Traders has a change request for the retail performance dashboard: load only the current fiscal years from a much larger source table. The Fabric center of excellence wants a solution where the design should minimize duplicated data. Which implementation is most suitable? The team will validate the change first in a nonproduction environment.

Create a DAX measure using the required aggregation and filter context logic
Apply the row filter as early as practical in the query or transformation
Denormalize selected attributes into the analytical table
Cast the column to the appropriate numeric type before aggregation
Precompute the required daily aggregate table or result

Correct answer: B

Why: Early filtering reduces the volume of data moved and processed while preserving only the rows required by the workload. This directly matches the stated requirement.

Option review:

B: Early filtering reduces the volume of data moved and processed while preserving only the rows required by the workload. This directly matches the stated requirement.

Learning point: Apply the row filter as early as practical in the query or transformation