离线数仓分了ODS/DWD/DWS/ADS四层，面试官问每层到底干什么——你能说清楚吗？

-- ODS层：原始广告点击日志，保留所有原始字段
CREATE TABLE ods_ad_click_log_di (
    event_id        STRING    COMMENT '事件唯一ID',
    user_id         STRING    COMMENT '用户ID(可能为空)',
    ad_id           STRING    COMMENT '广告ID',
    channel         STRING    COMMENT '渠道(facebook/tiktok/google)',
    click_time      STRING    COMMENT '点击时间(原始字符串,未转时区)',
    device_info     STRING    COMMENT '设备信息JSON(未解析)',
    country_code    STRING    COMMENT '国家代码',
    raw_data        STRING    COMMENT '原始JSON保底字段'
) PARTITIONED BY (dt STRING)
LIFECYCLE 30;  -- 只保留30天，节省存储

-- DWD层：广告归因明细表，清洗+标准化+维度退化
INSERT OVERWRITE TABLE dwd_ad_attribution_di PARTITION(dt='${bizdate}')
SELECT
    event_id,
    COALESCE(user_id, 'unknown') AS user_id,          -- 空值处理
    ad_id,
    UPPER(channel) AS channel,                         -- 渠道名统一大写
    -- 时区转换：原始UTC → 统一存UTC时间戳
    TO_TIMESTAMP(click_time, 'yyyy-MM-dd HH:mm:ss') AS click_ts,
    GET_JSON_OBJECT(device_info, '$.os') AS device_os, -- JSON解析
    GET_JSON_OBJECT(device_info, '$.model') AS device_model,
    country_code,
    dim_country.country_name,                          -- 维度退化：关联国家名
    dim_country.timezone                               -- 维度退化：关联时区
FROM ods_ad_click_log_di t
LEFT JOIN dim_country ON t.country_code = dim_country.country_code
WHERE dt = '${bizdate}'
  AND event_id IS NOT NULL                             -- 过滤异常数据
  AND click_time IS NOT NULL;

这里有几个值得面试时展开讲的细节：

① 空值处理策略：user_id为空时填'unknown'而不是直接丢弃，因为广告点击事件本身有价值（可以算展示量、点击率），即使没有关联到用户。

② 维度退化：把国家名和时区直接关联进DWD，避免DWS/ADS层重复JOIN维度表。这个在出海业务里特别重要，因为几乎每张报表都要按国家维度切分。

③ 命名规范：dwd_ad_attribution_di——dwd是层级，ad_attribution是业务域+主题，di表示日增量。整个团队统一命名规范后，新人看表名就知道这张表在哪一层、什么业务、增量还是全量。

DWS层：公共指标，减少重复计算

DWS层的核心价值是'算一次，用N次'。比如渠道级别的ROI指标，投放团队要看、财务要看、管理层也要看，如果每个下游都从DWD自己算一遍，SQL逻辑重复不说，口径还容易不一致。

-- DWS层：渠道ROI日汇总表
INSERT OVERWRITE TABLE dws_channel_roi_1d PARTITION(dt='${bizdate}')
SELECT
    channel,
    country_code,
    COUNT(DISTINCT user_id) AS click_uv,         -- 点击UV
    COUNT(*) AS click_pv,                        -- 点击PV
    SUM(CASE WHEN is_install = 1 THEN 1 ELSE 0 END) AS install_cnt,  -- 安装数
    SUM(CASE WHEN is_paid = 1 THEN 1 ELSE 0 END) AS paid_cnt,       -- 付费数
    SUM(pay_amount) AS total_revenue,            -- 总收入
    SUM(ad_cost) AS total_cost,                  -- 总花费
    -- ROI = 收入/花费，花费为0时返回NULL避免除零
    CASE WHEN SUM(ad_cost) > 0
         THEN ROUND(SUM(pay_amount) / SUM(ad_cost), 4)
         ELSE NULL END AS roi
FROM dwd_ad_attribution_di
WHERE dt = '${bizdate}'
GROUP BY channel, country_code;

粒度怎么定？这是面试高频追问。我的经验是'按最细的公共需求来定'——如果大多数下游都需要渠道+国家的粒度，那DWS就按渠道+国家+天来聚合。如果个别报表需要更细的维度（比如广告素材级别），那就在ADS层再从DWD取数。

ADS层：面向业务，直接出数

ADS层是最薄的一层，通常是从DWS（偶尔从DWD）取数，拼成具体报表需要的格式，推送到OLAP引擎（如Hologres）或BI工具。

-- ADS层：每日ROI报表（推送到Hologres供BI查询）
INSERT OVERWRITE TABLE ads_daily_roi_report PARTITION(dt='${bizdate}')
SELECT
    channel,
    country_code,
    click_uv, install_cnt, paid_cnt,
    total_revenue, total_cost, roi,
    -- 计算转化率
    ROUND(install_cnt / NULLIF(click_uv, 0), 4) AS install_rate,
    ROUND(paid_cnt / NULLIF(install_cnt, 0), 4) AS pay_rate
FROM dws_channel_roi_1d
WHERE dt = '${bizdate}';

面试高频追问

追问一：DWD和ODS的区别到底是什么？有人说DWD就是ODS加了清洗，还有必要分开吗？

有必要。分开的核心原因是'数据可回溯'。ODS保留了原始数据，一旦DWD的清洗逻辑有bug（比如时区转换错了），可以从ODS重新跑，而不用找上游重推数据。我在短剧出海项目就遇到过一次——DWD的时区转换公式写反了，UTC+8写成了UTC-8，导致东南亚区域的报表日期全错了。因为ODS还在，我们直接修了DWD的SQL从ODS重跑就修复了，没有影响上游。

追问二：你的DWS表有多少张？怎么判断需不需要建DWS？

我们DWS大概有30+张表。判断标准是'有没有3个以上的下游在用同样粒度的同样指标'。如果有，就建DWS抽出来。如果某个指标只有一个报表用，就直接在ADS里从DWD算，不用经过DWS。过度建DWS反而增加维护成本和调度链路的复杂度。

追问三：分层之后数据延迟会不会增加？怎么处理？

会增加，每多一层就多一次调度。我们的做法是：ODS→DWD→DWS→ADS整个链路在DataWorks里配成DAG依赖，每层任务完成后自动触发下一层。核心链路（ROI报表）的端到端延迟控制在2小时内——ODS落地后30分钟内DWD跑完，DWS再30分钟，ADS很快因为只是简单聚合。如果业务要求更快，就走实时链路，不走离线分层。

实战踩坑

我在某短剧出海公司遇到过一次惨痛的分层教训。项目初期为了赶进度，我们没有建DWS层，ADS直接从DWD取数。一开始只有3个报表，还好。后来报表增长到20+个，每个报表都从DWD各算各的'渠道UV'和'付费转化率'——结果发现不同报表的同一个指标数字对不上！

原因是不同的ADS表在算'付费用户数'时，有的算的是'首次付费'，有的算的是'当日付费'。口径根本不一致。排查了2天才理清楚。后来我们紧急补建了DWS层，把公共指标统一定义和计算，所有ADS都从DWS取数，彻底解决了口径不一致的问题。

-- 踩坑案例：两个ADS表对同一个指标的不同计算方式
 
-- ADS表A的写法（首次付费用户数）
COUNT(DISTINCT CASE WHEN is_first_pay = 1 THEN user_id END) AS paid_cnt
 
-- ADS表B的写法（当日付费用户数）
COUNT(DISTINCT CASE WHEN pay_amount > 0 THEN user_id END) AS paid_cnt
 
-- 同一个字段名paid_cnt，口径完全不一样！
-- 解决方案：在DWS层统一定义
-- first_paid_cnt = 首次付费用户数
-- daily_paid_cnt = 当日付费用户数
-- 名字不同，口径明确，下游不会混淆

总结

数仓分层不是照搬书上的模板，而是为了解决'数据复用'和'口径一致'两个核心问题。面试时讲分层，要能说出每一层具体做了什么、你的表命名规范、你遇到过什么问题以及怎么解决的。最重要的一点：如果你的项目确实只有三层或者两层，不要硬编四层——诚实说，然后解释为什么这么设计，反而更有说服力。

离线分层讲清楚了，那实时数仓也要分层吗？下一篇聊实时数仓的分层设计——跟离线分层的区别可太大了。

你们项目的数仓分了几层？DWS层有多少张表？评论区聊聊～

关注「Wood聊大数据」| 9年大数据老兵，聊技术、聊职场、聊成长