Designing BigQuery Analytical Data Models

BigQuery (opens in a new tab) partitioning and clustering control physical storage layout to reduce data scanned and slots consumed. BigQuery separates storage in Colossus from execution in Dremel and stores data compressed in a columnar format called Capacitor. Partitioning divides a large table into smaller segments by a key, while clustering sorts rows inside those segments so similar values sit together. Together they let the optimizer prune data before execution and lower both bytes processed and slot time.

BigQuery gathers column statistics during load and persists them during Capacitor encoding. The optimizer uses those statistics to compile plans and choose efficient runtime algorithms. Query resources are allocated as slots, units of computation that include CPU and RAM, under a fair scheduler. With on-demand pricing charges follow bytes processed and with capacity pricing charges follow reserved capacity, so eliminating scans directly lowers cost and contention.

1.1Partitioned Tables for Pruning and Lifecycle Management

Partitioned tables divide a BigQuery table into independent segments by a partition key so queries that filter on that key read only matching segments. Choose the partition key that matches the most frequent and selective filter, because only queries that filter on that key benefit from pruning. The three partition key families are:

• Time-unit partitioning on a DATE, TIMESTAMP, or DATETIME column for append-only event tables
• Ingestion-time partitioning on arrival time using automatic pseudocolumns
• Integer-range partitioning on an integer column for numeric ranges

Time-unit partitioning is common for append-only tables such as an orders table where new orders are appended to the partition for the current date. Partitioning also supports lifecycle management because storage is allocated as data arrives and deallocated when partitions or tables are removed. Older partitions are often static, so retention, export, and deletion can operate on partitions rather than the whole table. When a recent window can still change, the backup must include that full window, such as exporting both the current-date partition and the prior 7 days when returns can update orders within 7 days.

Partitioned exports flow from the source partitions to a new staging table to Cloud Storage (opens in a new tab), which keeps recovery focused on appended and recently updated data. A partitioned backup and restore for an append-only table follows an ordered workflow:

1. Copy required partitions: Create a copy of the required partitions in a new table from a query result, including new partitions and any mutable recent window.
2. Run export job: Run a BigQuery export job on the new table to Avro, JSON, Parquet, or ORC files in Cloud Storage.
3. Transfer files: Transfer the exported files with gcloud storage to the backup location on-premises or in another cloud, accounting for egress charges and export limits.

1.2Clustered Tables for Filter and Aggregate Efficiency

Clustered tables sort rows by up to several clustering columns so Capacitor blocks colocate records with similar values. When a query filters or aggregates on those clustered columns, BigQuery skips unnecessary blocks and calculates aggregates faster because related values are already grouped. Clustering also helps BigQuery better estimate query cost than without clustering. Examine query access patterns for columns frequently used for filtering and create tables with clustering on those columns.

Clustering works best after partitioning, not as a replacement for it. Partitioning first prunes whole segments by date, ingestion time, or integer range, then clustering prunes blocks inside the remaining partitions by high-cardinality filters such as customer ID, region, or status. Because Capacitor operates directly on compressed data without decompressing, colocated values compress better and scan faster through the petabit Jupiter network and in-memory execution. The result is fewer bytes read from Colossus, shorter slot hold time, and more predictable performance.

1.3How Partitioning and Clustering Reduce Slot Consumption

Slots in BigQuery are the unit of computational capacity required to execute SQL queries, and each query uses slots based on its type and complexity. BigQuery continuously calculates how many slots queries require as they execute, but allocates them through fair scheduling across projects, users, and reservations. By default Google Cloud projects are limited to a maximum of 2,000 slots, so uncontrolled full-table scans can queue or throttle concurrent work. Partition pruning and block elimination reduce the data that Dremel workers must read, shuffle, and aggregate, which reduces both bytes billed on demand and slots consumed under capacity pricing.

BigQuery combines several execution optimizations that amplify the benefit of a good layout:

• In-memory query execution keeps hot intermediate data close to compute slots connected by the petabit network.
• Dremel multilevel tree architecture distributes work across many servers and aggregates results up the tree.
• Automatic storage optimization within Capacitor uses persisted column statistics to pick efficient runtime algorithms.
• Jupiter network bandwidth provides high bisection bandwidth for shuffle between storage and compute.
• Autoscaling resource management scales query resources to petabyte scale without manual cluster resizing.

BigQuery (opens in a new tab) temporal models preserve how business records evolve over time so historical analysis stays accurate after source values change. The design combines a logical schema for facts and dimensions, an append-only strategy for history with change data capture (CDC), incremental updates through controlled pipelines, and a physical layout that makes time-based queries fast and cheap. Facts continue to reference the dimension state that was true at the time of the event, rather than the current overwritten value.

2.1Logical Models for Facts and Evolving Dimensions

Logical models for evolving dimensions define where metrics live and where descriptive attributes that change over time live. In a star schema, a central fact table collects metrics such as order amount, discount, and quantity, along with keys to dimension tables such as customer, supplier, and region. In a snowflake schema, the same fact-to-dimension pattern is used, but dimension tables are normalized, which gives the schema the appearance of a snowflake. BigQuery supports both star and snowflake schemas, but its native representation uses nested and repeated fields for a more natural representation of data.

Changing a migrated schema to use nested and repeated fields is an evolutionary choice that reduces joins. Simply migrating tables as-is still removes no-index rebuilds, no reshuffling of data blocks (vacuuming), and no downtime from version upgrades. Keeping that as-is model beyond initial iterations has disadvantages:

• Existing issues and technical debt associated with the schema remain.
• Query optimizations are limited, and they might need to be redone if the schema is updated later.
• The design does not take full advantage of other BigQuery features, such as nested and repeated fields, partitioning, and clustering.

2.2Append-Only History and Change Data Capture

Append-only history is the practice of adding new records for changes rather than overwriting older data, which matches how data warehouses store historic information. Most older warehouse data is often static, and many warehouses are designed to be append-only so prior states of customer, product, or region records remain queryable. If a warehouse is append-only, recovery can focus on the appended data, where only the appended data is backed up and then restored to bring the warehouse back with a subset of data.

Change data capture (CDC) is a software design pattern used to track data changes from various source systems over time. In warehousing, CDC collates changes from sources and feeds them into the warehouse so record evolution is captured rather than lost. Data warehouses are typically updated using extract, transform, and load (ETL) or extract, load, and transform (ELT) patterns, which provide controlled update paths. When update paths are controlled, recent update events can be reproduced from alternative sources, which helps maintain integrity of the temporal record.

2.3Incremental Loads With Staging and Controlled Update Paths

Incremental loads with staging apply source changes in controlled batches instead of reloading full history. In the ELT pattern, data flows from the source system into a staging table (opens in a new tab) in BigQuery using extract and load options, then SQL queries transform the data in the staging table and write the output into the final production table. The staging table isolates raw arrivals from curated history, so transformation logic can deduplicate arrivals, apply business rules, and append only validated new versions. Because the flow moves first from source to staging and only after validation from staging to production, errors can be corrected before they affect historical facts and dimensions.

Initial ingestion pipelines should not be over-perfected because those pipelines will need to adapt away from the existing data warehouse. Early iterations should move the initial schema to the cloud, perform light optimizations, and test key use cases before evolving toward nested fields and final time-based optimizations. This order prevents wasted work on a temporary model while still preserving change history needed for later analysis.

2.4Time-Optimized Layout for Historical Queries

Time-optimized physical layout in BigQuery makes historical queries scan less data without using indexes. BigQuery uses no indexes to query data, and its performance comes from its underlying model, storage and query techniques, and massively parallel architecture where more machines are added to scan data and aggregate results concurrently as more data is processed. Partitioning divides data into segments called partitions based on a TIMESTAMP or DATE column, or BigQuery can add pseudocolumns to automatically partition data during ingestion. Queries that involve smaller partitions scan only a subset of data, which improves performance and reduces cost by minimizing bytes read.

Clustering further optimizes history by automatically sorting data based on the values of a few columns and colocating them in optimally sized blocks. With clustered columns, BigQuery can better estimate query cost, eliminate scans of unnecessary data, and calculate aggregates quicker because blocks colocate records with similar values. Designers should examine queries for columns frequently used for filtering, such as event date and changing dimension keys, and create tables with clustering on those columns. Partitioning does not impact the existing structure of tables, so partitioned tables should be considered even when the logical schema is not yet modified.

BigQuery (opens in a new tab) denormalization and schema optimization is the shift from normalized relational models designed for row storage to flat, columnar-friendly structures that reduce joins and bytes scanned. Keeping a migrated star or snowflake schema as-is avoids rework during initial iterations and still removes index rebuilds, vacuuming, and upgrade downtime, but retaining it long term preserves technical debt, limits query optimization, and blocks native features such as nested and repeated fields, partitioning, and clustering. Long-term design should therefore be based on reads and business needs rather than on minimizing redundancy.

Keeping an unchanged warehouse model beyond initial migration has three related costs:

• Existing issues and technical debt associated with the schema remain.
• Query optimizations are limited, and they might need to be redone if the schema is updated at a later stage.
• The design does not take full advantage of other BigQuery features, such as nested and repeated fields, partitioning, and clustering.

3.1Traditional Star, Snowflake, and Denormalized Models

Traditional star and snowflake schemas organize analytics around a central fact table joined to dimension tables, while a denormalized BigQuery model consolidates dimensions into the fact record itself. A star schema is a denormalized model where a fact table collects metrics such as order amount, discount, and quantity along with keys to dimension tables such as customer, supplier, and region, with the fact table in the center surrounded by dimensions. A snowflake schema is similar to the star schema, but its dimension tables are normalized, giving the schema the appearance of a snowflake.

In a sales example, an orders fact table with OrderNum as primary key holds foreign keys to employee, location, and customer tables in star form, while the flattened form consolidates all employee, location, and customer attributes into a single record per order. The flattened form reads the order and its attributes from one table scan rather than joining multiple tables on every query.

3.2Why Columnar Storage Changes the Normalization Tradeoff

Columnar storage in BigQuery reverses the traditional reason for normalization because redundant values cost little to store but joins cost much to compute. Oracle (opens in a new tab) and similar row stores save each table row in data blocks, so analytic queries that filter or aggregate specific columns still fetch unneeded columns within the block. Those systems also use a shared-everything architecture with fixed hardware dependencies for memory and storage, which is why star, snowflake, and data vault techniques evolved to improve storage efficiency and query performance.

BigQuery uses a columnar format to store data and does not have fixed storage and memory limits, with massively parallel execution that adds machines to scan data and aggregate results concurrently as more data is processed. Because only referenced columns are read and repeated dimension values compress well, creating a fact table in a flat table structure is better for storage utilization than using multiple dimension tables, while also reducing JOIN usage. This architecture lets teams denormalize further and design schemas based on reads and business needs, reducing complexity and improving flexibility, scalability, and performance.

3.3Nested and Repeated Fields for Join Elimination

Nested and repeated fields are the BigQuery-native way to represent one-to-many relationships inside a single row instead of across joined tables. Changing a schema to use nested and repeated fields is an evolutionary choice that reduces the number of joins required and aligns the schema with the BigQuery internal data representation. Internally, BigQuery organizes data using the Dremel model and stores it in a columnar storage format called Capacitor.

Data flows from source dimension tables into nested structures within the fact table, so a query reads the order and its embedded customer, employee, and location attributes from one table scan rather than joining four tables. Denormalized tables built this way eliminate expensive join operations that dominate cost and latency in star and snowflake queries. One of the main goals of relational normalization is to reduce data redundancy, a goal best suited to row-format databases, while denormalization is preferable for columnar databases where scan reduction matters more than avoiding duplication.

3.4Optimizing Physical Layout Without Remodeling

Physical layout optimization in BigQuery controls how much data a query scans without forcing a logical remodel. Partitioning divides data into segments called partitions based on a TIMESTAMP or DATE column, or BigQuery can add pseudocolumns to partition data automatically during ingestion. Queries that involve smaller partitions are more performant because they scan only a subset of the data, and they reduce costs by minimizing the number of bytes read, and partitioning does not impact the existing structure of tables.

Clustering automatically sorts data based on the values of a few specified columns and colocates rows with similar values in optimally sized blocks. No indexes are used to query data in BigQuery, but with clustered columns queries eliminate scans of unnecessary data and calculate aggregates quicker because blocks colocate similar values, while BigQuery can also better estimate the cost of running the query. Examine queries for columns frequently used for filtering and create tables with clustering on those columns, a technique that can be considered when migrating list-partitioned or index-organized tables from Oracle.

Designing the data model connects logical shape, temporal history, and physical layout into one decision. The logical shape favors denormalized facts with nested and repeated fields to eliminate joins, the temporal strategy favors append-only history with staging and ELT to preserve evolving dimensions, and the physical layout favors partitioning by the dominant time or range filter with clustering on additional filter columns to prune partitions and blocks and reduce slot consumption.

Key Facts

• BigQuery separates storage in Colossus from execution in Dremel and stores data compressed in the columnar Capacitor format.
• Partitioning divides a table by time-unit, ingestion-time, or integer-range keys so queries that filter on the key scan only matching segments, while clustering sorts rows inside those segments to skip blocks and speed aggregations.
• Google Cloud projects are limited by default to a maximum of 2,000 BigQuery slots, so partition pruning and block elimination reduce queuing by lowering bytes read and slot time.
• BigQuery supports star and snowflake schemas, but its native representation is nested and repeated fields, which consolidate dimension attributes into the fact row and eliminate expensive joins.
• In the ELT pattern, data flows from the source system into a staging table in BigQuery and only after validation from the staging table into the final production table.