View all study notes here

---

NoSQL & Analytical Databases

For when schemas are dynamic or undefined, or when data ingestion throughput requirements exceed what relational systems can handle.

Firestore

Serverless, document oriented NoSQL database designed for mobile, web and IoT applications. Successor to Cloud Datastore.

• Data Model
  • Collections of JSON-like documents, each identified by a unique document ID. Documents contain fields; fields can contain nested sub-collections.
• Scales to zero automatically when idle; handles billions of documents natively.
• Real time data synchronization via WebSocket listeners.
  • Clients are pushed changes automatically without polling.
• Built-in offline caching.
  • Mobile clients continue to function without a network connection and sync when reconnected.
• Native ACID transactions across multiple documents and collections.
• Native Mode
  • Optimized for mobile/web apps requiring real-time updates and offline sync.
• Datastore Mode
  • For high throughput backend server architectures without real time sync requirements. Successor to the original Cloud Datastore product.
• Indexing
  • Firestore automatically creates single field indexes.
  • Composite indexes (queries filtering on multiple fields) must be manually defined, otherwise queries will fail with an error, not just return slower.
• !! Firestore is not designed for heavy analytical workloads or complex aggregations.
  • Use BigQuery for analytics.
  • Use Firestore for operational app data.

Cloud Bigtable

A fully managed, wide-column NoSQL database designed for petabyte-scale data volumes requiring single digit millisecond latency (<10ms) and extremely high write throughput.

• Target Workloads
  • IoT sensor telemetry, time-series data, financial market tickers, user clickstream events, large-scale ad-tech event streams, ML feature stores.
• Architecture
  • Decouples processing (Bigtable nodes) from storage (Colossus distributed file system).
  • Scaling nodes up or down requires no data redistribution or downtime.
  • Minimum recommended cluster size for production: 3 nodes (for replication and load distribution).
• Data Model
  • Each row is uniquely identified by a Row Key
    • The only index Bigtable maintains.
  • Data within a row is organized into Column Families (defined at table creation) and individual columns within each family.
  • Bigtable is not relational.
    • There is no SQL support, no joins, no secondary indexes.
    • All queries must be either a row key lookup or a row key range scan.
  • Each cell stores a value + a timestamp, enabling multiple timestamped versions of the same cell to be retained.
• Row Key Design (Critical for Performance)
  • Bigtable sorts all data lexicographically (alphabetically) by Row Key and distributes key ranges across nodes (tablets).
  • Hotspotting Problem
    • If the row key is sequential (e.g., a pure incrementing timestamp or auto-incrementing device ID), all consecutive writes land on a single Bigtable node (tablet server), causing a severe performance bottleneck.
  • Mitigation Strategies
    • Prepend a hash prefix to distribute writes across nodes.
    • Reverse timestamps so the most recent event sorts first (e.g., [MAX_TIMESTAMP - timestamp]).
    • Design composite keys
      • [Tenant_ID]#[Device_ID]#[Reversed_Timestamp] scatters by tenant and device, then keeps each device’s data in reverse chronological order for efficient range scans.
• Replication
  • Bigtable supports cross-cluster replication across up to 8 clusters in different regions.
  • Replication is eventual consistency, not synchronous. Not suitable for strong consistency requirements.
  • Use cases
    • low latency global reads, disaster recovery, isolating batch analytics workloads from real time serving traffic.
• !! Bigtable has no free tier and charges per node-hour.
• A cluster always has at least 1 node running.
• Use the emulator for local development.

---

Enterprise Data Warehousing & Messaging

BigQuery

A serverless, highly scalable, cost-effective multi-cloud data warehouse designed for enterprise business intelligence, OLAP analytics, and ML workloads.

Architecture

• Decouples storage and compute over a petabit-scale internal network:
  • Colossus
    • Google’s distributed file system storing data in an optimized Capacitor columnar format.
    • Columnar storage means only the specific columns referenced by a query are scanned, not entire rows.
  • Dremel
    • A massively parallel processing (MPP) query execution engine that distributes SQL queries across thousands of workers simultaneously.
  • Jupiter
    • The internal petabit-scale network fabric connecting Colossus and Dremel, enabling high throughput data transfer between storage and compute without bottlenecks.
• Because storage and compute are decoupled and serverless, there are no servers to provision, no indexes to build manually, and no cluster to manage.

Slot-Based Compute Model

• A slot is a unit of computational capacity (CPU + RAM + networking) used to execute BigQuery SQL queries.
• On-Demand Pricing
  • Pay for the volume of data scanned by each query.
  • Default: ~$5 per TB scanned.
  • No reservation required, slots are shared from a regional pool.
• Capacity Pricing (Editions)
  • Purchase dedicated slot reservations for predictable workloads.

Edition	Commitment	Best For
Standard	Per-second autoscale	Mixed, variable query workloads
Enterprise	Per-second autoscale + reservations	Production analytics with SLA requirements
Enterprise Plus	Highest priority + cross-region failover	Mission-critical, regulated analytics

• Flex Slots
  • Short term (60 second minimum) slot commitments for burst capacity during large batch jobs.

Performance Optimization Techniques

• Partitioning
  • Divides a massive table into smaller physical partition segments based on a column value, so queries only scan relevant segments rather than the entire table.
    • Partition types: TIMESTAMP/DATE column, INGESTION_TIME (auto), or INTEGER RANGE.
    • !! Always include a WHERE clause filtering on the partition column to trigger partition pruning and avoid a full table scan.
• Clustering
  • Organizes data within each partition by sorting rows based on the contents of up to 4 specified columns (e.g., customer_id, region).
    • Clustering allows BigQuery to skip entire blocks of data within a partition when the query filter matches the clustered column order.
• !! Always pair partitioning AND clustering together for optimal query performance. Partitioning reduces data scanned at the macro level; clustering reduces it further at the micro level.
• NEVER use SELECT * in production.
  • BigQuery scans every column listed in the query.
  • Columnar storage means column count directly determines scan cost.

BigQuery Storage

• Native Tables
  • Standard BigQuery managed columnar storage.
• External Tables
  • Query data directly from Cloud Storage (CSV, JSON, Parquet, ORC, Avro), Google Drive, or Cloud Bigtable without loading it into BigQuery first.
  • Slower than native tables.
  • No caching.
• BigQuery Omni
  • Run BigQuery queries against data stored in AWS S3 or Azure Blob Storage without moving data.
  • Useful for multi-cloud analytics.
• Materialized Views
  • Pre-computed query results that BigQuery automatically refreshes when the underlying table data changes. Dramatically accelerates expensive repeated aggregation queries.

BigQuery ML (BQML)

• Train and run ML models directly inside BigQuery using standard SQL
  • No need to export data to Vertex AI.
• Supported model types:
  • linear regression, logistic regression, k-means clustering, matrix factorization, deep neural networks, importing TensorFlow models.
• Best for data analysts who know SQL but not Python/ML frameworks.

BigQuery Data Transfer Service

• Automates scheduled data ingestion from SaaS sources (Google Ads, YouTube, Campaign Manager, Salesforce) into BigQuery datasets on a recurring basis.

BigQuery Authorized Views

• A view granted access to underlying data without exposing the raw source tables to the view’s users.
• Standard pattern for row/column-level security:
  • Create a view that filters sensitive columns, then grant users access to the view, not the underlying table.

Streaming Inserts vs. Load Jobs

	Streaming Inserts	Load Jobs (Batch)
Latency	Data queryable within seconds	Data available after job completes (minutes)
Cost	Higher per GB	Free (included in storage)
Best For	Real-time dashboards, event pipelines	Bulk historical data loads
Source	Pub/Sub → Dataflow → BigQuery	GCS, local files, other BQ tables

Pub/Sub (Standard)

The foundation of asynchronous, event-driven architectures and streaming data ingestion pipelines on GCP.

• Fully managed and global by default
  • No infrastructure to provision.
• Scales infinitely and automatically.
• Guarantees at-least-once delivery
  • A message may be delivered more than once (consumers must be idempotent to handle duplicate processing safely).
• Message Ordering can be enforced by assigning an Ordering Key to related messages.
  • All messages with the same key are delivered to a single subscriber in order.
  • Ordering not guaranteed across different regions.
• Highly resilient, synchronous replication across multiple zones within a region.
• Default message retention
  • 7 days (configurable).
  • Messages not acknowledged within the subscription’s acknowledgement deadline (default 10 seconds, up to 600 seconds) are redelivered.

Core Pub/Sub Concepts

• Topic
  • A named resource to which publishers send messages. Think of it as a channel.
• Subscription
  • A named resource representing an interest in receiving messages from a Topic.
    • Pull Subscription
      • Subscriber calls the Pub/Sub API to explicitly request messages (polling).
      • The subscriber controls the rate of consumption.
    • Push Subscription
      • Pub/Sub delivers messages by making HTTPS POST requests to a configured subscriber endpoint (e.g., a Cloud Run service URL).
      • No polling required; serverless friendly.
• Message
  • The data payload (up to 10 MB) plus optional key-value attributes.
  • Messages are Base64-encoded when delivered via the API.
• Dead Letter Topic (DLT)
  • When a message fails delivery after a configurable number of attempts, Pub/Sub automatically forwards it to a designated dead-letter topic for investigation and reprocessing.

Pub/Sub Fan-Out Pattern

• A single Topic can have multiple independent subscriptions.
  • Each subscription gets its own copy of every message.
• This enables fan-out
  • One event (e.g., a new order placed) triggers multiple independent downstream processes simultaneously (e.g., inventory service, email service, analytics pipeline) without any coupling between them.

Pub/Sub Lite

A zonal or regional messaging service for predictable, massive throughput at significantly lower cost.

• Requires manual provisioning and management of partition count, storage capacity, and throughput capacity.
• !! Use exclusively when data volume is extremely high and traffic patterns are stable/predictable, and to reduce messaging cost by up to 80% versus standard Pub/Sub.
• Zonal variant provides no cross-zone replication; data is only in one zone.

	Pub/Sub (Standard)	Pub/Sub Lite
Scope	Global	Zonal or Regional
Management	Fully serverless	Manual capacity provisioning
Scaling	Automatic, infinite	Manual partition scaling
Replication	Multi-zone synchronous	Zonal only (zonal variant)
Cost	Higher	Up to 80% lower
Best For	Variable traffic, default choice	Massive stable throughput, cost optimization

---

Supporting Data Services

Cloud Dataflow

• A fully managed, serverless stream and batch data processing service based on Apache Beam.
• The standard pipeline for: Pub/Sub → Dataflow → BigQuery (real-time streaming ETL).
• Handles windowing, late data arrival, and exactly-once processing semantics.
• !! When you see “streaming ETL”, “real-time pipeline from Pub/Sub to BigQuery”, or “Apache Beam” in a question.. the answer is Dataflow.

# Managed Service for Apache Spark (formerly Dataproc)

• A fully managed Apache Spark and Hadoop service for running existing big data workloads on GCP.
• !! Use Dataproc when migrating existing Hadoop/Spark jobs to GCP
  • lift-and-shift of on-premises big data clusters.
  • Do not build new pipelines with Dataproc if BigQuery or Dataflow can solve the problem instead.
• Clusters can be ephemeral (spin up for a job, then delete) to minimize cost.
• Can use preemptible VMs for batch worker nodes to further reduce cost.

Managed Service for Apache Airflow (formerly Composer)

• A fully managed Apache Airflow service for orchestrating complex, multi-step data pipelines across GCP services.
• Schedules and monitors workflows that span BigQuery queries, Dataflow jobs, Dataproc clusters, Cloud Storage transfers, and external APIs.
• !! Use Cloud Composer when you need a workflow orchestrator.
  • A scheduler that coordinates the sequence and dependencies of multi-step pipelines.

---

Deciding Between Storage/Database Products

1. Unstructured data (files, images, videos, backups)?
   • Cloud Storage
2. Message queue / streaming ingestion layer?
   • Serverless, global, variable traffic
     • Pub/Sub (Standard)
   • Predictable massive volume, cost-focused
     • Pub/Sub Lite
3. Analytical Data Warehouse (OLAP / Business Intelligence)?
   • BigQuery
   • If streaming ingestion is needed first:
     • Pub/Sub → Dataflow → BigQuery
4. Operational, high-throughput, millisecond-latency NoSQL?
   • Mobile/web app sync, user profiles, JSON documents
     • Firestore
   • IoT, time-series, financial tickers, heavy write throughput
     • Bigtable
5. Relational database (OLTP)?
   • Global write scale + cross-regional strong consistency
     • Cloud Spanner
   • Enterprise PostgreSQL performance / HTAP workloads
     • AlloyDB
   • Standard MySQL / PostgreSQL / SQL Server, < 64 TB, single region
     • Cloud SQL
6. Migrating existing Hadoop/Spark jobs?
   • Cloud Dataproc
7. Real-time or batch ETL pipelines?
   • Cloud Dataflow (Apache Beam)

---

Key Terms & Definitions

• ACID — Atomicity, Consistency, Isolation, Durability: the four properties of a reliable relational database transaction.
• OLTP — Online Transactional Processing: high-volume, low-latency reads/writes (e.g., checkout systems). Use Cloud SQL, AlloyDB, or Spanner.
• OLAP — Online Analytical Processing: complex aggregations over large historical datasets (e.g., business reporting). Use BigQuery.
• HTAP — Hybrid Transactional/Analytical Processing: serving both workload types from a single engine. AlloyDB’s columnar engine enables this.
• External Consistency — Spanner’s consistency model; the strongest possible in distributed systems (stronger than serializable).
• Hotspotting — When a poorly designed Bigtable row key causes disproportionate writes to funnel to a single tablet node, degrading performance.
• Partition Pruning — BigQuery skipping irrelevant table partitions during a query because the WHERE clause filters on the partition column, reducing data scanned and cost.
• Clustering (BigQuery) — Sorting rows within a partition by specified column values so BigQuery can skip entire data blocks that don’t match the query filter.
• Slot (BigQuery) — A unit of computational capacity (CPU + RAM) for executing BigQuery SQL queries.
• Signed URL — A time-limited, cryptographically signed URL granting unauthenticated access to a specific GCS object.
• Pull vs. Push Subscription (Pub/Sub) — Pull: subscriber polls for messages. Push: Pub/Sub delivers messages via HTTPS POST to a subscriber endpoint.
• Dead Letter Topic — A Pub/Sub destination for messages that repeatedly fail delivery, enabling manual investigation and reprocessing.
• Idempotency — The property where processing the same message more than once produces the same result. Required for Pub/Sub consumers due to at-least-once delivery.
• Object Versioning — GCS feature retaining historical versions of objects; protects against overwrites and accidental deletion.
• Bucket Lock — Makes a GCS retention policy permanent and irremovable; required for WORM compliance.
• TrueTime — Google’s GPS+atomic-clock-based time synchronization infrastructure used by Cloud Spanner to guarantee external consistency.