Prepare and Ingest Data
What this domain tests
Prepare and ingest data covers moving data from sources into Google Cloud storage and analytical engines with the right format, schema, and delivery mode. Expect questions on batch versus streaming, Cloud Storage landing zones, Pub/Sub, Datastream, Dataflow, and BigQuery load options.
Core rules
- Choose batch when freshness is hours or days and the source is a file or database snapshot; choose streaming when dashboards or alerts need seconds-level latency.
- Cloud Storage is the usual landing zone. Keep raw data immutable, use separate buckets for raw, curated, and temporary data, and set lifecycle rules.
- BigQuery batch loads accept CSV, newline-delimited JSON, Avro, Parquet, and ORC. Prefer Avro or Parquet for nested or columnar data; CSV is simple but fragile around commas and types.
- For continuous database replication use Datastream, which captures change data capture (CDC) from sources such as MySQL and PostgreSQL into Cloud Storage or BigQuery. Datastream does not transform business logic.
- For high-throughput streaming into BigQuery, use the Storage Write API rather than legacy streaming inserts. Use Dataflow when you need windowing, enrichment, deduplication, or dead-letter handling.
- Define schemas explicitly. Auto-detection is convenient for exploration but can infer a field as STRING on one load and INTEGER on the next, causing conflicts.
- Partition large BigQuery tables by date or ingestion time and cluster on common filter columns to reduce bytes scanned.
Worked example
Clickstream JSON events arrive in Pub/Sub and must appear in BigQuery within one minute for a real-time dashboard. The events include a nullable user_id and occasional malformed records. Reasoning: Pub/Sub handles ingestion buffering; Dataflow provides a streaming pipeline with parsing, validation, and windowing; the BigQuery Storage Write API gives efficient writes and supports exactly-once semantics in Dataflow. Malformed records go to a Pub/Sub dead-letter topic, not silently dropped. The target table is partitioned by event_date and clustered by event_type. A batch load job would be wrong because the SLA is seconds, and direct streaming inserts would make retries and deduplication harder.
Traps
- Using a batch load for a low-latency requirement, or streaming for a nightly file drop.
- Letting schema auto-detection drift across files.
- Assuming Datastream performs joins or cleansing; it replicates.
- Forgetting that BigQuery partition filters must reference the partition column directly.
- Loading CSV with embedded commas without quoting rules.