CertifyCloudxAce every cloud certification.

PDE sample questions with answers

10 free practice questions for the Professional Data Engineer exam. Try each one, then open the answer to see why the right option wins and every other option loses.

Question 1Designing data processing systems

A European retailer receives erasure requests under data protection law and must be able to prove, within a month of each request, that the customer's rows are gone from its BigQuery warehouse. The customer table is partitioned by signup month and clustered by customer_id, a daily snapshot of it is kept for thirty days, and the dataset's time travel window is set to the maximum. The privacy team has already deleted the rows with a DML statement and considers the matter closed. What should you do?

  1. A.

    Replace the DML deletion with a partition drop on the partition containing the customer, because dropping a partition bypasses time travel entirely.

  2. B.

    Export the table, filter the customer out with Dataflow, and load the result into a new table, which is the only supported way to guarantee removal.

  3. C.

    Re-run the deletion as a DELETE with FOR SYSTEM_TIME AS OF set to the current timestamp, which removes the customer's row from the historical versions held by time travel as well as from the current table.

  4. D.

    Point out that the rows remain readable through time travel and in the snapshots, and add a step that lets those copies age out or deletes the affected snapshots before the deadline.

Show answer

Answer: D

A DML delete removes the row from the current table but leaves it readable through time travel and inside existing snapshots, so erasure is not complete until those copies are gone.

  • A. Partition drops are also covered by time travel, and the partition holds every customer who signed up that month.
  • B. Works but is disproportionate, and the claim that it is the only supported method is untrue.
  • C. FOR SYSTEM_TIME AS OF is read-only syntax; there is no way to delete from historical versions with DML.
  • D. Names the two copies that still expose the data — time travel and snapshots — and sequences the erasure against the legal deadline.
Question 2Designing data processing systems

An electricity supplier loads half-hourly meter readings from three hundred thousand meters. A small proportion of readings each day are implausible — negative consumption or values a thousand times the meter's rating. The regulator requires that the published consumption series never contain an implausible value, and separately requires that no reading is ever discarded, because a disputed bill must be traceable to the original record. Settlement runs at 06:00 whether or not every meter has reported. What should you do?

  1. A.

    Load every reading into a raw table, then publish only readings that pass the plausibility rules into the curated series, routing the rest to a quarantine table linked back to the raw record.

  2. B.

    Clamp implausible values to the meter's rated maximum and to zero on load, and record the original value in the job's log entry.

  3. C.

    Fail the pipeline when any implausible reading is detected, so that nothing is published until the readings have been corrected at source.

  4. D.

    Load every reading into the curated series together with a plausibility flag column, publish the filtering rule in the data dictionary, and leave each downstream consumer to apply the flag as it sees fit in its own reports.

Show answer

Answer: A

Two obligations pull in opposite directions — publish nothing implausible, discard nothing — and only separating a raw layer from a curated layer with a quarantine satisfies both.

  • A. A raw layer preserves every reading while the curated layer excludes implausible ones, with quarantine linking the two.
  • B. Fabricates values in the published series and keeps the original only in logs, which are not a traceable record.
  • C. Turns a routine data quality issue into a daily failure of a settlement process with a fixed deadline.
  • D. Delegates a regulatory obligation to every consumer, so one missing filter breaches it.
Question 3Designing data processing systems

A bank's organization has accumulated hundreds of role bindings over six years. A new regulation says that nobody — including organization administrators, and including any binding granted in future — may read the data in the finance_restricted dataset, except two named service accounts. The security team cannot audit every existing binding before the deadline and needs a control that overrides allow policies. What should you do?

  1. A.

    Create an IAM deny policy at the organization that denies bigquery.tables.getData on that dataset to all principals, listing the two service accounts as exception principals.

  2. B.

    Create a custom role that omits bigquery.tables.getData and replace every existing binding on the finance project with that role.

  3. C.

    Move the dataset into a brand-new project outside the current folder hierarchy, grant only the two service accounts on it, and ask each team to clean up the bindings they previously inherited.

  4. D.

    Apply a VPC Service Controls perimeter around the finance project so that only requests made by the two service accounts are allowed to enter it.

Show answer

Answer: A

An IAM deny policy is evaluated before allow policies and applies to every principal including administrators, so it blocks existing and future bindings in one step.

  • A. Deny rules are evaluated before allow policies and bind every principal, including administrators and future grantees.
  • B. Requires the exhaustive binding audit the team cannot perform, and does not constrain bindings granted later.
  • C. Organization-level bindings still inherit into the new project, and the clean-up depends on voluntary team action.
  • D. A perimeter controls movement across its boundary; a principal inside it holding a read role can still read the table.
Question 4Designing data processing systems

A publisher runs 900 nightly Hive and Spark jobs on an ageing on-premises Hadoop cluster whose hardware refresh is due in five months. Its analysts have grown around the estate and about 200 of the jobs are SQL aggregations that a warehouse would run natively; the remaining 700 are Spark jobs with custom Scala libraries. The data team is small and its budget is fixed, and the board wants the refresh avoided. A consultant proposes rewriting all 900 jobs as BigQuery SQL over nine months. What should you propose instead?

  1. A.

    Refresh the on-premises hardware for one more cycle and plan the cloud migration later, once the data team has more capacity to rewrite the custom Scala libraries properly and to retrain the analysts.

  2. B.

    Move the Spark jobs to Managed Service for Apache Spark largely unchanged to meet the hardware deadline, and refactor the 200 SQL aggregations into BigQuery as a second, separately funded phase.

  3. C.

    Accept the consultant's plan, since ending on a single warehouse platform gives the lowest long-term cost of ownership.

  4. D.

    Move everything to Managed Service for Apache Spark (formerly Dataproc) unchanged, including the SQL aggregations, and keep the estate as it is once it is running in the cloud.

Show answer

Answer: B

A hard hardware deadline and a small team make a staged approach correct: rehost what moves as it is, and refactor only the portion where the warehouse clearly pays back.

  • A. Spends the capital the board wants avoided and defers the migration without reducing its difficulty.
  • B. Meets the hardware deadline by rehosting, and refactors only the subset where the warehouse pays back, in a separately funded phase.
  • C. Misses the hardware deadline and asks a small team to reimplement custom Scala libraries as SQL.
  • D. Lift and leave: the SQL aggregations keep paying for cluster time and no modernisation ever follows.
Question 5Designing data processing systems

A loyalty programme must remove membership numbers from its analytics tables. Analysts still need to join purchases to visits on that identifier and to count distinct members, and the fraud team must occasionally recover the original number for a named case with approval. Which de-identification transformation should you use?

  1. A.

    Deterministic encryption with a cryptographic key, so equal inputs always yield the same token and the value can be re-identified later with the same key.

  2. B.

    Cryptographic hashing of the membership number with SHA-256.

  3. C.

    Replacing the membership number with the infoType name, so every value becomes the literal string MEMBERSHIP_NUMBER.

  4. D.

    Bucketing membership numbers into ranges of one thousand so individual members cannot be distinguished.

Show answer

Answer: A

Deterministic encryption preserves referential integrity for joins and distinct counts while remaining reversible for an approved re-identification.

  • A. Deterministic and reversible with the key, so joins, distinct counts and approved re-identification all work.
  • B. Deterministic but irreversible, so the fraud team can never recover the original number.
  • C. Collapses every value to one constant, destroying joins and distinct counts.
  • D. Generalisation merges distinct members into ranges, so the identifier no longer identifies a row.
Question 6Designing data processing systems

A bank must move 60 TB of historical files into Cloud Storage and then keep sending about 400 GB a day for the eighteen months until its source systems are retired. Its data centre has a 1 Gbps internet link shared with everything else, of which the network team will allow 200 Mbps during a nightly window, and security has refused to send customer data over the public internet in any form. A Partner Interconnect can be provisioned in about three weeks. What should you do?

  1. A.

    Run an agent-based Storage Transfer Service job over the existing internet link within the nightly window, with a 200 Mbps bandwidth limit.

  2. B.

    Provision the Partner Interconnect, then run an agent-based Storage Transfer Service job over it for the initial 60 TB and for the daily increments.

  3. C.

    Set up Cloud VPN over the existing internet link and run the transfer through the tunnel so that the traffic is encrypted end to end.

  4. D.

    Order Transfer Appliance units for the 60 TB, and send the daily increments over the existing internet link once the appliances have been ingested.

Show answer

Answer: B

Security has ruled out the public internet for eighteen months of ongoing transfers, so a private connection must be provisioned and used for both the backfill and the daily feed.

  • A. Uses the public internet, which is prohibited, and the allocated bandwidth cannot absorb the backfill.
  • B. Private connectivity satisfies the security constraint for the whole period, with enough capacity for backfill and daily increments.
  • C. Encrypts the traffic but still routes it over the public internet, and tunnel throughput is limited.
  • D. Solves the one-off 60 TB but leaves eighteen months of daily transfers on the forbidden path.
Question 7Designing data processing systems

A parcel carrier processes scan events in a nightly batch today. The product roadmap says that within eighteen months customers will expect tracking within a minute of each scan, but the funding for that work has not been approved and may never be. The transformation logic — deduplication, enrichment from a reference table, and aggregation per route — will be identical in both modes. The team must not build the batch pipeline twice. What should you do?

  1. A.

    Build the batch pipeline as BigQuery scheduled queries now, and rewrite the logic as a Beam streaming pipeline if and when the requirement is funded.

  2. B.

    Build the streaming pipeline now and run it continuously, since it also satisfies the current nightly requirement and avoids a second project later.

  3. C.

    Build the batch pipeline on Managed Service for Apache Spark (formerly Dataproc) with Spark now, and enable Spark Structured Streaming on the same cluster when the requirement is funded.

  4. D.

    Write the transformations once as an Apache Beam pipeline and run it on Dataflow in batch mode now, switching the source and the windowing to streaming when the requirement is funded.

Show answer

Answer: D

Beam's unified model lets one set of transformations run as a batch pipeline today and as a streaming pipeline later, so the logic is written once without paying for streaming before it is needed.

  • A. Scheduled SQL cannot become a streaming pipeline, so the logic would have to be written a second time.
  • B. Pays for continuous low latency that is neither required nor funded for up to eighteen months.
  • C. Structured Streaming is a different API with different semantics, so the change is a rewrite rather than a configuration switch.
  • D. One Beam codebase serves both modes, so only the source and windowing change when streaming is funded.
Question 8Designing data processing systems

A clearing bank's reporting warehouse sits in a BigQuery dataset in europe-west4. A new continuity standard sets a recovery point objective of fifteen minutes and a recovery time objective of one hour for a complete regional outage, and requires that recovery not depend on re-running the ingestion pipelines, because the upstream systems would also be down. The dataset is 40 TB and is written continuously through the day. What should you do?

  1. A.

    Rely on the dataset's time travel window, which allows any table to be recovered to a point in the last seven days.

  2. B.

    Take a BigQuery table snapshot of every table every fifteen minutes and copy the snapshots to a dataset in a second region.

  3. C.

    Configure cross-region dataset replication to a second region and promote the replica to primary if the first region is lost.

  4. D.

    Export the dataset to a dual-region Cloud Storage bucket each night and load the files into a second region if the first is lost.

Show answer

Answer: C

Managed cross-region replication keeps a continuously updated copy in a second region and is promoted in minutes, which is the only option that meets a fifteen-minute recovery point without re-running ingestion.

  • A. Time travel data lives in the same region as the dataset, so it is unavailable during a regional outage.
  • B. Per-table snapshot and copy jobs cannot reliably complete inside a fifteen-minute window at this size, and recovery is a restore.
  • C. A continuously maintained replica in another region can be promoted quickly, meeting both objectives without re-ingesting.
  • D. A nightly export gives a recovery point measured in hours, not minutes, and reloading 40 TB breaks the recovery time.
Question 9Designing data processing systems

A prototype accepted 200 events per second from a pilot of 50 shops: a Cloud Run service wrote each event directly to Cloud SQL, and hourly SQL queries produced the reports. The national rollout to 4,000 shops has pushed it to 15,000 events per second. Writes now time out during peaks, the reporting queries take longer than the hour between them, and adding CPU has stopped helping. What should you do?

  1. A.

    Move the database to a larger Cloud SQL machine type with more memory, add read replicas, and run the reporting queries against the replicas.

  2. B.

    Shard the events across eight Cloud SQL instances by shop identifier, and union the results in the reporting layer.

  3. C.

    Batch the writes in the Cloud Run service so that each transaction inserts a thousand events at once, and add an index for the reporting queries.

  4. D.

    Publish events to Pub/Sub, process them with a streaming Dataflow job, and write to BigQuery for reporting, keeping Cloud SQL only for operational data.

Show answer

Answer: D

The prototype's design is the problem: a relational instance is both the ingestion buffer and the analytical store, and at rollout scale those jobs must be separated into services built for them.

  • A. Postpones the write limit and keeps a row-oriented engine serving analytical aggregation.
  • B. Adds application-level sharding complexity and eight instances to operate, and cross-shard reporting remains slow.
  • C. Reduces write overhead but leaves the reporting engine mismatch entirely unaddressed.
  • D. Separates ingestion buffering, processing and analytics into services designed for each, removing both limits.
Question 10Designing data processing systems

A team has declared a primary key on customer_id in the customers table and a matching foreign key on the orders table, and has told stakeholders that duplicate customer rows are now impossible. A week later, duplicates appear. What should you tell the team, and what should they do?

  1. A.

    Constraints are enforced only on rows inserted with DML, so they should convert the loading pipeline from load jobs to MERGE statements.

  2. B.

    BigQuery does not enforce these constraints — they inform the optimizer — so uniqueness must be maintained by the loading logic and checked by an assertion.

  3. C.

    The constraint requires the table to be clustered by the key column to take effect, so they should recreate the table clustered by customer_id.

  4. D.

    The constraint was declared after the table already contained duplicate customer rows, so they should deduplicate the table once and the declared constraint will hold from that point onwards.

Show answer

Answer: B

BigQuery table constraints are unenforced metadata used by the query optimizer, so uniqueness has to be guaranteed by the pipeline and verified by a check.

  • A. There is no enforcement path for DML either; the write mechanism makes no difference.
  • B. Constraints are unenforced optimizer metadata, so the pipeline must maintain uniqueness and a check must verify it.
  • C. Clustering affects pruning and storage layout, not constraint enforcement.
  • D. Declaration does not validate existing rows, and nothing prevents new duplicates afterwards.

Keep going with 490 more PDE questions

Free papers every day, in the real exam formats, with progress by exam domain. Unlock every paper and timed mock exam when you are ready.

Practise freeSee all PDE papers
PDE sample questions with answers (10 free) · CertifyCloudx