PDE sample questions with answers
The other 405 questions are in the practice tool, with answers revealed as you go.
Start 20 questionsDesigning data processing systems
- A healthcare insurer runs claims, membership, and provider network teams that each generate large datasets. Leadership wants each team to control its own data, while other teams can find and consume those datasets across the company. Which operating model fits this goal best?
- A global insurer holds policyholder records for customers in Germany, Brazil and Canada. Local regulators forbid moving identifiable records outside each country. The actuarial team still wants a consolidated view of claims trends across all three. What should the data engineer do?
- A retailer curates a sales feed in BigQuery and wants several outside partners to query it. The retailer must be able to cut off any partner immediately and see which partner ran which queries. Which approach satisfies both requirements?
- A company adopts a data mesh model. The marketing domain's customer engagement data is to be published as a reusable data product. Who should be accountable for its quality, documentation and service levels?
- A bank wants one set of completeness and validity checks applied to customer tables in several BigQuery datasets, with the results reported to a central place where data stewards can review them. Which Dataplex capability fits?
- A payments platform needs a single SQL database that accepts writes in several continents at once. Every transaction must be atomic and strongly consistent, and the system must keep serving if an entire region fails. Which option should you choose?
- A mobile game needs a live ranking of millions of players that updates as scores change. Players must see their rank with low-millisecond latency, and the ranking must stay correct after each atomic score update. Which design is most appropriate?
- A logistics firm wants three carriers to read a curated subset of its shipment tables. Each carrier should see only its own shipments, the firm must be able to revoke a carrier's access immediately, and no data should be copied into the carriers' environments. Which solution should the firm use?
- A ride-hailing company's reporting pipeline must survive the complete loss of a region. Acceptable data loss is at most one hour, and service must be restored within four hours. Which design meets both targets?
- A company runs a dozen event-driven applications on Apache Kafka in its own data center and wants them on Google Cloud as a managed service. Teams want to keep their existing Kafka client libraries, topic configuration and consumer groups, with the smallest possible code change. Which service fits?
- An insurer keeps a 40 TB BigQuery dataset. It must be able to restore the data as it stood no more than an hour before an accidental delete, keep those recovery points for 90 days, and minimize storage cost. Which approach is most cost-effective?
- A media company wants its clickstream history to stay readable by Spark on another cloud if it ever leaves Google Cloud. Which storage design best keeps the data portable?
- A ride-sharing platform streams trip events into BigQuery. Events with missing driver IDs, wrong timestamp types, or fares outside a plausible range must never reach the reporting tables. Which pipeline design handles this?
- A bank's ingestion team wants partner files to land in an unvalidated area, move to a validated area only after quality rules pass, and then feed a consumer layer. Which Google Cloud construct organizes this with governed zones?
- A payments-ingestion service must keep running if an entire Google Cloud zone fails, must not lose acknowledged data, and must recover within 30 minutes. Which deployment best fits?
- A fintech's data team needs development, test, and production environments with identical table definitions, and production data must never be reachable from development. Which setup is recommended?
- A mobile app emits event records whose optional attributes change frequently. Which format and load approach lets the warehouse absorb new optional attributes without breaking the pipeline?
- A ticketing company's Cloud Spanner database must lose no committed transactions, even if an entire region becomes unavailable. Which configuration delivers a zero recovery point objective?
Ingesting and processing data
- A retailer's MySQL order database must feed BigQuery with inserts, updates, and deletes within a few minutes. The team wants no custom code that tracks binary log positions. Which managed service should the data engineer choose?
- A payments service publishes ledger events for thousands of accounts and needs each account's events handled in sequence. What guarantee do Pub/Sub ordering keys provide?
- A nightly job loads sales records for business date D. Late records for date D keep arriving over the following days and must land in the correct date partition. Reruns after a failure must never double count. Which approach should the team use?
- A streaming job uses event-time windows, and events sometimes arrive several minutes late. What does the watermark tell the pipeline?
- A subscription uses the default message retention period. A consumer team was away for eight days and never acknowledged the messages that arrived during that time. What happens to those unacknowledged messages?
- A team receives JSON responses from a vendor API. Each response contains nested objects and arrays of line items, and the team needs one row per line item in BigQuery at high volume. Which approach is best?
- A Dataflow pipeline counts device events per device ID over hourly windows. A few popular devices produce enormous volumes, and the workers handling those keys run out of memory because the pipeline gathers every value into a list before counting. Which change addresses this?
- A logistics firm receives shipment events in one record type and carrier updates in another. The downstream sink accepts a single common record type, and the pipeline must combine both sources into one collection. Which steps achieve this?
- A logistics platform's order-events consumer must be offline for a two-day database migration. Events published during the outage still need processing afterward, and the producers cannot be changed. What should the team do?
- A Dataflow streaming job enriches events with location data from a third-party lookup API that enforces a request quota. Which design keeps the job within that quota while keeping throughput high?
- A data platform team needs a workflow that runs a validation step, branches to a repair path or a publish path depending on the result, and offers a graphical view of each run's history. Which service fits?
- Before an engineering group writes any code for a clickstream pipeline, it documents where events come from, where results go, which transformations apply, how encryption is handled, and how jobs are scheduled. Which phase of pipeline work is this?
- A logistics company drops CSV manifests into a storage bucket at unpredictable times during the day. Each new manifest should start a load into the analytics warehouse within seconds, without any idle polling. What should the engineer set up?
Storing data
- A SaaS startup runs an order-management application that needs a managed PostgreSQL database with automated backups, point-in-time recovery and automatic maintenance. It does not need writes in multiple regions. Which service is the best fit?
- A payments company must keep transaction receipts in a Cloud Storage bucket for 10 years. Regulators require that no one, including project owners, can delete or overwrite a receipt before its retention period ends. Which configuration meets this requirement?
- An IoT platform stores sensor readings in BigQuery. Every dashboard query specifies a reading time window, and many queries also filter by sensor_id. What table design fits this access pattern best?
- An executive dashboard reruns the same aggregation query every few minutes against an unchanging daily table. The dashboard must respond in under a second when people interact with it. Which approach fits best?
- A payments platform needs a relational schema with multi-row ACID transactions, SQL access and strongly consistent reads. It must also keep synchronous replicas on three continents so the service survives a regional outage. Which storage service fits?
- A data governance lead wants to know which BigQuery datasets and tables consume the most logical and physical storage, so that cleanup work can be targeted. Which built-in source provides this view?
- A mobile inventory app must refresh its screen the moment a stock document changes in Cloud Firestore, without the client repeatedly asking the server whether anything changed. What should the developer attach to that document?
- A photo marketplace stores originals and edited versions of each image. Prior file versions must stay recoverable for 90 days and then be removed without manual work. Which design meets this?
- A genomics company keeps raw sequencing outputs that Spark jobs, a BigQuery dashboard, and a Presto cluster must all read. Storage should avoid duplicate copies. Which approach enables shared access?
- A data engineer must show auditors which upstream files and which Dataflow pipeline steps produced a reporting table in BigQuery, so that each table can be traced back to its origins. Which capability should the engineer use?
- A marketing analyst runs a query over a 10 TB events table to find the 100 accounts with the most conversions last month. The table is partitioned by event date, but the query filters only on conversion status. The analyst wants a lower on-demand bill. Which change helps most?
- A hospital moved imaging files into the coldest Cloud Storage class last year. An auditor now needs three of those files within the next half hour for a review. Which statement describes the situation correctly?
Preparing and using data for analysis
- A logistics manager wants a Looker Studio scorecard to reflect shipments as they land in a BigQuery table, with no manual steps in between. Which data source configuration should be chosen?
- An orders table stores each order's line items as an ARRAY column inside a single row. An analyst needs one output row per line item, with the order ID repeated on each row. Which operator accomplishes this?
- A retailer has three years of weekly sales history with yearly peaks around holidays and a gradual upward trend. The team needs a forecast of the next twelve weeks, produced entirely inside BigQuery. Which model should they train?
- An analyst in BigQuery needs a trailing seven-day average of daily revenue for each row, keeping one output row per day. Which expression fits?
- A data scientist building a BigQuery ML regression must rescale a numeric feature to the 0 to 1 range, and the same scaling must be applied automatically whenever the model scores new rows. Which approach achieves this?
- A report statement computes the same expensive window-function subquery in three separate branches, and that work accounts for a large share of slot consumption. Which change reduces the total slot time?
- A retailer's event table stores a TIMESTAMP for every hour of activity, and the finance team wants monthly totals. Which approach is the most efficient way to group the rows?
- A bank drops CSV statements into a Cloud Storage bucket, and some files contain card numbers. The bank wants each new file inspected and sensitive values masked automatically as soon as it arrives. Which design does this?
- A payroll table holds an employee salary column next to job title and department. Analysts should query the rest of the table but must receive an access denial when they select salary. Which configuration achieves this?
- A streaming pipeline must not lose more than 0.1% of its input records. Which metric measures compliance with this requirement?
- A team wants to make PDFs and web pages searchable by meaning for a retrieval-augmented generation assistant. The pipeline must extract the text, split it into passages, and produce vectors that a retriever can search. Which combination of Google Cloud capabilities fits?
- A data engineer must restrict a shared BigQuery table so each analyst sees only the rows for regions assigned to them, with the assignment determined by the analyst's identity. Which BigQuery feature does this?
- A company's business users need daily refreshed charts and reports built on warehouse data, delivered to their inboxes on a schedule without anyone rebuilding the visuals by hand. Which approach fits best?
Maintaining and automating workloads
- A streaming pipeline stopped processing at 2 a.m. The on-call engineer must find the error messages written by the workers and also check the throughput and backlog graphs for the same time window. Which pair of services is most appropriate?
- A ride-hailing company publishes trip events to a Pub/Sub topic consumed by a pool of workers. Operators want a Cloud Monitoring signal showing how many messages are still waiting to be acknowledged on the subscription, so they can detect consumers falling behind. Which metric is appropriate?
- A regulated insurer requires that every BigQuery dataset in its organization be encrypted with keys its security team controls. The control must block creation of BigQuery datasets that use Google-managed keys in any project. Which mechanism enforces this?
- A team wants every push to the production branch of its Git repository to start a Cloud Build run that tests and deploys the pipeline code, with no manual step. Which trigger configuration should they use?
- A payments pipeline must encrypt a customer dataset in Cloud Storage with keys the company controls, and it needs key rotation and an audit trail of key usage. Which service should hold the keys?
- A data science group runs one Spark job roughly twice a week and must keep compute costs low. The cluster should consume Dataproc capacity only while the job runs. Which setup fits best?
- A request enters an API front end, calls a serverless function, and that function runs a BigQuery query. A support engineer needs to see the time spent in each hop for one request. Which Google Cloud capability provides that view?
- A reporting DAG must run only on working days, skipping weekends and a list of national holidays that the business maintains in a table. Which setup accomplishes this?
- An Airflow DAG must hold until a nightly export lands in a Cloud Storage prefix. Several such waits run at the same time, and the team wants idle waits to stop occupying executor slots. What should the sensor setup use?
- A nightly task pulls currency rates from a third-party service that sometimes returns server errors for a few minutes at a time. The team wants the task to recover from these brief outages without flooding the service. Which Airflow settings fit best?
- A Dataflow streaming pipeline that builds session windows needs a code change to one parsing step. Operators want the new version to take over without discarding the in-progress windowed state. Which approach meets this?
- A Dataflow streaming job calls an external enrichment API for every element. Worker CPU stays near 15 percent while the job falls further behind its input. Which change most directly addresses the cause?
- A streaming service reads a database password for its connection pool. The platform team wants rotated passwords to take effect without a redeploy. Which approach best fits?