GCP-ADP Data Management Practice Test 6 — 15 Questions

Question 1

You are designing a data lake in Cloud Storage and need to ensure that data is organized for efficient querying by BigQuery and other analytics services. You plan to use a Hive-style partitioned directory structure. Which two practices should you follow? (Choose two.)

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

Accepted Answer

Use a consistent key=value naming convention for partition directories, such as dt=2024-01-01.. Using a consistent key=value directory naming convention and columnar file formats like Parquet or ORC are best practices for a Hive-partitioned data lake. The naming convention enables automatic partition inference and pruning, while columnar formats reduce I/O and improve query efficiency. Together, they optimize both cost and performance for analytics services like BigQuery.

Answer

Store all files in a single directory and rely on file name prefixes for filtering.

Answer

Store each partition as a single large file to minimize the number of files.

Answer

Ensure that the partition key is the last element in the directory path, such as year=2024/month=01/day=01.

Question 2

You are designing a BigQuery table for a fleet of IoT devices that emit one reading per second. The table will hold years of data, and most queries filter by device identifier and a time range. You want to reduce the bytes scanned by these queries and control long-term storage cost. (Choose two.)

Accepted Answer

Cluster the table on the device identifier column.. Partitioning by the event timestamp and clustering by device identifier align the physical layout of the table with the dominant query pattern of filtering by device and time range. Together they let BigQuery prune partitions and skip blocks, cutting bytes scanned and cost, while still retaining years of data.

Answer

Enable a customer-managed encryption key on the table to reduce storage cost.

Answer

Create a materialized view that aggregates readings per device per hour and query only the view.

Answer

Set the table's default expiration to 30 days so older data is deleted automatically.

Question 3

Your organization stores sensitive customer data in a Cloud Storage bucket. You need to ensure that data is automatically classified and tagged as 'PII' when it is written, and that the tags are available for audit and access control decisions. You want to minimize manual effort and use Google Cloud native tooling. What should you do?

Accepted Answer

Enable Sensitive Data Protection (DLP) discovery on the bucket and configure a discovery scan with a schedule; then publish the results to Data Catalog.. Sensitive Data Protection discovery is the native Google Cloud service for automatically profiling and classifying data in Cloud Storage. It can detect PII infoTypes, run on a schedule, and publish findings as tags in Data Catalog. This satisfies the need for automatic classification with minimal manual effort and integrates with audit and access control.

Answer

Create a Cloud Function that triggers on object finalize events, calls the DLP API to inspect the object, and writes custom tags to a separate Firestore database.

Answer

Use Dataplex Data Quality tasks to scan the bucket and automatically apply PII tags based on predefined rules.

Answer

Configure a VPC Service Controls perimeter around the bucket and enable Access Transparency logs to classify the data.

Question 4

A data engineer needs to load a 500 GB CSV file from a Cloud Storage bucket into a BigQuery table once per day. The CSV has a header row and uses comma delimiters. The engineer wants the simplest managed approach that requires no code and handles schema autodetection. What should the engineer use?

Accepted Answer

BigQuery Data Transfer Service with a Cloud Storage transfer configuration. BigQuery Data Transfer Service is the managed, no-code option for scheduled loads from Cloud Storage into BigQuery. It supports CSV with headers, autodetects schema, and runs on a schedule without requiring a VM, cluster, or custom pipeline code. The other options all require managing infrastructure or writing code.

Answer

Cloud Dataproc with a PySpark job that reads the CSV from Cloud Storage and writes to BigQuery

Answer

bq load with the --autodetect flag executed from a cron job on a Compute Engine VM

Answer

Cloud Dataflow with a custom Apache Beam pipeline using TextIO and BigQueryIO

Question 5

You need to grant a data analyst read-only access to a specific BigQuery dataset. The analyst should be able to query tables but must not be able to modify data or view other datasets in the project. Which IAM role should you grant on the dataset?

Accepted Answer

roles/bigquery.dataViewer. Granting roles/bigquery.dataViewer on the dataset provides read-only access to the data and metadata, allowing the analyst to query tables without modification rights. It is the least privilege role that meets the requirement and can be scoped to a specific dataset, preventing access to other datasets in the project.

Answer

roles/bigquery.dataEditor

Answer

roles/bigquery.user

Answer

roles/bigquery.metadataViewer

Question 6

You are the data engineer for a retail company on Google Cloud. Analysts query a BigQuery table that contains 400 TB of point-of-sale records and almost always filter by transaction_date. Query costs have grown sharply because every query scans the full table. You need the most cost-effective change that reduces bytes scanned for date-filtered queries without altering the table schema. What should you do?

Accepted Answer

Partition the table by the transaction_date column, since partition pruning skips partitions that do not match the filter predicate.. Partitioning the table on the transaction_date column is correct because BigQuery uses partition pruning to read only the partitions that satisfy the date predicate, directly lowering bytes scanned and cost. Clustering, materialized views, and BI Engine can help in narrower cases, but none changes the physical partitioning of the huge base table the way date partitioning does.

Answer

Enable the BigQuery BI Engine reservation and let the in-memory cache absorb the repeated date-range scans.

Answer

Add a clustering key on transaction_date and rely on cluster pruning to eliminate all non-matching data blocks.

Answer

Create a materialized view that pre-aggregates daily sales totals, then point all analyst queries at the view.

Question 7

A healthcare analytics team stores patient encounter records in Cloud Storage as CSV files and needs to run SQL over them in BigQuery without loading or duplicating the data. The files must remain the system of record, and the team wants to avoid storage charges for a second copy. Which BigQuery feature should they use?

Accepted Answer

Create an external table over the Cloud Storage bucket and query the CSV files directly.. An external table points BigQuery at the CSV objects in Cloud Storage and queries them in place, satisfying the requirement to keep the files as the system of record and avoid a duplicate billed copy. Loading duplicates data, while BigLake and non-existent dataset mounting add complexity or do not address the stated need.

Answer

Load the CSV files into a native BigQuery table with a scheduled load job every hour.

Answer

Create a BigLake table over the CSV files and query it with the BigQuery Storage API.

Answer

Mount the Cloud Storage bucket as a BigQuery dataset using a federated dataset connection.

Question 8

You have a BigQuery table that is partitioned by day on a DATE column. You need to ensure that data older than 90 days is automatically deleted to reduce storage costs, while keeping the table structure and recent data intact. What should you do?

Accepted Answer

Set a partition expiration on the table to 90 days.. Setting partition expiration on a partitioned table automatically removes partitions older than the specified retention period. This is the simplest and most cost-effective way to delete old data while preserving the table and recent partitions. It requires no manual intervention and integrates with BigQuery's storage management.

Answer

Enable table expiration on the table for 90 days.

Answer

Create a scheduled query that deletes rows older than 90 days daily.

Answer

Use a lifecycle rule on the dataset to delete tables older than 90 days.

Question 9

You need to load a large CSV file from Cloud Storage into BigQuery. The file contains a header row, and you want to automatically detect the schema. You also need to ensure that if any row has a type mismatch, the load job fails and reports the error. Which load configuration should you use?

Accepted Answer

Set skipLeadingRows to 1 and autodetect to true, and set maxBadRecords to 0.. The correct configuration skips the header row, enables schema autodetection, and sets maxBadRecords to 0 to fail on any bad record. This ensures the load job processes the CSV correctly, infers the schema, and enforces strict error handling as required.

Answer

Set skipLeadingRows to 0 and autodetect to true, and set maxBadRecords to 0.

Answer

Set skipLeadingRows to 1 and autodetect to true, and set maxBadRecords to 10.

Answer

Set skipLeadingRows to 1 and autodetect to false, and set maxBadRecords to 0.

Question 10

A healthcare organization needs to analyze sensitive patient data in BigQuery while ensuring that analysts can see aggregated results but cannot view individual patient identifiers. Which Google Cloud feature should they implement?

Accepted Answer

Column-level security with policy tags. Column-level security with policy tags allows classifying sensitive columns and granting access only to authorized users. Analysts without the policy tag role can query other columns and compute aggregates but cannot see patient identifiers. Other options either restrict rows, use less scalable view patterns, or address network boundaries rather than column visibility.

Answer

Row-level security with filter predicates

Answer

BigQuery authorized views

Answer

VPC Service Controls perimeter around BigQuery

Question 11

Your analytics team stores clickstream events in Cloud Storage as newline-delimited JSON files. They want to query this data with BigQuery using SQL without loading it into native BigQuery storage, and they want the table definition to refresh automatically when new files land in the bucket. What should you configure?

Accepted Answer

Create a BigQuery external table over the Cloud Storage bucket and enable automatic metadata refresh.. The scenario calls for querying Cloud Storage files with BigQuery SQL and having the table definition track new files automatically. An external table over Cloud Storage satisfies in-place querying, and automatic metadata refresh keeps the table aware of newly added objects without manual intervention. The other approaches either copy data into native storage, rely on a non-existent SQL endpoint, or move the workload to a different engine and dialect.

Answer

Create a Dataplex asset over the bucket and query it with the Dataplex SQL endpoint.

Answer

Mount the bucket with Cloud Storage FUSE on a Dataproc cluster and query it with Spark SQL.

Answer

Load the JSON files into a native BigQuery table using a recurring Cloud Composer job.

Question 12

You manage a BigQuery dataset that contains a table with 500 TB of historical order data. Analysts frequently run queries that filter on the `order_date` column. Query costs are rising because every query scans the entire table. You want to reduce the bytes scanned and cost without changing the table name or existing SQL. What should you do?

Accepted Answer

Partition the table by the `order_date` column so queries filtering on that column scan only relevant partitions.. Partitioning a table by a column that queries commonly filter on lets BigQuery prune partitions and read only the data needed, which reduces bytes scanned and therefore cost. Because partitioning does not change the table name or require SQL changes, it satisfies all the constraints. The other choices either require SQL rewrites, do not affect scan volume, or block queries instead of optimizing them.

Answer

Set a custom query quota on the project so analysts cannot run queries that scan more than 1 TB.

Answer

Enable the BigQuery BI Engine reservation on the dataset to cache query results and lower cost.

Answer

Create a materialized view that pre-aggregates orders by date and instruct analysts to query the view instead.

Question 13

You are designing a data lake in Cloud Storage and need to ensure that data is organized for efficient querying by BigQuery and other analytics services. You plan to use a Hive-style partitioned directory structure. Which two practices should you follow? (Choose two.)

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

Accepted Answer

Create an external table in BigQuery that points to the Cloud Storage data.. To query Cloud Storage data in BigQuery without moving it, you create an external table. To enforce column-level access control, you apply policy tags to the columns of that external table. Policy tags are the native BigQuery feature for column-level security and work with external tables, meeting both requirements.

Answer

Create a BigQuery dataset and use authorized views to restrict access to specific columns.

Answer

Enable uniform bucket-level access on the Cloud Storage bucket.

Answer

Set up a Dataplex lake and zone, and register the Cloud Storage bucket as an asset.

Question 14

Your company stores sensitive customer records in a Cloud Storage bucket. A compliance officer requires that every object in the bucket be automatically classified and that any object containing credit card numbers be flagged for review. You want a managed Google Cloud service that samples and inspects the data without writing custom code. What should you use?

Accepted Answer

Cloud Data Loss Prevention (DLP) with a discovery scan configured on the Cloud Storage bucket.. Cloud DLP discovery scans are the managed way to sample and inspect data in Cloud Storage and produce findings when infoTypes such as credit card numbers are detected. This provides automatic classification and flagging without custom code. Audit logs, VPC Service Controls, and lifecycle rules operate on metadata, access, or storage class rather than content, so they cannot detect sensitive data inside objects.

Answer

Cloud Audit Logs with a log-based metric that counts objects uploaded to the bucket.

Answer

Cloud Storage Object Lifecycle Management rules that move objects to Coldline storage after 30 days.

Answer

VPC Service Controls with an access policy that restricts the bucket to approved projects.

Question 15

You have a BigQuery table that is partitioned by ingestion time. You need to reduce storage costs by automatically deleting data older than 90 days. You want to ensure that the deletion happens without manual intervention and that the partitions are completely removed. What should you do?

Accepted Answer

Set a partition expiration on the table for 90 days.. Partition expiration is a table setting that automatically deletes partitions after a specified number of days. For ingestion-time partitioned tables, this is the simplest and most efficient way to remove old data and reduce storage costs. It requires no manual intervention and ensures partitions are dropped entirely.

Answer

Configure a Cloud Scheduler job that calls the BigQuery API to drop partitions older than 90 days.

Answer

Create a scheduled query that runs daily to delete rows older than 90 days using a DELETE statement.

Answer

Use a lifecycle rule on the BigQuery dataset to delete tables older than 90 days.