得物交易域数据仓库数据质量保障体系建设

1、背景介绍

目前数仓测试，划分成交易、增长、社区等多个模块，不同的数仓测试域，都会有一名测试人员负责跟进，根据每个版本每个域资源实际投入情况，组内会适当的调整资源，以满足日常迭代需要；单交易域这块，版本迭代需求数，通常都要并行支持多个，且随着公司业务的发展，从承接的需求复杂度，或验证的指标量，都会有所提升，面对如此庞大的数据体量，在有限的时间/人力资源情况下，如何制定测试策略，保障数据质量按时上线，对我们测试人员而言，无疑挑战性是非常大的。基于此，本篇主要介绍、梳理、总结了在数仓测试实践过程中常用的一些方法，希望在以后相关的测试工作中，对大家有所帮助，可以避点坑；

2、业务范围

数仓交易域，数据测试范围涵盖了订单、履约、商家、商品、出价、库存、用户、寄存、财务、流量等多个模块，通常每个模块数仓这边都有维护一张或者多种对应的模型表。比如订单（ 子订单明细表），里面记录订单号，类型，状态，支付时间等基础信息；再比如履约（订单履约表），里面维护了订单号，商家，履约状态，未履约原因，未履约责任方等信息；

数仓这边，会在现有模型口径的基础上，进行日常迭代调整，同时根据prd需求不同，也会相应的新增宽表、指标，以满足业务需求；比如我们近期做核心指标宽表，需要基于商品spu维度，统计商品首次上架时间，动销商品数、出价商品数、曝光/点击uv等，需要汇总商品，订单，出价，流量等多个模块组合数据；

3、数据链路

在介入测试前，我们先简单的梳理下，数仓数据链路层次，自下到上，大致分为ods(源数据层)->dwd（数据清洗层）->dws(轻度汇总层)->ads/dm(数据应用层)，在生成最终结果表的过程中，也可能会使用到temp(临时层)和dim(维表层)，用于指标加工计算；

一般而言，标准数仓分为 ODS,DWD,DIM,DWS,ADS 等，且每层分工不同,每层具体有哪些功能，下面有详细的描述，大家可以了解下，有个整体的认知，已经熟悉的同学这部分可以跳过；

ODS：存储原始业务数据,数据原封不动同步到到ODS,不做任何修改，并且备份，备份时可以压缩；

DWD：数据清洗，脱敏，规范化，一般保持和ODS层一样的数据粒度，并且提供一定的数据质量保证。同时，为了提高数据明细层的易用性，该层会采用一些维度退化手法，将维度退化至事实表中，减少事实表和维表的关联,代表业务最小粒度层。任何数据的记录都可以从这一层获取，为后续的DWS做准备。另外，在该层也会做一部分的数据聚合，将相同主题的数据汇集到一张表中，提高数据的可用性；

DIM:    DWD同级别维度,比如时间维度、用户维度、权限维度、省份维度等；

DWS：又称数据集市或宽表。按照业务划分,比如订单，用户，商家，商品等，基于各个主题在加工和使用，进行轻度汇总,如统计各个主题7天，30天，90天的行为，用户购买行为，商品动销行为等，在DWD基础上关联DIM维度数据汇总，用于提供后续的业务查询，OLAP分析等；

ADS：要是提供给数据产品和数据分析使用的数据，一般会存放在 ES、ClickHouse、Redis等系统中供线上系统使用，也可能会存在 Hive 或者 Druid 中供数据分析和数据挖掘使用,一般在DWS基础上生成指标,主题宽表,主要用于具体的业务服务

TEMP：每一层的计算都会有很多临时表，专设一个temp层来存储我们数据仓库的临时表

对于质量把控来说，最核心的主要在于两个部分，dws层和ads层，原因如下：

ods的源数据同步及dwd层的数据清洗，目前datawork已经有相对完善的工作机制，可以保证数据质量，测试几乎可以不投入资源；

大部分数据加工、处理都是在dws层/ads层完成，而且相对于其他层级而言，日常改动、迭代更为频繁，同时出现问题的风险也比较大；

4、数据测试

4.1 数据质量保障流程

正常项目常规的流程，分析业务和需求->制定测试方案和测试计划->设计测试用例和准备测试数据->测试执行→生成测试报告→验收上线，数仓需求类似但又有所区别，如在需求评审阶段，我们更关注指标口径对齐，在口径明确的前提下，落到prd文档，开发才可以依据进行开发，测试作为标准进行验证。

从版本时间上，分别从移交测试前、冒烟/测试阶段、预发阶段、生产阶段，每个阶段关注的点不同，具体如下：

（1）移交测试前

指标口径对齐，举个非常简单的例子，统计商家半年以内全部品牌销售数量，测试前，如下口径点，都需要和产品/数分去沟通、明确；

• 口径对齐后，需要根据平时测试积累的口径，收集或开发对应的指标口径；
• Codeview ,在阅读开发代码的过程中，可以快速清楚口径的处理过程，相当于自己也在脑中重构了一次；

（2）冒烟/测试阶段

• 不同的需求，数据验证可能采取不同的验证方案，具体的在后续‘数据测试方案’中会详细描述；
• 数据完整性和准确性验证；

（3）预发阶段

• 回流需求，需要配合下游联调测试，打通全流程；
• 在测试通过后，需要整理相关的测试文档（含测试脚本，测试总结，结果，风险点等），供产品/数分/业务方验收参考；

（4）生产阶段阶段

• 上线前做，需要做一轮Codeview，确保发布的是最新脚本，check调度任务依赖没有遗漏，或者配错情况；
• 对于生产上的指标，特别是优先级比较高的，使用频繁的，需要在datawork里，配置DQC告警监控；
• 特殊情况下，比如口径不明确，业务方在验收过程中，也无法提供有效、合理的参考数据，需要和关联方一起评估上线方案；比如商家自运行项目，需要统计每个商家营收情况，业务方无法提供生产上真实的商户做验证，或者最多只能提供的一两个，也无法确保口径准确，为了避免客诉，一般通过开通商户白名单的方式，在数据稳定运行一段时间后，再全量放开；

4.2 数据测试方案

• 数仓需求，一般分两类，数分需求和回流需求，每类的测试方案都有所不同；

（1）数分需求

有数分介入，需明确业务口径，对齐后，作为测试验数参考，走DQC验证；

如统计商家销售成交明细，已提供了明确的业务/技术口径，可以编写DQC脚本，和对应的数仓报表口径进行比对；

（2）回流需求

没有数分介入，产品往往只能给到业务口径，具体的技术口径一般情况下是提供不了的，这时就要依赖平时积累的测试口径，需要自己写sql比对，和数仓报表数据进行校验；

以交易域需求为例，数分，研发，测试都有梳理沉淀具体的口径文档，在口径不明确的情况下，可以借鉴：

4.3 数据测试类型

• 大数据测试，简单的理解可以分两种类型，黑盒测试和白盒测试

（1） 黑盒测试

开发移交后，我们根据表名，就可以开始初步的测试，类似冒烟，这个环节对业务的口径不需要非常清楚就可以进行；

1. 检查目标表的表结构是否与设计文档一致
2. 主键是否唯一
3. 字段非空非null判断
4. 极值是否超出正常范围，如年龄类字段岁数大于200
5. 枚举值检查数据是否合理分布
6. 对应字段和字段内容是否一致，防止数据落表存在乱序情况
7. 占比类型字段值是否大于100%
8. 金额类字段是否存在负数情况
9. 数据是否有效合理，比如同分区下，卖家近7天成交订单量比近30天成交量还多的情况

• 唯一性

--主键，无重复记录
SELECT  主键ID
        ,count(1)
FROM    表名
WHERE   pt = '${bizdate}'
GROUP BY 主键ID
HAVING  count(1) > 1
;

• 为空判断

--空值判断，无异常空值数据
SELECT  字段1,字段2 
FROM    表名
WHERE   pt = '${bizdate}'
and     (字段1='' OR  字段2='')
;

• 为null判断

--空值判断
SELECT  字段1,字段2 
FROM    表名
WHERE   pt = '${bizdate}'
and     ((字段1 IS NULL OR  字段2 IS NULL)
;

• 枚举值判断

--枚举类型字段，比如只有0，1两种状态
SELECT  distinct(枚举类型字段)
FROM    表名
WHERE   pt = '${bizdate}' 
；

• 占比值判断

--占比值类型字段，比如转化率字段，无大于1的异常数据
SELECT  转化率字段
FROM    表名
WHERE   pt = '${bizdate}'
and    转化率字段>1
;

• 负值判断

--比如价格类型字段值，无负值情况
SELECT  价格
FROM    表名
WHERE   pt = '${bizdate}'
and    价格 <0
;

• 有效判断

--比如同一商品近7天的销售量是否存在大于商品近14天的销售量的异常数据
SELECT  近7天的销售量，近14天的销售量
FROM    表名
WHERE   pt = '${bizdate}'
and    近7天的销售量>近14天的销售量
;

（2）白盒测试

需要对开发的代码走读，check指标处理逻辑。同时测试也需要准备验证脚本，或者查找到可以作为验证参考的数据，便于口径核对，这个环节，对测试人员的指标口径沉淀有一定的要求。在发现指标数据存在差异的情况，需要协助开发人员一起定位差异原因，时常需要在现有的口径基础上，在数仓空间往上翻多层，或者一个指标定义不够清晰，需要自行去数分空间查找口径定义。另外，在测试通过后，需要编写相应的DQC脚本，及时监控生产数据质量。这些对测试来说，需要有一定的sql功底；

• check字段长度，最大最小值，异常值，边界值等
• 指标逻辑处理口径，需要验证关联约束条件和where条件约束是否和prd一致，是否满足业务需求
• 指标默认值设置是否合理；
• 涉及到占比类型字段时，开发脚本是否有考虑分母为0为null的情况；
• 计算单位是否统一；.常用的函数，特别是datediff、dateadd等时间函数，往往会导致时间范围存在偏差，导致指标值对不上的情况；
• 数仓上线表任务调度check，主要关注是否缺少依赖；
• DQC脚本编写，配置；

白盒测试阶段，常见的开发问题汇总

• 字段未做默认处理，数值字段一般默认为0，字符串默认为‘’；

,nvl(spu_inv_num_7day,0)              as  近七天_在售商品数        -- 近七天_在售商品数
    ,spu_inv_num_30day                  as  近30天_在售商品数       -- 近30天_在售商品数 
    ,spu_cnt_30day                         as  近30天_动销商品数       -- 近30天_动销商品数

• 过滤条件遗漏

以下面为例，统计卖家任务发货当天的订单量，需加上id_del判断，剔除无效数据。

SELECT  t1.卖家号
        ,TO_CHAR(t1.订单时间,'yyyyMMdd') AS 订单时间
        ,COUNT(t1.订单号) OVER (PARTITION BY t1.卖家号,t1.任务号,TO_CHAR(t1.订单时间,'yyyyMMdd') ) AS 卖家_任务_发货当天的订单 -- 卖家+任务+发货当天的订单                           
FROM    test1 t1 --过滤没有删除的订单
WHERE   t1.is_del = 0
;

以下面为例，统计卖家历史订单量和gmv，因为数仓目前统计的是T+1的数据，所以需要过滤掉当天跨零点数据；

--改动前
SELECT  用户号
        ,用户信息
FROM    test
WHERE   pt = '${bizdate}'
；
  --需调整为：
SELECT  *
FROM    (
            SELECT  用户号
                    ,用户信息
                    ,ROW_NUMBER() OVER (PARTITION BY 用户号 ORDER BY 用户创建时间 DESC ) AS rn
            FROM    test
            WHERE   pt = '${bizdate}'
        ) 
WHERE   rn = 1
；

• 表关联关系如果是1：1，关联时，如果关联健不唯一，那么关联会产生笛卡尔，导致数据膨胀。

以下面为例，商家表同一个用户号可能有多条数据，如果主表根据用户号会导致结果数据膨胀；

--改动前
  NVL(在线出价商品缺货数,0) / 在线商品出价数 
  --调整后
  CASE WHEN 在线商品出价数 IS NULL OR 在线商品出价数 = 0 THEN 0  ELSE NVL(在线出价商品缺货数,0) / 在线商品出价数 end

• 未考虑分母为0/空的情况

以下面为例，在线出价商品缺货率=在线出价商品缺货数/在线商品出价数，需要加上分母为0/空的情况，给到默认值0；

-- 近7天_实际支付金额GMV
sum(case when to_char(支付时间,'yyyymmdd') BETWEEN TO_CHAR(DATEADD(TO_DATE('${bizdate}','yyyyMMdd'), -7,'dd'),'yyyyMMdd') AND '${bizdate}' then coalesce(支付金额 ,0) end ) / 100     as 近7天_实际支付金额

• 函数规则使用有误

如统计近7天_实际支付金额GMV指标，使用的是DATEADD函数，统计近7天数据，需往前推6天，对应的前置条件应调整为‘-6’

4.4 常用的测试方法

（1）DQC对比

• 通过现有的数据/口径作为预期，通过一定的方式关联，直接和开发新表/口径做对比验证即可，可以大大的减少测试时间，测试效率非常高；

适用场景：

• 迁移、重构类需求，同一张表每个字段对应的口径差异不大，甚至是完全相同的，我们一般采取的方式是，根据主键进行关联新老表，相同的指标值预期应该一致，进行全量比对，如有差异数据记录，需要逐条分析排查；

例：卖家履约数据迁移需求为例，根据卖家id+履约统计时间为组合维度，校验迁移前后卖家履约率是否一致；

--全量个人商家履约数据明细验证
SELECT  卖家id
        ,履约统计时间
        ,t1.履约率 AS 新逻辑履约率
        ,t2.履约率 AS 旧逻辑履约率
FROM    (
            SELECT  卖家id
                    ,履约统计时间
                    ,履约率
            FROM    test1
            WHERE   pt = '${bizdate}'
        ) t1
INNER JOIN  (
                SELECT  卖家id
                        ,履约统计时间
                        ,履约率
                FROM    test2
                WHERE   pt = '${bizdate}'
            ) t2
ON      t1.卖家id = t2.卖家id
AND     t1.履约统计时间 = t2.履约统计时间
WHERE   t1.履约率 <> t2.履约率
；

• 目前很多数仓迭代需求，往往都可以在数分报表平台找到对应的指标数据，验证的时候可以直接复用具体的表具体的字段，或者没有现成的，如果只要简单的处理一下，也可以进行dqc验证；

（2）多维度对比

• 为满足业务需求，数仓这边开发的报表，往往同一个指标，有多重维度计算，且每层计算对应的值不一样，这种情况下，可以先验证一组维度数据准确性；然后基于测试通过的维度组指标作为参考组，可以快速的验证其他维度；

适用场景：新增报表需要聚合多维度指标数据

例：卖家核心指标需求为例，需要统计在仓库存数、出价卖家数、提交订单量、履约订单量、笔单价等100+个指标值，每个指标都要在不同统计维度下，如卖家类型+三级类目+品牌、卖家类型+一级类目+品牌、卖家类型+一级类目等情况下计算相应的数据，同时又分为日，周，月维度报表，单一个出价卖家数指标就有3（时间维度）*9（统计维度）=27种情况，如果在加上指标个数100+的话，需要验证的指标条数就多达2700条，显然没有这么多资源去验证，用这种方法，可以大大的提升我们测试验证时效；

（3）表间横向数据对比

• 表间横向对比可以理解为两张表或多张表之间，其中具有业务关联或者业务含义一致的字段，可以用来做数据对比：

例：订单费率迁移项目，费率迁移后，基于订单维度，订单侧t1表、t2表、t3表这3张表的，每笔跨境订单费率数据应该保持全部一致，如存在差异数据，需要拉出明细，和开发，关联方一一确认影响；

--统计表1的订单费率DROP TABLE IF EXISTS du_temp.t1;CREATE TABLE IF NOT EXISTS du_temp.t1 ASSELECT  订单号        ,订单跨境费率FROM    t1WHERE   pt = '${bizdate}';
--统计表2的订单费率DROP TABLE IF EXISTS du_temp.t2;CREATE TABLE IF NOT EXISTS du_temp.t2 ASSELECT  订单号        ,订单跨境费率FROM    t2WHERE   pt = '${bizdate}';
--统计表3的订单费率DROP TABLE IF EXISTS du_temp.t3;CREATE TABLE IF NOT EXISTS du_temp.t3 ASSELECT  订单号        ,订单跨境费率FROM    t3WHERE   pt = '${bizdate}';
--全量差异扫描，异常订单告警数据SELECT  t1.订单号        ,t1.订单跨境费率 AS t1_订单跨境费率        ,t2.订单跨境费率 AS t2_订单跨境费率        ,t3.订单跨境费率 AS t3_订单跨境费率FROM    du_temp.t1 t1INNER JOIN du_temp.t2 t2ON      t1.订单号 = t2.订单号INNER JOIN du_temp.t3 t3ON      t1.订单号 = t3.订单号WHERE   t1_订单跨境费率 != t2_订单跨境费率OR      t1_订单跨境费率 != t3_订单跨境费率;

（4）表内横向数据对比

• 表内横向对比可以理解为同一张表内，业务上相关联的两个或多个字段，他们存在一定的逻辑性关系，那么就可以用来做数据对比；

例1：同一个商品，正常来说，浏览量>=加入购物车>=生成订单>=支付订单>=完成交易，对于订单部分，实际业务下单量肯定大于支付量，编写sql如下：

select  提交订单量,支付订单量
FROM    test
WHERE   pt = '${bizdate}'
and 提交订单量 < 支付订单量;

例2:商家统计月内，应履约订单量满足以下条件，等于（实际履约量+超时未发货量+虚假量+鉴定未通过量+其他卖家原因而关闭的订单量）,这些字段都落履约表了，就可以直接对比，编写sql如下

--统计差异商家履约数据
SELECT  商家id
        ,统计月份
        ,应履约订单量
        ,实际履约量 + 超时未发货量 + 虚假量 + 鉴定未通过量 + 其他卖家原因而关闭的订单量 AS 预计应履约订单量
FROM    test
WHERE   pt = '${bizdate}'
AND     统计月 IN ('202105','202106','202107')
AND     应履约订单量 <> 实际履约量 + 超时未发货量 + 虚假量 + 鉴定未通过量 + 其他卖家原因而关闭的订单量
;

（5）execl对比

• 如果需要测试的报表字段个数太多，或者指标处理逻辑比较复杂，通过sql不好处理，那么可以考虑常用的execl表格工具，在处理上面两种情况下，见效非常快；

例1：核心报表需求，多个迭代版本需要验证的新指标数有1000+，如果按照以前的方法，验证起来会非常吃力，需要编写的测试脚本，验证数据工作量都非常巨大，如果使用execl，对于口径明确的情况下，只需要一、两个简单的select脚本，就可以将数据指标数据放到表格里，通过自动的if函数做个判断就行，可以快速核对指标，且后续也方面开发对齐修复，数分验收起来，也可以大大的缩短时间；

例2：财务补贴需求，新增两个指标，平台实收操作服务费和平台实收技术服务费，看似非常简单的一个需求，但实际处理起来，需要涉及到10多个原有的财务指标关联计算，且指标之间又存在依赖关系，加上计算过程中涉及到乘除，在统计订单数据较多的情况下，很容易因为精度问题，导致最终结果存在失真情况。如果按照prd需求进行口径验证，测试要编写上千行代码，光脚本就要花费1天（整个需求测试估时：1.5天），在和开发报表数据做对比，因为都存在上述的失真情况，差异数据排查起来，需要把计算逻辑一层一层的比对，验证起来非常耗时；

但通过execl处理，只要将对应的计算因子数据放到里面，通过工具本身自带的函数，可以快速的得到预期结果；

5、生产数据质量监控

不同行业有不同的评估数据质量的标准。一般来说，数据质量可以从完整性、准确性、一致性和及时性共四个角度进行评估。

• 完整性是指数据的记录和信息是否完整，是否存在数据缺失情况。数据缺失主要包括记录的缺失和具体某个字段信息的缺失，两者都会造成统计结果不准确。完整性是数据质量最基础的保障.
• 准确性是指数据中记录的信息和数据是否准确、是否存在异常或者错误的信息。例如，订单中出现错误的买家信息等，这些数据都是问题数据。确保记录的准确性也是保证数据质量必不可少的一部分。
• 一致性通常体现在跨度很大的数据仓库中。例如，某公司有很多业务数仓分支，对于同一份数据，在不同的数仓分支中必须保证一致性。例如，从在线业务库加工到数据仓库，再到各个数据应用节点，用户ID必须保持同一种类型，且长度也要保持一致。
• 及时性保障数据的及时产出才能体现数据的价值。例如，决策分析师通常希望当天就可以看到前一天的数据。若等待时间过长，数据失去了及时性的价值，数据分析工作将失去意义。

目前线上数据质量监控，数仓测试这边大多是通过DQC（Data Quality Center）数据质量中心配置进行，通过配置数据质量校验规则，自动在数据处理任务过程中进行数据质量方面的监控，根据离线任务的运行情况实时决策是否告警、何时告警、告警方式、告警给谁；具体的配置，使用方式，数据部门已经有了非常详细的操作文档，我就不过多介绍，感兴趣的可以直接拿来看看；

6、总结

数据校验的方式有多种多样，以上只是汇总了数仓测试过程中常用到的一些方法，实际应用中，还需要结合具体的需求，方法选取得当，可以起到事半功倍的效果。个人觉得，数据类测试，非常考验人的耐心，面对繁杂的指标，需要花费更多的时间，静下心来，去不断梳理、总结、沉淀，慢慢打磨形成一套可以作为自身验证标准的方法论，也只有在不断的熟悉本身业务的过程中，才能提升测试人员本身对数据敏感性，从而降低数据质量风险；

目前除了dqc生产配置，可以自动监控数据质量运行情况，日常迭代数仓测试过程，大多数情况还是通过人工去核对数据，在后续工作里，希望可以结合公司现有的业务，探索出更多可以提效的数据验证方法，测试比对工具，降低数据对比的成本，不断的完善现有的数据测试体系，持续保障数仓质量。