Microsoft Updated DP-600 Exam Questions and Answers by kamil

Comprehensive Detailed Explanation

We have a table with:

CustomerID → unique ID

CustomerName → customer’s name

VersionDate → timestamp representing version history

The requirement:

Keep only the latest record per customer.

Ensure no new technical columns (like max version date) remain in the semantic model.

Step 1: Identify latest version per customer

We need to determine the latest VersionDate for each CustomerID.

Use Group By CustomerID and calculate Max of VersionDate.

This creates a mapping of CustomerID → latest VersionDate.

Step 2: Filter to keep only latest records

Join or filter original data so that only rows with VersionDate = Max(VersionDate) are retained.

This ensures that only the most recent record is kept per customer.

Step 3: Remove the helper column

The column holding Max VersionDate is only needed for filtering.

It must be removed because the requirement is that no new columns are loaded to the semantic model.

Step 4: Remove duplicates

As a final safeguard, remove duplicates by CustomerID, ensuring that each customer appears only once.

Final Ordered Actions

Group by CustomerID and calculate the max version date per customer ID.

Filter the query where the version date value equals the max version date value.

Remove the max version date column.

Remove duplicates based on CustomerID.

Yes, the DESCRIBE HISTORY statement does meet the goal. It provides information on the history of operations, including maintenance tasks, performed on a Delta table. References = The functionality of the DESCRIBE HISTORY statement can be verified in the Delta Lake documentation.

Comprehensive Detailed Explanation

We have a KQL table (TaxiData) with columns:

VendorID

tpep_pickup_datetime (timestamp)

payment_type

total_amount

The requirement:

Create a new column FirstPickupDateTime

It should contain the first pickup timestamp per hour

Partitioning should be done by payment_type

Step 1: Which windowing function?

row_cumsum → running cumulative sum (not needed here).

row_rank_dense → assigns ranks without gaps, but does not guarantee minimum value only.

row_rank_min → gives the first/minimum value in each window partition. ✅ Correct.

row_window_session → sessionization of events, not required.

So, the correct function is row_rank_min.

Step 2: Which comparison operator?

We need to select the row where the rank = 1 (the first per partition).

So the correct operator is == (equals).

Step 3: Partitioning

The KQL query should partition by:

bin(tpep_pickup_datetime, 1h) → buckets data into 1-hour windows

payment_type → partitions further by payment type

Completed KQL Query

TaxiData

| sort by tpep_pickup_datetime asc, payment_type asc

| extend FirstPickupDateTime = row_rank_min(tpep_pickup_datetime, 1h, 0m, payment_type)

| where FirstPickupDateTime == 1

This assigns a rank within each 1-hour, per-payment-type window, then keeps the first pickup timestamp.

Why This Works

row_rank_min → ensures we capture the first occurrence in each hour.

== → filters only the first row per partition.

bin(..., 1h) ensures grouping is by hour.

References

Kusto row_rank_min() function

KQL window functions

o add new data to the Delta table while meeting the specified requirements:

You should use the append mode to ensure that all new data is added to the table without affecting the existing rows.

You should set the mergeSchema option to true to allow the schema of the Delta table to be updated with the new columns found in the DataFrame.

The completed code would look like this:

df.write.format("delta").mode("append")

option("mergeSchema", "true")

saveAsTable("Lakehouse1.TableName")