CCNA Pcd Design Scalable Questions

Using a composite key with a hash prefix or a monotonically increasing value as the first part of the key can cause hotspots. UUIDs distribute writes evenly across splits. Using a UUID as the leading part of a composite key avoids hotspotting better than the other options.

Bigtable supports replication across clusters in different zones within a region (or across regions). For automatic failover, you should use primary-secondary (also called single-cluster routing with failover) or multi-cluster routing. The recommended HA setup is to have two clusters in different zones with replication enabled.

Cloud Bigtable is designed for exactly this use case — petabyte-scale, low-latency (single-digit ms), high-throughput NoSQL storage for time-series, IoT, and financial data. It scales horizontally by adding nodes. BigQuery is optimised for analytics (seconds-to-minutes latency), Cloud SQL is for OLTP (limited to tens of thousands of QPS), and Firestore is for document data with hierarchical structure.

Cloud SQL for relational user profiles with HA, Firestore for NoSQL product catalog with low latency, Memorystore for session caching.

Using a composite primary key with a hash prefix (e.g., server_id) as the first part distributes writes across splits, reducing hotspots. A monotonically increasing key (like rank) causes hotspotting.

Stale reads with bounded staleness (Option A) allow Cloud Spanner to serve read requests from any replica within a configurable time window (e.g., up to 15 seconds), avoiding the latency of contacting the leader replica. This dramatically increases read throughput for product catalog queries where near-real-time data is acceptable. Local secondary indexes (Option D) are co-located with the base table data in the same split, enabling efficient, low-latency queries on attributes within a single region without cross-node coordination.

Spanner supports stale reads (bounded staleness) by setting a read timestamp. The read timestamp can be set to a past time within the maximum staleness. The `max_staleness` option in the client libraries or the `read_timestamp` parameter in gRPC API allow bounded staleness.

Firestore provides serverless NoSQL document storage, automatic scaling, offline sync, and integration with Firebase Authentication.

Option A is correct because prepending a hash of the user_id distributes writes uniformly across all Bigtable tablets. Bigtable uses the row key prefix to determine tablet assignment; a UUID prefix is random but sequential UUIDs (e.g., time-based) can cluster writes. A hash function (e.g., MD5 or CRC32) ensures even distribution, eliminating hotspotting on a single node.

To avoid hotspots, row keys should be designed to distribute writes across tablets. Reversing the timestamp (e.g., `[timestamp reversed]#[sensor_id]`) spreads new writes across different tablets, rather than appending to the same tablet.

Option B is correct because the query filters by `user_id` and sorts by `timestamp` descending, which requires a composite index on `(user_id, timestamp DESC)` to avoid a full collection scan. Without this index, Firestone performs a sort in memory, which becomes slow as the dataset grows. Creating the composite index allows Firestore to serve the query directly from the index, dramatically improving performance.

Database Migration Service (DMS) can be used for continuous replication. They also need to configure the source database for replication (binary logging). Cloud SQL proxy is for connecting applications, not migration.

Option D is correct because Cloud SQL HA (high availability) instances use synchronous replication to a standby VM in a different zone within the same region, providing automatic failover with zero data loss (0 RPO). This configuration ensures that committed transactions are immediately replicated to the standby before acknowledging the commit, guaranteeing that no committed data is lost even if the primary zone fails.

Bigtable is correct for real-time user behavior tracking because it is a fully managed, scalable NoSQL database designed for high-throughput, low-latency workloads like time-series data, user analytics, and event logging. It supports strong consistency for single-row operations, which is sufficient for tracking individual user events in real time, and can handle millions of writes per second across a global footprint.

Cloud SQL read replicas are used to offload read traffic and provide low-latency reads from different regions. They are asynchronous replicas that can be promoted in case of failure.

Bigtable garbage collection policies operate on column families and can delete cells older than a specified time or keep a max number of versions.

Reversing timestamps, salting keys, and promoting high-cardinality fields to the start of the row key are recommended to distribute writes evenly.

Cloud SQL is the correct choice because it supports SQL Server 2019, provides managed database services with up to 64 TB of storage (via the Enterprise Plus tier with 64 TB SSD capacity), and is ideal for lift-and-shift migrations of OLTP workloads without requiring application changes. It offers high availability, automated backups, and compatibility with SQL Server features like T-SQL and linked servers, making it a direct replacement for on-premises SQL Server.

Interleaved tables store child rows with their parent row in the same split, colocating related data and reducing cross-node communication. This is the correct optimization for parent-child joins.