AI・アナリティクス本部の田中です。
データアナリスト11名、データマネージャ4名が所属する事業推進部を管掌しています。
@takagiyudai の勧めで、ZOZOのデータマネジメントにおける秘伝のタレ（通称：差分検証クエリ）を公開したいと思います。
髙木さんのアドカレ記事はこちら↓
BigQueryで実行することを前提としていますが、お使いのDWHに合わせて書き換えることで流用可能です。
ユースケース
SQLを記述する業務をしていると、既存の検証済み.sqlファイルを改修する機会がしばしばあります。
その際、意図通りに改修できているか確認するために、大まかには2つの観点で出力データを検証します。
- 改修箇所は、意図通りの出力になっているか
- 改修箇所以外に、意図しない変化が起こっていないか
ここで紹介するのは「2. 改修箇所以外に、意図しない変化が起こっていないか」を検証するためのスクリプトです。
改修前後のデータをインプットし、明細データを突合して以下に分類することができます。
| 分類結果 | 分類 |
|---|---|
| Check OK(match) | 改修前後でレコード一致 |
| added(dev) | 改修前になく、改修後にはあるレコード |
| lost(dev) | 改修前にあって、改修後にないレコード |
| mismatch | 改修前後でレコード不一致 |
| duplicate(prd) | 改修前にレコード重複 |
| duplicate(dev) | 改修後にレコード重複 |
| duplicate(prd+dev) | 改修前後ともレコード重複 |
| mismatch + duplicate(prd) | 改修前後でレコードが不一致＋改修前にレコード重複 |
| mismatch + duplicate(dev) | 改修前後でレコードが不一致＋改修後にレコード重複 |
| mismatch + duplicate(prd+dev) | 改修前後でレコードが不一致＋改修前後にレコード重複 |
出力例
例えば、以下のような出力を得られる既存の.sqlファイルがあるとします。
| shop_name | goods_name | order_price |
|---|---|---|
| ショップA | 商品X | 400,000 |
| ショップB | 商品Y | 500,000 |
| ショップC | 商品Z | 600,000 |
このクエリに商品ページ閲覧数(goods_pv)のカラムを追加する改修を加えます。以下が期待出力です。
| shop_name | goods_name | order_price | goods_pv |
|---|---|---|---|
| ショップA | 商品X | 400,000 | 1,000 |
| ショップB | 商品Y | 500,000 | 2,000 |
| ショップC | 商品Z | 600,000 | 3,000 |
上記の期待出力に対し、改修に失敗して以下のような意図しない出力が得られているとしましょう。
| shop_name | goods_name | order_price | goods_pv |
|---|---|---|---|
| ショップA | 商品X | 400,000 | 1,000 |
| ショップA | 商品X | 400,000 | 1,000 |
| ショップC | 商品Z | 500,000 | 3,000 |
| ショップD | 商品Q | 300,000 | 4,000 |
この場合、以下のような出力が得られます。
差分サマリ
| check_pattern | pk_cnt |
|---|---|
| added(dev) | 1 |
| duplicate(dev) | 1 |
| lost(dev) | 1 |
| mismatch | 1 |
差分明細データ
| env | check_pattern | duplicate_records | shop_name | goods_name | order_price | pk |
|---|---|---|---|---|---|---|
| 1.prd | duplicate(dev) | ショップA | 商品X | 400,000 | {"shop_name":"ショップA","goods_name":"商品X"} | |
| 2.dev | duplicate(dev) | 2 | ショップA | 商品X | 400,000 | {"shop_name":"ショップA","goods_name":"商品X"} |
| 2.dev | duplicate(dev) | 2 | ショップA | 商品X | 400,000 | {"shop_name":"ショップA","goods_name":"商品X"} |
| 1.prd | lost(dev) | ショップB | 商品Y | 500,000 | {"shop_name":"ショップB","goods_name":"商品Y"} | |
| 1.prd | mismatch | ショップC | 商品Z | 600,000 | {"shop_name":"ショップC","goods_name":"商品Z"} | |
| 2.dev | mismatch | ショップC | 商品Z | 500,000 | {"shop_name":"ショップC","goods_name":"商品Z"} | |
| 2.dev | added(dev) | ショップD | 商品Q | 300,000 | {"shop_name":"ショップD","goods_name":"商品Q"} |
この結果の解釈は、意図せず以下が発生していることになります。
- ショップA-商品X のレコードが重複
- ショップB-商品Y のレコードが欠損
- ショップC-商品Z のレコードでorder_priceが不一致
- ショップD-商品Q のレコードが追加
差分検証クエリ
こちらが秘伝のタレです。ご賞味ください。
prd_data と dev_data のCTEを編集するだけで、即実行可能です。
8つのCTEの下段にあるメインのSELECT句は、以下の用途に応じてコメントアウト部分を修正してください。
- 差分サマリ ... どのような差分があるか、全体を把握
- 差分明細データ ... 予期せぬ差分があった場合は、明細を確認
with prd_data as
(
SELECT * except(__column_except) -- 更新日時などの必ず差分の出る項目を除外
, to_json_string(struct(__column_pk,__column_flat)) as pk --STRUCT,ARRAY項目を考慮したサロゲートキーを生成
FROM `__project__.__dataset.__table` --本番環境のテーブル
-- left join unnest(__column_struct-array) as __column_flat -- 構造体や配列を展開して検証する場合のみ記述
)
,dev_data as
(
SELECT * except(__column_except) -- 更新日時や追加項目などの必ず差分の出る項目を除外
, to_json_string(struct(__column_pk,__column_flat)) as pk --STRUCT,ARRAY項目を考慮したサロゲートキーを生成
FROM `__project__.__dataset.__table` --開発環境のテーブル
-- left join unnest(__column_struct-array) as __column_flat -- 構造体や配列を展開して検証する場合のみ記述
)
,check_data as
(
select 'prd' as env, * from prd_data
union all
select 'dev' as env, * from dev_data
)
--欠損の確認
,lost_record_check as
(
select pk
,1 as lost_record_flag
,count(1) as records
from check_data
group by pk
having records = 1 --どちらかの環境にしかない
)
--差分のあるレコードの特定
,diff_record_check as
(
select pk
,1 as diff_record_flag
,count(1) as records
from (select distinct * except(env) from check_data)
group by pk
having records > 1 --環境を跨いで一意にしたとき、差分がある
)
--主キーで一意でないレコードの特定（本来不要）
,duplicate_record_check_env as
(
select env
,case when env = 'prd' then 'dev'
when env = 'dev' then 'prd'
else 'error'
end as opposite_env --相対する環境の重複チェック用結合キー項目
,pk
,1 as duplicate_record_env_flag
,count(1) as check_duplicate
from check_data
group by env
,pk
having check_duplicate > 1
)
--主キーで一意でないレコードの特定（本来不要）
,duplicate_record_check as
(
select distinct
pk --いずれかの環境で重複を起こしているレコードのキー
,1 as duplicate_record_flag
from duplicate_record_check_env
)
,diff as
(
select case when check_data.env = 'prd' then '1.prd'
when check_data.env = 'dev' then '2.dev'
else 'error'
end as env
,case when lost_record_flag is null and diff_record_flag is null and duplicate_record_flag = 1 and duplicate_record_check_env.duplicate_record_env_flag = 1
and duplicate_record_check_env_otherside.duplicate_record_env_flag = 1 then 'duplicate(prd+dev)'
when lost_record_flag is null and diff_record_flag is null and duplicate_record_flag = 1 and duplicate_record_check_env.duplicate_record_env_flag = 1
and duplicate_record_check_env_otherside.duplicate_record_env_flag is null then concat('duplicate(',duplicate_record_check_env.env,')')
when lost_record_flag is null and diff_record_flag is null and duplicate_record_flag = 1 and duplicate_record_check_env.duplicate_record_env_flag is null
and duplicate_record_check_env_otherside.duplicate_record_env_flag = 1 then concat('duplicate(',duplicate_record_check_env_otherside.env,')')
when lost_record_flag is null and diff_record_flag = 1 and duplicate_record_flag = 1 and duplicate_record_check_env.duplicate_record_env_flag = 1
and duplicate_record_check_env_otherside.duplicate_record_env_flag = 1 then 'mismatch + duplicate(prd+dev)'
when lost_record_flag is null and diff_record_flag = 1 and duplicate_record_flag = 1 and duplicate_record_check_env.duplicate_record_env_flag = 1
and duplicate_record_check_env_otherside.duplicate_record_env_flag is null then concat('mismatch + duplicate(',duplicate_record_check_env.env,')')
when lost_record_flag is null and diff_record_flag = 1 and duplicate_record_flag = 1 and duplicate_record_check_env.duplicate_record_env_flag is null
and duplicate_record_check_env_otherside.duplicate_record_env_flag = 1 then concat('mismatch + duplicate(',duplicate_record_check_env_otherside.env,')')
when lost_record_flag is null and diff_record_flag = 1 and duplicate_record_flag is null then 'mismatch'
when lost_record_flag = 1 and diff_record_flag is null and duplicate_record_flag is null and check_data.env = 'dev' then 'added(dev)'
when lost_record_flag = 1 and diff_record_flag is null and duplicate_record_flag is null and check_data.env = 'prd' then 'lost(dev)'
else 'Check OK(match)'
end as check_pattern
,duplicate_record_check_env.check_duplicate as duplicate_records
,check_data.* except(env)
from check_data
left join lost_record_check
on check_data.pk = lost_record_check.pk
left join diff_record_check
on check_data.pk = diff_record_check.pk
left join duplicate_record_check_env
on check_data.env = duplicate_record_check_env.env
and check_data.pk = duplicate_record_check_env.pk
left join duplicate_record_check_env duplicate_record_check_env_otherside
on check_data.env = duplicate_record_check_env_otherside.opposite_env
and check_data.pk = duplicate_record_check_env_otherside.pk
left join duplicate_record_check
on check_data.pk = duplicate_record_check.pk
)
-- 差分サマリ
select check_pattern
,count(distinct pk) as pk_cnt
from diff
group by check_pattern
order by check_pattern
-- 差分明細データ
-- select *
-- from diff
-- where check_pattern != 'Check OK(match)'
-- order by diff.pk, diff.env
最後に
ZOZOではデータガバナンスチームとして、データエンジニア / アナリティクスエンジニア / データマネージャが密に協力しながらデータ活用の推進に取り組んでおり、日々進化を続けています。
今後の情報発信にもご期待ください。