数仓分层怎么落地

难度与前置

难度：入门
前置：知道离线表、分区、调度依赖是什么；能读简单 SQL。
读完能做什么：给一套订单/投放类主题画出四层边界，定表名与依赖，并用清单验收。

适用场景

当你要从 0 建一条可复用的离线主题链路（订单、投放消耗、内容播放等），或接手一堆「什么都往一张宽表塞」的表时，用分层把职责拆开。

不适用：

临时一次性取数、一次性导出（不必硬套四层）
源系统本身已是稳定汇总、且只有一个消费方（可直接 ADS，但要写清口径来源）
实时秒级指标（分层思想可借鉴，落地表结构另见实时域）

真实场景（一条）

投放业务要同时服务三件事：

对账：渠道侧消耗 vs 我方入账是否一致
日报：按渠道 × 剧本 × 天看消耗、转化
临时运营名单：某渠道近 7 天高消耗素材

如果每人各写一份从原始日志到报表的 SQL，会出现：

「消耗」口径三个版本（含税 / 不含税 / 含赠款）
同一天重跑，对账和日报对不上
临时名单直接扫原始日志，任务又慢又容易算重

目标分层：

层	这张链路里放什么	一行代表什么
ODS	渠道回传 / 业务库快照，几乎原样	源系统一行
DWD	清洗后的曝光/点击/消耗明细	一次计费或一次曝光事件
DWS	渠道×剧本×天等稳定主题汇总	一个聚合键一天
ADS	日报宽表、对账结果表、运营名单	面向具体交付

下面按「能照着做」给出步骤。示例表名可按你们域替换，公式不要改乱。

怎么做（步骤清单）

1. 先写粒度契约，再画层

每条主题链路开工前，先落到文档或表注释里（缺一项就先别建表）：

业务过程：例如「广告消耗入账」
主键 / 唯一键：例如 channel + creative_id + event_time + bill_id
时间口径：事件时间还是入账时间；分区用哪个日期
一行含义：一句话写死（「一行 = 一条计费明细」）

TIP

分层是后话，粒度才是第一份契约。粒度和 Join 搞不清，金额一定会翻倍或丢数。

2. 按职责落四层（判断口诀）

层	只做什么	明确不做什么
ODS	接数、分区落地、保留可追溯字段；字段名可贴近源	不算业务指标、不「顺便」过滤有效订单
DWD	去重、类型/枚举/时区标准化、补公共维度、统一核心口径字段	不做多主题大宽汇总；不绑某一张看板字段
DWS	多下游会复用的主题 × 周期汇总	不为单次临时需求建表；不偷偷改 DWD 已定口径
ADS	报表/接口/名单所需字段与过滤	不重新定义「成交金额」「消耗」等核心口径

落层三问（比「聚没聚合」更管用）：

一行还是稳定业务事件吗？→ 偏 DWD
相同维度/周期/口径会被多个应用复用吗？→ 偏 DWS
字段明显只服务一张看板或一次运营动作吗？→ 偏 ADS

3. 表命名（自文档化）

建议公式：

text

{层级}_{业务域}_{主题或实体}_{补充说明}_{时间粒度}

示例（投放消耗主题）：

text

ods_ad_spend_di              -- 日增量原始消耗
dwd_ad_spend_detail_di       -- 清洗后计费明细
dws_ad_channel_play_1d       -- 渠道×剧本 天汇总
ads_ad_daily_report_1d          -- 运营日报
ads_ad_recon_channel_1d      -- 渠道日对账结果
dim_ad_channel_df            -- 渠道维度日全量

约定（团队内统一即可）：

di 日增量，df 日全量，1d / 1h 汇总粒度
库或 schema 也可按层拆：ods / dwd / dws / ads / dim
字段一律 snake_case；同一含义全库同名（如统一 cost_amount，不要混用 spend / cost）

4. DDL 与分区骨架（可复制改）

DWD 示意（引擎语法按 Hive / Spark / MaxCompute 微调）：

sql

-- 粒度：一行 = 一条计费明细；分区 dt = 入账业务日
CREATE TABLE IF NOT EXISTS dwd_ad_spend_detail_di (
  bill_id          STRING  COMMENT '计费唯一键',
  channel_id       STRING  COMMENT '渠道',
  play_id          STRING  COMMENT '剧本/剧目',
  creative_id      STRING  COMMENT '素材',
  cost_amount      DECIMAL(18,4) COMMENT '消耗金额（口径：不含税本币）',
  event_ts         TIMESTAMP COMMENT '事件时间（已统一时区）',
  account_tz       STRING  COMMENT '账户时区',
  src_system       STRING  COMMENT '来源系统',
  is_valid         BOOLEAN COMMENT '是否计入正式口径（false=被业务过滤）',
  filter_reason    STRING  COMMENT '过滤原因；计入口径时为空。与坑1呼应：过滤放DWD不放ODS',
  etl_ts           TIMESTAMP COMMENT '入仓时间'
)
COMMENT 'DWD 广告消耗明细'
PARTITIONED BY (dt STRING COMMENT '入账业务日 yyyy-MM-dd');

DWS 示意：

sql

CREATE TABLE IF NOT EXISTS dws_ad_channel_play_1d (
  channel_id       STRING,
  play_id          STRING,
  cost_amount      DECIMAL(18,4) COMMENT '与 DWD 同口径汇总',
  bill_cnt         BIGINT
)
COMMENT 'DWS 渠道×剧本 天汇总'
PARTITIONED BY (dt STRING);

5. 加工顺序与依赖（调度落点）

推荐依赖边（不要跨层乱连）：

text

源系统 / 文件
  → ods_ad_spend_di
  → dwd_ad_spend_detail_di   （可关联 dim_*）
  → dws_ad_channel_play_1d
  → ads_ad_daily_report_1d / ads_ad_recon_channel_1d

调度检查项：

ODS 完成且分区行数 / 文件数达标，再跑 DWD
DWD 质量规则通过（主键唯一、金额非空），再跑 DWS
ADS 只读 DWS（或明确允许的 DWD），禁止 ADS 直接扫 ODS「抄近路」
补数：按分区 dt 重跑；上游重跑必须级联下游同分区

6. 层间数据契约（最少字段）

每层交接至少写清：

契约项	示例
主键与粒度	`bill_id` 全局唯一
分区字段	`dt` = 入账业务日
增量方式	日增量；迟到数据 T+2 可重跑
核心指标口径	`cost_amount` = 不含税本币，不含赠款
SLA	DWD 9:00 前就绪；ADS 9:30 前就绪
责任人	主题 owner + 值班群

常见坑（至少牢记这些）

把业务过滤塞进 ODS
例如 ODS 只留「有效消耗」，事后无法对账「渠道侧有、我方被滤掉」的差异。ODS 保真，过滤放到 DWD 并留过滤原因字段。
DWD 没定粒度就 Join
订单头 × 订单行、曝光 × 点击未对齐主键，金额/次数成倍放大。Join 前先写「一行含义」和预期膨胀比。
DWS / ADS 各自重算核心口径
日报用含税、对账用不含税，还都叫「消耗」。核心口径只在 DWD（或指标字典）定义一次，下游只引用。
ADS 直连 ODS 赶工期
短期快，长期每张报表一套清洗，口径分叉且无法统一补数。临时需求可开「一次性 ADS」，过期下线或回升 DWS。
跨时区只用服务器本地日切分区
多时区投放里，UTC 事件按东八区切日会导致留存/消耗错位。DWD 明确：事件时区、账户时区、分区业务日三者关系。

如何验收（可量化）

把下面当成合并前 checklist，全勾才算本篇实践落地：

[ ] 每张表注释或设计文档写清：业务过程、主键、分区、一行含义
[ ] 表名符合 {层}_{域}_{实体}_…；看表名能判断层与域
[ ] ODS 无业务指标计算；DWD 有统一后的核心口径字段，并有 is_valid / filter_reason（或等价字段）
[ ] 依赖边无「ADS → ODS」；补数按 dt 可级联重跑
[ ] 抽一日分区：sum(DWD.cost_amount) 与 sum(DWS.cost_amount) 同口径一致（允许说明内的舍入误差）
[ ] 对账 ADS 与渠道文件差异可解释（落到过滤原因或迟到分区，而不是「数不一样」）
[ ] 至少 3 条质量规则挂在 DWD：主键唯一、金额非空、分区延迟告警

快速核对 SQL 示例：

对齐前提

若 DWS 对 DWD 有过滤（例如只汇总 is_valid = true），对齐查询必须带同一过滤条件，否则 diff 会把「业务过滤掉的金额」误报成分层错误。

sql

-- 同日 DWD vs DWS 口径对齐（应接近 0）
-- 此处与 DWS 约定一致：只汇总正式口径
SELECT
  d.dt,
  d.cost_dwd,
  s.cost_dws,
  d.cost_dwd - s.cost_dws AS diff
FROM (
  SELECT dt, SUM(cost_amount) AS cost_dwd
  FROM dwd_ad_spend_detail_di
  WHERE dt = '${biz_date}'
    AND is_valid = true
  GROUP BY dt
) d
JOIN (
  SELECT dt, SUM(cost_amount) AS cost_dws
  FROM dws_ad_channel_play_1d
  WHERE dt = '${biz_date}'
  GROUP BY dt
) s ON d.dt = s.dt;

数仓分层怎么落地 ​

适用场景 ​

真实场景（一条） ​

怎么做（步骤清单） ​

1. 先写粒度契约，再画层 ​

2. 按职责落四层（判断口诀） ​

3. 表命名（自文档化） ​

4. DDL 与分区骨架（可复制改） ​

5. 加工顺序与依赖（调度落点） ​

6. 层间数据契约（最少字段） ​

常见坑（至少牢记这些） ​

如何验收（可量化） ​

相关面试题 ​

数仓分层怎么落地

适用场景

真实场景（一条）

怎么做（步骤清单）

1. 先写粒度契约，再画层

2. 按职责落四层（判断口诀）

3. 表命名（自文档化）

4. DDL 与分区骨架（可复制改）

5. 加工顺序与依赖（调度落点）

6. 层间数据契约（最少字段）

常见坑（至少牢记这些）

如何验收（可量化）

相关面试题