Ingesting and Processing the Data practice questions

Dataproc

Why it fails: Dataproc is for running Spark/Hadoop jobs; not designed for simple file transfers.

Cloud Data Fusion

Why it fails: Cloud Data Fusion is a managed ETL/ELT service, but it is overkill and not the simplest solution for a straightforward S3-to-BigQuery transfer.

Storage Transfer Service

Why it fails: Storage Transfer Service transfers data to Cloud Storage, not directly to BigQuery. Additional steps are needed.

Cloud Pub/Sub

Why it fails: Pub/Sub is a managed messaging service but not Kafka; you would need to adapt your Kafka producers/consumers.

Confluent Cloud on GCP

Why it fails: Confluent Cloud is a third-party managed Kafka offering, not a Google Cloud service.

Cloud Dataflow

Why it fails: Dataflow is a stream processing service, not a message broker.

Export data from Teradata to CSV files, upload to GCS using gsutil, then load into BigQuery.

Why it fails: This works but requires manual steps and is less efficient than the dedicated transfer service.

Use Dataproc to run a Spark job that reads from Teradata and writes to BigQuery.

Why it fails: A Spark job on Dataproc would read the entire 50 TB dataset into intermediate storage and then write it to BigQuery, introducing an unnecessary distributed processing layer that adds latency and resource overhead. This approach is tempting because Dataproc is often used for complex ETL transformations on large datasets, making it a correct choice when the data requires cleansing or reshaping before loading. However, for a one-time, high-bandwidth migration of raw historical data, a direct bulk load via the `bq` command-line tool or Storage Transfer Service avoids this intermediate processing, maximising throughput over the 10 Gbps link.

Use Transfer Appliance to ship the data offline.

Why it fails: Transfer Appliance requires days for physical shipping and manual racking, which contradicts the "one-time migration" and "high-speed network connection" (10 Gbps) in the stem — a direct network load can complete 50 TB in under 12 hours. It is tempting because Transfer Appliance is designed for very large datasets (hundreds of TB to PB) or low-bandwidth links, where shipping a physical device is the only viable path.

Flex Templates

Why it fails: Flex Templates are for packaging pipeline code, not runtime optimization.

Horizontal autoscaling

Why it fails: Horizontal autoscaling adds/removes workers but does not adjust individual worker size.

Streaming Engine

Why it fails: Streaming Engine offloads state to backend services but does not optimize worker resource allocation.

Dataflow

Why it fails: Dataflow runs Beam pipelines, not dbt models.

Cloud Build

Why it fails: Cloud Build is a CI/CD service, not for scheduling periodic dbt runs.

Cloud Run

Why it fails: Cloud Run can run dbt as a container, but it lacks built-in scheduling and versioning for multiple dbt models.

Pub/Sub

Why it fails: Pub/Sub is a messaging service but does not natively connect to Oracle for CDC without additional tooling.

Dataflow

Why it fails: Dataflow can process streams but requires a separate source connector for Oracle CDC.

Storage Transfer Service

Why it fails: Storage Transfer Service is for batch transfers of files between cloud and on-premises, not for real-time CDC.

Change the pipeline to use batch mode instead of streaming

Why it fails: Batch mode does not address real-time processing requirements and may increase latency.

Use a larger machine type for all workers

Why it fails: Uniform machine type may waste resources during low load and still fail under high load.

Increase the number of workers manually in the pipeline configuration

Why it fails: Manual scaling is not as responsive and may not handle load spikes efficiently.

Storage Transfer Service over the network

Why it fails: At 100 Mbps, transferring 50 TB would take over 45 days, which is not time-efficient.

BigQuery Data Transfer Service for Hadoop

Why it fails: BigQuery Data Transfer Service does not support Hadoop as a source.

gsutil cp with parallel composite uploads

Why it fails: gsutil is still limited by network bandwidth and would take too long.

Use a Classic Template and store the pipeline code in a Cloud Storage bucket

Why it fails: Classic Templates do not support custom Docker containers and require re-uploading the pipeline code for changes.

Use Cloud Composer to trigger the pipeline each time with updated parameters

Why it fails: Cloud Composer orchestrates workflows but does not address version-controlled template deployment.

Use Dataflow Prime and deploy the pipeline directly from the Apache Beam SDK

Why it fails: Dataflow Prime is a runtime optimization feature, not a template deployment mechanism.

Legacy streaming inserts

Why it fails: Legacy streaming inserts have a 1 MB limit per row and higher latency, and are being deprecated.

Pending mode

Why it fails: Pending mode is for writing data that is not immediately visible; it is used for batch loads, not real-time.

Committed mode

Why it fails: Committed mode writes data directly to BigQuery storage with strong durability, but at higher cost and latency than buffered mode.

Use Storage Transfer Service to copy Kafka logs from on-premises to GCS

Why it fails: Storage Transfer Service does not support Kafka logs and is not real-time.

Use Datastream to capture changes from Kafka

Why it fails: Datastream does not support Kafka as a source.

Replace the on-premises Kafka cluster with Google Cloud Pub/Sub

Why it fails: This requires significant changes to existing applications and is not minimal.

Datastream

Why it fails: Datastream does not support Teradata as a source.

Transfer Appliance

Why it fails: Transfer Appliance is for offline transfers, but the question mentions ongoing daily incremental loads which require online transfer.

Use Cloud Logging to capture logs and export to BigQuery via a sink.

Why it fails: This is for logs, not real-time clickstream data, and introduces minutes of delay.

Use Pub/Sub with Cloud Functions, writing each event directly via BigQuery legacy streaming inserts.

Why it fails: Cloud Functions have a timeout limit and are not ideal for high-throughput streaming; legacy streaming inserts have a 1-day quota and lack exactly-once semantics.

Use Datastream to stream clickstream data from Cloud SQL to BigQuery.

Why it fails: Datastream is for CDC from databases, not for web clickstream data.

Use Storage Transfer Service over the 1 Gbps link.

Why it fails: Storage Transfer Service moves data over the existing network path, consuming the 1 Gbps link for the entire 500 TB transfer. It is tempting because it is a managed service ideal for scheduled transfers between cloud storage buckets or HTTPS sources, where bandwidth is not the constraint and no physical shipment is involved.

Use gsutil cp in parallel with multiple threads.

Why it fails: Parallel gsutil threads still traverse the 1 Gbps link, saturating it for days and consuming the dedicated bandwidth. It is tempting because parallel composite uploads and multi-threaded copies dramatically accelerate transfers of many small files, which is the right approach when data volume is modest and network capacity is ample.

Use BigQuery Data Transfer Service for Hadoop.

Why it fails: BigQuery Data Transfer Service loads data into BigQuery datasets, not into Google Cloud Storage, and offers no Hadoop cluster source connector. It is tempting because it automates recurring ingestion from SaaS sources and Cloud Storage into BigQuery, which is correct when the destination is BigQuery rather than GCS.

Pub/Sub with a custom connector that polls the database every minute.

Why it fails: Polling can miss changes and adds load to the source database; it is not as efficient as log-based CDC.

Use BigQuery Data Transfer Service for PostgreSQL.

Why it fails: BigQuery Data Transfer Service does not support PostgreSQL as a source.

Use Dataflow with a JDBC IO connector to read from PostgreSQL.

Why it fails: JDBC IO is batch-oriented and can cause high load; it does not natively support CDC with logs.

Switch to Dataflow Prime with Vertical Autoscaling enabled.

Why it fails: Dataflow Prime provides vertical autoscaling (right-sizing workers) but does not specifically address autoscaling responsiveness better than Streaming Engine.

Increase the initial number of workers to handle the spike.

Why it fails: This provides a static baseline but does not improve autoscaling responsiveness to spikes; you cannot predict spikes.

Use Flex Templates instead of Classic Templates.

Why it fails: Flex Templates allow custom container images but do not directly affect autoscaling responsiveness.

Use Dataflow with Kafka IO instead of Dataproc.

Why it fails: The question specifically asks about migrating Kafka to Dataproc, not replacing it with Dataflow.

Use Dataproc with local SSDs for better performance, and enable autoscaling.

Why it fails: Local SSDs are ephemeral; if the instance terminates, data is lost. Kafka brokers require persistent storage unless replication is very high.

Use Dataproc with preemptible workers to reduce cost, and attach standard persistent disks.

Why it fails: Preemptible workers are not recommended for stateful streaming workloads like Kafka because they can be terminated at any time, causing data loss or rebalancing.

Increase the number of Dataflow workers to compensate for the latency.

Why it fails: More workers help but each element still incurs 500ms; batching is more effective.

Use Cloud Functions to call Cloud Run and write directly to BigQuery.

Why it fails: Cloud Functions have limited concurrency and would still be slow per invocation.

Use Dataflow's ParDo with synchronous calls to Cloud Run for each element.

Why it fails: Synchronous calls per element will cause high latency and reduce throughput significantly.

Shard the input into smaller reads using a BigQuery query.

Why it fails: Sharding might help but it changes the pipeline logic and may not fix the memory issue if the transform is memory-intensive.

Increase the disk size per worker.

Why it fails: Disk size is for storage, not memory; OOM is a memory issue.

Enable Dataflow Prime with vertical autoscaling.

Why it fails: Vertical autoscaling adjusts memory within the same machine family, but may not increase memory enough if the default type is already too low.