How to efficiently maintain complex multi-table aggregations incrementally in Microsoft Fabric?

How to efficiently maintain complex multi-table aggregations incrementally in Microsoft Fabric? Comparison with Snowflake Dynamic Tables

Hi everyone, I have a scenario where source data is continuously/incrementally loaded into Microsoft Fabric.

We have multiple entities such as Customer, Product, Orders, Sales, Inventory, Invoices and Payments.

We need to create business-ready/precomputed tables by joining multiple entities and performing aggregations.

For example, a simplified transformation could be Customer + Orders + Product, followed by joins and aggregation by Customer, Product and Month.

The challenge is not simply adding new records. A new incremental record can change an existing aggregation group.

For example, suppose we already have Customer C001, Product P001, Month Jan and Sales of 1,000. If a new order arrives for the same Customer, Product and Month with Sales of 250, the existing aggregate needs to become 1,250.

This becomes more challenging when the transformation involves multiple joins, GROUP BY, aggregations, DISTINCT counts, multiple related entities, and updates or deletes in source data.

If we rebuild the complete precomputed table during every refresh, a large amount of historical data may need to be recomputed.

As data volume and the number of entities increase, this could result in more data, larger transformations, more compute and longer refresh times.

We are therefore trying to determine the best incremental maintenance pattern for these types of tables in Microsoft Fabric.

SNOWFLAKE COMPARISON

One approach we have been looking at in Snowflake is Dynamic Tables.

Dynamic Tables provide a declarative way to define transformed or materialized results and allow Snowflake to manage refreshes based on changes and the configured refresh mode and target lag.

The capability we are particularly interested in is maintaining the derived result incrementally instead of treating every refresh as a complete rebuild.

We also understand that Snowflake Dynamic Tables do not make every transformation automatically incremental. Query shape, joins, aggregations and unsupported constructs can affect whether incremental refresh is possible.

FABRIC OPTIONS WE ARE EVALUATING

The first option we are evaluating is Lakehouse Materialized Lake Views (MLVs).

MLVs appear to provide a similar architectural pattern where source tables are transformed into a persisted materialized result and the platform manages refresh.

We are particularly interested in the optimal refresh and incremental refresh capabilities.

We would like to understand how well MLVs handle transformations involving multiple joins, GROUP BY, aggregations, large historical datasets and new records that affect existing aggregation groups.

For example, if Customer, Orders and Product are joined and aggregated by Customer, Product and Month, and a new order affects an existing historical Customer + Product + Month group, how does MLV incremental refresh handle this?

The second option we are considering is maintaining physical precomputed or serving tables in Fabric Warehouse.

The architecture would be roughly Lakehouse, Clean and Validated Data, Transformation and Aggregation, Warehouse Precomputed or Serving Table, Semantic Model and Power BI.

One possibility is to use CTAS or staging-based patterns to generate the serving tables.

However, if the transformation is rebuilt from the complete historical dataset, we may still end up recomputing a large amount of data during every refresh.

We would therefore like to understand whether there is a recommended Fabric pattern for maintaining these physical tables incrementally, particularly when new or changed source records can affect existing aggregate groups.

WHAT WE ARE TRYING TO DETERMINE

We are not trying to claim that one approach is better than another.

We are trying to identify the closest Fabric architecture to the incremental-maintenance capability we are familiar with from Snowflake Dynamic Tables.

Can MLVs efficiently maintain complex multi-table joins and aggregations incrementally as data grows?

What types of SQL transformations cause MLVs to fall back to full refresh?

How does MLV incremental refresh behave when new records modify an existing aggregation group?

For complex transformations, is it better to break the logic into multiple MLVs or intermediate layers?

If using Fabric Warehouse physical serving tables, what is the recommended pattern for avoiding full historical recomputation?

Are there Fabric-native patterns for identifying and recomputing only the affected partitions, keys or aggregation groups?

For large enterprise datasets, what approach have others found most scalable and maintainable?

OUR CURRENT ARCHITECTURE

Our architecture is roughly Source Systems, Bronze, Clean and Validated Data, Precomputed or Serving Tables, Gold or Consumption Layer, Semantic Model and Power BI.

The objective is to perform expensive joins and aggregations during data processing rather than repeatedly during interactive BI queries.

The open question is how best to maintain the Precomputed or Serving layer incrementally as source data continues to grow and change.

I would really appreciate input from anyone who has implemented this at scale in Microsoft Fabric, particularly comparisons between Fabric MLVs, Warehouse-based serving tables and other Fabric-native incremental processing patterns. 

Thanks in advance!