CCNA Pcd Design Scalable Questions

Cloud Spanner interleaved tables allow storing child rows (products) with their parent row (category) in the same split, providing low-latency joins and locality. The parent-child relationship is defined using the `INTERLEAVE IN PARENT` clause.

Adding a field promotion (e.g., user ID) to the row key distributes writes. Salting the row key with a hash prefix also distributes load. Adding nodes helps throughput but not the root cause.

Creating an HA instance provides automatic failover. Adding read replicas offloads read traffic. Enabling the proxy for connection pooling ensures efficient connections.

Cloud Bigtable is designed for petabyte-scale, low-latency, high-throughput NoSQL storage for time-series, IoT, and financial data.

AlloyDB is PostgreSQL-compatible, offers a columnar engine for fast analytics, supports in-database ML, and claims up to 4x faster OLTP than Cloud SQL PostgreSQL.

Firestore is serverless and scales automatically, including scaling to zero. Cloud SQL and Memorystore require provisioned capacity. Bigtable requires provisioned nodes.

Using a composite key with a hash prefix distributes writes. Interleaved tables store child rows with parent, reducing cross-split operations. Monotonically increasing keys cause hotspots.

A multi-region configuration with read-write replicas provides automatic failover and strong consistency across regions. Backup and restore is manual and not automatic.

AlloyDB is a PostgreSQL-compatible database service with a built-in columnar engine that accelerates analytical queries by storing data in columns rather than rows, enabling HTAP workloads without needing to move data between separate transactional and analytical systems. Its architecture decouples compute from storage and uses a columnar cache to deliver up to 100x faster analytical queries than standard PostgreSQL, making it the correct choice for HTAP on Google Cloud.

Field promotion moves the most frequently filtered column to the start of the row key. Since queries filter by metric_type and time range, placing metric_type first allows Bigtable to efficiently scan a contiguous range of rows.

Cloud SQL for MySQL with HA configuration provides synchronous replication to a standby instance in the same region, automatic failover, and zero RPO.

Strong reads provide the latest committed data with global consistency. Stale reads (bounded staleness) may return older data. External consistency is a Spanner property but not a read mode.

Cloud Bigtable is designed for petabyte-scale, low-latency, high-throughput NoSQL storage for time-series, IoT, and financial data. It scales horizontally by adding nodes. BigQuery is for analytics (seconds-to-minutes latency), Cloud SQL for OLTP (limited throughput), Firestore for document data.

In Cloud Spanner, secondary indexes can be local (interleaved with parent) or global (non-interleaved). If an index is defined as a global index, queries might still need to join with the base table unless the index includes all needed columns. Without the `STORING` clause, Spanner must fetch additional columns from the base table, causing the query to scan the base table.

Using a cross-region read replica can offload analytics queries without impacting the primary instance, but for read scalability within region, an in-region replica works too.

Cloud SQL HA instances use synchronous replication to a standby in a different zone within the same region, providing automatic failover and 0 RPO.

AlloyDB's columnar engine is specifically designed to accelerate analytical queries by storing data in a column-oriented format, which allows for faster scans and aggregations on large datasets. This enables the same database to handle both OLTP (row-oriented) and OLAP (column-oriented) workloads without requiring a separate analytics system, directly supporting the hybrid HTAP requirement described in the question.

Using the STORING clause in a secondary index stores the specified columns in the index, allowing queries to be satisfied from the index alone without a join back to the base table. This reduces read latency.

Database Migration Service (DMS) supports one-time migration with continuous replication until cutover, minimizing downtime.

Option A is correct because promoting a cross-region read replica to primary is the standard method for manual or automated failover in Cloud SQL for PostgreSQL when the primary region fails. Cloud SQL does not support automatic cross-region failover; you must use a read replica and promote it to take over as the new primary instance.

Memorystore for Redis is ideal for caching, session storage, and pub/sub messaging with sub-millisecond latency. Memorystore for Memcached is also for caching but does not support pub/sub. Firestore and Bigtable are not caching solutions.

Using UUIDs as primary keys can cause hotspotting on the leading key. A better approach is to use a composite key with a sharding prefix (e.g., hash prefix) to distribute writes, and local indexes for single-region queries are not applicable. Global indexes with STORING clause avoid index joins and reduce read latency.

Memorystore for Redis provides a managed Redis service for caching, session stores, and pub/sub messaging.

Firestore provides offline sync and real-time updates. Cloud Storage can store images and other large files with security rules. Cloud SQL and Bigtable are not suited for mobile offline sync.

Cloud Spanner is the correct choice because it provides a globally distributed, strongly consistent relational database with horizontal scaling and a 99.999% SLA. It combines the benefits of relational database structure (ACID transactions and SQL queries) with horizontal scalability across regions, making it ideal for financial applications that require strict consistency and high availability.

Cloud SQL with HA configuration provides synchronous replication to a standby instance in the same zone (or different zone) and automatic failover with 0 RPO. Spanner replicates across regions, Firestore multi-region replication is not synchronous, Bigtable cluster replication is asynchronous.

Memorystore provides managed Redis and Memcached for caching, session storage, and pub/sub. It is the correct service for these use cases.

Interleaved tables in Cloud Spanner physically co-locate parent and child rows on the same split, so a query joining orders and line items by the parent key (order_id) can be served from a single split without distributed reads. This eliminates cross-node network hops, dramatically reducing latency for the inventory system's order+line-item queries.

Using timestamp as the leading part of the row key causes all recent writes to go to a single tablet server, creating a hotspot.

Memorystore (Redis) is the managed caching service on GCP, ideal for session stores and reducing database load. Cloud SQL, Bigtable, and Firestore are not suitable as low-latency caches.

Memorystore for Redis provides in-memory caching, pub/sub, and rate limiting capabilities. Memcached does not support pub/sub.

Firestore is a serverless NoSQL document database designed for mobile and web apps. It provides offline persistence, real-time listeners, and Security Rules for access control. Cloud SQL and Spanner are not serverless; Bigtable requires cluster management.

Option B is correct because the application requires 48 vCPU and 312 GB RAM, and Cloud SQL Enterprise with db-custom-48-307200 provides exactly 48 vCPU and 307200 MB (300 GB) of RAM, which is the closest available tier that meets or exceeds the memory requirement. Cloud SQL Enterprise supports custom machine types with up to 48 vCPU and 307200 MB RAM, while Enterprise Plus offers only predefined 'perf-optimized' tiers that do not match the exact vCPU/RAM combination needed.

Cloud Pub/Sub is ideal for ingesting high-throughput event streams, and Cloud Bigtable provides low-latency storage and querying. Together they meet the requirements.

Cloud SQL HA provides synchronous replication to a standby in a different zone within the same region, achieving 0 RPO. Automatic failover typically completes within 60 seconds, so RTO is less than 60 seconds.

Cloud Spanner is the only Google Cloud database that offers global distribution, horizontal scaling, ACID transactions across regions, and a 99.999% SLA.

Cloud SQL HA instance provides synchronous replication to a standby in a different zone within the same region, ensuring automatic failover with zero RPO because no committed transaction is lost. This meets the requirement for high availability and zero data loss for an OLTP PostgreSQL database of this size.

The row key pattern `domain_reversed_timestamp` causes all writes for a given domain to hit the same tablet server, creating a hotspot under heavy load. Adding a random salting prefix (e.g., a hash of the domain or a random integer) distributes writes evenly across all nodes in the Bigtable cluster, eliminating the sequential write bottleneck. This is the standard anti-hotspot pattern for Bigtable row key design.

Salted keys (prepending a hash prefix) distribute writes across nodes. Reversed domain keys help with read patterns but not write distribution. Column families and GC policies do not affect write distribution.

Cloud SQL for PostgreSQL supports HA instances with synchronous replication to a standby in the same region, automatic failover, and zero RPO. It is the best choice for a lift-and-shift of a PostgreSQL OLTP workload.

Interleaved tables in Cloud Spanner allow storing child rows (Comments) physically near parent rows (Posts) if the child table's primary key includes the parent's primary key. This improves locality and reduces cross-split reads.

Bigtable is the closest equivalent to Cassandra for time-series data, offering similar performance and scalability. Cloud Spanner is relational, and Firestore is for document data.

In Cloud Spanner, a secondary index does not store all columns of the base table by default. When a query uses the index but needs columns not present in the index, Spanner performs a back join (also called a table lookup) to retrieve those missing columns from the base table. The STORING clause allows you to include additional non-key columns in the index itself, making the index covering and eliminating the need for the back join.

BigQuery is the serverless, highly scalable data warehouse for analytics. It supports SQL and BigQuery ML for in-database ML.

Option A is correct because placing the timestamp first in the row key ensures that writes are spread across the Bigtable tablet server range. In Bigtable, rows are sorted lexicographically by row key; leading with a monotonically increasing value (like timestamp) causes all new writes to hit a single tablet, creating a hotspot. By swapping to 'timestamp#advertiser_id#campaign_id', writes are still sequential but the timestamp prefix alone does not guarantee perfect distribution—however, combined with salting (Option E), it avoids the original hotspot caused by the advertiser_id prefix.

Field promotion (moving high-cardinality fields to the key), reversed domain, and salted keys are common patterns to avoid hotspots. Single table is not a key design practice. Column families affect storage, not key design.

BigQuery is the correct choice because it is a serverless, highly scalable data warehouse designed for petabyte-scale analytics using SQL. It excels at complex aggregations over terabytes of read-only log data, with automatic partitioning and columnar storage that optimize query performance for periodic analytical workloads.

AlloyDB is PostgreSQL-compatible and includes a columnar engine for in-database ML inference and HTAP workloads. It is optimized for OLTP and real-time analytics without ETL.

Option D is correct because a stale read with a staleness bound of 10 seconds allows Cloud Spanner to serve the read from any replica that has data within that staleness window, avoiding the latency of a strong read that must contact the leader. This provides the best performance while still guaranteeing consistency as of a point in time up to 10 seconds ago, meeting the requirement of tolerating staleness up to 10 seconds.

Firestore is a serverless, NoSQL document database that automatically scales, supports mobile and web clients with offline data persistence and synchronization via its SDKs, and enforces document-level security rules through Firebase Security Rules. This combination of serverless scaling, offline sync, and granular security makes it the correct choice for the described requirements.

Firestore (Native mode) provides serverless NoSQL document storage, offline sync for mobile clients, and real-time listeners for updates. It is the ideal choice for mobile and web apps.

Option B is correct because adding a shard prefix (e.g., a hash of the UUID) as the first part of a composite primary key distributes writes evenly across Cloud Spanner's splits, eliminating hotspotting. UUIDs alone cause hotspotting because they are randomly distributed but still lead to a single split being overwhelmed if the table is small or the UUIDs are inserted in order; a hash prefix ensures that consecutive writes target different splits, reducing write latency.

Database Migration Service (DMS) supports continuous replication from PostgreSQL to AlloyDB, enabling minimal downtime migrations.

Cloud Bigtable is a NoSQL, wide-column database optimized for high-throughput, low-latency analytical and operational workloads, but it does not support multi-row transactions or ACID guarantees. Therefore, it is not suitable as a transaction processing database for an e-commerce website, which requires strong consistency and transactional integrity for order management and inventory updates.

Cloud SQL, Cloud Spanner, and AlloyDB all support SQL. Bigtable uses HBase API, not SQL. Memorystore is a cache, not a database with SQL.

Firestore provides offline persistence, real-time sync, and document data model, making it ideal for mobile apps. Cloud SQL does not offer offline sync, Bigtable is not document-oriented, and Memorystore is a cache.

The 'Index Join' operation in Cloud Spanner occurs when a secondary index does not contain all the columns needed by the query, forcing Spanner to join the index with the base table to retrieve the missing data. By using the STORING clause (also known as covering index), you include the required columns directly in the index, allowing Spanner to satisfy the query entirely from the index without accessing the base table, thus eliminating the join and improving performance.

Option A is correct because separating frequently accessed columns like price and volume from metadata like trade ID into different column families optimizes read performance. In Bigtable, column families are stored separately on disk, so queries that only need price and volume can avoid reading the trade ID column family, reducing I/O and improving latency.

AlloyDB is correct because it combines PostgreSQL-compatible SQL analytics with sub-millisecond read latency for transactional workloads, using a columnar engine for analytical queries and a memory-optimized row store for real-time transactions. This dual-engine architecture allows the same data to serve both OLTP and OLAP requirements without data movement or latency trade-offs.

Firestore is a fully managed, serverless NoSQL document database that provides offline support, real-time listeners, and security rules for mobile and web apps.

Cloud Firestore is a serverless, NoSQL document database that automatically scales with usage, making it ideal for storing user preferences and providing offline sync capabilities via its built-in offline data persistence and real-time synchronization. It supports mobile and web clients with automatic multi-region replication and strong consistency, aligning perfectly with the requirements of a mobile app developer.

Cloud Spanner is the only Google Cloud database service that provides ACID transactions across regions with strong external consistency, making it ideal for a global inventory system requiring millions of updates per day. Its multi-region instance configuration uses synchronous replication and the TrueTime API to ensure linearizable transactions, meeting the need for strong consistency across warehouses.

Cloud Spanner supports bounded staleness reads (stale reads) which allow reading data as of a specific timestamp in the past, reducing latency by avoiding strong read barriers. Using a read timestamp 10 seconds in the past achieves this.

Option A is correct because placing `customer_id` as the first column in a composite primary key enables Spanner to distribute writes across splits based on the leading key column, avoiding hotspots that occur when using a monotonically increasing UUID as the sole primary key. This design also allows fast point-lookups and range scans on `customer_id` without requiring a secondary index, as Spanner can directly locate the row via the primary key.

Reversing the timestamp and prefixing with device ID spreads writes across tablets. Using only device ID or only timestamp causes hot spots.

Salted keys, field promotion, and reversed domain are common patterns to distribute writes. Using a single node is not a solution; it worsens performance.

Cloud Bigtable is a fully managed, scalable NoSQL database designed for large analytical and operational workloads, making it ideal for petabyte-scale time-series IoT sensor data. It supports single-digit millisecond latency for reads and writes at millions of operations per second through its underlying storage engine (based on Google's Chubby and SSTable technology) and its native integration with the HBase API, which provides efficient key-value access with timestamp-based versioning.

Multi-region configurations with read replicas provide strong consistency for writes and allow stale reads from read replicas for low-latency reads. Using global secondary indexes improves query performance. Interleaved tables help locality but not cross-region.