Memory hook: Shape, clean, move, store.
Must remember
- ETL transforms before loading; ELT loads before transforming in the destination; ETLT can split work across both. Choose based on privacy, target capabilities and transformation cost.
- Profile nulls, duplicates, invalid types, outliers and inconsistent units. Preserve raw data and record transformation rules so cleaning is reproducible.
- CSV is simple but weakly typed; JSON handles nested records; Avro is row-oriented with a schema; Parquet is columnar and efficient for analytical column scans.
- Cloud Storage holds objects; BigQuery serves analytics; Cloud SQL serves conventional relational applications; Spanner serves horizontally scalable relational workloads; Firestore serves document applications; Bigtable serves high-throughput key-based access.
- Storage Transfer Service moves supported object/file sources; BigQuery Data Transfer Service schedules supported analytics imports; Database Migration Service handles supported database migrations. A physical Transfer Appliance addresses very large transfers with constrained bandwidth.
- Use Dataflow for Beam processing, Data Fusion for visual integration, and SQL for warehouse transformations. Match locations across storage, datasets and processing to residency, availability and transfer-cost requirements.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Join and aggregate warehouse data | BigQuery with ELT when raw loading is permitted. |
| Terabytes of scan-heavy column data | Parquet in object storage or native BigQuery tables for analytical access. |
Traps
- BigQuery is not a drop-in low-latency transactional database.
- Moving bytes successfully does not prove the data is complete or correctly typed.
Active recall
1. How do you verify a transfer?
Compare counts, checksums where applicable, schema and representative records.
2. Where do nested event records fit?
JSON or Avro are common interchange formats; model them deliberately when loading analytics tables.
3. When is ETL preferable?
When data must be transformed or sensitive fields removed before entering the destination.
4. What does bq help operate?
BigQuery jobs, datasets, tables and queries from the command line.
5. Why decide location before loading?
Residency, service compatibility and cross-region transfer costs can constrain later processing.