データの変更をデータウェアハウスに反映させる方法: ステージエリアの役割

データウェアハウスは履歴データの宝庫です。ソースシステムで発生する変更を反映させることが不可欠な処理です。この記事では、ステージングエリアと増分ロードがどのようにこのプロセスを効率化できるかを説明します。

1. 効果的なデータソーススキーマ

更新効率の鍵は、データソースのスキーマにあります。ここでは、特に重要な 2 つの列について説明します。

• last_updated: このタイムスタンプ列は、レコードがソースシステムで最後に変更された時刻を正確に反映します。データ更新時に、これを利用して最近変更されたデータ 식別します。

• is_active: このフラグ (通常はブール値) は、論理削除に役立ちます。ソースシステムでレコードが使われなくなった場合、データウェアハウスから削除するのではなく、is_active を false に設定するだけです。これにより、履歴データの整合性が維持されつつ、非アクティブなレコードが識別されます。

2. 更新ギャップの追跡: ETL メタデータ

ETL (抽出、変換、読み込み) データ更新プロセスを毎日実行するとしましょう。前回の更新以降の新規データまたは変更されたデータのみを取得するようにするには、進捗状況を追跡する方法が必要です。ここで ETL メタデータテーブルが活躍します。

• high_watermark: この列には、前回の ETL ジョブで処理された最大の last_updated 値が格納されます。これは次回の実行の開始点として機能し、前回の更新以降に変更されたデータのみをフェッチするようにします。

3. 更新されたデータのみをロードする: 選択的抽出

下地が整ったので、ソースシステムからステージングエリアにデータを選択的にロードする方法を見てみましょう。

重要：ETL処理を開始する前に、ステージングエリアを必ずtruncate（切り捨て）してください。これにより、常に最新のデータのみを処理対象とすることができます。

1. high_watermarkに基づいてフィルターをかける: ETL スクリプトでは、データソースをクエリし、last_updated タイムスタンプが ETL メタデータテーブルの high_watermark に保存されている値よりも大きいレコードをフィルタリングします。これにより、最近更新されたデータまたは新規データのみを取得するようにします。

2. ステージングエリアへのロード: フィルタリングされたデータは、ステージングエリアにロードされます。この一時的なストレージは、データウェアハウスに到達する前にデータ処理を行うためのバッファーゾーンとして機能します。

4. データウェアハウスへの Upsert: データのクリーンさを維持する

最後に、処理済みのデータをステージングエリアからデータウェアハウスに移動させる必要があります。

Upsert: この強力な操作は、更新と挿入の機能を組み合わせたものです。一意の識別子 (ソースとウェアハウスの両方にあることが多い) を基準に既存のレコードをチェックします。一致するレコードが見つかった場合は、関連する列を更新します (ソースデータに基づいて is_active を false に設定する場合も)。一致するレコードが見つからない場合は、新しいレコードが挿入されます。

これらの手順に従うことで、ステージングエリアを使用した増分ロード戦略を実装できます。このアプローチにより、データウェアハウスはソースシステムの最新の変更と同期化され続け、論理削除と、最近変更されたデータだけに注目することでリソースを効率的に利用しながら、データの整合性が維持されます。

(上記な手順は一般的な増分更新で手間がかかりますが、BigQueryのMaterialized view を使えば、簡単にできます。)