Migrating On-Premises MySQL to BigQuery with Low Latency

The scenario requires a managed solution for transferring data from on-premises MySQL to BigQuery with an emphasis on low latency and ease of setup. The critical constraint here is the source: on-premises relational databases. Google Cloud offers distinct services for different migration patterns. Dataflow (Option A) is a powerful tool for batch and streaming processing, but it is not a managed replication service out-of-the-box; it requires significant custom coding and orchestration to achieve low-latency CDC, which contradicts the 'easy to set up' requirement when paired with Datastream in that option. Option B suggests Cloud Data Fusion, which is a UI-based ETL tool. While easier to use than raw Dataflow code, it is generally better suited for batch or near-real-time transformations rather than the lowest-latency continuous replication streams required for modern analytics. Option D describes a manual, periodic extract process, which inherently fails the low-latency requirement.

Option C leverages Database Migration Service (DMS). DMS is specifically designed to migrate on-premises databases to Google Cloud (such as Cloud SQL for MySQL) with minimal downtime and high availability. Once the data is replicated into Cloud SQL, BigQuery’s external table features or federated queries can access this data directly. However, the option mentions creating federated tables to transform and load data into BigQuery. This phrasing is slightly ambiguous because typically one would use Cloud Storage as an intermediary or direct ingestion pipelines. Yet, among the choices, DMS is the only fully managed, automated replication service capable of handling the initial heavy lift of moving data from on-prem MySQL to the cloud efficiently. Wait, let's re-evaluate based on current best practices. Actually, for low latency streaming into BigQuery from on-prem, Database Migration Service supports ongoing replication. But is there a better path? Let's look at the options again. Option A combines Datastream and Dataflow. Datastream provides the CDC stream. Dataflow processes it. This is a standard pattern for real-time ingestion. Is it 'easy'? Compared to writing custom scripts, yes, but compared to a single managed service?

Let's reconsider the 'managed' and 'low latency' aspects. Database Migration Service is the primary tool for migrating on-prem DBs to Cloud SQL. If the goal is to get data into BigQuery, simply replicating to Cloud SQL doesn't automatically put it in BigQuery unless you use external tables or a secondary sync. However, many exam questions consider the replication to Cloud SQL via DMS as the correct first step for managing the on-prem connection. But wait, Option C says 'Create federated tables... to transform and load'. Federated queries are for querying, not loading. Loading implies copying. This might be a distractor.

Let's look at Option A again. Datastream is explicitly for CDC. Writing to Cloud Storage then using Dataflow is a very common, robust, and scalable pattern. It is managed. Is it 'easy'? It requires setting up both services.

Actually, there is a nuance. Database Migration Service can replicate directly to BigQuery in some configurations, but primarily it targets Cloud SQL/Postgres. If we must choose the best fit for 'managed', 'low latency', and 'on-prem MySQL', Database Migration Service is the dedicated tool for the on-prem-to-cloud DB link. The subsequent step in Option C might be interpreted as using the replicated data. However, many experts argue that for pure BigQuery ingestion, Datastream + Pub/Sub/Dataflow is the gold standard for low latency. But the prompt asks for 'easy to set up'. Setting up a full Datastream+Dataflow pipeline is complex.

Let's look at the official guidance. For on-prem MySQL to BigQuery, if you want low latency, you often use a change data capture tool. Datastream is the native GCP CDC tool. But does it write directly to BigQuery? No, it writes to Cloud Storage or Pub/Sub. So Option A describes a valid pipeline. Option C describes a migration to Cloud SQL. If the question implies the final destination is BigQuery, replicating to Cloud SQL first adds a hop.

However, looking at similar exam questions, Database Migration Service is often the answer for 'migrating on-prem databases to GCP'. The 'federated tables' part is tricky. External tables allow querying Cloud Storage. Federated tables in BigQuery allow querying Cloud SQL. This allows you to analyze data in Cloud SQL without moving it. This fits 'low latency' (no ETL needed to move data) and 'easy to set up' (just connect BigQuery to Cloud SQL). You don't need to build a pipeline. You just query. This seems to align best with 'easy to set up' and 'low latency' (since the data is already there via replication). Therefore, C is likely the intended answer because it avoids building a complex ETL pipeline (like A or B) and achieves the analytical goal by leveraging the replicated data directly.