model – mainri

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.

System	Customer ID	Customer Name	Phone
CRM	customer_id	customer_name	phone
Banking	CUST_NO	CUST_NM	MOBILE_NO
Insurance	client_id	full_name	telephone
ERP	BP_ID	BP_NAME	TEL_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?”

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