You have a Fabric F32 capacity that contains a workspace. The workspace contains a warehouse named DW1 that is modelled by using MD5 hash surrogate…

The performance degradation is directly linked to the significant growth of the fact table to 500 million rows. V-Order is a write-time optimization specific to the Microsoft Fabric storage engine that reorders data within Parquet files. This optimization improves data compression and creates a more efficient data layout, which significantly accelerates read operations by compute engines like Power BI (using Direct Lake) and the SQL endpoint. Applying V-Order to the large fact table will directly address the performance degradation caused by increased data volume, especially for scans and filters common in year-over-year analysis. This is a built-in feature that improves resource utilization, thus meeting the requirement to minimize operational costs.

A. Change the MD5 hash to SHA256: This would increase the size of the surrogate key from 128 bits to 256 bits, which would worsen join performance and increase storage costs.

B. Increase the capacity: This is a brute-force approach that would increase operational costs, directly violating one of the solution's primary requirements.

D. Modify the surrogate keys to use a different data type: While changing from a hash to an integer-based key is a data modeling best practice, V-Order is a more direct optimization for the physical data layout, which is the likely bottleneck as data volume grows. V-Order is a less disruptive, Fabric-specific feature designed to solve this exact problem.

E. Create views: Views are saved logical queries and do not alter the underlying physical storage or query execution plan. They will not improve the performance of inefficient queries on the base tables.

1. Microsoft Learn - Fabric Decision Guide: Data Warehouse vs. Lakehouse: In the "Performance" section

it states

"The warehouse (and the SQL endpoint of the lakehouse) uses a query engine that is optimized for fast queries on large volumes of data... It also includes built-in performance optimization

such as V-ordering of Parquet files..." This highlights V-Order as a core

built-in performance feature for Fabric warehouses.

2. Microsoft Learn - What is V-Order?: "V-Order is a write time optimization to the parquet file format that enables lightning-fast reads under the Microsoft Fabric compute engines

such as Power BI

SQL

and Spark... V-Order improves performance for all Microsoft Fabric engines and doesn't require any other setting from the user." This document explicitly states V-Order's purpose is to enable fast reads and improve performance across Fabric.

3. Microsoft Learn - Performance tuning with OPTIMIZE: "The OPTIMIZE command optimizes the layout of data in Delta Lake tables in Microsoft Fabric... For existing tables

you can enable V-Order by running the OPTIMIZE command." This shows the practical step to apply the V-Order optimization to the existing large table.