Google Cloud Professional Data Engineer Study Guide

Google Cloud Professional Data Engineer Study Guide

Issuing body: Google Cloud Jurisdiction: Global (vendor certification — not a legal license to practice) Level: Professional Prerequisites: None formal; experience with GCP data services recommended. (Source: catalogue record data-ai-ml-cert-007 — note: this record was not individually re-verified against Google's catalog in the catalogue pass; confirm current status at cloud.google.com/learn/certification before scheduling.) Renewal: 2 years.

1. What this exam is

The Professional Data Engineer exam is Google's data-platform credential: designing, building, and operating data processing systems on GCP — ingestion, storage, pipelines, analytics, and ML-data workflows — with reliability, security, and cost discipline. Where the ML Engineer exam asks "how do we train models," this asks "how does the data platform work." It's the GCP counterpart to AWS DEA-C01, with BigQuery (not Redshift) at the center of the universe and Dataflow (not Glue) as the processing workhorse.

2. Pathway and exam format

• Typical path: Associate Cloud Engineer (GCP literacy) → 1–2 years building data pipelines (BigQuery, Dataflow, Pub/Sub, Composer) → this exam. Often paired with the ML Engineer cert for full-stack data/ML roles.
• Format: $200 USD, 2 hours, 50–60 multiple-choice/multiple-response. Scaled scoring (unpublished passing score; target ~70%+).
• The exam's center of gravity is BigQuery. Roughly a third of questions touch it directly (modeling, partitioning, slots, security, cost). Know BigQuery like a DBA knows their database.

3. Knowledge domains (with theory)

Public sections (approximate): Designing data processing systems (~22%), Building and operationalizing (~25%), Operationalizing ML models (~20%), Ensuring reliability (~15%), Visualizing/advocating (~8%), Security/governance (~10%). In practice: storage & ingestion, pipelines, BigQuery mastery, operations, security.

Storage: the GCP data platform

BigQuery (the center):

• Architecture: decoupled storage (Colossus) + compute (Dremel execution). Slots = compute currency: on-demand ($/TB scanned) vs flat-rate/reservations (dedicated capacity, autoscaling). BigQuery editions (Standard/Enterprise/Enterprise Plus — feature tiers for the reservation model).
• Partitioning (ingestion-time, or column: DATE/TIMESTAMP/integer-range) + clustering (up to 4 columns, sort within partitions) — the cost/performance levers. Partition pruning happens automatically; clustering helps filtered/joined queries on non-partition columns.
• Materialized views (smart tuning: automatic refresh, query rewrite) vs scheduled queries vs BI Engine (in-memory acceleration for dashboards).
• Data transfer: BigQuery Data Transfer Service (SaaS sources, scheduled); BigQuery Omni (cross-cloud: AWS/Azure data queried from BigQuery); BigLake (governed tables over open formats in Cloud Storage — the lakehouse answer: Iceberg/Parquet in GCS with BigQuery's engine + governance).
• ML integration: BigQuery ML (see ML Engineer guide), remote models/functions.

Operational stores: Cloud SQL (relational OLTP), AlloyDB (Postgres-compatible, fast), Spanner (global OLTP — the source your CDC pipelines read from), Bigtable (wide-column, time-series/IoT at scale), Firestore (document, app backends).

Streaming ingestion: Pub/Sub (global messaging: topics, subscriptions — pull/push, ordering keys, exactly-once delivery within the subscription semantics, dead-letter topics, message retention/replay) → Dataflow (Beam: windowed streaming ETL) → BigQuery (streaming inserts with deduplication via insert IDs) / Bigtable.

Batch ingestion: Transfer Service (SaaS), Storage Transfer Service (S3→GCS), Dataflow batch, Datastream (CDC from MySQL/Postgres/Oracle → BigQuery/GCS — the GCP analogue of DMS).

Pipelines: Dataflow, Composer, and orchestration theory

Apache Beam model (the theory Dataflow implements): unified batch/stream with windows (fixed, sliding, sessions), watermarks (event-time progress tracking — "we've seen all data up to T"), triggers (when to emit: watermark, processing-time, data-driven), and accumulation modes (discarding vs accumulating). Late data handled via allowed lateness. The exam tests whether you think in event time: "results by 5-minute windows, tolerate 10-min late data" → fixed windows + allowed lateness + trigger.

Dataflow: serverless Beam runner; autoscaling (throughput-based), streaming engine (decoupled compute — the modern architecture), exactly-once processing (checkpointing + transactional sinks), flex templates for CI/CD. Dataflow Prime (vertical autoscaling, right-sizing).

Orchestration: Cloud Composer (managed Airflow — DAGs, the complex-dependency answer), Workflows (serverless orchestration for service chaining), Cloud Scheduler + Pub/Sub (cron triggers), Dataform (SQL-based ELT in BigQuery — dbt-like).

The reliability theory: idempotent pipeline stages (rerunnable without duplication), dead-letter patterns (quarantine bad records, never halt), schema evolution strategy (backward/forward compatible — Beam coders, BigQuery schema update options), backfill design (partitioned reruns).

BigQuery cost and performance mastery (the exam's core)

• Cost levers in order: partition + cluster (prune bytes) → SELECT only needed columns → materialized views/BI Engine for repeated queries → slots reservations for sustained load → result caching (24 h) → query dry-runs before running.
• Performance levers: clustering on join/filter columns, denormalization (nested/repeated fields — BigQuery rewards wide tables, not 3NF), partition alignment in joins, avoiding SELECT *, shuffle reduction (filter before join).
• Slots math: on-demand = $5–6/TB (region-dependent); reservations = fixed $/slot-month; autoscaling handles burst. Break-even: sustained scanning >~300–400 TB/month usually favors reservations (do the math per workload).
• Quotas/limits: concurrent queries, streaming insert quotas, partition limits (4,000/day per table) — know they exist; the exam's "pipeline suddenly failing" questions often trace to quota.

Reliability, security, governance

• Reliability: pipeline SLAs (freshness), data quality checks (completeness, validity — Dataflow + BigQuery constraints/checks), monitoring (Cloud Monitoring on Dataflow lag, BigQuery slot utilization, Pub/Sub unacked messages — unacked message age = the Pub/Sub iterator-age equivalent), disaster recovery (BigQuery cross-region dataset copy, table snapshots/clones).
• Security: IAM (dataset/table/row-level via authorized views + row-level security policies), column-level security (policy tags via Dataplex/Sensitive Data Protection), CMEK, VPC Service Controls, audit logs.
• Governance: Dataplex (data mesh: lakes, zones, assets; unified governance, lineage, quality), Data Catalog (metadata search), Sensitive Data Protection/DLP (PII discovery/classification/de-identification — tokenization, masking for analytics copies).

ML operationalization (the overlap with ML Engineer)

Feature Store for serving features, Vertex Pipelines for train/deploy, BigQuery ML for in-warehouse modeling, model monitoring basics. The exam expects literacy, not ML-engineer depth — know the handoff points.

4. Key formulas and numbers

• BigQuery cost: bytes billed = bytes scanned (on-demand); dry-run first. 1 TB/day unpartitioned → ~$150–180/month; partitioned/clustered Parquet-equivalent → often <10%.
• Partition math: partition by the column you filter on (usually date); cluster by the next 1–4 filter/join columns. Clustering helps most when partition pruning is insufficient.
• Streaming inserts: ~$0.01–0.05 per 200 MB (region-dependent) + storage; deduplication via insertId (best-effort, 1-min window — design idempotent consumers anyway).
• Pub/Sub throughput: no shard math like Kinesis (global, auto-scaling) — but ordering keys serialize per key (throughput per key limited), and flow control on subscribers matters.
• Dataflow autoscaling: workers scale with backlog; streaming-engine decouples compute from state — cost follows actual processing, not provisioned capacity.
• Freshness budgeting: end-to-end latency = ingestion + processing + serving; budget each stage (e.g., 30 s total = 5 s Pub/Sub + 15 s Dataflow + 10 s BigQuery streaming buffer).

5. Worked examples

Example 1 — The canonical GCP analytics pipeline. Scenario: 50k events/sec from mobile apps; real-time dashboard (1 min freshness); daily reporting; ad-hoc exploration; 2-year retention. Solution: Pub/Sub (ordering keys per user for session logic; dead-letter topic) → Dataflow streaming (5-min fixed windows, allowed lateness 10 min; streaming engine) → BigQuery (partitioned by date, clustered by event type/user; streaming inserts) → Looker Studio/Looker on BI Engine for the dashboard; scheduled queries → aggregate tables for daily reporting; BigLake over GCS for the 2-year archive (cheaper than BigQuery long-term storage for cold data). One pipeline, tiered serving.

Example 2 — BigQuery cost rescue. Scenario: $25k/month BigQuery bill; investigation shows analysts running unpartitioned full-table scans on a 50 TB table. Solution: (1) Partition by date + cluster by customer_id (the two filter columns). (2) Materialized view for the top-10 repeated dashboard queries. (3) Workgroup... (BigQuery: reservation + custom quotas per project; maximum_bytes_billed per query as a guardrail). (4) Migrate sustained load to flat-rate slots (compute: 50 TB scanned daily ≈ $250/day on-demand → slots break even fast). (5) INFORMATION_SCHEMA auditing to find the next offender. Estimated: 60–80% reduction.

Example 3 — CDC from Cloud SQL to BigQuery. Scenario: Keep BigQuery within 5 min of a 2 TB Cloud SQL Postgres; minimal ops. Solution: Datastream (managed CDC: reads the WAL, writes to GCS/BigQuery staging) → Dataflow (dedupe/merge into the BigQuery tables) or Datastream's direct BigQuery destination. Handle: schema changes (Datastream schema-drift handling + alerts), backfill (initial snapshot then CDC), deletes (tombstone/merge logic). The exam's preferred answer over hand-rolled Debezium.

Example 4 — Multi-region analytics with residency. Scenario: EU user data must stay in the EU; US analysts need aggregate (non-PII) reports. Solution: Datasets pinned to EU multi-region; VPC Service Controls perimeter around EU projects; Sensitive Data Protection de-identification (tokenize user IDs) producing an aggregate dataset replicated to the US; row-level security + column policy tags so US analysts physically can't see PII. The residency requirement is architectural (locations + perimeters), not a promise.

Example 5 — Late data in streaming. Scenario: IoT sensors with spotty connectivity; data arrives up to 2 hours late; hourly aggregates must be correct eventually. Solution: Dataflow fixed 1-hour windows on event time, allowed lateness 2 h, trigger: after watermark + late firings (accumulating mode to correct earlier results); BigQuery sink with insertId dedup; a daily reconciliation batch job recomputing the last 3 days (belt and suspenders). The exam's lesson: streaming gives timely results, batch gives correct results — design both.

6. Standards and regulations cross-reference

• GDPR — same pipeline implications as DEA-C01: erasure propagation (BigQuery DML deletes + partition rewrites + cache expiry), data minimization, regional dataset locations, DPIAs.
• DAMA-DMBOK — the data-management vocabulary (Dataplex is Google's DMBOK-flavored product: domains, governance, lineage).
• ISO/IEC 27001 — logging (audit logs), backup (table snapshots), access control (IAM + row/column security) mapped to Annex A.
• PCI DSS v4.0.1 — tokenize PAN at ingestion (Sensitive Data Protection), scope the analytics CDE, no PAN in logs/exports.
• EU AI Act / ISO 42001 — when pipelines feed regulated AI: lineage (Dataplex), quality gates, documented provenance.
• EU Energy Efficiency Directive — data-center reporting (context for EU deployments).

7. Pitfalls, traps, and exam strategy

Trap patterns:

1. The Kinesis-brain trap: Pub/Sub has no shards — don't apply Kinesis shard math. Throughput limits are per-ordering-key and subscriber flow control.
2. The streaming-insert trap: insertId dedup is best-effort with a short window — design idempotent downstream anyway.
3. The partition trap: partitioning by the wrong column (high-cardinality ID instead of date) gives tiny partitions and no pruning benefit. Partition by filter column (date), cluster by the rest.
4. The denormalization trap: BigQuery rewards nested/denormalized tables; 3NF thinking causes expensive joins. But don't denormalize operational stores (Cloud SQL/Spanner stay normalized).
5. The Composer trap: simple cron pipeline → Scheduler + Workflows, not a full Airflow cluster. Composer is for complex DAGs.
6. The slot trap: sustained heavy load on on-demand = burning money; but small/ad-hoc on reservations = idle spend. Match pricing to the load shape.

Strategy: for every scenario, sketch: sources → ingestion (batch/stream?) → processing (Dataflow/Composer?) → storage (BigQuery table design: partition/cluster) → serving (dashboard/ML/export) → governance. Memorize: BigQuery pricing levers, Pub/Sub semantics (ordering, dedup, DLQ), Beam windowing concepts, Datastream/Dataplex/Dataform roles. 2 min/question — BigQuery questions are usually fast if you know the levers.

8. Practice problems

P1. A 100 TB table is queried 20×/day with WHERE date = CURRENT_DATE(), but it's not partitioned. Compute the monthly on-demand cost and the fix. Answer: 100 TB × 20 × 30 days = 60,000 TB scanned... wait: 100 TB/scan × 20/day × 30 = 60,000 TB/month × ~$5 = $300,000/month. (This is why the exam loves this question.) Fix: partition by date → each query scans ~0.3 TB (one day of ~100 TB/365) → 0.3 × 20 × 30 = 180 TB/month ≈ $900/month. 99.7% reduction. Add clustering on the next filter column and a maximum_bytes_billed guardrail.

P2. Pub/Sub subscription backlog is growing; subscribers are healthy. List three causes. Answer: (1) Flow control too tight on subscribers (max outstanding messages throttled — the boring common cause). (2) Ordering keys serializing: one hot key blocks its partition (same hot-key lesson as Kinesis). (3) Ack deadline too short → redelivery storms (messages reprocessed, backlog never drains). Check: unacked message age, redelivery rate, per-key throughput.

P3. Design exactly-once ingestion from Pub/Sub to BigQuery for payment events. Answer: Pub/Sub message IDs as BigQuery insertIds (best-effort dedup) + a Dataflow dedup stage (stateful, keyed by event ID, windowing) writing idempotent MERGE statements keyed on event_id. Exactly-once = dedup at two layers + idempotent sink. Acknowledge Pub/Sub only after BigQuery commit (or use transactional outbox from the source). State the guarantee honestly: effectively-once via idempotency, which is what production means by exactly-once.

P4. A Dataflow streaming job's watermark is stuck 30 min behind. What does this mean and how do you fix it? Answer: Watermark stuck = some input partition hasn't advanced event time (a straggler shard, or one Pub/Sub partition with no new data — watermarks advance on the slowest input). Consequences: windows don't fire, results delayed. Fix: check for hot/stuck partitions (Dataflow monitoring: watermark lag per stage), ensure all input partitions receive data (idle partitions hold the watermark — inject heartbeats or use withAllowedLateness + early triggers), consider single-output... For Pub/Sub-sourced: the fix is usually ensuring data flows on all subscriptions/partitions or restructuring to avoid the straggler.

P5. Choose: Dataflow vs Dataproc (managed Hadoop/Spark) for a nightly 5 TB Spark ETL the team already runs on-prem in Spark. Answer: Dataproc for the migration (lift-and-shift Spark, same code, cluster control, custom images) — fastest path with existing Spark expertise. Dataflow (Beam) for the rewrite — serverless, unified batch/stream, better long-term ops. The exam's lesson: Dataproc = migrate Spark as-is; Dataflow = the strategic Beam future. Many teams do Dataproc first, Beam second.

P6. An EU analytics dataset must support GDPR erasure within 30 days across BigQuery, GCS archive, and Looker cache. Sketch the mechanism. Answer: (1) BigQuery: DML DELETE WHERE user_id = X on partitioned tables (rewrite affected partitions), then verify via audit query. (2) GCS archive: object versioning + lifecycle; rewrite affected objects (Parquet row-groups can't delete single rows — rewrite the files). (3) Looker/BI Engine: cache TTL < 30 days (or manual invalidation). (4) Orchestrate via Workflows/Composer; log every step (audit evidence). (5) Prevent recurrence: tokenize user IDs at ingestion (Sensitive Data Protection) so erasure = dropping the token mapping (crypto-shredding at the mapping table).

Original ETS reference writing, verified against public sources on 2026-09-30. Thin sourcing is flagged inline. Not legal advice — confirm current editions with the issuing body.