Forum Discussion

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

ipkus's avatar
ipkus
Frequent Visitor
6 days ago

Materialized Views do physically persist data, and under the covers they are stored as Delta data as well. The key distinction is not the storage format, but the purpose and management of the object.

A Gold Delta table is typically your business-ready, reusable source of truth that can support many different analytical use cases. A Materialized View, on the other hand, is usually created to optimize a specific query pattern, such as pre-aggregating Sales by Region and Month. The Materialized View is automatically maintained and refreshed from its underlying tables.

So while both ultimately store data in Delta format, the recommended pattern is still:

Silver → Gold Delta Tables (source of truth) → Materialized Views (performance optimization for specific workloads)

This provides both reusability and performance without duplicating business logic across multiple datasets.

Also, consider creating Business views as well. MLV might start to capacity overhead if done across the board.