CertSafari
    Databricks Certified Data Analyst Associate· Lessons

    Domain 8 · Lesson 35/39

    Star, snowflake and data vault schemas on Databricks

    Apply industry-standard data modeling techniques, such as star, snowflake, and data vault schemas, to analytical workloads.

    18 min read
    2.5% of exam
    9 sources
    Published 22 Oct 2026
    Docs as of 17 Oct 2026

    What you will be able to do

    • Explain why Databricks recommends star-like models over heavily normalized 3NF for analytical workloads
    • Build a star schema with a fact table and dimension tables, and pick keys that cluster and join well
    • Recognise a snowflake schema and decide when its storage savings are worth the extra joins
    • Place a data vault model in the silver layer and dimensional models in the gold layer

    Key concept

    Fewer joins, wider tables — On Databricks, the main cost of an analytical model is the joins a typical query has to perform. Models that keep descriptive attributes together in a few wide tables (star, then snowflake) usually beat heavily normalized designs, because the optimizer has fewer tables to combine and more columns it can use to skip files.

    1.Why Databricks favours denormalized analytical models

    A data model is a decision about how many tables a question has to touch, and on Databricks that decision drives query performance, compute cost and storage cost. Three industry-standard patterns cover most analytical work. The star schema puts a fact table at the centre of denormalized dimensions. The snowflake schema normalizes those dimensions further. The data vault is an integration model built to absorb change. Before comparing them, it helps to know what the engine rewards.

    Joins are the bottleneck. The query optimizer tries to plan joins well, but it can struggle when a single query has to combine results from many tables. It can also fail to skip records in one table when the filter is on a column of another table, and the result is a full table scan. A 3NF model, where every attribute sits in its own normalized table, runs into both problems on almost every report.

    Wide tables help in two ways. Star and snowflake schemas need fewer joins in standard queries and have fewer keys to keep in sync. Putting more fields in one table also lets the optimizer skip large amounts of data using file-level statistics, so a filter on region can prune files directly instead of waiting for a join.

    None of this means you must rebuild an existing model. Databricks can work with any data model, and if you are migrating one, you should measure its performance before you re-architect it. The recommendation against 3NF applies to new lakehouses and to new datasets added to an existing one.

    Checkpoint 1 of 9· Check yourself

    A new lakehouse uses a 3NF model. The sales table stores only a region_id. The region name sits in a separate regions table. A dashboard filters sales on region name = 'EMEA'. What does Databricks warn can happen to the sales table?

    Sources1

    2.Star schema: one fact table, denormalized dimensions

    A star schema separates data into two kinds of table. Fact tables hold the events or measurements you care about, such as orders, clicks or sales. Each row is one occurrence, described mostly by keys and numeric measures. Dimension tables hold the descriptive context around those events, such as customers, products or dates, with one row per business entity. You place one fact table in the middle and join it to several dimensions through their keys. Each table has one clear job, which makes the model easy for analysts and BI tools to query.

    In a Lakeflow pipeline, the star schema lives in the gold layer. Each dataset's type follows how the data changes. Dimensions are usually materialized views recomputed from cleaned silver data. When you need a dimension's change history, build it as a streaming table using AUTO CDC with STORED AS SCD TYPE 2 so that older attribute values are kept rather than overwritten.

    A customer dimension as a materialized view over silver data: one row per customersql
    CREATE OR REFRESH MATERIALIZED VIEW dim_customer
    COMMENT "Customer dimension"
    AS SELECT customer_id, customer_name, region, signup_date
    FROM customers_silver;

    Facts are streaming tables fed incrementally from silver, so gold-layer aggregates stay close to real time. Keep facts narrow, with mostly keys and measures. A fact points to its dimensions by key instead of copying their descriptive attributes, and those attributes are joined in at query time.

    An orders fact table: keys to each dimension plus numeric measuressql
    CREATE OR REFRESH STREAMING TABLE fact_orders
    COMMENT "One row per order line, keyed to dimensions"
    AS SELECT
      order_id,
      customer_id,   -- foreign key to dim_customer
      product_id,    -- foreign key to dim_product
      order_date,    -- foreign key to dim_date
      quantity,
      amount
    FROM STREAM(orders_silver);

    Key choice decides whether those joins stay fast and correct. Use the source's natural key, such as an order number, when it is stable, because natural keys cluster and join well. Add a surrogate key only when a source reuses or changes IDs. Even then, avoid a hash like sha2(natural_key). Hash values are deliberately random, so physically adjacent rows end up scattered across files, which hurts liquid clustering and Z-order. Derive an order-preserving surrogate deterministically from the natural key instead. An IDENTITY column is safe only if the upstream table is append-only and never full-refreshed. Otherwise a rebuild can assign new IDs to the same entities and silently break every fact row that carries the old ones.

    Primary and foreign keys on Databricks are informational and are not enforced, so a star schema does not guarantee referential integrity on its own. Declared keys still help performance. If customer has a primary key with the RELY option, the optimizer can drop the join entirely from a query that left-joins store_sales to customer but only sums ss_quantity. Each sales row matches at most one customer, so the join cannot change the result.

    A fact-to-dimension left join that a RELY primary key lets the optimizer eliminatesql
    SELECT
      SUM(ss_quantity)
    FROM
      store_sales ss
      LEFT JOIN customer c ON ss.customer_sk = c.c_customer_sk;

    Checkpoint 2 of 9· Fill the gap

    Gold-layer facts should be fed incrementally from silver so that aggregates stay close to real time. Which keyword completes this fact table definition?

    CREATE OR REFRESH  ?  TABLE fact_orders
    COMMENT "One row per order line, keyed to dimensions"
    AS SELECT
      order_id,
      customer_id,   -- foreign key to dim_customer
      product_id,    -- foreign key to dim_product
      order_date,    -- foreign key to dim_date
      quantity,
      amount
    FROM STREAM(orders_silver);

    Checkpoint 3 of 9· Check yourself

    Your CRM reuses customer IDs, so dim_customer needs a surrogate key. The dimension uses liquid clustering and is sometimes fully rebuilt. Which key does Databricks recommend?

    Sources23

    3.Snowflake schema: normalizing dimensions into subdimensions

    A snowflake schema is a star schema whose dimensions have been split into related subdimensions. It still has a central fact table connected to dimensions by foreign keys. The difference is that a product dimension, for example, no longer carries its category and department on every row. Those attributes move into their own linked tables. Drawn as an entity-relationship diagram, the branching tables look like a snowflake, which is where the name comes from.

    The TPC-H sample data shows the pattern in three tables. orders is the fact. customer is a dimension. nation holds country data and is reached only through customer, so it is a subdimension of customer. To get revenue by country, a query has to go from fact to dimension to subdimension, which takes two joins where a star schema with n_name stored on customer would need one.

    Roles of the TPC-H tables in a snowflake schema
    TableRoleJoins to
    ordersFact table (order transactions)customer on o_custkey = c_custkey
    customerDimension table (customer details)nation on c_nationkey = n_nationkey
    nationSubdimension of customer (country or region reference)—

    Metric views model this chain directly. The orders table is the source and sets the grain. The nation join is nested inside the customer join, and fields reach it with chained dot notation such as customer.nation.n_name.

    Metric view joins: nation nested under customer models the snowflakeyaml
    source: SELECT * FROM samples.tpch.orders
    
    joins:
      - name: customer
        source: samples.tpch.customer
        'on': o_custkey = c_custkey
        joins:
          - name: nation
            source: samples.tpch.nation
            'on': c_nationkey = n_nationkey

    Choosing between the two comes down to storage against reads. Normalizing the dimensions cuts redundancy and storage, because a department name is stored once instead of on every product row. You pay for that in joins, and the extra joins make queries more complex and usually slower for read-heavy analytics. A star schema accepts duplicated values in return for faster queries. If a dimension's repeated hierarchy has become expensive to store and maintain, snowflaking it is reasonable. If the gold tables feed dashboards that are refreshed all day, keep them as a star.

    Star versus snowflake schema
    ConcernStar schemaSnowflake schema
    Dimension structureDenormalized, one table per dimensionDimensions split into related subdimensions
    Data redundancyMore duplication of dataLess, through normalization
    StorageLargerMore storage-efficient
    Joins per queryFewerMore
    Read query performanceFasterNot as good as denormalized models

    Checkpoint 4 of 9· Fill the gap

    This metric view needs to model nation as a subdimension that is reached only through customer, as in a snowflake schema. Which key, placed under the customer entry, completes the definition?

    source: SELECT * FROM samples.tpch.orders
    
    joins:
      - name: customer
        source: samples.tpch.customer
        'on': o_custkey = c_custkey
         ? :
          - name: nation
            source: samples.tpch.nation
            'on': c_nationkey = n_nationkey

    Checkpoint 5 of 9· Exam question

    A retailer's dim_product table repeats the full category, subcategory, and department hierarchy on every product row, and the table has grown large enough that storage cost and duplicate-value maintenance have become a real concern. The analytics team decides to split that hierarchy into separate dim_category, dim_subcategory, and dim_department tables linked by foreign keys. What modeling pattern does this change produce?

    Sources45

    4.Data vault: change-tolerant integration in the silver layer

    Star and snowflake schemas are built for reading. A data vault is built for integration. When you need to merge data from many disparate source systems into an enterprise data warehouse that matches your business processes, the silver layer of a lakehouse often uses a 3NF or Data Vault model. This is a write-performant model. It is optimized for change, so the warehouse can be modified quickly when business processes or source systems change.

    That makes a data vault a good fit for a bank pulling customer and account data from a dozen systems that gain new product lines every quarter. The integration layer has to absorb new sources and attributes without rework, and it has to act as the single source of truth. A data vault does not replace the star schema. It feeds it: the silver warehouse is the trusted base that gold dimensional data marts are built from.

    Several alternatives fail that bank in different ways. A star dimension that is fully overwritten on every load throws away earlier attribute values. When history matters in a dimension, the star-schema answer is an SCD Type 2 streaming table, not an overwrite. A flat reporting table that joins every source and is rebuilt every night is a read-optimized endpoint and has no integration layer behind it. A snowflake schema is a normalized form of a reporting model, not an integration model.

    Any model that writes one source record into several tables also runs into Databricks transaction scoping. By default, a transaction covers a single table. Loading one record into two or more tables therefore means several independent transactions, and each can succeed or fail separately. Downstream queries must tolerate the mismatch until every table has caught up. The exception is tables with catalog commits enabled, which can take part in transactions that span multiple statements and tables. Declaring primary and foreign keys in the silver warehouse documents how its tables relate to each other in Unity Catalog, but those keys are informational only and enforce nothing.

    Checkpoint 6 of 9· Match them up

    Match each modeling choice to the goal it serves in a Databricks lakehouse.

    Tap a term, then the definition that fits it.

    Checkpoint 7 of 9· Exam question

    A BI team is building a Gold-layer table set to power an AI/BI dashboard that sales managers will refresh throughout the day. They want the dashboard's queries to run with as few joins as possible, even if that means storing some dimension attributes redundantly. Which modeling approach best fits this goal?

    Checkpoint 8 of 9· Exam question

    A bank ingests customer and account data from a dozen source systems that change frequently as new product lines are added, and compliance requires a full, queryable audit trail of every historical value each attribute ever held. The data engineering team needs a Silver-layer design that absorbs source-system changes with minimal rework and preserves that history natively. Which modeling technique should they apply?

    Sources671

    5.Matching the model to the medallion layer

    The medallion architecture refines data from raw bronze through validated silver to enriched gold. Its layers are taught in Medallion architecture (bronze, silver, gold). For modeling, the main point is that the layer decides the model. The data warehouse is modeled in silver and feeds specialized data marts in gold. Silver favours write-performant models that absorb change. Gold uses denormalized, read-optimized models with fewer joins, because business analysts, BI developers and executives query it directly.

    Where each modeling technique sits in a Databricks pipeline
    LayerRole of the dataTypical model
    BronzeLanding space for raw data in its original format, converted to Delta tablesNo modeling; raw ingestion
    SilverIntegrate disparate sources into a data warehouse, the single source of truth3NF or Data Vault
    GoldPresentation layer of one or more data martsDimensional models such as a star schema

    Because each gold data mart presents one business perspective, an organization can run several of them side by side, for example one each for HR, finance and IT, all fed by the same silver warehouse. If a question is about who queries a table and how often, start from the layer, and the model usually follows from it.

    Checkpoint 9 of 9· Match them up

    In a Databricks pipeline, the layer decides the model. Pair each medallion layer with its role and the way it is typically modeled.

    Tap a term, then the definition that fits it.

    Sources678

    Sources

    (9)

    Every claim above is drawn from one of these pages, quoted as it was written on the date shown.

    1. 1.
      “The optimizer can also fail to skip records in a table when filter parameters are on a field in another table”
      ↩︎ Why Databricks favours denormalized analytical models
      “having more data fields in a single table allows the query optimizer to skip large amounts of data using file-level statistics”
      ↩︎ Why Databricks favours denormalized analytical models
      “Databricks can work well with any data model.”
      ↩︎ Why Databricks favours denormalized analytical models
      “Each of these transactions can succeed or fail independent of other transactions”
      ↩︎ Data vault: change-tolerant integration in the silver layer
      “as there are fewer joins present in standard queries and fewer keys to keep in sync”
      ↩︎ Key concept
      “Databricks recommends against using a heavily normalized model such as third normal form (3NF).”
      ↩︎ Exam trap 1
      “Databricks recommends against using a heavily normalized model such as third normal form (3NF).”
      ↩︎ Prediction
    2. 2.
      “place one fact table in the middle and join it out to several dimension tables through their keys”
      ↩︎ Star schema: one fact table, denormalized dimensions
      “In Lakeflow pipelines, the star schema fits naturally at the gold layer of the medallion architecture.”
      ↩︎ Star schema: one fact table, denormalized dimensions
      “Build dimension tables as materialized views (or as streaming tables with slowly changing dimension (SCD) Type 2 when you need history).”
      ↩︎ Star schema: one fact table, denormalized dimensions
      “Build fact tables as streaming tables fed incrementally from silver, so gold-layer aggregates stay close to real time.”
      ↩︎ Star schema: one fact table, denormalized dimensions
      “a rebuild can reassign different IDs to the same entity and silently break the fact-to-dimension joins”
      ↩︎ Star schema: one fact table, denormalized dimensions
      “A hash is deliberately random, which is bad for liquid clustering and Z-order performance”
      ↩︎ Exam trap 3
      “derive an order-preserving surrogate deterministically from the stable natural key, so the same business entity always maps to the same surrogate.”
      ↩︎ Checkpoint
    3. 3.
      “Using the primary key or unique constraint with RELY gives the query optimizer the information it needs to eliminate the join.”
      ↩︎ Star schema: one fact table, denormalized dimensions
    4. 4.
      “dimension tables are broken into related subdimensions, creating a more normalized, snowflake shaped model”
      ↩︎ Snowflake schema: normalizing dimensions into subdimensions
      “which makes query performance faster at the cost of duplicated data”
      ↩︎ Snowflake schema: normalizing dimensions into subdimensions
      “a snowflake schema saves space but usually requires more joins”
      ↩︎ Exam trap 4
    5. 5.
      “The nested nation join demonstrates a snowflake schema pattern, joining through customer to reach geographic data, where nation is a subdimension of customer.”
      ↩︎ Snowflake schema: normalizing dimensions into subdimensions
    6. 6.
      “Often, this data follows a Third Normal Form (3NF) or Data Vault model.”
      ↩︎ Data vault: change-tolerant integration in the silver layer
      “It is optimized for change, so you can quickly modify the data warehouse”
      ↩︎ Data vault: change-tolerant integration in the silver layer
      “Your data warehouse should serve as the single source of truth for your data marts.”
      ↩︎ Data vault: change-tolerant integration in the silver layer
      “The data warehouse is modeled in the silver layer and feeds specialized data marts in the gold layer.”
      ↩︎ Matching the model to the medallion layer
      “build a data warehouse in alignment with your existing business processes. Often, this data follows a Third Normal Form (3NF) or Data Vault model.”
      ↩︎ Checkpoint
    7. 7.
      “Data Vault-like, write-performant data models can be used in this layer.”
      ↩︎ Data vault: change-tolerant integration in the silver layer
      “The Gold layer is for reporting and uses more de-normalized and read-optimized data models with fewer joins.”
      ↩︎ Matching the model to the medallion layer
    8. 8.
      “Because the gold layer models a business domain, some customers create multiple gold layers to meet different business needs, such as HR, finance, and IT.”
      ↩︎ Matching the model to the medallion layer

    Also cited

    Spotted a mistake, or was something unclear? Tell us.