Professional Data Engineer Free Practice Questions — Page 2
Question 10
You are developing a fraud detection model using BigQuery ML. You have a raw transaction dataset and need to create new features such as the average_transaction_amount_last_24_hours and time_since_last_transaction. These features require aggregation and time-window calculations on the existing data. The goal is to ensure that these features are consistently applied during both model training and prediction without manual intervention. You need to prepare these features efficiently for your model. What should you do?
A. Implement a Cloud Run function that triggers on new transactions, calculates the features, and inserts them into a feature store before model serving.
B. Export the BigQuery data to Cloud Storage, perform feature engineering using a custom Python script in a Dataflow job, and then re-import the engineered features into BigQuery.
C. Use the TRANSFORM clause within the CREATE MODEL statement, leveraging SQL functions for aggregations and time-based calculations.
D. Create a separate BigQuery table containing pre-computed features using complex SQL queries and join this table with the raw data during model training and serving.
Show Answer
Correct Answer: C
Explanation: The BigQuery ML TRANSFORM clause lets you define feature engineering as part of the CREATE MODEL statement. These SQL-based transformations, including aggregations and time-related calculations where supported by SQL expressions over the training query, are stored with the model and automatically applied during prediction, ensuring consistency between training and inference without maintaining separate preprocessing pipelines.
Question 11
You analyze user clickstream data to personalize content recommendations. The data arrives continuously and needs to be processed with low latency, including transformations such as sessionization (grouping clicks by user within a time window) and aggregation of user activity. You need to identify a scalable solution to handle millions of events each second and be resilient to late-arriving data. What should you do?
A. Use Firebase Realtime Database for ingestion and storage, and Cloud Run functions for processing and analytics.
B. Use Cloud Data Fusion for ingestion and transformation, and Cloud SQL for storage and analytics.
C. Use Pub/Sub for ingestion, Dataflow with Apache Beam for processing, and BigQuery for storage and analytics.
D. Use Cloud Storage for ingestion, Dataproc with Apache Spark for batch processing, and BigQuery for storage and analytics.
Show Answer
Correct Answer: C
Explanation: Pub/Sub provides scalable event ingestion, Dataflow with Apache Beam supports low-latency stream processing, sessionization, windowing, aggregations, and robust handling of late-arriving data via event-time processing and watermarks. BigQuery is the appropriate analytics sink for large-scale querying of processed clickstream data.
Question 12
Your Cloud Storage data lake has raw, processed, and historical data in different buckets. Data older than two years is rarely accessed, and all data must be retained for no longer than seven years. You are concerned about rising storage costs. How should you control costs for the historical data bucket?
A. Write a script on a Compute Engine instance, triggered daily by Cloud Scheduler, to scan all objects and delete any older than seven years.
B. Configure an Object Lifecycle Management rule to transition objects older than two years to the Archive storage class and eventually delete them after seven years.
C. Enable the Autoclass feature on your Cloud Storage buckets and select Opt-in to object transitions to Coldline and Archive storage classes.
D. Replicate the buckets to a different region with lower storage costs and configure an Object Lifecycle Management rule to delete objects after seven years.
Show Answer
Correct Answer: B
Explanation: Object Lifecycle Management is designed to automate storage class transitions and object deletion based on object age. Moving objects older than two years to Archive minimizes storage costs for infrequently accessed data, and configuring deletion at seven years satisfies the retention limit without custom scripts. Autoclass does not provide a fixed-age retention/deletion policy, and replicating to another region is not a cost-optimization strategy for this requirement.
Question 13
You are designing BigQuery tables for large volumes of clickstream event data. Your data analyst team will most frequently query by specific event date ranges and filter by the user ID UUID. You want to optimize table structure for query cost and performance. What should you do?
A. Partition the table by the user ID column and cluster the table by the event date column.
B. Create an ingestion-time partitioned table and cluster it by the user ID column.
C. Cluster the table by both the event date and the user ID columns.
D. Partition the table by the event date column and cluster the table by user ID column.
Show Answer
Correct Answer: D
Explanation: For clickstream data queried primarily by event date ranges, partitioning on the event date enables partition pruning, reducing scanned data and query cost. Clustering by user ID further optimizes queries that filter within those date partitions by UUID. Partitioning by high-cardinality user IDs is not appropriate, and ingestion-time partitioning is less optimal when queries use the actual event date.
Question 14
Your company is planning to migrate a large on-premises data warehouse to BigQuery. The data is currently stored in a proprietary, vendor-specific format. You need to perform a batch migration of this data to BigQuery. What should you do?
A. Use the bq command-line tool to load the data directly from the on-premises data warehouse.
B. Export the data to CSV files, upload the files to Cloud Storage, then load the files into BigQuery.
C. Use the BigQuery Data Transfer Service.
D. Use Datastream to replicate the data in real time.
Show Answer
Correct Answer: B
Explanation: For a one-time batch migration from a proprietary on-premises data warehouse, the standard approach is to export the data into a format BigQuery supports (such as CSV), upload it to Cloud Storage, and then load it into BigQuery. The bq tool cannot load directly from a proprietary on-premises warehouse, BigQuery Data Transfer Service is intended for supported sources rather than arbitrary proprietary formats, and Datastream is for continuous change data capture/replication rather than a batch migration.
Question 15
You need to load a dataset with multiple terabytes of clickstream data into BigQuery. The data arrives each day as compressed JSON files in a Cloud Storage bucket. You need a low-cost, programmatic, and scalable solution to load the data into BigQuery. What should you do?
A. Create an external table in BigQuery pointing to the Cloud Storage bucket and run the INSERT INTO ... SELECT * FROM external_table command.
B. Use the BigQuery Data Transfer Service from Cloud Storage.
C. Create a Cloud Run function to run a Python script to read and parse each JSON file, and use the BigQuery streaming insert API.
D. Use Cloud Data Fusion to create a pipeline to load the JSON files into BigQuery.
Show Answer
Correct Answer: B
Explanation: For recurring multi-terabyte JSON files arriving in Cloud Storage, the BigQuery Data Transfer Service provides managed, scalable batch load jobs into BigQuery. Batch load jobs are the recommended low-cost approach because load jobs are free (you pay for storage/querying, not ingestion). Creating an external table and running INSERT...SELECT incurs query costs over the external data. Streaming inserts are intended for low-latency ingestion and are more expensive at this scale, while Cloud Data Fusion is unnecessary overhead for a straightforward Cloud Storage-to-BigQuery load.
Sources:
https://cloud.google.com/bigquery/docs/cloud-storage-transfer-overview
https://cloud.google.com/bigquery/docs/batch-loading-data
Question 16
Your organization stores highly personal data in BigQuery and needs to comply with strict data privacy regulations. You need to ensure that sensitive data values are rendered unreadable whenever an employee leaves the organization. What should you do?
A. Use column-level access controls with policy tags and revoke viewer permissions when employees leave the organization.
B. Use dynamic data masking and revoke viewer permissions when employees leave the organization.
C. Use customer-managed encryption keys (CMEK) and delete keys when employees leave the organization.
D. Use AEAD functions and delete keys when employees leave the organization.
Show Answer
Correct Answer: D
Explanation: AEAD functions provide application-level encryption of individual data values in BigQuery. If the encryption keys are deleted or destroyed, the encrypted values become unreadable, satisfying the requirement that sensitive data values become unreadable. Column-level access controls and dynamic data masking only restrict or mask access but do not render the underlying stored values unreadable. Deleting a CMEK would make entire tables inaccessible and is not appropriate for selectively protecting sensitive values at the data level.
Question 17
Your company wants to implement a Retrieval-Augmented Generation (RAG) system to allow employees to query an extensive knowledge base of internal documents, such as policy manuals and project reports. You need to prepare this unstructured text for embedding to be used in the RAG system. What should you do to ensure the system can retrieve the most relevant information?
A. Use Cloud Data Loss Prevention (Cloud DLP) to scan and redact sensitive information within the documents before processing.
B. Store the documents as compressed files in a traditional relational database to enable more efficient storage and retrieval.
C. Convert the unstructured documents into high-dimensional numerical vectors that capture the semantic meaning and relationships of the text.
D. Index each word from the documents into a search engine to enable keyword-based search.
Show Answer
Correct Answer: C
Explanation: RAG systems retrieve relevant context using embeddings. To prepare unstructured text for embedding, the documents should be transformed into high-dimensional vector representations that capture semantic meaning, enabling similarity search in a vector index or database. Keyword indexing alone does not provide semantic retrieval, relational databases are not appropriate for embedding-based retrieval, and DLP addresses data protection rather than retrieval quality.
Question 18
You are designing a stateful data processing pipeline that reads data from a Cloud Storage bucket and writes transformed data to a BigQuery table. The pipeline must be highly available and resilient to zonal failures within the us-central1 region. You need to configure a Dataflow pipeline ensuring minimal disruption during a zonal outage. What should you do?
A. Launch the Dataflow job with the --region=us-central1 parameter.
B. Deploy the Dataflow job to a single zone within us-central1 and configure it to use a regional persistent disk to store its state.
C. Deploy the Dataflow job to a single zone within us-central1 and use a multi-regional Cloud Storage bucket to store its state.
D. Launch the Dataflow job with the --zone=us-central1a parameter.
Show Answer
Correct Answer: A
Explanation: Using the regional endpoint (--region=us-central1) allows Dataflow to manage workers across multiple zones in the region, improving resilience to zonal failures for stateful pipelines. Specifying a single zone reduces availability, and Dataflow state is managed by the service rather than by manually storing it in Cloud Storage or configuring regional persistent disks for this purpose.
Question 19
Your team has created several BigQuery curated datasets containing anonymized industry benchmark data. You want to make these datasets easily discoverable and accessible for querying by external partner companies within their own Google Cloud projects. You need a secure and scalable solution. What should you do?
A. Publish the datasets as listings within BigQuery sharing (Analytics Hub).
B. Export the datasets to partner-specific Cloud Storage buckets.
C. Create authorized views for each dataset and grant access to each partner.
D. Grant the roles/bigquery.dataViewer IAM role to the partner group email addresses on the datasets.
Show Answer
Correct Answer: A
Explanation: BigQuery sharing (Analytics Hub) is designed for secure, scalable sharing of BigQuery datasets across organizations. Publishing curated datasets as listings makes them discoverable and queryable by external partners in their own Google Cloud projects without copying data. Exporting to Cloud Storage creates data copies and operational overhead, authorized views do not scale well for many partners, and directly granting dataset IAM access lacks the discovery and exchange capabilities of Analytics Hub.
$19
Get all 332 questions with detailed answers and explanations
Instant download HTML + PDF delivered the moment payment clears.
Secure Stripe checkout we never see or store your card details.
7-day refund if files are defective see our refund policy.