拉链表的数据质量

发表于 分类于 Waline：

在使用上游提供的表的时候，我们默认是信任的。但现实往往不如人意，上游表的质量不能保证，需要自己先检验再使用。最近我们的数据出现问题，排查到源头是上游一个拉链表出现了数据重复/重叠。

解决方法是我们下游设置一个Data Quality Rule，每天对上游的这个拉链表进行检测，如果发现重叠/重复，就发邮件给Owner解决。

一个正常的拉链表应该如同user_id = 101的数据这样衔接，而不是像user_id = 102 这样时间范围有重叠，从业务角度来说，一个用户在同一个时间点不能有两个互斥状态（比如一个用户，在同一个时刻即是男又是女）。

user_id status_cd start_ts end_ts
101 A 2022-01-01 00:00:00 2022-01-08 00:00:00
101 B 2022-01-08 00:00:00 2022-01-12 00:00:00
101 C 2022-01-12 00:00:00 NULL
102 A 2022-01-01 00:00:00 2022-01-08 00:00:00
102 B 2022-01-06 00:00:00 2022-01-10 00:00:00
102 C 2022-01-10 00:00:00 NULL

检测Overlap：

1
2
3
4
5
6
7
8
select
*
from table t1
join table t2
on t1.user_id = t2.user_id
where t1.start_ts < t2.end_ts
and t2.start_ts < t1.end_ts
;

正常情况下，是没有数据可以被查询出来的，如果有重叠的情况则可以查出重叠的数据。

检测Duplicate：

1
2
3
4
5
6
7
8
9
select
     user_id
   , start_ts
   , end_ts
   , count(1) as cnt
from table
group by 1,2,3
having cnt > 1
;

如果某用户有多条起止日期相同的记录，则会被查询出来。

可以用上述SQL设置为Daily DQ Rule来监测上游的数据质量。