PDE-2022-3

Your company is in the process of migrating its on-premises data warehousing solutions to BigQuery. The existing data warehouse uses triggerbased change data capture (CDC) to apply updates from multiple transactional database sources on a daily basis. With BigQuery, your company hopes to improve its handling of CDC so that changes to the source systems are available to query in BigQuery in near-real time using log-based CDC streams, while also optimizing for the performance of applying changes to the data warehouse. Which two steps should they take to ensure that changes are available in the BigQuery reporting table with minimal latency while reducing compute overhead? (Choose two.)

• Perform a DML INSERT, UPDATE, or DELETE to replicate each individual CDC record in real time directly on the reporting table.

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

• Periodically DELETE outdated records from the reporting table.

• Periodically use a DML MERGE to perform several DML INSERT, UPDATE, and DELETE operations at the same time on the reporting table.

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

You are designing a data processing pipeline. The pipeline must be able to scale automatically as load increases. Messages must be processed at least once and must be ordered within windows of 1 hour. How should you design the solution?

• Use Apache Kafka for message ingestion and use Cloud Dataproc for streaming analysis.

• Use Apache Kafka for message ingestion and use Cloud Dataflow for streaming analysis.

• Use Cloud Pub/Sub for message ingestion and Cloud Dataproc for streaming analysis.

• Use Cloud Pub/Sub for message ingestion and Cloud Dataflow for streaming analysis

You need to set access to BigQuery for different departments within your company. Your solution should comply with the following requirements:

✑ Each department should have access only to their data.

✑ Each department will have one or more leads who need to be able to create and update tables and provide them to their team.

✑ Each department has data analysts who need to be able to query but not modify data.

How should you set access to the data in BigQuery?

• Create a dataset for each department. Assign the department leads the role of OWNER, and assign the data analysts the role of WRITER on their dataset.

• Create a dataset for each department. Assign the department leads the role of WRITER, and assign the data analysts the role of READER on their dataset.

• Create a table for each department. Assign the department leads the role of Owner, and assign the data analysts the role of Editor on the project the table is in.

• Create a table for each department. Assign the department leads the role of Editor, and assign the data analysts the role of Viewer on the project the table is in.

You operate a database that stores stock trades and an application that retrieves average stock price for a given company over an adjustable window of time. The data is stored in Cloud Bigtable where the datetime of the stock trade is the beginning of the row key. Your application has thousands of concurrent users, and you notice that performance is starting to degrade as more stocks are added. What should you do to improve the performance of your application?

• Change the row key syntax in your Cloud Bigtable table to begin with the stock symbol.

• Change the row key syntax in your Cloud Bigtable table to begin with a random number per second.

• Change the data pipeline to use BigQuery for storing stock trades, and update your application.

• Use Cloud Dataflow to write a summary of each day's stock trades to an Avro file on Cloud Storage. Update your application to read from Cloud Storage and Cloud Bigtable to compute the responses.

You are operating a Cloud Dataflow streaming pipeline. The pipeline aggregates events from a Cloud Pub/Sub subscription source, within a window, and sinks the resulting aggregation to a Cloud Storage bucket. The source has consistent throughput. You want to monitor an alert on behavior of the pipeline with Cloud Stackdriver to ensure that it is processing data. Which Stackdriver alerts should you create?

• An alert based on a decrease of subscription/num_undelivered_messages for the source and a rate of change increase of instance/storage/ used_bytes for the destination

• An alert based on an increase of subscription/num_undelivered_messages for the source and a rate of change decrease of instance/storage/ used_bytes for the destination

• An alert based on a decrease of instance/storage/used_bytes for the source and a rate of change increase of subscription/ num_undelivered_messages for the destination

• An alert based on an increase of instance/storage/used_bytes for the source and a rate of change decrease of subscription/ num_undelivered_messages for the destination

You currently have a single on-premises Kafka cluster in a data center in the us-east region that is responsible for ingesting messages from IoT devices globally. Because large parts of globe have poor internet connectivity, messages sometimes batch at the edge, come in all at once, and cause a spike in load on your Kafka cluster. This is becoming difficult to manage and prohibitively expensive. What is the Google-recommended cloud native architecture for this scenario?

• Edge TPUs as sensor devices for storing and transmitting the messages.

• Cloud Dataflow connected to the Kafka cluster to scale the processing of incoming messages.

• An IoT gateway connected to Cloud Pub/Sub, with Cloud Dataflow to read and process the messages from Cloud Pub/Sub.

• A Kafka cluster virtualized on Compute Engine in us-east with Cloud Load Balancing to connect to the devices around the world.

You decided to use Cloud Datastore to ingest vehicle telemetry data in real time. You want to build a storage system that will account for the longterm data growth, while keeping the costs low. You also want to create snapshots of the data periodically, so that you can make a point-in-time (PIT) recovery, or clone a copy of the data for Cloud Datastore in a different environment. You want to archive these snapshots for a long time. Which two methods can accomplish this? (Choose two.)

• Use managed export, and store the data in a Cloud Storage bucket using Nearline or Coldline class.

• Use managed export, and then import to Cloud Datastore in a separate project under a unique namespace reserved for that export.

• Use managed export, and then import the data into a BigQuery table created just for that export, and delete temporary export files

• Write an application that uses Cloud Datastore client libraries to read all the entities. Treat each entity as a BigQuery table row via BigQuery streaming insert. Assign an export timestamp for each export, and attach it as an extra column for each row. Make sure that the BigQuery table is partitioned using the export timestamp column.

• Write an application that uses Cloud Datastore client libraries to read all the entities. Format the exported data into a JSON file. Apply compression before storing the data in Cloud Source Repositories.

You need to create a data pipeline that copies time-series transaction data so that it can be queried from within BigQuery by your data science team for analysis. Every hour, thousands of transactions are updated with a new status. The size of the initial dataset is 1.5 PB, and it will grow by 3 TB per day. The data is heavily structured, and your data science team will build machine learning models based on this data. You want to maximize performance and usability for your data science team. Which two strategies should you adopt? (Choose two.)

• Denormalize the data as must as possible.

• Preserve the structure of the data as much as possible.

• Use BigQuery UPDATE to further reduce the size of the dataset.

• Develop a data pipeline where status updates are appended to BigQuery instead of updated.

• Copy a daily snapshot of transaction data to Cloud Storage and store it as an Avro file. Use BigQuery's support for external data sources to query.

You are designing a cloud-native historical data processing system to meet the following conditions:

✑ The data being analyzed is in CSV, Avro, and PDF formats and will be accessed by multiple analysis tools including Dataproc, BigQuery, and Compute Engine.

✑ A batch pipeline moves daily data.

✑ Performance is not a factor in the solution.

✑ The solution design should maximize availability.

How should you design data storage for this solution?

• Create a Dataproc cluster with high availability. Store the data in HDFS, and perform analysis as needed.

• Store the data in BigQuery. Access the data using the BigQuery Connector on Dataproc and Compute Engine.

• Store the data in a regional Cloud Storage bucket. Access the bucket directly using Dataproc, BigQuery, and Compute Engine.

• Store the data in a multi-regional Cloud Storage bucket. Access the data directly using Dataproc, BigQuery, and Compute Engine.

You have a petabyte of analytics data and need to design a storage and processing platform for it. You must be able to perform data warehousestyle analytics on the data in Google Cloud and expose the dataset as files for batch analysis tools in other cloud providers. What should you do?

• Store and process the entire dataset in BigQuery.

• Store and process the entire dataset in Bigtable.

• Store the full dataset in BigQuery, and store a compressed copy of the data in a Cloud Storage bucket.

• Store the warm data as files in Cloud Storage, and store the active data in BigQuery. Keep this ratio as 80% warm and 20% active.