create-dbt-transformations

dbt 変換を作成する

このスキルを使って、SQL モデルを生成し、ソースを宣言し、schema.yml を更新して、dbt run ／ dbt test でモデルを検証します。

BigQuery の調査（データセットの一覧表示、raw テーブルの確認、行のプレビュー、NULL 率の確認）には、explore-lakehouse スキルを読み込みます。このスキルがインストールされていない場合は、次の方法にフォールバックします。

bq show --format=prettyjson $REVOS_BQ_DATASET.<table>

ユーザーに次のように警告します：「explore-lakehouse スキルがインストールされていません。代わりに bq show を使用します。より詳細なスキーマ調査を行うには、インストールしてください。」

---

パート 1：ナレッジベース

レイヤーの規約

• gold — レポート作成や下流での利用向けに公開される、ビジネスですぐに使えるモデルです。
• silver — クレンジング、重複排除、型の統一を行った中間モデルです。最下層の SQL レイヤーであり、{{ source('bronze', '<table>') }} を介して raw データを読み取ります。
• bronze — SQL レイヤーではありません。dbt/models/bronze/schema.yml のみを格納し、raw テーブルを dbt ソースとして宣言します。dbt/models/bronze/ の下に .sql ファイルを置いてはいけません。

コンテキストからレイヤーが明らかでない場合は、質問してください（チェックポイント 1 を参照）。

ソース（bronze レイヤー）

取り込みパイプラインによってウェアハウスにロードされた raw テーブルは、dbt モデルではありません。silver モデルから {{ source() }} で参照できるように、dbt ソースとして宣言します。

ソースは、BigQuery のデータセットを示す schema を使い、dbt/models/bronze/schema.yml の sources: ブロック内で宣言します。

sources:
  - name: bronze
    schema: "{{ env_var('REVOS_BQ_DATASET') }}"
    tables:
      - name: hubspot_contacts

silver SQL での参照：

-- dbt/models/silver/silver_hubspot_contacts.sql
SELECT * FROM {{ source('bronze', 'hubspot_contacts') }}

{{ source('bronze', 'hubspot_contacts') }} は ${REVOS_BQ_DATASET}.hubspot_contacts に解決されます。これは raw テーブルが格納されているものと同じデータセットなので、間に bronze SQL ビューを挟まずに silver から直接アクセスできます。

宣言パターンの全容については、schema-conventions.md を参照してください。

マテリアライゼーション

dbt_project.yml からグローバルに継承されます。ユーザーから明示的に上書きを求められない限り、{{ config(materialized=...) }} を追加しないでください。

schema.yml の規約

各レイヤーにつき、dbt/models/<layer>/schema.yml に共有ファイルを 1 つ置きます。新しいモデルは追記し、モデルごとの YAML ファイルは作成しないでください。完全な例と、複合主キー／dbt-utils のパターンについては、schema-conventions.md を参照してください。

BigQuery の物理テーブルの解決

マテリアライズされたテーブルの場所：$REVOS_BQ_DATASET.<model_name>

{{ ref() }} と {{ source() }} の使い分け：

コンテキスト	使用するもの
dbt SQL → 他の dbt モデル	{{ ref('<model>') }}
dbt SQL → raw テーブル（bronze から読み取る silver）	{{ source('bronze', '<table>') }}

silver は最下層の SQL レイヤーです。{{ source('bronze', ...) }} は silver でのみ使います。gold は {{ ref() }} を介して silver から読み取ります。dbt/models/bronze/ に SQL ファイルはありません。

raw テーブルは必ず参照前にソースとして宣言してください。完全修飾名をそのまま使わないでください。dbt の依存関係グラフとソースの鮮度追跡を迂回してしまいます。

標準的な dbt コマンド

dbt parse                               # validate syntax (no warehouse)
dbt compile --select <model>            # resolve refs, produce compiled SQL
dbt run --select <model>                # execute against warehouse
dbt test --select <model>               # run tests
dbt build --select <model>              # run + test
dbt build --select path:models/<layer>  # entire layer

---

パート 2：ワークフロー — 新しい dbt 変換を作成する

実行順序

変換ごとに（1つずつ、まとめて処理しないでください）：

1. 対象レイヤーを silver または gold のみから判断します（不明な場合はチェックポイント 1）。bronze の SQL モデルは拒否します（チェックポイント 4 を参照）。
2. モデル名を判断します。
3. そのモデルがすでに存在するか確認します（存在する場合はチェックポイント 2）。
4. ソースデータと変換ロジックを収集します。ブリッジモデルには、ブリッジテンプレート（sql-templates.md）を適用します。
5. モデルが raw データを読み取る場合、各 raw テーブルが dbt/models/bronze/schema.yml の bronze ソースに宣言されていることを確認します。宣言されていない場合は追加します。
6. dbt/models/<silver|gold>/<model_name>.sql を生成します。dbt/models/bronze/ の下に .sql ファイルを生成してはいけません。
7. 主キーを検出します（曖昧な場合はチェックポイント 3）。
8. PK テストと FK テストを含むモデルエントリーを dbt/models/<layer>/schema.yml に追加します。schema-conventions.md を参照してください。
9. dbt run --select <model_name> を実行し、結果を報告します。
10. dbt test --select <model_name> を実行し、結果を報告します。
11. 要約します（「最終回答の形式」を参照）。

1 つのリクエストに複数の変換が含まれる場合は、モデルごとに手順 1～11 を順番に繰り返します。

---

必須のユーザーチェックポイント

チェックポイント 1：レイヤーの選択

レイヤーが明らかでない場合は、確認してください：

Which layer should this transformation live in?

- gold: business-ready, exposed for reporting or downstream consumption
- silver: cleaned/intermediate, reads raw via `{{ source('bronze', ...) }}`

(bronze is not a SQL layer — it only holds `schema.yml` source declarations.)

ユーザーがレイヤーを明示している場合、レイヤーは明らかです。

チェックポイント 2：既存モデルとの競合

dbt/models/<layer>/<model_name>.sql が存在する場合：

A model named <model_name> already exists at dbt/models/<layer>/<model_name>.sql.

Options:
- overwrite: replace with new transformation
- edit:      modify existing (describe the change)
- rename:    use a different name

別のレイヤーで見つかった場合も、その旨を伝えてください。

チェックポイント 3：主キーが曖昧

PK の検出結果が明確でない場合：

I could not unambiguously detect the primary key. Candidates:
- <candidate_1>
- <candidate_2>

Which column(s) should be the primary key?

チェックポイント 4：bronze SQL モデルの作成を拒否

ユーザーが bronze SQL モデルの作成を明示的に依頼した場合：

Bronze is not a SQL layer in this project — it only holds source
declarations in `dbt/models/bronze/schema.yml`. Silver reads raw data
directly via `{{ source('bronze', '<raw_table>') }}`.

Would you like to create this as a silver model instead?

schema.yml 以外のファイルを dbt/models/bronze/ の下に生成してはいけません。

---

主キーの検出

以下の順序で適用し、最初に明確な結果が得られた時点で終了します：

1. ROW_NUMBER() OVER (PARTITION BY <cols>) = 1 → パーティション列が PK です。
2. 少数の列に対する SELECT DISTINCT → 選択されたすべての列が複合 PK です。
3. 最外部レベルの GROUP BY <cols> → グループ化列が PK です。
4. id という名前の単一列 → PK です。
5. モデル名の語幹と一致する <entity>_id という名前の単一列 → PK です。
6. ブリッジの命名規則 <entity_a>_<entity_b> → (<entity_a>_id, <entity_b>_id) が複合 PK です。

どれも明確な答えを導かない場合 → チェックポイント 3。

外部キーの検出

列名が <entity>_id に一致し、<entity> がモデル自身のエンティティと異なり、PK の一部ではなく、設計上 nullable でない場合、その列は FK の候補です。not_null テストのみを追加してください（デフォルトでは relationships テストは追加しません）。

タイムスタンプ列の伝播（Gold モデル）

下流のキューブで SQL ベースの refresh_key を使用できるように、すべての gold モデルは少なくとも 1 つのタイムスタンプ列を必ず伝播してください（create-cubes スキルを参照）。

1. raw テーブルの取り込み時刻列（例：Airbyte が _airbyte_extracted_at を書き込みます）— 存在する場合は伝播します。
2. updated_at / modified_at — CDC に適したストリームです。
3. created_at — 追加のみのファクトテーブルです。

上流ソースにこれらのいずれもない場合は、SQL コメントに記載します：-- no timestamp column available from source

SQL ファイルの生成

以下については sql-templates.md を参照してください：

• 標準的な silver モデルテンプレート（{{ source('bronze', ...) }} を使用して raw データを読み取ります）
• 標準的な gold モデルテンプレート（{{ ref() }} を使用して silver を読み取ります）
• 具体例を含むブリッジモデル（JSON 配列）テンプレート
• ブリッジモデルの命名規則と SQL の内容に関するルール

schema.yml の更新

ソース宣言、複合 PK、dbt-utils のパターンを含む詳細な例については、schema-conventions.md を参照してください。

エッジケース

SQL の詳細不足、上流モデルの不足、未宣言のソース、実行／テスト失敗への対処については、edge-cases.md を参照してください。

---

最終回答の形式

Created dbt transformation: <model_name>

Layer:           <silver | gold>
File:            dbt/models/<layer>/<model_name>.sql
Materialization: <inherited: table | overridden: <type>>
Primary key:     <pk_column>  (or composite: <col_1>, <col_2>)
Foreign keys:    <fk_1>, <fk_2>  (or "none detected")
schema.yml:      dbt/models/<layer>/schema.yml (entry added)

Tests:
- not_null on <pk>: added
- unique on <pk>: added | skipped: dbt-utils unavailable
- not_null on <fk>: added

Validation:
- dbt run:  passed | failed
- dbt test: passed | failed

Physical table after run:
`<resolved_dataset>.<model_name>`