Forum Discussion

ahmedshalabyy12's avatar
ahmedshalabyy12
Icon for Helper V rankHelper V
9 days ago
Solved

Materialized view vs delta tables in gold layer

Hello, I asked ChatGPT this question, but I got a useless answer from the AI, and it changed its opinion twice during the session. So I decided to ask the experts: from your previous experience, w...
data engineering
lakehouse
  • v-sathmakuri's avatar
    v-sathmakuri
    8 days ago

    Hi ahmedshalabyy12​ ,

    For example, if Silver has millions of sales records:

    Gold Delta table: Store the cleansed, business ready Sales data. This is your source of truth and can be reused by Power BI, notebooks and other pipelines.
    Materialized view: If Power BI frequently needs Sales by Region and Month, create an MV that pre-computes that aggregation for faster queries.

    So, the approach would be :  Silver -> Gold Delta table -> Materialized views for specific performance needs

    In short, use Delta tables for the core Gold data and materialized views when you need to optimize frequently used queries/aggregations.

    Thanks!!

v-sathmakuri's avatar
v-sathmakuri
Icon for Community Support rankCommunity Support
9 days ago

Hi ahmedshalabyy12​ ,

Thank you for reaching out to fabric community.

I would make Delta tables the default choice for the Gold layer, since they offer greater control over transformations, schema, incremental processing, history and reuse across workloads.

Materialized views are the better fit when the main objective is query performance for example, when pre-aggregating data or streamlining frequently run queries.

In short, Delta tables should hold the authoritative Gold data, while materialized views serve as performance-optimized derivatives built on top of it.

Thanks!!

  • ahmedshalabyy12's avatar
    ahmedshalabyy12
    Icon for Helper V rankHelper V
    9 days ago

    Please, if you can, elaborate more with an example since I know delta tables and optimized by Databricks or Fabric 

    • v-sathmakuri's avatar
      v-sathmakuri
      Icon for Community Support rankCommunity Support
      8 days ago

      Hi ahmedshalabyy12​ ,

      For example, if Silver has millions of sales records:

      Gold Delta table: Store the cleansed, business ready Sales data. This is your source of truth and can be reused by Power BI, notebooks and other pipelines.
      Materialized view: If Power BI frequently needs Sales by Region and Month, create an MV that pre-computes that aggregation for faster queries.

      So, the approach would be :  Silver -> Gold Delta table -> Materialized views for specific performance needs

      In short, use Delta tables for the core Gold data and materialized views when you need to optimize frequently used queries/aggregations.

      Thanks!!