![[アップデート] Amazon Redshift Zero-ETL 統合の History Mode による SCD Type2テーブルの自動生成を試してみた](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-63f1274931b942e9a92e601c1127ad73/cfe87ec6d62fa2fc3c474ed4cb2f6c2e/amazon-redshift)
[アップデート] Amazon Redshift Zero-ETL 統合の History Mode による SCD Type2テーブルの自動生成を試してみた
2025.01.24クラウド事業本部コンサルティング部の石川です。Amazon Redshift は、Zero-ETL 統合のHistory Modeがリリースされました。Zero-ETL 統合のソースデータの変更履歴をSlowly Changing Dimension Type 2（SCD Type2）形式で保存できる新機能です。思わず「センスの良い機能だなー」と声を上げてしまいました。
本日は、Zero-ETL 統合の History Modeを用いて、Slowly Changing Dimension Type 2（SCD Type2）テーブルの自動生成を試してみます。
Zero-ETL 統合の History Modeとは
Zero-ETL 統合の History Modeは、Amazon Redshift でコードを記述することなく、データベースのSlowly Changing Dimension Type 2 (SCD Type2) テーブルを構築できます。History Modeにより、履歴データの変更を追跡および分析するプロセスが簡素化され、時間の経過に伴うデータの進化から貴重な洞察を得ることができます。
AWS VP/Distinguished EngineerであるIppokratis Pandisさんのコメントの引用
History Mode for Zero-ETL.
When we announced the beginning of the Zero-ETL program 2 years ago (at ReInvent'22) the first question/feature request I got was whether there is an option to not be deleting in Redshift the records that get deleted in Aurora. Today we are excited to announce the launch of History Mode for Zero-ETL. History Mode allows past versions of records to remain visible in Redshift.
By preserving the complete history of data changes with History Mode, users can easily run advanced analytics on historical data, build look back reports, and perform trend analysis across multiple zero-ETL data sources, including Amazon Aurora, RDS and DynamoDB.
With History Mode the differentiating Zero-ETL capability of AWS is getting even better!
Zero-ETL の History Mode
2 年前 (ReInvent'22 で) Zero-ETL プログラムの開始を発表したとき、最初に受けた質問や機能リクエストは、Aurora で削除されるレコードを Redshift で削除しないオプションがあるかどうかでした。本日、Zero-ETL のHistory Modeの開始を発表できることを嬉しく思います。History Modeを使用すると、過去のバージョンのレコードを Redshift で表示したままにすることができます。
History Modeでデータ変更の完全な履歴を保存することで、ユーザーは履歴データに対して高度な分析を簡単に実行し、振り返りレポートを作成し、Amazon Aurora、RDS、DynamoDB などの複数の Zero-ETL データソースにわたって傾向分析を実行できます。
History Modeにより、AWS の差別化要因である Zero-ETL 機能がさらに向上します。
Slowly Changing Dimension Type 2 (SCD Type2) テーブルとは
ディメンションテーブル（Dimension Table）は、分析の軸（次元）を持つテーブルです。基幹データベースのマスタテーブルはディメンションテーブルの一つです。
Slowly Changing Dimension Type 2 (SCD Type2) テーブルは、ディメンションテーブルの値が変化したときに、新しいレコードを追加して、レコードに開始日と終了日を入れることで、データの変更履歴を保存する形式です。
History Modeは、データの変更を自動的に追跡し、その履歴を保存します。これにより、時間の経過に伴うデータの進化を簡単に分析できるようになります。具体的には
- データの変更を検出し、新しいバージョンとして保存します。
- 各レコードに有効期間（開始日と終了日）を付与します。
- 最新のデータと過去のバージョンを同じテーブル内で管理します。
例えば、以下のような開発プロジェクトがあったとします。
- 開発プロジェクト（prj1001）は、2025/01/01の時点で、未着手（To Do）
- 開発プロジェクト（prj1001）は、2025/02/01の時点で、進行中（In Progress）
- 開発プロジェクト（prj1001）は、2025/05/01の時点で、完了（Done）
このデータを管理するデータベースでは、時系列にデータを更新します。一方、分析するデータベース（DWH）のSlowly Changing Dimension Type 2 (SCD Type2) テーブルでは、以下のようにデータを時系列にレコードを追加・更新されます。
開発プロジェクト（CM1001）は、2025/01/01の時点で、未着手（To Do）のレコードが追加されます。
| プロジェクトID | ステイタス | 開始日 | 終了日 |
|---|---|---|---|
| prj1001 | To Do | 2025/01/01 | NULL |
開発プロジェクト（CM1001）は、2025/02/01の時点で、進行中（In Progress）のレコードが追加されます。未着手（To Do）のレコードに終了日に設定されます。
| プロジェクトID | ステイタス | 開始日 | 終了日 |
|---|---|---|---|
| prj1001 | In Progress | 2025/02/01 | NULL |
| prj1001 | To Do | 2025/01/01 | 2025/02/01 |
開発プロジェクト（CM1001）は、2025/05/01の時点で、完了（Done）のレコードが追加されます。進行中（In Progress）のレコードに終了日に設定されます。
| プロジェクトID | ステイタス | 開始日 | 終了日 |
|---|---|---|---|
| prj1001 | Done | 2025/05/01 | NULL |
| prj1001 | In Progress | 2025/02/01 | 2025/05/01 |
| prj1001 | To Do | 2025/01/01 | 2025/02/01 |
Zero-ETL 統合の環境構築
今回の検証環境は、下記のブログに従い、Aurora PostgreSQLとAmazon Redshift ProvisionedのZero-ETL 統合の環境を構築します。
Aurora PostgreSQL（データソース）のテーブル定義
Aurora PostgreSQLに以下のデータソースのテーブルを作成して、レコードを更新・追加します。
create table projects (
id varchar(16) PRIMARY KEY,
status varchar(32)
);
検証シナリオ
以下の順に、History Modeの変更やプロジェクトのステイトを更新します。
- History Modeが
FALSEの状態（デフォルト） - 開発プロジェクト（prj1001）は、未着手（To Do）
- History Modeを
TRUEの状態に変更 - 開発プロジェクト（prj1001）は、未着手（To Do）
- 開発プロジェクト（prj1001）は、進行中（In Progress）
- 重複した未着手（To Do）のレコードの削除
- 開発プロジェクト（prj1001）は、完了（Done）
Zero-ETL 統合の History Modeを試す
History ModeがFALSEの状態（デフォルト）から検証をスタートします。
1. 開発プロジェクト（prj1001）は、未着手（To Do）
Aurora PostgreSQL（データソース）のレコード（未着手（To Do））を追加します。
postgres=> insert into projects (id, status)
postgres-> values('prj1001', 'to do')
postgres-> on conflict (id)
postgres-> do update
postgres-> set status = EXCLUDED.status;
INSERT 0 1
数十秒でAmazon Redshiftのテーブルが自動作成され、データが同期されます。
History ModeがFALSEの状態（デフォルト）では、id、statusのカラムに加え、padb_internal_txn_seq_col、padb_internal_txn_id_colの2つのカラムが追加されます。
2. History ModeをTRUEに変更
Zero-ETL 統合が動作していることを確認しましたので、Amazon Redshiftのデータベースに対して、History Modeを有効（TURE）に設定します。
ALTER DATABASE dev INTEGRATION SET HISTORY_MODE = TRUE;
3. 開発プロジェクト（prj1001）は、未着手（To Do）
Aurora PostgreSQL（データソース）のレコード（未着手（To Do））に更新します。
postgres=> insert into projects (id, status)
postgres-> values('prj1001', 'to do')
postgres-> on conflict (id)
postgres-> do update
postgres-> set status = EXCLUDED.status;
INSERT 0 1
4. 開発プロジェクト（prj1001）は、進行中（In Progress）
Aurora PostgreSQL（データソース）のレコード（進行中（In Progress））に更新します。
History ModeがTRUEの状態（デフォルト）では、id、statusのカラムに加え、_record_is_active、_record_create_time、_record_delete_time、padb_internal_txn_seq_col、padb_internal_txn_id_colの5つのカラムが追加されます。
なお、自動的に追加される_record_is_activeカラムがtrueであるかを判定することで、最新の状態を保持したレコードを簡単に取り出すことが可能です。
postgres=> insert into projects (id, status)
postgres-> values('prj1001', 'In Progress')
postgres-> on conflict (id)
postgres-> do update
postgres-> set status = EXCLUDED.status;
INSERT 0 1
未着手（To Do）のレコードが2つあることが確認できました。
5. 重複した未着手（To Do）のレコードの削除
History ModeがFALSEの場合は参照のみのため、削除しようとするとエラーになりました。未着手（To Do）のレコードが2つあるので、一つ削除を試みます。History ModeがTRUEの状態の場合、レコードを削除できることを確認しました。
なぜ、History ModeがTRUEの状態の場合、レコードを削除できるかというと、Slowly Changing Dimension Type 2 (SCD Type2) テーブルは、更新するたびにレコードが増え、また履歴の保持期間を任意で管理できるようにするための配慮と考えられます。
6. 開発プロジェクト（prj1001）は、完了（Done）
Aurora PostgreSQL（データソース）のレコード（完了（Done））に更新します。
postgres=> insert into projects (id, status)
postgres-> values('prj1001', 'Done')
postgres-> on conflict (id)
postgres-> do update
postgres-> set status = EXCLUDED.status;
INSERT 0 1
Slowly Changing Dimension Type 2 (SCD Type2) テーブルが自動的に作成、管理されることが確認できました。
最後に
Amazon Redshift の Zero-ETL 統合の History Mode は、コードを記述することなく Slowly Changing Dimension Type 2 (SCD Type2) テーブルを自動的に生成し管理することが可能となりました。これは、データの変更履歴を追跡し、時系列での分析や傾向把握を容易にする強力なツールです。
History Mode の導入により、企業はデータの変遷を簡単に追跡し、過去のバージョンを保持しながら最新の状態を管理できるようになりました。_record_is_active カラムを用いることで、最新のレコードを簡単に識別することができ、データの整合性を保ちながら履歴データを活用することが可能です。
この機能は、データ分析プロセスを大幅に簡素化し、ストレージ効率を向上させるだけでなく、Aurora、RDS、DynamoDB などの多様なデータソースに対応しています。これにより、企業はより迅速かつ効率的にデータドリブンな意思決定を行うことができるようになります。
Zero-ETL 統合の History Mode は、データウェアハウジングと分析の分野に新たな可能性をもたらし、企業のデータ活用戦略を強化する重要なツールとなるでしょう。この機能の登場により、データエンジニアやアナリストは、より価値の高い分析タスクに集中できるようになり、ビジネスインサイトの創出に貢献することが期待されます。
この機会にデータモデリングについて学びたい方は、以下の動画をご覧ください。
参考文献
![[アップデート] Amazon Q Business のチャット上のファイルアップロード機能が画像ファイルをサポートしました](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-5dd35cec6954843cd98f8fe0f8bf3afe/08b840eb79b1e885b48a9fad3081ce0a/amazon-q)
