PDE domain - 22% of the exam

Designing Data Processing Systems

Designing Data Processing Systems is 22% of the PDE exam. These are the objectives it covers, each with practice questions, with every answer explained.

The domain in numbers

73
Practice questions
4
Objectives
55
Medium
18
Hard

Where people go wrong

  • Pointing a data-movement service at the wrong source or target

    The wrong answers pick a service whose source or destination does not fit. Datastream is offered for a move into Cloud SQL, and Database Migration Service for landing change data in BigQuery. BigQuery Data Transfer Service is offered for Oracle or MySQL change capture, and Transfer Appliance for near real-time loads. Datastream reads database logs and writes to BigQuery or Cloud Storage. Database Migration Service moves databases into Cloud SQL or AlloyDB with continuous replication. Transfer Appliance ships bulk files offline.

    See it in a sample question
  • Offering periodic copies where the recovery point is minutes

    Daily exports, hourly exports and nightly backups are offered where the recovery point is 15 minutes, 5 minutes or zero. A daily export gives at best a 24-hour recovery point, hourly exports sit close to 60 minutes, and a nightly restore loses later transactions. These targets need continuous replication instead: a dual-region Cloud Storage bucket with turbo replication for a 15-minute recovery point, BigQuery cross-region dataset replication, or a Spanner multi-region configuration whose synchronous replication gives a zero recovery point.

    See it in a sample question
  • Choosing a detective control for a preventive requirement

    Audit logs, asset feeds, alerting and after-the-fact remediation are offered where the requirement is to stop an action. They report once the bucket, dataset or copy already exists, so a window of exposure remains. Preventive controls act at creation or call time. An organisation policy constraint such as resource locations or public access rejects the resource, and a VPC Service Controls perimeter denies the API call, whatever IAM grants are in place.

    See it in a sample question
  • Copying data where it must stay in place and stay open

    Loading data into BigQuery native storage, copying it into Google Cloud or exporting it nightly is offered where the data must stay on Amazon S3 or Azure, or must stay portable. Native storage uses a proprietary format, and copies breach the keep-in-place and no-egress requirements. BigQuery Omni with BigLake tables queries S3 and Azure Data Lake Storage in place, and Apache Iceberg on Cloud Storage keeps an open format that BigQuery, Spark and Trino all read.

    See it in a sample question
  • Giving one pipeline tool another tool's job

    The wrong answers hand each tool a role that belongs to a neighbour. Dataflow is offered for SQL models inside BigQuery, Dataform for ingesting from many external systems, and Workflows or Cloud Scheduler for a Python DAG. Dataform compiles SQLX into BigQuery jobs with assertions. Dataflow runs Apache Beam. Cloud Data Fusion is a visual builder that runs on Dataproc Spark. Cloud Composer is the managed Airflow service for DAG dependencies.

    See it in a sample question

Objectives in this domain

What to study

The official documents this domain's practice questions cite most.

Sample questions from this domain

Free sampleDesigning Data Processing Systemshard

A retail group runs a self-managed PostgreSQL 14 database on-premises that backs its order-management system, and the team wants a near zero-downtime migration into Cloud SQL for PostgreSQL with continuous replication from the existing primary until cutover. They will keep the source writable during the migration window and need a managed service that handles initial load and ongoing change capture without writing custom pipelines. Which Google Cloud service fits this role most directly?

  • ABigQuery Data Transfer Service, configured with a PostgreSQL source connector to load the operational tables on a recurring schedule.
  • BDatabase Migration Service, using a continuous migration job that performs an initial dump and then streams changes from the source PostgreSQL primary until promotion. Correct
  • CDatastream, writing change events into Cloud Storage and then loading them into Cloud SQL with a scheduled Dataflow batch job.
  • DTransfer Appliance, by exporting nightly base backups and write-ahead log archives onto the appliance and reloading them into Cloud SQL after ingestion.
Identify Database Migration Service as the managed path for near zero-downtime homogeneous database migration with continuous replication into Cloud SQL. Database Migration Service is built specifically for homogeneous PostgreSQL and MySQL migrations into Cloud SQL and AlloyDB; it orchestrates an initial dump, runs continuous logical replication while the source stays writable, and supports a controlled promotion of the replica to primary, which is the textbook pattern for minimising application downtime.

Why A is wrong: BigQuery Data Transfer Service moves data into BigQuery from SaaS sources and a small set of databases, but its destination is BigQuery, not Cloud SQL, and it is built for analytics loads rather than transactional cutover with continuous replication.

Why B is correct: Database Migration Service is the managed homogeneous migration product for PostgreSQL and MySQL into Cloud SQL and AlloyDB, performing an initial load followed by continuous logical replication and a controlled promotion that minimises downtime at cutover.

Why C is wrong: Datastream captures change events from PostgreSQL but its supported destinations are BigQuery and Cloud Storage, not Cloud SQL, so reaching the operational target requires additional pipelines and gives up the managed cutover semantics of Database Migration Service.

Why D is wrong: Transfer Appliance is an offline bulk data shipping device for very large object datasets where network transfer is impractical, and it offers no continuous replication, so it cannot deliver the near zero-downtime cutover the team needs.

Free sampleDesigning Data Processing Systemsmedium

A telecommunications operator runs a Dataflow streaming pipeline in europe-west1 that ingests Pub/Sub call detail records and writes aggregates to a BigQuery dataset in europe-west1. The regulator requires that, if europe-west1 becomes unavailable, the team must be able to resume processing in europe-west4 with an RPO of no more than 5 minutes and an RTO of no more than 30 minutes. Which architecture best meets both targets while keeping steady-state cost reasonable?

  • ARely on the regional Dataflow service to auto-restart in a different zone of europe-west1 and use BigQuery managed backups to restore the dataset into europe-west4 on demand if the entire region is lost.
  • BRun a hot standby Dataflow job in europe-west4 from the same Pub/Sub topic using snapshots, configure BigQuery cross-region dataset replication to europe-west4, and fail traffic over via a managed routing layer when europe-west1 is degraded. Correct
  • CSchedule a Cloud Scheduler job that exports the BigQuery dataset to a multi-region Cloud Storage bucket every hour and load it back in europe-west4 if needed, while letting Pub/Sub buffer messages until europe-west1 returns.
  • DDeploy a Dataflow job in europe-west4 that pulls from a separate mirrored Pub/Sub topic populated by a Dataflow copy job, and replicate BigQuery results with scheduled queries that copy partitions every 30 minutes.
Choose a regional failover pattern for streaming pipelines that uses Pub/Sub snapshots and BigQuery cross-region replication to satisfy tight RPO and RTO targets. Pub/Sub snapshots capture the acknowledgement state of a subscription so a recovery pipeline in another region can resume from a known position within retention, which is what bounds the data-loss window. Combined with BigQuery cross-region dataset replication, the sink in the secondary region is already populated, and a routing flip restores writes quickly. Together these mechanisms meet a 5 minute RPO and a 30 minute RTO without paying for a fully duplicated active-active stack at all times.

Why A is wrong: Zonal restart only protects against zone-level failures, not a regional outage, and restoring BigQuery from managed backups in another region typically takes hours, blowing the 30 minute RTO and the 5 minute RPO.

Why B is correct: Pub/Sub topics are global, snapshots let the secondary pipeline resume from a known offset within the message retention window so the lost work stays under 5 minutes, BigQuery cross-region replication keeps the aggregate dataset readable in europe-west4, and a managed routing flip restores write traffic well inside 30 minutes.

Why C is wrong: Hourly exports give an RPO close to 60 minutes which already breaches the 5 minute target, and waiting for the original region to return is a recovery strategy, not a regional failover, so the RTO is unbounded.

Why D is wrong: Mirroring through a second topic doubles ingestion cost and risks divergence, and 30 minute scheduled copies between BigQuery datasets miss the 5 minute RPO, so the design is both more expensive and out of spec.

Free sampleDesigning Data Processing Systemsmedium

An organisation administrator wants to guarantee that no Cloud Storage bucket in any project beneath a particular folder can ever be created or modified to allow public access, regardless of which project owner attempts the change. The control must be enforced centrally and must not rely on auditing after the fact. Which mechanism best meets this requirement?

  • AA custom IAM role at the folder that omits storage.buckets.setIamPolicy, assigned to every principal who might otherwise grant public access.
  • BA Cloud Audit Logs sink to BigQuery that alerts when a bucket binding includes allUsers, paired with a Cloud Function that revokes the binding.
  • CA VPC Service Controls perimeter around the folder's projects that blocks all unauthenticated requests to the Cloud Storage API.
  • DAn organisation policy at the folder that enforces the constraint preventing public access to Cloud Storage buckets, inherited by all projects under the folder. Correct
Apply organisation policy constraints to enforce preventive security controls across a resource hierarchy. Organisation policies are evaluated at admission time and inherited down the hierarchy. The constraint that disables public access for Cloud Storage causes the API to reject any attempt to add allUsers or allAuthenticatedUsers to a bucket's policy, providing the central, preventive control the requirement asks for without depending on logging or remediation.

Why A is wrong: Removing the setIamPolicy permission from one custom role does not prevent other principals with project owner or storage admin roles from making buckets public, so the control is not centrally enforced.

Why B is wrong: Detection and remediation after the fact leaves a window where data is publicly accessible, and the requirement explicitly rules out an audit-driven approach in favour of central preventive enforcement.

Why C is wrong: VPC Service Controls restrict API access across network boundaries to mitigate data exfiltration, but they are not the mechanism for preventing a bucket's IAM policy or setting from being changed to allow public access.

Why D is correct: Organisation policy constraints are evaluated at admission time across the resource hierarchy, and the constraint that disables public access for Cloud Storage prevents any IAM binding or bucket setting that would grant allUsers or allAuthenticatedUsers, inherited automatically by descendant projects.

Free sampleDesigning Data Processing Systemsmedium

An insurer plans to keep its primary claims warehouse in BigQuery while also retaining the ability to query the same underlying Parquet files in Cloud Storage from open-source engines such as Apache Spark and Trino on Dataproc, using one consistent set of row-level and column-level security rules. The team prefers an open table format that supports schema evolution and time travel. Which combination best meets these requirements?

  • ABigQuery managed tables loaded from the Parquet files, with separate Ranger policies deployed on Dataproc to mirror BigQuery row-level and column-level security.
  • BExternal BigQuery tables defined directly on the Parquet files with authorised views, while Dataproc reads the same files via plain Cloud Storage paths without any BigLake involvement.
  • CCloud Storage object access controls combined with BigQuery row access policies on a federated query layer, leaving schema evolution and time travel to be handled by application code.
  • DBigLake Iceberg tables over the Parquet files in Cloud Storage, with row-level and column-level security defined on the BigLake table and honoured by BigQuery and supported open-source engines. Correct
Select BigLake with an open table format as the way to share one governance model across BigQuery and open-source engines on object storage. BigLake tables sit above object storage, including BigLake Iceberg tables on Cloud Storage, and propagate row-level and column-level security to BigQuery and compatible open-source engines through a connector. This keeps the Parquet-backed Iceberg files as the single source of truth while ensuring that the same policies are enforced regardless of whether the consumer is BigQuery, Spark on Dataproc, or Trino.

Why A is wrong: Loading into BigQuery managed tables breaks the multi-engine open-format goal because Spark and Trino can no longer read the canonical storage, and duplicating policies in Ranger creates drift between BigQuery and Dataproc enforcement.

Why B is wrong: Plain external tables and authorised views protect BigQuery readers only; Dataproc reading raw object paths bypasses those rules entirely and breaks the requirement for one consistent set of row-level and column-level security policies.

Why C is wrong: Object-level access controls cannot express row-level or column-level rules, and pushing schema evolution and time travel into application code abandons the open table format the team explicitly wants.

Why D is correct: BigLake supports the Apache Iceberg open table format on object storage and enforces row-level and column-level security at the BigLake table layer, so BigQuery and supported engines such as Spark and Trino see the same governed view of the data.

Free sampleDesigning Data Processing Systemsmedium

An analytics team must build a daily set of warehouse models that read from base tables in BigQuery, apply business logic, and materialise curated marts. They want declarative SQL development with dependency-aware execution, automated tests on the resulting tables, and Git-based source control for the model definitions. Which Google Cloud service is purpose-built for this workflow?

  • ACloud Data Fusion, because its visual pipeline studio supports SQL nodes, Git integration, and built-in assertions that validate the curated marts after each run.
  • BDataflow, because Apache Beam pipelines can be written in SQL, scheduled daily, and tested with PAssert to validate every materialised table in the warehouse.
  • CDataform, because it compiles SQLX models into a directed acyclic graph of BigQuery jobs, supports unit tests and assertions on outputs, and stores model code in a Git repository. Correct
  • DCloud Composer, because directed acyclic graphs of BigQuery operators provide scheduling, dependency tracking, and per-table data quality checks for warehouse models.
Recognise Dataform as the BigQuery-native, declarative SQL transformation service with built-in assertions and Git integration. Dataform translates SQLX files into a dependency graph and submits the resulting SELECT and CREATE TABLE statements to BigQuery, so the warehouse itself is the execution engine. Assertions encode data quality checks such as uniqueness and non-null constraints, and code lives in a Git repository for review. This combination matches the stated requirements more precisely than orchestration-only or Beam-based tools.

Why A is wrong: Cloud Data Fusion is a visual ETL tool that compiles pipelines to Dataproc Spark, not a SQL-first framework for BigQuery models, so it is the wrong fit even though it does integrate with version control.

Why B is wrong: Dataflow can run Beam SQL but its execution engine is the Dataflow service rather than BigQuery, and it does not provide declarative model management or built-in dataset assertions of the kind described.

Why C is correct: Dataform is designed exactly for this: SQLX definitions are compiled into a dependency graph of BigQuery jobs, assertions verify referential and uniqueness constraints, and projects are version controlled in Git.

Why D is wrong: Composer can orchestrate BigQuery jobs but the SQL, dependency definitions, and tests would still have to be authored separately. Dataform provides those as first-class concepts, which is what the requirement asks for.

Other domains in this exam

See also the PDE cert hub, the study guide, and the cheat sheet.

Examworthy is not affiliated with or endorsed by Google Cloud. Original, blueprint-aligned practice material only.