Doris Data Warehouse Builder

Standardized methodology for building Apache Doris data warehouses using MATERIALIZED VIEW for data modeling and layering.

When to Use

Starting a new Doris data warehouse project
Adding new business domains or tables to an existing DW
Reviewing DW architecture design
Generating deployment scripts and documentation
Onboarding new team members to DW conventions

1. Architecture Overview

1.1 Four-Layer Architecture

┌─────────────────────────────────────────────────────────────┐
│  ODS (贴源层) - Managed by CDC, NOT modified by deployment │
│  Raw tables mirroring business system, 66 tables           │
│  Naming: original table name (no prefix)                   │
├─────────────────────────────────────────────────────────────┤
│  DIM (维度层) - MATERIALIZED VIEW                          │
│  Flattened star schema, no snowflake                       │
│  Naming: dim_xxx                                            │
├─────────────────────────────────────────────────────────────┤
│  DWD (明细层) - MATERIALIZED VIEW                          │
│  Cleaned single-business transaction details               │
│  Naming: dwd_xxx                                            │
├─────────────────────────────────────────────────────────────┤
│  DWS (汇总层) - VIEW + MATERIALIZED VIEW hybrid            │
│  Multi-dimensional aggregated wide tables                  │
│  Naming: dws_xxx_daily_wide / dws_xxx_daily_mv             │
└─────────────────────────────────────────────────────────────┘

1.2 Technology Stack

| Component | Technology | Purpose | |-----------|-----------|---------| | Storage Engine | Apache Doris | OLAP columnar storage | | DIM Layer | MATERIALIZED VIEW | Auto-refreshing dimension tables | | DWD Layer | MATERIALIZED VIEW | Auto-refreshing detail tables | | DWS Layer | VIEW + MATERIALIZED VIEW | Logical views + pre-aggregated MVs | | Dimension JOIN | [broadcast] hint | Small table broadcast optimization | | Colocation | colocate_with='xxx' | Co-located JOIN optimization |

1.3 Key Design Principles

DIM/DWD use MATERIALIZED VIEW - auto-refresh, pre-computed
DWS uses VIEW - zero storage, real-time reflection of underlying data
DWD: single business domain - no cross-business JOIN
DWS: wide tables - multi-dimensional aggregation, support 80% of ad-hoc analysis
No redundant fields - eliminate *DateKey, use DATEV2 directly
All fields have Chinese comments - -- field comment
camelCase naming - matching ODS source field casing

2. Naming Conventions

2.1 Object Naming

| Layer | Prefix | Example | Type | |-------|--------|---------|------| | ODS | (none) | patient, chargeorder | TABLE | | DIM | dim_ | dim_office, dim_patient | MATERIALIZED VIEW | | DWD | dwd_ | dwd_chargeorderdetail | MATERIALIZED VIEW | | DWS | dws_ | dws_ops_daily_wide | VIEW or MATERIALIZED VIEW |

2.2 Field Naming

camelCase - match ODS source field casing (e.g., patientId, officeName)
ODS PascalCase → DIM/DWD camelCase (e.g., PatientId → patientId)
Avoid underscores - use camelCase instead (e.g., sourceTypeLevel1, not source_type_level1)
Prefix exception: isExcludedFromStats (boolean prefix)

2.3 File Naming

| File | Content | |------|---------| | 00_build_ods_all.sql | ODS table DDLs (if not CDC-managed) | | 01_build_dim_all.sql | All DIM layer MATERIALIZED VIEWs | | 02_build_dwd.sql | All DWD layer MATERIALIZED VIEWs | | 03_build_dws.sql | All DWS layer VIEWs and MVs | | 05_acceptance_test.sql | Deployment acceptance tests | | 06_data_quality_check.sql | Data quality checks (run after CDC sync) | | 08_arch_fix.sql | Architecture fix overrides (last step) | | run_deploy.ps1 | PowerShell deployment script | | 一键部署.bat | Windows batch deployment script | | 部署指南_最终版.md | Deployment guide | | 数仓设计文档.md | Architecture design document | | 数仓使用文档.md | Usage guide and data dictionary |

3. Layer Design Rules

3.1 DIM Layer Rules

MATERIALIZED VIEW with BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY N MINUTE
Flattened star schema - no snowflake, all attributes in one table
Broadcast hint for small tables: LEFT JOIN [broadcast] dim_xxx
Chinese comments on every field
Exclude system fields: IsInactive, RecordCreatedTime, RecordUpdatedTime, RecordCreatedUser, RecordUpdatedTime, TenantId, RecordVersion
CamelCase fields, matching ODS source

Template:

DROP MATERIALIZED VIEW IF EXISTS dim_xxx;
CREATE MATERIALIZED VIEW dim_xxx (
    field1,  -- 字段1注释
    field2,  -- 字段2注释
    field3   -- 字段3注释
) BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 30 MINUTE
DISTRIBUTED BY HASH(id) BUCKETS AUTO
PROPERTIES (grace_period=300,replication_num=1) AS
SELECT ... FROM ods_table WHERE IsInactive=FALSE;

3.2 DWD Layer Rules

MATERIALIZED VIEW with BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 5 MINUTE
Single business domain - one DWD per business domain
No cross-business JOIN - DWD must not JOIN across different business domains
No DWD-to-DWD references - DWD only references ODS tables directly
Date filter - all DWD must include WHERE xxx >= '2024-01-01'
Office blacklist - PayOfficeId NOT IN (excluded offices) from dim_office logic
NULL-safe JOIN - use <=> for NULLable fields in JOIN conditions
Field-level Chinese comments - every field must have -- comment
Exclude system fields (same as DIM)
patientId type: INT (use CAST(PatientId AS INT) if ODS is bigint)
Colocation: DISTRIBUTED BY HASH(patientId) BUCKETS 10 + colocate_with='patient_group' for patient-related tables

3.3 DWS Layer Rules

VIEW for wide tables (zero storage, real-time)
MATERIALIZED VIEW only for frequently-queried aggregations (e.g., daily base stats)
Wide table design - multi-dimensional aggregation covering 80% of analysis needs
Dimension attributes materialized - JOIN dimension tables in the MV, not in the VIEW
NULLIF divisor protection - COALESCE(SUM(x) / NULLIF(SUM(y), 0), 0) to prevent division by zero
No composite indicators - value stratification, activity stratification belong to BI layer
UNION ALL + GROUP BY instead of FULL JOIN to avoid data inconsistency
Source table UNION instead of CROSS JOIN for skeleton tables to avoid zero-value empty rows
All subqueries must include statDate filtering for partition pruning
date fields use DATEV2 - no redundant *DateKey fields

4. Deployment Workflow

4.1 Deployment Steps (6 steps)

Step 1: 01_build_dim_all.sql   → DIM层 (MATERIALIZED VIEW)
Step 2: 02_build_dwd.sql       → DWD层 (MATERIALIZED VIEW)
Step 3: 03_build_dws.sql       → DWS层 (VIEW + MV)
Step 4: 08_arch_fix.sql        → 架构修正 (override DIM/DWS)
Step 5: 06_data_quality_check.sql → 数据质量检查
Step 6: 05_acceptance_test.sql → 验收测试 (12 checks)

4.2 Deployment Script Template

PowerShell (run_deploy.ps1):

$mysql = "D:\Program Files\mysql8\bin\mysql.exe"
$h = "192.168.2.112"
$P = "9030"
$u = "root"
$db = "delun_eky"
$dir = "D:\delun\deploy"
$ErrorActionPreference = "Stop"

function Run-Step {
    param($num, $name, $file)
    Write-Host "`n========== [$num/6] $name ==========" -ForegroundColor Cyan
    & $mysql -h $h -P $P -u $u $db -e "source $dir\$file" 2>&1
    if ($LASTEXITCODE -ne 0) {
        Write-Host "$name 失败!" -ForegroundColor Red
        exit 1
    }
    Write-Host "$name 完成" -ForegroundColor Green
}

Batch (一键部署.bat):

@echo off
set MYSQL_PATH=D:\Program Files\mysql8\bin\mysql.exe
set DB_HOST=192.168.2.112
set DB_PORT=9030
set DB_USER=root
set DB_NAME=delun_eky
set SQL_DIR=D:\delun\deploy

5. Best Practices

5.1 NULL Handling

| Scenario | Rule | Example | |----------|------|---------| | Source dimension NULL | Use COALESCE to set non-NULL default | COALESCE(p.SourceType,'') | | NULL-safe JOIN | Use <=> for NULLable fields | a.field <=> b.field | | Division by zero | Wrap divisor with NULLIF | SUM(x) / NULLIF(SUM(y), 0) | | LEFT JOIN no match | COALESCE to 0 for numeric | COALESCE(pay.amount, 0) |

5.2 JOIN Optimization

| Technique | When | Example | |-----------|------|---------| | [broadcast] hint | Small dimension table | LEFT JOIN [broadcast] dim_office o | | Colocation | Patient-related tables | colocate_with='patient_group' | | DISTRIBUTED BY HASH | Same distribution key | DISTRIBUTED BY HASH(patientId) BUCKETS 10 | | Partition pruning | Date-range queries | WHERE statDate >= '2024-01-01' |

5.3 Materialized View Configuration

-- DIM (low refresh frequency)
BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 30 MINUTE
DISTRIBUTED BY HASH(id) BUCKETS AUTO
PROPERTIES (grace_period=300,replication_num=1)

-- DWD (medium refresh frequency)
BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 5 MINUTE
DISTRIBUTED BY HASH(patientId) BUCKETS 10
PROPERTIES (grace_period=500,replication_num=1,"colocate_with"="patient_group")

-- DWS MV (medium refresh frequency)
BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 10 MINUTE
DISTRIBUTED BY HASH(statDate) BUCKETS AUTO
PROPERTIES (grace_period=600,replication_num=1)

5.4 Excluded System Fields

All DIM/DWD tables must exclude these fields (analysis-irrelevant):

IsInactive
RecordCreatedTime
RecordUpdatedTime
RecordCreatedUser
RecordUpdatedUser
TenantId
RecordVersion

6. SQL Template Examples

6.1 DIM Template

DROP MATERIALIZED VIEW IF EXISTS dim_xxx;
CREATE MATERIALIZED VIEW dim_xxx (
    id,        -- ID
    name,      -- 名称
    attr1,     -- 属性1
    attr2      -- 属性2
) BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 30 MINUTE
DISTRIBUTED BY HASH(id) BUCKETS AUTO
PROPERTIES (grace_period=300,replication_num=1) AS
SELECT Id,Name,Attr1,Attr2
FROM ods_xxx
WHERE IsInactive=FALSE;

6.2 DWD Template

DROP MATERIALIZED VIEW IF EXISTS dwd_xxx;
CREATE MATERIALIZED VIEW dwd_xxx (
    id,          -- ID
    patientId,   -- 患者ID
    bizDate,     -- 业务日期
    amount       -- 金额
) BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 5 MINUTE
DISTRIBUTED BY HASH(patientId) BUCKETS 10
PROPERTIES (grace_period=500,replication_num=1,"colocate_with"="patient_group") AS
SELECT Id,PatientId,DATEV2(BizDate),Amount
FROM ods_xxx
WHERE IsInactive=FALSE
    AND BizDate >= '2024-01-01';

6.3 DWS VIEW Template

DROP VIEW IF EXISTS dws_xxx_daily_wide;
CREATE VIEW dws_xxx_daily_wide (
    statDate,         -- 统计日期
    officeId,         -- 诊所ID
    sourceTypeLevel1, -- 来源一级
    metric1,          -- 指标1
    metric2           -- 指标2
) AS
SELECT t.statDate,t.officeId,t.sourceTypeLevel1,
    COALESCE(SUM(t.metric1),0),
    COALESCE(SUM(t.metric2),0)
FROM dws_base_daily_mv t
GROUP BY t.statDate,t.officeId,t.sourceTypeLevel1;

7. Output Requirements

7.1 Project Directory Structure

deploy/
├── 01_build_dim_all.sql       # DIM层
├── 02_build_dwd.sql           # DWD层
├── 03_build_dws.sql           # DWS层
├── 05_acceptance_test.sql     # 验收测试
├── 06_data_quality_check.sql  # 数据质量检查
├── 08_arch_fix.sql            # 架构修正
├── run_deploy.ps1             # 一键部署脚本
├── 一键部署.bat               # 一键部署批处理
├── 部署指南_最终版.md          # 部署指南
├── 数仓设计文档.md             # 设计文档
└── 数仓使用文档.md             # 使用文档

7.2 Documentation Requirements

| Document | Content | Version Tracking | |----------|---------|-----------------| | 部署指南_最终版.md | Environment requirements, file list, deployment steps, rollback plan, FAQ | Version + date in header | | 数仓设计文档.md | Architecture overview, layer design, table schemas, field definitions, data flow | Version in title | | 数仓使用文档.md | Usage guide, query examples, data dictionary, metrics definitions | Version in title |

7.3 Acceptance Test Checklist

Object count verification (ODS + DIM + DWD + DWS)
DIM layer camelCase naming check
Primary key existence check (e.g., patientId)
Dimension field naming check (e.g., sourceTypeDepartmentId)
DWS VIEW field naming (no __coalesce_ auto-generated names)
DWS core field naming (dimension attributes)
Medical insurance field distribution (ybk/yb)
Payment type flag fields (inCashFlowAmount, incomeAmount, etc.)
Colocation configuration (patient_group)
DWS wide table smoke test (COUNT(*))
DWS patientId reference consistency
Data sanity pre-check

8. Common Mistakes to Avoid

| Mistake | Consequence | Fix | |---------|------------|-----| | DWD-to-DWD reference | Refresh order dependency, circular reference risk | Inline ODS tables directly | | Cross-business JOIN in DWD | Doris scan data expansion, slow processing | Keep DWD single-business | | NULL source dimensions | Incorrect aggregation groups | COALESCE to empty string | | Division by zero | Infinity error in aggregation | NULLIF divisor | | Missing date filter | Full table scan, slow performance | Add WHERE date >= baseline | | Province/City PascalCase | Inconsistent naming convention | Use lowercase aliases | | Missing field comments | Poor maintainability | Add -- comment to every field | | FULL JOIN in DWS | Data inconsistency | Use UNION ALL + GROUP BY | | CROSS JOIN skeleton | Zero-value empty rows | UNION source tables with actual data |

9. Quick Start Checklist

[ ] Set up ODS tables (via CDC or manual DDL)
[ ] Create DIM tables (MATERIALIZED VIEW)
[ ] Create DWD tables (MATERIALIZED VIEW)
[ ] Create DWS views (VIEW + MV)
[ ] Run architecture fix (if needed)
[ ] Run data quality check
[ ] Run acceptance test (12 checks)
[ ] Update documentation versions
[ ] Verify object count: 66 ODS + 6 DIM + 28 DWD + 12 DWS