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.
Jump to a section
A simple way to picture Migrating to Cloud Spanner and Bigtable: Specialized Migration Strategies
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
These come up on the exam all the time. Here's how to tell them apart.
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)
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)
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
Mistake
I can just export my MySQL database into a SQL file and import it directly into Bigtable.
Correct
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.
Mistake
Cloud Spanner is just a bigger version of MySQL, so migrating requires very little planning.
Correct
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.
Mistake
Once I move my data to Bigtable using Dataflow, the application can start using it to perform SQL queries straight away.
Correct
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.
Mistake
The most important part of a Bigtable migration is optimising the column families.
Correct
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.
Mistake
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.
Correct
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.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
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.
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.
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.
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.
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.
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.
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.
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.
You've finished Migrating to Cloud Spanner and Bigtable: Specialized Migration Strategies. Continue through the PCDE study guide to build a complete picture of the exam.
Done with this chapter?