Denormalization Tradeoffs in BigQuery and Redshift Schemas

Denormalization in BigQuery and Redshift is both a layout decision and a pricing decision. It is the right default for analytical schemas, but the engines reward different forms of it. BigQuery makes nested data and bytes scanned central to the choice, while Redshift makes distribution and sort keys central. A sound schema starts with those differences rather than carrying the same wide-table design from one engine to the other.

Why denormalization is the right default for analytical schemas, and why the engine still determines the mechanics

Columnar warehouses make scans with filters a strong starting point for analytical queries. Reading just the columns needed from a wide table avoids the planner overhead, intermediate results, and network shuffle that chains of multi-way joins add as the data grows. Fivetran's star-schema vs. OBT benchmark ran identical BI-style workloads on Redshift, Snowflake, and BigQuery and found that the denormalized table outperformed the star schema on every warehouse tested. BigQuery showed the largest improvement.

Removing joins is useful, but the next question is where related data lives and what work remains when a query reads it. BigQuery can keep child records inside a parent row, while in Redshift, what matters most is how rows sit across nodes. Treating both choices as “make one big table” misses the mechanism that makes each engine efficient.

How BigQuery's ARRAY<STRUCT> changes what denormalization means at the schema level

BigQuery's native ARRAY<STRUCT> support offers a third layout between a normalized star schema and a fully flattened OBT. A normalized layout keeps tables separate until query time, trading lower storage for more shuffle and greater latency variance. A flattened OBT removes joins but repeats dimension values across rows. Those rows may still need to move across nodes for grouping, and that costs more as cardinality rises.

Nested and repeated fields change that arrangement by storing child records inside the parent row. Related records are co-located by construction, so grouping them on the parent key does not require cross-node shuffle. This is not merely a wide table written with different syntax. It preserves the parent-child structure while removing the need to assemble that relationship through a join.

The choice therefore turns on both the shape of the relationship and the number of tables. Full flattening can remove joins while leaving grouping work that nesting avoids. However, querying nested arrays requires UNNEST, so the layout also changes what the query writer must understand. A Redshift engineer bringing a flat-table design to BigQuery has the right general instinct but may be choosing the less useful form of denormalization.

How BigQuery's byte-scanned billing model makes schema layout a direct cost control

On BigQuery's on-demand model, query cost follows bytes scanned. The physical layout therefore controls more than responsiveness. For a repeated query pattern, it helps determine the bill each time that pattern runs.

Nested fields can reduce scanned bytes relative to full flattening because BigQuery reads only the requested columns and does not scan unreferenced nested structs. Partitioning and clustering extend that control by limiting which data a query touches. Their value is not cosmetic tuning after the schema is finished. A wide denormalized table without either gives up the means to contain its scan cost.

Updates expose the other side of this model. BigQuery bills for data processed, so the scan cost of finding and changing matching rows in a large denormalized table can be high. Frequent updates can therefore erode the savings from cheaper analytical reads.

Why Redshift's denormalization decisions are distribution decisions, not type decisions

Redshift moves the central design question from nested relationships to row placement. Amazon Redshift's documentation on distribution style uses a star schema to show why facts and the dimension they most often join to should share distribution columns. The relevant dimension is the one participating in the most common join. That still matters after denormalization if common queries join the new table to other data.

KEY distribution puts rows with the same key value on the same node, so a matching join can avoid shuffle. However, popular key values can concentrate rows and create skew. A denormalized fact table should therefore use the join key shared with its most-queried dimension. Choosing another key forces data movement back into the common query pattern and can leave the new table worse than the star schema it replaced.

Other distribution styles express different tradeoffs rather than alternatives to understanding the workload. ALL copies a table to every node and works well for small dimensions that change slowly, while EVEN spreads rows round-robin for parallel scans but needs redistribution for joins. AUTO lets Redshift choose based on how big the table is. Provisioned Redshift leaves engineers actively managing distribution, sort keys, and workload queues, whereas Serverless automates some optimization at the cost of some control over worst-case skew.

Where sort keys and compound vs. interleaved choices let a denormalized Redshift table outperform a star schema

Correct distribution solves data movement, but it does not solve data skipping. A mismatched sort key can still force a full columnar scan because zone maps cannot skip the blocks the query does not need. Distribution and sorting are independent decisions. A denormalized table needs both to work well for the savings on joins to lower the cost of the query as a whole.

Compound sort keys work well when queries keep filtering on the same leading column. Their column priority matters, so adding more columns helps only when queries use them in order. A wide table with many possible filter columns is therefore a poor fit for one compound key when no leading filter dominates.

Interleaved sort keys give equal weight to key columns and better suit varied filter patterns. That flexibility comes with more demanding VACUUM and ANALYZE maintenance as data arrives. Without regular vacuuming, the wide table's scan performance can degrade, while smaller normalized dimensions are cheaper to maintain. Unlike BigQuery's automatically maintained clustering, this is an ongoing operational cost that belongs in the Redshift denormalization decision.

How the query pricing boundary between BigQuery and Redshift shifts depending on workload shape

The economic comparison depends on whether a query spends more effort moving data or computing over it. BigQuery's on-demand model charges for bytes scanned, while Redshift's compute model charges for compute time. Wide, IO-heavy scans tend to favor pay-per-compute pricing. Compute-heavy window operations and aggregations over smaller byte ranges tend to favor pay-per-byte pricing.

Srivastava and Castro Fernandez's cross-cloud workload cost analysis treats this difference as a query classification problem. Their multi-pricing model execution reduced individual query costs by as much as 90%. That finding shows why the shape of a query can matter as much as its runtime when comparing costs.

Repeated dashboard scans over large denormalized tables fit Redshift's compute model because byte-scanned charges would accumulate on BigQuery on-demand. Intermittent, unpredictable analyst or agent queries fit BigQuery on-demand because no provisioned cluster sits idle between queries. Sustained high concurrency can favor Redshift provisioned with reserved capacity. These are schema inputs rather than a procurement verdict, since choosing BigQuery for intermittent use and then building an unpartitioned flat OBT still exposes every query to a large scan.

When to preserve normalization in either engine, and the conditions that make denormalization counterproductive

The default stops being useful when storage, update work, or distribution skew costs more than the analytical reads save. Reference data that changes independently of facts is a clear exception because keeping it separate avoids rewriting millions of rows after each change. Frequency of change matters as much as size.

Redshift adds an exception when no single distribution key serves most queries. In that case, a star schema with small ALL-distributed dimensions may beat a wide table whose joins need shuffle or whose key creates skew. High write concurrency also makes MVCC handling and VACUUM overhead harder to absorb on large denormalized tables. These are limits within which the default remains useful.

The shared boundary is a table being asked to serve frequent changes and analytical reads at once. A normalized or append-only staging layer can feed a denormalized analytical layer instead. That separation keeps the write pattern from dictating the read layout. Trying to serve both through one denormalized table risks making it expensive to write and suboptimal to read.

Designing denormalized schemas that agentic workloads can query correctly and cheaply

Separating write and read needs becomes more important when AI agents query the analytical layer. Agents can issue many parallel queries and re-derive joins from schema introspection. A layout built for human BI use may then face a different mix of filters, joins, and scans. Distribution choices and scan boundaries now have to accommodate that access pattern too.

BigQuery's nested layout makes the tradeoff especially clear. Nesting reduces the data an agent needs to touch, but correct results depend on understanding UNNEST, which naive text-to-SQL can get wrong. A flat OBT is easier to query in one scan, yet that simplicity can expose much more data to each request.

Parallel access also changes the cost exposure. Many queries against a large unpartitioned BigQuery table accumulate byte-scanned charges far faster than a human's sequential exploration. Redshift's compute model changes that billing exposure, but it does not remove the need for suitable distribution and query design.

A semantic layer or governed query surface can separate agents from those physical details. Agents select consistently defined metrics instead of composing raw SQL against nested tables or triggering unrestricted full-table scans. The surface should also enforce read-only access by construction. Allowing agents to modify denormalized analytical tables adds cost and correctness risks precisely where the architecture should be separating writes from reads.

A decision framework for choosing the right denormalization pattern for each engine

A dominant query pattern of BI aggregations using fixed dimensions favors denormalization, while filters that vary across many columns give a reason to keep normalized dimensions, especially when Redshift can use ALL distribution for small dimensions. If a wide Redshift table still fits the workload, that same answer must guide the choice of a compound or interleaved sort-key.

The next question is how often dimension data changes. There is more room to denormalize when changes are rare, while keeping small dimensions separate works better when they change often. On BigQuery, when the query and update patterns support denormalization, nested and repeated fields are preferable to full flattening if the querying tools handle UNNEST correctly. Otherwise, a flat OBT with aggressive partitioning and clustering is the next option.

Repeated IO-heavy scans point toward Redshift compute pricing, intermittent access toward BigQuery on-demand, and sustained concurrency toward Redshift provisioned reserved capacity. Those answers must influence layout before the schema is finalized. On Redshift in particular, the distribution key must be settled before committing to the denormalized table.

Human analysts need a schema suited to their query patterns, while parallel agents need a governed abstraction and read-only access. BigQuery nesting also requires confidence that the query surface handles UNNEST correctly. Across both engines, denormalization succeeds when layout, maintenance, access, and pricing are designed together, rather than producing a table that is fast in testing and expensive in production.