Professional Data Engineer Free Practice Questions — Page 6
Question 50
You have a table that contains millions of rows of sales data, partitioned by date. Various applications and users query this data many times a minute. The query requires aggregating values by using AVG, MAX, and SUM, and does not require joining to other tables. The required aggregations are only computed over the past year of data, though you need to retain full historical data in the base tables. You want to ensure that the query results always include the latest data from the tables, while also reducing computation cost, maintenance overhead, and duration. What should you do?
A. Create a materialized view to aggregate the base table data. Include a filter clause to specify the last one year of partitions.
B. Create a materialized view to aggregate the base table data. Configure a partition expiration on the base table to retain only the last one year of partitions.
C. Create a view to aggregate the base table data. Include a filter clause to specify the last year of partitions.
D. Create a new table that aggregates the base table data. Include a filter clause to specify the last year of partitions. Set up a scheduled query to recreate the new table every hour.
Show Answer
Correct Answer: A
Explanation: A materialized view is the best fit for repeated aggregation queries over a large partitioned table. It precomputes and incrementally maintains aggregates, reducing query cost, latency, and maintenance. BigQuery materialized views also return fresh results by combining materialized data with recent base-table changes when needed. Filtering the materialized view to the last year matches the query pattern while retaining the full historical data in the base table. Option B deletes historical data, violating the retention requirement. Option C does not reduce computation because a standard view is recomputed on every query. Option D adds maintenance overhead and can be stale for up to an hour.
Question 51
You are preparing an organization-wide dataset. You need to preprocess customer data stored in a restricted bucket in Cloud Storage. The data will be used to create consumer analyses. You need to comply with data privacy requirements.
What should you do?
A. Use Dataflow and the Cloud Data Loss Prevention API to mask sensitive data. Write the processed data in BigQuery.
B. Use customer-managed encryption keys (CMEK) to directly encrypt the data in Cloud Storage. Use federated queries from BigQuery. Share the encryption key by following the principle of least privilege.
C. Use the Cloud Data Loss Prevention API and Dataflow to detect and remove sensitive fields from the data in Cloud Storage. Write the filtered data in BigQuery.
D. Use Dataflow and Cloud KMS to encrypt sensitive fields and write the encrypted data in BigQuery. Share the encryption key by following the principle of least privilege.
Show Answer
Correct Answer: A
Explanation: The goal is to preprocess customer data for organization-wide analytics while complying with data privacy requirements. Cloud DLP integrated with Dataflow is the recommended approach to de-identify sensitive information before loading it into BigQuery. Masking/tokenization preserves analytical utility while protecting PII. Option C removes sensitive fields entirely, which can unnecessarily reduce the usefulness of the dataset for consumer analysis. Options B and D focus on encryption, which protects data at rest or in transit but does not de-identify data for broader analytical use; Cloud Storage is already encrypted by default, and encrypted fields are not readily usable for analysis.
Question 52
You need to connect multiple applications with dynamic public IP addresses to a Cloud SQL instance. You configured users with strong passwords and enforced the SSL connection to your Cloud SQL instance. You want to use Cloud SQL public IP and ensure that you have secured connections. What should you do?
A. Add CIDR 0.0.0.0/0 network to Authorized Network. Use Identity and Access Management (IAM) to add users.
B. Add all application networks to Authorized Network and regularly update them.
C. Leave the Authorized Network empty. Use Cloud SQL Auth proxy on all applications.
D. Add CIDR 0.0.0.0/0 network to Authorized Network. Use Cloud SQL Auth proxy on all applications.
Show Answer
Correct Answer: C
Explanation: The Cloud SQL Auth Proxy (or Cloud SQL connectors) is the recommended way to securely connect to a Cloud SQL instance that uses a public IP, especially when clients have dynamic public IP addresses. The proxy authenticates using IAM and establishes an encrypted connection, eliminating the need to maintain Authorized Networks for changing client IPs. Adding 0.0.0.0/0 is not a secure practice, and manually maintaining Authorized Networks is impractical for dynamic IPs. Leaving Authorized Networks empty prevents direct IP-based access while still allowing connections through the Cloud SQL Auth Proxy.
Question 53
You are migrating a large number of files from a public HTTPS endpoint to Cloud Storage. The files are protected from unauthorized access using signed URLs. You created a TSV file that contains the list of object URLs and started a transfer job by using Storage Transfer Service. You notice that the job has run for a long time and eventually failed. Checking the logs of the transfer job reveals that the job was running fine until one point, and then it failed due to HTTP 403 errors on the remaining files. You verified that there were no changes to the source system. You need to fix the problem to resume the migration process. What should you do?
A. Set up Cloud Storage FUSE, and mount the Cloud Storage bucket on a Compute Engine instance. Remove the completed files from the TSV file. Use a shell script to iterate through the TSV file and download the remaining URLs to the FUSE mount point.
B. Renew the TLS certificate of the HTTPS endpoint. Remove the completed files from the TSV file and rerun the Storage Transfer Service job.
C. Create a new TSV file for the remaining files by generating signed URLs with a longer validity period. Split the TSV file into multiple smaller files and submit them as separate Storage Transfer Service jobs in parallel.
D. Update the file checksums in the TSV file from using MD5 to SHA256. Remove the completed files from the TSV file and rerun the Storage Transfer Service job.
Show Answer
Correct Answer: C
Explanation: HTTP 403 errors occurring after the transfer ran successfully for some time strongly indicate that the signed URLs expired during the long-running job. Storage Transfer Service relies on those URLs remaining valid for the duration of the transfer. Regenerating signed URLs with a longer expiration for the remaining objects resolves the authorization issue. Splitting the remaining transfers into smaller parallel jobs helps ensure completion before the URLs expire again. The other options do not address expired signed URLs: renewing the TLS certificate is unrelated to HTTP 403 authorization failures, changing checksums does not affect access, and manually downloading via FUSE is unnecessary and not the recommended fix.
Question 54
You currently have transactional data stored on-premises in a PostgreSQL database. To modernize your data environment, you want to run transactional workloads and support analytics needs with a single database. You need to move to Google Cloud without changing database management systems, and minimize cost and complexity. What should you do?
A. Migrate and modernize your database with Cloud Spanner.
B. Migrate your workloads to AlloyDB for PostgreSQL.
C. Migrate to BigQuery to optimize analytics.
D. Migrate your PostgreSQL database to Cloud SQL for PostgreSQL.
Show Answer
Correct Answer: B
Explanation: AlloyDB for PostgreSQL is the Google Cloud service designed to keep PostgreSQL compatibility while supporting both high-performance transactional workloads and analytics in a single database through HTAP-oriented features such as its columnar engine. Cloud SQL preserves PostgreSQL at lower cost but is not the platform positioned for combined transactional and analytical workloads in one database. Cloud Spanner changes the database system, and BigQuery is an analytics warehouse rather than a transactional database.
Question 55
You migrated your on-premises Apache Hadoop Distributed File System (HDFS) data lake to Cloud Storage. The data scientist team needs to process the data by using Apache Spark and SQL. Security policies need to be enforced at the column level. You need a cost-effective solution that can scale into a data mesh. What should you do?
A. 1. Deploy a long-living Dataproc cluster with Apache Hive and Ranger enabled. 2. Configure Ranger for column level security. 3. Process with Dataproc Spark or Hive SQL.
B. 1. Define a BigLake table. 2. Create a taxonomy of policy tags in Data Catalog. 3. Add policy tags to columns. 4. Process with the Spark-BigQuery connector or BigQuery SQL.
C. 1. Load the data to BigQuery tables. 2. Create a taxonomy of policy tags in Data Catalog. 3. Add policy tags to columns. 4. Process with the Spark-BigQuery connector or BigQuery SQL.
D. 1. Apply an Identity and Access Management (IAM) policy at the file level in Cloud Storage. 2. Define a BigQuery external table for SQL processing. 3. Use Dataproc Spark to process the Cloud Storage files.
Show Answer
Correct Answer: B
Explanation: BigLake lets you keep the data in Cloud Storage while exposing it as governed tables for both BigQuery SQL and Apache Spark via the Spark-BigQuery connector. Column-level security is implemented using Data Catalog policy tags (taxonomy + column tags). This is serverless, avoids loading data into BigQuery unnecessarily, is more cost-effective than maintaining long-running Dataproc clusters, and aligns with scalable data mesh architectures.
Question 56
You are designing the architecture to process your data from Cloud Storage to BigQuery by using Dataflow. The network team provided you with the Shared VPC network and subnetwork to be used by your pipelines. You need to enable the deployment of the pipeline on the Shared VPC network. What should you do?
A. Assign the compute.networkUser role to the Dataflow service agent.
B. Assign the compute.networkUser role to the service account that executes the Dataflow pipeline.
C. Assign the dataflow.admin role to the Dataflow service agent.
D. Assign the dataflow.admin role to the service account that executes the Dataflow pipeline.
Show Answer
Correct Answer: B
Explanation: For Dataflow jobs running on a Shared VPC, the service account that executes the Dataflow pipeline (the worker service account/Dataflow service account used by the job) must have the Compute Network User (roles/compute.networkUser) role on the Shared VPC subnetwork in the host project. The Dataflow admin role is unrelated to Shared VPC network attachment permissions.
Sources:
https://cloud.google.com/dataflow/docs/guides/specifying-networks
Question 57
You work for a large ecommerce company. You are using Pub/Sub to ingest the clickstream data to Google Cloud for analytics. You observe that when a new subscriber connects to an existing topic to analyze data, they are unable to subscribe to older data. For an upcoming yearly sale event in two months, you need a solution that, once implemented, will enable any new subscriber to read the last 30 days of data. What should you do?
A. Create a new topic, and publish the last 30 days of data each time a new subscriber connects to an existing topic.
B. Set the topic retention policy to 30 days.
C. Set the subscriber retention policy to 30 days.
D. Ask the source system to re-push the data to Pub/Sub, and subscribe to it.
Show Answer
Correct Answer: B
Explanation: A new subscription can only replay historical messages if the topic retains them. Topic message retention stores published messages for the configured period (up to 31 days), allowing newly created subscriptions to seek back and consume retained messages. Subscription retention applies to an existing subscription's state and does not enable brand-new subscribers to read older messages. Republishing or asking the source to resend data is unnecessary.
Question 58
Your organization is modernizing their IT services and migrating to Google Cloud. You need to organize the data that will be stored in Cloud Storage and BigQuery. You need to enable a data mesh approach to share the data between sales, product design, and marketing departments. What should you do?
A. 1. Create a project for storage of the data for each of your departments. 2. Enable each department to create Cloud Storage buckets and BigQuery datasets. 3. Create user groups for authorized readers for each bucket and dataset. 4. Enable the IT team to administer the user groups to add or remove users as the departments’ request.
B. 1. Create multiple projects for storage of the data for each of your departments’ applications. 2. Enable each department to create Cloud Storage buckets and BigQuery datasets. 3. Publish the data that each department shared in Analytics Hub. 4. Enable all departments to discover and subscribe to the data they need in Analytics Hub.
C. 1. Create a project for storage of the data for your organization. 2. Create a central Cloud Storage bucket with three folders to store the files for each department. 3. Create a central BigQuery dataset with tables prefixed with the department name. 4. Give viewer rights for the storage project for the users of your departments.
D. 1. Create multiple projects for storage of the data for each of your departments’ applications. 2. Enable each department to create Cloud Storage buckets and BigQuery datasets. 3. In Dataplex, map each department to a data lake and the Cloud Storage buckets, and map the BigQuery datasets to zones. 4. Enable each department to own and share the data of their data lakes.
Show Answer
Correct Answer: D
Explanation: A data mesh emphasizes decentralized domain ownership with governed self-service access. Dataplex is Google's data management and governance service designed to support data mesh across Cloud Storage and BigQuery by organizing data into lakes and zones while allowing domains to own and share their data. Analytics Hub is primarily for sharing BigQuery data, not Cloud Storage assets, so it does not fully address the requirement covering both Cloud Storage and BigQuery.
Question 59
You have terabytes of customer behavioral data streaming from Google Analytics into BigQuery daily. Your customers’ information, such as their preferences, is hosted on a Cloud SQL for MySQL database. Your CRM database is hosted on a Cloud SQL for PostgreSQL instance. The marketing team wants to use your customers’ information from the two databases and the customer behavioral data to create marketing campaigns for yearly active customers. You need to ensure that the marketing team can run the campaigns over 100 times a day on typical days and up to 300 during sales. At the same time, you want to keep the load on the Cloud SQL databases to a minimum. What should you do?
A. Create BigQuery connections to both Cloud SQL databases. Use BigQuery federated queries on the two databases and the Google Analytics data on BigQuery to run these queries.
B. Create a job on Apache Spark with Dataproc Serverless to query both Cloud SQL databases and the Google Analytics data on BigQuery for these queries.
C. Create streams in Datastream to replicate the required tables from both Cloud SQL databases to BigQuery for these queries.
D. Create a Dataproc cluster with Trino to establish connections to both Cloud SQL databases and BigQuery, to execute the queries.
Show Answer
Correct Answer: C
Explanation: Replicate the required Cloud SQL tables into BigQuery using Datastream (CDC). This keeps analytical queries off the Cloud SQL instances, minimizing database load while enabling frequent, scalable joins with the existing Google Analytics data already in BigQuery. Federated queries would execute against Cloud SQL for each campaign run, increasing load and latency, while Dataproc-based solutions add unnecessary complexity and do not address the requirement to minimize Cloud SQL load.
$19
Get all 332 questions with detailed answers and explanations
Instant download HTML + PDF delivered the moment payment clears.
Secure Stripe checkout we never see or store your card details.
7-day refund if files are defective see our refund policy.