Data Vault, Medallion Layers, and Gold-Layer Star Schemas

1.The medallion architecture as a map for data models

The medallion architecture organises lakehouse data into layers of increasing quality: bronze (raw), silver (validated) and gold (enriched). Data improves in structure and quality as it moves from Bronze to Silver to Gold. Databricks recommends this approach as a best practice, but it is not a requirement.

Each layer does a different job, and that job decides which data model belongs there. For analytical workloads, the key row of the Databricks comparison is *what happens in each layer*. Gold is the layer defined by dimensional modeling and aggregation, which is where star schemas live.

The gold layer holds refined, often aggregated datasets that map to business functions. You model it with a dimensional model by establishing relationships and defining measures. Because gold models a business domain, some organisations build more than one gold layer, for example for HR, finance and IT. Gold also holds pre-aggregated materialized views, so analysts don't have to rebuild common measures themselves.

CREATE OR REPLACE MATERIALIZED VIEW main.example_output.weekly_bookings AS
SELECT date_trunc('week', check_in) AS week,
       property_id,
       status,
       count(*) AS total_bookings,
       sum(total_amount) AS total_revenue
FROM samples.wanderbricks.bookings
GROUP BY week, property_id, status

Sources1

2.Data vault and normalized models in the silver layer

Data modeling often starts in the silver layer. Databricks lists several ways to represent nested or semi-structured data there, and one of them is to flatten the schema or normalize the data into multiple tables. The Databricks medallion blog describes silver as having more 3rd-Normal-Form-like models and names Data Vault-like, write-performant models as suitable for this layer. Gold then uses de-normalized, read-optimized models with fewer joins. That is where Kimball-style star schemas and Inmon-style data marts fit.

This resolves the apparent conflict with the advice to avoid 3NF. Normalized and data vault models optimise for writing and integrating data in silver. Star schemas optimise for analysts reading data in gold.

Limit of the sources: the provided Databricks material places data vault in the silver layer and calls it write-performant. It does not describe data vault's internal structure or how to build one on Databricks, so this lesson doesn't either.

Sources12

3.Building the gold-layer star schema

A star schema puts one fact table of events and measures at the centre, joined by key to dimension tables of descriptive context. In Lakeflow pipelines it fits naturally at the gold layer. Bronze and silver handle ingestion and cleaning, and gold materializes the facts and dimensions that BI tools query directly. Databricks recommends a dataset type for each kind of table:

- Dimensions are materialized views built from cleaned silver data, one row per business entity. If you need history, use a streaming table with AUTO CDC and STORED AS SCD TYPE 2. - Facts are streaming tables fed incrementally from silver, so aggregates stay close to real time. - dim_date is a materialized view generated with sequence() and explode() over a date range. You don't ingest it from a source.

CREATE OR REFRESH MATERIALIZED VIEW dim_customer
COMMENT "Customer dimension"
AS SELECT customer_id, customer_name, region, signup_date
FROM customers_silver;

Keys decide whether the star stays joinable over time. Prefer a stable natural key from the source, such as an order number, because it clusters and joins well. Use a surrogate key only when a source reuses or changes IDs. Even then, avoid hashes such as sha2(natural_key). A hash is deliberately random, so physically adjacent rows end up scattered across files, which hurts liquid clustering and Z-order performance. Instead, derive an order-preserving surrogate deterministically from the natural key, so it survives a full refresh. An IDENTITY column is only safe when the upstream table is append-only and never full-refreshed. A rebuild can give the same entity a new ID and silently break fact-to-dimension joins.

Finally, primary and foreign keys on Databricks are informational and not enforced. Declaring them documents the star's relationships, but it won't stop a fact row from pointing at a missing dimension row.

Sources34

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Hashing the natural key with sha2() is the best way to create a surrogate key for a dimension.Why is that wrong?

   A hash is deliberately random, so adjacent rows scatter across files and liquid clustering and Z-order suffer. Use an order-preserving surrogate derived deterministically from the natural key.

   Covered in Building the gold-layer star schema

2. 2.An IDENTITY column is always a safe surrogate key for a dimension.Why is that wrong?

   IDENTITY values are assigned at insert time. If the dimension is rebuilt or full-refreshed, the same entity can get a new ID and the fact-to-dimension joins break silently.

   Covered in Building the gold-layer star schema

3. 3.Declaring a foreign key from a fact table to a dimension stops orphan fact rows from being written.Why is that wrong?

   Primary and foreign keys on Databricks are informational only. They document relationships but are not enforced.

   Covered in Building the gold-layer star schema

4. 4.Data vault is the recommended model for the gold reporting layer.Why is that wrong?

   Databricks material places data-vault-like, write-performant models in silver. Gold uses de-normalized, read-optimized models such as star schemas.

   Covered in Data vault and normalized models in the silver layer