随笔

数据仓库分层建模：ODS、DWD、DWS、ADS常见模式

数据仓库分层不是简单换表名前缀，而是用稳定的数据流把原始数据、明细事实、主题汇总和应用结果拆开。2026 年，湖仓一体和批流一体继续普及，但 ODS、DWD、DWS、ADS 的职责边界仍然是建模的地基。下面用 8 个步骤落地一套可复用的分层模式，并给出常见表结构与 SQL 示例。

第1步：先定分层边界与数据流

分层的第一原则是单向依赖：ODS 只做贴源，DWD 只做清洗与明细整合，DWS 只做主题聚合，ADS 只做应用结果。跨层反查可以，但不能让 ADS 直接回写 DWD。建议统一命名：ods_业务_表_更新方式，dwd_域_事实_粒度，dws_域_主题_粒度，ads_应用_指标_粒度。更新方式常用 di 表示日增量，df 表示日全量，hi 表示小时增量。

text ODS（贴源） -> DWD（明细） -> DWS（汇总） -> ADS（应用） | | DIM（公共维度） <- 一致性维度

落地时先画数据流图，再建表。每张表必须写清粒度、主键、分区字段、更新频率和指标口径。比如 dwd_trade_order_detail_di 的粒度是订单明细，dws_trade_user_1d 的粒度是用户加天，ads_trade_daily_report 的粒度是自然日。粒度一旦写错，后续所有汇总都会失真。

第2步：ODS 层做贴源与可追溯

ODS 的核心是“保真”。不要在这一层做复杂清洗、不要改业务字段含义，只做抽取、分区、元数据登记。常见模式有三种：全量快照、增量日志、CDC 变更流。订单类数据建议保留增量日志，用户资料可保留全量快照，库存流水适合 CDC。ODS 表要保留源库、源表、抽取时间，方便追查问题。

sql CREATE TABLE ods_order_info_di ( id BIGINT, user_id BIGINT, order_status STRING, total_amount DECIMAL(18,2), create_time STRING, update_time STRING, src_db STRING, src_table STRING, ingest_time TIMESTAMP ) PARTITIONED BY (dt STRING);

抽取时按 dt 分区写入，保留原始字段类型和异常值。若来源是 JSON，可先用 STRING 落原始报文，再在 DWD 解析，避免 ODS 因解析失败丢数据。全量快照每天覆盖一个分区，增量日志按变更时间追加，CDC 则要记录操作类型。无论哪种模式，都要保证同一批次可重跑、分区可覆盖。

第3步：DWD 层做清洗、去重与明细整合

DWD 面向业务过程，输出原子粒度事实表。常见模式有事务事实表、周期快照事实表、累积快照事实表。交易域常用事务事实表，粒度为“订单明细一行”；库存域常用周期快照；订单履约常用累积快照。清洗包括去重、空值处理、单位统一、枚举映射、敏感字段脱敏。DWD 不要做应用层聚合，否则会失去明细追溯能力。

sql INSERT OVERWRITE TABLE dwd_trade_order_detail_di PARTITION (dt='2026-11-02') SELECT o.id AS order_id, o.user_id, d.sku_id, CAST(d.price AS DECIMAL(18,2)) AS price, CAST(d.qty AS BIGINT) AS qty, CAST(d.price AS DECIMAL(18,2)) * CAST(d.qty AS BIGINT) AS amount, COALESCE(o.order_status, 'UNKNOWN') AS order_status FROM ods_order_info_di o JOIN ods_order_detail_di d ON o.id = d.order_id WHERE o.dt='2026-11-02' AND d.dt='2026-11-02';

去重可用 ROW_NUMBER 按主键和更新时间取最新一条。维度字段不要直接塞进事实表，能退化的退化，不能退化的通过 DIM 关联。所有金额、数量、比率都要在 DWD 统一口径。

sql SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (PARTITION BY id ORDER BY update_time DESC) AS rn FROM ods_order_info_di t WHERE dt='2026-11-02' ) x WHERE rn=1;

第4步：DWS 层做主题聚合与轻度汇总

DWS 是复用率最高的一层。按主题域和粒度建轻度汇总表，常见粒度是“用户+天”“商品+天”“店铺+天”。模式上建议 1d 表按天增量，nd 表按滑动窗口，td 表按历史累计。不要一上来就做几百列的大宽表，先保证指标可追溯，再按查询场景合并。DWS 的每个指标要能回答：来自哪张 DWD、用什么过滤条件、是否去重、口径是否含退款。

sql CREATE TABLE dws_trade_user_1d ( user_id BIGINT, order_cnt BIGINT, pay_cnt BIGINT, pay_amount DECIMAL(18,2), refund_amount DECIMAL(18,2) ) PARTITIONED BY (dt STRING);

INSERT OVERWRITE TABLE dws_trade_user_1d PARTITION (dt='2026-11-02') SELECT user_id, COUNT(DISTINCT order_id) AS order_cnt, COUNT(DISTINCT CASE WHEN order_status='PAID' THEN order_id END) AS pay_cnt, SUM(CASE WHEN order_status='PAID' THEN amount ELSE 0 END) AS pay_amount, SUM(CASE WHEN order_status='REFUND' THEN amount ELSE 0 END) AS refund_amount FROM dwd_trade_order_detail_di WHERE dt='2026-11-02' GROUP BY user_id;

建议在表注释中写清“统计粒度 + 指标口径 + 更新周期”。1d 表保证当天口径稳定，nd 表通过 1d 表滚动汇总，td 表通过历史分区累加，避免每次都扫描全量明细。

第5步：ADS 层做应用结果与高性能输出

ADS 直接服务报表、接口、运营看板。它的表结构可以按查询定制，允许适度冗余，但指标必须引用 DWS 或 DWD 的统一定义。常见模式包括日报表、漏斗表、留存表、排行榜和标签结果表。ADS 表通常按应用命名，避免被其他应用直接复用。

sql CREATE TABLE ads_trade_daily_report ( report_date STRING, pay_user_cnt BIGINT, pay_order_cnt BIGINT, pay_amount DECIMAL(18,2), refund_amount DECIMAL(18,2), update_time TIMESTAMP );

写入时用覆盖或幂等合并，保证重复调度不产生重复行。对外接口只读 ADS，不直接查 DWD，避免大范围扫描影响核心链路。漏斗类应用可按“曝光—点击—下单—支付”建阶段表，留存类应用按首日与后续活跃日建结果表。

第6步：用 DIM 统一维度与缓慢变化维

公共维度建议独立成 DIM 层。用户、商品、店铺、地区等维度要统一主键和层级。SCD1 覆盖历史，适合纠错；SCD2 保留版本，适合分析历史归属。DIM 表要有生效时间、失效时间和当前标记。

sql CREATE TABLE dim_user_scd2 ( user_sk BIGINT, user_id BIGINT, user_level STRING, start_date STRING, end_date STRING, is_current INT );

DWD 关联 DIM 时优先使用代理键或自然键加时间条件，避免事实表被错误归属。SCD2 更新时先关闭旧版本，再插入新版本，保持同一自然键只有一个当前记录。

第7步：调度、质量与回跑

分层表要按依赖编排：ODS 完成后跑 DWD，DWD 完成后跑 DWS，最后跑 ADS。每层至少配置行数、主键唯一、空值率、金额非负、分区新鲜度检查。回跑时按 dt 重算，并向下游传递影响范围。

sql -- 质量检查示例：主键重复即失败 SELECT order_id, COUNT() AS c FROM dwd_trade_order_detail_di WHERE dt='2026-11-02' GROUP BY order_id HAVING COUNT() > 1;

建议保留数据血缘和表级 SLA，异常时先阻断下游，再定位 ODS 抽取或 DWD 清洗问题。调度依赖用 DAG 显式表达，避免人工顺序执行造成口径漂移。

第8步：落地检查清单

  1. 每张表是否写清粒度、主键、分区和更新方式？
  2. ODS 是否保留原始值与来源信息？
  3. DWD 是否完成去重、清洗、单位统一和维度关联？
  4. DWS 指标是否能追溯到 DWD 口径？
  5. ADS 是否只读汇总结果并具备幂等写入？
  6. DIM 是否覆盖 SCD1/SCD2 场景并标注当前版本？
  7. 调度依赖、质量规则、回跑方案是否齐备？

按这 8 步推进，ODS 保真、DWD 明细、DWS 复用、ADS 提速，分层才不会沦为表名前缀，而会成为可持续演进的数据资产。