Moving Most of Our Data from Cloud SQL to BigQuery
Listen to this article· 3 min
When I joined the team, we already had a working platform.
We had pipelines.
We had dashboards.
We had Cloud SQL.
We had GKE.
Nothing was obviously broken.
But after spending more time looking at how the platform was actually being used, I started asking a simple question:
Why are we using a relational database to hold data that is mostly being ingested once and then read for analytics?
That question eventually led to one of the bigger changes I worked on: moving the majority of our pipeline data from Cloud SQL to BigQuery, while keeping Cloud SQL for the smaller set of databases that actually needed transactional CRUD operations.
It wasn't a "Cloud SQL is bad, BigQuery is good" story.
It was a workload mismatch story.
Cloud SQL Was Doing Two Very Different Jobs
At the beginning, Cloud SQL was doing more than one job.
Some applications genuinely needed a relational database:
That makes sense.
These workloads need things like:
- frequent row-level updates
- transactional behavior
- application-driven CRUD
- predictable access to individual records
Cloud SQL is designed for exactly this kind of relational application workload.
But other workloads looked completely different:
The pipeline was mostly loading data.
The dashboard was mostly reading data.
And the data was being used to generate:
- Charts
- Graphs
- Aggregations
- KPIs
- Historical views
- Insights
That is much closer to an analytical workload than a transactional one.
BigQuery is built around analytical processing and large-scale querying rather than OLTP-style, single-row application transactions. Google explicitly recommends systems such as Cloud SQL for OLTP use cases.
So we had a database doing a job it wasn't necessarily the best fit for.
And we were paying for the infrastructure too.
The Cost Problem
The other problem was cost.
Cloud SQL uses provisioned instances, and CPU and memory for dedicated-core instances are tied to the instance configuration. Instance pricing is charged while the instance is running.
That means if you provision an instance with more capacity than your workload actually needs, the bill doesn't politely ask:
"Hey, are you using those CPUs today?"
It just keeps running.
And in our case, a large portion of the data workloads didn't need a large always-on relational database.
That made the architecture worth questioning.
The problem wasn't simply:
Cloud SQL = expensive
It was:
- 01Always-on relational capacity
- 02Mostly ingestion
- 03Mostly analytical reads
We were paying for a model that didn't match how the data was being used.
The Architecture Started to Make More Sense
The new direction was much cleaner.
Instead of using Cloud SQL as the main destination for all this data:
- 01Pipelines
- 02Cloud SQL
- 03ConsumersDashboardsServices
we moved toward:
- 01Pipelines
- 02BigQuery
- 03ConsumersDashboardsAnalytics
And Cloud SQL stayed where it made sense:
So instead of forcing one database to do everything, we gave each workload a better home.
That was the core idea behind the migration.
I Led the Change
This wasn't just a technical migration.
I had to understand the existing architecture, identify which datasets were suitable, look at how they were produced, understand how dashboards consumed them, and work out how to move the data without breaking the applications around it.
The challenge was not simply:
It was:
- 01Existing pipelines
- 02Existing schemas
- 03Existing transformations
- 04Existing dashboards
- 05Existing permissions
- 06New analytical architecture
And that meant learning BigQuery properly rather than treating it like "another SQL database."
That was probably the biggest part of the journey.
My Mental Model of a Database Had to Change
Coming from application development, my database mental model was very relational:
BigQuery made me think differently.
The mental model became:
BigQuery uses columnar storage and separates storage from compute, which is one of the reasons it can scale analytical workloads without asking me to provision and maintain a database server in the traditional sense.
That was a major mindset shift.
With Cloud SQL, I was thinking:
"How big does the database instance need to be?"
With BigQuery, I started thinking:
"How should the data be organized so queries only process what they need?"
That led me into concepts like:
- Partitioning
- Clustering
- Query scanning
- Data volume
- Table design
- Cost per query
Partitioning became especially important because filtering on the partitioning column can reduce the amount of data scanned, which improves both performance and cost.
That was a completely different way of thinking about optimization.
Some Queries Needed to Be Rethought
One of the challenges was that not every query could simply be copied from Cloud SQL and expected to behave the same way.
Some application-style assumptions don't translate well into an analytical system.
BigQuery supports UPDATE, DELETE, and MERGE, but Google still recommends avoiding patterns that try to use BigQuery like an OLTP database, especially frequent single-row mutations.
Luckily, that wasn't really our main pattern.
Most of our pipelines were doing something closer to:
rather than:
UPDATE row 123
UPDATE row 124
DELETE row 125
UPDATE row 126
That made BigQuery a much more natural fit.
The Migration Was Also a Data Engineering Lesson
Another thing I learned was that moving data is not the same as moving tables.
A table can exist in both systems and still behave differently.
We had to think about:
- Schema
- Data types
- Historical data
- Incremental loads
- Transformation logic
- Null handling
- Timestamps
- Dashboard queries
- Permissions
- Validation
And then there was the question nobody wants to discover after go-live:
"Why does the dashboard show a different number?"
So validation became important.
We needed confidence that:
- Cloud SQL result
- BigQuery result
at least where the business logic was supposed to remain equivalent.
Google's own migration guidance recommends a staged, iterative approach rather than trying to move everything blindly in one step.
That matched what we were learning in practice.
The End Result Wasn't "Goodbye, Cloud SQL"
One thing I particularly liked about this migration is that we didn't turn it into a religious debate about technologies.
We didn't say:
"Everything must go to BigQuery."
Instead:
That's a much healthier architecture.
Cloud SQL remained for the smaller number of databases that genuinely needed application-driven CRUD.
BigQuery became the better home for the larger analytical datasets powering pipelines, dashboards, graphs, and insights.
The result was also a more appropriate cost model for those workloads because we were no longer maintaining large relational database capacity just to store data that mostly needed to be loaded and queried.
What I Learned
The biggest lesson wasn't really BigQuery.
It was learning to look at how data is used, not just where it currently lives.
Before:
"We have a database, so let's put the data there."
After:
"What kind of workload is this data serving?"
That one question changes the architecture.
And sometimes the best optimization isn't reducing the size of your existing infrastructure.
Sometimes it's moving the workload to infrastructure designed for it.
From Full-Stack to Data Platform
This project was another important step in my transition from application development toward cloud and platform engineering.
I was no longer only building the dashboard consuming the data.
I was starting to work on the path that produced the data:
- 01Source
- 02Pipeline
- 03Transformation
- 04BigQuery
- 05GKE services / dashboards
That gave me a much better understanding of the whole system.
The dashboard isn't just a frontend.
Behind that graph is a pipeline.
Behind the pipeline is infrastructure.
Behind the infrastructure are IAM, storage, compute, networking, CI/CD, and cost decisions.
And once you start seeing that whole chain, you stop thinking of yourself as someone who only writes application code.
You start thinking about the platform that makes the application possible.
Final Thought
When we started, Cloud SQL wasn't "wrong."
It was simply being asked to do too many different jobs.
The migration taught me that good cloud architecture isn't about choosing the most modern service.
It's about matching the service to the workload.
For us, that meant:
Suddenly the architecture made more sense.
And the funny thing is, I started this work thinking I was moving data from one database to another.
I ended up changing the way I think about data itself.
That's probably the more valuable migration.
…
Up to 50 claps per reader