Microsoft DP-600 Online Access

Comprehensive Detailed Explanation

From the case study:

Research division data for Productline2 is currently in CSV format in storage2.

Requirement: “All the Research division data in the lakehouses must be presented as managed tables in Lakehouse explorer.”

In Fabric lakehouses, managed tables are stored in Delta format inside the Tables folder.

Step 1: Reading the source

df = spark.read. format ( " csv " ) \

options(header= " true " , inferSchema= " true " ) \

load( " abfss://storage1.dfs.core.windows.net/files/productline2 " )

This correctly ingests the CSV source from ADLS Gen2.

Step 2: Writing to Lakehouse as a managed table

You must write the data in Delta format to ensure it is queryable and managed within the lakehouse.

The correct path is under Tables/ , because this is where Fabric automatically manages Lakehouse managed tables .

The target table should be named productline2 , so the correct path is:

df.write.mode( " overwrite " ). format ( " delta " ).save( " Tables/productline2 " )

Why not other options?

CSV or Parquet formats would not create a managed Lakehouse table; they would just create files.

Writing to productline2 directly (without Tables/ ) would store unmanaged files in the Lakehouse Files area, not managed tables.

Writing to Tables/research/productline2 adds an unnecessary subdirectory and is not the standard structure for managed tables.

Correct Final Code

df.write.mode( " overwrite " ). format ( " delta " ).save( " Tables/productline2 " )

References

Managed tables in Microsoft Fabric Lakehouse

Delta Lake support in Fabric

Spark write to Lakehouse

Step 1: Requirement

We need to calculate total sales amount by OrderDate .

Total Sales Amount = Quantity × UnitPrice per row.

Then SUM over all rows per OrderDate.

Only include rows where the OrderDate matches a public holiday in Australia .

Step 2: Calculation Logic

If we select s.Quantity * s.UnitPrice , it only computes row-level values.

To calculate the total sales amount per OrderDate , we need:

SUM (s.Quantity * s.UnitPrice) AS TotalSalesAmt

Thus, the correct calculation: SUM(s.Quantity * s.UnitPrice)

Step 3: Join Type

We want to return only those sales that occurred on public holidays in Australia .

If we used a LEFT JOIN , we’d include all sales regardless of holiday.

A FULL OUTER JOIN would include non-matching rows, not required.

A CROSS JOIN would duplicate results unnecessarily.

A RIGHT OUTER JOIN would include holidays with no sales, which is not needed.

The correct choice is an INNER JOIN on matching dates.

Step 4: Final Query

SELECT

s.OrderDate,

SUM (s.Quantity * s.UnitPrice) AS TotalSalesAmt

FROM dbo.sales AS s

INNER JOIN dbo.publicholidays AS ph

ON s.OrderDate = ph.Date

WHERE ph.countryOrRegion = ' Australia '

GROUP BY s.OrderDate;

Why This is Correct

SUM(s.Quantity * s.UnitPrice) → ensures sales are aggregated correctly.

INNER JOIN → ensures only rows where OrderDate matches a holiday are included.

WHERE ph.countryOrRegion = ' Australia ' → filters holidays to Australia only.

References

SUM (Transact-SQL)

JOIN operations in T-SQL