Google Google Certifications PROFESSIONAL-DATA-ENGINEER Questions & Answers

• Question 101:

  You work for a global shipping company. You want to train a model on 40 TB of data to predict which ships in each geographic region are likely to cause delivery delays on any given day. The model will be based on multiple attributes collected from multiple sources. Telemetry data, including location in GeoJSON format, will be pulled from each ship and loaded every hour. You want to have a dashboard that shows how many and which ships are likely to cause delays within a region. You want to use a storage solution that has native functionality for prediction and geospatial processing. Which storage solution should you use?

  A. BigQuery

  B. Cloud Bigtable

  C. Cloud Datastore

  D. Cloud SQL for PostgreSQL

  Correct Answer: A

• Question 102:

  Your company is migrating its on-premises data warehousing solution to BigQuery. The existing data warehouse uses trigger-based change data capture (CDC) to apply daily updates from transactional database sources Your company wants to use BigQuery to improve its handling of CDC and to optimize the performance of the data warehouse Source system changes must be available for query m near-real time using tog-based CDC streams You need to ensure that changes in the BigQuery reporting table are available with minimal latency and reduced overhead. What should you do? Choose 2 answers

  A. Perform a DML INSERT UPDATE, or DELETE to replicate each CDC record in the reporting table m real time.

  B. Periodically DELETE outdated records from the reporting table Periodically use a DML MERGE to simultaneously perform DML INSERT. UPDATE, and DELETE operations in the reporting table

  C. Insert each new CDC record and corresponding operation type into a staging table in real time

  D. Insert each new CDC record and corresponding operation type into the reporting table in real time and use a materialized view to expose only the current version of each unique record.

  Correct Answer: BD

• Question 103:

  You are designing a data warehouse in BigQuery to analyze sales data for a telecommunication service provider. You need to create a data model for customers, products, and subscriptions All customers, products, and subscriptions can be updated monthly, but you must maintain a historical record of all data. You plan to use the visualization layer for current and historical reporting. You need to ensure that the data model is simple, easy-to-use. and cost-effective. What should you do?

  A. Create a normalized model with tables for each entity. Use snapshots before updates to track historical data

  B. Create a normalized model with tables for each entity. Keep all input files in a Cloud Storage bucket to track historical data

  C. Create a denormalized model with nested and repeated fields Update the table and use snapshots to track historical data

  D. Create a denormalized, append-only model with nested and repeated fields Use the ingestion timestamp to track historical data.

  Correct Answer: D

  A denormalized, append-only model simplifies query complexity by eliminating the need for joins.

  Adding data with an ingestion timestamp allows for easy retrieval of both current and historical states.

  Instead of updating records, new records are appended, which maintains historical information without the need to create separate snapshots.

• Question 104:

  You need to copy millions of sensitive patient records from a relational database to BigQuery. The total size of the database is 10 TB. You need to design a solution that is secure and time-efficient. What should you do?

  A. Export the records from the database as an Avro file. Upload the file to GCS using gsutil, and then load the Avro file into BigQuery using the BigQuery web UI in the GCP Console.

  B. Export the records from the database as an Avro file. Copy the file onto a Transfer Appliance and send it to Google, and then load the Avro file into BigQuery using the BigQuery web UI in the GCP Console.

  C. Export the records from the database into a CSV file. Create a public URL for the CSV file, and then use Storage Transfer Service to move the file to Cloud Storage. Load the CSV file into BigQuery using the BigQuery web UI in the GCP Console.

  D. Export the records from the database as an Avro file. Create a public URL for the Avro file, and then use Storage Transfer Service to move the file to Cloud Storage. Load the Avro file into BigQuery using the BigQuery web UI in the GCP Console.

  Correct Answer: A

• Question 105:

  You have data pipelines running on BigQuery, Cloud Dataflow, and Cloud Dataproc. You need to perform health checks and monitor their behavior, and then notify the team managing the pipelines if they fail. You also need to be able to work across multiple projects. Your preference is to use managed products of features of the platform. What should you do?

  A. Export the information to Cloud Stackdriver, and set up an Alerting policy

  B. Run a Virtual Machine in Compute Engine with Airflow, and export the information to Stackdriver

  C. Export the logs to BigQuery, and set up App Engine to read that information and send emails if you find a failure in the logs

  D. Develop an App Engine application to consume logs using GCP API calls, and send emails if you find a failure in the logs

  Correct Answer: B

• Question 106:

  Your organization has been collecting and analyzing data in Google BigQuery for 6 months. The majority of the data analyzed is placed in a time-partitioned table named events_partitioned. To reduce the cost of queries, your organization created a view called events, which queries only the last 14 days of data. The view is described in legacy SQL. Next month, existing applications will be connecting to BigQuery to read the events data via an ODBC connection. You need to ensure the applications can connect. Which two actions should you take? (Choose two.)

  A. Create a new view over events using standard SQL

  B. Create a new partitioned table using a standard SQL query

  C. Create a new view over events_partitioned using standard SQL

  D. Create a service account for the ODBC connection to use for authentication

  E. Create a Google Cloud Identity and Access Management (Cloud IAM) role for the ODBC connection and shared "events"

  Correct Answer: AE

• Question 107:

  Your infrastructure includes a set of YouTube channels. You have been tasked with creating a process for sending the YouTube channel data to Google Cloud for analysis. You want to design a solution that allows your world-wide marketing teams to perform ANSI SQL and other types of analysis on up-to-date YouTube channels log data. How should you set up the log data transfer into Google Cloud?

  A. Use Storage Transfer Service to transfer the offsite backup files to a Cloud Storage Multi-Regional storage bucket as a final destination.

  B. Use Storage Transfer Service to transfer the offsite backup files to a Cloud Storage Regional bucket as a final destination.

  C. Use BigQuery Data Transfer Service to transfer the offsite backup files to a Cloud Storage Multi-Regional storage bucket as a final destination.

  D. Use BigQuery Data Transfer Service to transfer the offsite backup files to a Cloud Storage Regional storage bucket as a final destination.

  Correct Answer: B

• Question 108:

  You need (o give new website users a globally unique identifier (GUID) using a service that takes in data points and returns a GUID This data is sourced from both internal and external systems via HTTP calls that you will make via microservices within your pipeline There will be tens of thousands of messages per second and that can be multithreaded, and you worry about the backpressure on the system How should you design your pipeline to minimize that backpressure?

  A. Call out to the service via HTTP

  B. Create the pipeline statically in the class definition

  C. Create a new object in the startBundle method of DoFn

  D. Batch the job into ten-second increments

  Correct Answer: A

• Question 109:

  You have a requirement to insert minute-resolution data from 50,000 sensors into a BigQuery table. You expect significant growth in data volume and need the data to be available within 1 minute of ingestion for real-time analysis of aggregated trends. What should you do?

  A. Use bq load to load a batch of sensor data every 60 seconds.

  B. Use a Cloud Dataflow pipeline to stream data into the BigQuery table.

  C. Use the INSERT statement to insert a batch of data every 60 seconds.

  D. Use the MERGE statement to apply updates in batch every 60 seconds.

  Correct Answer: B

• Question 110:

  You are designing a fault-tolerant architecture to store data in a regional BigOuery dataset. You need to ensure that your application is able to recover from a corruption event in your tables that occurred within the past seven days. You want to adopt managed services with the lowest RPO and most cost-effective solution. What should you do?

  A. Export the data from BigQuery into a new table that excludes the corrupted data.

  B. Migrate your data to multi-region BigQuery buckets.

  C. Access historical data by using time travel in BigQuery.

  D. Create a BigQuery table snapshot on a daily basis.

  Correct Answer: C

  Time travel is a feature of BigQuery that allows you to query and recover data from any point within the past seven days. You can use the FOR SYSTEM_TIME AS OF clause in your SQL query to specify the timestamp of the data you want to

  access. This way, you can restore your tables to a previous state before the corruption event occurred. Time travel is automatically enabled for all datasets and does not incur any additional cost or storage.

  References:

  Data retention with time travel and fail-safe | BigQuery | Google Cloud BigQuery Time Travel: How to access Historical Data? | Easy Steps