Forum Discussion

ahmedshalabyy12's avatar
ahmedshalabyy12
Icon for Helper V rankHelper V
5 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
    4 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!!

ahmedshalabyy12's avatar
ahmedshalabyy12
Icon for Helper V rankHelper V
5 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
4 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!!