概要

Bronze テーブルから Silver テーブルへのデータの差分連携を実施する際の注意事項を整理します。Bronze テーブルでは、Databricks Auto Loader などによりすべてのインタフェースファイルのデータを保持することがあります。これらのデータは、いわゆる履歴テーブルとして保持されることが一般的です。Bronze テーブルから Silver テーブルへの差分連携を行う際には、ファイルが遅れて到着した場合に、データを処理した順（下記表のINGEST_TIMESTAMP列）で Silver テーブルに連携すると、想定外のデータが連携される可能性があります。そのため、指定した UPDATE_TIMESTMAP 列以降のデータから求めた INGEST_TIMESTMAP 列の最小の値以降のデータを抽出する必要があります。本記事では、下記表の監査列を保持していることを前提として説明を行います。

#	カラム名	概要
1	INGEST_TIMESTAMP 列	データ分析基盤にデータが連携された日時を保持する列
2	UPDATE_TIMESTAMP 列	データ分析基盤にてデータが処理された日時を保持する列
3	DATASOURCE 列	ソースファイル名を保持する列

ファイルが遅れて届くケース

ファイルが遅れて届くケースには、ミスなどによりファイルの配置をできなかったケースだけでなく、ドキュメントにて下記のように記載があるように Databricks Auto Loader をファイル追加モードで利用する場合などにも発生します。そのため、ファイルが遅れて届くことを考慮した実装が重要であり、単純にファイルを処理した順のプログラムでは想定通りの結果とならないことに注意が必要です。

クラウドプロバイダーは、非常にまれな条件下ですべてのファイルイベントの100%配信を保証するものではなく、ファイルイベントの待機時間に関する厳格なSLAを提供していません。

引用元：Auto Loader ファイル通知モードとは何ですか?| Databricks on AWS

Silver テーブル連携時に UPDATE_TIMESTAMP 列を基準に反映してはいけない理由

UPDATE_TIMESTAMP列に基づき差分連携を実施することで想定外の動作となる事象を具体的なケースに基づき確認を行い、UPDATE_TIMESTAMP列を基準にしてはいけない理由を考察します。

まず、一日ごとにfile_1 -> file_2 -> file_3という順で３つのファイルを取り込むケースを検討します。3 日目（2024/4/3）の時点で Silver テーブルに反映すべきデータはfile_3です。このとき、UPDATE_TIMESTAMP列と INGEST_TIMESTAMP列がいずれも2024/4/3です。

そのため、UPDATE_TIMESTAMP列を基準として Silver テーブルにデータ連携を実施したとしても想定通りにfile_3を連携することができます。

次に、file_1 -> file_3 -> file_2という順でfile_2ファイルが 4 日目（2024/4/4）に遅れて届いたケースを検討します。前提として、 Silver テーブルに反映すべきデータはfile_3とします。

ここで、UPDATE_TIMESTAMP列を基準に Silver テーブルへ反映した場合には、最大のものがfile_2であるため、Silver テーブルに想定とは異なるデータが反映されてしまいます。本事象がUPDATE_TIMESTAMP列を基準に差分連携を実施する際に想定外の動作となってしまう事象です。

ファイルが遅れてくることを前提とした対応策

ファイルが遅れてくることを前提にデータ抽出を実施するには、次のステップを実施する必要があります。その方法を、指定した UPDATE_TIMESTMAP 列以降のデータから求めた INGEST_TIMESTMAP 列の最小の値以降のレコードを抽出する方法（パラメータ名をmin_inget_timestamp_in_update_timestamp）として実装しています。名称については再考の余地があると感じています。

UPDATE_TIMESTAMP列から抽出対象の期間のデータを取得
そのデータにおける最小の INGEST_TIMESTAMP（例：2024/04/02）を算出
再度テーブルから最小のINGEST_TIMESTAMP列以降のデータを抽出

実際にデータを抽出するステップを下記図で確認します。まず、UPDATE_TIMESTAMP列から抽出対象の期間である2024/04/04以降のデータを取得します。そのデータにおける最小のINGEST_TIMESTAMP列として2024/04/02の値を算出します。再度テーブルから最小のINGEST_TIMESTAMP列の値である2024/04/02以降のデータを抽出します。ここまでがデータの抽出のステップです。次にシルバーテーブルが ID 列を主キーとしたテーブルであるため、ID ごとに最後に連携されたデータ（例：INGEST_TIMESTAMP列が2024/04/04）をシルバーテーブルに反映します。

まとめ

Bronze テーブルから Silver テーブルへのデータの差分連携を実施する際には、ファイルが遅れて到着した場合を考慮する必要があります。そのため、指定した UPDATE_TIMESTMAP 列以降のデータから求めた INGEST_TIMESTMAP 列の最小の値以降のデータを抽出する方法（パラメータ名をmin_inget_timestamp_in_update_timestamp）を用いてデータ抽出を実施します。この方法により、ファイルが遅れて届くことを前提としたデータ抽出が可能となり、想定通りのデータ連携を実現できます。この対応策は、データ分析基盤の信頼性を確保するために重要な要素であり、データエンジニアリングの現場での知識として理解しておくべきです。