12.2 Dimensional Modeling in the Lakehouse

Dimensional Modeling on Databricks

Dimensional modeling remains a foundational skill for data engineers building the Lakehouse. While the underlying storage technology (Delta Lake) differs from traditional relational databases, the core concepts of Facts and Dimensions still apply—albeit with specialized techniques optimized for distributed computing.

Star Schema vs. Snowflake Schema

In dimensional modeling, a Star Schema places a central Fact table (representing business events or metrics) surrounded by denormalized Dimension tables (providing context, such as who, what, where, and when).

A Snowflake Schema further normalizes the Dimension tables into sub-dimensions (e.g., splitting a Location dimension into separate City, State, and Country tables).

Why Star Schema Wins in the Lakehouse

In a distributed system like Databricks, joins are the most expensive operation due to network shuffling. A Star Schema requires exactly one join to connect any dimension to the fact table. A Snowflake schema requires multiple joins, which can severely degrade performance. Therefore, data engineers should strongly favor Star Schemas, accepting the slight data redundancy in the dimension tables to achieve faster read times.

Slowly Changing Dimensions (SCDs)

Dimensions change over time. A customer might move to a new state, or a product might change its category. How a data engineer handles these changes defines the SCD Type.

SCD Type 1: Overwrite

SCD Type 1 simply overwrites the old value with the new value. No historical record is kept. This is easy to implement using a Delta Lake MERGE statement but destroys historical context (e.g., past sales for that customer will now be attributed to their new state).

SCD Type 2: Historic Tracking

SCD Type 2 maintains a full history of changes. Instead of overwriting, a new record is inserted for the changed entity, and the old record is "expired."

To implement SCD Type 2, dimension tables must include specific metadata columns:

• start_date: When the record became active.
• end_date: When the record expired (often set to '9999-12-31' for active records).
• is_current: A boolean flag indicating the currently active record.

-- Querying an SCD Type 2 dimension for current attributes
SELECT * FROM dim_customers
WHERE is_current = TRUE;

Delta Live Tables (DLT) provides a built-in APPLY CHANGES INTO API that automatically manages SCD Type 1 and Type 2 operations, significantly reducing boilerplate code.

SCD Type 3: Previous Value Column

SCD Type 3 adds a specific column to track the previous value of an attribute (e.g., current_state and previous_state). This allows tracking exactly one historical change and is rarely used in modern Lakehouse designs compared to Type 2.

Surrogate Key Generation

In traditional databases, an auto-incrementing integer is used as a Surrogate Key (a unique identifier with no business meaning) to link Facts and Dimensions. In a distributed environment, generating a sequential, gapless ID across multiple compute nodes is challenging.

1. The monotonically_increasing_id() Function

This PySpark function generates unique 64-bit integers. However, the IDs are not sequential or gapless. They are based on the partition ID and the record offset within that partition. While useful for creating unique row identifiers, it is not ideal for traditional surrogate keys.

2. Hash-Based Keys (xxhash64)

A robust approach for distributed systems is generating a surrogate key by hashing the natural business keys. The xxhash64 function is highly recommended in Databricks as it is computationally fast and produces a 64-bit integer.

-- Generating a surrogate key using xxhash64
SELECT 
  xxhash64(customer_email, source_system_id) as customer_sk,
  customer_email,
  customer_name
FROM raw_customers;

This approach is deterministic, meaning the same natural key will always yield the same surrogate key, simplifying idempotency in data pipelines.

3. IDENTITY Columns

Databricks Unity Catalog supports native IDENTITY columns (e.g., GENERATED ALWAYS AS IDENTITY). This feature automatically assigns unique, monotonically increasing values during INSERT operations. This brings the Lakehouse closer to traditional RDBMS functionality and is the preferred method for generating sequential surrogate keys in modern Databricks workloads.

Handling Late-Arriving Dimensions

A common race condition in ETL pipelines occurs when a Fact record arrives before its corresponding Dimension record. For example, a sales transaction is ingested for a product_id that does not yet exist in the dim_products table. If ignored, the Fact record cannot be joined to the dimension, resulting in lost data.

The Placeholder Pattern

To handle late-arriving dimensions:

1. During Fact processing, attempt to look up the dimension surrogate key using the natural key.
2. If the dimension record is missing, generate a new surrogate key and insert a placeholder row into the dimension table. This row contains the natural key and "Unknown" or default values for all other attributes.
3. Use the newly generated surrogate key for the Fact record.
4. When the actual dimension data eventually arrives, use an SCD Type 1 or Type 2 operation to update the placeholder row with the correct attributes.

This pattern ensures referential integrity is maintained and no factual data is dropped or orphaned during the ETL process.