Databricks SQLを使用したディメンショナルデータウェアハウスの実装：パート2

組織が分析ワークロードをDatabricksに統合する際、従来のデータウェアハウスの手法を適応する必要がよくあります。このシリーズでは、Databricks上でのディメンションモデリング、特にスタースキーマの実装方法を探ります。最初のブログではスキーマ設計に焦点を当てました。このブログでは、ディメンションテーブルのETLパイプライン、特にSlowly Changing Dimensions (SCD) Type-1およびType-2パターンを説明します。最後のブログでは、ファクトテーブルのETLパイプラインの構築方法を示します。

徐々に変化するディメンション (SCD)

前回のブログで、私たちはスタースキーマを定義し、ファクトテーブルとそれに関連するディメンションを含めました。特に、DimCustomerというディメンションテーブルを強調しました（スペースを節約するために一部の属性を削除して表示しています）：

このテーブルの最後の3つのフィールド、つまり、StartDate、EndDateおよびIsLateArrivingは、レコードのバージョニングを支援するメタデータを表しています。顧客の収入、結婚状況、住宅所有、家庭内の子供の数、その他の特性が変化すると、その顧客の新しいレコードを作成したいと考えます。これにより、FactInternetSalesなどのオンライン販売取引のファクトが、その顧客の正しい表現と関連付けられます。これらのレコードでは、自然（ビジネス）キーであるCustomerAlternateKeyは同じままですが、メタデータは異なり、その顧客のバージョンが有効だった期間を知ることができます。また、代理キーであるCustomerKeyも異なり、ファクトが正しいバージョンにリンクすることができます。

注：代理キーはファクトとディメンションをリンクするために一般的に使用されるため、ディメンションテーブルはこのキーに基づいてクラスタ化されることがよくあります。ソートされたレコードにb-treeインデックスを利用する従来のリレーショナルデータベースとは異なり、Databricksはリキッドクラスタリングという独自のクラスタリング方法を実装しています。リキッドクラスタリングの詳細はこのブログの範囲外ですが、ディメンションテーブルの定義中に常に代理キーにCLUSTER BY句を使用して、この機能を効果的に活用します。

属性が変化するたびにディメンションレコードをバージョニングするこのパターンは、タイプ2の緩やかに変化するディメンション（または単にタイプ2 SCD）パターンとして知られています。タイプ2 SCDパターンは、クラシックなディメンショナル方法論でディメンションデータを記録するための優先的なパターンです。しかし、ディメンションレコードの変更を扱う他の方法もあります。

ディメンション値の変更を処理する最も一般的な方法の一つは、既存のレコードをその場で更新することです。レコードのバージョンは一つだけ作成されるため、ビジネスキーがレコードの一意の識別子として残ります。パフォーマンスや一貫性などの理由から、私たちは代替キーを実装し、ファクトレコードをこれらのディメンションにそのキーでリンクします。それでも、開始日と終了日というメタデータフィールドは、特定のディメンションレコードがアクティブと見なされる時間間隔を記述するためには必要ありません。これはType-1 SCDパターンとして知られています。私たちのスタースキーマにおけるプロモーションディメンションは、Type-1ディメンションテーブルの実装の良い例を提供しています：

しかし、IsLateArrivingというメタデータフィールドは、Type-2のDimCustomerにはありますが、Type-1のDimPromotionにはありません。このフィールドは、レコードが遅延して到着したことを示すために使用されます。 遅延到着レコードは、ファクトETLサイクル中にビジネスキーが表示されるが、以前のディメンション処理中にそのキーのレコードが見つからないというものです。 Type-2 SCDの場合、このフィールドは、遅延到着レコードのデータが初めてディメンションETLサイクルで観察されたときに、レコードがその場で更新され（Type-1 SCDパターンと同様）、その後からバージョン管理されることを示すために使用されます。 Type-1 SCDの場合、このフィールドは必要ありません。なぜなら、レコードはどのような場合でもその場で更新されるからです。

注：Kimball Groupは、Type-1とType-2のパターンのバリエーションと組み合わせを含む、追加のSCDパターンを認識しています。Type-1とType-2のSCDは、これらのパターンの中で最も頻繁に実装され、他のものとの関連性が密接であるため、このブログではこれら二つのディメンションタイプに限定しています。Kimball Groupが認識する8種類のSCDについての詳細は、Slowly Changing Dimension Techniquesセクションを参照してください。このドキュメント。

タイプ1 SCDパターンの実装

データがインプレースで更新されるため、タイプ1 SCDワークフローパターンは2次元ETLパターンの中で最も単純です。これらのディメンションをサポートするには、以下の手順に従います。

1. 運用システムから必要なデータを抽出する
2. 必要なデータクレンジング操作を実行する
3. 既存のディメンションテーブルに対して、新たに入ってきたレコードを比較する
4. 既存のレコードを更新する（入力属性が既に記録されているものと異なる場合）
5. 次元テーブルに対応するレコードがない新規レコードを挿入する

タイプ1 SCDの実装を示すために、DimPromotionテーブルの継続的な集計のためのETLを定義します。

ステップ1：運用システムからデータを抽出する

最初のステップは、運用システムからデータを抽出することです。データ ウェアハウスは、Microsoftが提供するAdventureWorksDWサンプル データベースをモデルにしているため、密接に関連するAdventureWorks (OLTP) サンプル データベースをソースとして使用します。このデータベースはAzure SQL Databaseインスタンスにデプロイされ、Databricks環境内でフェデレーションクエリを介してアクセス可能になっています。抽出は単純なクエリ（一部のフィールドはスペースを節約するために省略）で行われ、クエリ結果は私たちのステージングスキーマのテーブルに保存されます（これは、ここに示されていない権限設定を通じて、私たちの環境のデータエンジニアだけがアクセスできるようになっています）。これは、この環境でソースシステムデータにアクセスするための多くの方法の一つです：

ステップ2：テーブル内のレコードと受信したレコードを比較する

追加のデータクレンジングステップが必要ないと仮定すると（これは UPDATE や別の CREATE TABLE AS ステートメントで実装できます）、ビジネスキーでステージングデータとディメンションデータをマッチングさせ、ディメンションデータの更新/挿入操作を一度に行うことができます。これは MERGE ステートメント を使用します：

ここで書かれている声明について重要なことは、ステージングと公開されたディメンションテーブルデータ間で一致が見つかった場合、既存のレコードを更新するということです。私たちは、ステージングのレコードがディメンションテーブルにある情報と異なる場合に限り、更新を行うための追加の基準をWHEN MATCHED節に追加することができますが、この特定のテーブルのレコード数が比較的少ないため、ここでは比較的軽量なロジックを採用しています。（DimCustomer,ははるかに多くのデータを含んでいるため、追加のWHEN MATCHEDロジックを使用します）

タイプ2のSCDパターン

タイプ2のSCDパターンは少し複雑です。このタイプのディメンションをサポートするためには、次のことが必要です：

1. 運用システムから必要なデータを抽出する
2. 必要なデータクレンジング操作を実行する
3. ターゲットテーブルの遅延到着メンバーレコードを更新する
4. ステージングで新バージョンが見つかった場合、ターゲットテーブルの既存レコードを期限切れにする
5. 新しい（または新しいバージョンの）レコードをターゲットテーブルに挿入する

ステップ1：ソースシステムからデータを抽出し、クレンジングする

タイプ1 SCDパターンと同様に、最初のステップはソースシステムからデータを抽出し、クレンジングすることです。上記と同じアプローチを使用して、フェデレーテッドクエリを発行し、抽出したデータを staging スキーマのテーブルに保存します：

ステップ2：ディメンションテーブルと比較する

このデータが取得できたので、必要なデータの変更を行うために、これを私たちのディメンションテーブルと比較することができます。これらの最初の一つは、以前のファクトテーブルETLプロセスから遅延到着としてフラグ付けされたレコードをその場で更新することです。これらの更新は、遅延到着としてフラグ付けされたレコードに限定されていることに注意してください。そして、IsLateArrivingフラグは更新と共にリセットされ、これらのレコードは通常のタイプ2 SCDとして動作します:

ステップ3：バージョン管理されたレコードを期限切れにする

次に、バージョニングが必要なレコードを期限切れにするデータの変更を行います。これらのレコードのEndDateの値は、次のステップで実装する新しいレコードバージョンのStartDateと一致する必要があります。そのため、これら二つのステップの間で使用するタイムスタンプ変数を設定します。

注： あなたが利用できるデータによっては、 EndDate 値をソースシステムから取得することを選択し、その時点では必ずしもここに示されているような変数を宣言しないかもしれません。

WHEN MATCHED節で使用される追加の基準に注意してください。このステートメントでは一つの操作しか行っていないので、このロジックをON句に移動することも可能ですが、明確さと保守性のために、現在のディメンションレコードのバージョンと一致するコアマッチングロジックから分離しています。

このロジックの一部として、私たちはequal_null()関数を大量に使用しています。この関数は、最初と二番目の値が同じか、両方ともNULLの場合にTRUEを返し、それ以外の場合にはFALSEを返します。これは、列ごとに変更を探す効率的な方法を提供します。DatabricksがNULLセマンティクスをどのようにサポートしているかの詳細については、このドキュメントを参照してください。

この段階では、ディメンションテーブルのレコードの以前のバージョンが期限切れになり、終了日が設定されています。

ステップ4：新しいレコードを挿入する

これで、新規レコードと新バージョンのレコードを挿入することができます：

前述の通り、これはINSERT文を使用して実装することも可能でしたが、結果は同じです。このステートメントにより、ステージングテーブルに存在し、ディメンションテーブルに未期限の対応するレコードがないレコードを特定しました。これらのレコードは単純に、このテーブルに存在する可能性のある期限切れのレコードと一致するStartDateの値で挿入されます。

次のステップ：ファクトテーブルのETLを実装する

ディメンションが実装され、データが入力されたので、今度はファクトテーブルに焦点を当てることができます。次のブログでは、これらのテーブルのETLがどのように実装できるかを示します。

Databricks SQLについて詳しく知りたい場合は、私たちのウェブサイトを訪れるか、ドキュメンテーションを読んでください。また、Databricks SQLのプロダクトツアーもチェックしてみてください。既存のウェアハウスを高性能でサーバーレスのデータウェアハウスに移行し、素晴らしいユーザーエクスペリエンスと低コストを実現したいと考えているなら、Databricks SQLが解決策です。-無料で試してみてください。