PCA framing

For the PCA, this section is less about memorizing ETL products and more about choosing the right pattern: warehouse vs lakehouse, batch vs streaming, direct ingest vs transformation, BI semantic layer vs raw dashboards, CDC vs one-time migration, and governance/cataloging across a data estate.

Petabyte-scale SQL analytics, dashboards, or reporting

BigQuery

Serverless analytics warehouse. Do not pick Cloud SQL/Spanner for large analytical scans.

Real-time event ingestion or decoupling producers/consumers

Pub/Sub

Global async messaging/event streaming. Use Dataflow when events need complex transforms.

Simple Pub/Sub messages directly into BigQuery with little/no transform

Pub/Sub BigQuery subscription

Lower operational overhead than running Dataflow for basic ingestion.

Streaming or batch transformation, enrichment, windowing, or joins

Dataflow

Managed Apache Beam runner for stream and batch ETL/ELT.

Existing Hadoop/Spark ecosystem or lift-and-shift big data jobs

Dataproc

Good when teams already use Spark/Hadoop and want cluster control.

Run Spark without managing clusters

Managed Service for Apache Spark / Dataproc Serverless

Better when the org wants Spark compatibility but less cluster management.

Orchestrate multi-step pipelines with dependencies and schedules

Cloud Composer / Managed Airflow

Controls workflow order. It is not the data processing engine.

Code-free visual data integration

Cloud Data Fusion

ETL/ELT pipelines with GUI, connectors, and transformations.

Near real-time replication from operational databases

Datastream

CDC from relational/other sources into BigQuery, Cloud Storage, Cloud SQL, Spanner, etc.

Enterprise BI, governed metrics, semantic model, embedded analytics

Looker

Use when the problem is trusted business metrics and analytics consumption.

Catalog, lineage, governance, quality, and discovery across data assets

Dataplex Universal Catalog / Knowledge Catalog

Govern and discover data/AI assets across projects and systems.

Query data in Cloud Storage with BigQuery SQL and fine-grained access

BigLake

Lakehouse pattern. Avoid granting direct bucket access to every consumer.

Securely share datasets or analytics assets across org boundaries

Analytics Hub

Managed data exchange/sharing pattern, often with BigQuery datasets.

BigQuery

Org-wide analytics warehouse for huge datasets, ad hoc SQL analysis, BI/reporting, ML/AI over data, and separating analytical workloads from production databases.

Petabyte-scale analytics, serverless SQL, pay-per-query/capacity model, partitioning/clustering, authorized views, row/column security, BigQuery ML/AI.

Do not choose BigQuery for low-latency OLTP transactions or per-row application serving. That is Cloud SQL, Spanner, Firestore, or Bigtable territory.

BigQuery partitioning and clustering

Controls query cost and performance by reducing how much data BigQuery scans and by colocating related data.

Time/date or integer partitioning; clustering for high-cardinality filters. Common answer when the requirement says reduce query cost/scan volume.

Partitioning is not the same as sharding tables by date manually. PCA usually prefers managed partitioned tables.

BigQuery materialized views / BI Engine

Speeds up repeated dashboard-style queries and interactive BI workloads.

Repeated aggregations, dashboards, low-latency BI queries, and cost/performance tuning without changing the raw data model.

Do not use this to fix an operational transactional database problem. It optimizes analytics access.

BigQuery ML / BigQuery AI

Lets analysts build ML or AI-powered analytics close to the data without moving data into a separate ML stack.

Predictive analytics from warehouse data, SQL-based ML, Gemini/AI-assisted analytics patterns.

Do not confuse with Vertex AI for full custom ML lifecycle, model hosting, or advanced MLOps.

Pub/Sub

Decouples producers and consumers, absorbs spikes, and provides durable asynchronous messaging for microservices and event/data pipelines.

Streaming analytics, application integration, fan-out to multiple subscribers, push/pull subscriptions, dead-letter topics, at-least-once delivery.

Pub/Sub is not a workflow orchestrator and not a transformation engine. Use Workflows/Composer for orchestration and Dataflow for transformations.

Pub/Sub Lite

Lower-cost, regional messaging option when the team can manage capacity and accept less global/fully managed simplicity.

Cost-sensitive streaming where regional scope and throughput planning are acceptable.

For PCA, default to Pub/Sub unless the question emphasizes cost, regional streaming, and capacity control.

Pub/Sub BigQuery subscriptions

Writes Pub/Sub messages directly into BigQuery when the messages do not need complex processing.

Simple streaming ingest into BigQuery without running subscriber code or a Dataflow job.

If the question requires joins, windowing, enrichment, heavy transformation, or exact deduplication logic, use Dataflow instead.

Dataflow

Managed stream and batch data processing using Apache Beam. Used to transform, enrich, aggregate, window, and route data between systems.

Pub/Sub to BigQuery with transformations, real-time analytics, batch ETL, unified stream/batch pipelines, windowing, autoscaling.

Do not pick Dataflow just to schedule jobs. Composer schedules/orchestrates; Dataflow processes data.

Dataproc

Managed Spark/Hadoop clusters for existing big data workloads, especially where teams already have Spark, Hive, Hadoop, or open-source tooling.

Lift-and-shift Hadoop/Spark, ephemeral clusters, custom images, cluster control, existing Spark jobs.

If no Hadoop/Spark dependency exists, BigQuery or Dataflow is often a more cloud-native answer.

Managed Service for Apache Spark / Dataproc Serverless

Runs Spark workloads without standing up and managing a persistent Dataproc cluster.

Spark compatibility with less infrastructure management; serverless Spark jobs; teams want Spark but not cluster operations.

Still choose BigQuery for SQL warehouse analytics and Dataflow for Beam-native streaming pipelines.

Dataproc Metastore

Central Hive-compatible metadata service for Spark/Hive ecosystems and lakehouse-style open table formats.

Shared metastore for Spark/Hive/Iceberg-style analytics, especially with Dataproc/Managed Spark.

Not a data warehouse, not a catalog for all enterprise governance. For governance/discovery, think Dataplex/Knowledge Catalog.

Cloud Composer / Managed Service for Apache Airflow

Schedules and orchestrates complex data workflows with dependencies, retries, DAGs, and cross-service tasks.

Run this after that; orchestrate BigQuery, Dataflow, Dataproc, Cloud Storage, APIs, and notifications.

Composer coordinates jobs; it does not process the data itself. Do not confuse it with Dataflow or Dataproc.

Cloud Data Fusion

Visual, code-free data integration for teams that need ETL/ELT pipelines, connectors, transformations, and repeatable ingestion without writing everything from scratch.

Low-code/no-code ETL, many connectors, business-user-friendly pipeline creation, hybrid data integration.

If the team is engineering-heavy and needs custom code/windowing, Dataflow may be a better answer.

Datastream

Serverless CDC and replication from source databases into analytics or operational destinations with minimal latency.

Near real-time replication from MySQL, PostgreSQL, AlloyDB, SQL Server, Oracle, Salesforce, MongoDB, etc. into BigQuery/Cloud Storage/Cloud SQL/Spanner.

Datastream captures changes; it does not replace DMS for migration planning or Dataflow for complex transformations.

Looker

Enterprise BI platform with governed semantic models, dashboards, embedded analytics, and consistent business metrics.

Single source of truth for business metrics, governed BI, embedded analytics, LookML semantic layer, trusted analytics for users.

Do not confuse Looker with BigQuery. BigQuery stores/analyzes data; Looker models and presents business meaning.

Looker Studio

Lightweight dashboarding/reporting tool for simpler visualizations and self-service reports.

Simple dashboards and visual reports where enterprise semantic modeling is not the core requirement.

For governed enterprise metrics and embedded analytics, Looker is the stronger PCA answer.

Dataplex Universal Catalog / Knowledge Catalog

Discovers, catalogs, governs, profiles, classifies, and tracks lineage for data and AI assets across the data estate.

Data governance, cataloging, data quality, lineage, ownership, discoverability, metadata management, data-to-AI governance.

Not a processing engine. It helps govern and understand data, not transform it.

Data lineage / data quality / catalog concepts

Helps organizations understand where data came from, how it changed, who owns it, and whether it is trustworthy.

Compliance, auditability, data quality, governance, AI grounding, impact analysis across pipelines.

Do not solve governance-only requirements with Dataflow. Use Dataplex/Knowledge Catalog plus IAM/security controls.

BigLake

Lakehouse layer that lets users query Cloud Storage data with BigQuery SQL while applying fine-grained access control.

Data lake/lakehouse over Cloud Storage, open formats such as Parquet/ORC, BigQuery interface without direct bucket access.

If performance/SLA is poor and data is heavily queried, loading into native BigQuery storage may be better.

Analytics Hub

Managed sharing/exchange of datasets, models, and analytics assets, especially around BigQuery.

Share data products internally, with partners, or across org boundaries while controlling access.

Not an ETL tool and not a dashboarding product. It is a data sharing and exchange pattern.

Dataform

SQL-based workflow and transformation management for BigQuery pipelines.

Versioned SQL transformations, dependency management, data build patterns inside BigQuery.

Do not confuse with Composer. Composer can orchestrate many services; Dataform focuses on SQL transformation workflows.

Cloud Storage as analytics landing zone

Common raw data lake landing zone before processing with Dataflow, Dataproc/Spark, Datastream, BigQuery external tables, or BigLake.

Raw/staged/curated zones, cheap durable storage, data lake patterns, event-driven processing from object creation.

Cloud Storage stores objects. It does not provide warehouse SQL performance unless paired with BigQuery/BigLake or processed into another system.

BigQuery vs Cloud SQL/Spanner

BigQuery is for analytics and large scans. Cloud SQL/Spanner are operational relational databases.

Pub/Sub vs Dataflow

Pub/Sub moves events. Dataflow transforms/enriches/aggregates events or batch data.

Pub/Sub BigQuery subscription vs Dataflow

Direct subscription is better for simple writes to BigQuery. Dataflow is better for complex transformations, windowing, and enrichment.

Dataflow vs Dataproc

Dataflow is managed Beam for stream/batch ETL. Dataproc is managed Spark/Hadoop for existing big data ecosystems.

Composer vs Dataflow/Dataproc

Composer orchestrates jobs. Dataflow/Dataproc do the processing.

Data Fusion vs Dataflow

Data Fusion is visual/low-code ETL. Dataflow is code-based and better for custom streaming/batch logic.

Datastream vs Database Migration Service

Datastream is CDC/replication. DMS is migration-focused, usually covered under database/migration topics.

Looker vs BigQuery

BigQuery is the analytics engine/warehouse. Looker is BI, semantic modeling, and consumption.

Dataplex/Knowledge Catalog vs Dataflow

Dataplex governs/catalogs data. Dataflow processes data.

BigLake vs native BigQuery storage

BigLake queries lake data in Cloud Storage. Native BigQuery storage is usually better for heavily queried, performance-sensitive analytics.

Real-time analytics

Producers -> Pub/Sub -> Dataflow -> BigQuery -> Looker. If no transform is needed, Pub/Sub BigQuery subscription may replace Dataflow.

CDC into analytics

Operational DB -> Datastream -> BigQuery or Cloud Storage -> optional Dataflow/Dataform -> Looker.

Batch ETL

Cloud Storage source/drop zone -> Dataflow or Dataproc -> BigQuery -> Looker.

Existing Hadoop modernization

Start with Dataproc for compatibility, then gradually move query/reporting workloads to BigQuery where appropriate.

Lakehouse

Cloud Storage for data lake storage + BigLake/BigQuery SQL access + Dataplex/Knowledge Catalog governance.

Governed BI

BigQuery for analytics storage/compute + Looker semantic layer to standardize metrics across the business.

Data sharing

BigQuery datasets or assets published through Analytics Hub, with IAM/VPC-SC/governance controls as needed.

BigQuery

Analytics warehouse. Petabyte-scale SQL, BI/reporting, ML/AI over data.

Pub/Sub

Async messaging/event ingestion. Decouple producers and consumers.

Dataflow

Managed Beam. Stream/batch transformations, windowing, enrichment.

Dataproc

Managed Spark/Hadoop. Existing big data ecosystem/lift-and-shift.

Composer

Managed Airflow. Orchestrate pipelines and dependencies.

Data Fusion

Code-free visual ETL/ELT with connectors.

Datastream

CDC/replication from databases into analytics destinations.

Looker

Enterprise BI and semantic model for governed metrics.

Dataplex / Knowledge Catalog

Data governance, catalog, lineage, quality, metadata.

BigLake

Lakehouse query layer over Cloud Storage using BigQuery.

Analytics Hub

Secure data and analytics asset sharing/exchange.