Data Normalization Versus Denormalization Decisions

Workload pattern analysis is the process of matching schema shape to how data is written and read. Online transaction processing (OLTP) systems such as Cloud Spanner (opens in a new tab) handle many small, concurrent writes and point lookups where correctness matters most, so they enforce relational normalization. Online analytical processing (OLAP) systems such as BigQuery (opens in a new tab) handle read-heavy scans over billions of rows where speed and scan cost matter most, so they favor denormalization.

The decision starts with two questions: what entities are stored and what queries rank highest by frequency and importance. A schema cannot satisfy every query, so the design optimizes for the most important access patterns first, then iterates after testing with realistic load and query simulations.

1.1Transactional Workloads Require Relational Normalization

Transactional workloads in an OLTP system require normalized schemas to maintain write integrity under concurrent updates. Normalization creates one table for each entity and links related tables at query time with a primary key to foreign key relationship resolved by JOINs. This structure avoids storing the same fact in multiple places, so an update to a customer, order, or location changes one row instead of many duplicated copies.

Row-oriented engines built for OLTP store data in row format using blocks organized in segments, with schemas owned by users and optional pluggable databases for isolation. Fast row lookups and joins depend on indexes, optimizer statistics, and careful capacity planning because storage is pre-provisioned through segments, datafiles, and tablespaces. Choose normalization when the workload is write-heavy, requires strong consistency, performs single-row reads and writes, and cannot tolerate update anomalies from duplicated data.

1.2Analytical Workloads Create Denormalization Incentives

Analytical workloads in an OLAP system favor denormalized schemas to optimize read-heavy queries that aggregate large datasets. Denormalization combines related entities into fewer, wider tables so a query reads pre-joined data in one scan instead of joining many normalized tables at query time. This trades extra storage and more expensive updates for faster reads, lower slot consumption, and simpler queries.

The tradeoff reverses in BigQuery because storage is much cheaper than compute and is effectively infinitely scalable. Data redundancy and duplication are encouraged when they produce faster queries, including techniques such as loading full snapshots into new daily partitions to track slowly changing dimensions (SCD) instead of performing in-place dimension updates. Choose denormalization when queries scan and aggregate far more data than is updated, when the same dimension values are read repeatedly, and when avoiding large multi-table joins saves more compute than the extra storage costs.

1.3Storage and Query Engines Change the Cost Equation

Storage and execution architecture determines why the same normalized design performs differently on OLTP and OLAP platforms. BigQuery decouples data storage and compute by storing data in Colossus in a compressed columnar format called Capacitor, operating directly on compressed data without full decompression. Storage resources allocate as data is consumed and deallocate when data is removed, while compute allocates per query as slots, where each slot bundles CPU and RAM. Datasets are the highest-level abstraction for organizing access, with schemas, labels, partitioning, and clustering used to organize tables and prune unnecessary scans.

BigQuery scales performance at the query level through coordinated optimizations:

• In-memory query execution connected to compute by a high-bandwidth network
• Multilevel tree execution based on the Dremel engine for petabyte-scale aggregation
• Automatic storage optimization and column statistics gathered during load
• 1 petabit per second total bisection bandwidth with Jupiter networking
• Autoscaling resource management with diagnostic query plans and timing

Row-based transactional systems couple CPU, memory, and disk through provisioned nodes or instances, require resizing to change capacity, and rely on tuning of indexes, statistics, and memory parameters. Because BigQuery separates storage from compute and scans columns rather than rows, colocating related values in denormalized or clustered layouts reduces bytes scanned and speeds aggregation in ways that normalization cannot.

1.4Migration Path Toward Denormalization in BigQuery

The migration path toward denormalization in BigQuery is an iterative order that moves from compatibility to native optimization. A flat order record with separate customer, salesperson, and location tables becomes one table where CustomerID, FName, and LName nest into a Customer struct, salesperson fields nest into a Salesperson struct, and location fields nest into a Location struct, while OrderNum and amount remain top-level because they are unique to the order. Marking Customer as repeated allows one order to carry both a primary and a secondary customer without a separate bridge table or join.

Related design choices support the same goal: create surrogate keys with row_number and partition by instead of sequences, use daily snapshot partitions for history instead of complex SCD update types, and cluster on columns frequently used for filtering so blocks colocate similar values. Migration follows an iterative order:

1. Transfer initial schema unchanged: Move the existing normalized schema without changes so pipelines, training, and automated tools keep working.
2. Apply light optimizations: Add partitioning and clustering for the highest-ranked queries and validate with key use cases.
3. Evolve to nested structures: Convert high-join fact-to-dimension relationships into nested and repeated fields.
4. Validate history and key strategy: Replace update-heavy SCD logic with snapshotting and surrogate-key queries, then retest performance and cost.

The historical warehouse models map to this evolution, but they are not the endpoint:

Evaluation of storage, compute, and financial implications is the comparison of how normalized and denormalized schemas change bytes stored, compute consumed, query speed, and Google Cloud billing in BigQuery (opens in a new tab). BigQuery separates compute, memory, and storage and bills for bytes scanned or for slot-hours consumed, so schema shape directly controls cost and latency. Flat, wide denormalized tables generally store less, scan less, and finish faster than highly normalized star or snowflake schemas carried over as-is.

2.1Normalized Star and Snowflake Models in a Columnar Warehouse

Normalized star and snowflake models in a columnar warehouse are legacy relational designs that preserve joins at the cost of extra compute. A star schema is a denormalized model where a central fact table collects metrics such as order amount, discount, and quantity along with keys to surrounding dimension tables such as customer, supplier, and region. A snowflake schema is similar to a star schema, but its dimension tables are further normalized, which creates more tables and more joins.

BigQuery supports both star and snowflake schemas, but keeping that model beyond initial migration leaves existing technical debt, limits query optimization, and forces optimizations to be redone if the schema later changes. The dependency runs from the fact table to the dimension tables at query time, so every analytic query must re-resolve those joins across query workers.

2.2Flat Wide Tables With Nested and Repeated Fields

Flat wide tables with nested and repeated fields consolidate all dimension attributes into a single record in the fact table instead of keeping multiple dimension tables linked by foreign keys. BigQuery native representation uses nested and repeated fields rather than star or snowflake joins, which aligns the schema with the internal Dremel model and Capacitor columnar storage format. Dimension data therefore flows from separate customer, supplier, region, and employee tables into one enriched fact record, and queries read that record without joining across tables.

Changing to nested and repeated fields reduces the number of joins required and is an evolutionary improvement after initial migration, light optimization, and testing of key use cases. The relationship is preserved inside the row rather than resolved between tables, so co-accessed parent and child values are scanned together.

2.3Storage Footprint and Physical Table Design

Storage footprint and physical table design in BigQuery is governed by columnar storage, partitioning, and clustering rather than by row blocks, indexes, or vacuuming. Oracle and other row-format systems store a full table row in data blocks, so analytic queries fetch unneeded columns inside the block and rely on star, snowflake, and vault modeling to work around fixed memory and storage on a server. BigQuery stores data in columns with no indexes, no reshuffling of data blocks, and no downtime for version upgrades, and a flat table structure is better for storage utilization than keeping multiple data warehouse dimension tables.

Partitioning and clustering control how much stored data a query must touch. Partitioning divides a table into segments called partitions without changing the existing table structure, and queries over smaller partitions are more performant because they scan only a subset of data and reduce billed bytes. BigQuery supports three partitioning approaches:

• Tables partitioned by ingestion time, where BigQuery adds pseudocolumns to partition data automatically during ingestion
• Tables partitioned by column, based on a TIMESTAMP or DATE column
• Tables partitioned by integer range, based on an integer column

Clustering automatically sorts data based on the values of a few specified columns and colocates similar values in optimally sized blocks. Clustering helps BigQuery better estimate query cost, eliminate scans of unnecessary data, and calculate aggregates quicker because blocks colocate records with similar values. Examine queries for columns frequently used for filtering and create clustered tables on those columns.

2.4Compute Behavior, Slot Utilization, and Query Latency

Compute behavior, slot utilization, and query latency in BigQuery are determined by slots, a unit of computational capacity where Google manages the underlying infrastructure. When a query runs, BigQuery uses massively parallel architecture and adds more machines to scan data and aggregate results concurrently, so larger scans consume more parallel compute and scale well to huge datasets. Denormalized tables eliminate expensive join operations and in most cases provide better analytic performance, which means fewer join stages, lower slot-time per query, and lower latency under concurrency.

Slot pressure is observed through monitoring rather than fixed server limits. High wait times in query execution plans combined with high slot utilization shown in audit logs and system tables can indicate that additional slots are needed to support the workload. Monitoring uses Cloud Monitoring, analysis of audit logs, and system-tables data for jobs and reservations to estimate how many total slots an organization needs as it grows. For example, an initial reservation of 4,000 slots to run 100 medium-complexity queries simultaneously may still show queuing if normalized joins keep slot consumption high.

2.5Financial Trade-Offs and Reservation Planning

Financial trade-offs and reservation planning connect schema choice to two billing dimensions plus reservation structure. Financial impact comes from two billing dimensions: bytes read per TiB under on-demand pricing and slot-hour consumption under capacity pricing, plus storage for bytes retained. Partitioned and clustered flat tables lower both dimensions by scanning fewer bytes per query and holding slots for less time, while normalized schemas that repeatedly join large fact and dimension tables increase bytes processed and slot-hours burned.

Capacity can be managed with buckets of slots called reservations that are assigned to projects, folders, or organizations. Slot commitments start at a minimum of 100 slots with flex, monthly, or yearly commitments, and queries running in one reservation automatically use idle slots from other reservations. A total commitment of 1,000 slots, for example, can be split into separate reservations for data science, extract-load-transform, and business intelligence workloads so predictable reporting and extract-load-transform concurrency does not starve interactive queries.

Choose a flat, wide denormalized design when reads dominate, business needs favor fewer joins, and flexibility, scalability, and performance matter more than minimizing redundancy. Keep star or snowflake only for initial iterations or when join-based compatibility must be preserved before applying partitioning, clustering, and nested fields.

Semi-normalized design in BigQuery (opens in a new tab) preserves relational logic inside a single table by using nested and repeated fields instead of spreading entities across many joined tables. STRUCT groups related columns into a single parent field, and ARRAY allows that field to hold multiple values per row. This native representation aligns with how BigQuery organizes data with the Dremel model and stores it in the Capacitor columnar format, so queries can read hierarchical relationships without paying the computational overhead of relational joins.

3.1STRUCT and ARRAY Types for Hierarchical Relationships

STRUCT and ARRAY are the BigQuery data types that implement nested and repeated fields for hierarchical relationships. A STRUCT nests related attributes together, such as customer identifier and name fields inside one Customer value, so the child attributes travel with the parent row. An ARRAY makes a field repeated, which means one row can contain a list of values for that field instead of requiring a separate table with a foreign key.

Together, STRUCT and ARRAY let a single BigQuery table preserve the one-to-many relationship that a normalized design would express with a primary key, a foreign key, and a JOIN. Because BigQuery stores data in Capacitor and organizes it with the Dremel model, this nested layout matches the internal storage representation. BigQuery operates directly on compressed columnar data, so reading a nested STRUCT avoids shuffling separate tables across query workers to resolve a join.

3.2Conversion From Flat Relational Models to Nested Form

Conversion from flat relational models to nested form means grouping dimension attributes into STRUCTs while leaving unique fact attributes at the top level of the row. In a traditional normalized design, each entity has its own table and relationships are defined by keys linked at query time with JOINs. BigQuery nested and repeated fields preserve the same relationship by embedding the related entity data inside one table, for example by keeping all customer data with the orders data nested for each customer.

A documented order example converts fields as follows: CustomerID, FName, LName nested into a new field called Customer; SalesPersonID, FName, LName nested into a new field called Salesperson; and LocationID, city, state, zip code nested into a new field called Location. Fields OrderNum and amount are not nested, because they represent unique elements of each order fact. To allow every order to have more than one customer, such as a primary and a secondary customer, the Customer field is marked as repeated, so one order row holds an ARRAY of STRUCT values.

3.3Performance, Cost, and Scalability Trade-Offs of Nesting

Performance, cost, and scalability trade-offs of nesting center on exchanging inexpensive storage for expensive query compute. Storage in BigQuery is much cheaper than compute and effectively infinitely scalable, so data redundancy and duplication are encouraged when they produce faster queries. Denormalization through nesting eliminates repeated key lookups and large join fan-outs, which reduces the slots, CPU, and memory needed during execution and can lower the bytes scanned when only the parent row and its embedded children are required.

The trade-off does not always favor nesting, and denormalization using nested and repeated fields does not lead to performance improvements in every case. Highly selective queries that need only a small dimension slice, frequently updated dimensions that would force rewriting large nested rows, and schemas that exceed nested limits can erase the join savings. Before committing to a nested design, evaluate query patterns for join frequency and co-access, confirm limits and restrictions for specifying nested and repeated columns, and review guidance for using nested fields for performance and for handling schema changes.

3.4Selection Among Star, Snowflake, and Nested Designs

Selection among star, snowflake, and nested designs is a choice of three ways to organize facts and dimensions in a BigQuery data warehouse. BigQuery supports star and snowflake schemas carried over from migration, but changing to nested and repeated fields is an evolutionary choice that reduces joins and aligns the schema with Capacitor storage.

Choose the nested design when child entities are almost always queried with their parent, when join-heavy star queries dominate slot usage, and when the hierarchy is stable enough to manage as one table. Keep star or snowflake form during initial migration to avoid changing pipelines and retraining users, then evolve high-value use cases to nested tables once key queries are tested and the denormalization benefit is confirmed.

Key Facts

• Cloud Spanner as an OLTP system enforces relational normalization for concurrent writes and point lookups, while BigQuery as an OLAP system favors denormalization for read-heavy scans and aggregations.
• BigQuery decouples storage in Colossus using the Capacitor columnar format from compute allocated per query as slots that bundle CPU and RAM.
• Flat wide tables with nested and repeated fields consolidate dimensions into one fact record to reduce joins, lower bytes scanned, and lower slot-hours compared to star and snowflake schemas.
• STRUCT groups related columns into one parent field and ARRAY allows multiple values per row, preserving one-to-many relationships inside a single BigQuery table without foreign-key joins.
• Slot commitments start at a minimum of 100 slots with flex, monthly, or yearly commitments, and reservations can isolate workloads such as data science, extract-load-transform, and business intelligence.