Designing data processing systems Study Guide — Professional Data Engineer

Designing Data Processing Systems

This domain accounts for 22% of the GCP Professional Data Engineer exam — the largest single domain. It tests your ability to select and configure the right storage and processing services for a given workload, design schemas correctly, apply security and compliance controls, plan for failure, and migrate data between systems. Expect scenario-based questions where two answers look equally valid; the differentiator is usually a specific constraint like latency, scale, cost, or compliance requirement.

Choosing Storage and Processing Services for Batch vs. Streaming

Core Decision Dimensions

The exam presents a workload and asks you to justify a service choice. The key variables are: ingestion pattern (batch vs. streaming), latency requirement (minutes vs. milliseconds), query pattern (analytical vs. transactional vs. key-value lookup), and data volume (GBs vs. TBs vs. PBs).

Service Selection Reference

Common Traps

• Dataproc ≠ Dataflow: Dataproc is for existing Spark/Hadoop workloads. Dataflow is the preferred choice for new streaming or unified batch/stream pipelines on GCP.
• Pub/Sub does not store data long-term: Default retention is 7 days; maximum is 31 days. It is a message bus, not a data lake.
• BigQuery Streaming inserts ≠ free: Streaming inserts cost $0.01 per 200 MB. Batch loads from Cloud Storage are free.
• Do not choose Bigtable when the question mentions ad-hoc SQL queries — Bigtable has no SQL layer beyond limited SQL in its API.

Worked Scenario

Requirement: A telemetry pipeline ingests 500,000 sensor events per second. Downstream, a dashboard must refresh every 5 seconds and a data science team runs weekly batch ML jobs on 90-day historical data.

Reasoning: Pub/Sub handles ingestion and decoupling. Dataflow consumes Pub/Sub in streaming mode, writes aggregated 5-second windows to Bigtable for the dashboard (low-latency reads), and writes raw events to Cloud Storage (Parquet) as the durable archive. The ML team reads from BigQuery, which uses federated queries or a scheduled load from Cloud Storage. No single service handles all three needs.

Decision tree for storage service selection:

Schema Design for BigQuery, Bigtable, Firestore, and Spanner

BigQuery Schema Design

Denormalization is preferred in BigQuery because joins on large tables are expensive and columnar storage makes repeated fields cheap. Key features:

• STRUCT (RECORD): nested object, stored in the same column family — avoids joins.
• ARRAY: repeated fields within a STRUCT. Querying requires UNNEST().
• Partitioning: Partition by ingestion time (_PARTITIONTIME), a DATE/TIMESTAMP column, or an INTEGER range. Partition pruning dramatically reduces bytes scanned = cost reduction. Max 4,000 partitions per table.
• Clustering: Up to 4 columns per table. BigQuery physically co-locates rows with similar values. Works best on high-cardinality columns used in WHERE and JOIN. Clustering is free; pruning is automatic.
• Partition + Cluster together: Partition on date, cluster on user_id is a canonical pattern.

Bigtable Schema Design

Bigtable is a wide-column key-value store. The row key is the only index. All query performance optimization lives in row key design.

Hotspotting occurs when row keys are sequential (e.g., timestamps, auto-increment IDs) because all writes hit one node. Fix: reverse the timestamp, hash the key, or use a salting prefix.

Bigtable does not support joins, secondary indexes, or multi-row transactions.

Firestore Schema Design

Firestore is a document store with two modes:

• Native mode: Real-time listeners, mobile SDKs, offline support. Collections → Documents → Subcollections.
• Datastore mode: Legacy Datastore API compatibility. No real-time listeners.

Key design rules:

• Avoid unbounded subcollections in a single document path (performance degrades).
• Composite indexes must be manually created for multi-field queries.
• Single document write limit: 1 write/second per document (hotspot risk with counters).
• Use distributed counters (sharded) to avoid this limit.

Spanner Schema Design

Spanner offers globally distributed ACID transactions with external consistency. Schema choices:

• Interleaving: Child rows are physically co-located with parent rows using INTERLEAVE IN PARENT. Critical for parent-child join performance.
• Primary keys: Must be carefully chosen to avoid hotspots (same issue as Bigtable — avoid monotonically increasing keys like timestamps or UUIDs in insertion order).
• UUID v4 or bit-reversal is recommended for primary keys.
• Spanner supports secondary indexes (unlike Bigtable) and foreign keys.
• Split points are managed automatically, but schema design influences split boundaries.

Common Traps

• BigQuery nested STRUCTs do not always replace normalization — for tables that are updated frequently, normalization in a transactional DB is still right; bring denormalized data into BQ at query time.
• Bigtable column families ≠ BigQuery STRUCT: They are completely different concepts.
• Firestore Native vs. Datastore mode cannot be changed after creation.
• Exam may offer Bigtable for a workload requiring SQL analytics — wrong choice if ad-hoc queries are needed.