数据仓库基石：分层架构设计完全指南

数据仓库基石：分层架构设计完全指南

• • 1. 分层架构概述
  • • 1.1 什么是数据仓库分层？
    • 1.2 为什么要分层？
    • 1.3 分层架构全景图
  • 2. 数据仓库各层详解
  • • 2.1 ODS层：操作数据存储层
    • 2.2 DWD层：数据明细层
    • 2.3 DWS层：数据汇总层
    • 2.4 DM层：数据集市层
    • 2.5 各层数据流向图
  • 3. 各层对比总结
  • • 3.1 层次对比表
    • 3.2 数据冗余与查询性能关系
  • 4. 分层设计最佳实践
  • • 4.1 命名规范
    • 4.2 分层设计原则
    • 4.3 各层数据保留策略
    • 4.4 分层架构实施检查清单
  • 5. 实战案例：电商数仓分层设计
  • • 5.1 业务场景
    • 5.2 分层设计实施
    • 5.3 ETL调度依赖关系
  • 6. 常见问题与解决方案
  • 7. 结语

在数据仓库的建设过程中，分层架构（Layered Architecture）是最基础也最重要的设计理念。合理的分层不仅能让数据流向清晰可控，还能有效提升数据质量、降低维护成本。本文将详细阐述数据仓库的经典分层架构、各层的职责定位以及设计最佳实践，帮助读者构建规范、高效的数据仓库体系。

1. 分层架构概述

1.1 什么是数据仓库分层？

数据仓库分层是指将数据从源系统到最终应用的处理过程划分为多个逻辑层次，每个层次承担特定的职责，数据按照"逐层流转、逐层加工"的原则在各层之间流动。

核心思想：高内聚、低耦合——每层只做自己该做的事。

1.2 为什么要分层？

1.3 分层架构全景图

#mermaid-svg-BVYEf5h3yp91Y3x6{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-BVYEf5h3yp91Y3x6 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-BVYEf5h3yp91Y3x6 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-BVYEf5h3yp91Y3x6 .error-icon{fill:#552222;}#mermaid-svg-BVYEf5h3yp91Y3x6 .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-BVYEf5h3yp91Y3x6 .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-BVYEf5h3yp91Y3x6 .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-BVYEf5h3yp91Y3x6 .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-BVYEf5h3yp91Y3x6 .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-BVYEf5h3yp91Y3x6 .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-BVYEf5h3yp91Y3x6 .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-BVYEf5h3yp91Y3x6 .marker{fill:#333333;stroke:#333333;}#mermaid-svg-BVYEf5h3yp91Y3x6 .marker.cross{stroke:#333333;}#mermaid-svg-BVYEf5h3yp91Y3x6 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-BVYEf5h3yp91Y3x6 p{margin:0;}#mermaid-svg-BVYEf5h3yp91Y3x6 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-BVYEf5h3yp91Y3x6 .cluster-label text{fill:#333;}#mermaid-svg-BVYEf5h3yp91Y3x6 .cluster-label span{color:#333;}#mermaid-svg-BVYEf5h3yp91Y3x6 .cluster-label span p{background-color:transparent;}#mermaid-svg-BVYEf5h3yp91Y3x6 .label text,#mermaid-svg-BVYEf5h3yp91Y3x6 span{fill:#333;color:#333;}#mermaid-svg-BVYEf5h3yp91Y3x6 .node rect,#mermaid-svg-BVYEf5h3yp91Y3x6 .node circle,#mermaid-svg-BVYEf5h3yp91Y3x6 .node ellipse,#mermaid-svg-BVYEf5h3yp91Y3x6 .node polygon,#mermaid-svg-BVYEf5h3yp91Y3x6 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-BVYEf5h3yp91Y3x6 .rough-node .label text,#mermaid-svg-BVYEf5h3yp91Y3x6 .node .label text,#mermaid-svg-BVYEf5h3yp91Y3x6 .image-shape .label,#mermaid-svg-BVYEf5h3yp91Y3x6 .icon-shape .label{text-anchor:middle;}#mermaid-svg-BVYEf5h3yp91Y3x6 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-BVYEf5h3yp91Y3x6 .rough-node .label,#mermaid-svg-BVYEf5h3yp91Y3x6 .node .label,#mermaid-svg-BVYEf5h3yp91Y3x6 .image-shape .label,#mermaid-svg-BVYEf5h3yp91Y3x6 .icon-shape .label{text-align:center;}#mermaid-svg-BVYEf5h3yp91Y3x6 .node.clickable{cursor:pointer;}#mermaid-svg-BVYEf5h3yp91Y3x6 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-BVYEf5h3yp91Y3x6 .arrowheadPath{fill:#333333;}#mermaid-svg-BVYEf5h3yp91Y3x6 .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-BVYEf5h3yp91Y3x6 .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-BVYEf5h3yp91Y3x6 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-BVYEf5h3yp91Y3x6 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-BVYEf5h3yp91Y3x6 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-BVYEf5h3yp91Y3x6 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-BVYEf5h3yp91Y3x6 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-BVYEf5h3yp91Y3x6 .cluster text{fill:#333;}#mermaid-svg-BVYEf5h3yp91Y3x6 .cluster span{color:#333;}#mermaid-svg-BVYEf5h3yp91Y3x6 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-BVYEf5h3yp91Y3x6 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-BVYEf5h3yp91Y3x6 rect.text{fill:none;stroke-width:0;}#mermaid-svg-BVYEf5h3yp91Y3x6 .icon-shape,#mermaid-svg-BVYEf5h3yp91Y3x6 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-BVYEf5h3yp91Y3x6 .icon-shape p,#mermaid-svg-BVYEf5h3yp91Y3x6 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-BVYEf5h3yp91Y3x6 .icon-shape .label rect,#mermaid-svg-BVYEf5h3yp91Y3x6 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-BVYEf5h3yp91Y3x6 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-BVYEf5h3yp91Y3x6 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-BVYEf5h3yp91Y3x6 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

应用层

DM层

DWS层

DWD层

ODS层

数据源层

业务数据库MySQL/Oracle

日志文件Nginx/App Log

外部API第三方数据

消息队列Kafka/RocketMQ

操作数据存储层Operational Data Store

原始数据无修改

近实时同步

数据明细层Data Warehouse Detail

数据清洗

维度退化

数据标准化

数据汇总层Data Warehouse Summary

轻度汇总

日/周/月聚合

数据集市层Data Mart

主题域

业务专用

BI报表Tableau/FineBI

数据产品推荐/画像

即席查询Ad-hoc Query

数据服务API

2. 数据仓库各层详解

2.1 ODS层：操作数据存储层

ODS（Operational Data Store） 是数据仓库的最底层，负责从各业务源系统抽取原始数据，保持与源系统基本一致的数据结构。

核心职责：

• 数据接入：从源系统抽取数据
• 数据存储：保留原始数据，不做修改
• 数据追溯：提供数据回溯能力
• 增量/全量：支持多种同步策略

表命名规范：

— ODS层表命名示例
ods_order_info_di — 订单信息表（日增量）
ods_user_info_df — 用户信息表（日全量）
ods_product_info_hi — 产品信息表（小时增量）

— 命名规则：ods_{业务主题}_{更新周期}
— di: day increment (日增量)
— df: day full (日全量)
— hi: hour increment (小时增量)

ODS表示例：

CREATE TABLE ods_order_info_di (
— 业务字段（与源系统保持一致）
order_id BIGINT COMMENT '订单ID',
user_id BIGINT COMMENT '用户ID',
product_id BIGINT COMMENT '产品ID',
order_amount DECIMAL(10,2) COMMENT '订单金额',
order_status TINYINT COMMENT '订单状态',
create_time DATETIME COMMENT '创建时间',
update_time DATETIME COMMENT '更新时间',

— ODS层附加字段
etl_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'ETL加载时间',
etl_date DATE COMMENT 'ETL加载日期',
data_source VARCHAR(50) COMMENT '数据来源',
is_deleted TINYINT DEFAULT 0 COMMENT '逻辑删除标记'
) COMMENT 'ODS层-订单信息表'
PARTITION BY RANGE (etl_date);

2.2 DWD层：数据明细层

DWD（Data Warehouse Detail） 是数据仓库的明细数据层，对ODS层数据进行清洗、标准化、维度退化等处理，形成统一、规范的明细数据。

核心职责：

• 数据清洗：去除脏数据、处理空值
• 数据标准化：统一编码、单位、格式
• 维度退化：将维度属性退化到事实表
• 数据整合：多源数据合并
• 数据脱敏：敏感信息处理

DWD层处理流程：

#mermaid-svg-ZC286iMjWndrtJx4{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-ZC286iMjWndrtJx4 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-ZC286iMjWndrtJx4 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-ZC286iMjWndrtJx4 .error-icon{fill:#552222;}#mermaid-svg-ZC286iMjWndrtJx4 .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-ZC286iMjWndrtJx4 .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-ZC286iMjWndrtJx4 .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-ZC286iMjWndrtJx4 .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-ZC286iMjWndrtJx4 .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-ZC286iMjWndrtJx4 .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-ZC286iMjWndrtJx4 .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-ZC286iMjWndrtJx4 .marker{fill:#333333;stroke:#333333;}#mermaid-svg-ZC286iMjWndrtJx4 .marker.cross{stroke:#333333;}#mermaid-svg-ZC286iMjWndrtJx4 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-ZC286iMjWndrtJx4 p{margin:0;}#mermaid-svg-ZC286iMjWndrtJx4 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-ZC286iMjWndrtJx4 .cluster-label text{fill:#333;}#mermaid-svg-ZC286iMjWndrtJx4 .cluster-label span{color:#333;}#mermaid-svg-ZC286iMjWndrtJx4 .cluster-label span p{background-color:transparent;}#mermaid-svg-ZC286iMjWndrtJx4 .label text,#mermaid-svg-ZC286iMjWndrtJx4 span{fill:#333;color:#333;}#mermaid-svg-ZC286iMjWndrtJx4 .node rect,#mermaid-svg-ZC286iMjWndrtJx4 .node circle,#mermaid-svg-ZC286iMjWndrtJx4 .node ellipse,#mermaid-svg-ZC286iMjWndrtJx4 .node polygon,#mermaid-svg-ZC286iMjWndrtJx4 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-ZC286iMjWndrtJx4 .rough-node .label text,#mermaid-svg-ZC286iMjWndrtJx4 .node .label text,#mermaid-svg-ZC286iMjWndrtJx4 .image-shape .label,#mermaid-svg-ZC286iMjWndrtJx4 .icon-shape .label{text-anchor:middle;}#mermaid-svg-ZC286iMjWndrtJx4 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-ZC286iMjWndrtJx4 .rough-node .label,#mermaid-svg-ZC286iMjWndrtJx4 .node .label,#mermaid-svg-ZC286iMjWndrtJx4 .image-shape .label,#mermaid-svg-ZC286iMjWndrtJx4 .icon-shape .label{text-align:center;}#mermaid-svg-ZC286iMjWndrtJx4 .node.clickable{cursor:pointer;}#mermaid-svg-ZC286iMjWndrtJx4 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-ZC286iMjWndrtJx4 .arrowheadPath{fill:#333333;}#mermaid-svg-ZC286iMjWndrtJx4 .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-ZC286iMjWndrtJx4 .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-ZC286iMjWndrtJx4 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-ZC286iMjWndrtJx4 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-ZC286iMjWndrtJx4 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-ZC286iMjWndrtJx4 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-ZC286iMjWndrtJx4 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-ZC286iMjWndrtJx4 .cluster text{fill:#333;}#mermaid-svg-ZC286iMjWndrtJx4 .cluster span{color:#333;}#mermaid-svg-ZC286iMjWndrtJx4 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-ZC286iMjWndrtJx4 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-ZC286iMjWndrtJx4 rect.text{fill:none;stroke-width:0;}#mermaid-svg-ZC286iMjWndrtJx4 .icon-shape,#mermaid-svg-ZC286iMjWndrtJx4 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-ZC286iMjWndrtJx4 .icon-shape p,#mermaid-svg-ZC286iMjWndrtJx4 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-ZC286iMjWndrtJx4 .icon-shape .label rect,#mermaid-svg-ZC286iMjWndrtJx4 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-ZC286iMjWndrtJx4 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-ZC286iMjWndrtJx4 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-ZC286iMjWndrtJx4 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

输出

DWD处理

输入

ODS订单表

ODS用户表

ODS产品表

数据清洗空值/异常值

标准化统一编码

关联补全维表退化

格式转换类型统一

dwd_order_detail明细事实表

DWD表示例：

CREATE TABLE dwd_order_detail_di (
— 事实字段
order_id BIGINT COMMENT '订单ID',
user_id BIGINT COMMENT '用户ID',
product_id BIGINT COMMENT '产品ID',
order_amount DECIMAL(10,2) COMMENT '订单金额',
actual_amount DECIMAL(10,2) COMMENT '实付金额',
discount_amount DECIMAL(10,2) COMMENT '优惠金额',
order_status TINYINT COMMENT '订单状态',

— 退化维度字段
user_name VARCHAR(100) COMMENT '用户名',
user_level VARCHAR(20) COMMENT '用户等级',
product_name VARCHAR(200) COMMENT '产品名称',
category_id INT COMMENT '品类ID',
category_name VARCHAR(100) COMMENT '品类名称',

— 时间维度
order_date DATE COMMENT '订单日期',
order_hour TINYINT COMMENT '订单小时',
create_time DATETIME COMMENT '创建时间',

— ETL字段
etl_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
etl_date DATE COMMENT 'ETL日期'
) COMMENT 'DWD层-订单明细表'
PARTITION BY RANGE (order_date);

2.3 DWS层：数据汇总层

DWS（Data Warehouse Summary） 是数据仓库的汇总数据层，基于DWD层按照业务所需的维度进行轻度汇总，形成面向主题的宽表。

核心职责：

• 轻度汇总：按天/周/月进行预聚合
• 宽表构建：将多个事实表关联形成宽表
• 指标计算：预计算常用指标
• 性能优化：减少上层计算量

DWS层粒度设计：

#mermaid-svg-XcOSi9sA0HekyNAL{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-XcOSi9sA0HekyNAL .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-XcOSi9sA0HekyNAL .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-XcOSi9sA0HekyNAL .error-icon{fill:#552222;}#mermaid-svg-XcOSi9sA0HekyNAL .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-XcOSi9sA0HekyNAL .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-XcOSi9sA0HekyNAL .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-XcOSi9sA0HekyNAL .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-XcOSi9sA0HekyNAL .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-XcOSi9sA0HekyNAL .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-XcOSi9sA0HekyNAL .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-XcOSi9sA0HekyNAL .marker{fill:#333333;stroke:#333333;}#mermaid-svg-XcOSi9sA0HekyNAL .marker.cross{stroke:#333333;}#mermaid-svg-XcOSi9sA0HekyNAL svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-XcOSi9sA0HekyNAL p{margin:0;}#mermaid-svg-XcOSi9sA0HekyNAL .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-XcOSi9sA0HekyNAL .cluster-label text{fill:#333;}#mermaid-svg-XcOSi9sA0HekyNAL .cluster-label span{color:#333;}#mermaid-svg-XcOSi9sA0HekyNAL .cluster-label span p{background-color:transparent;}#mermaid-svg-XcOSi9sA0HekyNAL .label text,#mermaid-svg-XcOSi9sA0HekyNAL span{fill:#333;color:#333;}#mermaid-svg-XcOSi9sA0HekyNAL .node rect,#mermaid-svg-XcOSi9sA0HekyNAL .node circle,#mermaid-svg-XcOSi9sA0HekyNAL .node ellipse,#mermaid-svg-XcOSi9sA0HekyNAL .node polygon,#mermaid-svg-XcOSi9sA0HekyNAL .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-XcOSi9sA0HekyNAL .rough-node .label text,#mermaid-svg-XcOSi9sA0HekyNAL .node .label text,#mermaid-svg-XcOSi9sA0HekyNAL .image-shape .label,#mermaid-svg-XcOSi9sA0HekyNAL .icon-shape .label{text-anchor:middle;}#mermaid-svg-XcOSi9sA0HekyNAL .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-XcOSi9sA0HekyNAL .rough-node .label,#mermaid-svg-XcOSi9sA0HekyNAL .node .label,#mermaid-svg-XcOSi9sA0HekyNAL .image-shape .label,#mermaid-svg-XcOSi9sA0HekyNAL .icon-shape .label{text-align:center;}#mermaid-svg-XcOSi9sA0HekyNAL .node.clickable{cursor:pointer;}#mermaid-svg-XcOSi9sA0HekyNAL .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-XcOSi9sA0HekyNAL .arrowheadPath{fill:#333333;}#mermaid-svg-XcOSi9sA0HekyNAL .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-XcOSi9sA0HekyNAL .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-XcOSi9sA0HekyNAL .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-XcOSi9sA0HekyNAL .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-XcOSi9sA0HekyNAL .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-XcOSi9sA0HekyNAL .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-XcOSi9sA0HekyNAL .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-XcOSi9sA0HekyNAL .cluster text{fill:#333;}#mermaid-svg-XcOSi9sA0HekyNAL .cluster span{color:#333;}#mermaid-svg-XcOSi9sA0HekyNAL div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-XcOSi9sA0HekyNAL .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-XcOSi9sA0HekyNAL rect.text{fill:none;stroke-width:0;}#mermaid-svg-XcOSi9sA0HekyNAL .icon-shape,#mermaid-svg-XcOSi9sA0HekyNAL .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-XcOSi9sA0HekyNAL .icon-shape p,#mermaid-svg-XcOSi9sA0HekyNAL .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-XcOSi9sA0HekyNAL .icon-shape .label rect,#mermaid-svg-XcOSi9sA0HekyNAL .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-XcOSi9sA0HekyNAL .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-XcOSi9sA0HekyNAL .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-XcOSi9sA0HekyNAL :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

DWS汇总层

DWD明细层

dwd_order_detail订单粒度

dwd_user_behavior行为粒度

dwd_refund_detail退款粒度

dws_user_order_day用户-日汇总

dws_product_order_day产品-日汇总

dws_region_order_day地区-日汇总

dws_category_order_month品类-月汇总

DWS表示例：

— 用户-日汇总表
CREATE TABLE dws_user_order_day_di (
— 维度字段
stat_date DATE COMMENT '统计日期',
user_id BIGINT COMMENT '用户ID',
user_level VARCHAR(20) COMMENT '用户等级',
user_register_date DATE COMMENT '注册日期',

— 订单指标
order_count INT COMMENT '订单数量',
order_total_amount DECIMAL(15,2) COMMENT '订单总金额',
order_avg_amount DECIMAL(10,2) COMMENT '平均订单金额',
max_order_amount DECIMAL(10,2) COMMENT '最大订单金额',

— 商品指标
sku_count INT COMMENT '购买商品种类数',
total_quantity INT COMMENT '购买商品总数',

— 优惠指标
total_discount DECIMAL(10,2) COMMENT '总优惠金额',
coupon_used_count INT COMMENT '使用优惠券次数',

— 行为指标
login_count INT COMMENT '登录次数',
browse_count INT COMMENT '浏览商品次数',

PRIMARY KEY (stat_date, user_id)
) COMMENT 'DWS层-用户日汇总表'
PARTITION BY RANGE (stat_date);

2.4 DM层：数据集市层

DM（Data Mart） 是面向具体业务场景的数据集市层，根据业务部门的特定需求进行定制化的数据加工。

核心职责：

• 业务定制：满足具体业务分析需求
• 跨域整合：整合多个DWS/DWD表
• 报表加速：预计算复杂报表逻辑
• 接口统一：为应用层提供统一数据接口

DM层特点：

DM表示例：

— 销售主题-日报表
CREATE TABLE dm_sales_daily_report (
report_date DATE PRIMARY KEY COMMENT '报表日期',

— 整体指标
gmv DECIMAL(15,2) COMMENT 'GMV',
order_count INT COMMENT '订单数',
order_user_count INT COMMENT '下单用户数',
payment_amount DECIMAL(15,2) COMMENT '支付金额',
payment_rate DECIMAL(5,2) COMMENT '支付转化率',

— 渠道指标
app_gmv DECIMAL(15,2) COMMENT 'App端GMV',
h5_gmv DECIMAL(15,2) COMMENT 'H5端GMV',
pc_gmv DECIMAL(15,2) COMMENT 'PC端GMV',

— 品类指标
top3_category VARCHAR(200) COMMENT 'TOP3品类',
top3_category_amount DECIMAL(15,2) COMMENT 'TOP3品类金额',

— 用户指标
new_user_count INT COMMENT '新用户数',
new_user_order_count INT COMMENT '新用户下单数',
old_user_order_count INT COMMENT '老用户下单数',

— 时效指标
avg_delivery_hours DECIMAL(5,2) COMMENT '平均配送时长',

etl_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) COMMENT 'DM层-销售日报表';

2.5 各层数据流向图

#mermaid-svg-HHXWd7ShHNEWPRbK{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-HHXWd7ShHNEWPRbK .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-HHXWd7ShHNEWPRbK .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-HHXWd7ShHNEWPRbK .error-icon{fill:#552222;}#mermaid-svg-HHXWd7ShHNEWPRbK .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-HHXWd7ShHNEWPRbK .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-HHXWd7ShHNEWPRbK .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-HHXWd7ShHNEWPRbK .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-HHXWd7ShHNEWPRbK .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-HHXWd7ShHNEWPRbK .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-HHXWd7ShHNEWPRbK .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-HHXWd7ShHNEWPRbK .marker{fill:#333333;stroke:#333333;}#mermaid-svg-HHXWd7ShHNEWPRbK .marker.cross{stroke:#333333;}#mermaid-svg-HHXWd7ShHNEWPRbK svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-HHXWd7ShHNEWPRbK p{margin:0;}#mermaid-svg-HHXWd7ShHNEWPRbK .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-HHXWd7ShHNEWPRbK .cluster-label text{fill:#333;}#mermaid-svg-HHXWd7ShHNEWPRbK .cluster-label span{color:#333;}#mermaid-svg-HHXWd7ShHNEWPRbK .cluster-label span p{background-color:transparent;}#mermaid-svg-HHXWd7ShHNEWPRbK .label text,#mermaid-svg-HHXWd7ShHNEWPRbK span{fill:#333;color:#333;}#mermaid-svg-HHXWd7ShHNEWPRbK .node rect,#mermaid-svg-HHXWd7ShHNEWPRbK .node circle,#mermaid-svg-HHXWd7ShHNEWPRbK .node ellipse,#mermaid-svg-HHXWd7ShHNEWPRbK .node polygon,#mermaid-svg-HHXWd7ShHNEWPRbK .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-HHXWd7ShHNEWPRbK .rough-node .label text,#mermaid-svg-HHXWd7ShHNEWPRbK .node .label text,#mermaid-svg-HHXWd7ShHNEWPRbK .image-shape .label,#mermaid-svg-HHXWd7ShHNEWPRbK .icon-shape .label{text-anchor:middle;}#mermaid-svg-HHXWd7ShHNEWPRbK .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-HHXWd7ShHNEWPRbK .rough-node .label,#mermaid-svg-HHXWd7ShHNEWPRbK .node .label,#mermaid-svg-HHXWd7ShHNEWPRbK .image-shape .label,#mermaid-svg-HHXWd7ShHNEWPRbK .icon-shape .label{text-align:center;}#mermaid-svg-HHXWd7ShHNEWPRbK .node.clickable{cursor:pointer;}#mermaid-svg-HHXWd7ShHNEWPRbK .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-HHXWd7ShHNEWPRbK .arrowheadPath{fill:#333333;}#mermaid-svg-HHXWd7ShHNEWPRbK .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-HHXWd7ShHNEWPRbK .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-HHXWd7ShHNEWPRbK .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-HHXWd7ShHNEWPRbK .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-HHXWd7ShHNEWPRbK .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-HHXWd7ShHNEWPRbK .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-HHXWd7ShHNEWPRbK .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-HHXWd7ShHNEWPRbK .cluster text{fill:#333;}#mermaid-svg-HHXWd7ShHNEWPRbK .cluster span{color:#333;}#mermaid-svg-HHXWd7ShHNEWPRbK div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-HHXWd7ShHNEWPRbK .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-HHXWd7ShHNEWPRbK rect.text{fill:none;stroke-width:0;}#mermaid-svg-HHXWd7ShHNEWPRbK .icon-shape,#mermaid-svg-HHXWd7ShHNEWPRbK .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-HHXWd7ShHNEWPRbK .icon-shape p,#mermaid-svg-HHXWd7ShHNEWPRbK .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-HHXWd7ShHNEWPRbK .icon-shape .label rect,#mermaid-svg-HHXWd7ShHNEWPRbK .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-HHXWd7ShHNEWPRbK .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-HHXWd7ShHNEWPRbK .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-HHXWd7ShHNEWPRbK :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

应用层

DM层

DWS层

DWD层

ODS层

ods_order_info

ods_user_info

ods_product_info

dwd_order_detail

dwd_user_behavior

dws_user_order_day

dws_product_order_day

dws_region_order_day

dm_sales_report

dm_user_report

dm_product_report

BI看板

数据API

即席查询

3. 各层对比总结

3.1 层次对比表

3.2 数据冗余与查询性能关系

#mermaid-svg-9BnWbkb2BKRGZk7p{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-9BnWbkb2BKRGZk7p .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-9BnWbkb2BKRGZk7p .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-9BnWbkb2BKRGZk7p .error-icon{fill:#552222;}#mermaid-svg-9BnWbkb2BKRGZk7p .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-9BnWbkb2BKRGZk7p .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-9BnWbkb2BKRGZk7p .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-9BnWbkb2BKRGZk7p .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-9BnWbkb2BKRGZk7p .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-9BnWbkb2BKRGZk7p .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-9BnWbkb2BKRGZk7p .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-9BnWbkb2BKRGZk7p .marker{fill:#333333;stroke:#333333;}#mermaid-svg-9BnWbkb2BKRGZk7p .marker.cross{stroke:#333333;}#mermaid-svg-9BnWbkb2BKRGZk7p svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-9BnWbkb2BKRGZk7p p{margin:0;}#mermaid-svg-9BnWbkb2BKRGZk7p .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-9BnWbkb2BKRGZk7p .cluster-label text{fill:#333;}#mermaid-svg-9BnWbkb2BKRGZk7p .cluster-label span{color:#333;}#mermaid-svg-9BnWbkb2BKRGZk7p .cluster-label span p{background-color:transparent;}#mermaid-svg-9BnWbkb2BKRGZk7p .label text,#mermaid-svg-9BnWbkb2BKRGZk7p span{fill:#333;color:#333;}#mermaid-svg-9BnWbkb2BKRGZk7p .node rect,#mermaid-svg-9BnWbkb2BKRGZk7p .node circle,#mermaid-svg-9BnWbkb2BKRGZk7p .node ellipse,#mermaid-svg-9BnWbkb2BKRGZk7p .node polygon,#mermaid-svg-9BnWbkb2BKRGZk7p .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-9BnWbkb2BKRGZk7p .rough-node .label text,#mermaid-svg-9BnWbkb2BKRGZk7p .node .label text,#mermaid-svg-9BnWbkb2BKRGZk7p .image-shape .label,#mermaid-svg-9BnWbkb2BKRGZk7p .icon-shape .label{text-anchor:middle;}#mermaid-svg-9BnWbkb2BKRGZk7p .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-9BnWbkb2BKRGZk7p .rough-node .label,#mermaid-svg-9BnWbkb2BKRGZk7p .node .label,#mermaid-svg-9BnWbkb2BKRGZk7p .image-shape .label,#mermaid-svg-9BnWbkb2BKRGZk7p .icon-shape .label{text-align:center;}#mermaid-svg-9BnWbkb2BKRGZk7p .node.clickable{cursor:pointer;}#mermaid-svg-9BnWbkb2BKRGZk7p .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-9BnWbkb2BKRGZk7p .arrowheadPath{fill:#333333;}#mermaid-svg-9BnWbkb2BKRGZk7p .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-9BnWbkb2BKRGZk7p .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-9BnWbkb2BKRGZk7p .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-9BnWbkb2BKRGZk7p .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-9BnWbkb2BKRGZk7p .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-9BnWbkb2BKRGZk7p .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-9BnWbkb2BKRGZk7p .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-9BnWbkb2BKRGZk7p .cluster text{fill:#333;}#mermaid-svg-9BnWbkb2BKRGZk7p .cluster span{color:#333;}#mermaid-svg-9BnWbkb2BKRGZk7p div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-9BnWbkb2BKRGZk7p .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-9BnWbkb2BKRGZk7p rect.text{fill:none;stroke-width:0;}#mermaid-svg-9BnWbkb2BKRGZk7p .icon-shape,#mermaid-svg-9BnWbkb2BKRGZk7p .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-9BnWbkb2BKRGZk7p .icon-shape p,#mermaid-svg-9BnWbkb2BKRGZk7p .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-9BnWbkb2BKRGZk7p .icon-shape .label rect,#mermaid-svg-9BnWbkb2BKRGZk7p .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-9BnWbkb2BKRGZk7p .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-9BnWbkb2BKRGZk7p .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-9BnWbkb2BKRGZk7p :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

查询性能

ODS慢

DWD中

DWS快

DM极快

存储成本

ODS低冗余

DWD中冗余

DWS高冗余

DM极高冗余

4. 分层设计最佳实践

4.1 命名规范

— 统一命名规范示例
— {分层标识}_{业务主题}_{表类型}_{更新周期}

— ODS层
ods_order_info_di — 订单信息表，日增量
ods_user_info_df — 用户信息表，日全量

— DWD层
dwd_order_detail_di — 订单明细表，日增量
dwd_user_behavior_di — 用户行为表，日增量

— DWS层
dws_user_order_day_di — 用户订单日汇总
dws_product_sale_month_di — 产品销售月汇总

— DM层
dm_sales_daily_report — 销售日报
dm_user_portrait — 用户画像

4.2 分层设计原则

4.3 各层数据保留策略

— 数据生命周期管理策略
CREATE TABLE data_retention_policy (
layer_name VARCHAR(10),
retention_days INT,
storage_tier VARCHAR(20),
compression VARCHAR(10),
deletion_strategy VARCHAR(20)
);

INSERT INTO data_retention_policy VALUES
('ODS', 30, 'SSD', 'none', 'daily_delete'),
('DWD', 90, 'SSD', 'zstd', 'partition_drop'),
('DWS', 365, 'HDD', 'zstd', 'partition_drop'),
('DM', 1095, 'HDD', 'zstd', 'manual_archive');

4.4 分层架构实施检查清单

## 设计阶段
□ 是否定义了清晰的分层边界？
□ 各层的职责是否明确？
□ 命名规范是否统一？
□ 数据流向是否符合业务逻辑？

## 开发阶段
□ ODS层是否保留了原始数据？
□ DWD层是否完成了数据清洗和标准化？
□ DWS层是否选择了合适的汇总粒度？
□ DM层是否满足了业务需求？

## 运维阶段
□ 各层的数据质量监控是否到位？
□ 数据血缘关系是否清晰？
□ 数据生命周期管理是否执行？
□ 是否存在跨层依赖的违规情况？

5. 实战案例：电商数仓分层设计

5.1 业务场景

某电商平台需要建设数据仓库，支持以下业务需求：

• 运营日报（GMV、订单量、用户数）
• 用户画像分析
• 商品销售排行
• 实时大屏展示

5.2 分层设计实施

— ============================================
— 1. ODS层：原始数据接入
— ============================================

— 订单表（每日增量）
CREATE TABLE ods_order_info_di (
order_id BIGINT,
user_id BIGINT,
order_amount DECIMAL(10,2),
order_status TINYINT,
create_time DATETIME,
etl_date DATE
) PARTITION BY RANGE (etl_date);

— 用户表（每日全量）
CREATE TABLE ods_user_info_df (
user_id BIGINT,
user_name VARCHAR(100),
register_time DATETIME,
user_level TINYINT,
etl_date DATE
) PARTITION BY RANGE (etl_date);

— ============================================
— 2. DWD层：明细数据加工
— ============================================

— 订单明细表（关联用户信息）
CREATE TABLE dwd_order_detail_di (
order_id BIGINT,
user_id BIGINT,
user_name VARCHAR(100),
user_level TINYINT,
order_amount DECIMAL(10,2),
actual_amount DECIMAL(10,2),
order_status TINYINT,
order_date DATE,
create_time DATETIME,
etl_date DATE
) PARTITION BY RANGE (order_date);

— ============================================
— 3. DWS层：轻度汇总
— ============================================

— 用户日汇总表
CREATE TABLE dws_user_order_day_di (
stat_date DATE,
user_id BIGINT,
user_level TINYINT,
order_count INT,
order_total_amount DECIMAL(15,2),
etl_date DATE
) PARTITION BY RANGE (stat_date);

— ============================================
— 4. DM层：业务报表
— ============================================

— 运营日报表
CREATE TABLE dm_operation_daily_report (
report_date DATE,
gmv DECIMAL(15,2),
order_count INT,
order_user_count INT,
new_user_count INT,
avg_order_amount DECIMAL(10,2)
);

5.3 ETL调度依赖关系

#mermaid-svg-Sohkccl1DbNnWpjy{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-Sohkccl1DbNnWpjy .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-Sohkccl1DbNnWpjy .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-Sohkccl1DbNnWpjy .error-icon{fill:#552222;}#mermaid-svg-Sohkccl1DbNnWpjy .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-Sohkccl1DbNnWpjy .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-Sohkccl1DbNnWpjy .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-Sohkccl1DbNnWpjy .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-Sohkccl1DbNnWpjy .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-Sohkccl1DbNnWpjy .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-Sohkccl1DbNnWpjy .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-Sohkccl1DbNnWpjy .marker{fill:#333333;stroke:#333333;}#mermaid-svg-Sohkccl1DbNnWpjy .marker.cross{stroke:#333333;}#mermaid-svg-Sohkccl1DbNnWpjy svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-Sohkccl1DbNnWpjy p{margin:0;}#mermaid-svg-Sohkccl1DbNnWpjy .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-Sohkccl1DbNnWpjy .cluster-label text{fill:#333;}#mermaid-svg-Sohkccl1DbNnWpjy .cluster-label span{color:#333;}#mermaid-svg-Sohkccl1DbNnWpjy .cluster-label span p{background-color:transparent;}#mermaid-svg-Sohkccl1DbNnWpjy .label text,#mermaid-svg-Sohkccl1DbNnWpjy span{fill:#333;color:#333;}#mermaid-svg-Sohkccl1DbNnWpjy .node rect,#mermaid-svg-Sohkccl1DbNnWpjy .node circle,#mermaid-svg-Sohkccl1DbNnWpjy .node ellipse,#mermaid-svg-Sohkccl1DbNnWpjy .node polygon,#mermaid-svg-Sohkccl1DbNnWpjy .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-Sohkccl1DbNnWpjy .rough-node .label text,#mermaid-svg-Sohkccl1DbNnWpjy .node .label text,#mermaid-svg-Sohkccl1DbNnWpjy .image-shape .label,#mermaid-svg-Sohkccl1DbNnWpjy .icon-shape .label{text-anchor:middle;}#mermaid-svg-Sohkccl1DbNnWpjy .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-Sohkccl1DbNnWpjy .rough-node .label,#mermaid-svg-Sohkccl1DbNnWpjy .node .label,#mermaid-svg-Sohkccl1DbNnWpjy .image-shape .label,#mermaid-svg-Sohkccl1DbNnWpjy .icon-shape .label{text-align:center;}#mermaid-svg-Sohkccl1DbNnWpjy .node.clickable{cursor:pointer;}#mermaid-svg-Sohkccl1DbNnWpjy .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-Sohkccl1DbNnWpjy .arrowheadPath{fill:#333333;}#mermaid-svg-Sohkccl1DbNnWpjy .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-Sohkccl1DbNnWpjy .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-Sohkccl1DbNnWpjy .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-Sohkccl1DbNnWpjy .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-Sohkccl1DbNnWpjy .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-Sohkccl1DbNnWpjy .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-Sohkccl1DbNnWpjy .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-Sohkccl1DbNnWpjy .cluster text{fill:#333;}#mermaid-svg-Sohkccl1DbNnWpjy .cluster span{color:#333;}#mermaid-svg-Sohkccl1DbNnWpjy div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-Sohkccl1DbNnWpjy .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-Sohkccl1DbNnWpjy rect.text{fill:none;stroke-width:0;}#mermaid-svg-Sohkccl1DbNnWpjy .icon-shape,#mermaid-svg-Sohkccl1DbNnWpjy .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-Sohkccl1DbNnWpjy .icon-shape p,#mermaid-svg-Sohkccl1DbNnWpjy .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-Sohkccl1DbNnWpjy .icon-shape .label rect,#mermaid-svg-Sohkccl1DbNnWpjy .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-Sohkccl1DbNnWpjy .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-Sohkccl1DbNnWpjy .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-Sohkccl1DbNnWpjy :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

00:00 ODS同步

02:00 DWD加工

04:00 DWS汇总

04:30 实时任务

06:00 DM报表

实时大屏

08:00 日报推送

6. 常见问题与解决方案

7. 结语

数据仓库的分层架构是数据治理和数据质量的重要保障。合理的分层设计能够让数据流向清晰、职责明确、问题可追溯、系统可维护。

核心要点回顾：

设计原则：

掌握分层架构设计，是构建高质量、高可维护性数据仓库的基础。希望本文能为读者的数仓建设实践提供有价值的参考。