The Implementing Analytics Solutions Using Microsoft Fabric (DP-600)

The df.explain() method does not meet the goal of evaluating data to calculate statistical functions. It is used to display the physical plan that Spark will execute. References = The correct usage of the explain() function can be found in the PySpark documentation.

Lookup activity can execute a SQL query or stored procedure and retrieve a single row or dataset. The returned values can then be passed to downstream pipeline activities.

Switch is for branching logic, not executing procedures.

Append variable just adds values to a variable.

KQL activity executes Kusto queries, not SQL stored procedures.

Therefore, the correct choice is Lookup.

When users interact with a report built from a Direct Lake semantic model that uses row-level security (RLS), the DAX queries will operate in DirectQuery mode (A). This is because the model directly queries the underlying data source without importing data into Power BI. References = The Power BI documentation on DirectQuery provides detailed explanations of how RLS and DAX queries function in this mode.

The Vertipaq Analyzer tool (B) and querying the $system.discovered_STORAGE_TABLE_COLUMNS_IN_SEGMENTS dynamic management view (DMV) (C) can help identify which columns are frequently loaded into memory. Both methods provide insights into the storage and retrieval aspects of the semantic model. References = The Power BI documentation on Vertipaq Analyzer and DMV queries offers detailed guidance on how to use these tools for performance analysis.

For a Type 2 slowly changing dimension (SCD), you typically need to add the following types of columns that do not exist in the source system:

An effective start date and time (E): This column records the date and time from which the data in the row is effective.

An effective end date and time (A): This column indicates until when the data in the row was effective. It allows you to keep historical records for changes over time.

A surrogate key (C): A surrogate key is a unique identifier for each row in a table, which is necessary for Type 2 SCDs to differentiate between historical and current records.

Scenario:

Tables:

DimAccount (accounts)

FactTransaction (transactions)

DimCustomer (customers)

Business rule:

A customer can have multiple accounts.

An account can be linked to multiple customers.

Many-to-many relationship.

Requirement: Calculate total transaction amounts by customer.

Analysis:

Direct many-to-many between FactTransaction and DimCustomer would not properly resolve aggregation.

Adding CustomerKey directly in FactTransaction is not possible because transactions link to accounts, not customers directly.

Correct approach: Create a bridge table (CustomerAccount) that maps Customer ↔ Account.

Then link FactTransaction → DimAccount → Bridge → DimCustomer.

Correct Answer:

C. A bridge table with relationships to DimCustomer and DimAccount

INSERT INTO … SELECT and SELECT INTO both physically copy all rows, which is heavy and not minimal.

CREATE TABLE AS SELECT (CTAS) also copies the data.

CREATE TABLE … AS CLONE OF creates a shallow clone in Fabric Warehouse, meaning it references the existing data without duplicating it, minimizing storage and copying.

Correct answer: B.

For Type 2 Slowly Changing Dimensions (SCD):

MERGE is the standard T-SQL approach, allowing conditional UPDATE for existing rows and INSERT for new history records, in a single statement.

UPDATE + INSERT would require multiple steps and higher resource usage.

TRUNCATE + INSERT would destroy historical data, violating SCD rules.

CREATE TABLE AS SELECT is not suitable for incremental Type 2 handling.

Correct answer: B.

Both a dataflow (A) and a Stored procedure activity in a pipeline (B) are capable of creating and populating a date dimension table. A dataflow can perform the transformation needed to create the date dimension, and it aligns with the preference for using low-code tools for data ingestion when possible. A Stored procedure could be written to generate the necessary date dimension data and executed within a pipeline, which also adheres to the technical requirements for the PoC.