CCNA Pcd Multi Database Questions

BigQuery federated queries via external tables can directly query Cloud SQL without data movement. However, for near real-time freshness, the best approach is to use Datastream to replicate changes from Cloud SQL to BigQuery in near real-time. Federated queries alone have higher latency (minutes) and are not suitable for sub-second freshness.

BigQuery Omni enables querying data in S3 and Azure Blob Storage using a multi-cloud analytics approach.

Option A is correct because when restoring a Cloud Bigtable backup, the target cluster must already exist in the specified region. The restore operation does not create a new cluster; it populates an existing cluster with the backed-up data. This ensures the cluster's configuration (e.g., node count, storage type) is predefined and meets the recovery requirements.

Pub/Sub is correct because it provides a serverless, highly scalable ingestion service that can receive change data capture (CDC) events from an on-premises Oracle database via a connector like Debezium or Oracle GoldenGate, and stream them into BigQuery for near-real-time analytics without managing servers. Datastream is correct because it is a serverless CDC service specifically designed to replicate data from sources like Oracle directly to BigQuery, handling schema mapping and minimal operational overhead.

Using Cloud SQL export to Cloud Storage (SQL dump) and then loading into BigQuery is simple and cost-effective for daily snapshots. Using Datastream would be overkill and more expensive for a daily batch. Federated queries would incur repeated query costs and may not be as fresh.

Using mysqldump to export the database and then importing it via mysql client is the simplest approach for a one-time migration where downtime is acceptable. DMS is more suitable for continuous migrations with minimal downtime.

Datastream is a serverless change data capture (CDC) and replication service that can continuously stream changes from Cloud SQL (including transactional data) to Bigtable. It captures inserts, updates, and deletes in near real-time, enabling the dashboard to reflect current inventory levels within seconds of a transaction.

BigQuery Omni allows querying data across clouds (including AWS S3) using BigQuery SQL. It supports federated queries of data in S3 without movement. BigQuery federated queries (standard) only work with GCP sources.

Cloud Spanner change streams capture all data changes in a database or table and can be read via Pub/Sub or Dataflow. This is the native way to stream changes from Spanner.

Datastream is correct because it provides serverless change data capture (CDC) from Oracle databases, enabling near real-time synchronization to BigQuery. Dataflow is correct because it can consume the Datastream output and perform complex transformations (e.g., joins, aggregations, schema mapping) using Apache Beam before writing to BigQuery.

The cache-aside pattern involves the application checking the cache first, and if not found, loading from the database and updating the cache. Eventual consistency is acceptable for cache. The saga pattern is for distributed transactions, not caching.

BigQuery federated queries allow you to query data in Cloud SQL and Cloud Bigtable directly using external tables, without moving the data. This approach meets the real-time analytics requirement by joining order data (Cloud SQL) with session logs (Cloud Bigtable) in a single SQL query, avoiding the need for a separate data warehouse or data movement.

Enabling automated backups and cross-region copies ensures data is available in another region. Configuring a cross-region read replica with failover capability provides a DR instance that can be promoted in a disaster. Point-in-time recovery is for granular restore, not failover.

Cloud Spanner provides ACID transactions with global consistency and horizontal scalability, making it ideal for transactional workloads. Firestore is a NoSQL document database optimized for real-time updates and high-read/write throughput, perfect for session storage. BigQuery is a serverless data warehouse designed for analytical queries on large datasets, fitting the analytics requirement.

Option B is correct because enabling parallel processing in the Datastream source configuration allows multiple threads to read from the PostgreSQL database simultaneously, reducing the time to capture and stream changes. This directly addresses high latency by increasing throughput from the source, leveraging PostgreSQL's logical replication slots more efficiently.

BigQuery federated queries using external tables allow you to query data stored in Cloud SQL and Cloud Storage (CSV files) directly from BigQuery without moving the data. This is achieved by creating an external table definition in BigQuery that references the source, enabling SQL queries across both sources in a single query using BigQuery's query engine.

Ora2Pg is an open-source tool that automates migration from Oracle to PostgreSQL, but it does not convert Oracle-specific PL/SQL packages such as DBMS_*, UTL_*, or other built-in Oracle libraries. These packages have no direct equivalent in PostgreSQL, so any stored procedures or functions that rely on them will fail after conversion. Manual rewriting or using PostgreSQL-compatible extensions (e.g., pg_dbms_* community modules) is required.

BigQuery federated queries using Cloud SQL external tables allow you to query Cloud SQL databases directly from BigQuery without moving or replicating the data. This feature uses a federated query engine that pushes down SQL operations to Cloud SQL, minimizing the impact on the transactional workload by only reading the required data on demand.

Cloud Spanner change streams can capture row-level changes and publish them to Pub/Sub via Dataflow or direct integration.

Option B is correct because Dataflow provides a fully managed, serverless execution environment that can read from Bigtable, apply transformations (e.g., using Apache Beam's PTransform), and write to BigQuery on a scheduled basis. This approach is cost-effective as it scales to zero when idle and uses autoscaling, and maintainable because the pipeline code is reusable and can be version-controlled. Alternatives either lack transformation capability, incur higher latency, or require custom orchestration that increases operational overhead.

BigQuery Omni allows you to query data across Google Cloud, AWS, and Azure without moving the data. It uses BigQuery's federated query engine to run queries directly on data stored in Amazon S3 and Google Cloud Storage, leveraging BigQuery's standard SQL interface. This meets the requirement of querying live data across both clouds without data movement.

Dataflow is the correct choice because it is a fully managed, unified stream and batch processing service built on Apache Beam. It can directly read from Pub/Sub, apply complex transformations (e.g., windowing, aggregations, joins), and write the results to both BigQuery and Cloud Bigtable using native Beam I/O connectors, all within a single pipeline.

Datastream uses CDC and can deliver duplicate records if the source MySQL binary log is not configured with ROW-based replication and the log position tracking resets due to a restart or network issue. Duplicates can occur if the stream restarts from a checkpoint.

Option B is correct because it leverages Spanner change streams' native ability to publish directly to Pub/Sub, which is the simplest integration path. A single Dataflow pipeline then subscribes to that Pub/Sub topic to write to BigQuery, fulfilling both the real-time streaming and archival requirements with minimal moving parts. This avoids the complexity of reading change streams directly in Dataflow or managing multiple pipelines.

Option D is correct because the intermittent 'log space' errors during CDC indicate that the source transaction log is running out of space to record changes for replication. Database Migration Service (DMS) requires sufficient log space to capture ongoing changes; increasing the log size and removing any size limits ensures the log can grow to accommodate the replication lag without failing.

Memorystore (Redis) can cache frequently accessed session data from Bigtable and order data from Cloud SQL, enabling low-latency reads without moving data permanently. Dataflow is batch-oriented, Cloud Functions is not for queries, and exporting to BigQuery adds latency.

Database Migration Service for Oracle to PostgreSQL requires supplemental logging at the database level to capture all changes for CDC. Without supplemental logging, DMS cannot replicate ongoing changes.

Dataflow (Apache Beam) is the recommended service for stream processing with complex transformations. It can read from Pub/Sub, apply transformations, and write to Bigtable. Cloud Functions are simpler and not suited for complex ETL.

Cloud Spanner is the only fully managed relational database on GCP that offers strong consistency and horizontal scalability across regions. It supports SQL and ACID transactions. Cloud SQL is regionally limited and does not auto-scale horizontally.

Memorystore is correct because it provides a managed in-memory data store (Redis or Memcached) that delivers sub-millisecond latency for session data, which is a key-value workload requiring fast reads and writes. This directly satisfies the low-latency key-value requirement for session management in the polyglot persistence architecture.

Datastream is a serverless CDC service that can stream changes to BigQuery, Cloud Storage, or Pub/Sub.

Polyglot persistence is the practice of using multiple database technologies within a single application ecosystem, each chosen for its strengths.

DMS allows you to promote the replica to a standalone instance for testing while the CDC stream continues. However, promoting the replica stops the migration. The recommended approach is to create a clone from the replica for testing, or use a separate test environment.

Spanner change streams allow you to capture data changes and stream them to Pub/Sub topics for further processing. They provide a record of all mutations.

Option B is correct because BigQuery federated queries allow you to query Cloud SQL (MySQL) and Google Cloud Storage (GCS) directly using BigQuery's SQL engine, without moving data. This approach is cost-effective (no storage costs for duplicated data) and performant (BigQuery handles complex analytical joins efficiently), while avoiding any impact on the Cloud SQL production instance since queries are executed externally.

Spanner change streams capture changes, Pub/Sub provides a scalable messaging channel, and Dataflow can transform and write to Bigtable. Cloud Functions could be used but are less suitable for high-throughput. Cloud Scheduler is batch-oriented.

Cloud Spanner with a multi-region configuration is the best approach because it provides native support for distributed ACID transactions across regions using synchronous replication and the TrueTime API, ensuring strong consistency, atomicity, and isolation without requiring application-level coordination. This eliminates the complexity and latency of managing distributed transactions manually while meeting strict ACID compliance requirements.

Polyglot persistence refers to using different database types for different components of an application, each optimized for its use case.

Datastream is purpose-built for minimal-latency, serverless change data capture (CDC) from sources like Cloud SQL MySQL to BigQuery. It uses log-based replication (reading the MySQL binary log) to stream row-level changes directly into BigQuery without requiring custom code or intermediate processing. This approach meets the requirements of real-time streaming with no custom code and minimal latency.

Option B is correct because setting the automated backup retention to 7 days ensures that daily automated backups are kept for the required recovery window. Option E is correct because point-in-time recovery (PITR) in Cloud SQL for MySQL requires binary logging (binlog) to be enabled, and the binlog retention period must be at least 7 days to allow recovery to any point within that window. Without sufficient binlog retention, PITR cannot replay transactions beyond the retained binary logs.

The recommended method for backing up a Cloud Spanner database for disaster recovery is to use the `gcloud` command to export the database to Cloud Storage in Avro format. This creates a full database export that can be stored in a different region, enabling restoration in case of a regional failure. Cloud Spanner does not support native backup to other regions; instead, you must export the data to Cloud Storage and then import it into a new instance in the target region.

Option A is correct because Datastream requires explicit DDL event replication to capture schema changes like adding a column. By enabling DDL event replication in the Datastream connection profile, the service will monitor the MySQL binary log for DDL statements and propagate them to BigQuery. Without this setting, only DML changes (INSERT, UPDATE, DELETE) are replicated.

Ora2Pg is an open-source tool for converting Oracle schemas, data, and procedures to PostgreSQL.

DMS supports continuous CDC, so you should enable CDC for minimal downtime. Testing with a clone avoids affecting production. Promoting the replica early would cause downtime.

Datastream is a serverless CDC replication service specifically designed for replicating from sources like PostgreSQL, MySQL, and Oracle to BigQuery, Cloud Storage, or Pub/Sub. It handles schema mapping and initial backfill.

Datastream is a serverless CDC replication service that can stream changes from MySQL (including Cloud SQL) to BigQuery, GCS, or Pub/Sub with low latency. Dataflow requires building pipelines, DMS is for database migrations (not ongoing sync), and Pub/Sub alone doesn't load into BigQuery.