Google Professional-Data-Engineer - Google Professional Data Engineer Exam

Google Professional-Data-Engineer Premium Access Download Demo

Page: 1 / 7
Total 376 questions

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?

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

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

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

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

Question # 2

You are using Cloud Bigtable to persist and serve stock market data for each of the major indices. To serve the trading application, you need to access only the most recent stock prices that are streaming in How should you design your row key and tables to ensure that you can access the data with the most simple query?

Create one unique table for all of the indices, and then use the index and timestamp as the row key design

Create one unique table for all of the indices, and then use a reverse timestamp as the row key design.

For each index, have a separate table and use a timestamp as the row key design

For each index, have a separate table and use a reverse timestamp as the row key design

Question # 3

You are building an application to share financial market data with consumers, who will receive data feeds. Data is collected from the markets in real time. Consumers will receive the data in the following ways:

Real-time event stream

ANSI SQL access to real-time stream and historical data

Batch historical exports

Which solution should you use?

Cloud Dataflow, Cloud SQL, Cloud Spanner

Cloud Pub/Sub, Cloud Storage, BigQuery

Cloud Dataproc, Cloud Dataflow, BigQuery

Cloud Pub/Sub, Cloud Dataproc, Cloud SQL

Question # 4

You are designing storage for very large text files for a data pipeline on Google Cloud. You want to support ANSI SQL queries. You also want to support compression and parallel load from the input locations using Google recommended practices. What should you do?

Transform text files to compressed Avro using Cloud Dataflow. Use BigQuery for storage and query.

Transform text files to compressed Avro using Cloud Dataflow. Use Cloud Storage and BigQuery

permanent linked tables for query.

Compress text files to gzip using the Grid Computing Tools. Use BigQuery for storage and query.

Compress text files to gzip using the Grid Computing Tools. Use Cloud Storage, and then import into

Cloud Bigtable for query.

Question # 5

You have several Spark jobs that run on a Cloud Dataproc cluster on a schedule. Some of the jobs run in sequence, and some of the jobs run concurrently. You need to automate this process. What should you do?

Create a Cloud Dataproc Workflow Template

Create an initialization action to execute the jobs

Create a Directed Acyclic Graph in Cloud Composer

Create a Bash script that uses the Cloud SDK to create a cluster, execute jobs, and then tear down the cluster

Question # 6

Your analytics team wants to build a simple statistical model to determine which customers are most likely to work with your company again, based on a few different metrics. They want to run the model on Apache Spark, using data housed in Google Cloud Storage, and you have recommended using Google Cloud Dataproc to execute this job. Testing has shown that this workload can run in approximately 30 minutes on a 15-node cluster, outputting the results into Google BigQuery. The plan is to run this workload weekly. How should you optimize the cluster for cost?

Migrate the workload to Google Cloud Dataflow

Use pre-emptible virtual machines (VMs) for the cluster

Use a higher-memory node so that the job runs faster

Use SSDs on the worker nodes so that the job can run faster

Question # 7

A shipping company has live package-tracking data that is sent to an Apache Kafka stream in real time. This is then loaded into BigQuery. Analysts in your company want to query the tracking data in BigQuery to analyze geospatial trends in the lifecycle of a package. The table was originally created with ingest-date partitioning. Over time, the query processing time has increased. You need to implement a change that would improve query performance in BigQuery. What should you do?

Implement clustering in BigQuery on the ingest date column.

Implement clustering in BigQuery on the package-tracking ID column.

Tier older data onto Cloud Storage files, and leverage extended tables.

Re-create the table using data partitioning on the package delivery date.

Question # 8

You have Cloud Functions written in Node.js that pull messages from Cloud Pub/Sub and send the data to BigQuery. You observe that the message processing rate on the Pub/Sub topic is orders of magnitude higher than anticipated, but there is no error logged in Stackdriver Log Viewer. What are the two most likely causes of this problem? Choose 2 answers.

Publisher throughput quota is too small.

Total outstanding messages exceed the 10-MB maximum.

Error handling in the subscriber code is not handling run-time errors properly.

The subscriber code cannot keep up with the messages.

The subscriber code does not acknowledge the messages that it pulls.

Question # 9

You created an analytics environment on Google Cloud so that your data scientist team can explore data without impacting the on-premises Apache Hadoop solution. The data in the on-premises Hadoop Distributed File System (HDFS) cluster is in Optimized Row Columnar (ORC) formatted files with multiple columns of Hive partitioning. The data scientist team needs to be able to explore the data in a similar way as they used the on-premises HDFS cluster with SQL on the Hive query engine. You need to choose the most cost-effective storage and processing solution. What should you do?

Import the ORC files lo Bigtable tables for the data scientist team.

Import the ORC files to BigOuery tables for the data scientist team.

Copy the ORC files on Cloud Storage, then deploy a Dataproc cluster for the data scientist team.

Copy the ORC files on Cloud Storage, then create external BigQuery tables for the data scientist team.

Explanation:

The requirements are:

Explore ORC formatted files with Hive partitioning.

Mimic the SQL on Hive query engine experience.

Cost-effective storage and processing.

Avoid impacting the on-premises Hadoop solution.

Let's analyze the options:

Option A (Import to Bigtable):Bigtable is a NoSQL database, not suited for SQL-based exploration of ORC files or Hive-style partitioning directly. This would require significant data transformation and a different query paradigm. Not cost-effective for this use case.

Option B (Import to BigQuery native tables):Importing data into BigQuery native storage is an option. BigQuery can load ORC files. This provides excellent query performance. However, it involves an ETL step (importing) and storage costs for the datawithin BigQuery, which might be higher than keeping it in its original format on Cloud Storage if query patterns are exploratory and not extremely frequent on all data.

Option C (Copy to Cloud Storage, deploy Dataproc):Dataproc allows you to run Hadoop/Spark (and thus Hive) clusters on Google Cloud. This would provide a very similar experience ("SQL on the Hive query engine"). However, running a persistent Dataproc cluster incurs compute costs for the cluster nodes, even when not actively querying. While ephemeral clusters are possible, it adds operational overhead for exploratory queries. Storage on Cloud Storage is cost-effective.

Option D (Copy to Cloud Storage, create external BigQuery tables):This is often the most cost-effective and straightforward solution for this scenario.

Cost-effective Storage:Cloud Storage is a low-cost option for storing files like ORC.

SQL Interface:BigQuery provides a familiar SQL interface.

External Tables:BigQuery can query data directly from Cloud Storage (including ORC files) using external tables. This avoids the need to load data into BigQuery's managed storage, saving on storage costs and ETL effort.

Hive Partitioning:BigQuery external tables support Hive partitioning layouts. When you define the external table, you can specify the partitioning scheme, and BigQuery will use partition pruning to scan only relevant partitions, improving performance and reducing costs for queries that filter on partition keys. This directly mimics the Hive experience.

Processing Cost:You only pay for the data scanned by BigQuery queries, which aligns with exploratory analysis.

Comparing D with B: External tables are generally more cost-effective for storage and initial setup if the data is already in ORC and an ETL process into BigQuery native storage is to be avoided. Query performance might be slightly less than native tables but is often sufficient for exploration, especially with partitioning. Comparing D with C: BigQuery external tables are serverless, meaning no cluster to manage or pay for when idle. Dataproc requires managing and paying for a cluster. For exploration, the serverless nature of BigQuery is usually more cost-effective.

Therefore, copying ORC files to Cloud Storage and using BigQuery external tables is the most cost-effective solution that meets all requirements.

[Reference:, Google Cloud Documentation: BigQuery > External data sources > Querying Cloud Storage data. "You can query data in Cloud Storage by using external tables or federated queries. External tables are tables that read data directly from files in Cloud Storage.", Google Cloud Documentation: BigQuery > External data sources > Supported formats and compression types. ORC is a supported format., Google Cloud Documentation: BigQuery > Creating and using tables > Creating external tables. "External tables let you query data stored in Cloud Storage as if it were a standardBigQuery table. You can use external tables to query data in various formats, including... ORC...", Google Cloud Documentation: BigQuery > Creating and using tables > Querying partitioned external tables. "You can create an external table that is partitioned on Hive partitioning keys. When you query a Hive partitioned external table, BigQuery performs partition pruning to skip reading unnecessary partitions." This directly addresses the "Hive partitioning" and "explore data in a similar way" requirements., Google Cloud Blog: "Choosing the right data processing option on GCP: BigQuery vs. Dataproc" (and similar articles) often highlight BigQuery external tables as a cost-effective way to query data in place on Cloud Storage, especially for data lake scenarios., , , ]

Question # 10

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 Cloud Dataproc, BigQuery, and Compute Engine.

A streaming data pipeline stores new data daily.

Peformance is not a factor in the solution.

The solution design should maximize availability.

How should you design data storage for this solution?

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

Store the data in BigQuery. Access the data using the BigQuery Connector or Cloud Dataproc and Compute Engine.

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

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

Summer Sale Limited Time 65% Discount Offer - Ends in 0d 00h 00m 00s - Coupon code: ecus65

Google Professional-Data-Engineer - Google Professional Data Engineer Exam

The Answer Is:

Explanation:

The Answer Is:

The Answer Is:

The Answer Is:

The Answer Is:

Explanation:

The Answer Is:

The Answer Is:

The Answer Is:

The Answer Is:

Explanation:

The Answer Is: