Zenn
👁️

CDCと SCDについて

2024/10/15に公開
cdc
SCD
tech

はじめに: 本記事を書いた経緯

はじめまして。f11eと申します。
普段は都内のお客様先でデータエンジニアリングのお手伝いをさせてもらっております。
最近業務外ではありますがdbtによるデータ変換処理が遅いという課題を検証する機会がありまして、
その原因の一つが単純に毎日full refreshでデータを処理していたからというものでした。
そこについてはincremental設定を行い必要なデータだけ処理を行えばいいのですが、調べていくうちにその仕組みとしてCDCやSCDという概念が関係していることがわかりました。
ワードとして聞いたことがあったのですが詳細についてはよく分からなかったことから、調査した内容についてここに記載することにしました。

CDC (Change Data Capture) とは

概要

CDC (Change Data Capture) とはデータソースで発生した変更(=挿入・変更・削除)を検知するためのデータ処理パターンです。
例えば下記図のように注文システム、在庫システム、配送システム、顧客管理システムなどからCDCプロセスがすべてのデータソースの変更を検知し、それらの変更をデータウェアハウスに集約します。集約されたデータを使って分析が行われ、その結果がレポートやダッシュボードとして提供されるという流れになっています。
構成によってはリアルタイムに近いデータ分析も可能となります。

個人的にはこれをパッと見た際にAWSのDynamoDB Streamが思い浮かんだので検索してみたところ、「Change data capture for DynamoDB Streams」というまんまなページを見つけました。

パターン別詳細

Fivetranによると、CDCには下記の種類があるとのことです[1]

  • ログベース
  • トリガーベース
  • タイムスタンプベース
  • 差分ベース

ログベース

RDBMSにおけるトランザクションログを利用して変更を検知するパターンです。
トランザクションログとは、データの整合性を確保し障害復旧などを行うために保管しているデータへのアクションを記録したログとなります[2]。
このログを利用することによって、テーブルの変更履歴を追うことができます。
使用するRDBMSごとにトランザクションログにアクセスするためのインターフェースが異なるので、統合的に行うことのできるCDCツールを利用するのが一般的です。

ツール例

簡単にいうと、Insert/Update/Deleteがコミットされるとそれを検知してディスティネーションのシステム（DWHやデータレイクなど）にロードしてくれる仕組みと思っておけば良さそうです。
リアルタイムに変更を検知したい場合はログベースを選択することになります。

構成例はこんな感じでしょうか

トリガーベース

Insert, Update, Deleteで発火するトリガーを作成し、別テーブルに履歴データを保管するという方法になります。

Fivetranの記事[1:1]によると

The trigger-based approach was preferred before log-based solutions were developed. Nowadays, organizations want to avoid the impact of triggers on database processing.

トリガーベースのアプローチは、ログベースのソリューションが開発される前は好まれていました。現在では、組織はデータベース処理へのトリガーの影響を避けたいと考えています。

とのことなので現在ではログベースを選択する方が良さそうです。

タイムスタンプベース

これはUpdatedAtやModifiedAtというような変更日付列を作り、最終更新日時から処理対象を検知するパターンとなります。

ID ProductName Price UpdatedAt
1 Widget A 10.99 2024-10-13 09:15:00
2 Gadget B 24.99 2024-10-14 11:30:00
3 Tool C 15.50 2024-10-12 14:45:00
4 Device D 49.99 2024-10-15 10:00:00

というテーブルがあり、最後のCDC操作が2024-10-13 12:00:00だとします。

SELECT *
FROM products
WHERE UpdatedAt > '2024-10-13 12:00:00'  -- 最後のCDC操作の時刻
ORDER BY UpdatedAt ASC;

上記SQLによって以下ような更新情報が取得でき、定期的なデータ同期やレポートのためのデータ抽出として利用できます。

ID ProductName Price UpdatedAt
2 Gadget B 24.99 2023-10-14 11:30:00
4 Device D 49.99 2023-10-15 10:00:00

次の定期実行時には今回の操作時刻を条件に指定し、データを抽出するような形となります。
タイムスタンプベースの弱点としては、次回取得までの間に複数回データが更新された場合に中間状態は追えないのと、物理削除されたデータは追えないことにあります。

差分ベース

まずとある時点のスナップショットを取得し、また別の時点でスナップショットを新たに取得します。
差分ベースCDCは両者のスナップショットを比較した際の差分を検知する方法となります。

Fivetranの記事[1:2]によると、小規模なデータ量で正確なトランザクションの一貫性を要求しないケース（中間状態をとるのは難しいため）に適するとのことです。
ただし記事にもあるとおり、

Relatively small data sets when other options are not available.

小規模なデータセットで、他の選択肢が使えない場合に選択し得るパターンとなるため積極的に採用したい場面はあまりなさそうです。

選定

現実的にはログベースとタイムスタンプベースの二択からの選択になりそうです。

要件 ログベース タイムスタンプベース
リアルタイム/ニアリアルタイム性が必要 ✅ ❌
完全な変更履歴（Insert,Update,Delete）が必要 ✅ ❌
データソース側のスキーマ変更を許容できない ✅ ❌※
CDCツール用追加インフラの導入・運用コストを許容できない ❌ ✅
実装の容易性
（追加インフラの有無、既存ETLへの組み込み、DBへの依存から判断）		 ❌ ✅

※RDBMSによってはシステム列を利用することでデータソース側のスキーマ変更することなく導入可能(PostgreSQLのXMINなど)

SCD (Slowly Changing Dimension) とは

概要

SCD(Slowly Changing Dimension）とは、現在のデータと履歴データを保存・管理するためのディメンションテーブルです。
先ほどのCDCが変更を追跡するための手段だったのに対し、こちらは変更データの保存・管理手段となります。
SCDには3つのパターンと派生・発展系があります。
この記事では3パターンを紹介しますが、派生・発展系についてはWikipedia[3]に詳細が載っているのでそちらを参照して下さい。

パターン別詳細

Type:1 (上書き)

SCDのType1は、新しいデータを既存のデータに上書きするものです。
このパターンは履歴を追跡することができなくなります。

例えば、以下のような顧客ディメンションテーブル（DimCustomer）があったとします：

CustomerID CustomerName City State PostalCode
1001 John Doe New York NY 10001
1002 Jane Smith Los Angeles CA 90001
1003 Bob Johnson Chicago IL 60601

Type1は単なる上書きですので、例えばCustomerID 1002のJane SmithがSan Franciscoに引っ越した場合、下記のようにCityとPostalCodeが書き換わるだけとなります：

CustomerID CustomerName City State PostalCode
1001 John Doe New York NY 10001
1002 Jane Smith San Francisco CA 94101
1003 Bob Johnson Chicago IL 60601

この方法では、Jane Smithが以前Los Angelesに住んでいたという情報は失われてしまいます。Type1 SCDは実装が簡単で、ディメンションテーブルのサイズを最小限に保つことができますが、データの履歴を追跡できないという欠点があります。

Type:2 (新しい行を追加)

Type2は履歴データを新しい行として挿入するパターンです。
これにより、データの変更履歴を完全に保持することができます。

例えば、以下のような顧客ディメンションテーブル（DimCustomer）があったとします：

CustomerID CustomerName City State PostalCode StartDate EndDate
1001 John Doe New York NY 10001 2024-01-01 NULL
1002 Jane Smith Los Angeles CA 90001 2024-01-01 NULL
1003 Bob Johnson Chicago IL 60601 2024-01-01 NULL

Jane Smith（CustomerID: 1002）がLos AngelesからSan Franciscoに引っ越し、Bob Johnson（CustomerID: 1003）が名前をRobert Johnsonに変更した場合、Type2 SCDを適用すると以下のようになります：

CustomerID CustomerName City State PostalCode StartDate EndDate
1001 John Doe New York NY 10001 2024-01-01 NULL
1002 Jane Smith Los Angeles CA 90001 2024-01-01 2024-10-01
1002 Jane Smith San Francisco CA 94101 2024-10-01 NULL
1003 Bob Johnson Chicago IL 60601 2024-01-01 2024-10-15
1003 Robert Johnson Chicago IL 60601 2024-10-15 NULL

この方法では、以下のような特徴があります：

  1. データの変更が新しい行として追加されるため、完全な履歴を保持できる
  2. 最新のレコードは EndDate が NULL であることで識別できる
    • 列指向DBを使っており、更新を発生させたくない場合はStartDateだけにし、最新の日時が入っているレコードを最新判定すればOK
  3. 同じ CustomerID に対して複数の行が存在する可能性がある

Type2 SCDは完全な履歴データを追えるため、監査や詳細な時系列分析に適しています。
ただし、データ量が増加するというデメリットがあるのとクエリが複雑になる可能性がある点に注意が必要です。

Type:3 (新しい属性を追加)

Type3は、現在の状態と以前の状態を示す属性を追加するパターンです。
これにより、1世代前の履歴を保持することができます。

例えば、以下のような顧客ディメンションテーブル（DimCustomer）があったとします：

CustomerID CustomerName CurrentCity PreviousCity CityChangeDate CurrentState PreviousState StateChangeDate
1001 John Doe New York NULL NULL NY NULL NULL
1002 Jane Smith Los Angeles NULL NULL CA NULL NULL
1003 Bob Johnson Chicago NULL NULL IL NULL NULL

Jane Smith（CustomerID: 1002）がLos AngelesからSan Franciscoに引っ越し、その後Seattleに引っ越した場合、Type3 SCDを適用すると以下のようになります：

CustomerID CustomerName CurrentCity PreviousCity CityChangeDate CurrentState PreviousState StateChangeDate
1001 John Doe New York NULL NULL NY NULL NULL
1002 Jane Smith Seattle Los Angeles 2024-10-15 WA CA 2024-10-15
1003 Bob Johnson Chicago NULL NULL IL NULL NULL

この方法では、以下のような特徴があります：

  1. 現在の値と1世代前の値を同じ行に保持する
  2. 値が変更された日付も記録できる
  3. 2世代以上前の履歴は保持されない（この例では、San Franciscoでの居住履歴は失われる）

Type3 SCDのメリットとデメリット：

メリット：

  • Type2よりも省スペース
  • 分析をする際のクエリが比較的単純
  • 現在の値と以前の値を容易に比較できる

デメリット：

  • 2世代以上前の履歴を保持していない
  • 属性ごとに列を追加する必要があるため、変更が頻繁に起こる多くの属性がある場合は適さない

Type3 SCDは、最新の状態と1つ前の状態のみを追跡すれば十分な場合や、テーブル構造の変更が制限されている場合に適しています。
ただし、完全な履歴追跡が必要な場合は、Type2 SCDの方が適しています。

選定

Fivetranのhistory modeでもType2が選定されている通り理由がなければType2の新しい行を追加するパターンを選定しておけば良さそうです。

  • 履歴の完全性を保管しておける
  • 分析用DBに保管する際に、列指向DBが得意としない更新処理なしに実現可能
  • インプットがどのCDCのパターンでも対応しやすい
    • ログベースCDCの場合: 特に加工せずそのままデータをロードさせればOK
    • タイムスタンプベースCDCの場合: 前回処理日時以降のデータをそのままロードさせればOK(データソースの更新日時列の更新も必要)

最後に

今回学習してみてCDCもSCDもある程度のスタンダードがあることが分かりましたが、メリデメを知るためにも様々なパターンを知ることも大事だなと思いました。
間違えていたり勘違いしている部分がありましたらお手数ですがご連絡お願いいたします🙇‍♀️

参考

脚注

  1. Change data capture: Definition, benefits, and how to use it | Fivetran ↩︎ ↩︎ ↩︎

  2. Transaction Log | dremio ↩︎

  3. Slowly changing dimension | Wikipedia ↩︎

Discussion