1: Data Preparation and Ingestion

Learning Objectives

By the end of this chapter, you will be able to:

• Differentiate between different data manipulation methodologies (e.g., ETL, ELT, ETLT)
• Choose the appropriate data transfer tool (e.g., Storage Transfer Service, Transfer Appliance)
• Assess data quality
• Conduct data cleaning (e.g., Cloud Data Fusion, BigQuery, SQL, Dataflow)
• Distinguish the format of the data (e.g., CSV, JSON, Apache Parquet, Apache Avro, structured database tables)
• Choose the appropriate extraction tool (e.g., Dataflow, BigQuery Data Transfer Service, Database Migration Service, Cloud Data Fusion)
• Select the appropriate storage solution (e.g., Cloud Storage, BigQuery, Cloud SQL, Firestore, Bigtable, Spanner, AlloyDB)
• Choose the appropriate data storage location type (e.g., regional, dual-regional, multi-regional, zonal)
• Classify use cases into having structured, unstructured, or semi-structured data requirements
• Load data into Google Cloud storage systems using the appropriate tool (e.g., gcloud and BQ CLI, Storage Transfer Service, BigQuery Data Transfer Service, client libraries)

Executive Summary

• The choice between ETL, ELT, and ETLT is driven by where the transformation runs (external engine versus warehouse), how raw the landing zone needs to be, and the cost and latency profile of the target system. BigQuery's separation of storage and compute makes ELT the dominant pattern, with ETL reserved for cases where the warehouse must never hold raw data.
• Transfer tooling on Google Cloud splits along three axes: online versus offline, file-based versus database-based, and one-time versus continuous. Storage Transfer Service moves objects across clouds and on-premises file systems over the network, Transfer Appliance handles offline shipments at petabyte scale, and Database Migration Service handles homogeneous and heterogeneous database moves with change data capture.
• Storage selection follows the data model first and the access pattern second. Structured relational workloads land in Cloud SQL, AlloyDB, or Spanner. Analytical workloads land in BigQuery. Wide-column key access lands in Bigtable. Document or mobile-first workloads land in Firestore. Unstructured blobs land in Cloud Storage.
• Location type controls write latency, read availability during regional events, and egress cost. Zonal scopes (used by Persistent Disk and some VM-attached resources) trade cost for single-zone failure exposure. Regional storage is the default for most workloads. Dual-region and multi-region storage trade cost for stronger availability and lower cross-region read latency.

Assumptions

• Service names follow the official product naming used in the Google Cloud documentation at the latest stable revision. Where a brand is in transition, the chapter uses the name that appears in the official exam guide.
• All gcloud, bq, and gsutil commands assume the operator has already authenticated with gcloud auth login and selected an active project with gcloud config set project.
• Sample buckets, projects, and datasets use the placeholder names acme-data, acme-raw, and acme_warehouse throughout.