Kimball Dimensional Modeling

Data Modeling include:

  • Conceptual Modeling
  • Logical Modeling
  • Physical Modeling
  • Operational Data Modeling
  • Relational Modeling
  • Data Vault
  • Dimensional Modeling
    e.g. Kimball dimensional modeling
  • Other Modeling Approaches

Dimensional Modeling is a data modeling technique designed primarily for analytical workloads.

It organizes data around:

  • Facts — measurable business events 可以度量的业务事件
  • Dimensions — descriptive business context 描述业务上下文的信息

Kimball Dimensional Modeling

Kimball Dimensional Modeling is an approach to designing analytical data warehouses using dimensional models, centered around business processes, facts, dimensions, conformed dimensions, and a bus architecture.

Kimball Dimensional Modeling belongs to Dimmensional Modeling.

The Core Philosophy of Kimball:
Start with the business process, not the database tables.

Important concept of Kimball’s dimensional modeling

Business Process

A Business Process is a business activity that an organization performs and wants to measure, analyze, and improve.
企业正在执行的一类业务活动,而且企业希望对这类活动进行度量、分析和改进. e.g.

  • 销售 Sales
  • 订单履行 Order Fulfillment
  • 库存 Inventory
  • 支付 Payments
  • 退货 Returns
  • 保险理赔 Claims
  • 医院入院 Admissions
  • 贷款申请 Loan Applications
  • 客户服务 Customer Service

Kimball dimensional modeling starts with the business process.

Typical examples of Business Processes

Retail:

  • Sales
  • Inventory
  • Returns
  • Payments
  • Shipping

Banking:

  • Account Opening
  • Transactions
  • Payments
  • Loans
  • Credit Card Transactions
  • Fraud Detection

Government

  • Application
  • Case Processing
  • Permit Issuance
  • Payment
  • Inspection
  • Appeal

Kimball Dimensional Design — 4 Steps

The four critical steps of the Kimball lifecycle / dimensional modeling methodology are:

  1. Select the Business Process
  2. Declare the Grain
  3. Identify the Dimensions
  4. Identify the Facts

An Example: “We want to analyze credit card transactions.”

Do not create tables now.

Step 1: Select the Business Process

What is the Business Process? Credit Card Transaction

What is the Business Event? A cardholder makes a transaction
at a merchant.

Step 2: Declare the Grain

What is the Grain? One row = one credit card transaction

Step 3: Identify the Dimensions

What Dimensions?

  • Customer
  • Card
  • Merchant
  • Date
  • Time
  • Location
  • Transaction Type

Step 4: Identify the Facts

What Facts?

  • Transaction Amount
  • Fee Amount
  • Tax Amount
  • Reward Points

Finally:

                  Dim Customer
                       |
                       |
Dim Card ------- Fact Transaction ------ Dim Merchant
                       |
                       |
                    Dim Date
                       |
                       |
                    Dim Time

This is Kimball’s thought.

Implementation – Initially Physical Model

Assuming, I have decided:
Grain = one row per sales order line

#  Fact
CREATE TABLE fact_sales (
    sales_date_key       INT,
    customer_key         BIGINT,
    product_key          BIGINT,
    store_key            BIGINT,

    order_number         STRING,

    quantity             DECIMAL(18,2),
    unit_price            DECIMAL(18,2),
    discount_amount       DECIMAL(18,2),
    sales_amount          DECIMAL(18,2),
    cost_amount           DECIMAL(18,2)
);



#  Dimensions:
CREATE TABLE dim_customer (
    customer_key BIGINT,
    customer_id  STRING,
    customer_name STRING,
    province STRING,
    customer_segment STRING
);

CREATE TABLE dim_product (
    product_key BIGINT,
    product_id STRING,
    product_name STRING,
    category STRING,
    brand STRING
);

CREATE TABLE dim_date (
    date_key INT,
    calendar_date DATE,
    year INT,
    quarter INT,
    month INT,
    month_name STRING,
    day_of_week STRING
);

Grain:Fact Table

The grain defines exactly what one row in a fact table represents.
Grain 定义 Fact Table 中“一行数据到底代表什么业务事件”。

One row = one sales transaction
一行 = 一笔销售交易

One row = one sales transaction
一行 = 一笔销售交易

One row = one order line
一行 = 一个订单明细

Grain is the semantic contract of the fact table.

Three common fact grains in Kimball

In data warehousing, these three grains are formally known as:

  1. Transaction Fact grain (事务粒度)
  2. Periodic snapshot Fact grain (周期快照粒度)
  3. Accumulating snapshot Fact grain (累积快照粒度)
Fact TypeGrain核心问题
Transaction FactOne business event
一次业务事件
What happened?
发生了什么?
Periodic SnapshotOne entity/process at a periodic point
某个周期时点的状态
What was the state?
当时状态如何?
Accumulating SnapshotOne process lifecycle
一个业务流程生命周期
How did the process progress?
流程进行到哪里?

Transaction Fact Grain

One row represents one business transaction/event.

e.g. One row = one order line

DateCustomerProductStoreQtySales
Sep 13C001P001S00111200
Sep 13C001P002S0012500
Sep 13C001P003S001150

Periodic snapshot Fact grain

A row represents the state of a business process at a regular point in time.
一行代表某个业务对象/流程在一个固定周期时间点的状态。

e.g. Account Balance Snapshot
One row = Customer Account + Day

DateAccountBalance
Sep 11A00110,000
Sep 12A0019,500
Sep 13A00111,000

Accumulating snapshot Fact grain

A single row represents the entire lifecycle of a process or a specific entity that moves through multiple, predictable milestones.

e.g. Order Fulfillment

Order Created
↓
Payment Approved
↓
Warehouse Processing
↓
Shipped
↓
Delivered

We can represent the entire lifecycle of an order in one row.

OrderOrder DatePayment DateShip DateDelivery Date
10001Sep 10Sep 10Sep 11Sep 13

Grain Determines Whether a Fact Is Valid

A fact is only meaningful if it is measured at the same grain as the fact table.
一个 Fact 只有在与 Fact Table 的 Grain 相匹配时,才是有意义、可以正确分析的。

Suppose we define:

Grain: One row represents one Order Line.

That means every row in the fact table represents exactly:

One product on one order

OrderProductQuantitySales Amount
O1001iPhone1$1,200
O1001AirPods2$500
O1002MacBook1$2,000

A error fact example.

OrderProductSales AmountOrder Total
O1001iPhone1,2001,700
O1001AirPods5001,700

there is issue on the column “Order Total”
把 $1,700 放进一个 Order-Line Grain 的 Fact Table,并让它看起来像每一行的可加 Fact,是错误的设计。 我们的 Fact Grain 是 “One row represents one Order Line.” 一行一个order 的 “行” – order 上的一行。

Order Total O1001 这份order的 合计金额, 是 “Order Grain” – 一份order的. 放在目前的Fact 上就不对。

如果一定要使用 “Order Total ”, 那就再叫另外一个Fact_order,

Grain:One row per Order

OrderCustomerOrder TotalShipping Cost
O1001C001170020
O1002C002200030

Grain Determines Dimension Context

Kimball-style terminology: The grain defines not only what the fact represents, but also which dimensions can validly describe the row.

Dimensions

A Dimension is a business-oriented structure that provides the descriptive context used to analyze facts.
用于描述和分析 Fact 的业务分析结构。

A Very Important Kimball Principle

Kimball Principle: Dimensions should be understandable to business users.

Data mesh

Data mesh is a decentralized data architecture and organizational model that shifts data ownership from a single central IT team to individual business domains.

Its core idea is: Data should be owned and managed by the business domains that understand that data best, while still following common enterprise governance standards.

Instead of one central data team owning everything, different business domains become responsible for their own data products.

Enterprise
│
├── Customer Domain
│   └── Customer Data Product
│
├── Finance Domain
│   └── Finance Data Product
│
├── Risk Domain
│   └── Risk Data Product
│
├── HR Domain
│   └── Employee Data Product
│
└── Operations Domain
    └── Operations Data Product

Data Mesh, on the other hand, becomes: Domain owns the data, Enterprise governs the data.

Finance Domain → looks after Finance Data
HR Domain → looks after HR Data
Risk Domain → looks after Risk Data
Customer Domain → looks after Customer Data

What is Data Mesh trying to address?

  • Domain Ownership
    Data is owned by the business domains.
  • Data as a Product
    Data must be treated like a product.
  • Self-Serve Data Platform
    The enterprise provides a centralized platform enabling domains to autonomously publish and govern their data.
  • Federated Computational Governance
    The enterprise continues to define standardized rules.
    • Security
    • Privacy
    • Governance
    • Compliance
    • Data Quality
    • Metadata
    • Lineage

What is Domain-Driven Modeling?

Domain-Driven Modeling means: Designing data models around the business domains, business concepts, business processes, and business rules rather than starting from source-system tables.

Domain vs Canonical Model

Canonical Model ≠ Data Mesh

Data Mesh –>Reorganize data ownership, data products, the platform, and governance.

                 Enterprise
                     │
             Data Mesh Governance
                     │
        ┌────────────┼────────────┐
        ↓            ↓            ↓
    Customer       Finance       Risk
     Domain         Domain       Domain
        │            │            │
     Canonical    Canonical    Canonical
      Model         Model        Model
        │            │            │
    Data Product  Data Product  Data Product

Data Mesh targets a different set of challenges: data is owned by the business domains, it must be treated like a product, and the enterprise provides a unified self-serve platform. Ultimately, it requires reorganizing data ownership, data products, the platform, and governance.

What exactly are we modeling in Domain-Driven Design?

Business Domain

First, determine: What business area are we modeling?

  • Customer
  • Payment
  • Risk
  • Mortgage
  • Finance

Business Capability

Within a domain, we can further examine business capabilities

Customer Domain
│
├── Customer Onboarding
├── Customer Identification
├── Customer Profile Management
├── Customer Relationship Management
└── Customer Communication

Capability indicates: What the business domain can do.
Capability 表示:这个业务领域能够做什么。

Business Concept

Then, identify the truly critical business concepts within the domain.
然后识别 Domain 中真正重要的业务概念。

# Customer Domain:
Customer
Customer Profile
Customer Relationship
Customer Address
Customer Contact

# Payment Domain:
Payment
Payment Instruction
Payment Method
Payment Transaction
Payment Status

Business Event

Taking it a step further: What events occur within the domain?

e.g.

  • CustomerCreated
  • CustomerUpdated
  • CustomerAddressChanged
  • PaymentInitiated
  • PaymentCompleted
  • PaymentFailed

In Domain-Driven Design (DDD), things that “happen” are specifically called “Domain Events.”

Using the word “events” makes your phrasing sound highly sophisticated and immediately signals the right architectural concept to other engineers.

Business Rule

Domain has business rules. e.g.

Payment amount > $10,000
↓
Additional approval required

Data Entity

Finally, data modeling

Customer
Account
Payment
Address
Transaction

THen define

Attributes
Keys
Relationships
Cardinality
Constraints
History

Therefore, the overall process is as follows:

Business Domain
       ↓
Business Capability
       ↓
Business Concept
       ↓
Business Event
       ↓
Business Rule
       ↓
Domain Entity
       ↓
Data Model
       ↓
Data Product

Complete example

Step 1 — Identify Domain

Customer Domain: Manage customer identity, profile and relationship information.

Step 2 — Identify Business Concepts

Customer
Customer Profile
Customer Address
Customer Contact
Customer Relationship

Step 3 — Identify Business Events

CustomerCreated
CustomerUpdated
CustomerAddressChanged
CustomerStatusChanged

Step 4 — Identify Business Rules

e.g. Customer must have a unique enterprise customer ID.

Customer status must be one of:

  • ACTIVE
  • INACTIVE
  • SUSPENDED
  • CLOSED

Step 5 — Build Domain Model

Customer
│
├── Customer Profile
│
├── Customer Address
│
├── Customer Contact
│
└── Customer Relationship

Step 6 — Map Source Systems

CRM
   │
   ├── CRM_CUSTOMER
   └── CRM_CONTACT
             │
             ↓
Core Banking ─────→ Customer Domain
   │
   └── CUSTOMER
             │
             ↓
Mortgage
   │
   └── BORROWER

Step 7 — Canonical Model

Customer
------------------------
customer_id
customer_type
first_name
last_name
date_of_birth
status
created_date
updated_date

Step 8 — Data Product

Customer Domain provides “Customer Data Product”.

e.g. customer.gold_customer. it should has

  • Owner
  • Schema
  • Description
  • Data Quality
  • SLA
  • Security
  • Lineage
  • Documentation
  • Access Policy

As a result, the downstream Risk team does not need direct access to:

  • CRM
  • Core Banking
  • Mortgage

Instead, they consume: Customer Data Product

How to implement in Databricks

                    Enterprise Data Mesh
                            │
              Federated Governance
                            │
        ┌───────────────────┼───────────────────┐
        ↓                   ↓                   ↓
 Customer Domain       Finance Domain       Risk Domain
        │                   │                   │
        ↓                   ↓                   ↓
 Unity Catalog         Unity Catalog        Unity Catalog
        │                   │                   │
        ↓                   ↓                   ↓
 Delta Tables          Delta Tables         Delta Tables
        │                   │                   │
        ↓                   ↓                   ↓
 Data Products         Data Products        Data Products

Catalog / Schema designing

e.g.

customer_catalog
│
├── bronze
├── silver
└── gold

Do not think “One Domain = One Catalog”,
No, implementation choice, it is NOT data Mesh define

Domain-owned tables

-- ============================================================
-- PURPOSE:
-- Create the Customer domain's curated customer entity.
--
-- KEY IDEA:
-- The Customer domain owns the business meaning and quality
-- of this dataset.
-- ============================================================

CREATE TABLE customer_catalog.gold.customer
(
    -- Enterprise-wide business identifier.
    customer_id STRING NOT NULL,

    -- Business classification of the customer.
    customer_type STRING,

    -- Standardized customer name.
    first_name STRING,
    last_name STRING,

    -- Standardized date of birth.
    date_of_birth DATE,

    -- Controlled business status.
    status STRING,

    -- Audit columns.
    created_date TIMESTAMP,
    updated_date TIMESTAMP
)
USING DELTA;

Key Takeaways

Data Mesh is primarily about how an enterprise organizes ownership, products, platforms, and governance around data domains.

Domain: Business responsibility boundary

Domain-Driven Modeling: Business first –> Data model second

Canonical Model: Standardized enterprise representation of data

Data Product: A Data Product is not simply a Delta table.

Canonical Model

1. What is Canonical Model?

A canonical model is a design pattern that uses a single, standardized data format to let different systems talk to each other without needing custom connections for every pair.

先用人话说, Canonical Model = 企业统一规定的一套“标准数据语言”。大家都必须按照同一套“词典 + 语法 + 数据结构”来描述同一个业务对象。

For example, a company has 10 data system, each system has “customer”. but the “customer” is named in different way in each data system.

SystemCustomer IDCustomer NamePhone
CRMcustomer_idcustomer_namephone
BankingCUST_NOCUST_NMMOBILE_NO
Insuranceclient_idfull_nametelephone
ERPBP_IDBP_NAMETEL_NO

Canonical Model means that all “Customer” will be uniformly defined in this way in the future in the company.

Customer
│
├── customer_id
├── first_name
├── last_name
├── date_of_birth
├── email
├── phone
└── address

CRM.customer_id → Canonical.Customer.customer_id

Bank.CUST_NO → Canonical.Customer.customer_id

Insurance.client_id → Canonical.Customer.customer_id

So, this is the core concept of Canonical. Reduce data integration complexity through a common enterprise representation.

2. What are included in Canonical Model

2.1 Business Entity

How many important business entity are there in a enterprice? for eaxample, Banking:

  • Customer
  • Account
  • Product
  • Transaction
  • Branch
  • Employee
  • Loan
  • Payment

Healthcare:

  • Patient
  • Provider
  • Hospital
  • Encounter
  • Diagnosis
  • Medication
  • LabResult
  • ClinicalDocument

These are Canonical Business Entities

2.2 Attributes

Each entity has its own attributes. for example,

customer, it may define:

  • customer_id
  • first_name
  • last_name
  • date_of_birth
  • email
  • phone
  • status

2.3 Relationships

Canonical Model has to define relationship between entity, e.g. a customer may has multiple account, so it is ” 1 … N “

Customer
│
├── Account 001
├── Account 002
└── Account 003

3. Data Type

Data types are another key component of the Canonical Model. For example, ‘Date of Birth’ must have a consistent data type across the system, rather than being defined as a Varchar one day and a Date type the next.

4. Business Meaning

Unique enterprise identifier representing a customer within the enterprise customer domain.

How does Canonical Model use in Lakehouse

                ┌──────────────┐
                │ CRM          │
                └──────┬───────┘
                       │
                ┌──────▼───────┐
                │ Banking      │
                └──────┬───────┘
                       │
                ┌──────▼───────┐
                │ ERP          │
                └──────┬───────┘
                       │
                       ▼
              ┌──────────────────┐
              │ Bronze           │
              │ Raw Source Data  │
              └────────┬─────────┘
                       │
                       ▼
              ┌──────────────────┐
              │ Standardization │
              │ + Mapping       │
              └────────┬─────────┘
                       │
                       ▼
              ┌──────────────────┐
              │ Canonical       │
              │ Customer        │
              └────────┬─────────┘
                       │
                       ▼
              ┌──────────────────┐
              │ Unified /        │
              │ Conformed Data   │
              └────────┬─────────┘
                       │
          ┌────────────┼────────────┐
          ▼            ▼            ▼
       Power BI       ML/AI       APIs

A complete example

Assuming, a enterprice has 3 system CRM, Banking and Insurance

CRM

CUST_ID
CUST_NAME
MOBILE_NO

Banking

CUSTOMER_NO
FULL_NAME
PHONE

Insurance

CLIENT_ID
NAME
TELEPHONE

Canonical:

Customer
├── customer_id
├── name
└── phone

Mapping:

CRM.CUST_ID    --->    Customer.customer_id

CRM.CUST_NAME  --->    Customer.name

CRM.MOBILE_NO  --->    Customer.phone

Bank:

Banking.CUSTOMER_NO
        ↓
Customer.customer_id

Insurance:

Insurance.CLIENT_ID
        ↓
Customer.customer_id

Implementation in Databricks Lakehouse

Assuming data looks this:

CUST_ID | CUST_NM       | MOBILE_NO
--------|---------------|------------
10001   | John Smith    | 416-111-1111
10002   | Mary Chen     | 416-222-2222

Bronze:

-- ============================================================
-- BRONZE LAYER
-- Purpose:
--   保留 CRM 原始数据。
--
-- Key point:
--   Bronze 不应该急着改变 Source System 的原始结构。
--   我们首先保证 source fidelity / traceability。
-- ============================================================

CREATE TABLE bronze.crm_customer
AS
SELECT
    *
FROM source.crm_customer;

Create Canonical Customer

Customer

-- ============================================================
-- SILVER / CANONICAL LAYER
--
-- Purpose:
--   将 CRM Source Model 转换成 Enterprise Canonical Model。
--
-- 这里最重要的工作不是简单 rename column。
-- 我们同时执行:
--
--   1. Schema standardization
--   2. Data type standardization
--   3. Business naming standardization
--   4. Data cleansing
--   5. Business rule application
-- ============================================================

CREATE OR REPLACE TABLE silver.canonical_customer AS

SELECT
    -- --------------------------------------------------------
    -- 将 CRM 的 CUST_ID 映射为企业统一的 customer_id。
    -- --------------------------------------------------------
    CAST(CUST_ID AS STRING) AS customer_id,

    -- --------------------------------------------------------
    -- 将 Source System 的 CUST_NM
    -- 转换成 Canonical Model 的 name。
    -- trim() 用于消除 source data 中多余空格。
    -- --------------------------------------------------------
    TRIM(CUST_NM) AS name,

    -- --------------------------------------------------------
    -- 统一电话号码格式。
    -- 实际企业项目中这里可能还会调用标准化规则。
    -- --------------------------------------------------------
    MOBILE_NO AS phone,

    -- --------------------------------------------------------
    -- 添加 source_system。
    -- 这个 metadata 非常重要:
    -- 后续可以追溯数据来自哪个系统。
    -- --------------------------------------------------------
    'CRM' AS source_system,

    -- --------------------------------------------------------
    -- 记录 ingestion / transformation 时间。
    -- 用于 lineage、audit 和 troubleshooting。
    -- --------------------------------------------------------
    current_timestamp() AS processed_at

FROM bronze.crm_customer;

result:
customer_id | name        | phone
------------|-------------|---------------
10001       | John Smith  | 416-111-1111
10002       | Mary Chen   | 416-222-2222

Banking

  • CUSTOMER_NO
  • FULL_NAME
  • PHONE
-- ============================================================
-- 将 Banking Source Model 转换为同一个 Canonical Model。
--
-- 注意:
-- CRM 和 Banking 的 Source Schema 完全不同。
-- 但是输出 Schema 必须遵循同一个 Canonical Contract。
-- ============================================================

SELECT
    CAST(CUSTOMER_NO AS STRING) AS customer_id,
    TRIM(FULL_NAME) AS name,
    PHONE AS phone,
    'BANKING' AS source_system,
    current_timestamp() AS processed_at
FROM bronze.banking_customer;

Unified Customer

-- ============================================================
-- UNIFIED CUSTOMER
--
-- Purpose:
--   将不同 source systems 的 canonical records
--   汇聚成企业统一 Customer view。
--
-- 注意:
--   Canonicalization != Entity Resolution
--
--   Canonicalization:
--       把结构统一。
--
--   Entity Resolution:
--       判断两个 records 是否代表同一个真实 Customer。
-- ============================================================

CREATE OR REPLACE TABLE gold.unified_customer AS

SELECT
    customer_id,
    name,
    phone
FROM silver.canonical_customer_crm

UNION ALL

SELECT
    customer_id,
    name,
    phone
FROM silver.canonical_customer_banking;

Usually, in actual enterprise deployments, this stage usually includes the following additions:

Identity Resolution
Deduplication
Survivorship Rules
Master Data
Golden Record

Key Takeaways

Canonical Model answers: “When different systems talk about the same business concept, what should that concept look like and mean inside the enterprise?”

要点ENCN
Canonical ModelStandardized enterprise representation of data企业统一的数据表示方式
核心目的Reduce integration complexity降低系统集成复杂度
核心思想Different sources → common representation不同 Source → 统一表示
Business EntityCustomer, Account, Product, etc.业务实体
AttributesStandardized fields and meanings标准化字段及业务含义
RelationshipsDefines relationships between entities定义实体之间关系
Business SemanticsDefines what each field actually means定义字段真正的业务含义
Source ModelRepresents one source system某个源系统自己的数据模型
Canonical ModelEnterprise-standard representation企业标准数据模型
Data UnificationCombines data into a consistent enterprise view将数据统一成企业级一致视图
Master DataIdentifies and manages trusted business entities管理可信的核心业务实体
Data ContractDefines producer/consumer data agreement定义生产者与消费者之间的数据契约
DatabricksCanonical can be implemented using Delta tables/views可用 Delta Table/View 等实现
Canonical ≠ UnificationStructure standardization is not identity resolution结构统一不等于实体统一
Canonical + MetadataMetadata explains meaning, ownership, lineage, etc.Metadata 解释含义、Owner、Lineage 等
Canonical + DQCanonical fields can have standardized quality rulesCanonical 字段可以有统一质量规则
Canonical + GovernanceStandard model enables consistent governance标准模型有利于统一治理