DevOps Peach

GCP Basics · Data on GCP – BigQuery, Pub/Sub, Dataflow and a First Pipeline · Lesson 5 of 5

A Simple Data Pipeline: Cloud Storage → BigQuery → Dashboard

Land raw files in Cloud Storage, load or query them in BigQuery, transform with scheduled SQL or Dataform, orchestrate with Workflows or Cloud Composer, and publish a Looker Studio dashboard.

Practice interactively →

Most first analytics projects on Google Cloud follow the same ELT pattern: Extract to Cloud Storage, Load into BigQuery, Transform with SQL there.

flowchart LR
  SRC["Cloud SQL orders<br/>and CSV exports"] -- "Datastream / exports" --> RAW["Landing bucket gs://dl-raw"]
  RAW -- "load job or external table" --> STG["BigQuery dataset staging"]
  STG -- "scheduled SQL / Dataform" --> MART["Dataset mart<br/>daily_revenue"]
  MART --> LS["Looker Studio dashboard"]
  ORCH["Workflows or Cloud Composer"] --> RAW
  %% step SRC: Operational data lives in Cloud SQL and in files from partners.
  %% step RAW: Raw data lands unchanged in a Cloud Storage bucket with lifecycle rules; Datastream can also replicate database changes straight into BigQuery.
  %% step STG: Load jobs (free) or external tables bring the raw data into a staging dataset.
  %% step MART: Scheduled queries or Dataform turn staging tables into clean, documented mart tables.
  %% step LS: Looker Studio reads the mart tables for a dashboard business users can share.
  %% step ORCH: An orchestrator runs the steps in order, retries failures and alerts on errors.

Building blocks

Step Options
Ingest files gcloud storage cp, Storage Transfer Service, partner uploads with signed URLs
Ingest databases Datastream (change data capture from MySQL, PostgreSQL, Oracle into BigQuery), exports, federated queries to Cloud SQL
Ingest events Pub/Sub → BigQuery subscription or Dataflow
Load / read bq load, external tables / BigLake over Cloud Storage
Transform Scheduled queries, Dataform (SQL workflows with tests and dependencies in Git)
Orchestrate Workflows (simple, serverless) or Cloud Composer (managed Apache Airflow for complex DAGs)
Visualise Looker Studio (free dashboards), Looker (governed semantic model), Connected Sheets
Govern Dataplex Universal Catalog for discovery, lineage and data quality

Layered datasets

  1. raw / landing – data as received, never edited.
  2. staging – typed, de-duplicated, one table per source.
  3. mart – business tables (daily_revenue, customer_ltv) used by dashboards.

Grant analysts dataViewer on mart only, and give pipelines their own service account with dataEditor on staging and mart.

A transform as a scheduled query

CREATE OR REPLACE TABLE mart.daily_revenue
PARTITION BY day AS
SELECT DATE(created_at) AS day, country, SUM(total) AS revenue, COUNT(*) AS orders
FROM staging.orders
GROUP BY day, country;

bq query --use_legacy_sql=false --destination_table=mart.daily_revenue --replace   'SELECT DATE(created_at) AS day, country, SUM(total) AS revenue FROM staging.orders GROUP BY day, country'
bq mk --transfer_config --data_source=scheduled_query --target_dataset=mart   --display_name="Daily revenue" --schedule="every 24 hours" --params='{"query":"…"}'

Quality and cost checks

Command Meaning
bq mk --external_table_definition=… staging.partner_csv External table over files
bq ls --transfer_config --transfer_location=asia-southeast1 Scheduled queries
gcloud composer environments create … Managed Airflow environment

Traps: transforming data in place in the raw layer loses the original; dashboards on huge unpartitioned tables run up query costs every time someone opens them; Cloud Composer environments cost money even when idle, so prefer Workflows or scheduled queries for simple pipelines.

Practice interactively →