About This Edition: A clean, intermediate-level architectural guide covering all 19 Subsections and 70 Official Considerations of the Google Cloud Certified Professional Data Engineer curriculum, illustrated with Mermaid architecture blueprints and 140 custom-designed concept & architecture diagrams.
Chapter Plan at a Glance
Ch # | Chapter Title | Subsections | Considerations | Custom Diagrams | Core Focus |
|---|---|---|---|---|---|
00 | Overview | — | Mermaid | The Architectural Planes, plain-English guide to in-scope services, and the 10 Core Architectural Principles. | |
01 | 1.1, 1.2, 1.3, 1.4 | 17 | 34 | BigQuery column/row-level security, Sensitive Data Protection (DLP), CMEK,VPC-SC, SLO/RPO/RTO pipeline resilience, open-format portability (Iceberg/Parquet), and BigQuery Data Transfer Service migrations. | |
02 | 2.1, 2.2, 2.3 | 14 | 28 | Batch vs. streaming architecture, Pub/Sub ordering/exactly-once/DLQs, Apache Beam / Dataflow (windows, watermarks, triggers, state/timers, Shuffle/Prime), Dataproc Serverless, Dataform SQL ELT, and Cloud Composer. | |
03 | 3.1, 3.2, 3.3, 3.4 | 13 | 26 | Storage class lifecycles, BigQuery partitioning (ingestion/column) & clustering, nested/repeated STRUCT/ARRAY schemas, BigLake external tables (Apache Iceberg), Bigtable row-key design, and Dataplex governance. | |
04 | 4.1, 4.2, 4.3 | 11 | 22 | Materialized views, BI Engine caching, Looker semantic modeling, BigQuery ML vs. Vertex AI Feature Store preparation, RAG vector search in BigQuery/AlloyDB, and zero-copy sharing via Analytics Hub. | |
05 | 5.1, 5.2, 5.3, 5.4, 5.5 | 15 | 30 | BigQuery Editions (Autoscaling Slots: Standard/Enterprise/Enterprise Plus) vs. on-demand, physical vs. logical storage billing, Dataflow straggler/hotkey debugging, INFORMATION_SCHEMA diagnostics, and DR failover. | |
06 | All Sections | — | — | Side-by-side Look-Alike Tool Comparison Matrix, 5-Step Architecture Decision Checklist, and Top 25 Golden Rules. |
Detailed Table of Contents
Chapter 0: The PDE Reference Stack & 10 Core Principles
- 0.1 The Master Architectural Map: All In-Scope Services of the PDE Stack
- 0.2 The 10 Core Architectural Principles
Chapter 1: Designing Data Processing Systems: Security, Privacy, Reliability, Portability & Migration
- Subsection 1.1: Designing for security and compliance (Section 1: Designing Data Processing Systems)
- Consideration 1.1a: Identity and Access Management (e.g., Cloud IAM and organization policies) (Diagrams:
pde-1.1a-1-principle-of-least.jpg,pde-1.1a-2-zero-static-service.jpg) - Consideration 1.1b: Data security (encryption and key management) (Diagrams:
pde-1.1b-1-managed-encryption-keys.jpg,pde-1.1b-2-customer-managed.jpg) - Consideration 1.1c: Privacy (e.g., strategies to handle personally identifiable information) (Diagrams:
pde-1.1c-1-right-de-identification.jpg,pde-1.1c-2-redaction-masking.jpg) - Consideration 1.1d: Regional considerations (data sovereignty) for data access and storage (Diagrams:
pde-1.1d-1-enforce-sovereignty.jpg,pde-1.1d-2-regionalizing-dataflow.jpg) - Consideration 1.1e: Legal and regulatory compliance (Diagrams:
pde-1.1e-1-worm-immutability-storage.jpg,pde-1.1e-2-event-based-holds-vs.jpg) - Consideration 1.1f: Designing the project, dataset, and table architecture to ensure proper data governance (Diagrams:
pde-1.1f-1-hub-spoke-multi-project.jpg,pde-1.1f-2-authorized-views.jpg) - Consideration 1.1g: Multi-environment use cases (development vs. production) (Diagrams:
pde-1.1g-1-folder-project-level.jpg,pde-1.1g-2-zero-human-write-access.jpg) - Quick Decision Cheat Sheet (Subsection 1.1)
- Subsection 1.2: Designing for reliability and fidelity (Section 1: Designing Data Processing Systems)
- Consideration 1.2a: Preparing and cleaning data (e.g., Dataform, Dataflow, and Cloud Data Fusion, prompting LLMs for query generation) (Diagrams:
pde-1.2a-1-dataform-sql-elt-in.jpg,pde-1.2a-2-dataflow-apache-beam.jpg) - Consideration 1.2b: Monitoring and orchestration of data pipelines (Diagrams:
pde-1.2b-1-composer-managed-apache.jpg,pde-1.2b-2-handling-late-or-missing.jpg) - Consideration 1.2c: Disaster recovery and fault tolerance (Diagrams:
pde-1.2c-1-zero-data-loss-rpo.jpg,pde-1.2c-2-predictable-15-minute.jpg) - Consideration 1.2d: Making decisions related to ACID compliance and availability (Diagrams:
pde-1.2d-1-spanner-external.jpg,pde-1.2d-2-sql-alloydb-regional-acid.jpg) - Consideration 1.2e: Data validation (Diagrams:
pde-1.2e-1-data-validation-data.jpg,pde-1.2e-2-dataflow-pubsub-storage.jpg) - Quick Decision Cheat Sheet (Subsection 1.2)
- Subsection 1.3: Designing for flexibility and portability (Section 1: Designing Data Processing Systems)
- Consideration 1.3a: Mapping current and future business requirements to the architecture (Diagrams:
pde-1.3a-1-unify-batch-streaming.jpg,pde-1.3a-2-decouple-ingestion.jpg) - Consideration 1.3b: Designing for data and application portability (e.g., multi-cloud and data residency requirements) (Diagrams:
pde-1.3b-1-bigquery-omni-multi.jpg,pde-1.3b-2-open-file-table-formats.jpg) - Consideration 1.3c: Data staging, cataloging, profiling, and discovery (data governance) (Diagrams:
pde-1.3c-1-tier-medallion-staging-in.jpg,pde-1.3c-2-automated-discovery.jpg) - Quick Decision Cheat Sheet (Subsection 1.3)
- Subsection 1.4: Designing data migrations (Section 1: Designing Data Processing Systems)
- Consideration 1.4a: Analyzing current stakeholder needs, users, processes, and technologies, and creating a plan to get to desired state (Diagrams:
pde-1.4a-1-phase-discovery.jpg,pde-1.4a-2-phase-prioritizing-use.jpg) - Consideration 1.4b: Planning migration and validation to Google Cloud (e.g., BigQuery Data Transfer Service, Database Migration Service, Transfer Appliance, Google Cloud networking, Datastream) (Diagrams:
pde-1.4b-1-planning-migration.jpg,pde-1.4b-2-bigquery-data-transfer.jpg) - Quick Decision Cheat Sheet (Subsection 1.4)
Chapter 2: Ingesting & Processing Data Pipelines: Pub/Sub, Dataflow, Dataproc, Dataform & Composer
- Subsection 2.1: Planning the data pipelines (Section 2: Ingesting and Processing the Data)
- Consideration 2.1a: Defining data sources and sinks (Diagrams:
pde-2.1a-1-pub-sub-fan-out.jpg,pde-2.1a-2-multiple-independent.jpg) - Consideration 2.1b: Defining data transformation and orchestration logic (Diagrams:
pde-2.1b-1-to-choose-elt-extract.jpg,pde-2.1b-2-benefit-composer-dataflow.jpg) - Consideration 2.1c: Networking fundamentals (Diagrams:
pde-2.1c-1-networking-fundamentals.jpg,pde-2.1c-2-dataflow-virtual-private.jpg) - Consideration 2.1d: Data encryption (Diagrams:
pde-2.1d-1-uniform-cmek-pipeline.jpg,pde-2.1d-2-complete-cmek-pipeline.jpg) - Quick Decision Cheat Sheet (Subsection 2.1)
- Subsection 2.2: Building the pipelines (Section 2: Ingesting and Processing the Data)
- Consideration 2.2a: Data cleansing (Diagrams:
pde-2.2a-1-dead-letter-queue-dlq.jpg,pde-2.2a-2-stream-deduplication.jpg) - Consideration 2.2b: Identifying the services (e.g., Dataflow, Apache Beam, Dataproc, Cloud Data Fusion, BigQuery, Pub/Sub, Apache Spark, Hadoop ecosystem, and Apache Kafka) (Diagrams:
pde-2.2b-1-dataflow-apache-beam.jpg,pde-2.2b-2-dataproc-apache-spark.jpg) - Consideration 2.2c: Batch transformations (Diagrams:
pde-2.2c-1-eliminate-hot-skew.jpg,pde-2.2c-2-dataflow-shuffle-service.jpg) - Consideration 2.2d: Streaming transformations (e.g., windowing, late arriving data) (Diagrams:
pde-2.2d-1-window-types-event-time.jpg,pde-2.2d-2-watermarks-triggers.jpg) - Consideration 2.2e: Processing logic (Diagrams:
pde-2.2e-1-core-element-collection.jpg,pde-2.2e-2-right-join-pattern-side.jpg) - Consideration 2.2f: AI data enrichment (Diagrams:
pde-2.2f-1-apache-beam-runinference.jpg,pde-2.2f-2-in-warehouse-ai.jpg) - Consideration 2.2g: Data acquisition and import (Diagrams:
pde-2.2g-1-bigquery-batch-load-jobs.jpg,pde-2.2g-2-bigquery-storage-write.jpg) - Consideration 2.2h: Integrating with new data sources (Diagrams:
pde-2.2h-1-apache-kafka-integration.jpg,pde-2.2h-2-updating-running.jpg) - Quick Decision Cheat Sheet (Subsection 2.2)
- Subsection 2.3: Deploying and operationalizing the pipelines (Section 2: Ingesting and Processing the Data)
- Consideration 2.3a: Job automation and orchestration (e.g., Cloud Composer and Workflows) (Diagrams:
pde-2.3a-1-composer-managed-apache.jpg,pde-2.3a-2-to-choose-composer.jpg) - Consideration 2.3b: CI/CD (Continuous Integration and Continuous Deployment) (Diagrams:
pde-2.3b-1-tier-automated-testing.jpg,pde-2.3b-2-dataflow-flex-templates.jpg) - Quick Decision Cheat Sheet (Subsection 2.3)
Chapter 3: Enterprise Data Storage: BigQuery Warehousing, BigLake Lakehouses, Bigtable & Dataplex
- Subsection 3.1: Selecting storage systems (Section 3: Storing the Data)
- Consideration 3.1a: Analyzing data access patterns (Diagrams:
pde-3.1a-1-point-lookups-narrow.jpg,pde-3.1a-2-sub-millisecond-read.jpg) - Consideration 3.1b: Choosing managed services (e.g., BigQuery, BigLake, AlloyDB, Bigtable, Spanner, Cloud SQL, Cloud Storage, Firestore, Memorystore) (Diagrams:
pde-3.1b-1-sql-vs-alloydb-vs.jpg,pde-3.1b-2-sql-spanner-sql-bigtable.jpg) - Consideration 3.1c: Planning for storage costs and performance (Diagrams:
pde-3.1c-1-bigtable-row-design.jpg,pde-3.1c-2-avoid-hotspotting-storage.jpg) - Consideration 3.1d: Lifecycle management of data (Diagrams:
pde-3.1d-1-storage-classes-minimum.jpg,pde-3.1d-2-standard-storage.jpg) - Quick Decision Cheat Sheet (Subsection 3.1)
- Subsection 3.2: Planning for using a data warehouse (Section 3: Storing the Data)
- Consideration 3.2a: Designing the data model (Diagrams:
pde-3.2a-1-use-struct-record-array.jpg,pde-3.2a-2-querying-nested-arrays.jpg) - Consideration 3.2b: Deciding the degree of data normalization (Diagrams:
pde-3.2b-1-to-denormalize-nested.jpg,pde-3.2b-2-to-keep-normalized.jpg) - Consideration 3.2c: Mapping business requirements (Diagrams:
pde-3.2c-1-active-vs-long-term.jpg,pde-3.2c-2-90-day-automatic-long.jpg) - Consideration 3.2d: Defining architecture to support data access patterns (Diagrams:
pde-3.2d-1-table-partitioning.jpg,pde-3.2d-2-types-bigquery-bigquery.jpg) - Quick Decision Cheat Sheet (Subsection 3.2)
- Subsection 3.3: Using a data lake (Section 3: Storing the Data)
- Consideration 3.3a: Managing the lake (configuring data discovery, access, and cost controls) (Diagrams:
pde-3.3a-1-uniform-bucket-level.jpg,pde-3.3a-2-biglake-tables-beat.jpg) - Consideration 3.3b: Processing data (Diagrams:
pde-3.3b-1-replace-hdfs-gs-storage.jpg,pde-3.3b-2-share-metadata-ephemeral.jpg) - Consideration 3.3c: Monitoring the data lake (Diagrams:
pde-3.3c-1-storage-insights.jpg,pde-3.3c-2-audit-logs-data-access.jpg) - Quick Decision Cheat Sheet (Subsection 3.3)
- Subsection 3.4: Designing for a data platform (Section 3: Storing the Data)
- Consideration 3.4a: Building a data platform using Google Cloud tools (e.g., Dataplex, Dataplex Catalog, BigQuery, Cloud Storage) (Diagrams:
pde-3.4a-1-raw-zone-bronze-data.jpg,pde-3.4a-2-curated-zone-silver-gold.jpg) - Consideration 3.4b: Building a federated governance model for distributed data systems (Diagrams:
pde-3.4b-1-core-principles-of-data.jpg,pde-3.4b-2-automatic-iam-policy.jpg) - Quick Decision Cheat Sheet (Subsection 3.4)
Chapter 4: Preparing & Using Data for Analysis: BI Engine, Looker, BigQuery ML, Vertex AI & Analytics Hub
- Subsection 4.1: Preparing data for visualization (Section 4: Preparing and Using Data for Analysis)
- Consideration 4.1a: Connecting to tools (Diagrams:
pde-4.1a-1-looker-enterprise-bi.jpg,pde-4.1a-2-looker-studio-self.jpg) - Consideration 4.1b: Precalculating fields (Diagrams:
pde-4.1b-1-standard-logical-view.jpg,pde-4.1b-2-bigquery-materialized.jpg) - Consideration 4.1c: BigQuery features for business intelligence (e.g., BI Engine, materialized views) (Diagrams:
pde-4.1c-1-bigquery-bi-engine.jpg,pde-4.1c-2-materialized-view-smart.jpg) - Consideration 4.1d: Troubleshooting poor performing queries (Diagrams:
pde-4.1d-1-fix-troubleshooting-poor.jpg,pde-4.1d-2-fix-bigquery-bi-engine.jpg) - Consideration 4.1e: Security, data masking, IAM, and Cloud DLP (Diagrams:
pde-4.1e-1-viewer-credentials-vs.jpg,pde-4.1e-2-owner-credentials.jpg) - Quick Decision Cheat Sheet (Subsection 4.1)
- Subsection 4.2: Preparing data for AI and ML (Section 4: Preparing and Using Data for Analysis)
- Consideration 4.2a: Preparing data for feature engineering, training and serving ML models (e.g., BigQueryML) (Diagrams:
pde-4.2a-1-right-model-type-in.jpg,pde-4.2a-2-binary-or-multi-class.jpg) - Consideration 4.2b: Preparing unstructured data for embeddings and RAG (Diagrams:
pde-4.2b-1-step-govern-unstructured.jpg,pde-4.2b-2-step-parse-chunk.jpg) - Quick Decision Cheat Sheet (Subsection 4.2)
- Subsection 4.3: Sharing data (Section 4: Preparing and Using Data for Analysis)
- Consideration 4.3a: Defining rules to share data (Diagrams:
pde-4.3a-1-sharing-filtered-slices.jpg,pde-4.3a-2-enforcing-minimum-group.jpg) - Consideration 4.3b: Publishing datasets (Diagrams:
pde-4.3b-1-direct-iam-grants-on.jpg,pde-4.3b-2-publishing-via-analytics.jpg) - Consideration 4.3c: Publishing reports and visualizations (Diagrams:
pde-4.3c-1-looker-multi-tenant.jpg,pde-4.3c-2-looker-studio-external.jpg) - Consideration 4.3d: BigQuery sharing (Analytics Hub) (Diagrams:
pde-4.3d-1-zero-copy-linked-datasets.jpg,pde-4.3d-2-billing-separation-who.jpg) - Quick Decision Cheat Sheet (Subsection 4.3)
Chapter 5: DataOps: FinOps Resource Optimization, CI/CD Repeatability, Observability & Fault Mitigation
- Subsection 5.1: Optimizing resources (Section 5: Maintaining and Automating Data Workloads)
- Consideration 5.1a: Minimizing costs per required business need for data (Diagrams:
pde-5.1a-1-dataflow-flexible.jpg,pde-5.1a-2-dataproc-preemptible-spot.jpg) - Consideration 5.1b: Ensuring enough resources for business-critical data processes (Diagrams:
pde-5.1b-1-dataproc-autoscaling.jpg,pde-5.1b-2-fix-graceful.jpg) - Consideration 5.1c: Deciding between persistent or job-based data clusters (e.g., Dataproc) (Diagrams:
pde-5.1c-1-choose-job-based.jpg,pde-5.1c-2-required-dataproc.jpg) - Quick Decision Cheat Sheet (Subsection 5.1)
- Subsection 5.2: Designing automation and repeatability (Section 5: Maintaining and Automating Data Workloads)
- Consideration 5.2a: Creating directed acyclic graphs (DAGs) for Cloud Composer (Diagrams:
pde-5.2a-1-directed-acyclic-graphs.jpg,pde-5.2a-2-composer-scheduler.jpg) - Consideration 5.2b: Scheduling and orchestrating jobs in a repeatable way (Diagrams:
pde-5.2b-1-idempotent-bigquery.jpg,pde-5.2b-2-repeatable-ephemeral.jpg) - Quick Decision Cheat Sheet (Subsection 5.2)
- Subsection 5.3: Organizing workloads based on business requirements (Section 5: Maintaining and Automating Data Workloads)
- Consideration 5.3a: Capacity management (e.g., BigQuery Editions and reservations) (Diagrams:
pde-5.3a-1-on-demand-vs-capacity.jpg,pde-5.3a-2-on-demand-pricing.jpg) - Consideration 5.3b: Interactive or batch query jobs (Diagrams:
pde-5.3b-1-interactive-priority.jpg,pde-5.3b-2-batch-priority-batch.jpg) - Quick Decision Cheat Sheet (Subsection 5.3)
- Subsection 5.4: Monitoring and troubleshooting processes (Section 5: Maintaining and Automating Data Workloads)
- Consideration 5.4a: Observability of data processes (e.g., Cloud Monitoring, Cloud Logging, BigQuery admin panel) (Diagrams:
pde-5.4a-1-bigquery-observability.jpg,pde-5.4a-2-bigquery-admin-panel.jpg) - Consideration 5.4b: Monitoring planned usage (Diagrams:
pde-5.4b-1-auditing-slot-utilization.jpg,pde-5.4b-2-proactive-quota.jpg) - Consideration 5.4c: Troubleshooting error messages, billing issues, and quotas (Diagrams:
pde-5.4c-1-cause-troubleshooting.jpg,pde-5.4c-2-fix-dataflow-compute.jpg) - Consideration 5.4d: Managing workloads such as jobs, queries, and compute capacity (reservations) (Diagrams:
pde-5.4d-1-cancelling-runaway-jobs.jpg,pde-5.4d-2-cancel-bigquery.jpg) - Quick Decision Cheat Sheet (Subsection 5.4)
- Subsection 5.5: Maintaining awareness of failures and mitigating impact (Section 5: Maintaining and Automating Data Workloads)
- Consideration 5.5a: Designing system for fault tolerance and managing restarts (Diagrams:
pde-5.5a-1-transient-errors-http-429.jpg,pde-5.5a-2-pub-sub-replay-via.jpg) - Consideration 5.5b: Running jobs in multiple regions or zones (Diagrams:
pde-5.5b-1-zone-failures-vs-region.jpg,pde-5.5b-2-zone-failure-mitigation.jpg) - Consideration 5.5c: Preparing for data corruption and missing data (Diagrams:
pde-5.5c-1-bigquery-corruption.jpg,pde-5.5c-2-time-travel-window-days.jpg) - Consideration 5.5d: Data replication and failover (e.g., Cloud SQL, Redis clusters) (Diagrams:
pde-5.5d-1-sql-availability-regional.jpg,pde-5.5d-2-sql-availability-ha-sql.jpg) - Quick Decision Cheat Sheet (Subsection 5.5)
Chapter 6: Master Look-Alike Tool Comparison & Decision Playbook
- 6.1 The Master Look-Alike Tool Comparison Matrix
- 6.2 The 5-Step Architecture Decision Checklist
- 6.3 Top 25 Golden Rules for Final Review
Generating the Custom Illustrations (pde-image-prompts.json)
All 140 custom image prompts and output filenames for this book are stored in pde-image-prompts.json (and mirrored in openai-image-gen/out/9010-openai-prompts-pde-book.json). To generate the illustrations using openai-image-gen/images-from-prompts.py, run:
cd openai-image-gen uv run images-from-prompts.py -file ../books/pde-architecture-takeaways/pde-image-prompts.json -simple -quality medium
AwesomeGCP.com Learning
The approach taken is a question-led learning approach: you explore realistic project scenarios where you need to make decisions or take actions to achieve business goals within specific constraints. Instead of passive learning, you are challenged to think and work through the options to master the Professional Data Engineer (PDE) certification!