数据仓库建模方法论复盘：星型模型与宽表模型的取舍记录

数据仓库建模方法论复盘星型模型与宽表模型的取舍记录一、一个真实的建模困境上个月接了一个数据仓库迁移项目要把原来的 Hive 数仓迁移到 Doris 上。翻看原来的 DDL 时发现一个有趣的历史遗迹同一个业务域用户行为分析居然同时存在两套模型——一套是经典的星型模型1 张事实表 4 张维度表是数仓刚建的时候老同事设计的。另一套是去年业务方自己搞的宽表模型一张表 200 列所有维度全部打平据说是BI 工具跑得太慢干脆全展开。两套模型同时跑了一年多数据还不一致维护起来苦不堪言。这个项目的核心任务之一就是确定到底用哪种建模方式然后统一掉。这篇文章就来复盘一下决策过程的纠结和取舍。二、两套模型的 SQL 实现对比2.1 星型模型规范但慢星型模型的建表语句大概是这样的-- 星型模型维度表 -- 用户维度表缓慢变化维度SCD Type 2 CREATE TABLE dim_user ( user_sk BIGINT AUTO_INCREMENT COMMENT 用户代理键数仓内部使用, user_id VARCHAR(32) NOT NULL COMMENT 业务主键, user_name VARCHAR(64) COMMENT 用户昵称, age_group VARCHAR(16) COMMENT 年龄段0-18/19-30/31-45/46-60/60, city VARCHAR(32) COMMENT 所在城市, register_date DATE COMMENT 注册日期, membership_level VARCHAR(16) COMMENT 会员等级普通/银卡/金卡/钻石, -- SCD Type 2 字段 effective_date DATE COMMENT 生效日期, expire_date DATE DEFAULT 9999-12-31 COMMENT 失效日期, is_current TINYINT DEFAULT 1 COMMENT 是否当前记录, PRIMARY KEY (user_sk), INDEX idx_user_id (user_id), INDEX idx_current (is_current) ) COMMENT 用户维度表; -- 时间维度表 CREATE TABLE dim_date ( date_sk INT PRIMARY KEY COMMENT 日期代理键格式YYYYMMDD, full_date DATE NOT NULL COMMENT 完整日期, year INT COMMENT 年份, quarter INT COMMENT 季度, month INT COMMENT 月份, week_of_year INT COMMENT 一年中的第几周, day_of_week INT COMMENT 一周中的第几天1周一, is_weekend TINYINT COMMENT 是否周末, is_holiday TINYINT COMMENT 是否节假日 ) COMMENT 时间维度表; -- 事件维度表 CREATE TABLE dim_event ( event_id VARCHAR(32) PRIMARY KEY COMMENT 事件ID, event_name VARCHAR(64) COMMENT 事件名称, event_category VARCHAR(32) COMMENT 事件分类浏览/点击/下单/支付, page_name VARCHAR(128) COMMENT 所在页面 ) COMMENT 事件维度表; -- 星型模型事实表 CREATE TABLE fact_user_behavior ( event_sk BIGINT AUTO_INCREMENT COMMENT 事件代理键, user_sk BIGINT NOT NULL COMMENT 用户维度外键, date_sk INT NOT NULL COMMENT 日期维度外键, event_id VARCHAR(32) NOT NULL COMMENT 事件维度外键, -- 度量值事实 pv BIGINT DEFAULT 1 COMMENT 页面浏览量, duration_sec INT DEFAULT 0 COMMENT 停留时长秒, interaction_count INT DEFAULT 0 COMMENT 交互次数, amount DECIMAL(12, 2) DEFAULT 0 COMMENT 交易金额, -- 退化维度直接存在事实表中 session_id VARCHAR(64) COMMENT 会话ID, device_type VARCHAR(16) COMMENT 设备类型, PRIMARY KEY (event_sk), INDEX idx_date (date_sk), INDEX idx_user (user_sk), INDEX idx_event (event_id) ) COMMENT 用户行为事实表; -- 星型模型的查询写法JOIN很多 -- 需求查询2026年7月各年龄段的PV、UV和平均停留时长 SELECT u.age_group, d.month, COUNT(*) AS pv, COUNT(DISTINCT u.user_id) AS uv, AVG(f.duration_sec) AS avg_duration FROM fact_user_behavior f INNER JOIN dim_user u ON f.user_sk u.user_sk AND u.is_current 1 -- 只关联当前有效记录 INNER JOIN dim_date d ON f.date_sk d.date_sk WHERE d.year 2026 AND d.month 7 GROUP BY u.age_group, d.month ORDER BY u.age_group;星型模型的优点不言而喻规范化、无冗余、维度变化可以追溯SCD Type 2。但问题也很突出——每查一次都要 JOIN 三四张表数据量上来后JOIN 的成本直线上升。2.2 宽表模型快但冗余宽表就是把所有维度字段全部拍平到一张表里-- 宽表模型一张表搞定一切 CREATE TABLE wide_user_behavior ( -- 用户维度字段冗余 user_id VARCHAR(32) NOT NULL, user_name VARCHAR(64), age_group VARCHAR(16), city VARCHAR(32), membership_level VARCHAR(16), -- 时间维度字段冗余 full_date DATE NOT NULL, year INT, quarter INT, month INT, day_of_week INT, is_weekend TINYINT, -- 事件维度字段冗余 event_name VARCHAR(64), event_category VARCHAR(32), -- 度量值 pv BIGINT DEFAULT 1, duration_sec INT DEFAULT 0, amount DECIMAL(12, 2) DEFAULT 0, -- 退化维度 session_id VARCHAR(64), device_type VARCHAR(16), -- 索引 INDEX idx_date (full_date), INDEX idx_user (user_id), INDEX idx_event (event_category) ) COMMENT 用户行为宽表; -- 宽表的查询零JOIN直接查 SELECT age_group, month, COUNT(*) AS pv, COUNT(DISTINCT user_id) AS uv, AVG(duration_sec) AS avg_duration FROM wide_user_behavior WHERE year 2026 AND month 7 GROUP BY age_group, month ORDER BY age_group;宽表的查询体验好得离谱——零 JOIN、直接 GROUP BY分析师的 SQL 写得开心查询速度也快。但代价是存储爆炸一个用户信息在每条行为记录里都存一遍200 维度的宽表存储是星型的 3-5 倍。更新痛苦用户换了会员等级不好意思得把历史数据全部回刷一遍。维度不一致同一个用户在不同时间的行为记录里可能关联的是不同的维度快照。2.3 对比总结# 两套模型的对比评分 comparison { 查询性能: {星型模型: 2, 宽表模型: 5}, # 5分满分 存储效率: {星型模型: 5, 宽表模型: 1}, 维度更新方便: {星型模型: 5, 宽表模型: 1}, # SCD vs 全量回刷 分析师易用性: {星型模型: 2, 宽表模型: 5}, 灵活性加新维度: {星型模型: 5, 宽表模型: 2}, 数据一致性保证: {星型模型: 4, 宽表模型: 2}, JOIN复杂度: {星型模型: 1, 宽表模型: 5}, }三、实际决策分场景使用我们最后的选择非常务实不分胜负按场景各取所长。具体策略如下星型模型作为底层数据源ODS/DWD 层规范化存储保证数据的一致性和可追溯性。维度变化通过 SCD Type 2 记录历史。宽表作为上层应用表ADS 层对固定分析场景如日报、周报看板通过 ETL 从星型模型生成宽表。这个 ETL 每天跑一次就行。Doris 的聚合模型把宽表性能推到极致用 Doris 的 Aggregate Key 模型建宽表自动帮我们做预聚合查询直接走物化结果。这样做的好处是底层数据只有一份星型模型不用担心数据不一致。上层查询走宽表零 JOIN秒级响应。新维度加到星型模型后通过 ETL 自动传播到宽表不需要手动维护。四、工程落地方案实际落地的关键一步是自动化生成宽表的 ETL 流程。-- 从星型模型生成宽表Doris Routine Load -- Step 1: 创建 Doris 宽表使用聚合模型 -- Doris 的 AGGREGATE KEY 会自动对相同维度组合做预聚合 CREATE TABLE ads_user_behavior_wide ( -- 维度列 age_group VARCHAR(16), city VARCHAR(32), membership_level VARCHAR(32), full_date DATE, month INT, is_weekend TINYINT, event_category VARCHAR(32), -- 指标列聚合类型 pv BIGINT SUM DEFAULT 0, -- SUM: 对相同维度组合求和 duration_sec BIGINT SUM DEFAULT 0, amount DECIMAL(12,2) SUM DEFAULT 0, uv BIGINT HLL_UNION DEFAULT 0, -- HLL: 用HyperLogLog做近似去重 -- 聚合时间 update_time DATETIME REPLACE DEFAULT CURRENT_TIMESTAMP -- 每次更新覆盖 ) AGGREGATE KEY (age_group, city, membership_level, full_date, month, is_weekend, event_category) DISTRIBUTED BY HASH(age_group, city) BUCKETS 32 PROPERTIES ( replication_num 2, storage_medium SSD ); -- Step 2: 每日ETL —— 从星型模型INSERT到宽表 INSERT INTO ads_user_behavior_wide SELECT u.age_group, u.city, u.membership_level, d.full_date, d.month, d.is_weekend, e.event_category, -- 指标 f.pv, f.duration_sec, f.amount, HLL_HASH(f.user_id) AS uv_approx, -- HyperLogLog哈希 NOW() FROM fact_user_behavior f INNER JOIN dim_user u ON f.user_sk u.user_sk AND u.is_current 1 INNER JOIN dim_date d ON f.date_sk d.date_sk INNER JOIN dim_event e ON f.event_id e.event_id WHERE d.full_date CURDATE() - INTERVAL 1 DAY -- 只处理昨天的数据增量 ; -- Step 3: 查询宽表零JOIN秒级 SELECT age_group, month, SUM(pv) AS total_pv, HLL_UNION_AGG(uv) AS approx_uv, -- 估算UV SUM(amount) AS total_amount FROM ads_user_behavior_wide WHERE full_date 2026-07-01 AND full_date 2026-08-01 GROUP BY age_group, month ORDER BY month, age_group;五、总结这次数据仓库建模的复盘最大的收获是建模方法没有对错只有合适不合适。星型模型是地基。它保证了数据的规范性、一致性和可追溯性。不管上层怎么变地基要稳。宽表模型是装修。面向应用场景做优化让数据更好用、更快。但装修可以随时改地基不能随便动。不要二选一要做组合。用星型模型管源头用宽表服务查询用 ETL 做桥梁。三层解耦各司其职。OLAP 引擎的特性会影响建模决策。Doris 的聚合模型天然适合宽表如果是 Hive 可能宽表就不那么有优势。自动化的 ETL 比手动维护重要一百倍。手动维护宽表迟早会出错把生成逻辑固化在 ETL 里才能持续健康运行。建模这件事纠结一两次就够了找到适合自己团队和引擎的模式然后把它做好。