SCD Type2のテーブルスナップショットを利用したシステムテスト
この記事はBigQuery Advent Calendar 2023 18日目の記事です。
アナリティクスエンジニアリングにおいて、as-was分析を実現する手段としてスナップショットの利用が普及していると思いますが、エンジニアリングの観点からの開発プロセス改善にもつなげることができます。
SCD Type2 によるスナップショット
BigQueryにおいでは"スナップショット"という機能名で実現される、いくつかの手段が用意されていますが
この記事では BigQuery機能そのもののスナップショット機能ではなく、SCD Typ2によるスナップショットを利用していきます。
この機能自体は BigQuery では提供されているわけではなくデータ構築フレームワークであるdbtやdataform、各種フレームワークを通じて提供されています。(参考)
この機能はBigQuery Script単体でも実現することができます。 過去の記事
SCD Type2のテーブルの特徴を説明しておくと、 対象の分析元の通常のテーブルのカラムに加えて
valid_from, valid_to の有効期間を表すカラムをもつことで、as-was分析を可能にしています
Type 2では対象の元テーブルに対して、変化のあったレコードレベルでの記録を行う。
先の例でいえば、Type 2では次のように valid_from, valid_to の 有効期間のカラムを導入することで実現する。
station_id valid_from valid_to name status location ... 12345 timestamp A null 品川駅 Open 品川 ... 12346 timestamp B null 高輪ゲートウェイ駅 Open 五反田 ... 12346 timestamp A timestamp B 高輪ゲートウェイ駅 Close 五反田 ...
SCD Type2 スナップショットの基本的な利用
この記事では上記の操作をBigQuery Scriptで利用できるようにした手前みそで簡潔化した
bqmake.v0 ユーティリティを用いて、スナップショット操作を行っていきます。
準備
今回の記事用にデータセットの準備をしておきます。
-- データセットの準備
create schema if not exists `bq20231218__snapshot`;
スナップショットのテーブルの初期化のために次のクエリを実行します。
このクエリを打つことで、SCD Type2用の記録用のテーブルやTVFを構築することができます
-- SCD Type-2用のデータの準備
declare unique_key, query string;
-- SCD Type2 のパラメータの設定
set (unique_key, query) = ("key", "select 1 as key, 'initial_value' as value");
-- SCD type2用の初期化: アクセステーブルや内部リポジトリの用意
call `bqmake.v0.snapshot_table__init`(
(@@project_id, "bq20231218__snapshot", "example_1")
, (unique_key, query, "2023-12-18 10:00:00")
, null
);
最新データを参照する
bqmake.v0.snapshot_table__initで用意されたテーブルは、次のように利用することができます。
-- 最新のデータを再現する
select * from `bq20231218__snapshot.example_1`;
/*----+-----------------+
| key | value |
+-----+-----------------+
| 1 | "initial_value" |
+-----+-----------------*/
データ更新
スナップショットとしてデータ更新を行うときには bqmake.v0.snapshot_table__update を利用します。
declare unique_key, query string;
set (unique_key, query) = ("key", "select 1 as key, 'updated_value' as value");
-- 定期実行することで差分データが記録できる
call `bqmake.v0.snapshot_table__update`(
(@@project_id, "bq20231218__snapshot", "example_1")
, []
, (unique_key, query, "2023-12-18 20:00:00")
, null
);
select * from `bq20231218__snapshot.example_1`
/*----+-----------------+
| key | value |
+-----+-----------------+
| 1 | "updated_value" |
+-----+-----------------*/
特定の日時の結果を参照する
-- 特定日時のデータを再現する
select * from `example_1`("2023-12-18 15:00:00");
/*----+-----------------+
| key | value |
+-----+-----------------+
| 1 | "initial_value" |
+-----+-----------------*/
日時別 結果を生成する
SCD Type2型のモデリングを採用するすることで、日付別テーブルを生成することができます。
容易されたタイムラインTVFを利用し、次のように生成できます。
-- 特定日時のデータを再現する
select
date(_at) as date
, *
from `bq20231218__snapshot.example_1__timeline`(
generate_timestamp_array(
"2023-12-18 10:00:00"
, "2023-12-20 10:00:00"
, interval 24 hour
)
);
/*-----------+----------------------+-----+-----------------+
| date | _at | key | value |
+------------+----------------------+-----+-----------------+
| 2023-12-18 | 2023-12-18T10:00:00Z | 1 | "initial_value" |
| 2023-12-19 | 2023-12-19T10:00:00Z | 1 | "updated_value" |
| 2023-12-20 | 2023-12-20T10:00:00Z | 1 | "updated_value" |
+------------+----------------------+-----+-----------------*/
システム開発においてテーブルスナップショット利用
ここまでで SCD Type2の基本的な利用方法を紹介してきましたが
このSCD Type2のスナップショットを分析用途だけでなく、システム開発時などでも用いることで強力な機能になります。
例えば、次の2つのようなシナリオが考えられることができます。
-
出力データのスナップショット: 複雑なSQLやワークフローを変更した場合の最終結果の差分が見れることは、レビュープロセスを進めるうえで役に立ちます。
-
出力テーブルのプロファイル結果のスナップショット: テーブルの差分では変更量が多い場合には、出力データの差分では良し悪しがしづらくなります。この場合にはレコード量や各種統計量を算出するクエリ出力に対してスナップショットをとることで、その変化を比較することができます。
このどちらも特定のクエリ結果については、スナップショット時に生成されるクエリを応用する
比較用クエリを記述することで確認がしやすくなります。
with
reference as (
-- `bigquery-public-data.austin_bikeshare.bikeshare_stations` に対して
-- 比較元となる集計結果を用意する
select
status
, count(1) as n_station
from `bigquery-public-data.austin_bikeshare.bikeshare_stations`
group by status
)
, update_data as (
with latest as (
-- `bigquery-public-data.austin_bikeshare.bikeshare_stations` に対して
-- 比較先となる集計結果を用意する
select
status
-- 比較結果を変えるために +1を加算する
, count(1) + 1 as n_station
from `bigquery-public-data.austin_bikeshare.bikeshare_stations`
group by status
)
select
trip_id as unique_key
, revision_hash
, @timestamp as valid_from
, timestamp(null) as valid_to
, entity
from
latest as entity
, (select as value generate_uuid()) as revision_hash
)
select
unique_key
, action
, R.revision_hash as base_revision
, if(
action in ('CHANGED')
, array(
select as struct
key, u.value as after, r.value as before
from unnest(R_entries) as r
left join unnest(U_entries) as u using(key)
where r.value is distinct from u.value
)
, []
) as entity_changes
, if(R.unique_key is not null, [R.entity], [])
|| if(U.unique_key is not null, [U.entity], [])
as entity_comparison
-- , diff
from reference as R
full join update_data as U using(unique_key)
left join unnest([struct(
format('%t', R.entity) != format('%t', U.entity) as will_update
-- 比較結果の判定
, case
when U.unique_key is not null and R.unique_key is not null
then if(
format('%t', R.entity) != format('%t', U.entity)
, 'CHANGED'
, 'UNCHANGED'
)
when U.unique_key is not null and R.unique_key is null
then 'NEW'
when U.unique_key is null and R.unique_key is not null
then 'DELETE'
else error('UNKNOWN')
end as action
, `bqmake.v0.zjson_entries_recursive`(to_json_string(R.entity)) as R_entries
, `bqmake.v0.zjson_entries_recursive`(to_json_string(U.entity)) as U_entries
)])
where
action in ('CHANGED', 'NEW', 'DELETE')
/*-----------+----------+----+---------------------+--------------------------------------+
| unique_key | action |... | entity_changes | entity_comparison |
+------------+-----------+----+---------------------+--------------------------------------+
| "active" | "CHANGED" | | [{ | [ |
| | | | "key":"n_station", | {"status":"active","n_station":78} |
| | | | "after":"79", | {"status":"active","n_station":79} |
| | | | "before":"78" | ] |
| | | | }] | |
+------------+-----------+----+---------------------+--------------------------------------+
| "closed" | "CHANGED" | | [{ | [ |
| | | | "key":"n_station", | {"status":"closed","n_station":24}, |
| | | | "after":"25", | {"status":"closed","n_station":25}, |
| | | | "before":"24" | ] |
| | | | }] | |
+------------+-----------+----+---------------------+--------------------------------------*/
上記の操作のためには複雑なSQLを用意する必要がありますが
汎用利用できるようにこれをプロシージャにしておくことで、システムテスト用際には簡潔に利用することができるようになります。
この一連の操作をプロシージャ化したbqmake.v0.assert_golden として用意しています。
これを次のように書くことができます。
declare query1, query2 string;
-- デプロイ時には、スナップショットのデータ更新をする
set query1 = """
select
status
, count(1) as n_station
from `bigquery-public-data.austin_bikeshare.bikeshare_stations`
group by status
""";
call `bqmake.v0.assert_golden`(
(@@project_id, "bq20231218__snapshot", "profile_table")
, query1
, "status"
, is_update => true
);
-- テスト時には、スナップショットを比較しデータ更新された場合にエラーとなる
set query2 = """
select
status
-- 比較結果を変えるために +1を加算する
, count(1) + 1 as n_station
from `bigquery-public-data.austin_bikeshare.bikeshare_stations`
group by status
""";
call `bqmake.v0.assert_golden`(
(@@project_id, "bq20231218__snapshot", "profile_table")
, query2
, "status"
, is_update => false
);
--> Assertion failed
このようなBigQuery ScriptをCIに組み込むことで、不用意なデータの変更や破壊を防ぐことができるようになります。
assertionのために生成した比較テーブルを Pull Requestに貼り付けることで、さらに効率的にデータ確認を行うことができます。
Discussion