Data Vault on Snowflake：ハッシュキー設計とロード戦略の勘どころ

Data Vault 2.0のモデリングはイメージできた、SnowflakeでいざHub・Link・Satelliteを実装しようとすると、判断ポイントが集中します。「ハッシュキーはMD5でいいのか、何のカラムから生成するのか」「Satelliteの差分検知はSnowflakeのどの機能を使うのが速いのか」「ロードはどう並列化するのか」。一つずつ詰めていきましょう。

Snowflakeは、Data Vault 2.0と相性が良いクラウドDWHです。ストレージとコンピュートの分離、仮想ウェアハウス、ハッシュ関数の標準実装、Streams＆Tasksなど、Vault実装に効く機能が揃っています。Snowflakeで効くデザインパターンを実務目線で整理します。

ハッシュキー設計の勘どころ

Data Vault 2.0の中核は、ビジネスキーをハッシュ化した「ハッシュキー」です。Snowflakeでは、用途に応じてハッシュ関数を選びます。

MD5は暗号学的には脆弱ですが、Data Vaultでは「衝突しないキー識別子」が目的で、攻撃耐性は要求されません。実装の多くはMD5を採用しています。サイズも小さく、JOIN性能でも有利です。SHA系を選ぶのは内部ポリシーでMD5を禁じている場合などです。

-- ビジネスキーを正規化してからハッシュ化する
-- 大文字小文字、空白、NULLの扱いを統一しないと、別の行と判定される
SELECT
  MD5_BINARY(UPPER(TRIM(COALESCE(customer_id, '')))) AS hub_customer_hk,
  customer_id AS customer_bk
FROM stg_customer;

ハッシュキー設計でいちばん事故るのが、入力の正規化忘れです。同じ顧客IDが「ABC123」と「abc123」「 ABC123 」のように混じると、別の顧客と判定されます。必ずUPPER・TRIM・NULL置換を統一してからハッシュ化する、をルールにしてください。

Satelliteの差分検知をSnowflakeで効かせる

Satelliteの肝は、属性が変わったときだけ新しい行を追加する差分検知です。Data Vaultではhash_diff（属性をハッシュ化した値）を使い、前回の最終行と比較します。

-- Satelliteのロード（dbtのincrementalモデル想定）
WITH source AS (
  SELECT
    MD5_BINARY(UPPER(TRIM(customer_id))) AS hub_customer_hk,
    MD5_BINARY(CONCAT_WS('|',
      COALESCE(customer_name, ''),
      COALESCE(address, ''),
      COALESCE(email, '')
    )) AS hash_diff,
    customer_name,
    address,
    email,
    CURRENT_TIMESTAMP() AS load_date,
    'ec_main' AS record_source
  FROM stg_customer
),
latest AS (
  SELECT hub_customer_hk, hash_diff
  FROM sat_customer_profile
  QUALIFY ROW_NUMBER() OVER (PARTITION BY hub_customer_hk ORDER BY load_date DESC) = 1
)
SELECT s.*
FROM source s
LEFT JOIN latest l USING (hub_customer_hk)
WHERE l.hash_diff IS NULL
   OR l.hash_diff != s.hash_diff;

QUALIFY と ROW_NUMBER の組み合わせは Snowflake で頻出のパターンで、最新行の抽出に便利です。差分があった顧客だけが新しい行として追加されます。

ロードの並列度とウェアハウス設計

Data Vault 2.0でハッシュキーを採用したいちばんの理由は、並列ロードに強くするためです。Snowflakeは、仮想ウェアハウスを分けることで物理的にも並列に動かせます。

Snowflakeの仮想ウェアハウスの考え方はSnowflakeとはにまとめています。Vaultロードと、BIの参照を別ウェアハウスで動かすと、ロード中の重さがダッシュボードに影響しません。

Streams ＆ Tasks で増分ロードを自動化する

SnowflakeのStreams（変更データを追跡）とTasks（スケジュール実行）を組み合わせると、Bronzeから来た新しいデータだけをVaultにロードできます。バッチ全件再処理を避けられます。

-- BronzeにStreamを張り、変更分だけを追跡
CREATE STREAM stream_bronze_customer ON TABLE bronze.customer;

-- 定期実行するTaskでVaultへロード
CREATE TASK task_load_hub_customer
  WAREHOUSE = WH_VAULT_LOAD
  SCHEDULE = '15 MINUTE'
AS
  INSERT INTO silver.hub_customer
  SELECT DISTINCT
    MD5_BINARY(UPPER(TRIM(customer_id))) AS hub_customer_hk,
    customer_id AS customer_bk,
    CURRENT_TIMESTAMP() AS load_date,
    'ec_main' AS record_source
  FROM stream_bronze_customer
  WHERE METADATA$ACTION = 'INSERT';

dbt Cloud や Airflow を使ってオーケストレーションする場合は、TasksではなくdbtのジョブやAirflow DAGで同様の流れを組みます。dbtでの実装パターンはモデリング手順を、dbtでの層分けはdbt実装ガイドを参照してください。

PIT / Bridge で Gold変換を速くする

Vaultの上にGold層のマートを作るとき、Hub・Link・Satelliteを大量にJOINするとクエリが重くなります。PIT（Point-in-Time）テーブルとBridgeテーブルは、その重さを下げるための補助テーブルです。

最初から作り込む必要はありません。Gold層の変換が遅くなったら、計測してから足すのが現実的です。Snowflakeのクエリプロファイルで重いJOINを特定し、そこにPITやBridgeを当てると効果が見えやすくなります。

よくあるSnowflake特有の落とし穴

• VARCHARの照合順序：大文字小文字や全角半角の違いがハッシュに影響する。正規化を入口で統一する。
• NULL の COALESCE 忘れ：NULLを含むカラムをそのまま連結すると、結果がNULLになりhash_diffも一致しない。COALESCEで空文字に置き換えるルールにする。
• Time Travelに頼りすぎる：Snowflakeには時間遡及機能があるが、Data Vaultの履歴は標準保持期間（最大90日）を超える。長期履歴はVaultで明示的に持つ。
• ウェアハウスを大きくしすぎる：差分が少ない日のロードに大きなウェアハウスは無駄。Auto Suspend を短く、サイズは負荷に応じて調整。

まとめ

• ハッシュキーはMD5_BINARYが標準。ビジネスキーは入口で UPPER・TRIM・COALESCE で正規化する。
• Satelliteの差分検知は QUALIFY＋ROW_NUMBER で前回の最新行と比較する。
• ロードと参照は仮想ウェアハウスを分けて干渉を避ける。
• Streams＆Tasksで増分ロードを自動化できる。dbt/Airflow派は同じパターンをDAGで組む。
• Gold変換が遅くなったらPIT・Bridgeを後付け。

Data Vaultの全体像はData Vault 2.0 入門、スタースキーマとの使い分けはスタースキーマとの判断軸、モデリング手順はモデリング手順にあります。Snowflake上での設計や移行を一緒に詰めたいときは、DE-STKの初回相談（30分・無料）をご利用ください。

よくある質問（FAQ）

Q. ハッシュキーはBINARYとVARCHAR、どちらで持つべきですか？

A. BINARYを推奨します。サイズが小さく、JOINも速いです。VARCHARで16進文字列として持つと、ストレージとJOINコストが増えます。Snowflakeでは MD5_BINARY などBINARY返却の関数を使うとそのまま扱えます。BI側で人が読む必要がある場面でだけ、HEX_ENCODE で文字列化します。

Q. Snowflake以外のDWHでも同じパターンで作れますか？

A. 基本パターンは共通です。BigQueryやDatabricksでも、MD5/SHA系のハッシュ関数と差分検知のロジックは同じ考え方で実装できます。違いが出るのは、増分ロードの仕組み（BigQueryならスケジュールクエリ、DatabricksならDelta LiveTables など）と、ウェアハウスの考え方です。クラウドDWH別の特徴はクラウドDWH入門にまとめています。

Q. Streams ＆ Tasks と dbt、どちらでオーケストレーションすべきですか？

A. dbtで変換ロジックを管理する組織なら、dbtジョブで動かすのが素直です。Snowflake内で完結させたい・SQL中心の組織なら Streams＆Tasks も有力です。混在運用も可能で、入口の取り込みは Streams＆Tasks、変換は dbt、というハイブリッドもよく見ます。組織のスキルセットとツール戦略に合わせて選んでください。

Q. Snowflakeのコストを抑えるには、何に気をつけますか？

A. Data Vaultの場合、いちばん効くのはウェアハウスのサイズ最適化です。差分が少ない時間帯のロードに大きなウェアハウスを動かしっぱなしにしないこと、Auto Suspend を短く設定すること、用途別にウェアハウスを分けて不必要な共有を避けることが基本です。Gold変換だけ大きなウェアハウスにする、といった分け方が、費用と性能のバランスを取りやすくします。