When to Choose Views vs. Materialized Lake Views

Hi ipkus​ ,

Absolutely, I agree. Fabric provides a good level of architectural flexibility, allowing us to choose between approaches based on the specific workload requirements. MLV can be a good fit when you want Fabric to proactively process and materialize data for better performance, while Views are more suitable when real-time data access and flexibility are the priority. Ultimately, the right choice comes down to the required data freshness, query performance, workload pattern, and overall business use case.

I hope this helps resolve your issue.

Thanks,

Chaithanya.

Hi ipkus​ ,

I agree that row count should not be the sole factor in deciding between MLV and virtual transformations. The recommended approach is to first determine where the processing should happen based on the workload.

• Use MLV/materialization when transformations are complex, data is consumed frequently, or query performance and concurrency are key requirements.
• Use virtual transformations when data freshness is the priority and the transformation logic can reasonably be processed at query time.
• Consider transformation complexity, freshness requirements, query patterns, concurrency, performance, storage/compute cost, and maintainability.
• For a federated Fabric environment, these should be treated as guidelines rather than fixed rules based on row count.
• If both approaches are feasible, perform a workload-based performance test and choose the option that provides the best balance of performance, freshness, and operational simplicity.

Therefore, the final recommendation is to choose the architecture based on workload and business requirements, not row count alone.

Thanks,

Chaithanya.

Thanks for sharing your perspective. I agree that these should be viewed as general guidelines rather than absolute rules or hard thresholds.

One of the challenges in a large, federated environment is that Fabric is being used by teams with varying levels of technical expertise. The intent of the article is therefore to provide simpler, practical guidelines that teams can use as a starting point when making architectural decisions, rather than implying that row count alone should determine the choice.

To me, the broader architectural question is: Where do you want the processing to happen?

Do you want to do more of the work upfront by materializing transformations through an MLV and optimize for downstream query performance? Or do you want to keep the logic virtual, get closer to real-time data, and allow Fabric to perform that processing at query time, potentially accepting some performance trade-offs?

There isn't a universally right answer. The appropriate choice depends on the transformation complexity, freshness requirements, consumption patterns, concurrency, maintainability, and the technical maturity of the teams supporting the solution.

Appreciate the additional perspective, it adds useful context to the discussion.

Hi ipkus​ ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

Regards,

Chaithanya

Hi ipkus​,

I would not make the choice based primarily on row count. I think the more useful distinction is whether you want the transformation evaluated at query time or materialized at refresh time.

A standard SQL view is a good fit when the logic is relatively lightweight, query-time freshness matters, and the runtime performance is already acceptable.

A Materialized Lake View makes more sense when joins, aggregations or other transformations are expensive and repeatedly consumed. Fabric persists the result as a Delta table, so downstream consumers query the precomputed result rather than rerunning the transformation each time.

There are also a couple of Fabric-specific considerations I would add.

For Direct Lake, Microsoft documents that an unmaterialized SQL view can cause Direct Lake on a SQL endpoint to fall back to DirectQuery. An MLV produces a physical Delta table, so it can be consumed through Direct Lake subject to the normal model guardrails.

Also, current MLVs do not require you to manually orchestrate every dependency. Fabric tracks the MLV lineage and refreshes dependent views in the correct order. With optimal refresh, Fabric can also choose between incremental, full or no refresh depending on what changed.

So my general approach would be:

• Use a normal view for simple, fresh, inexpensive query-time logic.
• Use an MLV when repeated computation, Direct Lake compatibility, or reusable Silver/Gold transformations justify the additional storage and refresh lifecycle.

I would be cautious about universal thresholds such as 5M or 50M rows. I am not aware of Microsoft documenting those as MLV decision boundaries. Query complexity, concurrency, refresh requirements and the consumption pattern are usually more useful signals than row count by itself.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.