Data Warehousing Architecture: Modelling for Databricks SQL
Who this is for:
Architecture / Concept Overview: Data Warehousing Architecture: Modelling for Databricks SQL
The medallion architecture provides a layered approach to data quality, with dimensional modelling applied primarily at the gold layer where analysts and BI tools consume data.
%%{init: {"theme":"base","themeVariables":{"background":"#0B0E14","primaryTextColor":"#E0E6ED","lineColor":"#5D6470","darkMode":true,"primaryColor":"#2E4A4A","secondaryColor":"#374151","secondaryTextColor":"#E0E6ED","tertiaryColor":"#111827","tertiaryTextColor":"#E0E6ED","edgeLabelBackground":"#1f2937"}}}%%
flowchart LR
classDef source fill:#3F4B59,stroke:#9CA3AF,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef ingestion fill:#5A4B36,stroke:#C9A86B,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef processing fill:#535072,stroke:#8E82B4,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef storage fill:#2E4A4A,stroke:#5FAFA8,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef serving fill:#3D5550,stroke:#6BB7AA,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef governance fill:#5A3F52,stroke:#C28BB0,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
SRC[Source Systems] -->|Raw ingestion| BRONZE[Bronze Layer<br/>Raw, append-only]
BRONZE -->|Clean & validate| SILVER[Silver Layer<br/>Conformed entities]
SILVER -->|Model & aggregate| GOLD[Gold Layer<br/>Star schema / Wide tables]
GOLD --> BI[BI Tools & Dashboards]
SRC:::source
BRONZE:::ingestion
SILVER:::processing
GOLD:::storage
BI:::serving
*Figure 1 — Medallion architecture with dimensional modelling applied at the gold layer for analyst consumption.*
A classic star schema in the gold layer consists of a central fact table surrounded by dimension tables.
%%{init: {"theme":"base","themeVariables":{"background":"#0B0E14","primaryTextColor":"#E0E6ED","lineColor":"#5D6470","darkMode":true,"primaryColor":"#2E4A4A","secondaryColor":"#374151","secondaryTextColor":"#E0E6ED","tertiaryColor":"#111827","tertiaryTextColor":"#E0E6ED","edgeLabelBackground":"#1f2937"}}}%%
graph TD
classDef source fill:#3F4B59,stroke:#9CA3AF,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef ingestion fill:#5A4B36,stroke:#C9A86B,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef processing fill:#535072,stroke:#8E82B4,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef storage fill:#2E4A4A,stroke:#5FAFA8,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef serving fill:#3D5550,stroke:#6BB7AA,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef governance fill:#5A3F52,stroke:#C28BB0,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
FACT[fact_sales<br/>order_id · date_key · product_key · customer_key · amount] --- DIM_DATE[dim_date<br/>date_key · year · quarter · month]
FACT --- DIM_PRODUCT[dim_product<br/>product_key · name · category · brand]
FACT --- DIM_CUSTOMER[dim_customer<br/>customer_key · name · segment · region]
FACT --- DIM_CHANNEL[dim_channel<br/>channel_key · name · type]
FACT:::processing
DIM_DATE:::storage
DIM_PRODUCT:::storage
DIM_CUSTOMER:::storage
DIM_CHANNEL:::storage
*Figure 2 — Star schema with a central fact table and surrounding dimension tables.*
Key Terms
Prerequisites and Setup
- Unity Catalog enabled with schemas for each medallion layer (e.g.,
main.bronze,main.silver,main.gold) CREATE TABLEprivileges on target schemas- Understanding of your source system entities and grain
- A SQL warehouse for DDL and query execution
Step-by-Step Implementation
Configuration Reference
| Pattern | Use When | Lakehouse Implementation |
|---|---|---|
| Star schema | Structured BI queries with known dimensions | Fact + dimension tables in gold schema |
| Wide / denormalised table | Simple queries, fewer joins, ad-hoc exploration | Pre-joined table in gold schema |
| SCD Type 1 | Only current state matters | MERGE ... WHEN MATCHED THEN UPDATE |
| SCD Type 2 | Historical tracking required | MERGE + effective_from/to + is_current |
| Medallion layers | Incremental data quality | bronze → silver → gold schemas |
| One Big Table (OBT) | Dashboard-specific performance | Denormalised materialised view |