Storing the Data practice questions

Store all data in one bucket and use object labels to distinguish raw, curated, and processed.

Why it fails: Labels are not as effective for lifecycle management and access control as separate paths or buckets.

Store raw data in a different project for security isolation.

Why it fails: Overly complex; typical data lakes use a single project with proper IAM and bucket structure.

Use different storage classes for raw, curated, and processed data within the same bucket.

Why it fails: Storage class is separate from organization; you still need a folder structure to apply rules per zone.

Store the sensitive data in Cloud Storage with CMEK and use external tables.

Why it fails: This adds complexity and doesn't provide native column-level encryption within BigQuery.

Use BigQuery column-level security with data classification.

Why it fails: Column-level security controls access, not encryption; data is still stored in plaintext.

Create a BigQuery table with CMEK enabled; it will automatically encrypt all columns.

Why it fails: CMEK encrypts the entire table at rest, not per column with different keys or granular control.

device_id#timestamp (e.g., device123#2024-03-15-10:30:00)

Why it fails: Starting with device_id will cause all writes for one device to go to a single tablet, creating a hotspot.

timestamp#device_id (e.g., 2024-03-15-10:30:00#device123)

Why it fails: Starting with timestamp will cause all writes at the same second to hit the same tablet, causing a hotspot.

device_type#device_id#timestamp

Why it fails: Starting with device_type doesn't help; if many devices share the same type, it still causes hotspots.

[timestamp reversed]#[sensor_id]

Why it fails: Reversed timestamp still leads to sequential writes; not as effective as hashing.

[sensor_id]#[timestamp]

Why it fails: If sensor_id is sequential (e.g., auto-increment), writes still hotspot. Better to hash sensor_id.

[timestamp]#[sensor_id]

Why it fails: Timestamp prefix leads to hotspotting as all writes go to same tablet server.

Use Cloud Bigtable instead

Why it fails: Not relevant; the question is about Spanner.

Create a secondary index on order_id

Why it fails: Index helps lookups but join still requires separate tablet reads.

Denormalize the order items into the orders table using repeated fields

Why it fails: Spanner does not support repeated fields; it's relational. Denormalization is possible but interleaving is better.

Use a bucket retention policy with a retention period of 1 year

Why it fails: Retention policy prevents deletion, not enforce it.

Set an IAM policy to prevent deletion of objects

Why it fails: Opposite of what is needed.

Create a Cloud Function to check object age and delete them

Why it fails: A Cloud Function that checks object age and deletes them would require continuous execution to scan all objects, creating latency and cost inefficiencies for a data lake with high-volume ingestion. The correct approach uses Object Lifecycle Management rules, which apply declarative policies directly on the bucket without compute overhead. This option is tempting because Cloud Functions are a common tool for custom event-driven processing, such as transforming or archiving objects upon creation, where lifecycle rules cannot execute arbitrary logic.

Cloud SQL

Why it fails: PostgreSQL-compatible but no columnar analytics; limited to 10k QPS.

Cloud Bigtable

Why it fails: No SQL, not relational, eventual consistency.

BigQuery

Why it fails: OLAP only, not OLTP.

Cloud SQL (MySQL) with cross-region read replicas

Why it fails: Cloud SQL does not provide strong consistency across regions; replicas are eventually consistent.

BigQuery multi-region

Why it fails: BigQuery is for analytics, not transactional OLTP.

Cloud Bigtable multi-cluster routing

Why it fails: Bigtable offers eventual consistency across clusters, not strong consistency.

Reverse the key to 'timestamp#deviceID'

Why it fails: This would cause all writes to go to the same tablet for the current timestamp, worsening hotspots.

Use a single column family with many columns

Why it fails: Column families don't affect row key distribution.

Store all data in one row per device

Why it fails: Storing all data in one row would cause excessive I/O and contention.

BigQuery ingestion from Cloud Storage using load jobs

Why it fails: Load jobs import data into BigQuery storage, not directly query from GCS.

Cloud Storage FUSE to mount bucket and query

Why it fails: FUSE can mount GCS but is not the direct BigQuery feature.

BigQuery federated queries with Cloud Storage

Why it fails: Federated queries is a concept, but the term 'external tables' is official.

Use BigQuery column-level encryption (AEAD) with a key from Cloud KMS

Why it fails: BigQuery AEAD functions encrypt individual column values using a Cloud KMS key, but the table's data-at-rest encryption remains Google-managed, so CMEK lifecycle control over the dataset is absent. It is tempting because AEAD genuinely protects specific sensitive fields from users with table-level read access.

Enable default encryption on the Cloud Storage bucket used for staging data

Why it fails: Bucket default encryption protects staged objects in Cloud Storage, not the BigQuery table's data-at-rest layer, so the CMEK requirement on the dataset is unmet. It is tempting because staging buckets genuinely hold the loaded files, and securing them is a legitimate step when the mandate covers the whole ingestion pipeline.

Encrypt the data before loading using a custom application and store the key in Secret Manager

Why it fails: Application-side encryption stores ciphertext in BigQuery, so BigQuery never holds the CMEK and cannot decrypt natively; Secret Manager also manages secrets, not Cloud KMS key rotation or destruction. It is tempting because envelope encryption with external keys genuinely satisfies sovereignty rules demanding that the cloud provider never hold key material.

Enable Private Google Access on the VPC subnet.

Why it fails: Private Google Access allows VMs to reach Google APIs via private IP, but does not restrict access from public internet.

Use Cloud Storage signed URLs for all access.

Why it fails: Signed URLs provide temporary access but do not restrict network origin.

Disable public internet access by turning off the default internet gateway.

Why it fails: This would break all internet access, not just to Cloud Storage.

Object Versioning

Why it fails: Versioning keeps multiple versions of objects, but does not automate deletion or storage class transitions.

Retention Policies

Why it fails: Retention policies prevent deletion, not schedule deletion or storage class changes.

Bucket Lock

Why it fails: Bucket Lock is for WORM compliance, not lifecycle management.

Only sensor ID

Why it fails: Only sensor ID may cause hotspot if many writes go to same sensor, and does not handle time-range queries well.

Timestamp followed by sensor ID

Why it fails: Starting with timestamp causes sequential writes to hotspot a single tablet server.

Random salt appended to timestamp

Why it fails: While better than timestamp first, it's less common; hashed prefix is recommended.

Cloud SQL for PostgreSQL

Why it fails: Cloud SQL is OLTP but not optimized for analytical queries; lacks columnar engine.

Cloud Spanner

Why it fails: Spanner is globally distributed relational, not PostgreSQL-compatible.

BigQuery

Why it fails: BigQuery is OLAP only, not transactional.

lastAccessTime: 30

Why it fails: Used for storage class transitions based on last access, not deletion.

daysSinceCustomTime: 30

Why it fails: Requires custom time to be set on objects, not applicable here.

BigQuery

Why it fails: BigQuery is an analytical warehouse optimised for large scans and aggregations, not single-digit millisecond point reads at millions per second. It is tempting because it ingests petabytes cheaply and handles time-series analytics well. Bigtable, a wide-column store, delivers that low-latency key-value throughput instead.

Cloud Spanner

Why it fails: Cloud Spanner is a globally consistent relational database built for transactional workloads, not millions of key-value reads per second at single-digit millisecond latency. It is tempting because it scales horizontally and offers strong consistency, but its relational overhead and cost do not fit simple timestamped sensor data. Bigtable is designed for this.

Firestore

Why it fails: Firestore is a document database with per-document read scaling limits and higher latency than single-digit milliseconds at millions of reads per second. It is tempting for simple key-value data and mobile sync, but it cannot sustain that throughput. Bigtable's wide-column design serves this time-series workload.

Secondary indexes

Why it fails: Secondary indexes accelerate lookups on non-primary-key columns; they do not optimise parent-child interleaved joins, which rely on physical co-location of child rows with their parent. Secondary indexes are the right choice when filtering or sorting on arbitrary columns that are not part of the primary key.

Bigtable as a caching layer

Why it fails: Bigtable is a separate NoSQL service; it cannot act as a caching layer inside Cloud Spanner's transactional engine, so it neither preserves strong consistency nor accelerates interleaved joins. Bigtable is the right choice for high-throughput key-range scans over massive sparse datasets, not SQL relational workloads.

Read replicas for global distribution

Why it fails: Read replicas serve stale-tolerant reads closer to users; they do not provide the automatic regional failover with strong consistency the stem demands, and add nothing to parent-child join performance. Read replicas are correct when the workload is read-heavy and can accept bounded staleness for lower latency.

BigQuery

Why it fails: BigQuery is an analytical warehouse optimised for large scans and aggregations, so it cannot return single-row lookups in single-digit milliseconds at millions of reads per second. It fits batch analytics and BI reporting over the same IoT data, not low-latency point reads.

Firestore

Why it fails: Firestore is a document database whose per-document read pricing and query model cannot sustain millions of reads per second at petabyte scale. It suits mobile and web application state synchronisation, not high-volume time-series key-value ingestion.

Cloud Spanner

Why it fails: Cloud Spanner is a globally distributed relational database with strong consistency, so its per-row transactional overhead cannot deliver the key-value throughput required here. It suits horizontally scalable OLTP workloads needing SQL and multi-region consistency, not petabyte time-series ingestion.

Set a retention policy on the bucket for 7 years and enable Object Versioning.

Why it fails: Object Versioning alone does not prevent deletion; it keeps old versions. Retention policy prevents deletion but does not delete automatically.

Use Object Lock with WORM mode and set a retention period of 7 years. After the period, objects are automatically deleted.

Why it fails: Object Lock is for individual objects, not bucket-wide. Also, it does not automatically delete objects after retention expires.

Set a lifecycle rule to delete objects after 7 years and enable Bucket Lock.

Why it fails: The order is wrong: lifecycle rule cannot delete objects while retention policy is active. Retention policy must be released first.