Dimensional modeling in Lakeflow pipelines

Dimensional modeling is a technique for organizing your gold-layer data into fact tables and dimension tables so that analysts and business intelligence (BI) tools can query it efficiently. This page explains how to build that model with Lakeflow pipelines.

Overview

Dimensional modeling separates data into two kinds of tables:

  • Fact tables hold the events or measurements you care about, such as orders, clicks, or sales. Each row is one occurrence of that event, described mostly by keys and numeric measures.
  • Dimension tables hold the descriptive context around those events, such as customers, products, or dates. Each row is one business entity.

A star schema is the shape you get when you place one fact table in the middle and join it out to several dimension tables through their keys. The layout is easy for analysts and BI tools to query and easy for engineers to reason about, because each table has a single, clear responsibility.

In Lakeflow pipelines, the star schema fits naturally at the gold layer of the medallion architecture. Bronze and silver datasets handle ingestion and cleaning, and gold materializes your fact and dimension tables so downstream consumers query them directly. Because the pipeline keeps those tables up to date incrementally, you get the query simplicity of a star schema without a separate extract, transform, load (ETL) step at the BI layer.

How it works

You build dimensions and facts as datasets in your pipeline, choosing the dataset type that matches how each one changes. For most gold-layer models:

  • Build dimension tables as materialized views (or as streaming tables with slowly changing dimension (SCD) Type 2 when you need history). A materialized view recomputes efficiently from your cleaned silver data as inputs change, giving you one row per business entity.
  • Build fact tables as streaming tables fed incrementally from silver, so gold-layer aggregates stay close to real time. Facts reference their dimensions by key rather than duplicating descriptive attributes.

For more information about the two dataset types, see Materialized views and Streaming tables. To track history in a dimension, see The AUTO CDC APIs: Simplify change data capture with pipelines.

Keys and surrogate keys

Prefer natural keys (an identifier that already exists in the source data, such as an order number) where the source's natural key is stable and usable, because it clusters and joins well. Only reach for a surrogate key (a pipeline-generated stand-in identifier) when a source reuses or changes IDs.

When you do need a surrogate key, avoid a hash surrogate such as sha2(natural_key). A hash is deliberately random, which is bad for liquid clustering and Z-order performance because physically adjacent rows end up scattered across files. Instead, derive an order-preserving surrogate deterministically from the stable natural key, so the same business entity always maps to the same surrogate. A deterministic key survives a full refresh or rebuild of the dimension, which keeps existing fact-to-dimension joins intact.

Alternately, you can use an IDENTITY column when the upstream table is append-only and never full-refreshed. Because IDENTITY values are assigned as rows are inserted, a rebuild can reassign different IDs to the same entity and silently break the fact-to-dimension joins that carried the old values.

Date dimensions

Build a dim_date as a simple materialized view generated with sequence() and explode() over a date range, rather than ingesting it from a source. It's static reference data, cheap to compute, and it simplifies date-based joins and windowing everywhere else in the model.

Examples

The following examples build a small star schema with a customer dimension and an orders fact table.

Dimension table

A dimension table is typically a materialized view built from cleaned silver data, with one row per business entity, as in the following code:

Python

from pyspark import pipelines as dp

@dp.materialized_view(name="dim_customer", comment="Customer dimension")
def dim_customer():
    return (
        spark.read.table("customers_silver")
        .select("customer_id", "customer_name", "region", "signup_date")
    )

SQL

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

Fact table

A fact table holds the measurable events, referencing dimensions by their keys rather than duplicating descriptive attributes. Keep facts narrow (mostly keys and numeric measures) and use joins to pull in descriptive detail at query time, as in the following code:

Python

from pyspark import pipelines as dp

@dp.table(name="fact_orders", comment="One row per order line, keyed to dimensions")
def fact_orders():
    return (
        spark.readStream.table("orders_silver")
        .select(
            "order_id",
            "customer_id",       # foreign key to dim_customer
            "product_id",        # foreign key to dim_product
            "order_date",        # foreign key to dim_date
            "quantity",
            "amount",
        )
    )

SQL

CREATE OR REFRESH STREAMING TABLE fact_orders
COMMENT "One row per order line, keyed to dimensions"
AS SELECT
  order_id,
  customer_id,   -- foreign key to dim_customer
  product_id,    -- foreign key to dim_product
  order_date,    -- foreign key to dim_date
  quantity,
  amount
FROM STREAM(orders_silver);

Best practices

A few practices keep a star schema healthy as it grows:

  • Keep facts as streaming tables and dimensions as materialized views unless you specifically need change history, in which case use AUTO CDC with STORED AS SCD TYPE 2. See The AUTO CDC APIs: Simplify change data capture with pipelines.
  • Use downstream BI tools to query gold materialized views directly. Lakeflow pipelines keep them incrementally refreshed, so you get near-real-time results without a separate reporting ETL step.
  • Model dimensions and facts as separate flows into the same gold layer, so each dataset can be scheduled, checkpointed, and refreshed as part of one coherent DAG. See Load and process data incrementally with Lakeflow pipeline flows.

Additional resources

  • Last updated on