Build a Data Warehouse with BigQuery

GSP340 — Build a Data Warehouse with BigQuery: Challenge Lab

Estimated time: 1 hour 15 minutes

# 🚀 Implement Cloud Security Fundamentals on Google Cloud > ⚠️ **Disclaimer:** This guide is provided for educational and learning purposes only. It is intended to help you understand Google Cloud security concepts, IAM configuration, private GKE environments, service accounts, permissions, and secure deployment workflows while preparing for Google Cloud certifications and hands-on labs. Always follow the official Google Cloud documentation, Qwiklabs/Google Cloud Skills Boost instructions, and

clear

# ==============================================================================
# Color Variables & Orbit of Ops Branding
# ==============================================================================
RED='\e[1;31m'
GREEN='\e[1;32m'
YELLOW='\e[1;33m'
BLUE='\e[1;34m'
MAGENTA='\e[1;35m'
CYAN='\e[1;36m'
WHITE='\e[1;37m'
BOLD='\e[1m'
RESET='\e[0m'

echo -e "${CYAN}${BOLD}"
cat << "EOF"
  ____       _     _ _            __    ___            
 / __ \     | |   (_) |          / _|  / _ \           
| |  | |_ __| |__  _| |_   ___  | |_  | | | |_ __  ___ 
| |  | | '__| '_ \| | __| / _ \ |  _| | | | | '_ \/ __|
| |__| | |  | |_) | | |_ | (_) || |   | |_| | |_) \__ \
 \____/|_|  |_.__/|_|\__| \___/ |_|    \___/| .__/|___/
                                            | |        
                                            |_|        
EOF
echo -e "${RESET}"
echo -e "${MAGENTA}${BOLD} 🚀 Starting Orbit of Ops Execution (GSP340: Part 1)... ${RESET}"
echo -e "${BLUE}--------------------------------------------------------------------------------${RESET}\n"

# ==============================================================================
# PRE-FLIGHT CHECKS & VARIABLES (DYNAMIC AUTO-FETCH)
# ==============================================================================
echo -e "${BOLD}${YELLOW}[Orbit of Ops] Auto-fetching Project, Zone, and Region...${RESET}"

export PROJECT_ID=$(gcloud config get-value project 2>/dev/null)
if [[ -z "$PROJECT_ID" ]]; then
    export PROJECT_ID=$DEVSHELL_PROJECT_ID
fi

export PROJECT_NUMBER=$(gcloud projects describe $PROJECT_ID --format='value(projectNumber)' 2>/dev/null)

export ZONE=$(gcloud compute project-info describe \
    --format="value(commonInstanceMetadata.items[google-compute-default-zone])" 2>/dev/null | tail -n 1)

if [[ -z "$ZONE" ]]; then
    echo -e "${BOLD}${RED}⚠️ Could not auto-detect the default zone via gcloud metadata.${RESET}"
    read -p "$(echo -e ${BOLD}${CYAN}Please enter the lab Zone \(e.g., us-east1-c\): ${RESET})" ZONE
    export ZONE
fi

export REGION=${ZONE%-*}

gcloud config set compute/zone $ZONE 2>/dev/null
gcloud config set compute/region $REGION 2>/dev/null

echo -e "✅ Project ID: ${GREEN}$PROJECT_ID${RESET}"
echo -e "✅ Zone:       ${GREEN}$ZONE${RESET}"
echo -e "✅ Region:     ${GREEN}$REGION${RESET}\n"
echo -e "${BLUE}--------------------------------------------------------------------------------${RESET}\n"

echo -e "${BOLD}${CYAN}[Orbit of Ops] Task 1: Creating 'covid' schema & oxford_policy_tracker table...${RESET}"
bq query --use_legacy_sql=false <<EOF
CREATE SCHEMA IF NOT EXISTS covid;

CREATE OR REPLACE TABLE covid.oxford_policy_tracker
PARTITION BY date
OPTIONS(
    partition_expiration_days=2175
) AS
SELECT *
FROM \`bigquery-public-data.covid19_govt_response.oxford_policy_tracker\`
WHERE alpha_3_code NOT IN ('GBR', 'BRA', 'CAN', 'USA');
EOF

echo -e "\n${BOLD}${CYAN}[Orbit of Ops] Task 2: Adding columns to global_mobility_tracker_data table...${RESET}"
bq query --use_legacy_sql=false <<EOF
ALTER TABLE \`covid_data.global_mobility_tracker_data\`
ADD COLUMN IF NOT EXISTS population INTEGER,
ADD COLUMN IF NOT EXISTS country_area FLOAT64,
ADD COLUMN IF NOT EXISTS mobility STRUCT<
  avg_retail FLOAT64,
  avg_grocery FLOAT64,
  avg_parks FLOAT64,
  avg_transit FLOAT64,
  avg_workplace FLOAT64,
  avg_residential FLOAT64
>;
EOF

echo -e "\n${GREEN}${BOLD}✅ Part 1 complete! Please proceed to Part 2.${RESET}"

RED='\e[1;31m'
GREEN='\e[1;32m'
CYAN='\e[1;36m'
MAGENTA='\e[1;35m'
BOLD='\e[1m'
RESET='\e[0m'

echo -e "\n${MAGENTA}${BOLD} 🚀 Starting Orbit of Ops Execution (GSP340: Part 2)... ${RESET}\n"

echo -e "${BOLD}${CYAN}[Orbit of Ops] Task 3: Populating country population data...${RESET}"
bq query --use_legacy_sql=false <<EOF
UPDATE
  \`covid_data.consolidate_covid_tracker_data\` t0
SET
  t0.population = t2.pop_data_2019
FROM
  (SELECT DISTINCT country_territory_code, pop_data_2019 FROM \`bigquery-public-data.covid19_ecdc.covid_19_geographic_distribution_worldwide\`) AS t2
WHERE
  t0.alpha_3_code = t2.country_territory_code;
EOF

echo -e "\n${BOLD}${CYAN}[Orbit of Ops] Task 4: Populating country area data...${RESET}"
bq query --use_legacy_sql=false <<EOF
UPDATE
  \`covid_data.consolidate_covid_tracker_data\` t0
SET
  t0.country_area = t2.country_area
FROM
  \`bigquery-public-data.census_bureau_international.country_names_area\` t2
WHERE
  t0.country_name = t2.country_name;
EOF

# ==============================================================================
# COMPLETION
# ==============================================================================
echo -e "\n${MAGENTA}${BOLD}╔════════════════════════════════════════════════════════════╗${RESET}"
echo -e "${MAGENTA}${BOLD}║            🎉 AUTOMATION COMPLETED SUCCESSFULLY 🎉           ║${RESET}"
echo -e "${MAGENTA}${BOLD}╚════════════════════════════════════════════════════════════╝${RESET}"
echo -e "${GREEN}${BOLD}You can now safely click ALL 'Check my progress' buttons in your lab manual.${RESET}"
echo -e "${CYAN}${BOLD}Subscribe to Orbit of Ops: https://www.youtube.com/@orbitofops/videos${RESET}\n"