GCP-ADP Data Preparation And Ingestion — 40 Questions

Question 1

You need to load CSV files from GCS to BigQuery. The schema changes frequently. Which approach handles schema evolution automatically?

Accepted Answer

BigQuery Load job with auto-detect. BigQuery load jobs with schema auto-detect inspect the CSV header and sample rows to infer column names and types, and when combined with the load job's schema update options they can add new columns automatically as the source schema evolves. This is the only listed approach that adapts to frequent schema changes without code changes. Dataflow, Dataprep, and Cloud Functions all require the schema to be defined or mapped explicitly, so they break when columns are added or renamed.

Answer

Dataprep with rigid recipe

Answer

Cloud Functions with manual schema mapping

Answer

Dataflow with fixed schema

Question 2

You need to ingest small files from an on-premises server to Cloud Storage once daily. Which tool is most cost-effective and simple for this?

Accepted Answer

gsutil rsync. gsutil rsync is the standard tool for simple, scheduled file synchronization between local systems and GCS.

Answer

Cloud Pub/Sub

Answer

Dataflow

Answer

Storage Transfer Service

Question 3

You want to run a Dataflow pipeline on a schedule. What should you use?

Accepted Answer

Cloud Scheduler. Cloud Scheduler is a fully managed cron service that can trigger HTTP endpoints, Pub/Sub topics, or App Engine handlers on a defined schedule. To run a Dataflow pipeline on a schedule, you configure Cloud Scheduler to invoke the Dataflow template launch API (or a Cloud Function that launches it) at the desired intervals. This is the canonical GCP pattern for scheduled batch Dataflow jobs.

Answer

Dataflow Flex Templates

Answer

Cloud Composer

Answer

Cloud Functions

Answer

Pub/Sub

Question 4

You are cleaning data in Dataprep and realize you need to change the format of a date column. Which feature should you use?

Accepted Answer

Recipe Builder. Dataprep's 'Transform' interface allows you to apply functions to columns to change their format.

Answer

Dataflow Job Inspector

Answer

IAM policies

Answer

Cloud Scheduler

Question 5

What is the recommended format for data storage in BigQuery to maximize performance?

Accepted Answer

Avro. Columnar formats like Avro or Parquet are optimized for BigQuery's columnar storage architecture.

Answer

JSON

Answer

XML

Answer

CSV

Question 6

You need to ingest data from a legacy database into Google Cloud. The database has strict network egress controls. How should you connect?

Accepted Answer

Cloud VPN. Cloud VPN is the correct choice because it establishes an encrypted tunnel over the public internet between the on-premises network and Google Cloud, which satisfies the legacy database's strict egress controls without requiring the database to expose itself publicly. It is a standard, supported connectivity option for hybrid ingestion scenarios. The VPN tunnel allows the database to push data to a private GCP endpoint while keeping all traffic encrypted and controlled.

Answer

Identity Aware Proxy

Answer

Public Internet

Answer

Service Account

Question 7

You need to ingest small files into Cloud Storage using a command-line tool. Which command is most appropriate?

Accepted Answer

gcloud storage cp. Both `gcloud storage cp` and `gsutil cp` are correct because they are command-line tools designed for copying files to and from Cloud Storage. `gcloud storage cp` is the modern, recommended tool that offers faster performance and is part of the gcloud CLI, while `gsutil cp` is the legacy tool that still works but is deprecated. For ingesting small files, either command is appropriate, but `gcloud storage cp` is preferred for new projects.

Answer

gsutil mv

Answer

gsutil rsync

Question 8

Which TWO of the following are valid ways to ingest data into BigQuery?

Accepted Answer

Streaming API. Option A, the Streaming API, is correct because BigQuery provides the legacy tabledata.insertAll streaming API and the newer Storage Write API for ingesting individual records in real time with low latency. Option E, Batch Load jobs, is correct because BigQuery supports load jobs that ingest data in bulk from sources such as Google Cloud Storage, local files, or other Google services (e.g., via bq load or the jobs.insert API). Option B (direct copy-paste in UI) is not a supported ingestion mechanism for loading data into BigQuery tables. Option C (FTP upload) is not a BigQuery ingestion method, as BigQuery does not accept FTP as a data source. Option D (SQL INSERT statements) is not a valid bulk ingestion path for BigQuery, since DML INSERT is limited and not intended as a primary data-loading method.

Answer

Direct copy-paste in UI

Answer

FTP upload

Answer

SQL INSERT statements

Question 9

Which TWO of the following are benefits of using Cloud Storage?

Accepted Answer

Global availability. Option B (Global availability) is correct because cloud storage services such as Amazon S3, Azure Blob Storage, and Google Cloud Storage replicate data across multiple regions and availability zones, allowing objects to be accessed from anywhere in the world via HTTP/HTTPS endpoints with low latency. Option D (High durability) is correct because these services are designed for extreme durability—for example, Amazon S3 Standard offers 99.999999999% (11 nines) durability by redundantly storing data across multiple facilities, protecting against hardware failures and data loss. The unmarked options do not belong: automatic virus scanning (A) is not an inherent feature of cloud storage and typically requires separate services like antivirus integrations or malware scanning tools; automatic data compression (C) is not a default benefit, as compression must be applied by the client or via specific configurations; and auto-scaling SQL queries (E) is a database capability (e.g., Amazon Aurora or Azure SQL) rather than a benefit of object/blob storage.

Answer

Automatic virus scanning

Answer

Automatic data compression

Answer

Auto-scaling SQL queries

Question 10

Which THREE of the following are valid BigQuery table types?

Accepted Answer

Native. Option A (Native) is correct because BigQuery's standard, internally managed tables are referred to as native tables, where data is stored in BigQuery's columnar storage. Option B (Partitioned) is correct because partitioning is a valid table type/design in BigQuery, where a table is divided into segments (e.g., by ingestion time, DATE/TIMESTAMP column, or integer range) to improve query performance and cost control. Option E (External) is correct because BigQuery supports external tables that query data stored outside BigQuery, such as in Cloud Storage, BigLake, or other federated sources, without loading it into native storage. Option C (Local) is not a BigQuery table type, as BigQuery is a fully managed, serverless cloud warehouse with no 'local' table concept. Option D (Virtual) is not a BigQuery table type; while BigQuery has logical views and authorized views, these are not categorized as 'virtual' tables in the product's table-type taxonomy.

Answer

Local

Answer

Virtual

Question 11

You need to ingest large amounts of unstructured data into Cloud Storage from an on-premises data center with limited bandwidth. Which service should you choose to ensure the most cost-effective and secure transfer?

Accepted Answer

Storage Transfer Service. Storage Transfer Service is designed for large-scale, online data movement into Cloud Storage from on-premises or other cloud sources. It supports scheduled transfers, bandwidth throttling, and encryption in transit, making it the most cost-effective and secure choice for limited-bandwidth on-premises ingestion. It avoids the cost and complexity of running a custom pipeline.

Answer

Cloud Pub/Sub

Answer

BigQuery Data Transfer Service

Answer

Cloud Dataflow

Question 12

You have a large CSV file in Cloud Storage that needs to be loaded into BigQuery. The file contains a nested JSON structure in one column. How should you best prepare this data?

Accepted Answer

Use a BigQuery load job with autodetect. Using BigQuery's native JSON support or Dataflow allows parsing nested structures during the load process.

Answer

Convert to Parquet before loading

Answer

Upload as a text file and use BigQuery federated queries

Answer

Use Cloud Storage transfer service

Question 13

You are configuring a Cloud Data Fusion pipeline to ingest data from an external SQL database. You need to ensure that only rows modified within the last hour are ingested. Which feature should you use?

Accepted Answer

Change Data Capture (CDC). Change Data Capture (CDC) in Cloud Data Fusion is designed to capture only the rows that have changed since the last pipeline run, using database transaction logs or timestamp-based tracking. For a requirement to ingest only rows modified within the last hour, CDC provides the precise incremental mechanism, avoiding the overhead of scanning the entire table. It supports plugins like the 'SQL Server CDC' or 'Oracle CDC' sources that read change tables or redo logs, enabling near-real-time ingestion. This is the correct feature because it directly addresses incremental, time-bound data extraction.

Answer

Full load with a SQL filter

Answer

Dataflow template overrides

Answer

Batch processing at high frequency

Question 14

Your company ingests a continuously growing stream of application logs into Pub/Sub. Analysts need to query the most recent 30 days of logs in BigQuery with standard SQL, but they do not want to manage any pipeline code or infrastructure. You must implement the ingestion with the least operational overhead. What should you do?

Accepted Answer

Create a BigQuery subscription on the Pub/Sub topic with a write metadata option, and set the topic's message retention to 30 days.. A BigQuery subscription is a native Pub/Sub push target that streams messages straight into a BigQuery table with no code to write or infrastructure to run. Analysts can query the table with standard SQL and filter on the publish-time column added by write metadata to cover the last 30 days. It is the only listed approach that meets the streaming, no-code, low-overhead constraints.

Answer

Schedule a Cloud Scheduler job that invokes a Cloud Function to pull messages from the Pub/Sub topic and insert them into BigQuery.

Answer

Run a Dataflow streaming pipeline that reads from the Pub/Sub topic and writes to BigQuery using a windowed write.

Answer

Export the Pub/Sub topic to a Cloud Storage bucket, then load the files into BigQuery with a recurring load job.

Question 15

You are ingesting a 2 TB CSV file from Cloud Storage into BigQuery using the bq load command. The file has a header row and uses comma delimiters, but some fields contain embedded commas within double quotes. You need to ensure the data loads correctly without errors. What should you specify in your load command?

Accepted Answer

Set --skip_leading_rows=1 and --quote='"'. The load must skip the header row and correctly interpret quoted fields containing commas. Explicitly setting the quote character to double quote ensures that commas inside quotes are not treated as delimiters. While BigQuery defaults to double quote, specifying it removes ambiguity and guarantees correct parsing for files with embedded commas.

Answer

Set --autodetect=true and --field_delimiter=','

Answer

Set --skip_leading_rows=1 and --field_delimiter=','

Answer

Set --allow_quoted_newlines=true and --field_delimiter=','

Question 16

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?

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 17

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 18

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 19

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 20

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