数仓分层实战：从ODS到ADS的完整数据流设计（含ETL避坑指南）

数仓分层实战：从ODS到ADS的完整数据流设计（含ETL避坑指南）

最近在复盘一个电商订单分析项目时，我重新梳理了整个数据仓库的分层架构，发现很多团队在数据流转过程中容易陷入“为了分层而分层”的误区。数据仓库分层不是简单的概念划分，而是需要结合具体业务场景，设计出既能保证数据质量、又能支撑高效分析的数据流水线。今天我就以电商订单分析为例，分享从原始数据到应用报表的完整设计思路，重点聊聊各层的表设计规范、字段冗余策略，以及那些容易踩坑的ETL细节。

1. 数据仓库分层架构的核心价值

数据仓库分层本质上是一种工程化的数据组织方法。我在多个项目中验证过，合理的分层设计能让数据团队的工作效率提升30%以上。为什么这么说？因为分层解决了几个关键问题：

数据血缘的清晰追溯：当业务方反馈某个报表数字异常时，如果数据没有分层，你可能需要从最终报表一直回溯到原始业务表，这个过程就像在迷宫里找出口。而有了清晰的分层，你可以快速定位问题发生在哪一层——是ODS层数据同步延迟，还是DWD层清洗逻辑有误，或是DWS层聚合计算偏差。

计算资源的有效复用：很多数据分析需求有共性。比如用户留存分析、商品复购率计算、渠道转化漏斗，这些指标在DWS层做好轻度汇总后，多个业务方都可以直接使用，避免了每个团队都从原始数据开始重复计算。

数据质量的逐层保障：数据质量问题应该在合适的层级解决。ODS层保持原始数据原貌，DWD层进行标准化清洗，DWS层确保指标口径一致。这种分层处理让数据质量问题的排查和修复更加有针对性。

注意：分层不是越多越好。我见过有些团队设计了七八层，结果数据流转路径过长，维护成本反而增加。对于大多数业务场景，五层架构（ODS、DWD、DWS、DWT、ADS）已经足够清晰和实用。

2. ODS层：原始数据的接入与存储策略

ODS层是数据仓库的“入口”，这里的设计直接影响后续所有数据加工的质量和效率。很多团队对ODS层的重视不够，认为只是简单地把数据搬过来，其实这里面有很多讲究。

2.1 数据同步的两种模式

根据业务需求，ODS层的数据同步通常采用全量或增量两种模式：

全量同步适合数据量较小、变化缓慢的维度表，比如商品类目表、地区编码表。这类表的特点是记录数不多（通常几万条以内），但需要保存完整的历史快照。

-- 全量同步示例：商品类目表
CREATE TABLE ods_product_category_full (
  category_id BIGINT COMMENT '类目ID',
  category_name STRING COMMENT '类目名称',
  parent_id BIGINT COMMENT '父类目ID',
  level TINYINT COMMENT '类目层级',
  is_active BOOLEAN COMMENT '是否有效',
  create_time TIMESTAMP COMMENT '创建时间',
  update_time TIMESTAMP COMMENT '更新时间',
  dt STRING COMMENT '分区字段（业务日期）'
) COMMENT '商品类目全量表'
PARTITIONED BY (dt STRING)
STORED AS PARQUET;

增量同步适合数据量大的事务表，比如订单表、用户行为日志表。这类表每天新增数据多，全量同步成本太高。

-- 增量同步示例：订单表
CREATE TABLE ods_order_inc (
  order_id STRING COMMENT '订单ID',
  user_id BIGINT COMMENT '用户ID',
  product_id BIGINT COMMENT '商品ID',
  order_amount DECIMAL(10,2) COMMENT '订单金额',
  order_status TINYINT COMMENT '订单状态',
  create_time TIMESTAMP COMMENT '创建时间',
  update_time TIMESTAMP COMMENT '更新时间',
  dt STRING COMMENT '分区字段（业务日期）'
) COMMENT '订单增量表'
PARTITIONED BY (dt STRING)
STORED AS PARQUET
TBLPROPERTIES ('parquet.compression'='SNAPPY');

2.2 ODS层常见问题与解决方案

在实际项目中，我遇到过几个典型的ODS层问题：

问题一：时间字段格式混乱 业务系统的时间字段可能有多种格式：2024-01-15、2024/01/15、20240115，甚至带有时区信息。如果不在ODS层做初步规范化，后续处理会很麻烦。

我的做法是在ODS层增加一个标准化时间字段：

-- 在表结构中增加标准化字段
ALTER TABLE ods_order_inc ADD COLUMNS (
  order_time_std TIMESTAMP COMMENT '标准化订单时间'
);

-- 在数据同步时进行转换
INSERT OVERWRITE TABLE ods_order_inc PARTITION (dt='2024-01-15')
SELECT 
  order_id,
  user_id,
  product_id,
  order_amount,
  order_status,
  create_time,
  update_time,
  -- 统一时间格式转换
  CASE 
    WHEN create_time RLIKE '^\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}$' 
      THEN CAST(create_time AS TIMESTAMP)
    WHEN create_time RLIKE '^\\d{4}/\\d{2}/\\d{2} \\d{2}:\\d{2}:\\d{2}$'
      THEN FROM_UNIXTIME(UNIX_TIMESTAMP(create_time, 'yyyy/MM/dd HH:mm:ss'))
    ELSE NULL
  END AS order_time_std
FROM source_order_table
WHERE dt='2024-01-15';

问题二：敏感数据过早脱敏 有些团队喜欢在ODS层就对手机号、身份证号做脱敏处理，这其实不利于数据问题排查。我的建议是：ODS层保留原始数据，在DWD层再做脱敏。

问题三：分区策略不合理 ODS层的分区设计要考虑数据量和查询模式。我常用的策略是：

表类型	分区策略	保留周期	说明
用户行为日志	按天分区	30天	数据量大，查询通常按时间范围
订单表	按天分区	90天	中等数据量，需要支持历史查询
用户资料表	按月分区	永久	数据量小，变化缓慢
商品信息表	按月分区	永久	数据量中等，需要历史版本

3. DWD层：数据清洗与维度退化的艺术

DWD层是数据质量的关键防线，也是性能优化的重点区域。这一层要做的事情很多，但核心是“去伪存真，化繁为简”。

3.1 数据清洗的标准化流程

数据清洗不是简单的过滤，而是有章法的处理过程。我总结了一个四步清洗法：

第一步：空值处理 空值处理要分情况讨论，不能一概用NULL填充或直接过滤。

-- 空值处理示例
CREATE TABLE dwd_order_detail AS
SELECT 
  order_id,
  -- 用户ID为空时，尝试从其他关联表获取
  COALESCE(user_id, 
    (SELECT user_id FROM user_backup WHERE order_id = o.order_id LIMIT 1)
  ) AS user_id,
  -- 金额为空时，根据业务规则设置默认值
  CASE 
    WHEN order_amount IS NULL THEN 0.0
    WHEN order_amount < 0 THEN 0.0  -- 处理负值
    ELSE order_amount
  END AS order_amount,
  -- 状态字段枚举值标准化
  CASE order_status
    WHEN '已支付' THEN 2
    WHEN '待支付' THEN 1
    WHEN '已取消' THEN 0
    WHEN '支付中' THEN 1
    ELSE -1  -- 未知状态
  END AS order_status_code,
  create_time,
  dt
FROM ods_order_inc o
WHERE dt = '2024-01-15'
  -- 关键字段不能为空
  AND order_id IS NOT NULL
  AND create_time IS NOT NULL;

第二步：异常值检测 异常值检测需要结合业务规则。比如订单金额，可以设置合理的上下限：

-- 异常值检测：订单金额在1元到10万元之间
SELECT 
  COUNT(*) as total_records,
  SUM(CASE WHEN order_amount < 1 OR order_amount > 100000 THEN 1 ELSE 0 END) as outlier_count,
  ROUND(SUM(CASE WHEN order_amount < 1 OR order_amount > 100000 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as outlier_percentage
FROM ods_order_inc
WHERE dt = '2024-01-15';

如果异常值比例超过1%，就需要深入排查是数据问题还是业务异常。

第三步：重复数据去重 重复数据去重要根据业务主键来判断：

-- 基于订单ID去重，保留最新记录
SELECT 
  order_id,
  user_id,
  order_amount,
  order_status,
  create_time,
  ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY update_time DESC) as rn
FROM ods_order_inc
WHERE dt = '2024-01-15'
QUALIFY rn = 1;

第四步：数据格式统一 确保相同含义的字段在不同表中格式一致：

字段类型	统一格式	示例
时间字段	yyyy-MM-dd HH:mm:ss	2024-01-15 14:30:00
金额字段	DECIMAL(10,2)	199.99
布尔字段	TINYINT (0/1)	1 表示是
状态字段	统一枚举值	用数字代码代替中文描述

3.2 维度退化的实战技巧

维度退化是DWD层的重要优化手段，目的是减少后续查询的JOIN操作。但退化什么、退化多少，需要仔细权衡。

应该退化的维度：

1. 高频查询的维度属性：比如商品名称、商品类目，这些在订单分析中几乎每次都要用到
2. 变化缓慢的维度：比如用户注册渠道、商品品牌
3. 层级较少的维度：比如省份、城市，层级少且数据量不大

不应该退化的维度：

1. 变化频繁的维度：比如商品价格，今天和明天可能不一样
2. 层级复杂的维度：比如完整的行政区划（省-市-区-街道）
3. 数据量大的维度：比如详细的用户行为标签

-- 维度退化示例：订单事实表
CREATE TABLE dwd_order_fact (
  -- 订单基础信息
  order_id STRING COMMENT '订单ID',
  order_time TIMESTAMP COMMENT '订单时间',
  order_amount DECIMAL(10,2) COMMENT '订单金额',
  
  -- 用户维度退化
  user_id BIGINT COMMENT '用户ID',
  user_name STRING COMMENT '用户昵称',
  user_gender TINYINT COMMENT '用户性别',
  user_province STRING COMMENT '用户省份',
  user_city STRING COMMENT '用户城市',
  
  -- 商品维度退化  
  product_id BIGINT COMMENT '商品ID',
  product_name STRING COMMENT '商品名称',
  product_category_id INT COMMENT '商品类目ID',
  product_category_name STRING COMMENT '商品类目名称',
  product_brand STRING COMMENT '商品品牌',
  
  -- 商家维度退化
  seller_id BIGINT COMMENT '商家ID',
  seller_name STRING COMMENT '商家名称',
  
  -- 时间维度退化
  order_year INT COMMENT '订单年份',
  order_month INT COMMENT '订单月份',
  order_day INT COMMENT '订单日期',
  order_hour INT COMMENT '订单小时',
  order_weekday INT COMMENT '订单星期几',
  
  -- 原始外键（用于关联详细维度）
  original_user_id BIGINT COMMENT '原始用户ID（关联用户维表）',
  original_product_id BIGINT COMMENT '原始商品ID（关联商品维表）',
  
  dt STRING COMMENT '分区字段'
) COMMENT '订单事实表（含维度退化）'
PARTITIONED BY (dt STRING)
STORED AS PARQUET;

这个设计在订单事实表中冗余了用户、商品、商家的关键属性，同时保留了原始外键。这样80%的查询可以直接用宽表，20%需要详细维度信息的查询可以通过外键关联。

3.3 DWD层ETL避坑指南

坑一：时间戳处理不当 时间戳处理是ETL中最容易出错的地方。我踩过的坑包括：

• 时区问题：业务系统用UTC时间，数仓用本地时间
• 精度问题：有的时间戳精确到秒，有的精确到毫秒
• 格式问题：字符串和timestamp类型混用

解决方案：

-- 统一时间处理函数
CREATE FUNCTION normalize_timestamp(timestamp_str STRING) RETURNS TIMESTAMP
AS '
  -- 处理多种时间格式
  CASE
    WHEN timestamp_str IS NULL THEN NULL
    WHEN timestamp_str RLIKE "^\\\\d{4}-\\\\d{2}-\\\\d{2} \\\\d{2}:\\\\d{2}:\\\\d{2}\\\\.\\\\d{3}$" 
      THEN FROM_UNIXTIME(CAST(SUBSTR(timestamp_str, 1, 10) AS BIGINT))
    WHEN timestamp_str RLIKE "^\\\\d{10}$" 
      THEN FROM_UNIXTIME(CAST(timestamp_str AS BIGINT))
    WHEN timestamp_str RLIKE "^\\\\d{13}$"
      THEN FROM_UNIXTIME(CAST(SUBSTR(timestamp_str, 1, 10) AS BIGINT))
    ELSE TRY_CAST(timestamp_str AS TIMESTAMP)
  END
';

坑二：维度退化遗漏关键字段 退化维度时，容易遗漏业务分析需要的字段。比如只退了商品名称，没退商品品牌，导致后续查询还是要JOIN商品维表。

我的检查方法是：收集过去半年所有的业务查询SQL，统计GROUP BY和WHERE中出现的维度字段，把出现频率前20的字段考虑退化。

坑三：数据清洗过度 清洗规则太严格，过滤掉太多数据，导致分析结果失真。比如把金额为0的订单都过滤了，但有些促销活动就是0元订单。

解决方案：设置数据清洗报告，每天监控：

-- 数据清洗质量报告
SELECT 
  dt,
  table_name,
  total_records,
  filtered_records,
  filtered_percentage,
  -- 关键指标变化监控
  LAG(filtered_percentage, 1) OVER (ORDER BY dt) as prev_percentage,
  CASE 
    WHEN filtered_percentage - LAG(filtered_percentage, 1) OVER (ORDER BY dt) > 5 
      THEN '异常波动'
    ELSE '正常'
  END as status
FROM dwd_quality_daily
WHERE dt >= DATE_SUB(CURRENT_DATE, 7);

4. DWS层：轻度汇总与主题宽表设计

DWS层是面向分析的数据服务层，这里的核心思想是“预聚合，降复杂度”。设计好的DWS层能让80%的查询直接命中，无需访问下层明细数据。

4.1 主题宽表的构建原则

主题宽表不是越宽越好，而是要有明确的主题边界。我通常按业务分析视角划分主题：

用户主题：围绕用户行为构建 商品主题：围绕商品表现构建
交易主题：围绕订单流程构建 流量主题：围绕用户访问构建

每个主题宽表包含该主题下的核心指标和常用维度。以用户主题为例：

-- 用户日行为宽表
CREATE TABLE dws_user_action_daily (
  user_id BIGINT COMMENT '用户ID',
  dt STRING COMMENT '日期',
  
  -- 用户属性（轻度退化）
  user_age_group STRING COMMENT '年龄分段',
  user_gender TINYINT COMMENT '性别',
  user_province STRING COMMENT '省份',
  
  -- 活跃行为指标
  login_count INT COMMENT '登录次数',
  session_count INT COMMENT '会话次数',
  page_view_count INT COMMENT '浏览页面数',
  duration_seconds BIGINT COMMENT '停留时长（秒）',
  
  -- 互动行为指标
  like_count INT COMMENT '点赞次数',
  comment_count INT COMMENT '评论次数',
  share_count INT COMMENT '分享次数',
  collect_count INT COMMENT '收藏次数',
  
  -- 交易行为指标
  order_count INT COMMENT '下单次数',
  order_amount DECIMAL(15,2) COMMENT '下单金额',
  payment_count INT COMMENT '支付次数',
  payment_amount DECIMAL(15,2) COMMENT '支付金额',
  refund_count INT COMMENT '退款次数',
  refund_amount DECIMAL(15,2) COMMENT '退款金额',
  
  -- 商品偏好（数组类型，存储top商品）
  top_view_products ARRAY<STRUCT<product_id:BIGINT, view_count:INT>> COMMENT '浏览最多商品',
  top_order_products ARRAY<STRUCT<product_id:BIGINT, order_count:INT>> COMMENT '购买最多商品',
  
  -- 时间分布（JSON格式）
  active_hours MAP<STRING, INT> COMMENT '活跃小时分布',
  
  -- 衍生指标
  avg_order_amount DECIMAL(10,2) COMMENT '平均订单金额',
  conversion_rate DECIMAL(5,4) COMMENT '下单转化率',
  
  -- 数据质量标记
  data_source STRING COMMENT '数据来源',
  update_time TIMESTAMP COMMENT '更新时间'
) COMMENT '用户日行为宽表'
PARTITIONED BY (dt STRING)
STORED AS PARQUET
TBLPROPERTIES (
  'parquet.compression'='SNAPPY',
  'parquet.block.size'='268435456'  -- 256MB块大小
);

4.2 聚合粒度的选择策略

聚合粒度决定了宽表的灵活性和性能。我的经验是：

基础粒度表：按最细粒度聚合，保留最大灵活性

• 用户+天粒度：dws_user_action_daily
• 商品+天粒度：dws_product_sales_daily
• 渠道+天粒度：dws_channel_performance_daily

常用粒度表：按业务常用维度聚合，提升查询性能

• 用户+周粒度：dws_user_action_weekly
• 商品+类目+天粒度：dws_category_sales_daily
• 省份+天粒度：dws_region_sales_daily

特殊粒度表：针对特定场景优化

• 用户+最近30天滚动：dws_user_action_30d
• 商品+最近7天热度：dws_product_hot_7d

-- 多粒度聚合示例
CREATE TABLE dws_user_action_weekly (
  user_id BIGINT,
  week_start_date STRING COMMENT '周开始日期',
  week_end_date STRING COMMENT '周结束日期',
  
  -- 周汇总指标
  login_days INT COMMENT '活跃天数',
  total_duration BIGINT COMMENT '总停留时长',
  total_order_amount DECIMAL(15,2) COMMENT '周订单总额',
  
  -- 周内分布（周一到周日）
  daily_active_map MAP<STRING, BOOLEAN> COMMENT '每日是否活跃',
  
  -- 趋势指标
  prev_week_order_amount DECIMAL(15,2) COMMENT '上周订单额',
  week_over_week_growth DECIMAL(8,4) COMMENT '周环比增长'
) COMMENT '用户周行为宽表'
PARTITIONED BY (week_start_date STRING);

4.3 宽表更新的优化技巧

宽表更新是DWS层的性能瓶颈。我常用的优化策略：

增量更新：对于可增量计算的指标，使用增量更新

-- 增量更新用户日行为表
INSERT OVERWRITE TABLE dws_user_action_daily PARTITION (dt='2024-01-15')
SELECT 
  COALESCE(new.user_id, old.user_id) as user_id,
  '2024-01-15' as dt,
  -- 指标计算：新数据 + 旧数据
  COALESCE(old.login_count, 0) + COALESCE(new.login_count, 0) as login_count,
  COALESCE(old.order_amount, 0) + COALESCE(new.order_amount, 0) as order_amount,
  -- 数组合并
  CASE 
    WHEN new.top_view_products IS NOT NULL AND old.top_view_products IS NOT NULL
      THEN array_union(new.top_view_products, old.top_view_products)
    WHEN new.top_view_products IS NOT NULL THEN new.top_view_products
    ELSE old.top_view_products
  END as top_view_products
FROM (
  -- 当日增量数据
  SELECT user_id, login_count, order_amount, top_view_products
  FROM dwd_user_action_daily
  WHERE dt='2024-01-15'
) new
FULL OUTER JOIN (
  -- 历史数据（用于增量更新）
  SELECT user_id, login_count, order_amount, top_view_products
  FROM dws_user_action_daily
  WHERE dt='2024-01-14'
) old ON new.user_id = old.user_id;

分时更新：将宽表更新任务分散到不同时间执行，避免资源竞争

分层更新：先更新基础粒度表，再基于基础表更新上层聚合表

5. DWT层：主题累积与历史汇总

DWT层是DWS层的补充，主要解决“历史至今”的累积分析需求。比如用户从注册至今的总消费、商品从上架至今的总销量。

5.1 累积表的更新策略

累积表的最大挑战是如何高效更新。我常用的方法有：

全量快照：每天全量计算，适合数据量小的主题 增量合并：每天增量更新，适合数据量大的主题 拉链表：记录每条记录的生命周期，适合缓慢变化维度

-- 用户累积行为拉链表
CREATE TABLE dwt_user_behavior_chain (
  user_id BIGINT,
  start_date STRING COMMENT '记录开始日期',
  end_date STRING COMMENT '记录结束日期',
  is_current BOOLEAN COMMENT '是否当前有效',
  
  -- 累积指标
  total_login_count BIGINT COMMENT '累计登录次数',
  total_order_count BIGINT COMMENT '累计订单数',
  total_order_amount DECIMAL(20,2) COMMENT '累计消费金额',
  first_order_date STRING COMMENT '首次下单日期',
  last_order_date STRING COMMENT '最近下单日期',
  
  -- 当前快照
  current_login_count_30d INT COMMENT '最近30天登录次数',
  current_order_amount_30d DECIMAL(15,2) COMMENT '最近30天消费金额'
) COMMENT '用户行为累积拉链表'
PARTITIONED BY (start_date STRING)
STORED AS PARQUET;

-- 拉链表更新逻辑
WITH new_data AS (
  -- 当日用户行为汇总
  SELECT 
    user_id,
    login_count as daily_login,
    order_amount as daily_order_amount
  FROM dws_user_action_daily
  WHERE dt = '2024-01-15'
),
current_chain AS (
  -- 当前有效记录
  SELECT *
  FROM dwt_user_behavior_chain
  WHERE is_current = true
)
INSERT OVERWRITE TABLE dwt_user_behavior_chain PARTITION (start_date)
SELECT 
  user_id,
  start_date,
  end_date,
  is_current,
  total_login_count,
  total_order_count,
  total_order_amount,
  first_order_date,
  last_order_date,
  current_login_count_30d,
  current_order_amount_30d
FROM (
  -- 历史记录（非当前）
  SELECT * FROM current_chain WHERE is_current = false
  
  UNION ALL
  
  -- 更新当前记录为历史
  SELECT 
    c.user_id,
    c.start_date,
    '2024-01-14' as end_date,  -- 前一天为结束日期
    false as is_current,
    c.total_login_count,
    c.total_order_count,
    c.total_order_amount,
    c.first_order_date,
    c.last_order_date,
    c.current_login_count_30d,
    c.current_order_amount_30d
  FROM current_chain c
  LEFT JOIN new_data n ON c.user_id = n.user_id
  WHERE n.user_id IS NOT NULL  -- 当日有行为的用户
  
  UNION ALL
  
  -- 插入新的当前记录
  SELECT 
    COALESCE(c.user_id, n.user_id) as user_id,
    '2024-01-15' as start_date,
    '9999-12-31' as end_date,
    true as is_current,
    COALESCE(c.total_login_count, 0) + COALESCE(n.daily_login, 0) as total_login_count,
    COALESCE(c.total_order_count, 0) + CASE WHEN n.daily_order_amount > 0 THEN 1 ELSE 0 END as total_order_count,
    COALESCE(c.total_order_amount, 0) + COALESCE(n.daily_order_amount, 0) as total_order_amount,
    COALESCE(c.first_order_date, 
      CASE WHEN n.daily_order_amount > 0 THEN '2024-01-15' ELSE NULL END
    ) as first_order_date,
    CASE 
      WHEN n.daily_order_amount > 0 THEN '2024-01-15'
      ELSE c.last_order_date
    END as last_order_date,
    -- 最近30天指标需要重新计算（简化示例）
    COALESCE(n.daily_login, 0) as current_login_count_30d,
    COALESCE(n.daily_order_amount, 0) as current_order_amount_30d
  FROM current_chain c
  FULL OUTER JOIN new_data n ON c.user_id = n.user_id
) t;

5.2 滚动时间窗口的计算优化

滚动窗口计算（如最近7天、最近30天）是DWT层的常见需求。如果每次都从明细数据计算，性能会很差。我的优化方案是：

预计算滚动窗口：每天更新时，基于前一天的结果增量计算

-- 用户最近30天行为滚动表
CREATE TABLE dwt_user_action_rolling_30d (
  user_id BIGINT,
  dt STRING COMMENT '统计日期',
  
  -- 滚动窗口指标
  login_count_30d INT COMMENT '最近30天登录次数',
  order_count_30d INT COMMENT '最近30天订单数',
  order_amount_30d DECIMAL(15,2) COMMENT '最近30天消费金额',
  
  -- 每日明细（用于滚动计算）
  daily_login_count INT COMMENT '当日登录次数',
  daily_order_amount DECIMAL(10,2) COMMENT '当日消费金额',
  
  -- 历史队列（存储最近30天每日值）
  login_history ARRAY<INT> COMMENT '登录历史队列',
  amount_history ARRAY<DECIMAL(10,2)> COMMENT '消费历史队列'
) COMMENT '用户30天滚动行为表'
PARTITIONED BY (dt STRING);

-- 滚动窗口增量更新
INSERT OVERWRITE TABLE dwt_user_action_rolling_30d PARTITION (dt='2024-01-15')
SELECT 
  user_id,
  '2024-01-15' as dt,
  -- 计算最近30天总和
  CASE 
    WHEN SIZE(login_history) >= 30 
      THEN REDUCE(login_history, 0, (acc, x) -> acc + x, acc -> acc) - login_history[0] + daily_login
    ELSE REDUCE(login_history, 0, (acc, x) -> acc + x, acc -> acc) + daily_login
  END as login_count_30d,
  -- 类似计算其他指标...
  daily_login_count,
  daily_order_amount,
  -- 更新历史队列（保留最近30天）
  CASE 
    WHEN SIZE(login_history) >= 30 
      THEN SLICE(CONCAT(login_history, ARRAY(daily_login)), 2, 30)
    ELSE CONCAT(login_history, ARRAY(daily_login))
  END as login_history,
  -- 类似更新消费历史队列...
FROM (
  SELECT 
    r.user_id,
    COALESCE(d.login_count, 0) as daily_login_count,
    COALESCE(d.order_amount, 0) as daily_order_amount,
    COALESCE(r.login_history, ARRAY()) as login_history,
    COALESCE(r.amount_history, ARRAY()) as amount_history
  FROM (
    SELECT user_id, login_history, amount_history
    FROM dwt_user_action_rolling_30d
    WHERE dt = '2024-01-14'
  ) r
  LEFT JOIN (
    SELECT user_id, login_count, order_amount
    FROM dws_user_action_daily
    WHERE dt = '2024-01-15'
  ) d ON r.user_id = d.user_id
) t;

6. ADS层：应用数据服务与性能优化

ADS层是直接面向业务的数据服务层，这里的设计要特别考虑查询性能和数据新鲜度。

6.1 应用表的分类设计

根据使用场景，我把ADS层的表分为几类：

报表宽表：为固定报表准备，包含所有需要的字段

-- 日报表宽表
CREATE TABLE ads_daily_report (
  report_date STRING COMMENT '报表日期',
  dimension_type STRING COMMENT '维度类型',
  dimension_value STRING COMMENT '维度值',
  
  -- 流量指标
  pv BIGINT COMMENT '页面浏览量',
  uv BIGINT COMMENT '独立访客数',
  bounce_rate DECIMAL(5,4) COMMENT '跳出率',
  
  -- 交易指标
  order_count BIGINT COMMENT '订单数',
  order_amount DECIMAL(15,2) COMMENT '订单金额',
  payment_amount DECIMAL(15,2) COMMENT '支付金额',
  refund_amount DECIMAL(15,2) COMMENT '退款金额',
  
  -- 用户指标
  new_user_count BIGINT COMMENT '新用户数',
  active_user_count BIGINT COMMENT '活跃用户数',
  retention_rate DECIMAL(5,4) COMMENT '留存率',
  
  -- 商品指标
  sku_sold_count BIGINT COMMENT '商品销量',
  top10_products ARRAY<STRUCT<product_id:BIGINT, sales:INT>> COMMENT '热销商品TOP10',
  
  -- 数据时间
  update_time TIMESTAMP COMMENT '更新时间'
) COMMENT '日报表宽表'
PARTITIONED BY (report_date STRING)
STORED AS PARQUET;

API服务表：为数据接口准备，结构简单，查询快速

-- 用户画像API表
CREATE TABLE ads_user_profile_api (
  user_id BIGINT PRIMARY KEY,
  user_tags JSON COMMENT '用户标签',
  user_score DECIMAL(5,2) COMMENT '用户价值分',
  recent_behavior JSON COMMENT '近期行为',
  update_time TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB COMMENT '用户画像API表';

-- 创建索引加速查询
CREATE INDEX idx_update_time ON ads_user_profile_api(update_time);
CREATE INDEX idx_user_score ON ads_user_profile_api(user_score);

实时大屏表：为监控大屏准备，数据更新频繁

-- 实时交易大屏表
CREATE TABLE ads_realtime_dashboard (
  metric_time TIMESTAMP COMMENT '指标时间',
  metric_name STRING COMMENT '指标名称',
  metric_value DECIMAL(20,2) COMMENT '指标值',
  compare_value DECIMAL(20,2) COMMENT '对比值（昨日同期）',
  growth_rate DECIMAL(8,4) COMMENT '增长率'
) COMMENT '实时大屏表'
STORED AS PARQUET;

-- 使用事件时间窗口
WITH realtime_data AS (
  SELECT 
    WINDOWSTART as window_start,
    WINDOWEND as window_end,
    COUNT(*) as order_count,
    SUM(order_amount) as order_amount
  FROM kafka_orders
  GROUP BY TUMBLE(rowtime, INTERVAL '5' MINUTE)
)
INSERT INTO ads_realtime_dashboard
SELECT 
  window_end as metric_time,
  '5min_order_amount' as metric_name,
  order_amount as metric_value,
  LAG(order_amount, 12) OVER (ORDER BY window_end) as compare_value,  -- 昨日同期
  (order_amount - LAG(order_amount, 12) OVER (ORDER BY window_end)) / 
    NULLIF(LAG(order_amount, 12) OVER (ORDER BY window_end), 0) as growth_rate
FROM realtime_data;

6.2 查询性能优化策略

ADS层的查询性能直接影响用户体验。我常用的优化手段：

索引策略：

• 主键查询：使用聚簇索引
• 范围查询：使用B+树索引
• 多维度查询：使用联合索引
• 全文搜索：使用倒排索引

分区策略：

• 时间范围查询：按时间分区
• 地域查询：按地区分区
• 用户查询：按用户ID哈希分区

缓存策略：

• 热点数据：使用Redis缓存
• 计算结果：使用本地缓存
• 静态数据：使用CDN缓存

-- 多级分区表示例
CREATE TABLE ads_sales_detail (
  sale_id BIGINT,
  sale_time TIMESTAMP,
  user_id BIGINT,
  product_id BIGINT,
  sale_amount DECIMAL(10,2),
  region_code STRING,
  dt STRING COMMENT '日期'
) COMMENT '销售明细表'
PARTITIONED BY (
  dt STRING,  -- 一级分区：按天
  region_code STRING  -- 二级分区：按地区
)
CLUSTERED BY (user_id) INTO 10 BUCKETS  -- 分桶：按用户ID
STORED AS PARQUET;

-- 创建分区索引
ALTER TABLE ads_sales_detail ADD INDEX idx_sale_time (sale_time) LOCAL;
ALTER TABLE ads_sales_detail ADD INDEX idx_user_product (user_id, product_id) LOCAL;

6.3 数据同步与更新机制

ADS层的数据需要同步到各种查询引擎。我的同步方案：

批量同步：T+1数据，使用Sqoop/DataX 增量同步：小时级数据，使用Canal/Flink CDC 实时同步：秒级数据，使用Kafka Connect/Flink SQL

# 使用Sqoop同步到MySQL
sqoop export \
  --connect jdbc:mysql://mysql-host:3306/ads_db \
  --username ads_user \
  --password-file /etc/sqoop/password.txt \
  --table daily_report \
  --export-dir /user/hive/warehouse/ads.db/ads_daily_report/dt=2024-01-15 \
  --input-fields-terminated-by '\001' \
  --update-key report_date,dimension_type,dimension_value \
  --update-mode allowinsert \
  --batch

7. 数仓分层Checklist与质量监控

最后，我总结了一个数仓分层设计的Checklist，用于项目评审和日常检查：

7.1 分层设计Checklist

ODS层检查项：

• [ ] 数据是否保持原貌，未做业务清洗
• [ ] 分区策略是否合理（按天/月分区）
• [ ] 压缩格式是否统一（Parquet+Snappy）
• [ ] 敏感数据是否未脱敏（留到DWD层）
• [ ] 数据保留周期是否明确
• [ ] 同步监控是否到位（延迟、成功率）

DWD层检查项：

• [ ] 空值处理逻辑是否完整
• [ ] 异常值检测规则是否合理
• [ ] 数据去重策略是否正确
• [ ] 维度退化是否适度（不退化频繁变化字段）
• [ ] 时间字段是否统一格式
• [ ] 数据清洗报告是否每日生成

DWS层检查项：

• [ ] 主题划分是否清晰（用户/商品/交易）
• [ ] 宽表字段是否覆盖80%查询需求
• [ ] 聚合粒度是否合理（日/周/月）
• [ ] 更新策略是否高效（增量/全量）
• [ ] 数据冗余是否可控
• [ ] 查询性能是否达标（P95 < 3s）

DWT层检查项：

• [ ] 累积指标计算是否准确
• [ ] 拉链表更新逻辑是否正确
• [ ] 滚动窗口计算是否高效
• [ ] 历史数据回溯是否支持
• [ ] 存储增长是否可控

ADS层检查项：

• [ ] 查询性能是否满足SLA
• [ ] 索引设计是否合理
• [ ] 数据同步是否及时
• [ ] 缓存策略是否有效
• [ ] 监控告警是否完备

7.2 数据质量监控体系

我建立的数据质量监控包括几个维度：

完整性监控：关键字段非空率

-- 字段完整性监控
SELECT 
  dt,
  table_name,
  field_name,
  total_count,
  not_null_count,
  not_null_rate,
  CASE 
    WHEN not_null_rate < 0.99 THEN '告警'
    WHEN not_null_rate < 0.999 THEN '警告'
    ELSE '正常'
  END as status
FROM dwd_field_completeness_daily
WHERE dt >= DATE_SUB(CURRENT_DATE, 7)
ORDER BY dt DESC, not_null_rate ASC;

一致性监控：跨层数据一致性检查

-- 层间数据一致性检查
SELECT 
  check_date,
  '订单金额一致性' as check_item,
  ods_total_amount,
  dwd_total_amount,
  ABS(ods_total_amount - dwd_total_amount) as diff_amount,
  CASE 
    WHEN ABS(ods_total_amount - dwd_total_amount) / NULLIF(ods_total_amount, 0) > 0.001 
      THEN '不一致'
    ELSE '一致'
  END as status
FROM (
  SELECT 
    dt as check_date,
    SUM(order_amount) as ods_total_amount
  FROM ods_order_inc
  WHERE dt = '2024-01-15'
  GROUP BY dt
) ods
JOIN (
  SELECT 
    dt as check_date,
    SUM(order_amount) as dwd_total_amount
  FROM dwd_order_fact
  WHERE dt = '2024-01-15'
  GROUP BY dt
) dwd ON ods.check_date = dwd.check_date;

及时性监控：数据更新延迟监控

-- 数据更新延迟监控
SELECT 
  table_name,
  partition_value,
  expected_time,
  actual_time,
  TIMESTAMPDIFF(MINUTE, expected_time, actual_time) as delay_minutes,
  CASE 
    WHEN TIMESTAMPDIFF(MINUTE, expected_time, actual_time) > 60 THEN '严重延迟'
    WHEN TIMESTAMPDIFF(MINUTE, expected_time, actual_time) > 30 THEN '一般延迟'
    ELSE '正常'
  END as status
FROM ads_data_freshness
WHERE partition_value >= DATE_SUB(CURRENT_DATE, 3)
ORDER BY delay_minutes DESC;

准确性监控：关键业务指标波动检测

-- 指标波动检测
WITH daily_stats AS (
  SELECT 
    dt,
    AVG(order_amount) as avg_order_amount,
    STDDEV(order_amount) as std_order_amount
  FROM dwd_order_fact
  WHERE dt >= DATE_SUB(CURRENT_DATE, 30)
  GROUP BY dt
),
anomaly_detection AS (
  SELECT 
    dt,
    avg_order_amount,
    LAG(avg_order_amount, 1) OVER (ORDER BY dt) as prev_avg,
    std_order_amount,
    -- 3sigma原则检测异常
    ABS(avg_order_amount - LAG(avg_order_amount, 1) OVER (ORDER BY dt)) > 
      3 * LAG(std_order_amount, 1) OVER (ORDER BY dt) as is_anomaly
  FROM daily_stats
)
SELECT dt, avg_order_amount, prev_avg, is_anomaly
FROM anomaly_detection
WHERE is_anomaly = true;

这个监控体系帮我及时发现了很多数据问题，比如某天DWD层清洗规则误过滤了促销订单，导致交易额异常下降；还有一次ODS层同步程序异常，部分分区数据缺失。有了这些监控，问题能在半小时内被发现并定位。

数据仓库分层设计是个持续优化的过程，我在实际项目中会根据业务变化不断调整。比如随着业务增长，原来按天分区的表可能需要改为按小时分区；原来在DWD层退化的维度，可能因为业务分析需求变化需要调整。关键是要建立分层设计的思维框架，然后灵活应用。