最初のダッシュボードを超えてスケールするSQLとデータモデリング

これまで3つのウェアハウスを引き継いできましたが、診断は毎回同じです。金曜日の午後4時にSlack DMに誰かが貼り付けたクエリがある。マーケティングがその上にダッシュボードを構築した。セールスはさらにその上に別のダッシュボードを構築した。6ヶ月後、CFOが同じミーティングで両方を開いて2つの異なる収益の数字を見て、四半期の残り期間、データチームへの信頼を失います。

解決策はより賢いSQLではありません。規律です。レイヤー、テスト、命名、そして廃止サイクル。このガイドがその規律です。JOINの方法はすでに知っていて、「なぜこのダッシュボードは違う数字を言っているんだ」という対応から抜け出したいアナリストのために書いています。

Slackスレッドのクエリ

こうして始まります。#revopsで誰かが質問する。あなたが本番レプリカに対して60行のクエリを書いて、スレッドに答えを投稿して、次に進む。そのクエリがModeノートブックにコピーされる。ModeノートブックがLookerのタイルになる。Lookerのタイルがボードデッキのグラフになるわけです。

90日後に基礎となるスキーマが変わり、クエリがサイレントに壊れ、グラフはまだ表示されているので誰も気づかない。ただし数字は間違っています。あるいは悪いことに、クエリは壊れていないが「アクティブ顧客」の定義が一つも名付けられておらず、3つのチームが3つの異なる数値を見ています。

Slackスレッドのクエリは、Slackスレッドのクエリとしては問題ありません。問題は「参照」から「基幹ダッシュボード」への経路に、その間に何のチェックポイントもないことです。チェックポイントが必要です。そのチェックポイントがモデルです。

収益の定義が2つあることは信頼を失うイベント

財務とプロダクトが同じミーティングに2つの異なる収益の数字を持ち込んだ日に、1年分の信頼を失い、6ヶ月かけてそれを取り戻すことになります。3つの異なる会社でそれを目撃してきました。ツールの問題ではありません。Snowflakeは失敗していません。dbtも失敗していません。失敗したのは、5人が5つのクエリを書く前に「収益」が何を意味するかについて誰も決断しなかったことです。

これは予防可能なエンジニアリングです。レイヤーとテストが2つの収益問題を防ぎます。命名と廃止がウェアハウスの腐敗を防ぎます。作業は意図的に退屈です。データスタックが日々エキサイティングに感じられるなら、何かが燃えています。

実際にスケールするSQLの習慣

4つの習慣が、ウェアハウスを引き継ぎ可能なアナリストとコードが退職後に死ぬアナリストを分けます。

ネストされたサブクエリよりCTE。 ネストされたサブクエリは書き込み専用の成果物です。書くことはできますが、6週間後には自分でも読めず、次の人も読めません。CTEは各ステップに名前をつけ、各ステップをデバッグし、1ブロック1つのメンタルモデルで考えることができます。

-- これはやめる
SELECT customer_id, SUM(amount)
FROM (
  SELECT * FROM orders
  WHERE status = 'completed'
    AND created_at >= (SELECT MIN(created_at) FROM orders WHERE ...)
) o
JOIN (SELECT * FROM customers WHERE deleted_at IS NULL) c
  ON o.customer_id = c.id
GROUP BY 1;

-- こうする
WITH completed_orders AS (
  SELECT *
  FROM {{ ref('stg_shopify__orders') }}
  WHERE status = 'completed'
),

active_customers AS (
  SELECT *
  FROM {{ ref('stg_shopify__customers') }}
  WHERE deleted_at IS NULL
),

revenue_by_customer AS (
  SELECT
    c.customer_id,
    SUM(o.amount) AS lifetime_revenue
  FROM active_customers c
  LEFT JOIN completed_orders o
    ON o.customer_id = c.customer_id
  GROUP BY c.customer_id
)

SELECT * FROM revenue_by_customer;

2番目のバージョンは長いです。でもデバッグできます。どのCTEでも選択して単独で実行し、行数を確認できます。それがすべてです。

すべてのモデルに命名テスト。 「後でテストを追加する」ではなく、すべてのモデルに、同じPRで。役割を果たす4つのテスト。

# models/marts/_marts.yml
version: 2

models:
  - name: dim_customers
    columns:
      - name: customer_id
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['active', 'churned', 'paused']
      - name: account_owner_id
        tests:
          - relationships:
              to: ref('dim_users')
              field: user_id

4つのテスト、15行のYAML、そしてボードデッキに表示されるサイレントな壊れの90%を発見できます。uniqueとnot_nullはすべての主キーに。accepted_valuesはすべてのEnum型のカラムに。relationshipsはすべての外部キーに。キーをテストしなければ、モデルではなくクエリです。

ビジネスエンティティごとに信頼できる唯一の情報源モデルを1つ。 dim_customersは1つ。fct_ordersは1つ。dim_usersは1つ。dim_customers_v2でも、dim_customers_marketingでも、dim_customers_for_finance_q3でもありません。マーケティングと財務が異なる顧客の切り方を必要とする場合、同じdim_customersの異なるカラムを使うか、その上にダウンストリームのマート層を構築します。テーブルをクローンする権利はありません。

クローンが2つの収益問題を生み出す原因です。即座に排除してください。

セルフJOINよりウィンドウ関数。 「前回の注文日」や「顧客内のランク」を計算するためにテーブルをセルフJOINしているなら、無駄な労力をかけています。LAG()、LEAD()、ROW_NUMBER()、RANK()は同じ仕事を1回のパスで、半分の行数とウェアハウスコストの10分の1で実行します。

データモデリングのレイヤー: ステージング、中間、マート

これが誰もやりたがらない部分です。余分な作業に見えるからです。余分な作業ではありません。後に続くすべてを可能にする作業です。

ステージング（stg_）。 ソーステーブルごとに1つのモデル。カラム名をプロジェクトの規則に変更する。型を変換する。それだけです。テーブル結合も、ビジネスロジックも、集計も不要。ステージング層の役割は、すべてのダウンストリームモデルに1つのソーステーブルのクリーンで予測可能な名前付きビューを提供することです。「ただのリネームだから」とステージングをスキップすると、Stripeがカラム名を変えたり、Fivetranの同期がケースの大小を変えたりした日に後悔します。

-- models/staging/stripe/stg_stripe__charges.sql
WITH source AS (
  SELECT * FROM {{ source('stripe', 'charges') }}
),

renamed AS (
  SELECT
    id              AS charge_id,
    customer        AS customer_id,
    amount / 100.0  AS amount_usd,
    status          AS charge_status,
    created::TIMESTAMP AS charged_at
  FROM source
)

SELECT * FROM renamed;

これがモデルの全体です。退屈です。それが目的です。

中間（int_）。 テーブル結合、ビジネスロジック、再利用可能な構成要素はここに存在します。中間モデルはBI ツールに公開されません。マート層のモデルを読みやすくし、パーツを再利用できるようにするために存在します。3つのマートすべてが「顧客ごとの月次MRR」を必要とする場合、それは1回構築して3回参照されるint_customer_mrr_monthlyモデルです。

-- models/intermediate/int_customer_mrr_monthly.sql
WITH subscriptions AS (
  SELECT * FROM {{ ref('stg_stripe__subscriptions') }}
),

months AS (
  SELECT * FROM {{ ref('int_calendar_months') }}
),

active_in_month AS (
  SELECT
    m.month_start,
    s.customer_id,
    s.monthly_amount_usd
  FROM months m
  LEFT JOIN subscriptions s
    ON m.month_start BETWEEN s.started_at AND COALESCE(s.canceled_at, '9999-12-31')
)

SELECT
  customer_id,
  month_start,
  SUM(monthly_amount_usd) AS mrr_usd
FROM active_in_month
GROUP BY 1, 2;

マート（fct_ / dim_）。 これがBI ツールがクエリするものです。非正規化されていて、ビジネス上読みやすく、経営幹部が名付けるように名前がついています。fct_はファクトテーブル（注文、請求、セッション）。dim_はディメンションテーブル（顧客、製品、従業員）。マート層はウェアハウスのAPIです。APIの変更のように扱ってください。バージョン管理され、周知され、期日を設けて廃止されます。

10人のデータチームは通常、これら3つのレイヤーにわたって200〜400のモデルを持つことになります。おおよそ50〜60%がステージング、25〜30%が中間、15〜20%がマートです。マート数がその比率を超えて増えているなら、中間にあるべきビジネスロジックをマートに入れています。

「収益の定義が2つある」問題の診断

これは20分で実行できます。引き継いだウェアハウスで新しいモデルを書く前に実施してください。

1. タイトルやカラムに「収益」という言葉を含むすべてのダッシュボードのタイル、BIレポート、保存済みクエリを取得する。
2. それぞれのSQLを1つのドキュメントにコピーする。
3. WHERE句、テーブル結合の条件、SUMの対象を比較する。
4. テーブルを作成する: ダッシュボード名、オーナー、定義、ソース。
5. 財務部門に持っていく。1つの定義を書面でコミットしてもらう。
6. コミットされた定義に合致するfct_revenueモデルを1つ構築する。
7. すべてのダッシュボードをそのモデルに移行する。古いクエリを廃止する。

多くのチームは最初の監査で3〜5種類の異なる収益定義を見つけます。場合によってはすべてが何らかの限定的な目的では「正しい」が、CFOが決算発表で弁護するのは1つだけです。それがモデルに入るものです。他のものは上に構築されるビューとして書き換えます（vw_revenue_marketing_attributed、vw_revenue_booking_basis）。これにより、別々の数字ではなく同じ数字のカットであることが明らかになります。

「アクティブ顧客」、「解約」、「ARR」、「MRR」に対して同じ監査を実行します。同じ問題が見つかります。同じ方法で修正します。

dbtの命名規則

命名はバイクシェディング（些細なことへのこだわり）ではありません。次のアナリストがウェアハウスで迷わずに方向を把握できる方法です。

• stg_{source}__{entity}。ダブルアンダースコアがソースとエンティティを区切ります。stg_stripe__charges、stg_hubspot__contacts、stg_shopify__orders。ダブルアンダースコアは左側がシステム名であることを示す視覚的な手がかりです。
• int_{entity}_{verb}。モデルが何を「する」かを説明します。int_orders_pivoted、int_customers_enriched、int_sessions_attributed。
• fct_{event}とdim_{entity}。例: fct_orders、fct_charges、fct_page_views、dim_customers、dim_products、dim_dates。
• テストではソースエイリアスを使い、マートSQLではカラムエイリアスを使わない。dim_customers.customer_idはcustomer_idであるべきで、dim_customers__customer_idではありません。

_v2のサフィックスは問題のサイン。 dim_customers_v2.sqlがdim_customers.sqlの隣にある場合、誰かが元のものを壊すのが怖くてフォークをリリースしました。バージョン管理はgitに属するものであり、ファイル名には属しません。正しい対応は: dim_customersを書き直して、マイグレーションノートとともに破壊的変更をリリースして、旧い動作をカレンダー上で廃止することです。両方を永遠に維持することは、2つの収益問題が別の形で戻ってくることを保証します。

バージョン管理と変更ログの規律

mainへの直接マージはしない。いかなるときも。1行のタイポ修正でも。例外を設けた瞬間に、それがルールになります。

すべてのPRはCHANGELOG.mdに1行のエントリを持ちます。

## 2026-04-29
- `fct_orders`: `is_first_order`カラムを追加。影響: marketing-attribution, lifecycle-emailsダッシュボード。(@camellia)
- `breaking: dim_customers`: `acct_owner`を`account_owner_id`にリネーム。影響: すべてのCRMダッシュボード。マイグレーション: #1284参照。(@camellia)
- `stg_stripe__charges`: ゲストチェックアウトからのNull `customer_id`を処理。(@camellia)

CHANGELOGの3つのルール。

1. すべてのPRがエントリを追加する。エントリなしにPRはリリースしない。CIで強制できます。
2. スキーマ変更はbreaking:タグをつけて、PRがマージされる前に#data-changesで通知する。
3. 「影響」フィールドはダッシュボードまたはダウンストリームのモデル名を指定し、「各種レポート」のような曖昧な言葉は使わない。影響するものを名指しできなければ、マージするほどダウンストリームのグラフを把握できていません。

スキーマ変更にはカレンダー上の廃止期限も設けます。古いカラムはコメント付きで30日間残り、新しいカラムは並行して追加され、ダウンストリームの利用者が移行し、それから古いカラムが削除されます。「簡単な」リネームに例外はありません。簡単なリネームが、誰かがあなたのウェアハウスの上にZapierフローを構築していて誰にも知らされていなかったことを発見するきっかけです。

ダッシュボードか1回限りか: 何をいつ作るか

判断ルール: この数字は30日以内にまた聞かれますか?

• はい → ダッシュボードです。数字はモデルの後ろにある。ダッシュボードにはオーナーがいる。
• いいえ → CSVを送る。SQLを依頼スレッドまたはPRにリンクする。次に進む。

ミスはアドホックな依頼のたびにダッシュボードを構築することです。6ヶ月後には400個のダッシュボードがあり、そのうち350個はリリース当日以来開かれておらず、作ったアナリストは退職しています。

ダッシュボードにはオーナーフィールドがあります。 「データチーム」ではなく、名前です。オーナーが指定されていないダッシュボードはアーカイブしてください。オーナーの仕事は、ステークホルダーが「これはまだ正しいですか?」と聞いてきたときに答えることです。誰もオーナーでなければ、誰も答えられず、信頼の問題に戻ります。

1回限りのSQLはdbtプロジェクトのanalyses/フォルダに入れて、コミットしてレビューしますがモデル化はしません。これにより、ウェアハウスを膨らませることなく記録が残ります。

廃止サイクル

ウェアハウスは屋根裏部屋のように腐敗します。ゆっくりと、そして突然に。それを防ぐサイクル。

• 最終閲覧日による四半期ごとのダッシュボードレビュー。 ほとんどのBI ツールがこれを提供しています。四半期ごとにリストを取得します。
• 60日間未閲覧 → 廃止バナー。 「このダッシュボードはYYYY-MM-DDにアーカイブされる予定です」という表示。オーナーに通知。決断を迫ります。
• 90日間未閲覧 → アーカイブ。 archive/フォルダに移動。基礎となるモデルをdbtの設定でenabled: falseにマークして、構築を止めます。

# models/marts/legacy/_legacy.yml
models:
  - name: fct_orders_2024_legacy
    config:
      enabled: false
    description: |
      2026-04-29アーカイブ, fct_ordersに置き換えられました。
      最終クエリ: 2026-01-12。オーナーは2025年Q4に退職。

このサイクルを1年続けると、ダウンストリームからの苦情なしにモデル数を30〜40%削減できます。削除したモデルはすでに死んでいたものです。単に公式にするだけで、計算コストをかけ続けることも新人アナリストを混乱させることもなくなります。

よくある落とし穴

最もよく見るもの4つを、与えるダメージの大きさ順にランキングします。

1. 定義に合意する前にモデリングする。 まず監査をして、定義を得てから、モデルを構築する。逆の順序では、新しいウェアハウスに2つの収益問題を生み出します。
2. 「ただのリネームだから」とステージングをスキップする。 ステージングをスキップすると、ソーススキーマが変わった日にすべてのダウンストリームモデルが壊れます。ステージングは緩衝材です。退屈な税を払ってください。
3. _v2ファイルが増殖するのを放置する。 すべての_v2は誰かが元のものを壊すのが怖かったことの告白です。元のものを修正してください。変更を周知する。1つのモデルをリリースする。
4. 重要なモデルにオーナーがいない。 オーナーのないfct_revenueはSQLファイルが添付された負債です。オーナーフィールドはモデルのYAMLで必須です。テストと同様に。

「完成」した状態とはどんな状態か

この規律を構築できたとき。

• すべてのビジネス指標を定義するモデルがちょうど1つある。
• すべてのモデルに、主キーに対して少なくともuniqueとnot_nullのテストがある。
• すべてのダッシュボードに名前のついたオーナーがいる。
• 新しいアナリストが任意のダッシュボードの任意の数字を、誰にも聞かずに10分以内でそのソース（マート、中間、ステージング、ソースを経由して）まで追跡できる。
• CHANGELOGがSlackを開かずに先週何が変わったかを教えてくれる。
• 廃止されたものを削除しているので、モデル数が増えながらも計算コストが横ばいか減少している。

これがバーです。退屈です。そして午後11時に「ボードデッキの数字が合わない」と呼び出されるチームでなくなる方法でもあります。

関連記事

• データアナリスト職務記述書テンプレート
• ステークホルダーとの翻訳: 曖昧な依頼をリリース可能な分析に変える
• 意思決定を促すダッシュボードデザイン
• データアナリストのよくある落とし穴