Google Data Engineer Sample Questions:

1. Suppose you have a table that includes a nested column called "city" inside a column called "person", but when you try to submit the following query in BigQuery, it gives you an error.
SELECT person FROM `project1.example.table1` WHERE city = "London"
How would you correct the error?

A) Add ", UNNEST(person)" before the WHERE clause.
B) Change "person" to "city.person".
C) Change "person" to "person.city".
D) Add ", UNNEST(city)" before the WHERE clause.

2. You need to deploy additional dependencies to all of a Cloud Dataproc cluster at startup using an existing initialization action. Company security policies require that Cloud Dataproc nodes do not have access to the Internet so public initialization actions cannot fetch resources. What should you do?

A) Deploy the Cloud SQL Proxy on the Cloud Dataproc master
B) Copy all dependencies to a Cloud Storage bucket within your VPC security perimeter
C) Use an SSH tunnel to give the Cloud Dataproc cluster access to the Internet
D) Use Resource Manager to add the service account used by the Cloud Dataproc cluster to the Network User role

3. Which Cloud Dataflow / Beam feature should you use to aggregate data in an unbounded data source every hour based on the time when the data entered the pipeline?

A) A processing time trigger
B) An hourly watermark
C) An event time trigger
D) The with Allowed Lateness method

4. You are building a new application that you need to collect data from in a scalable way. Data arrives continuously from the application throughout the day, and you expect to generate approximately 150 GB of JSON data per day by the end of the year. Your requirements are: Decoupling producer from consumer Space and cost-efficient storage of the raw ingested data, which is to be stored indefinitely Near real-time SQL query Maintain at least 2 years of historical data, which will be queried with SQ Which pipeline should you use to meet these requirements?

A) Set up periodic exports of the database to write to Cloud Storage and load into BigQuery.
B) Create an application that writes to a Cloud SQL database to store the dat
C) Create an application that provides an AP
D) Write a tool to poll the API and write data to Cloud Storage as gzipped JSON files.
E) Create an application that publishes events to Cloud Pub/Sub, and create a Cloud Dataflow pipeline that transforms the JSON event payloads to Avro, writing the data to Cloud Storage and BigQuery.
F) Create an application that publishes events to Cloud Pub/Sub, and create Spark jobs on Cloud Dataproc to convert the JSON data to Avro format, stored on HDFS on Persistent Disk.

5. What are two methods that can be used to denormalize tables in BigQuery?

A) 1) Join tables into one table; 2) Use nested repeated fields
B) 1) Use nested repeated fields; 2) Use a partitioned table
C) 1) Split table into multiple tables; 2) Use a partitioned table
D) 1) Use a partitioned table; 2) Join tables into one table

Solutions: