11.1 Dimensional Modeling Fundamentals

Overview of Dimensional Modeling in Databricks SQL

Dimensional modeling remains the cornerstone of modern data warehousing and business intelligence. Pioneered by Ralph Kimball, dimensional modeling optimizes relational data for fast querying, intuitive reporting, and efficient ad-hoc analytics. In the context of the Databricks Data Intelligence Platform and Databricks SQL, dimensional schemas provide structured semantic structures that allow analytical engines like Photon to execute high-speed join algorithms, leverage cache vectorization, and streamline aggregation processing. While traditional operational database design prioritizes normalized schemas (such as Third Normal Form, or 3NF) to minimize redundancy and prevent write anomalies during transaction processing (OLTP), analytical systems (OLAP) favor denormalized structures that minimize multi-table join chains and simplify query authoring for data analysts.

Star Schema vs. Snowflake Schema Architecture

In dimensional design, schemas are broadly categorized into two structural patterns: Star Schemas and Snowflake Schemas.

A Star Schema consists of a single central fact table surrounded by non-normalized dimension tables. Each dimension connects directly to the central fact table via a single join path, creating a star-like visual shape. This structure minimizes join complexity—queries typically require joining the fact table to one or two dimension tables.

A Snowflake Schema extends the star schema by normalizing dimension tables into sub-dimensions (for instance, splitting a dim_product table into dim_subcategory and dim_category). While snowflaking eliminates redundant text storage, it introduces multi-level join hierarchies (e.g., joining fact to product, subcategory, and category). On modern lakehouses powered by Delta Lake and Databricks SQL, Star Schemas are strongly preferred over Snowflake Schemas. Denormalized dimensions significantly reduce shuffle overhead during query execution, allowing Photon to perform fast broadcast hash joins.

Fact Tables, Grain, and Measures

At the core of any dimensional model is the Fact Table. A fact table records numeric measurements, metrics, or events resulting from business operations (e.g., retail sales transactions, website clickstreams, or inventory snapshots).

Defining the Grain of a fact table is the single most critical decision in dimensional modeling. The grain represents the exact real-world atomic detail stored in a single row. For example, a fact table grain could be "one row per item sold on a customer checkout receipt" or "one row per daily summary of account balances." Establishing the atomic (lowest level) grain provides maximum analytical flexibility, enabling analysts to slice and dice metrics across any dimension attribute without losing detail.

Fact tables contain two types of columns:

1. Foreign Keys: Integer keys linking each fact record to its corresponding dimension tables (e.g., customer_id, product_id, store_id, date_id).
2. Measures (Numeric Metrics): Quantitative values that can be aggregated. Measures fall into three operational categories:
   • Additive Measures: Values that can be meaningfully summed across all dimensions (e.g., sales_amount, quantity_sold).
   • Semi-Additive Measures: Values that can be summed across some dimensions but not time (e.g., account_balance or inventory_count where summing across days produces incorrect totals).
   • Non-Additive Measures: Ratios, percentages, or unit prices that cannot be added directly (e.g., margin_percentage or unit_price); these must be computed dynamically using underlying additive metrics (e.g., SUM(margin) / SUM(revenue)).

Dimension Tables and Slowly Changing Dimensions (SCD)

Dimension Tables contain descriptive context surrounding business events. They store contextual attributes (such as customer names, product categories, store regions, or promo details) that analysts filter, group, and slice by in SQL queries. Dimensions typically contain fewer rows than fact tables but feature many wide text attributes.

In real-world enterprise environments, dimension attributes change over time—customers change addresses, products switch categories, and stores are rebranded. Handling these updates requires Slowly Changing Dimension (SCD) strategies:

• SCD Type 1 (Overwrite): Old attribute values are directly overwritten with new values. Historical context is lost; past transactions appear as if they occurred under the new attribute value. This strategy is ideal when correcting errors or when historical tracking is irrelevant.
• SCD Type 2 (Add New Row): A new record is inserted into the dimension table whenever an attribute changes, preserving complete historical context. The existing record is marked as expired by setting a valid_to timestamp and an is_current = FALSE flag, while the new record receives a valid_from timestamp and is_current = TRUE. All historical fact transactions retain foreign key links to the dimension version active when the event took place.

Surrogate Keys and Identity Columns in Delta Lake

To support SCD Type 2 and insulate dimensional models from operational source changes, dimensional tables utilize Surrogate Keys—synthetic integer primary keys generated specifically for the data warehouse, distinct from natural business keys (e.g., Social Security Number or operational SKU).

Databricks SQL provides native identity column syntax for automatic surrogate key generation in Delta Lake tables using BIGINT GENERATED ALWAYS AS IDENTITY or BIGINT GENERATED BY DEFAULT AS IDENTITY.

-- Creating a Dimension Table with Native Identity Surrogate Keys
CREATE TABLE main.analytics.dim_customer (
    customer_sk BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
    customer_id STRING NOT NULL, -- Natural business key from source system
    customer_name STRING,
    email STRING,
    city STRING,
    state STRING,
    valid_from TIMESTAMP NOT NULL,
    valid_to TIMESTAMP,
    is_current BOOLEAN NOT NULL
) USING DELTA;

When inserting new records into dim_customer, Databricks SQL automatically assigns monotonically increasing integer values to customer_sk. Fact tables store customer_sk as a foreign key, allowing queries to join facts with exact historical dimension versions efficiently.

Data Vault Modeling for Analytical Workloads

Alongside star and snowflake designs, the exam outline expects familiarity with Data Vault modeling. Data Vault is a hub-and-spoke historical modeling approach often used in enterprise integration layers before dimensional marts are published.

When analysts encounter Data Vault on Databricks

• Bronze/silver integration zones may store hubs, links, and satellites as Delta tables under Unity Catalog.
• Analysts rarely query raw vault tables for dashboards. Instead, curated gold star schemas or metric views are built on top of vault structures.
• Compared with star schemas: Data Vault optimizes for auditability and source integration; star schemas optimize for BI query simplicity and aggregation performance on Databricks SQL / Photon.

Source systems → Data Vault (Hubs/Links/Satellites) → Dimensional mart (Star/Snowflake) → Dashboards/Genie

Exam tip: if a scenario emphasizes long-term historization and multi-source integration with minimal redesign, Data Vault is the modeling family; if it emphasizes fast BI aggregations and clear fact/dimension grain, choose star (or snowflake when dimensions are normalized).

Best Practices for Databricks SQL Data Modeling

1. Prefer Star Schemas: Keep dimensions wide and denormalized to maximize Photon broadcast join speed.
2. Choose Integer Keys: Use integer surrogate keys (BIGINT) rather than long string GUIDs for join keys to reduce memory usage and speed up hash joins.
3. Establish Clear Grain: Avoid mixing aggregated and atomic data within the same fact table.
4. Pre-compute Additive Base Metrics: Store raw additive sums in facts so non-additive ratios can be calculated dynamically during query time.