Q1.A lakehouse is provisioned in an organization’s Fabric tenant.
In a Fabric notebook, you run the following code to save a large DataFrame.
df.write.partitionBy(“year”,”month”,”day”).mode(“overwrite”).parquet(“Files/SalesOrder”)
For each of the following statements, select [Yes] if the statement is true, or [No] if it is not.
Note: Each correct selection is worth one point.
| Statement | Yes | No | |
|---|---|---|---|
| A folder hierarchy is created for each partition key. | |||
| The generated file partitions can be read in parallel across multiple nodes. | |||
| File compression is applied to the generated file partitions. |
Show answer
When you specify partitionBy(“year”,”month”,”day”), Spark creates a folder hierarchy of year, month, and day based on each column value and writes the files there.
Because files are split per partition, they can be read in parallel across multiple nodes in a distributed environment.
In addition, the Parquet format applies file compression using Snappy by default, so files are compressed even without explicitly specifying a compression option.
Therefore, all three statements are correct with “Yes.”
Stream data into the lakehouse with Spark – Microsoft Fabric
Q2.You have a Fabric tenant that contains a lakehouse named Lakehouse1.
Lakehouse1 contains a Delta table made up of one million Parquet files.
You need to remove files that have not been referenced by the table in the past 30 days.
This operation must preserve the integrity of the transaction log and maintain the table’s ACID properties.
How should you proceed?
Show answer
To safely remove old files that are no longer referenced in a Delta table, use the VACUUM command.
VACUUM removes files not referenced by the Delta log according to the retention period conditions, so it maintains the integrity of the transaction log and the ACID properties.
OPTIMIZE and V-Order are intended to optimize read efficiency and file layout, and their main purpose is not to delete unnecessary files.
Deleting files directly in OneLake file explorer risks inconsistency with the Delta log.
Perform Delta table maintenance in a lakehouse
Q3.You have a semantic model named Model1.
Model1 contains five tables that all operate in Import mode.
Model1 has a dynamic row-level security (RLS) role named HR configured.
The HR role filters employee data so that HR managers can view only the data for the department they are responsible for.
You published Model1 to the Fabric tenant and configured the RLS role membership.
You then shared the model and the related report with users.
An HR manager reports that the data shown in the report is incomplete.
What should you do to verify the data that this HR manager sees?
Show answer
With dynamic RLS, the rows displayed change per user based on functions such as USERPRINCIPALNAME().
Therefore, checking only as the HR role cannot correctly verify the department data assigned to a specific person.
The Test as role feature in the Power BI service lets you verify the view not only as a role but also as a specific user.
Since what needs to be verified here is what the person who reported the issue sees, viewing the report as the HR manager is the correct answer.
Row-level security (RLS) in Power BI – Microsoft Fabric | Microsoft Learn
Q4.You have a Fabric tenant that contains a lakehouse named lakehouse1.
Lakehouse1 contains a Delta table named Customer.
When you run queries against Customer, you find that execution is slow.
You suspect that maintenance has not been performed on the table.
You need to identify whether maintenance tasks have been run against Customer.
Solution: You run the following Spark SQL statement.
DESCRIBE HISTORY customer
Does this meet the goal?
Show answer
DESCRIBE HISTORY is a read-only command that returns the operation-level history of a Delta table.
The history includes operations such as WRITE, MERGE, OPTIMIZE, and VACUUM, along with the execution time, user, and operation metrics.
Therefore, you can check whether maintenance has been performed on the target table.
Since the goal here is to identify whether it was performed rather than to improve performance, DESCRIBE HISTORY customer meets the requirement.
Inspect Delta table metadata – Microsoft Fabric | Microsoft Learn
Q5.You have a Fabric tenant that has a workspace named Workspace1.
Workspace1 contains a lakehouse named LH1 and a warehouse named DW1.
LH1 has a table named signindata in the dbo schema.
You need to create a stored procedure in DW1 that deduplicates the data in the signindata table.
How should you complete the T-SQL statement?
Show answer
A stored procedure in a Fabric warehouse can be created in T-SQL, with the processing body written after AS.
To begin a normal processing block, use BEGIN.
There is no need to begin a distributed transaction, and SET does not begin a processing block either.
Deduplication only needs to return unique combinations of PersonID, FirstName, and LastName, so specify DISTINCT immediately after SELECT.
For deduplication without aggregation, SELECT DISTINCT is the most appropriate.
Tutorial: Transform data using a stored procedure in a warehouse
Q6.You have a Fabric tenant that contains a new semantic model in OneLake.
Using a Fabric notebook, you read the data into a Spark DataFrame.
You need to evaluate the data to determine the minimum, maximum, mean, and standard deviation for all string and numeric columns.
Solution: You use the following PySpark expression.
df.show()
Does this meet the goal?
Show answer
df.show() is a method for displaying the contents of a Spark DataFrame on screen, and it does not compute per-column statistics.
What is required is computing descriptive statistics such as the minimum, maximum, mean, and standard deviation.
In a Fabric notebook, you can check a DataFrame’s statistical summary with something like display(df, summary = true).
Therefore, simply running df.show() does not meet the goal.
Notebook visualization in Microsoft Fabric
Q7.You have a Fabric tenant that has a workspace named Workspace1.
Workspace1 has one semantic model that contains two Microsoft Power BI reports.
A data loss prevention (DLP) policy named DLP1 is configured in a Microsoft 365 subscription.
You need to apply DLP1 to the items in Workspace1.
What should you do?
Show answer
To apply a Microsoft Purview DLP policy to Fabric or Power BI items, sensitivity labels that classify the target content are a prerequisite.
Sensitivity labels can be applied to semantic models and reports and are used as conditions for the DLP policy.
Promotion and certification are features that improve discoverability and trust, and they are not conditions for applying DLP.
A workspace identity is also for connecting to external resources and is unrelated to this requirement.
Therefore, apply sensitivity labels to the semantic model and the reports.
Sensitivity labels in Power BI – Microsoft Purview Information Protection | Microsoft Learn
Q8.You have a Fabric workspace named Workspace1.
Workspace1 contains a dataflow named Dataflow1.
Dataflow1 returns 500 rows of data.
You need to identify the minimum and maximum values for each column in the query result.
Which three data view options should you select?
Each correct answer presents part of the solution.
Show answer
Power Query data profiling lets you check column quality, column distribution, and column profile.
To review statistics such as the minimum and maximum values per column, first enable the column profile, and to display the results, enable the details pane.
Then, by showing the column profile in the details pane, you can review the statistics for each column.
Column quality centers on the percentage of valid, empty, and error values, and column distribution centers on checking the distribution of values, so they are insufficient for checking minimum and maximum values.
Using the data profiling tools – Power Query
Q9.You have a Fabric tenant that contains two workspaces named Workspace1 and Workspace2, and a user named User1.
You need to enable User1 to perform the following tasks.
Create a new domain.
Create two subdomains named subdomain1 and subdomain2.
Assign Workspace1 to subdomain1.
Assign Workspace2 to subdomain2.
This configuration must follow the principle of least privilege.
Which role should you assign to User1?
Show answer
In Fabric domains, only a Fabric admin can create a new domain.
A domain admin can create subdomains and assign workspaces within existing domains they manage, but does not have the permission to create a new domain.
A domain contributor is mainly responsible for assigning workspaces they manage to existing domains, and a workspace admin does not have domain management permissions either.
Therefore, the minimal option that satisfies all the requirements is Fabric admin.
Domains – Microsoft Fabric | Microsoft Learn
Q10.You have a Fabric warehouse that contains a table named Sales.Orders.
Sales.Orders contains the following columns.
You need to write a T-SQL query that returns the following columns.
How should you complete the code?
To answer, select the appropriate options in the answer area.
Note: Each correct selection is worth one point.
| Name | Data type | Nullable |
|---|---|---|
| OrderID | Integer | No |
| CustomerID | Integer | No |
| OrderDate | Date | No |
| Quantity | Integer | Yes |
| Weight | Decimal(18, 3) | Yes |
| ListPrice | Decimal(18, 2) | No |
| SalePrice | Decimal(18, 2) | Yes |
| Name | Description |
|---|---|
| OrderID | Returns OrderID |
| CustomerID | Returns CustomerID |
| PeriodDate | Returns a date representing the first day of the month of OrderDate |
| DayName | Returns the day-of-week name of OrderDate. Example: Wednesday |
Show answer
PeriodDate must return a date that uses the year and month of OrderDate with the day fixed to 1, so DATEFROMPARTS is appropriate.
DATEPART is a function that returns part of a value as a number, and DATETRUNC and DATE_BUCKET do not match the code format of this question.
Since DayName must return the day-of-week name, specify weekday as the datepart of DATENAME.
day and dayofyear are specifications that return the day or the day of the year, and do not produce the day-of-week name.
Therefore, select DATEFROMPARTS and weekday.
Date and time data types and functions (Transact-SQL)