Google Cloud Cheat Sheet

gcloud projects list
gcloud config set project lake-dev
gcloud config set compute/region us-central1
gcloud billing projects describe lake-dev

gcloud iam service-accounts create etl --project=lake-dev --display-name="Lake ETL"
# email: etl@lake-dev.iam.gserviceaccount.com
# Dataflow, Composer, and Pub/Sub run as this account

gcloud projects add-iam-policy-binding lake-dev --member="serviceAccount:etl@lake-dev.iam.gserviceaccount.com" --role="roles/bigquery.jobUser"
gcloud projects add-iam-policy-binding lake-dev --member="serviceAccount:etl@lake-dev.iam.gserviceaccount.com" --role="roles/bigquery.dataEditor"

gcloud projects get-iam-policy lake-dev --flatten="bindings[].members" --filter="bindings.members:etl@lake-dev.iam.gserviceaccount.com" --format="value(bindings.role)"

bq mk --dataset --location=US lake-dev:lake
bq ls lake-dev:

bq mk --table --time_partitioning_field=dt --clustering_fields=order_id lake-dev:lake.orders order_id:STRING,amount:FLOAT64,dt:DATE

CREATE TABLE lake.orders (
  order_id STRING,
  amount FLOAT64,
  dt DATE
)
PARTITION BY dt
CLUSTER BY order_id;

ALTER TABLE lake.orders
SET OPTIONS (require_partition_filter = true);

SELECT dt, SUM(amount) AS revenue
FROM lake.orders
WHERE dt = DATE '2026-03-15'
GROUP BY dt;

SELECT * EXCEPT (rn) FROM (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY order_id ORDER BY updated_at DESC
  ) AS rn
  FROM lake.orders_raw
)
WHERE rn = 1;

MERGE lake.orders t
USING lake.orders_stage s
ON t.order_id = s.order_id
WHEN MATCHED THEN
  UPDATE SET amount = s.amount, dt = s.dt
WHEN NOT MATCHED THEN
  INSERT (order_id, amount, dt)
  VALUES (s.order_id, s.amount, s.dt);

bq load --source_format=PARQUET lake-dev:lake.orders gs://lake-dev-raw/bronze/orders/dt=2026-03-15/*.parquet

LOAD DATA INTO lake.orders
FROM FILES (
  format = 'PARQUET',
  uris = ['gs://lake-dev-raw/bronze/orders/dt=2026-03-15/*.parquet']
);

CREATE OR REPLACE EXTERNAL TABLE lake.orders_ext (
  order_id STRING,
  amount FLOAT64
)
WITH PARTITION COLUMNS (dt DATE)
OPTIONS (
  format = 'PARQUET',
  uris = ['gs://lake-dev-raw/silver/orders/*'],
  hive_partition_uri_prefix = 'gs://lake-dev-raw/silver/orders/'
);

bq query --dry_run --use_legacy_sql=false 'SELECT SUM(amount) FROM lake.orders WHERE dt = "2026-03-15"'

import apache_beam as beam

with beam.Pipeline() as p:
    (p
     | beam.io.ReadFromParquet("gs://lake-dev-raw/bronze/orders/*.parquet")
     | beam.Filter(lambda r: r["amount"] is not None)
     | beam.io.WriteToParquet("gs://lake-dev-raw/silver/orders/out"))

python orders_pipeline.py --runner DataflowRunner --project lake-dev --region us-central1 --temp_location gs://lake-dev-tmp/temp --service_account_email etl@lake-dev.iam.gserviceaccount.com

gcloud dataflow jobs list --region=us-central1 --status=active
gcloud dataflow jobs cancel JOB_ID --region=us-central1

import json
import apache_beam as beam
from apache_beam.transforms.window import FixedWindows

def parse(raw):
    return json.loads(raw.decode("utf-8"))

(p
 | beam.io.ReadFromPubSub(subscription="projects/lake-dev/subscriptions/orders-dataflow")
 | beam.Map(parse))

| beam.WindowInto(FixedWindows(60))

python orders_stream.py --runner DataflowRunner --project lake-dev --region us-central1 --temp_location gs://lake-dev-tmp/temp --streaming --service_account_email etl@lake-dev.iam.gserviceaccount.com

from datetime import datetime, timedelta
from airflow import DAG
from airflow.providers.google.cloud.operators.bigquery import BigQueryInsertJobOperator

with DAG(
    "nightly_orders",
    start_date=datetime(2026, 3, 1),
    schedule="@daily",
    catchup=False,
    default_args={"retries": 2, "retry_delay": timedelta(minutes=5)},
) as dag:
    load = BigQueryInsertJobOperator(
        task_id="load_orders",
        location="US",
        configuration={"query": {
            "query": "SELECT 1",
            "useLegacySql": False,
        }},
    )

extract >> load
# load runs only after extract succeeds

"query": "SELECT * FROM lake.orders_ext WHERE dt = DATE '{{ ds }}'"
# {{ ds }} is the Airflow logical date, not a date baked into the file

gcloud composer environments run lake-dev --location us-central1 dags trigger -- nightly_orders

gcloud pubsub topics create orders
gcloud pubsub subscriptions create orders-pull --topic=orders --ack-deadline=60

gcloud pubsub topics publish orders --message='{"order_id":"1042","amount":19.5}'

gcloud pubsub subscriptions pull orders-pull --auto-ack --limit=10
# ack only after the write succeeds; otherwise the message is redelivered

gcloud pubsub topics create orders-dead
gcloud pubsub subscriptions create orders-dataflow --topic=orders --ack-deadline=60 --dead-letter-topic=orders-dead --max-delivery-attempts=5
# the Pub/Sub service agent needs publisher on orders-dead

CREATE TABLE lake.orders_stream (
  data STRING,
  subscription_name STRING,
  message_id STRING,
  publish_time TIMESTAMP
);

gcloud pubsub subscriptions create orders-bq --topic=orders --bigquery-table=lake-dev:lake.orders_stream --write-metadata --drop-unknown-fields

SELECT
  JSON_VALUE(data, '$.order_id') AS order_id,
  publish_time
FROM lake.orders_stream
WHERE publish_time >= TIMESTAMP('2026-03-15');