Snowflake ウェアハウス最適化

概念 1: コスト対パフォーマンス

ウェアハウスのサイズを1レベル増やすたびに、コンピューティングインスタンスの CPU と RAM を2倍にしています。対応して、消費するクレジットを通じて価格も2倍にしています。

価格が2倍になる一方で、理想的な世界では適切なワークロード/クエリに対して、コンピューティングインスタンスのリソースが2倍になるためクエリ時間は半減します。したがって、この状況では、どちらのウェアハウスでも同じ価格を支払いますが、大きいウェアハウスはクエリの実行時間を半分にするため、より効率的な選択肢になります。

最終的に、ウェアハウスをアップサイジングし続けると、クエリの実行時間がわずかに短縮されるだけでコストが不釣り合いに増加する収穫逓減のポイントに達します。 こちらに select.dev のコスト対パフォーマンスダイアグラムがあり、この点を示しています [1]。

概念 2: ウェアハウス効率の定義

ウェアハウス効率にはコストとパフォーマンスの2つの次元があります。「ウェアハウス効率」はモデル効率とは異なる概念です。

2つの次元があるため、「ウェアハウス効率」には明確な定義がありません。「コスト」と「パフォーマンス」をどれだけ重視するかに基づいて決定する必要があります。

私たちの場合、異なるウェアハウスを使用する際に次の2つの結果のいずれかを望んでいます:

• 既存の実行時間を維持しながらコストを削減する
• コストを大幅に増やさずに実行時間を短縮する

上記の要件に基づき、ウェアハウス効率を次のように明確に定義します:

ウェアハウスは、次のサイズが小さいウェアハウスと比較したとき、クエリの実行時間が 40% 以上短縮される場合に効率的です。

説明: 前のセクションで説明したように、次のサイズが大きいウェアハウスが損益分岐点に達するためには、2倍の速さで実行する（クエリの実行時間が**50%**短縮される）必要があります。これにより 2 倍の価格増加が相殺されます。

私たちの定義では、実行時間が少なくとも 40% 短縮されることを要求しており、これはコストの最大 20% 増加に相当します。実行時間の 50% 削減（コストの損益分岐点）を要求しない理由は次のとおりです:

• 既存の dbt 実行時間に近いパフォーマンスを維持する必要があります
• イテレーション的に作業したいため、より多くの余裕を持つ現実的な目標を設定し、さらに学ぶにつれてゴールポストを調整します。

少しあいまいに聞こえるかもしれないので、いくつかの仮想的な例を示します:

仮想的な例

例 1

実行時間が 50% 短縮され、1時間 -> 30分になりました。この場合、ウェアハウスサイズを増やすことは当然の選択です:

例 2

実行時間が 40% 短縮され、1時間 -> 36分になりました。一方、コストは $2 から $2.40 に 20% 増加しました:

この場合、ウェアハウスのアップサイジングは当然の選択ではなくなります。コストとクエリの実行時間をどれだけ重視するかによって異なります。

コストのみを気にする場合は X-small を選び、速度のみを気にする場合は Small を選びます。

私たちの場合、両方のバランスを見つけたいため、以前に確立したガイドラインを使用します:

ウェアハウスは、次のサイズが小さいウェアハウスと比較したとき、クエリの実行時間が 40% 未満の場合は効率的と見なします。

この場合、クエリの実行時間が 40% 短縮されており、「効率的」と見なされる閾値にちょうど達しているため、「S」ウェアハウスを選ぶべきです。

例 3

例 3 では、実行時間が 20% 短縮され、1時間 -> 48分になりました。一方、コストは $2 から $3.20 に 60% 増加しました:

この場合、実行時間が 20% しか短縮されていないため、クエリは「非効率」と見なされます。「効率的」と見なされるためには、実行時間が 40% 以上短縮される必要があります。したがって、「XS」ウェアハウスを選ぶべきです。

理論的に「最適な」ウェアハウスとは何かを説明しましたが、実際には各モデルに対して適切なウェアハウスをどのように選択するのでしょうか？

手順は以下のとおりです:

1. テーブルのパーティション数を把握するために explain plan を実行する
2. パーティション数に基づいて、select.dev パーティション数ダイアグラムを使用して開始ウェアハウスを推定する
3. 前の手順の推定ウェアハウスを使用して dbt でクエリを実行する
4. 下の「ベンチマークのヒューリスティック」セクションを確認して、すぐにウェアハウスを割り当てられるかどうか確認する。できない場合は続ける
5. この Google スプレッドシートを使用して クエリ時間 を記録する
6. ウェアハウスをアップサイジングする
   • 次に高いウェアハウスでクエリを再実行する
   • Google スプレッドシートに結果を再度記録する
   • コストがパフォーマンスより速く増加したら停止する

以下のセクションでは次の内容を確認します:

• ベンチマークを dbt で行う必要がある理由
• これらの手順に従ってウェアハウスを適切にサイジングする2つの例:
  1. シンプルなクエリ: gitlab_dotcom_deployment_todo_dedupe_source
  2. より多くのパーティションを持つより複雑なクエリ: prep_ci_stage

{% if is_incremental() %}
  {{ config(
      warehouse='smaller_warehouse'  # Use this warehouse for incremental runs
  ) }}
{% else %}
  {{ config(
      warehouse='larger_warehouse'  # Use this warehouse for full refresh runs
  ) }}
{% endif %}

SELECT *
FROM my_table

EXPLAIN
SELECT
  *
FROM
  "RAW".tap_postgres.GITLAB_DB_TODOS
QUALIFY ROW_NUMBER() OVER ( PARTITION BY id ORDER BY _uploaded_at DESC) = 1;

ALTER SESSION SET USE_CACHED_RESULT = FALSE;

EXPLAIN
WITH
  dim_project AS (
    SELECT
      *
    FROM
      "PROD".common.dim_project
  ),
  dim_ci_pipeline AS (
    SELECT
      *
    FROM
      "PROD".common.dim_ci_pipeline
  ),
  dim_namespace_plan_hist AS (
    SELECT
      *
    FROM
      "PROD".common.dim_namespace_plan_hist
  ),
  dim_date AS (
    SELECT
      *
    FROM
      "PROD".common.dim_date
  ),
  ci_stages AS (
    SELECT
      *
    FROM
      "PREP".gitlab_dotcom.gitlab_dotcom_ci_stages_dedupe_source
    WHERE
      created_at IS NOT NULL
  ),
  joined AS (
    SELECT
      ci_stages.id AS dim_ci_stage_id,
      IFNULL(dim_project.dim_project_id, -1) AS dim_project_id,
      IFNULL(dim_ci_pipeline.dim_ci_pipeline_id, -1) AS dim_ci_pipeline_id,
      IFNULL(dim_namespace_plan_hist.dim_plan_id, 34) AS dim_plan_id,
      IFNULL(dim_namespace_plan_hist.dim_namespace_id, -1) AS ultimate_parent_namespace_id,
      dim_date.date_id AS created_date_id,
      ci_stages.created_at::TIMESTAMP AS created_at,
      ci_stages.updated_at::TIMESTAMP AS updated_at,
      ci_stages.name AS ci_stage_name,
      ci_stages.status AS ci_stage_status,
      ci_stages.lock_version AS lock_version,
      ci_stages.position AS POSITION
    FROM
      ci_stages
      LEFT JOIN dim_project ON ci_stages.project_id = dim_project.dim_project_id
      LEFT JOIN dim_namespace_plan_hist ON dim_project.ultimate_parent_namespace_id = dim_namespace_plan_hist.dim_namespace_id
      AND ci_stages.created_at >= dim_namespace_plan_hist.valid_from
      AND ci_stages.created_at < COALESCE(dim_namespace_plan_hist.valid_to, '2099-01-01')
      LEFT JOIN dim_ci_pipeline ON ci_stages.pipeline_id = dim_ci_pipeline.dim_ci_pipeline_id
      INNER JOIN dim_date ON TO_DATE(ci_stages.created_at) = dim_date.date_day
  )
SELECT
  *,
  '@mpeychet_'::VARCHAR AS created_by,
  '@chrissharp'::VARCHAR AS updated_by,
  '2021-06-29'::DATE AS model_created_date,
  '2022-06-01'::DATE AS model_updated_date,
  CURRENT_TIMESTAMP() AS dbt_updated_at,
  CURRENT_TIMESTAMP() AS dbt_created_at
FROM
  joined;

ALTER SESSION SET USE_CACHED_RESULT = FALSE;

前置きセクションで述べたように、non-product dbt タスク内のすべてのモデルを適切にサイジングするベンチマークがこのベンチマーク Issue に従って実施されました。最小のモデルを適切にサイジングしても、これらのタイプのモデルが実行内のモデルの 90% 以上を占めているにもかかわらず、大幅なコスト削減にはつながらない（まったくつながらない場合もある）ことが示されています。

したがって、dbt.py DAG のコンテキストでは、最も高コストのモデルに対してベンチマークが最も価値があります。Snowflake の query_attribution テーブルを使用してこれらのモデルを見つけることができます。

上記の注意点は、本番の dbt.py DAG 以外の環境でも dbt モデルを実行することです:

• ローカル
• CI ジョブ

後者の環境では、モデルが小さい場合でも、すべてのモデルに対してウェアハウスを適切にサイジングすることが意味をなす場合があります。これらの場合、適切にサイジングする方法はそれほど精密である必要はありません。つまり、複数のウェアハウスサイズと比較する必要はなく、モデルによってスキャンされるパーティション数に基づいて推定ウェアハウスをすぐに割り当てることができます。

まとめると、ウェアハウスの適切なサイジングの優先順位は、最も長く実行されて最も多くのクレジットを消費する最大のモデルであるべきです。これらのモデルは消費される総クレジットの不均衡な割合を占めているためです。