GCP ADP Exam Prep: Essential SQL Patterns, Looker Studio & Dataform Complete Guide

A deep dive into the SQL skills and Looker Studio / Dataform implementation patterns tested on the ADP exam. Covers BigQuery-dialect SQL, window functions, UNNEST, choosing between Looker and Looker Studio, and Dataform vs. Composer selection criteria.

Essential BigQuery SQL Patterns

1. Arrays and Structs (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 Functions

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. CTEs and Recursion

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 / Unpivot

-- 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 Basic Configuration Example

-- 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

Typical Data Pipeline

1. Ingestion: Pub/Sub → Dataflow → BigQuery (streaming) or Cloud Storage → Batch Load
2. Transformation: Define staging → intermediate → mart layers in Dataform
3. Catalog & quality: Data lineage and quality checks in Dataplex
4. Visualization: Looker Studio (casual) or Looker (enterprise BI)
5. Orchestration: Cloud Composer for external system integration, Workflows for lightweight cases

Common Trick Questions

• BigQuery vs Spanner: OLAP (analytics) → BigQuery; OLTP (transactions) → Spanner
• Looker vs Looker Studio: Enterprise-wide BI → Looker; free / prototyping → Looker Studio
• Dataform assertions: NULL_CHECK / UNIQUE / ROW_CONDITIONS ensure quality
• Cloud Composer: Watch Airflow version compatibility; Composer 2 is the current recommendation

* Google Cloud, BigQuery, and Looker are trademarks of Google LLC. For the latest information, see Dataform official docs / Looker Studio.