dbt-utils活用！作業効率を劇的に上げるSQLジェネレーターガイド

• 目的: ソーステーブルの全カラムを、冗長な記述なしに選択しつつ、特定のカラムを除外したり、追加したりします。
• 課題: SELECT *は便利ですが、不要なカラムが含まれるリスクや、将来のカラム追加で意図しない影響が出るリスクがあります。手動で全カラムを記述するのは非常に手間です。
• 解決策: star()マクロを使えば、親モデル（sourceやref）を指定するだけで全カラム名を自動で展開し、except引数で除外したいカラムを指定できます。

select
    {{ dbt_utils.star(
        from=source('jaffle_shop', 'orders'), except=['_etl_loaded_at']
    )}}
from {{ source('jaffle_shop', 'orders') }}

• 目的: 特定のキーと優先順位の条件に基づいて、重複行を簡単に削除します。
• 課題: 重複行を削除する際、ROW_NUMBER()とPARTITION BYを使った複雑なサブクエリを記述する必要があります。
• 解決策: deduplicate()マクロを使えば、キーとなるカラムとソート順を指定するだけで、SQLを自動で生成してくれます。

-- models/stg_customers.sql
{{ dbt_utils.deduplicate(
    relation=ref('raw_customers'),
    partition_by='customer_id',
    order_by='updated_at desc'
) }}

• 目的: 複数のテーブルを統合する際のUNION ALLを自動化します。
• 課題: 異なるスキーマやカラムを持つ複数のテーブルをUNION ALLで結合する場合、カラムの不一致を手動で調整する必要があります。
• 解決策: union_relations()マクロは、指定した複数のリレーション（テーブル）からユニオンを行い、カラムの不一致も自動でハンドリングしてくれます。

-- models/all_web_events.sql
{{ dbt_utils.union_relations(
    relations=[ref('stg_web_events_1'), ref('stg_web_events_2')]
) }}

• 目的: 特定の列の値を、新しい列として展開します。
• 課題: CASE WHENや集計関数を使って手動でピボット（転置）するSQLは、冗長で保守性が低くなります。特にカテゴリが増える可能性がある場合は管理が大変です。
• 解決策: pivot()マクロを使えば、ピボットしたい列、集計したい値、ピボット後の新しい列名を指定するだけで、SQLを自動生成します。

-- models/pivot_sales.sql
select
  customer_id,
  {{ dbt_utils.pivot(
      'order_status',
      dbt_utils.get_column_values(ref('stg_orders'), 'order_status'),
      agg='sum(amount)',
      then_value='amount'
  ) }}
from {{ ref('stg_orders') }}
group by 1

• 目的: pivot()とは逆に、複数の列を1つの列にまとめます。
• 課題: 複数の列をキーと値のペアに分解するUNPIVOT操作は、SQLの記述が複雑になりがちです。
• 解決策: unpivot()マクロを使えば、アンピボットしたい列と、キー・値の新しい列名を指定するだけで、SQLを自動生成します。

-- models/unpivot_sales.sql
{{ dbt_utils.unpivot(
    relation=ref('stg_sales'),
    cast_to='numeric',
    field_name='metric',
    value_name='sales_amount'
) }}

• 目的: 指定した開始日と終了日の間で、連続した日付リストを生成します。
• 課題: 存在しない日付のデータも分析に含めたい場合、カレンダーテーブルを自作するか、再帰CTE（Common Table Expression）などで複雑なSQLを記述する必要があります。
• 解決策: date_spine()マクロを使えば、開始日、終了日、期間（日、月など）を指定するだけで、必要な日付のリストを簡単に生成できます。

-- models/date_spine.sql
{{ dbt_utils.date_spine(
    datepart="day",
    start_date="cast('2023-01-01' as date)",
    end_date="cast('2023-12-31' as date)"
) }}

• 目的: 特定の範囲で連続した数値を生成します。
• 課題: 連続した数値リストが必要な場合に、手動で値を入力したり、再帰CTEを使う手間がかかります。
• 解決策: generate_series()マクロを使えば、開始値と終了値を指定するだけで、連続した数値のリストを生成できます。

-- models/series_of_numbers.sql
{{ dbt_utils.generate_series(20) }}

• 目的: 複数のカラムを組み合わせて、一意な代理キー（Surrogate Key）を生成します。
• 課題: 複合主キーを生成する際、複数のカラムをCONCATなどで結合すると、SQLが複雑になり、可読性が低下します。また、NULL値の扱いやハッシュ関数の選択にも気を配る必要があります。
• 解決策: generate_surrogate_key()マクロを使えば、指定したカラムリストを自動でハッシュ化し、一意なキーを生成します。

-- models/int_order_items.sql
select
    {{ dbt_utils.generate_surrogate_key(['order_id', 'item_id']) }} as order_item_key,
    order_id,
    item_id,
    ...
from {{ ref('stg_orders') }}

• 目的: ゼロ割エラーを防ぎ、計算結果を安全に返します。
• 課題: denominatorがゼロになる可能性がある場合、NULLIF(denominator, 0)のような記述を毎回追加する必要があり、冗長です。
• 解決策: safe_divide()マクロを使えば、分子と分母を指定するだけで、分母がゼロの場合はNULLを返す安全な計算が実現します。

-- models/mart_sales_metrics.sql
select
    revenue,
    cost,
    {{ dbt_utils.safe_divide('revenue', 'cost') }} as margin_ratio
from {{ ref('int_monthly_sales') }}

• 目的: NULL値を無視して安全に足し算や引き算を行います。
• 課題: SQLでは、NULLと数値を計算すると結果がNULLになります。COALESCE(value, 0)を毎回記述するのは手間です。
• 解決策: safe_add()やsafe_subtract()マクロを使えば、NULLを自動で0として扱い、意図せず計算結果がNULLになることを防ぎます。

-- models/int_customer_metrics.sql
select
    customer_id,
    {{ dbt_utils.safe_add(['purchase_count', 'refund_count']) }} as total_transactions
from {{ ref('stg_customer_events') }}

• 目的: 緯度と経度から、2点間のハバーシン距離（球面上の最短距離）を計算します。
• 課題: データベースのネイティブ関数がない場合、複雑な数学関数を組み合わせて手動でSQLを記述する必要があります。
• 解決策: haversine_distance()マクロを使えば、2点分の緯度と経度を指定するだけで、正確な距離を簡単に算出できます。

-- models/haversine_calculation.sql
select
    from_city,
    to_city,
    {{ dbt_utils.haversine_distance(
        from_lat='from_latitude',
        from_long='from_longitude',
        to_lat='to_latitude',
        to_long='to_longitude',
        unit='miles'
    ) }} as distance_miles
from {{ ref('stg_city_pairs') }}

• 目的: データを等間隔のバケツ（グループ）に分類します。
• 課題: 特定の数値をいくつかのカテゴリに分類する際、CASE WHENを連発するSQLは可読性が低く、保守が大変です。
• 解決策: width_bucket()マクロを使えば、バケツの数や範囲を指定するだけで、データを簡単に分類できます。

-- models/customer_buckets.sql
select
    customer_id,
    revenue,
    {{ dbt_utils.width_bucket(
        'revenue',
        min_value=0,
        max_value=1000,
        num_buckets=5
    ) }} as revenue_bucket
from {{ ref('stg_customers') }}

• 目的: GROUP BY句を簡単に作成します。
• 課題: 多くのカラムでグループ化したい場合、カラム名を一つずつ記述する手間がかかり、ミスが発生しやすいです。
• 解決策: group_by()マクロを使えば、数字のインデックス（例: 1,2,3...）を指定するだけで、SQLを自動生成します。

-- models/daily_sales.sql
select
    date_day,
    product_category,
    sum(sales_amount) as total_sales
from {{ ref('stg_orders') }}
group by {{ dbt_utils.group_by(2) }}