GCP ADP 試験対策｜SQL 必修パターン・Looker Studio・Dataform

ADP 試験で問われる SQL スキルと Looker Studio / Dataform の実装パターンを深掘りします。BigQuery 方言の SQL、Window Function、UNNEST、Looker と Looker Studio の使い分け、Dataform / Composer の選択基準まで網羅します。

BigQuery SQL 必修パターン

1. 配列・構造体 (STRUCT / ARRAY)

SELECT
  user_id,
  ARRAY_AGG(STRUCT(event_name, event_timestamp)) AS events
FROM analytics.events
GROUP BY user_id;

-- 配列展開
SELECT user_id, event.event_name
FROM analytics.user_events,
UNNEST(events) AS event;

2. Window Function

SELECT
  user_id,
  event_timestamp,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_timestamp) AS event_seq,
  LAG(event_name) OVER (PARTITION BY user_id ORDER BY event_timestamp) AS prev_event
FROM analytics.events;

3. CTE と再帰

WITH daily_sessions AS (
  SELECT DATE(event_timestamp) AS d, COUNT(DISTINCT user_id) AS dau
  FROM analytics.events
  GROUP BY 1
)
SELECT d, dau, AVG(dau) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS dau_7d
FROM daily_sessions;

4. ピボット / アンピボット

-- PIVOT
SELECT * FROM (
  SELECT user_id, event_name FROM analytics.events
) PIVOT (COUNT(*) FOR event_name IN ('login', 'purchase', 'logout'));

Looker vs Looker Studio

Dataform vs Cloud Composer

Dataform 基本構成例

-- definitions/raw_events.sqlx
config { type: "table", schema: "raw" }
SELECT * FROM `{ref('source_events')}`

-- definitions/daily_metrics.sqlx
config { type: "view", schema: "analytics" }
SELECT DATE(event_timestamp) AS d, COUNT(*) AS events
FROM `{ref('raw_events')}`
GROUP BY 1

典型的なデータパイプライン

1. 取り込み: Pub/Sub → Dataflow → BigQuery (ストリーミング) または Cloud Storage → Batch Load
2. 変換: Dataform で staging → intermediate → mart 層を定義
3. カタログ・品質: Dataplex でデータ系譜 + 品質チェック
4. 可視化: Looker Studio (カジュアル) または Looker (全社 BI)
5. オーケストレーション: Cloud Composer で外部システム連携、Workflows で軽量

頻出ひっかけ問題

• BigQuery vs Spanner: OLAP (分析) → BigQuery、OLTP (トランザクション) → Spanner
• Looker vs Looker Studio: 全社統合 BI → Looker、無料/プロトタイプ → Looker Studio
• Dataform Assertion: NULL_CHECK / UNIQUE / ROW_CONDITIONS で品質担保
• Cloud Composer: Airflow のバージョン互換に注意、Composer 2 が現行推奨

※ Google Cloud、BigQuery、Looker は Google LLC の商標です。最新情報は Dataform 公式 / Looker Studio をご確認ください。