Professional Data Engineer · 15% of the exam

Preparing and using data for analysis: free practice questions

5 sample questions from our 38-question bank for this domain — answers and explanations included. These are the same scenario-based style as the real Google Cloud exam.

1. A data engineer writes a BigQuery User-Defined Function (UDF) to calculate a discount percentage based on customer segment and purchase history. The UDF is written in SQL and includes a nested SELECT query. The function is used in a WHERE clause of a large query processing 10 billion rows. Performance is unexpectedly slow. What is the most likely cause?

  • A. SQL UDFs are interpreted at runtime and cannot be optimized by the BigQuery query planner; rewrite as a JavaScript UDF instead
  • B. The UDF is being executed for every row, and the nested SELECT causes a subquery to run per row; materialize the lookup table or use a JOIN instead✓ Correct
  • C. BigQuery UDFs do not support nested SELECT statements; you must refactor to use only aggregate functions
  • D. The UDF is inline expanded, which is correct, but the WHERE clause predicate pushdown doesn't work; move the UDF to the SELECT clause
Explanation

The correct answer is that the UDF is executed per row, and the nested SELECT causes excessive subqueries. When a UDF with a SELECT statement is applied to billions of rows in a WHERE clause, each row execution triggers the nested SELECT, resulting in billions of subquery evaluations. The solution is to materialize the lookup table or use a JOIN instead. Answer A is wrong; SQL UDFs are actually optimized well by BigQuery's planner. Answer C is false; SQL UDFs support nested SELECT statements. Answer D is misleading; moving the UDF to SELECT doesn't solve the issue if the underlying logic is inefficient.

2. A data engineer needs to write a BigQuery User-Defined Function (UDF) in JavaScript that masks the last four digits of a credit card number string, replacing them with 'XXXX'. For example, '4111-1111-1111-1234' should become '4111-1111-1111-XXXX'. Which of the following CREATE FUNCTION statements is correct?

  • A. CREATE TEMP FUNCTION MaskCard(card_number STRING) RETURNS STRING LANGUAGE js AS r"""\ return card_number.slice(0, -4) + 'XXXX'; """;✓ Correct
  • B. CREATE TEMP FUNCTION MaskCard(card_number STRING) RETURNS STRING AS (REGEXP_REPLACE(card_number, r'\d{4}$', 'XXXX'));
  • C. CREATE TEMP FUNCTION MaskCard(card_number STRING) RETURNS STRING LANGUAGE js AS r"""\ return card_number.replace(/\d{4}$/, 'XXXX'); """;✓ Correct
  • D. CREATE TEMP FUNCTION MaskCard(card_number STRING) RETURNS STRING LANGUAGE python AS r"""\ return card_number[:-4] + 'XXXX' """;
Explanation

Options A and C are both correct JavaScript UDFs that correctly mask the last four characters/digits. (A) uses `slice(0, -4)` to get everything except the last 4 characters and appends 'XXXX' — correct for any trailing 4-character suffix. (C) uses a JavaScript regex replace targeting the last 4 digits — also valid and slightly more precise (only replaces digits). Both are syntactically valid BigQuery JS UDFs. Option B is not a JS UDF — it uses a SQL expression body with REGEXP_REPLACE; while this would work as a SQL UDF and is actually the most elegant solution, the question asks specifically about a JavaScript UDF, so B is not a JS UDF. Option D is wrong because BigQuery UDFs support JavaScript and (in some contexts) SQL expression bodies, but **not Python** as an inline UDF language (Python is supported via remote functions/Cloud Run, not native inline UDFs).

3. A data science team uses Vertex AI to train a binary classification model for fraud detection. They build the model using BigQuery ML (BQML) and then want to deploy it to Vertex AI Model Registry and create an online endpoint for real-time predictions. The team also wants to compare the BQML model performance against a custom model trained in a Vertex AI notebook using TensorFlow. Which statement correctly describes the workflow for registering and comparing both models?

  • A. Export the BQML model to Cloud Storage, upload it to Vertex AI Model Registry using the Google Cloud Console, and compare metrics against the TensorFlow model's registered version in the same registry✓ Correct
  • B. BQML models cannot be registered to Vertex AI Model Registry; instead, use the BigQuery ML prediction functions directly to score data and compare results manually against the TensorFlow model
  • C. Train both models in Vertex AI Workbench, export metrics to BigQuery, and use a Dataform workflow to compare performance KPIs across both models
  • D. Register the TensorFlow model to Vertex AI Model Registry from the notebook, export the BQML model as a SavedModel, and use Vertex AI Pipelines to orchestrate comparison experiments
Explanation

BQML models can be exported and registered in Vertex AI Model Registry. You export the model to Cloud Storage, then import it into Model Registry via the Google Cloud Console or API. Both the BQML and TensorFlow models can be compared side-by-side in the registry. Option B is incorrect; BQML models ARE compatible with Model Registry. Option C conflates separate services; the Dataform workflow would not be the standard approach for model comparison. Option D adds unnecessary complexity with SavedModel export and Pipelines orchestration when direct Model Registry comparison is simpler and more aligned with the goal.

4. Your team is building a feature engineering pipeline in Vertex AI notebooks for a binary classification model. You need to join streaming clickstream data with historical customer profiles from BigQuery. Which Vertex AI component is best suited for this workflow?

  • A. Vertex AI Pipelines (Kubeflow) to orchestrate data joins and train the model end-to-end
  • B. Vertex AI Feature Store to manage feature definitions, serve them at inference time, and handle the join logic✓ Correct
  • C. BigQuery SQL directly in the notebook with pandas DataFrames for feature engineering
  • D. Dataform to materialize joined features, then read the output into the notebook
Explanation

The correct answer is Vertex AI Feature Store. Feature Store is designed to manage and serve features consistently between training and inference, including joins across multiple sources. It handles the join logic, versioning, and online/offline serving—exactly what you need for combining clickstream and customer profile data. Answer A (Pipelines) is for orchestration and works well with Feature Store, but isn't specialized for the join logic itself. Answer C (BigQuery SQL + pandas) works for one-off analysis but doesn't provide feature governance, versioning, or inference-time serving guarantees. Answer D (Dataform) materializes joined data but doesn't provide the feature management and serving capabilities of Feature Store.

5. A healthcare organization stores patient data in BigQuery. The security team requires that analysts in the 'research' group can query the `patients` table but must never see the `ssn` or `diagnosis_code` columns. The solution must scale to hundreds of analysts without modifying every individual's permissions. What is the recommended approach?

  • A. Create an authorized view that selects all columns except `ssn` and `diagnosis_code`, and grant the research group access to the view only
  • B. Use BigQuery column-level security by attaching policy tags to `ssn` and `diagnosis_code`, then deny the 'research' group the Fine-Grained Reader role on those tags✓ Correct
  • C. Export the table to Cloud Storage without the sensitive columns and have analysts query the exported files via BigQuery external tables
  • D. Enable VPC Service Controls on the BigQuery dataset and configure an ingress rule that strips the sensitive columns
Explanation

Option B is correct. Column-level security with policy tags is the native, scalable BigQuery mechanism for restricting access to specific columns. By tagging `ssn` and `diagnosis_code` and not granting the research group the Fine-Grained Reader role on those tags, the columns are automatically masked/blocked at query time for every group member, requiring no per-user changes. Option A (authorized view) also works technically, but it requires maintaining a separate view and does not prevent a user with table-level access from querying the base table directly; column-level security enforces the restriction at the column itself regardless of access path. Option C introduces operational overhead (re-exporting on every update) and is not a native security control. Option D is incorrect; VPC Service Controls control network-level access to the API, not column-level data visibility within a query.

33 more questions in this domain

Practice the full bank with instant grading, flashcards, and a timed mock exam.

Start practicing free