Designing data processing systems: free practice questions

Option A is correct. Partitioning by result_date aligns with the most common filter pattern (queries filtering by date range, e.g., "results from the last 30 days"). Clustering by test_name and patient_id optimizes the secondary filter and join patterns. Partitioning reduces the data scanned for date-range queries (mandatory pruning), while clustering provides optional pruning for test_name. BigQuery charges by data scanned, so this strategy minimizes cost. Option B (partition by patient_id) is inefficient—patient_id has high cardinality (~millions of unique values), creating too many partitions and fragmenting data, which hurts query performance and increases metadata overhead. Option C (partition by test_name) creates too few partitions; test_name has low cardinality and doesn't reflect access patterns. Option D (no partitioning) scans the entire table for every query, increasing cost and latency—partitioning is essential for billion-row tables.

Option 1 is correct: Row keys in Bigtable determine data locality and query efficiency. With user_id as the row key, all posts by a user are co-located (good for access pattern 1), but to answer access pattern 2 (posts by region), you'd need to scan every user's data—a full table scan. This violates Bigtable's design principle of pushing query logic into the row key. Option 0 is incorrect: User IDs are perfectly suitable as row keys; they're just opaque identifiers. The issue isn't the data type but the access pattern coverage. Option 2 is incorrect: Bigtable does support range scans on row keys. The problem isn't the limitation of Bigtable; it's that this row key doesn't support both access patterns efficiently. A composite key like region#timestamp could handle geographic queries. Option 3 is incorrect: While hot spotting is a real Bigtable concern, it applies when certain keys are accessed disproportionately more (e.g., a celebrity user). The question doesn't indicate this, and it's not the primary design flaw for supporting two different access patterns.

Option 2 is correct: This is the most cost-effective approach for predictable, scheduled batch jobs: - Cloud Scheduler triggers the job at a fixed time (e.g., 5 AM), allowing 4 hours for processing to complete by 9 AM. - Spot VMs cost 60-80% less than on-demand; since Dataproc jobs are fault-tolerant and re-runnable, spot VM preemption is acceptable (reschedule via Cloud Scheduler logic or Dataproc's built-in auto-recovery). - Tearing down the cluster post-job saves costs during the 20-hour idle window (no cluster running overnight). - This is ideal for predictable daily jobs. Option 0 is incorrect: CUDs require minimum 1-year commitment and upfront payment; they're best for always-on infrastructure. This job runs 4 hours/day, leaving 20 hours of unused capacity. CUDs would be wasteful. Spot VMs (option 2) are cheaper for variable workloads. Option 1 is incorrect: Dataflow is better for streaming or complex ETL workflows, not simple batch transformations. BigQuery Flex Slots are useful for variable query loads but don't replace Dataproc for Spark jobs. This would likely be more expensive and introduces unnecessary complexity. Option 3 is incorrect: While optimizing Spark partitioning is good practice, it doesn't change the fundamental cost structure. A persistent on-demand cluster incurs charges 24/7 ($X/hour × 24 hours), even when idle. Spot VMs with cluster teardown (option 2) is more cost-effective than persistent clusters.

Option B is correct and nuanced. CMEK is appropriate when organizational policy or regulation requires the data owner (your company, not Google) to control key material and rotation. Some compliance frameworks (e.g., FedRAMP, certain HIPAA interpretations, some financial regulations) require this; others (e.g., standard PCI-DSS, most GDPR implementations) accept Google-managed encryption. CMEK is a security control, not a magic compliance solution. Option A overstates the requirement—most frameworks accept Google-managed encryption; CMEK adds operational burden (key rotation, recovery) and should be adopted purposefully. Option C is incomplete; while Google-managed encryption is robust, some contracts or regulations do require customer control. Option D is incorrect; BigQuery doesn't support column-level encryption natively—CMEK applies at the table/dataset level.

The correct answer is option 1. This architecture separates concerns: Pub/Sub handles the high-volume ingestion, Bigtable provides sub-second query latency for 90 days of hot data (its native use case), and BigQuery archives cold data cost-effectively. Option 2 is incorrect because Cloud Storage is not queryable with sub-second latency. Option 3 is wrong because Firestore is not designed for 500 MB/min of streaming telemetry at this scale and lacks the time-series query patterns needed. Option 4 is suboptimal because BigQuery's query latency (seconds) exceeds the sub-second requirement, and it's expensive for retaining 90 days of hot data.