GCP-ADP Data Management Practice Test 2 — 15 Questions

Question 1

A financial services company stores transaction records in BigQuery. Compliance requires that individual rows be deleted upon customer request, and the table is partitioned by transaction date. Which approach satisfies the deletion requirement while minimizing cost?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Run a DELETE statement filtered by customer ID on the partitioned table. A DELETE statement filtered by customer ID on the partitioned table removes the specific rows required by compliance. Because the table is partitioned by transaction date, filtering on the partition column can reduce the data scanned and lower cost. Other options either rewrite the whole table, delete entire partitions, or merely restrict access without deleting data.

Answer

Set a partition expiration on the table to remove old partitions

Answer

Recreate the table with a CREATE TABLE AS SELECT that excludes the customer rows

Answer

Use a column-level security policy to hide the customer records

Question 2

A financial services company stores transaction records in BigQuery. Compliance requires that individual rows be deleted upon customer request, and the table is partitioned by transaction date. Which approach satisfies the deletion requirement while minimizing cost?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Create a view that selects only the allowed columns and filters region to EMEA, then grant the compliance officer access to the view instead of the base table.. An authorized view is the right mechanism when one user needs a restricted projection and filter while others keep full access. The view runs with the owner's permissions, so the compliance officer reads only the allowed columns and rows without any direct grant on the base table, and no data copy is created.

Answer

Export the EMEA rows to a separate BigQuery dataset and grant the compliance officer access to that dataset only.

Answer

Use column-level security to grant access only to account_id and transaction_date, and use row-level security to filter region to EMEA.

Answer

Grant the compliance officer the BigQuery Data Viewer role on the dataset and rely on their query habits to select only the allowed columns.

Question 3

You are managing a BigQuery dataset that contains a table with a column of type STRING that stores JSON-formatted data. Analysts frequently need to extract specific fields from this JSON column in their queries. You want to provide them with a way to query these fields efficiently without repeatedly parsing the JSON. What should you do?

Accepted Answer

Add generated columns to the table that extract the JSON fields using JSON_VALUE, and then query those columns.. Generated columns let you define columns that are automatically computed from other columns using functions like JSON_VALUE. They are stored and maintained by BigQuery, so queries can access the extracted values without parsing the JSON string each time. This provides an efficient, native way to expose JSON fields as first-class columns.

Answer

Use a materialized view that precomputes the extracted JSON fields and refreshes automatically.

Answer

Create a separate table that stores the parsed JSON fields and use a scheduled query to keep it in sync.

Answer

Create a view that uses the JSON_EXTRACT_SCALAR function to parse the JSON column and expose the fields as separate columns.

Question 4

A company stores customer transaction data in BigQuery. The security team requires that analysts can query the data but cannot see individual credit card numbers. Analysts should still be able to run aggregations and joins. What should the data engineer implement?

Accepted Answer

Column-level security using policy tags in Data Catalog. Policy tags in Data Catalog provide column-level access control in BigQuery. By assigning a policy tag to the credit card column and granting only specific groups the fine-grained reader role, analysts can query the table and perform aggregations while the sensitive column remains inaccessible or masked. This directly enforces the security requirement.

Answer

BigQuery row-level security with a filter on transaction amount

Answer

Cloud DLP inspection of the table with a scheduled scan

Answer

Authorized views that exclude the credit card column

Question 5

Your analytics team queries a BigQuery table that grows by about 40 GB per day. Most queries filter on a single `event_date` column, but the table is not partitioned, so each query scans the full table and costs are climbing. You want the lowest-effort change that reduces bytes scanned for these date-filtered queries without rewriting the ingestion pipeline. What should you do?

Accepted Answer

Create a new table partitioned by the `event_date` column and load new data into it, then point the team's queries at the new table.. Partitioning the table on the column that filters appear in most often allows BigQuery to skip partitions that cannot match the filter, which cuts bytes scanned and therefore cost. Because the team already filters on `event_date`, a table partitioned on that column gives immediate benefit, and loading new data into it avoids a disruptive full rewrite of historical ingestion.

Answer

Set a custom quota on the dataset that limits the number of bytes each user can query per day.

Answer

Enable the BigQuery BI Engine reservation on the dataset and let the team continue querying the original table.

Answer

Create a materialized view that selects all columns from the table, and have the team query the materialized view instead.

Question 6

You are managing a Cloud Storage bucket that contains sensitive customer data. Your security team requires that all data written to the bucket be encrypted with a Customer-Managed Encryption Key (CMEK) using Cloud KMS. You need to ensure that any object uploaded without a CMEK is automatically rejected. What should you do?

Accepted Answer

Create an organization policy constraint requiring CMEK for Cloud Storage buckets.. To enforce that all objects in a Cloud Storage bucket are encrypted with a CMEK, you must use an organization policy constraint. This policy ensures that any upload without a CMEK is denied. Setting a default encryption key only applies the key when none is specified, but it does not reject uploads without a CMEK. The organization policy is the only mechanism that actively enforces the requirement.

Answer

Use a Cloud Storage bucket retention policy with a KMS key restriction.

Answer

Enable uniform bucket-level access and grant the KMS key to the bucket's service account.

Answer

Set a bucket policy using the gcloud storage buckets update command with --default-encryption-key.

Question 7

You manage a Cloud SQL for PostgreSQL instance that holds order data. A downstream BigQuery pipeline must read this data nightly, and the security team requires that credentials never be stored in pipeline code and that the connection be authorized through Google Cloud identity. Which two actions should you take? (Choose two.)

Accepted Answer

Use a BigQuery external connection to query Cloud SQL through the EXTERNAL_QUERY function after the connection service account is authorized on the instance.. A BigQuery Cloud SQL connection plus EXTERNAL_QUERY lets the pipeline read PostgreSQL data using a connection service account authorized through IAM, so no database credentials live in code. Granting that service account the Cloud SQL Client role on the instance is what actually permits the connection to open sessions and run the federated query.

Answer

Create a Cloud SQL Auth Proxy sidecar in the pipeline and pass a static database password through an environment variable.

Answer

Store the PostgreSQL username and password in a Secret Manager secret and reference it from the pipeline source code.

Answer

Enable the Cloud SQL Admin API and assign the BigQuery connection service account the Cloud SQL Viewer role at the project level.

Question 8

Your team ingests application logs into BigQuery every hour. Analysts complain that queries joining the logs table with a slow-changing reference table run slowly and cost more than expected. The reference table has about 50,000 rows and changes a few times per day. You want to reduce the bytes scanned by these joins without changing the analysts' SQL. What should you do?

Accepted Answer

Create a materialized view that pre-joins the logs with the reference table, and let BigQuery automatically rewrite matching queries to use it.. A materialized view that pre-joins the logs with the reference table lets BigQuery automatically rewrite matching analyst queries to read the smaller pre-computed result. This lowers bytes scanned and cost while leaving the analysts' SQL untouched, which is exactly what the scenario requires.

Answer

Convert the reference table to a clustered table on its primary key column.

Answer

Copy the reference table into the logs table as denormalized columns during ingestion.

Answer

Partition the logs table by the log timestamp column and require analysts to add a timestamp filter.

Question 9

A company is designing a data lake on Google Cloud using Cloud Storage and wants to ensure that data is organized and governed effectively. Which two practices should they follow? (Choose two.)

Accepted Answer

Apply IAM policies at the bucket and prefix level to enforce least privilege. A consistent naming and directory structure, plus IAM policies applied at bucket and prefix levels, together provide organization and governance for a Cloud Storage data lake. They make data discoverable and enforce least privilege. Other options either flatten the structure, mix data zones, or focus on a permission model setting rather than overall organization.

Answer

Store all data in a single bucket with no prefixes to simplify management

Answer

Store raw and processed data in the same prefix to reduce duplication

Answer

Enable uniform bucket-level access to simplify permissions

Question 10

A data engineer must move a 2 TB on-premises Oracle table into BigQuery nightly. The source is behind a firewall, there is limited bandwidth, and the team wants minimal custom code and a fully managed, repeatable pipeline. Which approach best fits these constraints?

Accepted Answer

Install the BigQuery Data Transfer Service agent on-premises to extract from Oracle and load into BigQuery on a schedule.. An on-premises Oracle source behind a firewall with a need for managed, scheduled transfers points to the BigQuery Data Transfer Service with a self-hosted agent. The agent bridges the private network to BigQuery and the service handles scheduling, retries, and monitoring, minimizing custom code. The other choices either lack an on-premises connector, require hand-built scripts, or use an object-transfer service that cannot read a relational database.

Answer

Export the Oracle table to CSV, upload to Cloud Storage, and use bq load on a nightly cron job.

Answer

Use the BigQuery Data Transfer Service with an on-premises connector to schedule recurring transfers.

Answer

Deploy the Storage Transfer Service with a transfer agent inside the on-premises network.

Question 11

You are a data engineer at a financial services company. You need to ingest data from an on-premises Oracle database into BigQuery. The data volume is large, and you require incremental updates. You want to use a fully managed Google Cloud service that supports change data capture (CDC) and can handle schema changes automatically. Which TWO of the following are true about using Datastream for this purpose? (Choose two.)

Accepted Answer

Datastream can replicate data from Oracle to BigQuery with minimal latency.. Datastream is a serverless CDC and replication service that supports Oracle to BigQuery with low latency and automatic schema change handling. It does not require an on-premises agent, supports multiple destinations including BigQuery, and operates continuously rather than in daily batches. Therefore, the true statements are that it replicates with minimal latency and handles schema changes automatically.

Answer

Datastream can only replicate data to Cloud SQL, not to BigQuery.

Answer

Datastream performs batch extracts every 24 hours by default.

Answer

Datastream requires you to deploy a agent on-premises to connect to the Oracle database.

Question 12

A media company ingests clickstream events into a Pub/Sub topic. Analysts want near-real-time dashboards in BigQuery, and the team wants to avoid managing load jobs or writing custom subscriber code. Which approach should they use?

Accepted Answer

Configure the Pub/Sub topic to stream into BigQuery using the BigQuery subscription type.. A BigQuery subscription lets Pub/Sub write messages straight into a BigQuery table with a supplied schema, giving low-latency ingestion without custom code or pipeline management. Cloud Run consumers and Dataflow pipelines are valid but demand operational effort the team wants to avoid, and scheduled batch loads cannot meet near-real-time latency.

Answer

Schedule a recurring BigQuery load job that reads a Pub/Sub export file from Cloud Storage every five minutes.

Answer

Write a Cloud Run service that pulls from the subscription and calls the BigQuery streaming insert API.

Answer

Run a Dataflow streaming pipeline with the Pub/Sub to BigQuery template.

Question 13

You are using Dataplex to manage a data lake that spans multiple Cloud Storage buckets and BigQuery datasets. You need to provide a group of data scientists with read-only access to all data and metadata within a specific zone, but they should not be able to modify any assets or access other zones. Which IAM role should you grant them on the zone?

Accepted Answer

roles/dataplex.dataReader. The Dataplex Data Reader role is designed for read-only access to data and metadata within a zone. Granting it on the specific zone limits access to that zone's assets, allowing data scientists to query data without modification rights. This aligns with the principle of least privilege and meets the requirement.

Answer

roles/bigquery.dataViewer and roles/storage.objectViewer on the project.

Answer

roles/dataplex.dataOwner

Answer

roles/dataplex.viewer

Question 14

A data engineer loads CSV files into a BigQuery table every hour using a load job. Some rows contain a date string in the format `MM/DD/YYYY` in a column defined as STRING, and downstream users need to query it as a DATE. The engineer wants the conversion to happen automatically at load time without changing the source files or running a separate transformation job. What should the engineer do?

Accepted Answer

Define the column as DATE in the schema and specify the date format in the load job configuration so BigQuery parses the string during load.. BigQuery load jobs can parse string values into DATE columns when the schema declares the column as DATE and the load configuration specifies the expected format. This performs the conversion during ingestion, requires no source file changes, and avoids a separate transformation job. Views, scheduled transformations, and source file edits either defer conversion, add steps, or violate the constraint.

Answer

Change the source CSV files to ISO 8601 format so BigQuery can infer the DATE type automatically.

Answer

Keep the column as STRING and create a view that uses PARSE_DATE on the column for downstream users.

Answer

Load the column as STRING and schedule a nightly query that runs CAST on the column and overwrites the table.

Question 15

A platform team manages a BigQuery dataset containing customer records that include names, email addresses, and account numbers. Leadership requires that sensitive fields be masked for most analysts, that the masking be centrally defined and reusable across tables, and that authorized users still see real values when needed. Which TWO approaches satisfy these requirements? (Choose two.)

Accepted Answer

Define policy tags in Data Catalog and apply them to the sensitive columns, then grant fine-grained reader roles to authorized users.. The two matching approaches both rely on Data Catalog policy tags applied to sensitive columns, with BigQuery enforcing masking and fine-grained reader roles granting real-value access to authorized principals. This centralizes classification and masking so the same tag can be reused across tables. Authorized views duplicate logic per table, CMEK protects data at rest rather than query output, and row-level security filters rows instead of masking columns.

Answer

Use row-level security to filter rows so that analysts only see records belonging to their region.

Answer

Create separate analyst views that omit or hash sensitive columns and grant analysts access only to those views.

Answer

Encrypt the dataset with a customer-managed encryption key and share the key only with authorized users.