Silver テーブルへ差分連携する際の Bronze テーブルからデータを抽出方法の注意事項

## 概要

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](https://docs.databricks.com/ja/ingestion/auto-loader/file-notification-mode.html#file-notification-events)

## 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`です。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/194d6215-93d6-d62a-339a-b2c752bbfd34.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/ff1bee4e-715b-6c62-79db-58c437ee52bc.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/ad8a6ff1-004e-abb2-6f43-46b3f4dda54c.png)


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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/2c829a84-0ddc-997d-478a-c22610953aed.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/7ff1c517-1c57-ef05-5564-2d4bd2fdaf18.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/96bdf2b3-9a57-e297-1cb7-3773eae7e20a.png)


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

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

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/e52a3ff3-d2ab-b544-d532-9c9aeff41594.png)

## まとめ

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