この記事は、Magic Moment Advent Calendar 2023 16 日目の記事です。

クリスマスまであと8日となりました。
今年もターキーを焼くことになったので、この記事が公開される頃には恐らくターキーさんが自宅に届いているはずです。
冷凍の丸鶏は解凍するのに3日ほどかかるし味の漬け込み時間も必要なので、1週間弱くらいは見て計画しておく必要があります！

さて、今回はデータ活用をするための履歴データの残し方について今年は考えることが多かったので、その辺りをざっくりとまとめて今年2023年を締めくくろうかなと思います。
(記事執筆も計画的に進めたいところですが、いつもギリギリになりがち・・)

この記事を書くきっかけ

私は現在、Magic Moment Playbookという営業活動に関するSaaSを運営している企業でデータエンジニアというポジションで仕事をしています。

サービスの提供範囲が拡大していく中、データ分析やデータ可視化、機械学習やLLMの活用など、様々なデータ活用を行っていきたいという機運が高まっていますが、肝心のデータがないことによってできない分析手法が発生することも残念ながら多々あります。

もちろんSaaSとしてやっているので、アプリケーションとして絶対的に必要となる「今」の情報は豊富にあります。しかし「いつ誰が何をどんな値からどんな値に変更したのか」までのデータ、つまり変更履歴のデータとなると残っていないケースも存在しているのが現状です。

データ活用では履歴データが非常に重要

ここで言っている「データ活用」とは、主にデータの可視化や機械学習などを指していますが、これらのデータ活用手法では主に「過去の実績から未来の予想をする」であるとか「過去の実績からとるとよいと考えられる最適な行動を予想する」などに集約できることが多いのではないでしょうか。前者の予想はデータ可視化によるトレンドや傾向の分析など、後者の最適行動の予測は「過去の販売実績から何を売るとよいのか」や「過去のマッチング履歴から最適なマッチング」などの予測系の機械学習になります。これらの未来予測は非常にビジネスと相性が良く活用しやすいです。

このように、 「過去のデータ変遷をいかに克明に記すことができるか」 によって、できる未来予測の幅や質に大きくかかわることになります。

履歴データを残すのは面倒だが・・

活用以前に今まさに世に出していこうとしているアプリケーションを動かすためのデリバリーを最優先したい事情はあるでしょう。
そんな時に、いちいち履歴などという過去にこだわっている場合ではない！なんてことはよくある話です。
しかし過去データがないと過去からの予測はできません。
「愚者は経験に学び、賢者は歴史に学ぶ」ということで、過去データをしっかり残せるようにいつかは考えたいものです。

履歴データの残し方を分類する

以上のような経緯で、2023年末の時点でどんな方法がありえそうかを色々調べたり、過去の経験も照らし合わせて方法論をできるだけ体系的に並べたのが下記の表になります。これよりも良い方法を知っている方がおられましたらご指摘いただけると嬉しいです！

結局、私としては以下のような方法しかないのではないかという結論に至りました。

どこで解決するか	考え方	方法論	概要
アプリケーションDBで	論理設計で解決する	イミュータブルデータモデリング	テーブルを「リソース」と「イベント」に分割し UPDATE処理を極力排除する
		バイテンポラルデータモデリング	データ履歴を厳密に管理できるよう各テーブルに履歴レコード含めて保存し各レコードに「有効期間」と「トランザクション時刻」カラムを付与する
	物理設計・アーキテクチャで解決する	SQL:2011テンポラルデータベース機能を利用する	バイテンポラルのカラムや検索方法など一連をRDBMSにお任せで管理してもらう
		履歴テーブルを設け、データの作成・変更・削除の都度コピーする	アプリケーション実装やDBトリガーなどで頑張ってコピーする
分析基盤のデータレイクなど外部DBで	定期的に保存	定期的にスナップショットコピーする	ソースDBのテーブル群を日次等で分析基盤へスナップショットとしてデータ基盤のテーブルやパーティション等へ保存する
	半リアルタイムに保存	CDCを利用した製品等で半リアルタイムデータコピー	fivetranや各種クラウドでサポートされているCDC機能を利用して履歴を半リアルタイムコピー

大別すると、大体上記のように分類されるのではないかと思います。
分析基盤側で履歴データが見られれば良いという考え方もありますが、アプリケーションDBの中で履歴をしっかり残せればアプリケーション内だけでダッシュボード機能や分析機能などの提供がしやすくなるなど、プロダクト自体の品質向上に向けたメリットが大きいと思います。¹
そこで今回は、分析基盤での解決方法ではなくアプリケーションDBの中で行えるモデリングや物理設計にフォーカスをし、以降はそちらについてキーワードや概要を紹介していきたいと思います。

イミュータブルデータモデリング

イミュータブルモデリングとは、RDBにおけるテーブルのUpdateを極力排除することで、データの変更履歴が失われないようにするデータモデリングの考え方です。
これが確実に行われれば、不意に失われる変更履歴データはなくなるはずです。なぜならばアプリケーションDBから情報が失われるのは「UPDATEのタイミングだから」です。DELETE、TRUNCATE、DROPなどは意図的に行われるはずなので問題にはなりません。過去を失うタイミングとしてはUPDATEが一番危ういのです！！

このイミュータブルデータモデリングの考え方について恐らく一番詳しいのは「WEB+DB PRESS vol130」にあった特集記事なのではないかと思います。
手っ取り早く知るには、こちらのWeb記事も参考になるでしょう。

イミュータブルデータモデリングのポイント

すべてのテーブルは「リソース」と「イベント」の2種類に分類が可能
リソースはモノの管理。「会社」とか「社員」とか「部署」、「商品」、「顧客」などです。
イベントはコトの管理。「配属」とか「販売」、「請求」、「出荷」、などです。
各テーブルに複数の「日時」カラムが入り込まないようにリソースからイベントテーブルへ分割していく
- 「日時」カラムがあるということはそのテーブルの背後に必ず固有の「イベント」があるはずなのでイベントテーブルとして別テーブルへ括りだします。
- これを繰り返すことで、UPDATEの存在しない非常にクリーンな世界が訪れるのです！！(半分ウソ。後でこの件は回収します)

リソースから分割されたイベントテーブルが即ち不変の履歴データに相当するデータになり永続化される ことになります。
例えば、以下のようなテーブルは日時を表すカラムがたくさんあるので、上記ルールから複数のエンティティに切り出せるはずです。

以下のように日にちを起点にエンティティの分割ができるはずです。

こうしてみるとRDBの正規化とかなり似ているのですが、「イベントとリソースという考え方」「日時カラムに着目してエンティティに分割する」というところから、より実務に適用しやすくキャッチーな捉え方ができ設計上のルール化がしやすいかなと思います。

また、先程の「半分ウソ」については、リソースについてはUPDATEを許容することも必要です。
例えば今回の会員テーブルに関しては、氏名が変わるケースがありこれを 氏名変更処理 のようなイベントとして捉えることもできますが、この履歴管理がビジネス上あまり重要でないのであればUPDATEを許容してしまった方がRDBの応答性能を確保しやすいです。イベント履歴を須らく残すということはレコードが大量に増えるという負の側面があります。このようなトレードオフがあるので何でもかんでもイベントに括りだして履歴を厳密に保持し続けるのも無理が生じてくることに注意は必要です。システム開発の現場では当たり前かもしれませんが費用と効果のバランスを常に考える必要があります。

以上のような方法論でモデリングを行うことでUPDATEが極力発生しない状況が作れます。

少なくともイベントからはUPDATEを完全排除できる
リソースには極力不変な属性のみをもたせるようにイベントテーブルへ分割を検討(リソースからのUPDATE排除はベストエフォートで)
- 但し、RDBの応答速度パフォーマンスとのトレードオフを考えバランスをとることも重要

昨今ではプログラミングやDocker等インフラなどもイミュータブル性が重要視されていますが、RDBの世界もイミュータブルがやはりクリーンであり厳密であり、ということで管理性が高いということなのだろうと思います。

テンポラルデータベース

テンポラルデータベースという概念を利用したデータ管理を行うと、履歴データの時刻に関するかなり詳細な履歴を残すことができるようになります。

テンポラルデータベースについては、Wikipediaの「Temporal database」にこの概念についての詳しい説明があります。

テンポラルデータベースはSQL2011で標準SQLとして仕様策定されているのだが・・

テンポラルデータベースはSQL2011にて標準SQLとして仕様が策定されています。
SQL2011のテンポラルテーブル仕様では、後述するテンポラルデータベースのタイプで言う「Uni-temporal」か「Bi-temporal」を扱えることが謳われています。

しかし2023年時点では各種RDBMS製品にてあまり実装状況は良くないようです。(Oracle/DB2/SQLServerなど商用系のRDBMSでは使えるが、OSS系はMariaDBくらいで、PostgreSQLは公式にはなくパッチを当てることでテスト利用可能という程度らしい)

履歴管理をする上で切り札にもなりえる機能なので、広く実装が進むことに私は期待しています。

下記のような詳細な時刻情報をRDBMSとして管理するのがテンポラルデータベースであり、これを利用すると、特定時刻のテーブルデータがいつでも見られる環境が手に入ります。(もっと厳密なBigQueryのタイムトラベル機能のようなもの)

テンポラルデータベースが特徴的に取り扱う情報

1.Valid time(有効時間)
- 履歴レコードが有効な期間(FromとTo)
2.Transaction time(トランザクション時間)
- 履歴レコードがDBに実際に登録された時刻
3.Decision time(決定時刻)
- 履歴レコードをDBに登録することが決まった時刻(登録したかった時刻)

テンポラルデータベースのタイプ(レベル)

下に行けば行くほど厳密な時刻管理を持った履歴を残せます。（ですが管理は大変になります）

Uni-temporal：上記 1 or 2 を扱う
Bi-temporal：　上記 1 and 2 を扱う
Tri-temporal：　上記 1 and 2 and 3 を扱う

※レベル3のTriは決定時刻があり若干過剰な厳密さなので、現実的には「バイテンポラル」という選択になっているように見えます。

テンポラルデータベースの概要

テンポラルデータベースでは、各テーブルのレコードと共に上記3つの時刻が登録される。
UPDATEの代わりに各テーブルのレコードが上記3つの時刻とともに履歴としてINSERTされる。
テーブルのレコードをPKで引く場合に、SQL＋時刻情報で、いつの時点のレコードが欲しいのかを指定する
これにより、特定の時刻の履歴レコードがいつでも取得できるDBとなります。

レコードの挙動のイメージ

1月1日に社員ID15の「山田花子さん」を社員テーブルに登録
- ID: 15、有効期間="1月1日～ ∞"、トランザクション=1月1日
1月3日に山田花子さんが結婚して「川口花子」さんに変わっていたことが2月1日の時点で分かった
- ID: 15、山田花子：有効期間="1月1日～ 1月2日"、トランザクション=1月1日
- ID: 15、川口花子：有効期間="1月3日～ ∞"、トランザクション=2月1日

このようにすることで、社員テーブルを検索するときは、PK＋時刻があればいつの時点のデータでも取れることになる ということです。有効期間だけでなくトランザクション期間のデータもあるので、1月3日～2月1日までの間は前の姓で会社生活していたことも後から追うことができます。

詳しくは、以下のサイトなどが詳しいので、参照頂ければと思います。

以上のようなことがデータベースの機能として実現されるのがテンポラルデータベースです。
例えばPostgreSQLのまだ一般公開まではされていないテンポラルデータベース拡張機能の場合の利用感はこちらのページに書かれているので見てみてください。
※但し、2023年末の現時点ではPostgreSQLのテンポラルDB拡張機能では「有効期間」しか対応できていないので、Uni-temporalのレベルまでであることに注意してください。²

完全に上記サイトの引用ですが、以下のような構文でSQLを実行するようです。
starttime と endtimeというカラムが自動管理されている様子が見えます。

テンポラルテーブル作成の例(PostgreSQL※要拡張機能パッチ)

postgres=# create table t1 ( a int primary key generated always as identity
                           , b text ) 
                           with system versioning;
CREATE TABLE
postgres=# d t1
                                      Table "public.t1"
  Column   |           Type           | Collation | Nullable |            Default           
-----------+--------------------------+-----------+----------+-------------------------------
 a         | integer                  |           | not null | generated always as identity
 b         | text                     |           |          | 
 starttime | timestamp with time zone |           | not null | generated always as row start
 endtime   | timestamp with time zone |           | not null | generated always as row end
Indexes:
    "t1_pkey" PRIMARY KEY, btree (a, endtime)

レコードのINSERT/UPDATEの様子(PostgreSQL※要拡張機能パッチ)

postgres=# insert into t1 (b) select md5(a::text) from generate_series(1,10) a;
INSERT 0 10
postgres=# update t1 set b = random()::text;
UPDATE 10
postgres=# select * from t1 for system_time from '-infinity' to 'infinity' order by a;
 a  |                b                 |           starttime           |            endtime            
----+----------------------------------+-------------------------------+-------------------------------
  1 | c4ca4238a0b923820dcc509a6f75849b | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  1 | 0.42111793538855835              | 2021-08-24 16:16:55.417076+02 | infinity
  2 | c81e728d9d4c2f636f067f89cc14862c | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  2 | 0.5479128803753532               | 2021-08-24 16:16:55.417076+02 | infinity
  3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  3 | 0.5512468293024142               | 2021-08-24 16:16:55.417076+02 | infinity
  4 | a87ff679a2f3e71d9181a67b7542122c | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  4 | 0.4112741522472554               | 2021-08-24 16:16:55.417076+02 | infinity
  5 | e4da3b7fbbce2345d7772b0674a318d5 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  5 | 0.46017420469036807              | 2021-08-24 16:16:55.417076+02 | infinity
  6 | 1679091c5a880faf6fb5e6087eb1b2dc | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  6 | 0.3495216613664702               | 2021-08-24 16:16:55.417076+02 | infinity
  7 | 8f14e45fceea167a5a36dedd4bea2543 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  7 | 0.2657576876373895               | 2021-08-24 16:16:55.417076+02 | infinity
  8 | c9f0f895fb98ab9159f51fd0297e236d | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  8 | 0.9808748465536858               | 2021-08-24 16:16:55.417076+02 | infinity
  9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  9 | 0.4533070845652887               | 2021-08-24 16:16:55.417076+02 | infinity
 10 | d3d9446802a44259755d38e6d163e820 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
 10 | 0.20914767879762408              | 2021-08-24 16:16:55.417076+02 | infinity
(20 rows)

テンポラルデータモデリング

テンポラルデータベースは、各RDBで実装されていなければRDBMSの機能としては利用できませんが、そもそもはデータ管理の考え方なので、自前で同じことを行えれば データモデリングの1手法として 応用することはできます。
記録が重要な金融系などでは使いどころは十分にあると思います。

おわりに

今回はデータ活用のための履歴データの残し方について、データモデリングの観点でまとめてみました。
「データは新たな石油である」などとも言われますが(言われていた？)、そもそもの記録されているデータが不十分ですと原油にすらなれずにただの泥水のようになってしまいます。手間はかかれどうまく精製できる形で残していけるよう頑張っていきたいと思います。
また同志のみなさま、頑張っていきましょう！

明日のアドベントカレンダーは @morishin さんの「開発チームの一体感を出すために専用のバーチャル背景をつくろうとしたら、いつの間にかシェルスクリプトを触っていた話」です。

もちろん分析基盤側で履歴管理を行うことにもメリットはあります。一番大きいところは開発工数の観点と開発組織的な観点だと思います。履歴という複雑なものを分析基盤側へ押し込むことでの疎結合にできるので工数削減につながる可能性がある。また組織的な観点では、履歴を一番欲しがるのはデータ分析に関わる人間なのでアプリ開発チームとは別チームだったりすることが多く、(近視眼的な)利害関係の観点でも分離した方が良いケースもあるかと思います。コンウェイの法則と捉えてもいいかもしれない。 ↩
SQL:2011に次の記載がある。「PostgreSQL requires installation of the temporal_tables extension. Temporal Tables Extension supports the system-period temporal tables only, but does not follow the SQL:2011 design.」 ↩

データ活用のための履歴データの残し方を考える(アプリケーションDBモデリング編)