Professional-Data-Engineer Google Professional Data Engineer Exam Questions and Answers

The CSV data loaded in BigQuery is not flagged as CSV.

The CSV data has invalid rows that were skipped on import.

The CSV data loaded in BigQuery is not using BigQuery’s default encoding.

The CSV data has not gone through an ETL phase before loading into BigQuery.

Rewrite the job in Pig.

Rewrite the job in Apache Spark.

Increase the size of the Hadoop cluster.

Decrease the size of the Hadoop cluster but also rewrite the job in Hive.

Ensure all the tables are included in global dataset.

Ensure each table is included in a dataset for a region.

Adjust the settings for each table to allow a related region-based security group view access.

Adjust the settings for each view to allow a related region-based security group view access.

Adjust the settings for each dataset to allow a related region-based security group view access.

Rowkey: date#device_idColumn data: data_point

Rowkey: dateColumn data: device_id, data_point

Rowkey: device_idColumn data: date, data_point

Rowkey: data_pointColumn data: device_id, date

Rowkey: date#data_pointColumn data: device_id

Load the data into Google Sheets, use formulas to calculate a metric, and use filters/sorting to show only suboptimal links in a table.

Load the data into Google BigQuery tables, write Google Apps Script that queries the data, calculates the metric, and shows only suboptimal rows in a table in Google Sheets.

Load the data into Google Cloud Datastore tables, write a Google App Engine Application that queries all rows, applies a function to derive the metric, and then renders results in a table using the Google charts and visualization API.

Load the data into Google BigQuery tables, write a Google Data Studio 360 report that connects to your data, calculates a metric, and then uses a filter expression to show only suboptimal rows in a table.

Create a table called tracking_table and include a DATE column.

Create a partitioned table called tracking_table and include a TIMESTAMP column.

Create sharded tables for each day following the pattern tracking_table_YYYYMMDD.

Create a table called tracking_table with a TIMESTAMP column to represent the day.

Cloud Datastore and Cloud Bigtable

Cloud Bigtable and Cloud SQL

BigQuery and Cloud Bigtable

BigQuery and Cloud Storage

Look through the current data and compose a series of charts and tables, one for each possible

combination of criteria.

Look through the current data and compose a small set of generalized charts and tables bound to criteria filters that allow value selection.

Export the data to a spreadsheet, compose a series of charts and tables, one for each possible

combination of criteria, and spread them across multiple tabs.

Load the data into relational database tables, write a Google App Engine application that queries all rows, summarizes the data across each criteria, and then renders results using the Google Charts and visualization API.

The zone

The number of workers

The disk size per worker

The maximum number of workers

Delete the table CLICK_STREAM, and then re-create it such that the column DT is of the TIMESTAMP type. Reload the data.

Add a column TS of the TIMESTAMP type to the table CLICK_STREAM, and populate the numeric values from the column TS for each row. Reference the column TS instead of the column DT from now on.

Create a view CLICK_STREAM_V, where strings from the column DT are cast into TIMESTAMP values. Reference the view CLICK_STREAM_V instead of the table CLICK_STREAM from now on.

Add two columns to the table CLICK STREAM: TS of the TIMESTAMP type and IS_NEW of the BOOLEAN type. Reload all data in append mode. For each appended row, set the value of IS_NEW to true. For future queries, reference the column TS instead of the column DT, with the WHERE clause ensuring that the value of IS_NEW must be true.

Construct a query to return every row of the table CLICK_STREAM, while using the built-in function to cast strings from the column DT into TIMESTAMP values. Run the query into a destination table NEW_CLICK_STREAM, in which the column TS is the TIMESTAMP type. Reference the table NEW_CLICK_STREAM instead of the table CLICK_STREAM from now on. In the future, new data is loaded into the table NEW_CLICK_STREAM.

There are very few occurrences of mutations relative to normal samples.

There are roughly equal occurrences of both normal and mutated samples in the database.

You expect future mutations to have different features from the mutated samples in the database.

You expect future mutations to have similar features to the mutated samples in the database.

You already have labels for which samples are mutated and which are normal in the database.

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.

Send the data to Google Cloud Datastore and then export to BigQuery.

Send the data to Google Cloud Pub/Sub, stream Cloud Pub/Sub to Google Cloud Dataflow, and store the data in Google BigQuery.

Send the data to Cloud Storage and then spin up an Apache Hadoop cluster as needed in Google Cloud Dataproc whenever analysis is required.

Export logs in batch to Google Cloud Storage and then spin up a Google Cloud SQL instance, import the data from Cloud Storage, and run an analysis as needed.

Use federated data sources, and check data in the SQL query.

Enable BigQuery monitoring in Google Stackdriver and create an alert.

Import the data into BigQuery using the gcloud CLI and set max_bad_records to 0.

Run a Google Cloud Dataflow batch pipeline to import the data into BigQuery, and push errors to another dead-letter table for analysis.

Specify the TableReference object in the code.

Use .fromQuery operation to read specific fields from the table.

Use of both the Google BigQuery TableSchema and TableFieldSchema classes.

Call a transform that returns TableRow objects, where each element in the PCollexction represents a single row in the table.

Create a Google Cloud Dataflow job to process the data.

Create a Google Cloud Dataproc cluster that uses persistent disks for HDFS.

Create a Hadoop cluster on Google Compute Engine that uses persistent disks.

Create a Cloud Dataproc cluster that uses the Google Cloud Storage connector.

Create a Hadoop cluster on Google Compute Engine that uses Local SSD disks.

Use a fixed-time window with a duration of 60 minutes.

Use a sliding time window with a duration of 60 minutes.

Use a session window with a gap time duration of 60 minutes.

Use a global window with a time based trigger with a delay of 60 minutes.

Re-write the application to load accumulated data every 2 minutes.

Convert the streaming insert code to batch load for individual messages.

Load the original message to Google Cloud SQL, and export the table every hour to BigQuery via streaming inserts.

Estimate the average latency for data availability after streaming inserts, and always run queries after waiting twice as long.

all data

no data

the last transaction

the time dimension

Cloud Dataflow connector

DataFlow SDK

BiqQuery API

BigQuery Data Transfer Service

Import the data from Cloud Storage into BigQuery Create a new BigQuery table, and skip the rows with errors.

Create a Compute Engine instance and create a new copy of the data in Cloud Storage Skip the rows with errors

Create a Cloud Dataflow workflow that reads the data from Cloud Storage, checks for values outside the expected range, sets the value to an appropriate default, and writes the updated records to a new dataset in

Cloud Storage

Create a Cloud Dataflow workflow that reads the data from Cloud Storage, checks for values outside the expected range, sets the value to an appropriate default, and writes the updated records to the same dataset in Cloud Storage

Unsupervised learning

Regressor

Classifier

Clustering estimator

An hourly watermark

An event time trigger

The with Allowed Lateness method

A processing time trigger

Cloud SQL

BigQuery

Cloud Bigtable

Cloud Datastore

‘bigquery-public-data.noaa_gsod.gsod‘

bigquery-public-data.noaa_gsod.gsod*

‘bigquery-public-data.noaa_gsod.gsod’*

‘bigquery-public-data.noaa_gsod.gsod*`

Use a row key of the form .

Use a row key of the form .

Use a row key of the form #.

Use a row key of the form >##.

Hive

Pig

YARN

Spark

VPN connection

Special browser

SSH tunnel

FTP connection

Execute to Google Cloud Storage; write to Google Cloud Logging

Write to Google Cloud Storage; read to Google Cloud Logging

Execute to Google Cloud Storage; execute to Google Cloud Logging

Read and write to Google Cloud Storage; write to Google Cloud Logging

Dataflow pipelines are tied to Dataflow, and cannot be run on any other runner

Dataflow pipelines can consume data from other Google Cloud services

Dataflow pipelines can be programmed in Java

Dataflow pipelines use a unified programming model, so can work both with streaming and batch data sources

Make a call to the Stackdriver API to list all logs, and apply an advanced filter.

In the Stackdriver logging admin interface, and enable a log sink export to BigQuery.

In the Stackdriver logging admin interface, enable a log sink export to Google Cloud Pub/Sub, and subscribe to the topic from your monitoring tool.

Using the Stackdriver API, create a project sink with advanced log filter to export to Pub/Sub, and subscribe to the topic from your monitoring tool.

Assign global unique identifiers (GUID) to each data entry.

Compute the hash value of each data entry, and compare it with all historical data.

Store each data entry as the primary key in a separate database and apply an index.

Maintain a database table to store the hash value and other metadata for each data entry.

Redefine the schema by evenly distributing reads and writes across the row space of the table.

The performance issue should be resolved over time as the site of the BigDate cluster is increased.

Redesign the schema to use a single row key to identify values that need to be updated frequently in the cluster.

Redesign the schema to use row keys based on numeric IDs that increase sequentially per user viewing the offers.

Supervised learning to determine which transactions are most likely to be fraudulent.

Unsupervised learning to determine which transactions are most likely to be fraudulent.

Clustering to divide the transactions into N categories based on feature similarity.

Supervised learning to predict the location of a transaction.

Reinforcement learning to predict the location of a transaction.

Unsupervised learning to predict the location of a transaction.

Put the data into Google Cloud Storage.

Use preemptible virtual machines (VMs) for the Cloud Dataproc cluster.

Tune the Cloud Dataproc cluster so that there is just enough disk for all data.

Migrate some of the cold data into Google Cloud Storage, and keep only the hot data in Persistent Disk.

Disable caching by editing the report settings.

Disable caching in BigQuery by editing table details.

Refresh your browser tab showing the visualizations.

Clear your browser history for the past hour then reload the tab showing the virtualizations.

PCollection

Transform

Pipeline

Sink API

multi-keyed data with very high latency

multi-keyed data with very low latency

single-keyed data with very low latency

single-keyed data with very high latency

Your gcloud does not have access to the BigQuery resources

BigQuery cannot be accessed from local machines

You are missing gcloud on your machine

Pipelines cannot be run locally

Avoid schema designs that are based on NoSQL concepts

Create schema designs that are based on a relational database design

Avoid schema designs that require atomicity across rows

Create schema designs that require atomicity across rows

Insert a Reshuffle operation after each processing step, and monitor the execution details in the Dataflow console.

Log debug information in each ParDo function, and analyze the logs at execution time.

Insert output sinks after each key processing step, and observe the writing throughput of each block.

Verify that the Dataflow service accounts have appropriate permissions to write the processed data to the output sinks

Field promotion

Randomization

Salting

Hashing

increasing or decreasing the number of worker nodes

increasing or decreasing the number of master nodes

moving memory to run more applications on a single node

deleting applications from unused nodes periodically

Pipelines are a set of operations

Pipelines represent a data processing job

Pipelines represent a directed graph of steps

Pipelines can share data between instances

So you can try two different sets of features

To make sure your model is generalized for more than just the training data

To allow you to create unit tests in your code

So you can use one dataset for a wide model and one for a deep model

Disable writes to certain tables.

Restrict access to tables by role.

Ensure that the data is encrypted at all times.

Restrict BigQuery API access to approved users.

Segregate data across multiple tables or databases.

Use Google Stackdriver Audit Logging to determine policy violations.

Set initialization actions

Modify configuration files using cluster properties

Configure the cluster using Cloud Deployment Manager

Log into the master node and make changes from there

Store and process the entire dataset in BigQuery.

Store and process the entire dataset in Cloud 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.

Make a secondary instance of the Redis database on a Compute Engine instance, and then perform a live cutover.

Write a shell script to migrate the Redis data, and create a new Memorystore for Redis instance.

Create a Dataflow job to road the Redis database from the on-premises data center. and write the data to a Memorystore for Redis instance

Make an RDB backup of the Redis database, use the gsutil utility to copy the RDB file into a Cloud Storage bucket, and then import the RDB tile into the Memorystore for Redis instance.

Use K-means Clustering to detect faces in the pixels.

Use feature engineering to add features for eyes, noses, and mouths to the input data.

Use deep learning by creating a neural network with multiple hidden layers to automatically detect features of faces.

Build a neural network with an input layer of pixels, a hidden layer, and an output layer with two categories.

Cancel

Drain

Stop

Finish

Increase the CPU size on your server.

Increase the size of the Google Persistent Disk on your server.

Increase your network bandwidth from your datacenter to GCP.

Increase your network bandwidth from Compute Engine to Cloud Storage.

Build Dataform assertions into your code

Write a Spark-based stored procedure.

Build BigQuery user-defined functions (UDFs).

Create Dataplex data quality tasks.

1Create an Apache Airflow directed acyclic graph (DAG) in Cloud Composer with sequential tasks by using the Cloud Storage. Dataproc. and BigQuery operators

2 Use a single shared DAG for all tables that need to go through the pipeline

3 Schedule the DAG to run hourly

1 Create an Apache Airflow directed acyclic graph (DAG) in Cloud Composer with sequential tasks by using the Dataproc and BigQuery operators.

2 Create a separate DAG for each table that needs to go through the pipeline

3 Use a Cloud Storage object trigger to launch a Cloud Function that triggers the DAG

1 Create an Apache Airflow directed acyclic graph (DAG) in Cloud Composer with sequential tasks by using the Cloud Storage, Dataproc. and BigQuery operators

2 Create a separate DAG for each table that needs to go through the pipeline

3 Schedule the DAGs to run hourly

1 Create an Apache Airflow directed acyclic graph (DAG) in Cloud Composer with sequential tasks by using the Dataproc and BigQuery operators

2 Use a single shared DAG for all tables that need to go through the pipeline.

3 Use a Cloud Storage object trigger to launch a Cloud Function that triggers the DAG

Update SQL pipelines and ad-hoc queries to run as interactive query jobs.

Increase the slot capacity of the project with baseline as 0 and maximum reservation size as 3000.

Update SOL pipelines to run as a batch query, and run ad-hoc queries as interactive query jobs.

Increase the slot capacity of the project with baseline as 2000 and maximum reservation size as 3000.

1. Deploy a long-living Dalaproc cluster with Apache Hive and Ranger enabled.

2. Configure Ranger for column level security.

3. Process with Dataproc Spark or Hive SQL.

1. Define a BigLake table.

2. Create a taxonomy of policy tags in Data Catalog.

3. Add policy lags to columns.

4. Process with the Spark-BigQuery connector or BigQuery SOL.

1. Load the data to BigQuery tables.

2. Create a taxonomy of policy tags in Data Catalog.

3. Add policy tags to columns.

4. Procoss with the Spark-BigQuery connector or BigQuery SQL.

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.

Assign the compute. networkUser role to the Dataflow service agent.

Assign the compute.networkUser role to the service account that executes the Dataflow pipeline.

Assign the dataflow, admin role to the Dataflow service agent.

Assign the dataflow, admin role to the service account that executes the Dataflow pipeline.

Exceptions in worker code

Job validation

Graph or pipeline construction

Insufficient permissions

Issue a command to restart the database servers.

Retry the query with exponential backoff, up to a cap of 15 minutes.

Retry the query every second until it comes back online to minimize staleness of data.

Reduce the query frequency to once every hour until the database comes back online.

Provide latitude and longtitude as input vectors to your neural net.

Create a numeric column from a feature cross of latitude and longtitude.

Create a feature cross of latitude and longtitude, bucketize at the minute level and use L1 regularization during optimization.

Create a feature cross of latitude and longtitude, bucketize it at the minute level and use L2 regularization during optimization.

1Create 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.

1Create 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.

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.

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.

Set a retention policy. Lock the retention policy.

Set a retention policy. Set the default storage class to Archive for long-term digital preservation.

Enable the Object Versioning feature. Add a lifecycle rule.

Enable the Object Versioning feature. Create a copy in a bucket in a different region.

Attach the timestamp on each message in the Cloud Pub/Sub subscriber application as they are received.

Attach the timestamp and Package ID on the outbound message from each publisher device as they are sent to Clod Pub/Sub.

Use the NOW () function in BigQuery to record the event’s time.

Use the automatically generated timestamp from Cloud Pub/Sub to order the data.

Cloud Pub/Sub, Cloud Dataflow, and Cloud Storage

Cloud Pub/Sub, Cloud Dataflow, and Local SSD

Cloud Pub/Sub, Cloud SQL, and Cloud Storage

Cloud Load Balancing, Cloud Dataflow, and Cloud Storage

Store the common data in BigQuery as partitioned tables.

Store the common data in BigQuery and expose authorized views.

Store the common data encoded as Avro in Google Cloud Storage.

Store he common data in the HDFS storage for a Google Cloud Dataproc cluster.

Export the data into a Google Sheet for virtualization.

Create an additional table with only the necessary columns.

Create a view on the table to present to the virtualization tool.

Create identity and access management (IAM) roles on the appropriate columns, so only they appear in a query.

Redis

HBase

MySQL

MongoDB

Cassandra

HDFS with Hive

Change the processing job to use Google Cloud Dataproc instead.

Manually start the Cloud Dataflow job each morning when you get into the office.

Create a cron job with Google App Engine Cron Service to run the Cloud Dataflow job.

Configure the Cloud Dataflow job as a streaming job so that it processes the log data immediately.

Option A

Option B.

Option C

Option D

Introduce data compression for each file to increase the rate file of file transfer.

Contact your internet service provider (ISP) to increase your maximum bandwidth to at least 100 Mbps.

Redesign the data ingestion process to use gsutil tool to send the CSV files to a storage bucket in parallel.

Assemble 1,000 files into a tape archive (TAR) file. Transmit the TAR files instead, and disassemble the CSV files in the cloud upon receiving them.

Create an S3-compatible storage endpoint in your network, and use Google Cloud Storage Transfer Service to transfer on-premices data to the designated storage bucket.