緩やかに変化する値を遡って分析したい

# TL;DL
* データマネジメントにおいて、緩やかに変化する値は"Slowly Changing Dimension"と呼ばれる
* アプリケーション側のDBが値を上書きする仕様になっていた場合、過去の値を遡って分析することが出来ない
* "Type-2 Slowly Changing Dimension" と呼ばれる形式でログテーブルを作成すればOK

# 「緩やかに変化する値」の具体例
広告配信を例に考えます。

クリック課金の広告配信において、広告主(`user_id`)は「1Clickに最大いくらまで払えるか」という上限額(`max_cpc`)を設定します。
DBには以下のような値が格納されています。

| user_id | max_cpc | created_at | updated_at |
| -------- | -------- | -------- | -------- |
| 1 | 30 | 2022-03-01 09:00:00 | 2022-03-01 09:00:00 |

広告配信の効果感が良いので、広告主は3月3日昼12時に「もっと高いお金を払ってもいいのでより広告を出したい」と思い、`max_cpc`を30円から60円に変更します。  
DBが値を上書きする仕様になっていた場合、DBは以下のようになります。

| user_id | max_cpc | created_at | updated_at |
| -------- | -------- | -------- | -------- |
| 1 | 60 | 2022-03-01 09:00:00 | 2022-03-03 12:00:00 |

`max_cpc`が30円から60円になり、`updated_at`の値が3月3日12時になりました。  

このように緩やかに設定値が変わっていくカラムを、データマネジメントの世界では"Slowly Changing Dimension"と呼びます。
詳細: https://zenn.dev/pei0804/articles/slowly-changing-dimensions

# 課題
アプリケーションの機能を提供する上では上記実装に問題はありませんが、過去の値が上書きされているため、テーブルの過去の状態を復元出来ません。  
ただ、当該のカラムが「いつ」「どんな値だったのか」が分析上重要になることがあります。  
そこで、過去の値を遡って分析可能なログテーブルを設計します。  

過去の値のログが無いのであれば、まずログを貯めるところから始めましょう。


# 解決策&実装例
今回は、過去の値を分析しやすい形で格納する、スナップショットテーブルを設計します

以下の2点に分けて解説します。
* スナップショットテーブルの作成
* 分析に使ってみる

データベースはBigQueryを想定しています。

## スナップショットテーブルの作成
### やりたいこと
アプリケーション側のテーブル(`Before`)をもとに、[Type-2 Slowly Changing Dimensions](https://en.wikipedia.org/wiki/Slowly_changing_dimension#Type_2:_add_new_row) と呼ばれる形式のスナップショットテーブル(`After`)を作ります。

**Before**
| user_id | max_cpc | created_at | updated_at |
| -------- | -------- | -------- | -------- |
| 1 | 30 | 2022-03-01 09:00:00 | 2022-03-01 09:00:00 |

↓ 上書き

| user_id | max_cpc | created_at | updated_at |
| -------- | -------- | -------- | -------- |
| 1 | 60 | 2022-03-01 09:00:00 | 2022-03-03 12:00:00 |

**After**
| user_id | max_cpc | valid_from | valid_to |
| -------- | -------- | -------- | -------- |
| 1 | 30 | 2022-03-01 09:00:00 | 2022-03-03 12:00:00 |
| 1 | 60 | 2022-03-03 12:00:00 | 2999-12-31 23:59:59 |


(余談) 最新のログの`valid_to`はNULLのままでもいいですが、JOINが複雑になるので絶対に取り得ない大きな値を入れておきます。  
分析用途のテーブルにおけるNULLの扱いについては以下を参考にしてください。私もNULLを除きたい派です。  
https://zenn.dev/pei0804/articles/donot-use-nullable-in-star-schema

### 実装例
まずは、アプリケーション側のテーブルを用いてスナップショットテーブルの初期状態を作成します。
```sql
CREATE TEMP FUNCTION maxDatetime() AS (DATETIME("2999-12-31T23:59:59"));

CREATE TABLE IF NOT EXISTS `snapshots` AS (
SELECT
  user_id
  , max_cpc
  # snapshot作り方参考: https://docs.getdbt.com/docs/building-a-dbt-project/snapshots
  , updated_at AS valid_from
  , maxDatetime() AS valid_to
FROM
  `log`
)
```

次に、差分更新を行うバッチクエリを定期的に実行します。

```sql
BEGIN TRANSACTION;

CREATE TEMP FUNCTION maxDatetime() AS (DATETIME("2999-12-31T23:59:59"));

# 何か変更が行われている(過去のログテーブルに存在しない)ログを取得
# これをログテーブルに追加する
CREATE TEMP TABLE `rows_to_append` AS (
  SELECT
    user_id
    , max_cpc
    , updated_at AS valid_from
    , maxDatetime() AS valid_to
  FROM
    `aplication_table` AS `at`
  WHERE NOT EXISTS (
    SELECT
      user_id
      , max_cpc
      , valid_from AS updated_at
    FROM
      `snapshots` AS `s`
    WHERE
      at.user_id = s.user_id
      AND at.max_cpc = s.max_cpc
      AND at.updated_at = s.valid_from
  )
);

# ログテーブルに追加されるキャンペーンの最新ログを取得
# これらのログは最新ではなくなるので"valid_to"を変更する
CREATE TEMP TABLE `rows_to_modified` AS (
  SELECT
    s.user_id
    , s.max_cpc
    , s.valid_from
    , rta.valid_from AS valid_to
  FROM
    `snapshots` AS `s`
  INNER JOIN `rows_to_append` AS `rta` USING(user_id)
  WHERE
    # 最新のログを持ってくる
    bcs.valid_to = maxDatetime()
);

# 最新ではなくなったログの"valid_to"をmaxDatetime()から追加予定のログの"updated_at"に変更
UPDATE `snapshots` AS `s`
SET valid_to = rtm.valid_to
FROM `rows_to_modified` AS `rtm`
WHERE 
  s.user_id = rtm.user_id
  AND s.valid_from = rtm.valid_from;

# 最新ログを追加
INSERT INTO `snapshots`
SELECT * FROM `rows_to_append`;

COMMIT TRANSACTION;
```

これでスナップショットテーブルの更新が出来ました。  
更新バッチを実行する度に値の変更履歴が追記されます。

スナップショットテーブルの更新頻度は注意が必要です。  
例えば日毎に更新をする場合、**同じ日に値が複数回変わっても最後の変更値しか取得出来ません。**  
一方で、差分を検知する間隔を狭めるほどクエリ発行コストがかかります。

## 分析に使ってみる
スナップショットテーブルが出来たので、早速分析に使ってみましょう。  
分析したいテーブルの`timestamp`をスナップショットテーブルの`valid_from`と`valid_to`で挟み込んであげればOKです。  

例えば、以下のような形式で広告クリックログがあるとします。  
ログ1行 = クリック1回とします。

| user_id | timestamp |
| -------- | -------- |
| 1 | 2022-03-01 09:00:00 |
| 1 | 2022-03-03 09:03:00 |
| ... | ... |

支払い上限価格を変更する毎の、1時間あたりのImpression数を集計してみます。

```sql
SELECT
  l.user_id
  , s.max_cpc
  , s.valid_from
  , s.valid_to
  , SAFE_DIVIDE(COUNT(*), TIMESTAMP_DIFF(s.valid_to, s.valid_from, HOUR)) AS count_click_per_hour
FROM
  `click_log` AS l
LEFT JOIN `snapshots` AS s
  ON l.user_id = s.user_id
  # click_logテーブルのtimestampをsnapthosテーブルで挟み込むのがキモ
  AND l.timestamp >= s.valid_from
  AND l.timestamp < s.valid_to
ORDER BY
  s.valid_from
```

| user_id | max_cpc | valid_from | valid_to | count_click_per_hour |
| -------- | -------- | -------- | -------- | -------- |
| 1 | 30 | 2022-03-01 09:00:00 | 2022-03-03 12:00:00 | 10000 |
| 1 | 60 | 2022-03-03 12:00:00 | 2022-03-07 18:00:00 | 30000 |
| ...|...|...|...|...|

支払い上限額を30円→60円に上げることで、1時間あたりの広告クリック数が10000回から30000回に増えていることがわかります。

# 終わりに
ログテーブルを作成するのはそれなりにコストがかかるため、**重要なカラムに絞ってピンポイントにログを作る**のがいいと思います。  
アプリケーション側のテーブルの持ち方を変える選択肢もありますが、分析用途でしか使わない場合はシュッとスナップショットテーブルを作ってしまいましょう。  

過去履歴を考慮した分析を行うことで、データの力をより引き出しましょう。

# 参考
dbt の snapshots 機能  
https://docs.getdbt.com/docs/building-a-dbt-project/snapshots

Slowly Changing Dimension  
https://zenn.dev/pei0804/articles/slowly-changing-dimensions

Star Schema
https://www.amazon.co.jp/Schema-Complete-Reference-Christopher-Adamson/dp/0071744320