Data Models and the Medallion Architecture

What it is

A data model decides how analytical data is split into tables and how those tables relate. The exam covers three standard models: the star schema, the snowflake schema and the data vault.

The Medallion Architecture is the lakehouse pattern of improving data in layers: bronze for raw data, silver for cleaned and integrated data, and gold for business-ready data. Each model has a natural home in one of these layers.

How it works

Star schema

A star schema has one central fact table surrounded by dimension tables. The fact table holds measurable events, such as order lines, with numeric measures and a foreign key to each dimension. Dimensions hold descriptive attributes, such as customer, product and date, and are denormalized into one table each.

Queries join the fact to a few dimensions and aggregate, which makes star schemas fast and easy for BI tools, dashboards and Genie Agents.

SELECT d.year, p.category, SUM(f.net_amount) AS revenue
FROM gold.fact_sales f
JOIN gold.dim_date d ON f.date_key = d.date_key
JOIN gold.dim_product p ON f.product_key = p.product_key
GROUP BY d.year, p.category;

Snowflake schema

A snowflake schema starts from a star but normalizes the dimensions into further tables. For example, product points to a separate category table, and category points to a department table.

This removes repeated attribute values and can make dimensions easier to maintain. The cost is more joins per query, so reporting is usually a little slower and harder to write.

Data vault

A data vault splits data into three kinds of tables:

New sources or attributes are added as new satellites or links without changing existing tables. Every change is kept as history, which suits auditing and integrating many changing sources. It is not designed for direct reporting, because answering a business question needs many joins.

Comparing the models

Alignment with the Medallion Architecture

Bronze keeps source data as it arrived, so it is not modeled for analysis. Silver cleans and integrates data from several sources; a data vault or a normalized model fits here because it stores integrated history flexibly.

Gold presents data for consumption. Star schemas, sometimes snowflaked, live here and feed dashboards, metric views and Genie Agents.

Keys and constraints in Databricks SQL

Dimension tables often use a surrogate key from an identity column. Unity Catalog tables can declare primary and foreign keys; these are informational and not enforced, but they document the model and help tools understand the joins.

CREATE TABLE gold.dim_customer (
  customer_key BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
  customer_id STRING,
  segment STRING,
  CONSTRAINT dim_customer_pk PRIMARY KEY (customer_key)
);

Because keys are not enforced, duplicate key values must be prevented by the pipeline that loads the table.

How the exam asks about this

• "Business users need fast dashboard queries over sales by product and month." Build a star schema in the gold layer.
• "Integrate many source systems and keep every historical change, while sources keep changing." Use a data vault in the silver layer.
• "Which data vault table stores a customer's changing address over time?" A satellite.