Why BigQuery Breaks the Rules You Learned About Databases
One of the first lessons every software engineer learns about databases is normalization.
Split information into multiple tables.
Remove duplication.
Create primary keys.
Create foreign keys.
Join everything back together when you need the data.
For transactional systems, this advice is excellent.
For analytical systems, it can become surprisingly expensive.
This is one of the biggest mental shifts engineers face when moving from PostgreSQL, MySQL or SQL Server to BigQuery.
Nothing about normalization suddenly becomes “wrong.”
Instead, the problem changes.
And when the problem changes, the architecture must change with it.
Why Normalization Exists
To understand why BigQuery often encourages denormalization, we first need to understand why normalization became so popular.
Imagine an online store.
Every order contains customer information.
Without normalization, the customer’s name, address and phone number would be copied into every order.
If the customer changes their address, thousands of records would need updating.
This creates inconsistency.
Storage grows unnecessarily.
Updates become slow.
Normalization solves this elegantly.
Store customer information once.
Reference it everywhere else.
The database stays clean, consistent and easy to update.
For systems processing thousands of writes every second, this design is almost ideal.
The fewer duplicated values, the fewer updates are required.
That is exactly what OLTP databases optimize for.
BigQuery Rarely Cares About Updates
Now imagine a completely different question.
“Show total revenue by country, marketing campaign and customer segment for the last three years.”
Notice something interesting.
Nobody is updating customers.
Nobody is modifying addresses.
Nobody is inserting individual orders.
The query simply wants to read historical information.
That changes everything.
BigQuery spends most of its life reading data.
Not updating it.
If updates are rare but analytical queries are constant, minimizing duplication becomes much less important than minimizing computation.
This is where traditional normalization begins to lose its advantage.
Every JOIN Is a Conversation
JOIN operations are incredibly powerful.
They allow multiple datasets to behave as one.
But every JOIN also requires work.
BigQuery must identify matching records.
Exchange information between execution stages.
Synchronize distributed workers.
Sometimes shuffle enormous amounts of data across Google’s infrastructure.
None of this is free.
Imagine asking ten librarians to assemble one book by collecting chapters stored in different buildings across a city.
The book can certainly be assembled.
But the process is slower than simply opening one complete volume already sitting on the shelf.
That is why analytical platforms often prefer wider tables.
The goal is not elegance.
The goal is reducing unnecessary work.
Duplication Is Sometimes Cheaper Than Computation
This sentence feels uncomfortable to many database professionals.
“Duplicate the data.”
Years of database education taught exactly the opposite.
Yet analytical systems evaluate cost differently.
Storage in BigQuery is relatively inexpensive.
Distributed computation is not.
Suppose customer country appears in every purchase record.
Yes, the value is duplicated.
But every report can now answer regional questions without joining another table.
The storage footprint increases slightly.
The computational cost decreases every single time analysts run a report.
Over months or years, computation savings often exceed storage costs many times over.
In BigQuery, bytes stored are usually cheaper than bytes processed.
Understanding that economic model explains many architectural decisions that initially seem counterintuitive.
Nested Data Changes the Rules
Google did not simply recommend denormalization.
It introduced entirely new data structures.
Instead of forcing every relationship into separate tables, BigQuery supports STRUCT and ARRAY.
Imagine a customer placing an order containing five products.
A traditional relational database stores the order in one table.
The products appear in another.
A JOIN reconnects them later.
BigQuery allows something different.
The products can live inside the order itself.
Not as repeated rows.
As a nested collection.
When analytical queries need both the order and its products, the information is already together.
The execution engine avoids additional joins.
The architecture reflects how analysts actually explore the business.
Denormalization Does Not Mean Chaos
This is another common misunderstanding.
Some engineers hear “denormalization” and imagine copying everything everywhere.
That is not architecture.
That is entropy.
Effective denormalization is intentional.
Business entities remain clearly defined.
Historical information stays consistent.
Dimensions are duplicated only when doing so reduces computational complexity.
Architects constantly ask one question.
“Will this duplication reduce repeated work across thousands of future queries?”
If the answer is yes, denormalization may be justified.
If the answer is no, normalization may remain the better choice.
Good architecture is never ideological.
It is economical.
Design Around Questions, Not Tables
Perhaps the most important lesson BigQuery teaches is that data modeling begins with business questions.
Traditional systems begin with entities.
Customers.
Orders.
Invoices.
Products.
Analytical systems begin somewhere else.
Revenue.
Retention.
Conversion.
Attribution.
Lifetime value.
Funnels.
Cohorts.
These are not tables.
They are questions.
The tables should exist to answer those questions as efficiently as possible.
This is why experienced analytics engineers often start projects by collecting reporting requirements before creating a single dataset.
The reports determine the model.
Not the other way around.
The Cost of Following Old Habits
Many organizations migrate from PostgreSQL to BigQuery by copying the same normalized schema.
Technically, the migration succeeds.
Architecturally, very little changes.
The same joins remain.
The same relationships remain.
The same reporting logic remains.
Then someone wonders why BigQuery scans far more data than expected.
The answer is simple.
The architecture still thinks like a transactional database.
Moving data into Google Cloud does not automatically create a cloud-native analytical platform.
The design principles must evolve as well.
Architect’s Notebook
Normalization minimizes duplication.
BigQuery minimizes computation.
These goals often point in different directions.
The best analytical model is rarely the most elegant relational model.
It is the one that answers business questions using the least amount of distributed work.
Storage is inexpensive.
Repeated computation is not.
Design accordingly.
Closing Thought
One of the hardest habits to break is believing that every duplicate value is a mistake.
In BigQuery, duplication can be a deliberate investment.
A few extra gigabytes of storage may eliminate billions of unnecessary comparisons over the lifetime of an analytical platform.
Sometimes the most efficient architecture is not the one with the fewest tables.
It is the one that asks the fewest questions during execution.