GCP-ADP Data Preparation And Ingestion Practice Test 2 — 15 Questions

Question 1

You need to load a 4 TB uncompressed CSV file from Cloud Storage into BigQuery. The file has a header row and uses comma delimiters. You want the load to complete as quickly as possible and minimize cost. What should you do first?

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

Accepted Answer

Split the CSV into multiple files (e.g., 100 files of ~40 GB each), compress them with snappy or gzip, and load them in parallel using a wildcard URI.. Splitting a large CSV into multiple smaller files and compressing them with a splittable codec like snappy enables BigQuery to load data in parallel, which significantly reduces load time. Using a wildcard URI allows a single load job to process all files. This approach is faster and more cost-effective than loading a single large file or using gzip, which is not splittable.

Answer

Compress the CSV to gzip, then load it into BigQuery using the bq load command with --autodetect.

Answer

Load the CSV directly into BigQuery using the bq load command with --autodetect.

Answer

Use the BigQuery Data Transfer Service to schedule a recurring load from Cloud Storage.

Question 2

You need to copy a 500 GB dataset from an on-premises Hadoop cluster to Cloud Storage for a one-time analytics project. The data consists of many small files. Which Google Cloud tool should you use to transfer the data efficiently?

Accepted Answer

DistCp. DistCp is built for efficient, parallel copying of large datasets from HDFS to Cloud Storage. It uses MapReduce to distribute the transfer, handling many small files well. Other tools either lack direct HDFS support or are not optimized for bulk parallel transfers, making DistCp the right choice for this one-time migration.

Answer

Cloud Data Fusion

Answer

gsutil cp

Answer

Storage Transfer Service

Question 3

You are preparing a Dataflow pipeline that reads from a Pub/Sub subscription and writes to BigQuery. The pipeline must handle late-arriving data that can arrive up to 24 hours after the event timestamp. You need to ensure that all data is processed exactly once and that late data is included in the correct window. Which approach should you take?

Accepted Answer

Use a fixed window of 1 hour with allowed lateness of 24 hours and set the pipeline to use streaming engine.. The requirement is to process streaming data with late arrivals up to 24 hours and ensure exactly-once processing. Fixed windows with allowed lateness of 24 hours correctly assign late events to their original window, and streaming engine provides exactly-once semantics for streaming pipelines writing to BigQuery. Other windowing types or batch mode fail to meet the latency and correctness requirements.

Answer

Use a sliding window of 24 hours with a 1-hour period and set the pipeline to use batch mode.

Answer

Use a session window with a gap duration of 24 hours and set the pipeline to use streaming engine.

Answer

Use a global window with allowed lateness of 24 hours and set the pipeline to use batch mode.

Question 4

You are designing a data pipeline that ingests data from an on-premises Oracle database into BigQuery. The database has strict network egress controls and cannot be accessed directly from the public internet. You need to ensure secure and reliable data transfer with minimal latency. Which TWO approaches should you use? (Choose two.)

Accepted Answer

Use Cloud VPN to establish a secure tunnel between the on-premises network and Google Cloud, then use Cloud Data Fusion to extract and load data. Both Cloud VPN and Cloud Interconnect provide private connectivity to on-premises, satisfying egress controls. Cloud Data Fusion and Dataflow offer managed ETL/ELT with Oracle connectors and BigQuery sinks. These combinations ensure secure, reliable, low-latency ingestion without public internet exposure.

Answer

Export the Oracle database to CSV files, upload them to Cloud Storage via the public internet, and load into BigQuery

Answer

Set up a Cloud SQL federated query to the Oracle database using BigQuery Omni

Answer

Use the BigQuery Data Transfer Service with an on-premises connector that pulls data over the public internet

Question 5

A streaming pipeline writes raw JSON events to a Cloud Storage bucket every few minutes. Analysts query this data with BigQuery external tables, but each query scans the entire history and returns duplicate-looking records because the same event identifier appears in multiple files. You want queries to read only new files and to avoid counting an event twice when a corrected file overwrites an earlier one. Which combination of features should you configure?

Accepted Answer

BigQuery external table with hive partitioning and Cloud Storage object metadata (file modification time) freshness. Two distinct problems exist: scanning all history and reading duplicate events from superseded files. Hive partitioning cuts the scan to relevant partitions, while the object metadata freshness option makes BigQuery ignore files older than the newest matching object, so corrected files replace earlier ones rather than being read together. Native loading with DISTINCT, versioning, and staleness settings do not solve both problems for external data.

Answer

BigQuery external table with hive partitioning enabled and Cloud Storage object versioning

Answer

BigQuery external table with a maximum staleness and a require partition filter setting

Answer

A BigQuery native table loaded with a scheduled query using SELECT DISTINCT on the event identifier

Question 6

You are ingesting streaming events into Pub/Sub. Downstream, you need to write each event to Cloud Storage in Parquet format with exactly-once semantics and minimal latency. You want to avoid managing infrastructure. What should you use?

Accepted Answer

Cloud Dataflow with the Pub/Sub to Cloud Storage template and a 1-minute fixed window.. Dataflow is a fully managed, serverless service that supports exactly-once processing and can write Parquet files to Cloud Storage with low latency using a short window. The Pub/Sub to Cloud Storage template is purpose-built for this scenario and handles sharding, naming, and deduplication. Other options either require infrastructure management, introduce latency, or lack exactly-once guarantees.

Answer

Cloud Dataflow with the Pub/Sub to BigQuery template, then export the BigQuery table to Cloud Storage in Parquet format.

Answer

Cloud Dataproc with a Spark Streaming job that reads from Pub/Sub and writes Parquet to Cloud Storage.

Answer

Cloud Pub/Sub with a push subscription to a Cloud Function that writes each event as a separate Parquet file.

Question 7

You need to copy a 500 GB dataset from an on-premises NFS server to Cloud Storage. Your network bandwidth is limited to 100 Mbps, and the transfer must complete within one week. You want a fully managed service that requires minimal setup. What should you use?

Accepted Answer

Storage Transfer Service with a transfer agent installed on-premises.. For on-premises data transfer to Cloud Storage, Storage Transfer Service with a transfer agent is the recommended fully managed solution. The agent can be installed on a machine with access to the NFS server, and the service handles scheduling, retries, and parallelism. This meets the requirements of minimal setup and completion within the deadline, unlike manual tools or FUSE mounts.

Answer

gsutil cp -r command from the NFS server.

Answer

Storage Transfer Service with an agentless transfer.

Answer

Cloud Storage FUSE mount on the NFS server.

Question 8

You need to copy a 500 GB dataset from an on-premises NFS server to Cloud Storage. The server has a 1 Gbps network connection, and the transfer must be completed within a week. You want a fully managed service that handles retries and parallelism automatically. Which Google Cloud service should you use?

Accepted Answer

Storage Transfer Service. Storage Transfer Service is the managed service for moving large datasets from on-premises or other cloud sources into Cloud Storage. It provides automatic parallelism, retry logic, and scheduling, and it supports NFS sources. For a 500 GB dataset over 1 Gbps, it can complete the transfer quickly and reliably. gsutil cp is manual, Cloud Data Fusion is for ETL pipelines, and Transfer Appliance is for offline transfers where bandwidth is inadequate.

Answer

gsutil cp -r

Answer

Transfer Appliance

Answer

Cloud Data Fusion

Question 9

You are designing a data ingestion pipeline that reads CSV files from Cloud Storage, applies transformations, and writes to BigQuery. The files arrive every hour, and you need to ensure that only new files are processed and that the pipeline can handle late-arriving data. Which Dataflow feature should you use to track which files have been processed?

Accepted Answer

Use the Watch transform from Apache Beam's FileIO to continuously watch for new files and process them exactly once.. The Watch transform in Apache Beam's FileIO is designed to continuously monitor a directory for new files and process them as they arrive. It can be configured with a deduplication mechanism to ensure each file is processed once, even with late-arriving data. This is the appropriate Dataflow feature for the scenario.

Answer

Use Pub/Sub notifications for Cloud Storage to trigger a Cloud Function that starts a Dataflow job for each new file.

Answer

Use the BigQuery Storage Write API with change data capture (CDC) to ingest files as they arrive.

Answer

Use the FileIO match and read transforms with a custom metadata-based deduplication.

Question 10

You are using Cloud Data Fusion to ingest data from an on-premises Oracle database into BigQuery. The database is behind a firewall and only allows outbound connections to specific Google Cloud IP ranges. Which component should you deploy to enable secure connectivity?

Accepted Answer

Cloud Data Fusion private IP. Deploying Cloud Data Fusion with a private IP allows the instance to communicate with on-premises systems over a private network, such as through Cloud VPN or Interconnect, without exposing the database to the internet. This is the correct component to enable secure connectivity for the Data Fusion pipeline.

Answer

Cloud VPN

Answer

Cloud Interconnect

Answer

VPC Service Controls

Question 11

You need to ingest data from an on-premises Oracle database into BigQuery daily. The database is behind a firewall and cannot accept inbound connections. You want a fully managed, serverless solution that minimizes operational overhead. (Choose two.)

Accepted Answer

Use a Dataflow pipeline with the JDBC-to-BigQuery template, connecting via a VPN.. Cloud Data Fusion and Dataflow are both fully managed, serverless services that can connect to on-premises Oracle through a VPN or Cloud Interconnect and write to BigQuery. Data Fusion offers a graphical pipeline with an Oracle connector, while Dataflow provides a JDBC-to-BigQuery template. Both minimize operational overhead. The other options either lack Oracle connectivity, require unsupported engines, or are not designed for direct database loads.

Answer

Use the bq command-line tool to run a load job directly from the Oracle database.

Answer

Use BigQuery Data Transfer Service with a VPN or Cloud Interconnect connection.

Answer

Use Cloud SQL for Oracle as a replication target, then federate queries to BigQuery.

Question 12

A retail company wants to continuously copy new objects from a Cloud Storage bucket into a BigQuery table for near-real-time dashboards. The source files are written by an external partner and are not guaranteed to be consistently named. The team prefers a fully managed, configuration-driven solution with no custom code. What should they use?

Accepted Answer

BigQuery Data Transfer Service with a Cloud Storage transfer configuration.. BigQuery Data Transfer Service provides managed, scheduled ingestion from Cloud Storage into BigQuery using configuration rather than code. It can discover new files by URI pattern and load them on a recurring schedule, which fits the near-real-time dashboard requirement. The other choices either move data only between storage systems or require custom code and additional infrastructure.

Answer

A Pub/Sub notification on the bucket that triggers a Cloud Run function to call the BigQuery load API.

Answer

Cloud Storage Transfer Service with a recurring transfer job.

Answer

A Cloud Composer DAG that runs a gsutil copy command on a cron schedule.

Question 13

You need to ingest data from a local file system into Cloud Storage on a recurring basis. The data is about 10 GB per day and must be encrypted in transit. Which tool should you use?

Accepted Answer

gsutil cp with the -m option to copy files over HTTPS.. gsutil cp with the -m option is the most straightforward and efficient way to transfer files from a local file system to Cloud Storage. It uses HTTPS for encryption in transit and supports parallel uploads. Other methods are either not designed for recurring large transfers or require additional infrastructure.

Answer

Cloud Data Fusion with a local file system plugin.

Answer

Storage Transfer Service with a POSIX file system source.

Answer

Google Cloud Console drag-and-drop upload.

Question 14

You have a Cloud Storage bucket with millions of small JSON files. You need to query this data using SQL in BigQuery without loading it, and you want to minimize cost. What should you do?

Accepted Answer

Create a BigQuery external table over the Cloud Storage bucket and query it directly.. BigQuery external tables allow you to query data in Cloud Storage directly without loading, avoiding BigQuery storage costs and load job overhead. For millions of small JSON files, external tables can read them in parallel. This is the most cost-effective and direct method for the scenario. Other options involve loading or transforming data, which adds cost and latency.

Answer

Use Cloud Dataprep to transform the JSON files and write them to BigQuery.

Answer

Load all JSON files into a BigQuery native table using a load job, then query the table.

Answer

Create a Cloud SQL instance, import the JSON files, and query using federated queries from BigQuery.

Question 15

A healthcare analytics group receives daily CSV extracts from an on-premises billing system. The files land in a Cloud Storage bucket, and a Dataprep by Trifacta job is used to clean and standardize them before loading into BigQuery. Compliance requires that the original CSV files be retained unchanged for seven years, and the team wants to minimize storage cost for those originals. What should they do?

Accepted Answer

Apply a Bucket Lock retention policy with a seven-year retention period to the bucket holding the original CSVs.. Bucket Lock applies a retention policy that prevents deletion or overwriting of objects for a specified period, which is the Cloud Storage mechanism for compliance retention. Pairing it with a cold storage class addresses the cost goal while preserving the originals unchanged. Lifecycle deletion, Autoclass, and access controls do not enforce the seven-year immutability requirement.

Answer

Configure Object Lifecycle Management to transition the CSVs to Archive storage and delete them after 2555 days.

Answer

Move the original CSV files to a bucket with an Autoclass-enabled storage class after the Dataprep job completes.

Answer

Enable uniform bucket-level access and grant the Dataprep service account read-only permissions.