# Migrating to Cloud Spanner and Bigtable: Specialized Migration Strategies

> Chapter 8 of the Courseiva GOOGLE-PCDE curriculum — https://courseiva.com/learn/google-pcde/migrating-to-spanner-and-bigtable

**Official objective:** 2.3 — Migrate databases to Cloud Spanner and Bigtable, including schema conversion and data loading.

## Introduction

Exam objective 2.3 focuses on moving your existing databases into two of Google's most powerful, specialised database services: Cloud Spanner and Bigtable. For anyone studying for the PCDE, understanding these migrations is critical because you will be asked how to convert a traditional database schema (structure) and load terabytes of data without breaking the application that depends on it.

## The Moving House With a Specialised Moving Company Analogy

Have you ever watched someone move house and realised they have a piano, a grand piano, that requires a specialist team, not just your mate Steve with a borrowed van?

Migrating a database to Cloud Spanner or Bigtable is like moving that piano. You wouldn't use the same moving company that handles your cardboard boxes (your regular SQL Server or MySQL) for a delicate, giant concert grand. Cloud Spanner is like moving an entire orchestra's worth of instruments all at once, ensuring every single musician (your application) can access their sheet music (the data) at the exact same time, from any location, without delay. It's a global-scale, strongly consistent move. Bigtable, on the other hand, is like moving a massive archive of historical newspapers. You need speed, you need to scan millions of pages (your time-series data or analytics) quickly, but it's okay if a few pages in transit are slightly out of order for a moment. You don't need the same level of orchestral precision.

So, the 'migration strategy' isn't just about packing boxes. It's about deciding whether your data is a piano or a newspaper archive, then choosing the specialist movers (the conversion tools and loading methods) who know how to secure it, catalogue it, and get it to the new house (Google Cloud) without dropping it or losing the piece for the finale.

## Core explanation

Welcome to the world of database migration. If you have zero IT background, think of a database as a giant, organised digital filing cabinet. Your company has been using one type of filing cabinet (say, a traditional SQL database like MySQL or PostgreSQL) for years. Now, you need to move everything into a new, super-powered cabinet that lives in Google's cloud. That move is called a migration.

The exam objective 2.3 covers two very specific types of new cabinets: Cloud Spanner and Bigtable. They are not general-purpose filing cabinets. They are specialised tools designed for specific jobs.

**Cloud Spanner** is a relational database that can handle enormous scale and still guarantee that every user sees the exact same data at the exact same time, no matter where in the world they are. This is called 'strong global consistency'. Imagine a bank account. If you transfer money from London to Sydney, both the London bank teller and the Sydney bank teller must see the same balance instantly. Spanner makes that possible across thousands of servers. Migrating to Spanner is a big deal because you are taking a database that might only be running in one building and turning it into a planet-wide service.

**Bigtable** is a NoSQL database. 'NoSQL' means it does not use the traditional row-and-column table structure of a filing cabinet. Instead, it is more like a giant index card system where each card has a unique key, and you can have many different columns on each card, often with different properties. Bigtable is built for speed and massive scale for analytical workloads (like processing billions of data points from the Internet of Things) or time-series data (like stock prices every second). It does not offer strong global consistency in the same way Spanner does; it offers 'eventual consistency', meaning that if you write data to one server, it might take a tiny fraction of a second for all other servers to see that update. That trade-off is fine for many applications where speed is more important than instant accuracy.

**The Schema Conversion Problem**

When you migrate, you cannot just copy and paste. You have to convert the 'schema', which is the blueprint of your filing cabinet—the names of the drawers (tables), the labels on the tabs (columns), and the rules for how drawers connect (relationships).

For **migrating to Cloud Spanner**, your source database is probably a relational database like MySQL, PostgreSQL, or Microsoft SQL Server. These all use SQL (Structured Query Language). Spanner uses a very similar SQL dialect, so the schema conversion is relatively straightforward, but there are traps. For example, Spanner does not use the same kind of 'auto-incrementing' number columns as MySQL. You must change those. You also have to think about 'primary keys'—the unique identifier for each row—very carefully because how you choose your primary key in Spanner determines how fast your queries are. Google provides a tool called the **Spanner Migration Tool (SMT)** that helps you analyse your source schema and suggest a new schema for Spanner. It is like a translator that reads your old blueprint and writes a new one.

For **migrating to Bigtable**, your source database is likely a different NoSQL database (like Apache HBase, which Bigtable was originally based on) or maybe a huge collection of CSV files. Bigtable does not use SQL; it uses other languages and APIs. The schema in Bigtable is defined by a single 'table' with a 'row key'. Your entire job in schema conversion is to design that one row key perfectly. A bad row key is like labelling every folder in your filing cabinet with just 'Miscellaneous'. You will never find anything quickly. Bigtable also organises data into 'column families', which are groups of related columns, and uses 'versions' (or timestamps) to track changes. Migrating to Bigtable often involves writing a custom script or using Hadoop to read the old data and write it into Bigtable.

**The Data Loading Problem**

Moving the actual files (the data) is called 'data loading'. You have to get your terabytes of information from your old filing cabinet into the new one. You cannot just email it.

For **Cloud Spanner**, Google recommends using **Harrow**, which is a service that continuously replicates data from your source database to Spanner in near real-time. Think of it as a pipeline that sucks data out of your old cabinet and pours it into the new one, keeping them in sync while you test. You also use **batch loading tools** like the `gcloud spanner` command-line tools or the Dataflow service (a way to process large amounts of data). The key is to avoid downtime—users should barely notice the switch.

For **Bigtable**, the preferred method for massive initial loads is **Dataflow** or using the **HBase (Hadoop) to Bigtable migration tool**. You can also use simple client libraries in C++, Java, Python, etc., to write data directly. The challenge with Bigtable loading is 'hotspotting'—if you write data to a single point in the table faster than it can be processed, the whole system slows down. Good row-key design prevents this.

Why does all this matter? Because the PCDE exam demands you know the *strategy* for each service, not just the tools. You must recognise when to use a live-migration tool like Harrow versus a batch job with Dataflow. You must know that Bigtable expects you to flatten your data into one wide table, while Spanner still wants a normalised relational model. Getting this wrong in the exam or in real life means a failed migration, which means a broken product and unhappy customers.

## Real-world context

Let us walk through a realistic scenario. You are a database engineer (or someone studying to become one) at a global online retailer called 'ShopSphere'. Currently, ShopSphere uses a traditional MySQL database running in its own data centre to track all customer orders. The company wants to expand into South America and Asia. They have two major problems.

First, the MySQL database is slow for customers in Europe trying to check their order history from a new warehouse in Brazil. Second, the marketing team wants to analyse billions of clickstream events (every mouse click on every product page) to recommend products in real time.

The engineering team decides on a two-pronged migration strategy: they will move the order-tracking system to **Cloud Spanner** to make it globally fast and consistent, and they will move the clickstream data to **Bigtable** for high-speed analytics.

**Step 1: Assessment and Schema Conversion**

As the engineer, you first assess the current MySQL database. You run the Spanner Migration Tool (SMT) against your MySQL schema. The tool shows you several issues:
- The `order_id` column uses 'AUTO_INCREMENT', which Spanner does not support. Your old database auto-generated a new number for each order. In Spanner, you need to use a 'UUID' generation (a long, random string unique across the planet) or a sequence.
- There is a table that joins orders to customers. In Spanner, to ensure fast global reads, you decide to 'denormalise' (combine) that join into a single table, accepting slight redundancy to gain speed.
- You choose a primary key. You decide to use the `order_id` (a UUID) as the primary key for Spanner because it distributes reads evenly across servers.

For the clickstream data going to Bigtable, you analyse the data format. It is a series of JSON files (a text format for data) that record user ID, product ID, timestamp, and action (click, add to cart, view). You design a Bigtable row key that is a combination of `user_id` and a reversed timestamp (so the most recent clicks for a user are read first). You define a column family called `click_data` with columns for each action type.

**Step 2: Data Loading with a Continuous Pipeline**

You cannot shut down ShopSphere for a month to move data. So for Spanner, you set up the **Harrow** service. Harrow connects to your live MySQL database. It reads all the existing data and loads it into Spanner in a batch. Then, it starts replicating every new order that comes in, keeping the MySQL and Spanner databases in sync. This is called a 'dual-write' or 'replica' phase. You run your new application code against Spanner (on a small test group) while the old code still runs against MySQL. You verify that the order totals match. After a week of testing, you 'cut over' and all new traffic goes to Spanner. You stop the Harrow pipeline.

For Bigtable, you use **Dataflow** to read the historical JSON files from Google Cloud Storage (a place to store files) and write them into Bigtable in a 'bulk load'. This is a one-time job. Then, for the live streaming clickstream data, you use a tool like **Pub/Sub** (a message queue) to send new click events directly to a Cloud Dataflow pipeline that writes them into Bigtable in near real-time.

**Step 3: Post-Migration Optimisation**

After the move, you monitor performance. In Spanner, you use the 'Key Visualiser' tool to spot hotspots (where too many queries are hitting one node). You adjust a few secondary indexes to speed up common searches. In Bigtable, you check the 'CPU utilisation' of the table and adjust the number of nodes (servers) to balance cost and performance. You also optimise the row keys so that a sudden surge of clicks from a popular product does not overload a single server, a phenomenon called 'scatter writes'.

As an IT professional, your job is not just to copy data. It is to design the new blueprint (schema), pick the right moving truck (migration tool), and ensure no one notices the house has moved.

## Exam focus

The PCDE exam tests objective 2.3 with specific, scenario-based questions designed to separate engineers who understand the *strategy* from those who just memorised tool names. Here is exactly what you need to know.

**Question Types and Patterns**

You will see several types of questions:
- **You are given a source database and a target (Spanner or Bigtable) and asked to pick the correct migration tool.** For Spanner, the trap is that you might think you can use the Dataflow for everything. But for continuous, near-zero-downtime migrations from MySQL/PostgreSQL, the answer is almost always **Harrow** (which is a Google-specific migration service). For batch-loading a static data dump into Spanner, Dataflow is correct. For Bigtable, the correct tool for bulk loading is **Dataflow** or a custom MapReduce job (Hadoop-based).
- **You are given a table schema in MySQL and asked how to modify it for Spanner.** The traps always involve auto-increment columns (replace with UUIDs or sequences), and foreign key constraints (Spanner supports them, but the exam tests if you know they are optional and come with a performance cost).
- **You are asked to design a row key for Bigtable.** This is the most common Bigtable question. They give you a use case (like a weather sensor network reporting every second, or a social media feed of user posts). The correct row key will be a composite of the entity ID (sensor ID, user ID) and some form of reversed timestamp. The trap is using just the timestamp as the first part of the row key, which causes hotspotting.
- **You are asked about consistency guarantees.** The exam will ask: 'Your application writes a new order. All future reads must see this order immediately, even from different regions. Which service do you choose?' The answer is Cloud Spanner (strong consistency), not Bigtable (eventual consistency).

**Trap Patterns to Avoid**

- **Confusing 'Schema Conversion' with 'Data Loading'.** The exam loves to test that you know the schema conversion must be done *before* data loading, not during. A question might describe an engineer who starts Dataflow to move data from MySQL to Spanner without first changing the schema. The correct answer will be 'The migration will fail because Spanner does not support auto-increment columns.'
- **Thinking Bigtable is a Relational Database.** A classic trap question describes a complex join query (e.g., 'Find all customers who bought product X and also returned product Y'). If the question says the target is Bigtable, the correct answer is 'This is not appropriate because Bigtable does not support joins.'
- **Misunderstanding 'Denormalisation'.** The exam might ask about optimising a Spanner schema. A beginner might think adding more tables is better (normalisation). For Spanner at scale, the correct approach is often to denormalise (combine tables) to reduce the need for distributed joins across servers.
- **Choosing the Wrong Tool for Live Migrations.** For a real-time, continuous sync from a running database, the answer is Harrow. A trap answer might suggest a one-time Dataflow job, which would miss all new transactions after the job starts.
- **Forgetting about the 'Pilot' Phase.** The exam will test if you know that before cutting over all traffic, you should run a 'pilot' or 'shadow' phase where traffic is mirrored to the new database (using Harrow) to compare performance.

**Key Definitions to Memorise**

- **SMT (Spanner Migration Tool):** Analyses source schema and suggests conversions.
- **Harrow:** Continuous, low-downtime migration service for MySQL/PostgreSQL to Spanner.
- **Dataflow:** Serverless, fully managed service for batch and stream data processing; used for data loading to both Spanner and Bigtable.
- **Row Key:** The single, most important design decision for a Bigtable table.
- **Hotspotting:** Performance degradation caused by poor row key design that directs all writes to a single server node.
- **Strong Consistency vs. Eventual Consistency:** Spanner gives you the first; Bigtable gives you the second.
- **Dual-Write / Live Replication:** The phase during migration where data is written to both old and new databases simultaneously to ensure consistency during cut-over.

## Step by step

1. **Assess Source Database and Design Target Schema** — You first identify the source database type (e.g., MySQL, PostgreSQL, HBase) and its schema. For Spanner, use the SMT to analyse and suggest a new schema, replacing auto-increment columns with UUIDs. For Bigtable, design the row key (e.g., user_id + reversed timestamp) and column families based on access patterns. This step defines the blueprint for the entire migration.
2. **Set Up Target Database Instance in Google Cloud** — Provision a Cloud Spanner instance with the appropriate number of nodes and a multi-region configuration, or a Bigtable instance with the correct cluster configuration. This step ensures the infrastructure is ready before data arrives, avoiding resource contention later.
3. **Perform Initial Bulk Data Load** — For Spanner, use Dataflow or the `gcloud spanner databases execute-sql` command to import the existing data dump. For Bigtable, use Dataflow to read the historical data files from Cloud Storage and write them into Bigtable in a batch. This loads the static data that does not change during the migration.
4. **Set Up Continuous Replication (for Spanner) or Streaming Pipeline (for Bigtable)** — For Spanner, configure Harrow to start replicating live transactions from the source database into Spanner, keeping both databases in sync. For Bigtable, set up a Pub/Sub topic and Dataflow pipeline to stream new incoming data (e.g., clickstream events) into Bigtable in near real-time. This ensures the new database stays up to date with new changes.
5. **Run Pilot Test and Validate Data Consistency** — Direct a small subset of application traffic to the new database (Spanner or Bigtable) while the majority still uses the old one. Use validation scripts to compare output (e.g., read order totals from both databases). Identify and fix any discrepancies or schema issues before proceeding.
6. **Perform Cut-Over and Decommission Old Database** — Once the pilot test confirms consistency and performance, switch all application traffic to the new database. After a stable period (e.g., 24 hours), stop the Harrow replication pipeline and shut down the old database. This final step completes the migration with minimal downtime.

## Comparisons

### Cloud Spanner vs Bigtable

**Cloud Spanner:**
- Relational database with SQL support for joins and transactions
- Offers strong global consistency, meaning reads always see the latest write
- Best suited for transactional workloads like order management, finance, and global user records

**Bigtable:**
- NoSQL database with no SQL joins; data is stored in a single wide table
- Offers eventual consistency, meaning a small delay may exist before writes are visible globally
- Best suited for analytical workloads like time-series, IoT sensor data, and recommendation systems

### Harrow (Migration Tool) vs Dataflow (Migration Tool)

**Harrow (Migration Tool):**
- Specifically designed for live, near-zero-downtime migrations to Cloud Spanner
- Continuously replicates transactions from MySQL or PostgreSQL into Spanner
- Handles schema conversion and validation during the replication process

**Dataflow (Migration Tool):**
- A general-purpose data processing service for both batch and streaming workloads
- Used for bulk loading historical data into both Spanner and Bigtable
- Requires you to write or configure pipelines to transform data into the target format

### Schema Conversion (for Spanner) vs Row Key Design (for Bigtable)

**Schema Conversion (for Spanner):**
- Focuses on converting table structures, data types, and constraints from MySQL/PostgreSQL
- Requires replacing auto-increment with UUIDs or sequences
- Uses the Spanner Migration Tool (SMT) to automate much of the analysis

**Row Key Design (for Bigtable):**
- Focuses on designing a single row key that determines data distribution
- Must avoid monotonically increasing patterns to prevent hotspotting
- Requires understanding of the access pattern (e.g., scan recent data by user) to shape the key

## Diagram

_A decision tree showing the migration path from a source database to Cloud Spanner or Bigtable, including the key stage (schema conversion or row key design) before cut-over._

```mermaid
flowchart TD
    A[Source Database: MySQL, PostgreSQL, HBase] --> B{Choose Target}
    B --> C[Cloud Spanner: Relational, SQL, Strong Consistency]
    B --> D[Bigtable: NoSQL, High Throughput, Eventual Consistency]
    C --> E[Schema Conversion: SMT, Replace Auto-Increment with UUIDs]
    C --> F[Data Loading: Harrow for Live Replication, Dataflow for Bulk]
    C --> G[Pilot Phase: Validate with Dual Writes]
    D --> H[Row Key & Column Family Design: Critical for Performance]
    D --> I[Data Loading: Dataflow for Bulk & Streaming via Pub/Sub]
    D --> J[Pilot Phase: Validate with Shadow Reads]
    E --> K[Cut Over: Switch All Traffic to New DB]
    F --> K
    G --> K
    H --> K
    I --> K
    J --> K
```

## Common misconceptions

- **Misconception:** I can just export my MySQL database into a SQL file and import it directly into Bigtable. **Reality:** Bigtable is a NoSQL database with a completely different structure. You must first design a row key and column families, then use a tool like Dataflow to transform the data into the Bigtable format. A SQL dump is incompatible. (People hear 'database' and assume all databases work the same way, forgetting that NoSQL systems do not use tables with rows and columns in the traditional sense.)
- **Misconception:** Cloud Spanner is just a bigger version of MySQL, so migrating requires very little planning. **Reality:** Spanner is a globally distributed, strongly consistent database. The migration requires careful schema redesign to handle things like auto-increment replacements (use UUIDs) and primary key distribution to avoid hotspots. You also need to plan for the continuous replication phase via Harrow. (Because both use SQL, beginners think the migration is 'just a bigger database move'. They overlook the architectural differences in scalability and consistency models.)
- **Misconception:** Once I move my data to Bigtable using Dataflow, the application can start using it to perform SQL queries straight away. **Reality:** Bigtable does not support SQL queries natively. You must use the Bigtable API (C++, Java, Python) or a library like OpenTSDB. You cannot write a standard SQL `SELECT` statement against Bigtable. (The word 'database' implies 'SQL' to many. They do not realise that Bigtable is a NoSQL database that uses a different query interface entirely.)
- **Misconception:** The most important part of a Bigtable migration is optimising the column families. **Reality:** The most important part is designing the row key. A poor row key (e.g., just a timestamp) causes hotspotting, which kills performance. Column families are important, but row key design is the critical success factor. (Beginners focus on the structure they can see (columns) and underestimate the invisible but critical impact of the row key on data distribution.)
- **Misconception:** For a live migration to Spanner, I should first stop the application, export the data, and then import it into Spanner. That is the safest way. **Reality:** The safest way with minimal downtime is to use Harrow to perform a live, continuous replication. This allows you to run a pilot test with mirrored traffic before a controlled cut-over, without stopping the application entirely. (People fear change and assume the only way to be safe is to stop everything. They do not know that cloud services like Harrow are designed to eliminate downtime during migrations.)

## Key takeaways

- Cloud Spanner is a globally distributed relational database with strong consistency, requiring a schema conversion step to replace auto-increment columns with UUIDs or sequences.
- Bigtable is a NoSQL database designed for high-throughput, low-latency analytical workloads, requiring a single-row key design and using Dataflow for bulk data loading.
- The Spanner Migration Tool (SMT) analyses your source MySQL or PostgreSQL schema and suggests necessary conversions, such as removing foreign key constraints for performance.
- Harrow is the preferred tool for continuous, near-zero-downtime migrations from MySQL or PostgreSQL to Cloud Spanner, as it replicates live changes during the pilot phase.
- A poorly designed Bigtable row key (e.g., a monotonically increasing timestamp) will cause hotspotting, which degrades performance across the entire cluster.
- In the PCDE exam, the correct answer for a real-time migration scenario always favours using Harrow for Spanner, and Dataflow or a custom Hadoop job for Bigtable.
- Bigtable does not support SQL joins, so you must denormalise your data into a single wide table before migration.
- During migration to Spanner, you must perform a 'cut-over' after verifying data consistency in the pilot phase, switching over all application traffic to the new database.
- Schema conversion must happen before data loading, not after, to avoid runtime errors when the data is written into the target database.
- Cloud Spanner supports global reads with strong consistency, while Bigtable offers high throughput with eventual consistency, defining the use case for each.
- Dataflow is a serverless service that can handle both batch loading of historical data and streaming live data into Bigtable or Spanner.
- Denormalisation (combining related tables) is a deliberate strategy for Spanner schemas to minimise the need for distributed joins across regions.
- A 'pilot' or 'shadow' phase using tools like Harrow is essential for testing before the full cut-over, even if the initial load seems successful.
- You must design Bigtable column families to group related columns together, as they affect storage and read efficiency.
- The primary key of a Spanner table dictates data distribution across nodes; a poor choice leads to hotspots just as in Bigtable.

## FAQ

**What is the difference between Cloud Spanner and Bigtable?**

Cloud Spanner is a relational database with strong global consistency and SQL support, best for transactional workloads like financial systems. Bigtable is a NoSQL database with eventual consistency, optimised for high-throughput analytical and time-series workloads, like sensor data or recommendation engines.

**Can I use the same migration tool for both Spanner and Bigtable?**

No, you typically use different tools. For Spanner, the Spanner Migration Tool (SMT) helps with schema conversion and Harrow for live data replication. For Bigtable, you use Dataflow for bulk loading and custom client libraries for streaming.

**What is a row key in Bigtable and why is it important?**

A row key is the unique identifier for each row in Bigtable. It determines how data is distributed across servers. A poorly designed row key (e.g., just a timestamp) causes hotspotting, slowing down the entire table.

**Do I need to shut down my application to migrate to Cloud Spanner?**

No, you can use Harrow to perform a live migration without significant downtime. It replicates data continuously while the old database is still in use, then you perform a cut-over after testing.

**What happens if I forget to convert auto-increment columns when migrating to Spanner?**

The migration will fail, or the application will break because Spanner does not support MySQL-style auto-increment. You must replace them with UUIDs or Spanner sequences during schema conversion.

**Is it okay to run SQL joins in Bigtable?**

No, Bigtable does not support SQL joins because it is a NoSQL database. You must denormalise the data into a single wide table before migrating to Bigtable.

**What is a 'pilot' or 'shadow' phase in database migration?**

It is a testing phase where you direct a small amount of live traffic to the new database (Spanner or Bigtable) while keeping the old one active. This helps validate data consistency and performance before a full cut-over.

**How do I load historical data into Bigtable after the initial migration?**

You can use Dataflow to process files (e.g., in Cloud Storage) and write them into Bigtable. This is a batch job, not real-time streaming.

## Check your understanding

1. **Your company has a MySQL database that must be migrated to Cloud Spanner with zero downtime during the cut-over. Which Google Cloud service should you use for the migration?**

   Answer: You should use Harrow, which performs continuous replication from MySQL to Spanner, allowing a controlled cut-over with minimal downtime.

2. **You are migrating a time-series dataset of stock prices to Bigtable. What is the most critical design decision you must make before loading the data?**

   Answer: The most critical decision is designing the row key. A good choice is 'stock_symbol + reversed_timestamp' to distribute writes evenly and allow efficient scans of recent data.

3. **What is the main reason you need to convert auto-increment columns in MySQL to UUIDs or sequences when migrating to Cloud Spanner?**

   Answer: Cloud Spanner does not support auto-increment; using a UUID-based primary key ensures the data can be distributed across servers without creating hotspots.

4. **After migrating a large dataset to Bigtable, you notice high latency on writes. What is the most likely cause?**

   Answer: The most likely cause is hotspotting, which occurs because the row key is designed with a common prefix (like a timestamp) that directs all writes to a single server node.

5. **Your application needs to perform complex join queries spanning multiple tables, and it must run in a globally distributed environment with strong consistency. Which Google Cloud database should you migrate to?**

   Answer: You should migrate to Cloud Spanner, because it supports SQL joins and provides strong global consistency across regions.

---

Interactive version with quiz and diagrams: https://courseiva.com/learn/google-pcde/migrating-to-spanner-and-bigtable