Data Preparation And Ingestion practice questions

Dataprep with rigid recipe

Why it fails: Dataprep recipes are generally rigid regarding schema.

Cloud Functions with manual schema mapping

Why it fails: Manual mapping requires code changes when the schema evolves.

Dataflow with fixed schema

Why it fails: Fixed schemas break when the source file changes.

Cloud Pub/Sub

Why it fails: Pub/Sub is for messaging, not file synchronization.

Dataflow

Why it fails: Dataflow is overkill and expensive for simple file synchronization.

Storage Transfer Service

Why it fails: This is better for large-scale datasets, not small daily transfers.

Dataflow Flex Templates

Why it fails: Flex Templates package pipeline code into a reusable Docker image, but they contain no scheduler; something external must still trigger each run. They are tempting because they standardise pipeline launches, and would be correct when you need parameterised, versioned pipeline artefacts rather than recurring execution.

Cloud Composer

Why it fails: Cloud Composer orchestrates pipelines via Airflow DAGs, but the question asks which service provides the scheduling trigger itself, not an orchestration layer wrapping it. Composer is tempting because its DAG schedules can invoke Dataflow jobs, and would be right for multi-step dependencies across services.

Cloud Functions

Why it fails: Cloud Functions executes short event-driven code and has no built-in cron scheduling for launching Dataflow jobs; it would need Cloud Scheduler to fire it. Functions suit lightweight event handlers, and would be correct when reacting to a single event rather than running a pipeline on a recurring timetable.

Pub/Sub

Why it fails: Pub/Sub is a messaging service that delivers events to subscribers; it holds no cron capability and cannot initiate a pipeline on a timetable by itself. It is tempting because Dataflow commonly reads from Pub/Sub, and would be right for triggering pipelines on message arrival rather than a fixed schedule.

Dataflow Job Inspector

Why it fails: This is for debugging.

IAM policies

Why it fails: This is for security.

Cloud Scheduler

Why it fails: This is for task triggering.

JSON

Why it fails: JSON is text-based and inefficient.

XML

Why it fails: XML is bulky and inefficient.

CSV

Why it fails: CSV is row-based and slower to parse.

Identity Aware Proxy

Why it fails: Identity-Aware Proxy brokers user-to-application HTTP access, not database egress from an on-premises host. It would suit protecting internal web apps, but reaching a legacy database behind strict egress controls requires Cloud VPN or Interconnect, which the database's firewall can permit outbound.

Public Internet

Why it fails: Routing database traffic over the public Internet violates the strict egress controls and exposes credentials in transit. Public Internet connectivity suits publicly reachable endpoints, but a legacy database with restrictive egress rules needs Cloud VPN or Dedicated Interconnect, giving a private, authenticated path.

Service Account

Why it fails: A service account is an identity, not a transport mechanism, so it cannot traverse strict egress controls on the legacy database. It is tempting because service accounts authenticate workloads to Google Cloud APIs, which is the right choice for granting a pipeline permission to read or write cloud resources once connectivity exists.

gsutil mv

Why it fails: Moving files relocates them, deleting the source, whereas ingesting small files requires copying them into Cloud Storage while leaving originals intact. It is tempting because gsutil mv efficiently renames or relocates objects within and between buckets, which suits reorganising existing cloud data rather than uploading new local files.

gsutil rsync

Why it fails: rsync synchronises directory trees, transferring only changed or missing files, so it targets bulk or incremental mirroring rather than a straightforward one-off upload of small files. It is tempting because rsync excels at keeping large local directories continuously aligned with a bucket, which is unnecessary for a simple ingest.

Direct copy-paste in UI

Why it fails: Not an ingestion method.

FTP upload

Why it fails: Not a GCP service.

SQL INSERT statements

Why it fails: Not recommended for ingestion.

Automatic virus scanning

Why it fails: Cloud Storage does not scan objects for viruses; it stores bytes and enforces IAM, encryption and retention. Scanning requires a separate service such as Cloud Storage scanning or a third-party tool. It tempts because security features are often bundled with storage, but virus detection is not a native capability.

Automatic data compression

Why it fails: Cloud Storage stores objects as supplied and does not compress them automatically; compression must be applied by the client or pipeline before upload. It tempts because compression reduces storage cost and transfer time, but that is a client-side choice, not a service benefit. Object-level gzip is optional, not automatic.

Auto-scaling SQL queries

Why it fails: This is BigQuery's job.

Local

Why it fails: BigQuery table types are native, external, and views (plus materialised views); 'Local' is not among them. It tempts because local tables exist in other warehouses, but BigQuery distinguishes native managed storage from external sources instead.

Virtual

Why it fails: BigQuery's valid table types are native, external, and views; 'Virtual' is not a BigQuery table type. It tempts because virtual tables appear in other database products, but BigQuery expresses that concept through views and external tables.

Cloud Pub/Sub

Why it fails: Pub/Sub is for streaming messaging, not bulk file transfer.

BigQuery Data Transfer Service

Why it fails: BigQuery Data Transfer Service schedules recurring loads into BigQuery from SaaS sources such as Google Ads or Cloud Storage; it cannot ingest on-premises files into Cloud Storage. It is tempting because it automates managed transfers, but that scenario involves analytics datasets, not bandwidth-constrained uploads of unstructured data.

Cloud Dataflow

Why it fails: Dataflow is a managed Apache Beam runner for transforming and processing data in pipelines, not a transfer mechanism for moving on-premises files into Cloud Storage. It is tempting because it handles large-scale ingestion, but that applies once data already resides in GCP or when streaming transformations are required.

Convert to Parquet before loading

Why it fails: While effective, it is not the most direct method if the CSV can be ingested directly.

Upload as a text file and use BigQuery federated queries

Why it fails: Federated queries read the CSV externally, leaving the JSON column as an opaque STRING that BigQuery cannot address with dot notation. It is tempting because it avoids a load step entirely, and would be correct for ad-hoc exploration of external data that needs no nested field extraction.

Use Cloud Storage transfer service

Why it fails: Storage Transfer Service moves or copies objects between locations and buckets; it performs no schema transformation, so the nested JSON column remains unparsed. It is tempting because it handles large-scale data movement reliably, and would be correct when migrating existing objects into Cloud Storage from another cloud or on-premises source.

Full load with a SQL filter

Why it fails: A full load with a SQL filter still reads the entire table each run; the filter reduces rows but does not track incremental changes, so repeated runs re-ingest everything. It is tempting for one-off filtered extracts, and would be correct when a complete refresh with a static predicate is genuinely required.

Dataflow template overrides

Why it fails: Dataflow template overrides configure runtime parameters for a launched pipeline; they do not provide the incremental row-tracking mechanism Cloud Data Fusion needs. It is tempting because Dataflow can read JDBC sources, and would be correct when you need custom pipeline logic executed on Dataflow rather than Fusion's built-in plugins.

Batch processing at high frequency

Why it fails: High-frequency batch scheduling still re-reads the whole table or a fixed window, so it cannot restrict ingestion to rows changed in the last hour. It suits predictable bulk loads where latency is acceptable. Incremental extraction needs a watermark column, which the stem's requirement implies.

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

Why it fails: This design needs a Cloud Function with code to pull and transform messages, plus a scheduler, and it introduces polling latency and duplicate-delivery handling concerns. It is more moving parts than a managed subscription and violates the no-pipeline-code requirement. While it can work, it is not the lowest-overhead option for streaming logs into BigQuery.

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

Why it fails: A Dataflow streaming pipeline can move Pub/Sub data into BigQuery, but it requires you to develop and maintain pipeline code plus manage the Dataflow job and its workers. The scenario explicitly demands the least operational overhead with no pipeline code, so this approach adds avoidable engineering effort. It is a valid pattern, but it does not meet the stated requirement.

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

Why it fails: Pub/Sub does not offer a native export-to-Cloud-Storage sink; you would need a Dataflow template or custom code to write messages to objects. Even then, the recurring load job adds scheduling and file-management complexity, and data lands in BigQuery only after each load. This indirect path fails the requirement for minimal operational overhead and near-real-time queryability.

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

Why it fails: Autodetect can infer schema and may handle some formatting, but it does not reliably manage embedded commas within quoted fields. It also does not skip the header row automatically. Relying on autodetect for a 2 TB file with complex quoting risks schema errors and data corruption, making it unsuitable for this precise requirement.

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

Why it fails: Skipping the header row and setting the delimiter to comma is necessary but not sufficient. Without specifying a quote character, BigQuery may misinterpret embedded commas inside quoted fields as delimiters, causing column misalignment or load failures. The default quote character is double quote, but explicitly setting it ensures correctness when the file uses quoting.

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

Why it fails: Allowing quoted newlines is useful if fields span multiple lines, but the scenario only mentions embedded commas, not newlines. Without skipping the header row, the first row would be loaded as data, causing type errors or unwanted records. Additionally, the quote character is not specified, so embedded commas may still break parsing if the default is not applied.

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

Why it fails: Compressing the file to gzip reduces storage and network transfer, but BigQuery cannot parallelize reads of a single gzip file, so the load will be limited to one worker and will be slower. Also, gzip is not splittable, which defeats the purpose of speeding up a 4 TB load. This approach does not minimize load time.

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

Why it fails: Using bq load with --autodetect can work, but for a 4 TB uncompressed CSV, the load will be slow and costly because BigQuery must scan the entire file to infer the schema and parse the data. Autodetect also may misinterpret types. It does not address the size or compression, so it is not the best first step to optimize speed and cost.

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

Why it fails: The BigQuery Data Transfer Service is designed for scheduled, recurring transfers from sources like Google Ads, YouTube, or Cloud Storage, but it is not intended for one-time large file loads. It also does not optimize the load speed for a single large CSV. For a one-time 4 TB load, it adds unnecessary complexity and potential cost.

Cloud Data Fusion

Why it fails: Cloud Data Fusion is a fully managed data integration service for building pipelines, but it is not a direct bulk copy tool. Using it for a one-time 500 GB transfer would require designing a pipeline, which adds unnecessary complexity and cost. It is better suited for ongoing ETL/ELT processes rather than simple data movement.

gsutil cp

Why it fails: gsutil cp can copy files from a local file system to Cloud Storage, but it is not optimized for HDFS and would require mounting HDFS or exporting files first. For many small files, gsutil may be slow due to per-file overhead and lacks parallelization across a cluster. It is not the recommended tool for large-scale Hadoop migrations.

Storage Transfer Service

Why it fails: Storage Transfer Service is designed for transferring data between cloud storage systems or from HTTP/HTTPS sources, but it does not directly support Hadoop Distributed File System (HDFS) as a source. It is better suited for moving data from other object stores or on-premises file systems exposed via HTTP, not for HDFS clusters.

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

Why it fails: Sliding windows with a 24-hour duration and 1-hour period would produce overlapping windows, causing duplicate processing of events. Batch mode is unsuitable for streaming data from Pub/Sub and does not support exactly-once processing for unbounded data. This approach fails to handle late data correctly and does not meet the exactly-once requirement.

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

Why it fails: Session windows group events based on gaps of inactivity, which is not appropriate for fixed hourly windows. A 24-hour gap would merge events across long periods, losing the intended hourly aggregation. While streaming engine supports exactly-once, the windowing strategy does not correctly handle late data within hourly windows.

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

Why it fails: A global window accumulates all events into a single window, which prevents per-hour aggregation and makes it impossible to correctly assign late data to specific hourly windows. Batch mode cannot process streaming data from Pub/Sub in real time and does not provide exactly-once semantics for unbounded data.

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

Why it fails: This approach violates the strict network egress controls because it requires uploading over the public internet. It also introduces manual steps and potential security risks, and may not be reliable for large datasets. It does not provide a secure, automated pipeline.

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

Why it fails: BigQuery Omni allows querying data in other clouds (AWS, Azure), not on-premises Oracle. Federated queries require a connection to an external database, but BigQuery Omni does not support on-premises sources. This approach is technically infeasible and does not address the egress constraints.

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

Why it fails: BigQuery Data Transfer Service does not support direct on-premises Oracle sources; it is designed for SaaS applications and other Google Cloud services. Using it over the public internet would also violate egress controls and is not a supported approach for this scenario.

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

Why it fails: Hive partitioning prunes directories by key, which reduces bytes scanned, but object versioning keeps every overwritten generation and does not make BigQuery ignore superseded files. Queries would still see the old and new object generations, so duplicates persist. Versioning is a recovery feature, not a deduplication mechanism, and it actually increases the number of objects the external table can match.

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

Why it fails: Maximum staleness applies to cached metadata for native tables, and require partition filter only forces a predicate on partitioned tables. Neither option removes older object versions or prevents duplicate event identifiers across files, so queries would still return the same event multiple times. These settings govern access patterns, not content freshness or deduplication.

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

Why it fails: A native table with DISTINCT deduplicates by event identifier but requires a load and a recurring query, which adds latency and cost and does not stop the external scan from seeing stale files before deduplication. It also loses non-identical fields if two records share an identifier but carry different payloads, so it can silently drop legitimate corrections.