Hard Difficulty Questions

The query uses SELECT * instead of specific columns

Why wrong: Selecting * increases bytes but the question implies aggregation on value only; still, the main issue is partition count.

Clustering on sensor_id is ineffective

Why wrong: Clustering on sensor_id is appropriate for grouping.

The table is not using columnar storage

Why wrong: BigQuery is columnar by default.

Use nested and repeated fields to avoid JOINs

Why wrong: Nested fields can complicate queries and are not always optimal for BI.

Create indexes on frequently queried columns

Why wrong: BigQuery does not support indexes.

Migrate to Cloud Spanner and use interleaved tables to simulate foreign keys

Why wrong: Cloud Spanner does not support triggers and interleaved tables are not a direct replacement.

Remove all foreign keys and triggers and implement validation in the application layer

Why wrong: Increases application complexity and risk of data inconsistency.

Convert the schema to use Firestore in Datastore mode with composite indexes

Why wrong: Firestore is NoSQL and does not support foreign keys or triggers.

The query is using a stale read timestamp.

Why wrong: Strong reads ignore the timestamp; they return the latest data.

The query is reading from a read-only replica.

Why wrong: Read-only replicas support strong reads with consistent data.

Cloud Spanner is using eventual consistency for this query.

Why wrong: Strong reads guarantee external consistency.

Monitor 'cloudsql.googleapis.com/network/received_bytes_count' and compare with connection count.

Why wrong: Network bytes received is a throughput metric, not a direct indicator of connection spikes.

Monitor 'cloudsql.googleapis.com/database/mysql/replication/seconds_behind_master' and compare with query latency.

Why wrong: This metric tracks replication lag, which is unrelated to connection spikes causing performance issues.

Monitor 'cloudsql.googleapis.com/instance/uptime' and check for instance restarts during degradation.

Why wrong: Uptime indicates availability, not connection spikes or performance.

The service account lacks the spanner.databases.read permission.

Why wrong: The service account has databaseReader role, which includes read access.

The service account is assigned the role roles/spanner.databaseBackupAdmin, which is a custom role that does not include the spanner.backups.create permission.

Why wrong: roles/spanner.databaseBackupAdmin is not a predefined role; the correct role is roles/spanner.backupAdmin at the instance level.

The instance 'orders-db' is in a regional configuration, which does not support backups.

Why wrong: Regional instances support backups; the configuration is not the issue.

Create a new table with the new column, then use a fan-out pattern to write to both tables until the old table is deprecated.

Why wrong: This approach is overengineered for a simple column addition.

Use an ALTER TABLE statement during a maintenance window.

Why wrong: Spanner does not require a maintenance window; schema changes are online.

Drop the table and recreate it with the new schema.

Why wrong: Dropping a table causes downtime and data loss.

The new schema does not use commit timestamps for versioning.

Why wrong: Commit timestamps are for MVCC, not directly related to cross-node latency.

The new schema lacks secondary indexes on foreign key columns.

Why wrong: Missing indexes can cause full table scans but not necessarily cross-node latency.

The Spanner instance was not resized after the schema change.

Why wrong: Resizing affects throughput but not the inherent latency of cross-node transactions.

Denormalize the dimension table into the fact table.

Why wrong: Denormalization increases storage and may not be necessary; the dimension table is small.

Partition the dimension table by its primary key.

Why wrong: Partitioning on the primary key does not help with the join filter on symbol.

Cluster the dimension table on its primary key.

Why wrong: Clustering on the primary key does not help if the join key is not the primary key.

Maintain the same schema and rewrite joins as materialized views in PostgreSQL to optimize queries.

Why wrong: Rewriting joins as views does not address schema compatibility; the application still needs to handle composite keys.

Migrate to Cloud Spanner instead, using interleaved tables to replace join-heavy operations.

Why wrong: Cloud Spanner would require significant application changes and is not a direct migration path from MySQL.

Remove composite primary keys and replace them with surrogate keys; use look-up tables for foreign key relationships.

Why wrong: This would require extensive application code changes to adapt to new key structures.

Shard the database across multiple regional instances based on user geography.

Why wrong: Sharding across instances complicates schema and transactions, and Spanner multi-region is the recommended approach.

Create read replicas in other regions to offload read traffic and keep writes in the primary region.

Why wrong: Read replicas do not help with write latency or write capacity.

Increase the number of nodes in the existing regional instance to handle the increased write capacity.

Why wrong: This only scales within one region; write latency from other regions will still be high.

Use logical storage billing and partition the table by date; older data automatically moves to long-term storage after 90 days.

Why wrong: Logical storage billing is less cost-effective for large, highly compressed data. Long-term storage provides some cost savings but not optimal.

Use Bigtable for recent data and BigQuery for historical data, with a scheduled transfer between them.

Why wrong: This adds operational complexity and Bigtable costs may not be lower; query interface changes.

Export data older than 30 days to Cloud Storage as Avro files and delete them from BigQuery; query them externally when needed.

Why wrong: Querying external data is slower and incurs additional costs; management is complex.

Clustering does not create indexes on symbol

Why wrong: BigQuery does not use indexes; clustering organizes storage but does not guarantee fast lookup for a single symbol.

Partitioning causes data skew across partitions

Why wrong: Partitioning by date typically results in even distribution.

Partitioning by date is not granular enough

Why wrong: Date partitioning is appropriate for 30-day window functions.

Create a view with UNION ALL of all region tables

Why wrong: Does not prune tables; scans all tables on every query.

Create materialized views for each region

Why wrong: Materialized views are pre-computed and may not support dynamic region selection.

Store all data in a single table with region as a column

Why wrong: Increases storage and may violate data locality requirements.

BigQuery requires explicit indexes on columns used in WHERE clauses.

Why wrong: BigQuery uses columnar storage and automatic pruning; no indexes needed.

BigQuery supports unique constraints and foreign keys for data integrity.

Why wrong: BigQuery does not enforce constraints; it's schema-on-read.

Create secondary indexes on foreign key columns

Why wrong: Secondary indexes do not co-locate data; joins still require distributed reads.

Store foreign key relationships as JSON arrays in the parent table

Why wrong: Violates normalization and complicates queries; not recommended.

Disable binary logging to reduce write I/O.

Why wrong: Compromises point-in-time recovery capability.

Increase the storage size to 200 GB to improve IOPS.

Why wrong: Does not directly reduce latency; IOPS are already sufficient.

Decrease the max_connections parameter to reduce overhead.

Why wrong: Could cause connection failures during peak hours.

Query cache uses disk storage for cached results.

Why wrong: The query cache is stored in memory, not on disk.

Prepared statements always bypass the query cache.

Why wrong: Prepared statements can use the query cache if they are identical binary representations.

Add secondary indexes on the primary key columns.

Why wrong: Secondary indexes are redundant for point lookups by primary key.

Redesign the schema to use interleaved tables.

Why wrong: Interleaved tables optimize joins, not point lookups.

Reduce the number of Spanner nodes to lower CPU overhead.

Why wrong: Fewer nodes may increase load per node.

The Bigtable cluster does not have enough nodes to handle the scan.

Why wrong: Even with enough nodes, the scan is inefficient due to row key design.

The query is scanning multiple column families.

Why wrong: Column families don't affect scan speed significantly; the row key is the issue.

The table has too many tablets, causing high overhead.

Why wrong: More tablets improve parallelism; not the cause of slowness.