注：本記事は(2022年10月27日)に公開された(Data Vault Techniques on Snowflake: Hub Locking on Snowflake)を翻訳して公開したものです。

Snowflakeは、データプラットフォームのメンテナンス作業を不要にし、クラウド用のデータモデル手法を自由に選択できるようにすることで、クラウドにおける新たなデータ標準を提示し続けています。トランザクションの分離を検討すべきシナリオは複数存在しますが、データVaultの場合、「共通ハブテーブル」がまさにそうしたシナリオに該当します。

今回のブログは、以下の「SnowflakeでのデータVaultのテクニック」シリーズの第8回となります。

  1. イミュータブルストア、仮想の終了日
  2. データVault用のSnowsightダッシュボード
  3. ポイントインタイム構造とジョインツリー
  4. 非常に大きなサテライトテーブルのクエリ
  5. ビュー上でのストリームとタスク
  6. 条件付きのマルチテーブルINSERTとその使用場面
  7. 行アクセスポリシー+マルチテナント
  8. Snowflakeでのハブのロック
  9. 仮想ハウスとチャージバック

前回のブログではパッシブ統合のコンセプトについて取り上げました。ビジネスオブジェクトの定義に従って共通ハブテーブルによる統合とローディングを同時に行うのは、データVaultモデルの適用にぴったりのシチュエーションです。Snowflakeは「Read Committed（コミットされた読み込み）分離レベル」を採用していますが、それぞれのハブテーブルローダーによるロード後も確実にターゲットハブテーブルが同じ完全性を維持できるようにするにはどうすればよいでしょうか？これをSnowflakeでどのように実行するか、またそのことがデータVaultモデルにおいてどのような意味を持つのかを見ていきましょう。

並列ローディングおよび結果整合性

上図に示すように、全てのランディングされたソースデータはステージングされ、モデリングされたハブ、リンク、サテライトテーブルにロードされます。

  • サテライトテーブルはシングルソースであり、定義された親キー、ロード日付、 記述的コンテンツで一意に識別されます。
  • リンクテーブルはマルチソースであることはほとんどなく、定義された作業単位で一意に識別されます。
  • ハブテーブルは常にマルチソースであり、ビジネスオブジェクトにより一意に識別されます。

上図でそれぞれのソースがどのように共通ハブテーブルにロードされるかを見てください。その際、ハブテーブルはハブローダーに発見された時と同じ完全性を保っていなければなりません。ビジネスキー、ビジネスキー衝突コード、およびマルチテナントIDにより定義される一意のビジネスオブジェクトリストとなります。

問題

前回6回目のブログ「条件付きのマルチテーブルINSERTとその使用場面」で取り上げたのと同じ問題になります。前回のブログでは、条件付きマルチテーブルのINSERTについて説明しました。Read Committedトランザクション分離の性質上、同じターゲットハブテーブルにロードするスレッドを全て同じタイミングで実行すると、それぞれのスレッドは他のスレッド由来のコミットされていないトランザクションを除いたターゲットテーブルを表示します。下図をご確認ください。

問題

重複が存在するとデータVaultモデルの完全性を損なうため、情報マート（および関連付けられたデータ構造）に不備が発生します。

解決策

Snowflakeではテーブルのロックを認めており、構文は極めてシンプルです。SQL INSERTステートメントをSQL MERGEステートメントに変更すると、MERGEステートメントに定義されているターゲットハブテーブルは、ターゲットテーブルを更新する時にロックされます。当然ハブローダーテンプレートも変更されますので、これ以降、設定された全てのハブローダーはSQL MERGE INTOステートメントを使用するようになります。

解決策
   
 insert into HUB_TABLE
    select distinct 
        < HUB-COLUMNS … >
    from STAGED stg
    where not exists 
      (select 1
       from HUB_TABLE h
       where stg.HASH-KEY = h.HASH-KEY)
    
merge into HUB_TABLE h 
    using (select distinct 
        < HUB-COLUMNS … >
    from STAGED) stg 
   on h.HUB_KEY = stg.HUB_KEY
when not matched then 
    insert (< HUB-COLUMNS … >) 
     values (< HUB-COLUMNS … >)

以下の3つのSQLデータ操作言語（DML）ステートメントは、更新時にSnowflakeテーブルをロックします。

  • SQL MERGE – 2番目のテーブルまたはサブクエリの値に基づいて、テーブルの値を挿入、更新、削除します
  • SQL UPDATE – ターゲットテーブルの指定された行を新しい値で更新します
  • SQL DELETE – テーブルの行を削除します

この自動化スキーマにより、はじめにアニメーションで示した例は以下のように更新されました。

このアプローチによりレイテンシーは増えるでしょうか？その可能性はありますが、ハブテーブルは一般にアンチ－セミ結合を用いて一度に少数の凝縮されたレコードを処理するだけなので、レイテンシーが発生してもその度合いは軽微であると考えられます。このアプローチの優れた点は、外部定義されたセマフォを用いてターゲットテーブルを明示的にロックする必要がないところです。Snowflake側でどのスレッドを優先的に処理すべきかランダムに判断しますが、ハブテーブルの場合はこれで何の問題もありません。

ハブテーブルに関連してもう一つお話ししましょう。

SQL MERGEステートメントはSQL INSERTやUPDATEの実行を可能にするため、過去に非推奨化されたデータVaultアーチファクト（ハブ＆リンクテーブルのlast seen date（最終確認日））について調べることができます。 ハブ＆リンクテーブは非常に薄く長さも短いことが多いため、これらのテーブルを構成するマイクロ―パーティションの数はごく少数です。ならば、なぜSQL UPDATEをハブ＆リンクテーブルに適用しないのでしょうか？

SQL UPDATEは確かにコストのかかる演算ですが、記述列数が1行から数百行に及ぶこともあるサテライトテーブルにもそれは当てはまります。いずれにしろSQL UPDATEの使用はここではお勧めしません。ここで前の例に戻って、「last-seen-date」をどのように利用できるかみてみましょう。

   
merge into HUB_TABLE h 
    using (select distinct 
        < HUB-COLUMNS … >
    from STAGED) stg 
   on h. HUB_KEY = stg.HUB_KEY
 when not matched then 
    insert (< HUB-COLUMNS … >) 
     values (< HUB-COLUMNS … >)


    
merge into HUB_TABLE h 
    using (select distinct 
        < HUB-COLUMNS … >
    from STAGED) stg 
   on h. HUB_KEY = stg.HUB_KEY
   when matched then 
    update set h.LAST = stg.LAST
   when not matched then 
    insert (< HUB-COLUMNS … >) 
     values (< HUB-COLUMNS … >)

その他のデータVaultアーチファクト

データVaultはINSERT-ONLYのテーブル構造であるため、データの追跡のために以下のようなさまざまなアーチファクトが開発されました。

  • レコード追跡サテライト（RTS） – ビジネスオブジェクトもしくは仕事単位を見つけるたびにレコードとして記録します。
  • ステータス追跡サテライト（STS） – ビジネスオブジェクト若しくは仕事単位が出現、更新、もしくはソースから削除（ソースがスナップショットとして提供されている場合）されるたびに追跡を行います。
  • 有効性サテライト（EFS） – 駆動キーと組み合わせることで、特定のリレーションシップにおける非駆動キーに対する駆動キーの追跡ができます。
SAT、RTS、STSとハブlast-seen-dateの比較。

「last-seen-date」はそのビジネスオブジェクトもしくは仕事単位の最終発生日しか記録しないため、例えば以下を確認するには信頼のおける情報源とは言えません。

  • RTSで追跡しているソース発生頻度
  • STSで追跡しているソース削除履歴
  • 最新のリレーションの追跡にリレーションシップの変更の確認を要する場合

駆動キーと有効性

リレーションシップを駆動するエンティティの有効性を追跡するビジネス日付がデータソースに含まれていない場合、もしくはデータソースで追跡されているものとは異なる駆動エンティティの有効性を追跡したい場合、有効性サテライトを使用する必要があります。リンクテーブルのLAST_SEEN_DATE列をチェックすれば、非常に複雑なデータVaultパターンを用いずに最新のリレーションシップが何なのかを確認することができます、このやり方について以下の例で詳しく見てみましょう。

EFSを使用しない場合のデメリット

last seen dateを使用すれば、駆動キーが何であっても,その駆動キー／リレーションシップに対する現在のアクティブなリレーションシップを確認することはでき、単一のリンクテーブルについて追跡したい各駆動キーについて複数の有効性サテライトを展開する必要はありません。しかし、その駆動キーと非駆動キーの関係の変更履歴を確認することはできません。ソースシステムでこうした履歴を提供していない場合、有効性サテライトを用いるのが唯一の確認方法となります。

ロックすべきかどうかの判断基準

必ず自分で各シナリオをテストしてください。このテクニックの核にあるのは、Snowflake技術を有効活用しながらデータVault自動化を単一用途に保つというコンセプトです。

Zen of Pythonにもある通り、「明確な、最善の方法があるはず」なのです。

  • 各ハブローダーは、単一のソースファイルから一つのハブテーブルをロードすること。
  • 各リンクローダーは、単一のソースファイルから一つのリンクテーブルをロードすること。
  • 各サテライトローダーは、単一のソースファイルから一つのサテライトテーブルをロードすること。
  • ランディングされたコンテンツのステージング方法は一つであること。

Snowflake技術を活用して必要とする自動化の目的を確実に達成してください。

その他の参考資料