Dinesh/ Blog
← All articles
cloud engineering

Exploring different services in GCP

Exploration and documentation of different services offered in GCP

By Dinesh Maluchuru

22 min readChecking device speech…
Lesson preparation & details

Level: intermediate

By the end, you should be able to

  • Reason about GoogleSQL array grain, nulls, joins and partition expiry
  • Separate cloud networking, IAM and billing responsibilities
  • Design object-generation preconditions and a weather-data pipeline

Bring with you

  • SQL joins and grouping
  • HTTP and cloud project concepts

Editorial review: · What review means

In this article · 51 sections

GCP Services for ML Engineering and Data Engineering

These notes follow an exploration in two stages: first a resource roadmap, then the project setup and BigQuery labs I actually worked through. The workspace image is an orientation to that learning environment, not an architecture for the proposed weather dashboard. Keep that distinction in mind: the project idea is a destination, while the later SQL records the smaller exercises used to build toward it.

Documenting in Notion : link for easy updatability. I will copy from notion to blog in regular intervals.

Following is the plan to explore the services and read documentations on the services

Google Cloud Learning Resources Roadmap provided by Claude AI - Planned using the Claude Haiku LLM to give me resources and plan to explore GCP in short period of time.

Rapid Plan to explore and familiarize GCP services and technologies.

Cloud Fundamentals & Data Engineering Resources

Cloud Console & Infrastructure Setup

Cloud Fundamentals & Networking

Data Engineering Foundations

Advanced Data Engineering

Machine Learning Infrastructure

Practical ML Project Resources

Deep Learning & Advanced Implementations

Deep Learning Foundations

Practical Deep Learning

Advanced ML Techniques

Real-world Project Resources

Cloud Cost Optimization

Project Documentation & Performance

Additional Comprehensive Learning Resources

🚀 Pro Learning Tips

  1. Parallel Learning

    • Open multiple browser tabs for comprehensive resource exploration
    • Create a structured learning environment

  2. Active Learning Techniques

    • Take detailed, structured notes for each resource
    • Screenshot key configurations and code snippets
    • Create a personal knowledge repository

  3. Practical Implementation

    • Practice immediate implementation of learned concepts
    • Build small projects to reinforce understanding
    • Experiment with different cloud services and tools

Recommended Learning Approach

  • Read the official contract for one service before designing the exercise.
  • Build a small local fixture or an explicitly authorized disposable lab with known expected results.
  • Record one failure case, its diagnosis and the boundary that remains untested before moving on.

After learning all these services , need to build a product.

  • Asked Claude AI to provide beginner friendly projects to implement in GCP, among the list selected below project.

Weather Data Analysis Dashboard

Complexity: Beginner

Key GCP Services:

  • Cloud Run functions (formerly Cloud Functions)
  • BigQuery
  • Looker Studio (formerly Data Studio)

Project Features:

  • Fetch weather data from public APIs
  • Store historical weather information
  • Create simple visualizations
  • Basic predictive analysis

Implementation of the plan :

The roadmap now becomes a lab notebook. I start by creating a project, installing the SDK, and connecting to a VM; those steps establish where resources live and how I access them. After networking and IAM, the notes switch to managed BigQuery labs. The private archive screenshots record that progression rather than a finished end-to-end dashboard.

Cloud Fundamentals & Data Engineering Resources :

Setting up :

Project setup and google SDK installation

Cloud Console & Infrastructure Setup

Official Setup Guide: Cloud Resource Manager

A project is a resource/IAM/API boundary with a human-readable name, a globally unique project ID and an assigned project number. Organization/folder policies and inherited IAM can constrain it. Billing association and enabled APIs are separate prerequisites, not permissions granted by knowing the project ID. Before a new lab, choose the organization/folder and region intentionally, establish least-privilege roles and verify the billing/cost limits; do not reuse identifiers copied from this historical notebook.

Project Name : GCP Exploration

Project ID : [historical lab project omitted]

Manually created the project

Free Credits: Google Cloud Free Tier

The original 2024 account had promotional credits; those are not a current entitlement. Free Tier has regional, product and monthly limits, not unlimited free compute/storage. Verify current eligibility, supported regions, billing and an explicit cleanup plan before any optional lab. An 8-vCPU VM in Mumbai is not the small eligible Free Tier VM.

SDK Installation: Google Cloud SDK Install

  • Downloaded and installed the google sdk in local machine

For an ARM64 Mac, select the current official ARM64 CLI package and verify the checksum published for that exact version. A mutable rapid-channel URL paired with a historical fixed checksum/size is not a valid integrity recipe.

  • Installed the SDK and added to the path

  • Running the gcloud CLI locally

Cloud Fundamentals & Networking

Compute Engine Tutorial: Quickstart Linux

Enabling the Compute engine API

The Compute Engine API is a per-project prerequisite. Enabling APIs and creating VMs are billable-environment administration, not performed by this review.

Creating a Linux VM in GCP

  • Project and Compute Engine API needs to be created and enabled

  • Historical selection: an E2 high-memory 8-vCPU/64-GB configuration. A vCPU is a virtual scheduling resource; do not use this note as a portable physical-core guarantee or cost recommendation

  • Modified the OS to Ubuntu Linux - 20.04 LTS , region - Mumbai , name : gcpexploration-1

  • Historical UI firewall selections allowed HTTP/HTTPS traffic. For a new lab, choose only required ports and source ranges; a web-server checkbox is not a complete network-security design

  • SSH to the VM via web interface

  • Connecting to the created VM from local

  • Authentication and SSH are deliberately not replayed here. For an authorized lab use the official supported sign-in and OS Login/IAP workflow, least-privilege permissions and no shared keys in source control. Distinguish gcloud CLI credentials from Application Default Credentials used by application libraries; a CLI login is not a universal application-authentication setup.

# Safe local version inspection only; no login, resource creation or remote shell.
gcloud version

Connected to VM via local terminal

Networking Guide: VPC Overview

  • Virtual private cloud is a managed networking service for our services to securely communicate with eachother
  • we can isolate the network or configure with custom definitions.
  • These networks are global in nature so any one VPC can be multi region
  • In VPC we have subnets, these are regional so we can isolate resources within specific region.
  • Subnets further divides VPC in to smaller segments and we can allocate specific IP addresses to a specific region.
  • A subnet allocates regional internal address ranges. External IPs and internet reachability are separately configured on supported resources; creating a subnet does not make every VM public.
  • Routing - Normally VPCs have default routes to connect resources each other in same VPC or to the internet.
  • Routes tell VM instances and the VPC network how to send traffic from an instance to a destination, either inside the network or outside of Google Cloud.
  • We can also create custom routes for advanced network designs.
  • Firewall rules - Built-in firewall rules allow you to control the traffic to and from the resources in the VPC.
  • VPC Peering exchanges supported routes between two networks; it is not transitive and does not merge IAM or firewall policies
  • Forwarding rules - While routes govern traffic leaving an instance, forwarding rules direct traffic to a Google Cloud resource in a VPC network based on IP address, protocol, and port.
  • Alias IP ranges - multiple services running on a single VM instance, you can give each service a different internal IP address by using alias IP ranges , VPC network then routes to specific service.

IAM Documentation: Identity Management Quickstart

  • Mainly used to manage access to resources, allows to control the permissions of users, groups , service accounts.
  • IAM for organizations useful for auditing purposes to manage access , monitor activity and grant right access to right people.
  • Identity in IAM represents a user or system that requires access to GCP resources
    • Accounts - Individuals
    • Service Accounts - Used by applications or Services
    • Groups
    • Federated Identities - External identities (from another identity provider) mapped to GCP
  • Roles define what actions can be performed on specific GCP resources
    • Basic roles like Owner , Editor and Viewer
    • Predefined roles - Roles defined to managed specific tasks or resources like roles for managing Cloud storage or Compute
    • Custom roles - User defined with custom permissions
    • Permissions specify what actions are allowed on resources.
  • Policies are bindings that associate identities with roles, defining access permissions.

Note : Free credits provided are exhausted, now I will be continuing to explore using the https://www.cloudskillsboost.google/

BigQuery Quickstart

  • Starting the Build a Data Warehouse with BigQuery course for this section

  • Course link : google skill

  • Completing this will provide in-depth knowledge on BigQuery

  • Course also contains hands-on labs

  • BigQuery is a managed analytical service using GoogleSQL. Managed infrastructure does not eliminate schema, access, quality or cost operations. Query pricing can be on-demand bytes processed or capacity-based; “low cost” depends on the workload.

  • The Lab I am doing will focus on how to create new reporting tables using SQL JOINS and UNIONs.

  • Scenario: Your marketing team provided you and your data science team all of the product reviews for your ecommerce website. You are partnering with them to create a data warehouse in BigQuery which joins together data from three sources:

    • Website ecommerce data
    • Product inventory stock levels and lead times
    • Product review sentiment analysis
    • What you'll do
    • In this lab, you learn how to perform these tasks:
    • Explore new ecommerce data on sentiment analysis.
    • Join datasets and create new tables.
    • Append historical data with unions and table wildcards.

Lab-only SQL boundary: DDL/INSERT snippets below illustrate the historical exercises, not instructions to run against a live project. Use a newly named disposable dataset, verify region/cost/permissions, and execute one reviewed statement at a time. CREATE fails rather than replacing existing tables; real deletion is replaced by a read-only candidate count.

SELECT * FROM ecommerce.products 
where name like '%Aluminum%'
LIMIT 1000;
 
# pull what sold on 08/01/2017
CREATE TABLE ecommerce.sales_by_sku_20170801 AS
SELECT
  productSKU,
  SUM(IFNULL(productQuantity,0)) AS total_ordered
FROM
  `data-to-insights.ecommerce.all_sessions_raw`
WHERE date = '20170801'
GROUP BY productSKU
ORDER BY total_ordered DESC #462 skus sold
 
 
# join against product inventory to get name
SELECT DISTINCT
  website.productSKU,
  website.total_ordered,
  inventory.name,
  inventory.stockLevel,
  inventory.restockingLeadTime,
  inventory.sentimentScore,
  inventory.sentimentMagnitude,
  SAFE_DIVIDE(website.total_ordered, inventory.stockLevel) AS ratio
FROM
  ecommerce.sales_by_sku_20170801 AS website
  LEFT JOIN `data-to-insights.ecommerce.products` AS inventory
ON website.productSKU = inventory.SKU
WHERE SAFE_DIVIDE(website.total_ordered,inventory.stockLevel) >= .50
ORDER BY total_ordered DESC;
 
 
CREATE TABLE ecommerce.sales_by_sku_20170802
(
productSKU STRING,
total_ordered INT64
);
 
 
INSERT INTO ecommerce.sales_by_sku_20170802
(productSKU, total_ordered)
VALUES('GGOEGHPA002910', 101);
 
SELECT * FROM ecommerce.sales_by_sku_20170801
UNION ALL
SELECT * FROM ecommerce.sales_by_sku_20170802;
 
SELECT * FROM `ecommerce.sales_by_sku_2017*`;
 
SELECT * FROM `ecommerce.sales_by_sku_2017*`
WHERE _TABLE_SUFFIX = '0802';
  • Big Query SQL Syntax : Link
  • columnar data processing engine developed by Google, Dremel is the engine that powers BigQuery. It is based on MPP - Massively parallel processing architecture , tree based query engine.
  • Compute and Storage layers separation makes it easy to scale independently.
  • BigQuery separates managed compute and storage. On-demand analysis pricing depends on billed bytes; capacity pricing depends on slot capacity/commitments. Storage, transfer and other features have their own charges.
  • Completed exercise one for this series, will continue to the second exercise.

Creating Date-Partitioned Tables in BigQuery Lab

The first lab used daily sales tables and wildcard queries to combine dates. This lab changes the storage layout: keep rows in a date-partitioned table so a date filter can restrict the partitions scanned. Read the recorded byte counts below as observations from those particular queries, not as a universal cost promise.

  • In this tut focus is on following topics

    • Query partitioned tables.
    • Create your own partitioned tables.

  • Utilized 5 credits to complete this exercise

  • partitioning in bigquery, stats of executed query, auto expiring partitioning using dataset NOAA_GSOD

  • Completed this module below are few queries

 
## Creating partitioned table to improve efficiency while querying on date
 
 CREATE TABLE ecommerce.partition_by_day
 (
  date_formatted date,
  fullvisitorId STRING
 )
 PARTITION BY date_formatted
 OPTIONS(
   description="a table partitioned by date"
 ) 
 
 
INSERT INTO `ecommerce.partition_by_day`
SELECT DISTINCT
 PARSE_DATE("%Y%m%d", date) AS date_formatted,
 fullvisitorId
 FROM `data-to-insights.ecommerce.all_sessions_raw`;
 
-- Separate alternative below: choose one creation recipe, never drop an existing table to try it.
 
 
#standardSQL
 CREATE TABLE ecommerce.partition_by_day_ctas
 PARTITION BY date_formatted
 OPTIONS(
   description="a table partitioned by date"
 ) AS
 
 SELECT DISTINCT
 PARSE_DATE("%Y%m%d", date) AS date_formatted,
 fullvisitorId
 FROM `data-to-insights.ecommerce.all_sessions_raw`
 
 
 #standardSQL
SELECT *
FROM `data-to-insights.ecommerce.partition_by_day`
WHERE date_formatted = '2016-08-01';
 
 
-- Bytes processed 25.05 KB ,Bytes billed 10 MB , Slot milliseconds 26
 
#standardSQL
SELECT *
FROM `data-to-insights.ecommerce.partition_by_day`
WHERE date_formatted = '2018-07-08';
 
 
-- Duration 0 sec,  Bytes processed 0 B , Bytes billed 0 B
 
 
-- Syntax to create a expiring partitioned query for a partition 
-- standardSQL
 CREATE TABLE ecommerce.days_with_rain
 PARTITION BY date
 OPTIONS(
   partition_expiration_days = 60,
   description="weather stations with precipitation, partitioned by day"
 ) AS
 SELECT
   DATE(CAST(year AS INT64), CAST(mo AS INT64), CAST(da AS INT64)) AS date,
   (SELECT MIN(name) FROM `bigquery-public-data.noaa_gsod.stations` AS stations
    WHERE stations.usaf = weather.stn AND stations.wban = weather.wban) AS station_name, -- deterministic label at the station key; audit duplicates
   prcp
 FROM `bigquery-public-data.noaa_gsod.gsod*` AS weather
 WHERE prcp < 99.9  -- Filter unknown values
   AND prcp > 0      -- Filter stations/days with no precipitation
   AND _TABLE_SUFFIX >= '2018';

Troubleshooting and Solving Data Join Pitfalls

A cheaper query is not necessarily a correct one. Before joining sales to product details, we need to know whether a product identifier maps to one name or several. The next queries inspect that relationship, then show how a cross join deliberately multiplies rows for a promotion example.

  • In this lab following exercises are present.
  • Use BigQuery to explore and troubleshoot duplicate rows in a dataset.
  • Create joins between data tables.
  • Choose between different join types.

Exercise insights

  • You can add any public datasets available by clicking the +Add button at the top and selecting the Star a Project Name option. Add data-to-insights to import the public datasets and tables into your GCP workspace.
  • ARRAY_AGG Big query supports natively array data types.
  • Query Optimizations best practices for Big query : Best Practices
  • including some basic queries for syntax remembrance in big query.
  • Progress completed the lab
-- Complete lab
with a as (
SELECT distinct productSKU , v2ProductName FROM 
`data-to-insights.ecommerce.all_sessions_raw`),
b as (
  select productSKU 
  , row_number() over(partition by productSKU order by v2ProductName) as rn_prod
  ,v2ProductName
  ,  row_number() over(partition by v2ProductName order by productSKU) as rn_prodname
from a
)
select * from b where rn_prodname > 1
order by rn_prodname desc;
 
 
-- other way
SELECT
  productSKU,
  COUNT(DISTINCT v2ProductName) AS product_count,
  ARRAY_AGG(DISTINCT v2ProductName LIMIT 5) AS product_name
FROM `data-to-insights.ecommerce.all_sessions_raw`
  WHERE v2ProductName IS NOT NULL
  GROUP BY productSKU
  HAVING product_count > 1
  ORDER BY product_count DESC
 
#standardSQL
CREATE TABLE ecommerce.site_wide_promotion AS
SELECT .05 AS discount;
 
INSERT INTO ecommerce.site_wide_promotion (discount)
VALUES (.04),
       (.03);
SELECT discount FROM ecommerce.site_wide_promotion
 
#standardSQL
SELECT DISTINCT
productSKU,
v2ProductCategory,
discount
FROM `data-to-insights.ecommerce.all_sessions_raw` AS website
CROSS JOIN ecommerce.site_wide_promotion
WHERE v2ProductCategory LIKE '%Clearance%'
AND productSKU = 'GGOEGOLC013299'

Working with JSON, Arrays, and Structs in BigQuery

The join exercises make row granularity important. Nested data changes where that granularity lives: a row can contain several participants, and each participant can contain several lap times. The race schema below makes that relationship explicit; the later queries flatten one level at a time to answer questions about runners and laps.

  • Load and query semi-structured data including unnesting.
  • Troubleshoot queries on semi-structured data.

Exercise insights

  • Traditionally we split in to star schema with de-norm to structure the data model for easy updates and join across the tables to get required results.
  • In Big query if you are able to store the data at different granularity then denorm is the way to go, using Arrays
  • data in a array needs to be of same data type (all strings or all numbers),finding the number of elements with ARRAY_LENGTH(<array>) , deduplicating elements with ARRAY_AGG(DISTINCT <field>), ordering elements with ARRAY_AGG(<field> ORDER BY <field>) , limiting ARRAY_AGG(<field> LIMIT 5)
  • A STRUCT groups named fields into one value. An ARRAY represents repeated values; ARRAY<STRUCT<...>> represents repeated records. They solve different parts of nested modeling. Documentation : Structs
  • Structs props : One or many fields in it , The same or different data types for each field , It's own alias.
  • Structs are containers that can have multiple field names and data types nested inside., Arrays can be one of the field types inside of a Struct.
  • Good documentation on working with structs and arrays : link , additional reading
 
[
    {
        "name": "race",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "participants",
        "type": "RECORD",
        "mode": "REPEATED",
        "fields": [
            {
                "name": "name",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "splits",
                "type": "FLOAT",
                "mode": "REPEATED"
            }
        ]
    }
]

Some of the queries to remember the syntax, UNNEST, ARRAY_AGG , ARRAY_LENGTH

These are separate experiments, not one script to run from top to bottom. Some intentionally demonstrate errors, such as mixing strings and numbers in an array or accessing a repeated field without flattening it. Compare each failure with the following query that changes the data shape.

#standardSQL
SELECT
['raspberry', 'blackberry', 'strawberry', 'cherry'] AS fruit_array;
 
#standardSQL
SELECT
['raspberry', 'blackberry', 'strawberry', 'cherry', 1234567] AS fruit_array;
--Array elements of types {INT64, STRING} do not have a common supertype at [3:1]
 
#standardSQL
SELECT person, fruit_array, total_cost FROM `data-to-insights.advanced.fruit_store`;
 
SELECT
  fullVisitorId,
  date,
  ARRAY_AGG(v2ProductName IGNORE NULLS) AS products_viewed,
  ARRAY_AGG(pageTitle IGNORE NULLS) AS pages_viewed
  FROM `data-to-insights.ecommerce.all_sessions`
WHERE visitId = 1501570398
GROUP BY fullVisitorId, date
ORDER BY date
 
 
SELECT
  fullVisitorId,
  date,
  ARRAY_AGG(DISTINCT v2ProductName IGNORE NULLS) AS products_viewed,
  ARRAY_LENGTH(ARRAY_AGG(DISTINCT v2ProductName IGNORE NULLS)) AS distinct_products_viewed,
  ARRAY_AGG(DISTINCT pageTitle IGNORE NULLS) AS pages_viewed,
  ARRAY_LENGTH(ARRAY_AGG(DISTINCT pageTitle IGNORE NULLS)) AS distinct_pages_viewed
  FROM `data-to-insights.ecommerce.all_sessions`
WHERE visitId = 1501570398
GROUP BY fullVisitorId, date
ORDER BY date
 
 
SELECT
  visitId,
  hits.page.pageTitle
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
WHERE visitId = 1501570398
-- you can not access it directly from array, 
 
SELECT DISTINCT
  visitId,
  h.page.pageTitle
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`,
UNNEST(hits) AS h
WHERE visitId = 1501570398
LIMIT 10
-- A correlated UNNEST follows the table whose array it references; standalone UNNEST also exists (think of it conceptually like a pre-joined table)
 
 
SELECT
  visitId,
  totals.*,
  device.*
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
WHERE visitId = 1501570398
LIMIT 10;
-- the .* syntax tells BigQuery to return all fields for that STRUCT (much like it would if totals.* was a separate table we joined against).
 
#standardSQL
SELECT STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits) AS runner
 
#standardSQL
SELECT r.race, p.name
FROM racing.race_results AS r
CROSS JOIN UNNEST(r.participants) AS p;
 
 
#standardSQL
SELECT r.race, p.name
FROM racing.race_results AS r, UNNEST(r.participants) AS p;
 
#standardSQL
SELECT COUNT(p.name) AS racer_count
FROM racing.race_results AS r, UNNEST(r.participants) AS p
 
 
#he total race time for racers whose names begin with R. Order the results with the fastest total time first. Use the UNNEST() operator and start with the partially written query below.
SELECT
  r.race, p.name,
  SUM(split_times) as total_race_time
FROM racing.race_results AS r
, UNNEST(r.participants) AS p
, UNNEST(p.splits) AS split_times
WHERE p.name LIKE 'R%'
GROUP BY r.race, p.name
ORDER BY total_race_time ASC;
 
 
#see that the fastest lap time recorded for the 800 M race was 23.2 seconds, but you did not see which runner ran that particular lap. Create a query that returns that result.
SELECT
  p.name,
  split_time
FROM racing.race_results AS r
, UNNEST(r.participants) AS p
, UNNEST(p.splits) AS split_time
WHERE split_time = 23.2;

Lab link - gcp big query

Build a Data Warehouse with BigQuery (Challenge)

The challenge combines the earlier building blocks: create tables from public data, choose a partition and retention policy, then filter or clean the copied rows. The task excerpts use several dataset and table names; treat them as lab-specific instructions rather than a single reusable deployment script.

Challenge to solve few concepts

Prob 1

  • Create a new dataset covid and create a table oxford_policy_tracker in that dataset partitioned by date, with a partition expiry of 1445 days (not 1445 days from insertion for every row). The table should initially use the schema defined for the oxford_policy_tracker table in the COVID 19 Government Response public dataset .
  • You must also populate the table with the data from the source table for all countries and exclude the United Kingdom (GBR), Brazil (BRA), Canada (CAN) and the United States (USA) as instructed above.

Solving process

  • Got the public dataset from the link directly - link
  • Initially to create table we will need either the complete create table schema or we can insert from a source directly before inserting and define partition by and expiry as below.
 CREATE TABLE covid.oxford_policy_tracker
 PARTITION BY date
 OPTIONS(
   partition_expiration_days = 1445,
   description="Covid oxford_policy_tracker"
 ) AS
SELECT *  FROM `bigquery-public-data.covid19_govt_response.oxford_policy_tracker` 
where alpha_3_code not in ('GBR','CAN','BRA','USA');

Prob 2

  • Create a new table 'country_area_data' within the dataset named as 'covid_data'. The table should initially use the schema defined for the country_names_area table data from the Census Bureau International public dataset.
  • Add the country area data to the 'country_area_data' table with country_names_area table data from the Census Bureau International public dataset.

Solving process

  • This looks like a name change for the table name and inserting the raw data while creating the table at the same time.
CREATE TABLE covid_data.country_area_data
AS
SELECT * FROM `bigquery-public-data.census_bureau_international.country_names_area`;

Prob3

  • Create a new table 'mobility_data' within the dataset named as 'covid_data'. The table should initially use the schema defined for the mobility_report table data from the Google COVID 19 Mobility public dataset.
  • Add the mobility record data to the 'mobility_data' table with data from the Google COVID 19 Mobility public dataset.

Solving process

  • Same as above task
CREATE TABLE covid_data.mobility_data
AS
select * from `bigquery-public-data.covid19_google_mobility.mobility_report`;

Prob 4

  • Delete data from the oxford_policy_tracker_by_countries table in the covid_data dataset where the population value is null.
  • Now delete data from the oxford_policy_tracker_by_countries table in the covid_data dataset where the country_area value is null.

Solving process

  • Created a copy table first to do the delete rows.
  • DELETE FROM syntax similar to other data bases.
 
-- creating a copy backup as I am changing in place for new values.
 
CREATE TABLE covid_data.oxford_policy_tracker_by_countries_copy
AS 
select * from covid_data.oxford_policy_tracker_by_countries;
 
-- First inspect candidates without modifying the source:
SELECT COUNT(*) AS rows_to_remove
FROM covid_data.oxford_policy_tracker_by_countries
WHERE population IS NULL OR country_area IS NULL;
 
-- After scope/count/recovery review, the DML form would be:
-- DELETE FROM covid_data.oxford_policy_tracker_by_countries
-- WHERE population IS NULL OR country_area IS NULL;
-- It remains commented out: no DELETE is run in this lesson review.

Completed the course, earned the badge for completing this module : Certificate

Cloud Storage Guide: Storage Quickstart

Next in-depth analysis is on storage layer

The historical lab record ends with the BigQuery challenge. The following conceptual extension completes the storage objective; it does not claim a new cloud run.

A bucket is a location, policy and naming boundary; an object is data plus metadata identified by its name and generation. Objects are not ordinary mutable filesystem files. In a flat-namespace bucket a slash in an object name creates a useful prefix, not a POSIX directory. Hierarchical-namespace buckets have additional folder semantics, so state which model the design uses.

For the proposed weather pipeline, land immutable raw responses under names such as weather/provider/date/hour/batch-id.json, preserve event time and retrieval time separately, and publish a manifest only after validation. Choose location with the BigQuery dataset and processing region in mind; do not assume all cross-region movement is free. Use uniform bucket-level access for an IAM-based design, least-privilege service identities and public-access prevention where appropriate. A producer should not need broad project Owner access.

Retry contract: use generation-match preconditions so an upload intended to create a new object does not silently overwrite an existing generation. Reusing an idempotency key should return the same accepted batch, not create a second sale/weather observation. Retention policies, object versioning, soft delete and lifecycle deletion are distinct features with storage costs; verify recovery behavior and avoid locking a retention policy in a learning exercise.

Solved storage question: two workers publish the same logical batch concurrently. Unconditional overwrites make the winner timing-dependent. A create-only generation precondition lets one create succeed and makes the other handle the precondition failure by checking the existing batch identity/hash. This is an object-write design contract, not a claim that BigQuery ingestion has automatically become exactly-once.

Also documenting in Notion : link.

Worked BigQuery and project-design checks

The following is read-only GoogleSQL over inline values. It needs no tables, storage or credentials to reason about; executing even a query on BigQuery would still be a separate authorized service operation.

WITH orders AS (
  SELECT 'O1' AS id, NUMERIC '25.00' AS total,
    [STRUCT('A' AS sku, 2 AS qty), STRUCT('B' AS sku, 1 AS qty)] AS items,
    ['P1', 'P2', 'P3'] AS promotions
)
SELECT id, ARRAY_LENGTH(items) AS item_count,
  ARRAY_LENGTH(promotions) AS promotion_count,
  (SELECT SUM(i.qty) FROM UNNEST(items) AS i) AS units,
  total
FROM orders;
-- Expected: O1, 2, 3, 3, 25.00. No parent-row multiplication.

Join answer: independently crossing items and promotions creates six rows and repeats 25.00 six times. A correlated scalar aggregate keeps the parent grain. LEFT JOIN UNNEST retains an empty-array parent; CROSS JOIN drops it. WITH OFFSET plus ORDER BY is needed to reconstruct original array order. SUM(DISTINCT total) is not a fix: two different orders can have the same amount.

Partition answer: partition expiration is measured from the partition’s UTC boundary. Loading old NOAA/COVID dates into a 60/1445-day retention table today can immediately expire old partitions. Omit expiration in a historical-analysis scratch table unless that deletion is intended. A constant predicate on the partition column enables pruning; LIMIT does not necessarily reduce scanned bytes. Use dry-run estimates and maximum-bytes-billed before a real on-demand query.

SQL notebook repair key: visitor IDs are strings, not arithmetic quantities; ARRAY_AGG output must not contain null elements, hence IGNORE NULLS; independently run lab statements need terminators or separate cells. A left join followed by a WHERE condition on nullable inventory fields filters unmatched rows—move the condition into ON if unmatched sales must be retained. DISTINCT must not hide duplicate inventory keys. NOAA station identity includes the station identifiers; an arbitrary ANY_VALUE name does not validate a one-to-one relationship.

Challenge answer: CTAS copies selected schema/data but not every source policy, partition, description or permission. Each task uses its declared dataset; covid and covid_data are not interchangeable, and the enriched oxford_policy_tracker_by_countries table must already exist before task 4. NOT IN excludes null country codes too; decide explicitly whether missing codes belong in rejects. Inspect the deletion count first. A CTAS copy can help in a small lab, but production recovery also needs verified access, point-in-time/time-travel or snapshot policy and restoration tests.

Weather design answer: a scheduled function fetches with provider timeouts/rate limits, Cloud Storage retains raw immutable batches, a validated BigQuery table stores one row per provider/location/observation time, and Looker Studio queries a curated view. Keep duplicate/revision handling, missing observations and unit/timezone conversion explicit. Predictive modeling requires a time-based validation split and a naïve baseline before claiming improvement. The roadmap’s Dataflow/Beam/Vertex links are optional further study, not implemented ML objectives or deployment evidence.

Corrected contracts and failure analysis

UNNEST expands an array into rows and does not preserve element order without WITH OFFSET and an explicit order. A correlated cross join drops the parent when its array is empty; a left join retains it with null element fields. Joining two repeated arrays can multiply rows and overstate revenue. Establish the grain before aggregating.

Date partitioning helps only when predicates allow pruning. Filter the actual partition column with suitable constant bounds, inspect estimated bytes/dry-run output and avoid assuming LIMIT caps scanned bytes. Use an appropriate numeric/decimal representation for money, and distinguish missing values from zero. A public sample dataset query is not a benchmark of your production schema or permissions.

Projects organize resources and billing; service identities and IAM grants determine access. Use least privilege and explicit regions. Credits, badges and tutorial timings in the historical record are not current guarantees, and an alerts-only budget does not cap usage. Current Google documentation also describes spend-cap budgets with their own availability/scope; do not confuse those with ordinary alert notifications.

Boundary exercise with solution

An order has two line items and three promotion records. A query unnests both independently. How many rows, and why can SUM(order_total) be wrong?

Solution and reasoning

Six rows, because 2×3 combinations are produced. The parent total repeats six times. Aggregate each child relation separately to the order grain or join by a real relationship; DISTINCT is not a principled repair because different orders may share the same total.

Source-backed review notes

  • Work with arrays  |  BigQuery  |  Google Cloud Documentation — accessed 2026-10-07. Exact supporting passage: “To convert an ARRAY into a set of rows, also known as "flattening," use the UNNEST operator. UNNEST takes an ARRAY and returns a table with a single row for each element in the ARRAY.”
  • Query partitioned tables  |  BigQuery  |  Google Cloud Documentation — accessed 2026-10-07. Exact supporting passage: “Partition pruning is the mechanism BigQuery uses to eliminate unnecessary partitions from the input scan. The pruned partitions are not included when calculating the bytes scanned by the query. In general, partition pruning helps reduce query cost.”
  • Google Cloud budgets — accessed 2026-10-07. Alerts-only budgets notify; they do not automatically cap usage. Check the separate spend-cap feature before making stronger claims.

Version-specific reference contracts

Documentation snapshots accessed 7 October 2026; mutable service/browser documentation is timestamped evidence, not an immutable product-version pin. Exact supporting passages and hashes accompany the review ledger.

Review and execution boundary

GoogleSQL BigQuery arrays and partition pruning; GCP resource setup and historical challenge completion are not reproduced.

Reviewed on 7 October 2026 against the official source snapshots linked below. Whole-current and original source review supports the GoogleSQL, IAM/network and storage teaching. Local reference-model fixtures are not BigQuery engine execution. Historical DDL/DML is only for explicitly disposable datasets; no authentication, resource writes or credential operations were run. Historical setup commands and optional exercises were not executed. No cloud resources, third-party packages or external side effects were created. Old screenshots and unavailable private assets remain in the private recovery archive, not prerequisites for this lesson.

Pause / Recall / Apply

Can you explain it without the page?

Close the example. Reconstruct the core idea, then change one assumption. Mark complete when you’re ready; you can always undo it.

Stored in this browser only. No account, no sync. Clearing browser data removes your record.