What's changed: Created Professional Data Engineer Chapter 3 (Domain 3 "Storing": storage choice and DWH = BigQuery/BigLake/AlloyDB/Bigtable/Spanner/Cloud SQL/Cloud Storage/Firestore/Memorystore, normalization/denormalization, partitioning/clustering; data lakes and platforms = data lake (Cloud Storage) discovery/access/cost, Dataplex/Dataplex Catalog, federated governance).
3.1Choosing storage systems and the data warehouse
Understand choosing managed services by access pattern (BigQuery, BigLake, AlloyDB, Bigtable, Spanner, Cloud SQL, Cloud Storage, Firestore, Memorystore), storage cost/performance, lifecycle management, and data-warehouse modeling (degree of normalization, optimizing for access patterns).
Where to store data depends on access patterns, scale, and cost. Data engineers choose the best-fit storage and design the data warehouse.
3.1.1Choosing storage
Analyze access patterns: large-scale SQL analytics (DWH) = BigQuery, accessing Cloud Storage data from BigQuery = BigLake, high-performance PostgreSQL = AlloyDB, huge-scale low-latency NoSQL = Bigtable, global strong-consistency RDB = Spanner, typical business RDB = Cloud SQL, unstructured objects = Cloud Storage, documents = Firestore, cache = Memorystore. Consider cost/performance and lifecycle management (move/delete old data). Map "analytics = BigQuery," "low-latency scale = Bigtable," "global strong = Spanner."
3.1.2Data-warehouse modeling
In a DWH like BigQuery, design the data model. Analytical DWHs often choose denormalization (some redundancy) to reduce join cost, while OLTP RDBs use normalization to avoid duplication. Match access patterns with partitioning (split by date, etc.) and clustering (order by frequently filtered columns) to reduce scan volume and cost. Map "analytical DWH leans denormalized" and "reduce scan = partitioning/clustering."
Common: requirement → service/design. E.g., "petabyte SQL analytics" = BigQuery; "access Cloud Storage data from BigQuery" = BigLake; "huge-scale low-latency" = Bigtable; "global strong consistency" = Spanner; "reduce BigQuery scan" = partitioning + clustering; "reduce analytical join cost" = denormalization.
Watch the mix-ups: (1) Analytical BigQuery vs OLTP Cloud SQL/Spanner differ in purpose. (2) Partitioning (split) and clustering (ordering) are distinct; both cut scan. (3) Unlike OLTP, analytical DWHs often benefit from denormalization.
3.1.3Section summary
- Choose by access pattern: analytics = BigQuery/BigLake, low-latency scale = Bigtable, strong = Spanner, etc.
- DWH leans denormalized, OLTP normalized; partitioning/clustering reduce scan
- Design for cost/performance and lifecycle management (move/delete)
Sign in to track progress — Log in.
Quick check
(just a quick review)Q1. Which data warehouse analyzes petabyte-scale data quickly with SQL?
Q2. To analyze Cloud Storage data from BigQuery without moving it, which is best?
Q3. For huge-scale, low-latency time series/IoT data, which is best?
Q4. To reduce BigQuery query scan volume and cost, which design best fits?
Q5. Which is generally effective for analytical data-warehouse modeling?
Q6. You need a globally distributed, strongly consistent relational DB. Which is best?