BigQuery is Google's data warehouse that lets you run massive analytical queries on billions of rows in seconds. But without careful schema design — the way you organise your tables, columns, and data types — your queries can become slow, expensive, and frustrating. For the PCDE exam, you must master partitioning, clustering, and denormalisation because they directly affect performance, cost, and how Google bills you for data scanned.
Jump to a section
A simple way to picture Schema Design for BigQuery: Partitioning, Clustering, and Optimized Storage
Your company has 40,000 customer files crammed into a single, enormous filing cabinet. Every time you need a report — say, all orders from last July — you must pull open the heavy drawer and flip through every single file, one by one, from A to Z. It takes hours. Your back aches. You waste whole afternoons just finding the right papers.
Now imagine you buy 12 smaller file boxes, one for each month of the year. That is partitioning. When you need July's orders, you go straight to the July box and ignore the other 11. You save 92% of the search time immediately.
Inside the July box, you still have thousands of folders. But what if you organise them alphabetically by customer surname? That is clustering. When you need orders from customers whose surname starts with 'S', you open the July box, find the 'S' section, and pull only a handful of folders. You don't touch 'A', 'B', or 'C'.
Finally, instead of keeping each customer's name, address, and order history in separate paper slips that you have to cross-reference, you staple them all together on one colour-coded sheet. That is denormalisation — putting related data together so you never have to flip between different cabinets.
Together, these three tricks turn a four-hour search into a 30-second grab.
Schema design in BigQuery is about deciding how your data is physically arranged on disk before you ever run a query. The three main tools are partitioning, clustering, and denormalisation. Each solves a different problem, and they work best when combined.
Partitioning means splitting a big table into smaller, manageable segments based on a column that contains a date or timestamp. Instead of one giant table with 10 years of sales data, you have 120 monthly segments. When you ask for 'sales from January 2024', BigQuery knows it only needs to read the January 2024 segment. It completely ignores the other 119 segments. This is called 'pruning' — cutting away data you do not need. Partitioning reduces the amount of data scanned, which directly lowers your query cost because BigQuery charges per byte processed. Common partition columns are 'order_date', 'event_timestamp', or 'ingestion_time'. You can partition by day, month, or year, or by a special pseudo-column called '_PARTITIONTIME' that BigQuery adds automatically when you use ingestion-time partitioning.
Clustering is a second layer of organisation within each partition. When you set a clustering column — for example, 'customer_id' or 'country' — BigQuery sorts the rows inside each partition by that column and stores similar values physically close together. If you filter on a clustered column, BigQuery can skip entire blocks of data that do not match your filter. Clustering columns can be up to four columns and are best for columns that are frequently used in WHERE clauses or GROUP BY operations. For example, if you partition by 'order_date' and cluster by 'store_id', a query asking for 'all orders from store 33 on 1st January 2024' will only read the January 1st partition (thanks to partitioning) and then only the blocks within that partition that contain store 33 records (thanks to clustering). The performance gain multiplies.
Denormalisation is the deliberate choice to combine related data into a single table, even if that means storing the same information multiple times. In a normalised database, you might have separate tables for customers, orders, and products, and use JOINs to bring them together. In BigQuery, JOINs are expensive because they require shuffling data between computing nodes. Denormalisation moves frequently accessed columns — like customer name, product description, and order total — into one flat table. It saves the cost and time of JOINs. The trade-off is increased storage size and the risk of data inconsistency if you update information in only some rows. BigQuery handles this well because it is designed for append-only, immutable data — you usually add new rows rather than update old ones.
How they work together: Imagine a table of 1 billion e-commerce transactions. You partition by 'order_date' (daily partitions). Inside each daily partition, you cluster by 'customer_id' and 'country'. You denormalise by including the customer's name, email, and loyalty tier directly in the transaction table instead of keeping them in a separate customer table. A query for 'total spend by British customers on 15th May 2024' will scan only the 15th May partition, read only the blocks sorted for 'UK' and matching customer IDs, and will have all the data it needs in one table — no JOINs needed. This combination can reduce data scanned by 90% or more compared to a poorly designed schema.
Why this matters for the exam: The PCDE exam expects you to know the exact syntax for creating partitioned and clustered tables, the limits (maximum partition columns, maximum clustering columns, data types allowed for each), and when to choose one strategy over another. You also need to understand the cost implications: partitioning on a high-cardinality column like 'transaction_id' is useless because each row has a unique value, so no pruning happens. Clustering on a column with very few distinct values — like 'gender' (male/female) — also provides little benefit because blocks still contain many mixed values. The exam tests your ability to spot these traps.
Analyse Your Query Patterns
List the most common queries that will run on the table. Note which columns appear in WHERE clauses, which columns are used for date filtering, and which columns are often joined with other tables. This analysis determines your partition and clustering choices.
Choose a Partition Column and Type
Select a DATE, TIMESTAMP, DATETIME, or INTEGER column that will be used in date or range filters. Decide between time-unit partitioning (DAY, HOUR, MONTH, YEAR) or integer range partitioning (for numeric ranges like customer ID groups). Ensure the partition column has a reasonable number of distinct values — not so few that pruning is useless, not so many that you hit the 4,000 partition limit.
Select Clustering Columns
Pick up to four columns that are frequently used in filter conditions (WHERE clauses) and have high cardinality (many distinct values). Avoid columns with very few distinct values. Cluster columns that are often used together in queries to maximise block-level pruning.
Design the Denormalised Schema
Identify columns from related tables that are consistently accessed alongside the main table. Add them directly to the table to eliminate JOINs. For example, include customer_name and product_category in the transaction table. Accept the storage overhead for improved query speed and reduced complexity.
Create the Table with the Correct DDL
Write the CREATE TABLE statement using `PARTITION BY` and `CLUSTER BY` clauses. Example: `CREATE TABLE mydataset.sales (order_date DATE, customer_id INT64, amount FLOAT64, product_name STRING) PARTITION BY order_date CLUSTER BY customer_id, product_name;`. After creation, load data and monitor query performance to verify pruning is working.
Let us walk through a realistic scenario. You work for a retail company called ShopFast that sells electronics online. You are tasked with designing the BigQuery schema for a table called 'order_events' that stores every click, cart addition, checkout attempt, and purchase. The table gets 50 million new rows per day. The analytics team needs to run three types of queries:
Daily sales dashboard: 'What were total sales by product category for yesterday?'
Customer behaviour analysis: 'How many times did customer X add a product to cart before purchasing?'
Regional reports: 'Which countries had the highest conversion rate last month?'
Step 1: Choose the partition column. You decide to partition by 'event_timestamp' using a column of type TIMESTAMP. You set the partition type to DAY because the analytics team runs daily reports and the table grows by 50 million rows a day. Partitioning by hour would create too many small partitions (720 per month) which is inefficient. Partitioning by month would mean a single partition holds 1.5 billion rows — too large for pruning to be effective. Daily is the sweet spot.
Step 2: Choose clustering columns. Within each daily partition, you cluster by two columns: - 'customer_id' (high cardinality, frequently filtered in behaviour queries) - 'event_type' (a string with values like 'page_view', 'add_to_cart', 'purchase' — moderate cardinality, frequently used in WHERE clauses)
This means that when the analytics team queries 'all purchase events for customer 12345 on 1st June 2024', BigQuery goes straight to the 1st June partition, finds only the blocks containing customer 12345's data, and within those blocks, only the rows where event_type is 'purchase'. The rest of the 50 million rows in that partition are never touched.
Step 3: Denormalise. Instead of having separate 'customers' and 'products' tables, you include the customer's name, email, loyalty tier, and the product name, category, and price directly in the 'order_events' table. This saves two JOINs per query. The storage cost goes up because the same customer name appears in millions of rows, but BigQuery's columnar storage compresses repeated values very well — the actual storage increase is often less than 10%.
Step 4: Monitor and adjust. After a week, you notice that queries filtering by 'region' (a column not in the clustering set) are still scanning the entire partition. You add 'region' as a third clustering column. Clustering is free and can be changed without recreating the table, so you simply run an ALTER TABLE statement. BigQuery will reorganise the data automatically during the next write operation.
The result: The daily sales dashboard query that used to scan 50 GB per run now scans 200 MB. The monthly bill drops from $1,500 to $60 for that table alone. The analytics team gets results in 3 seconds instead of 45 seconds. You have successfully designed a schema that balances query performance, storage cost, and operational simplicity.
The PCDE exam tests your understanding of schema design in BigQuery through multiple-choice and multiple-select questions. Here is exactly what you need to know:
Partitioning details you must memorise: - The three partition types: INTEGER range partitioning, time-unit partitioning (DAY, HOUR, MONTH, YEAR), and ingestion-time partitioning (using _PARTITIONTIME). - Maximum partitions per table: 4,000 (for time-unit partitioning) and 10,000 (for integer range partitioning). - Partition columns must be of type DATE, TIMESTAMP, DATETIME, or INTEGER (for range partitioning). - A table can have only ONE partition column. - Partitioning does not reduce storage — it only reduces the amount of data scanned.
Clustering details you must memorise:
- You can specify up to FOUR clustering columns.
- Clustering columns are specified in the CREATE TABLE statement using CLUSTER BY (column1, column2, ...).
- Clustering works best on columns with high cardinality (many distinct values) that are frequently used in filters and sorts.
- Clustering can be used WITHOUT partitioning — but the combination is stronger.
- There is no extra cost for clustering; you only pay for the storage and the data scanned.
Common exam traps: - Trap 1: The question says 'partition by transaction_id because each transaction is unique'. This is wrong — partitioning requires a column with a reasonable number of distinct values (like date). Transaction ID is too granular, so each row becomes its own partition, which hits the 4,000 partition limit immediately. - Trap 2: The question says 'cluster by gender (male/female)'. Clustering on a column with only two distinct values provides almost no benefit because each block will contain both values. The exam expects you to recognise this as a poor choice. - Trap 3: The question asks for 'the best way to reduce JOIN cost'. Correct answer is denormalisation, not partitioning or clustering. Denormalisation eliminates the need for the JOIN entirely. - Trap 4: The question claims 'partitioning and clustering are set in stone after table creation'. False — you can change clustering columns with ALTER TABLE, and you can change partitioning for partitioned tables (with some restrictions on time-unit partitions).
What they love to test: - Which data types are valid for partition columns. - The difference between partitioning and clustering (partitioning splits by column value range; clustering sorts within a partition). - When to use integer range partitioning vs. time-unit partitioning. - The maximum number of clustering columns and partition columns. - The impact of denormalisation on storage vs. query cost.
Cost model understanding: BigQuery charges for the data scanned by your query, not for the data stored (except for active storage and long-term storage rates). A well-designed schema reduces scanned bytes. The exam may give you a table size and ask you to estimate the cost savings of adding partitioning or clustering. Practise calculating bytes scanned reduction: if a table is partitioned by day and a query filters to one day, you save approximately (number of partitions - 1) / total partitions of the scan cost.
Partitioning splits a table by a date or integer column, allowing BigQuery to scan only the relevant segments and ignore the rest.
Clustering sorts rows within each partition on up to four columns, enabling block-level pruning for filter-heavy queries.
Denormalisation removes expensive JOINs by storing frequently related data together in one table, at the cost of increased storage and possible data duplication.
A table can have only one partition column but up to four clustering columns; the combination provides the greatest performance gains.
Change clustering columns any time with ALTER TABLE without recreating the table; partition type changes may require table recreation depending on the partition column.
The PCDE exam loves testing the limits: 4,000 max partitions, valid partition data types, and the distinction between partitioning and clustering.
Cost in BigQuery is based on bytes scanned, not rows returned — so reducing scanned data directly reduces your bill.
Never partition on high-cardinality columns like transaction IDs, and never cluster on low-cardinality columns like gender.
These come up on the exam all the time. Here's how to tell them apart.
Partitioning
Splits a table into separate physical segments based on a column value.
Reduces data scanned by ignoring entire partitions that do not match the filter.
Limits: one partition column per table, maximum 4,000 partitions.
Clustering
Sorts rows within a partition and groups similar values together on disk.
Reduces data scanned by skipping blocks within a partition that do not contain matching values.
Limits: up to four clustering columns per table, no partition count limit.
Denormalisation
Stores related data in one table, duplicating values to avoid JOINs.
Increases storage size but reduces query computation and scan cost.
Best for query-heavy, append-only workloads where updates are rare.
Normalisation
Splits related data into separate tables with foreign keys, minimising duplication.
Reduces storage size but requires JOINs, increasing query cost and time.
Best for transactional, update-heavy workloads where data consistency is critical.
Time-Unit Partitioning
Partitions by DATE, TIMESTAMP, or DATETIME column with granularities HOUR, DAY, MONTH, YEAR.
Automatically creates partitions as new dates are inserted.
Limited to 4,000 partitions; suitable for event logs and time-series data.
Integer Range Partitioning
Partitions by an INTEGER column with a specified range start, end, and interval.
Requires explicit definition of the range; partitions are fixed at creation.
Up to 10,000 partitions; suitable for customer ID ranges or numeric categories.
Mistake
Partitioning and clustering are the same thing, just different names.
Correct
Partitioning splits a table into separate physical segments based on a column value. Clustering sorts the rows within each partition and groups similar values together on disk. They serve different purposes and work best together.
Both techniques aim to reduce data scanned, so beginners often conflate them. The exam distinguishes them clearly.
Mistake
You should always use as many clustering columns as possible (up to four) because more is better.
Correct
Clustering on too many columns can degrade performance because BigQuery has to maintain sorted order across multiple dimensions. Only cluster on columns that are frequently used in filters and have high cardinality.
There is a natural assumption that more indexing equals better performance, but clustering has diminishing returns and added maintenance overhead.
Mistake
Denormalisation is always better because it removes JOINs.
Correct
Denormalisation increases storage costs and can lead to data inconsistency if the source data changes. It is a trade-off: you sacrifice storage efficiency and update simplicity for query speed. Use it only for columns that are queried together frequently.
Beginners learn that JOINs are slow, so they over-correct by denormalising everything without considering the downsides.
Mistake
You can partition a table by any column, including strings and integers, as long as you use the right syntax.
Correct
Only DATE, TIMESTAMP, DATETIME, and INTEGER columns can be partition columns. STRING, BOOL, FLOAT, and ARRAY types are not allowed for partitioning. Integer range partitioning requires an integer column with a defined range and interval.
BigQuery's documentation lists specific types, but beginners assume any column can work. The exam tests this boundary.
Mistake
Clustering permanently sorts the data when you insert it, and later inserts will mess up the sorted order.
Correct
BigQuery automatically manages clustering. When new data is inserted, it is placed in the correct partition but not immediately re-sorted. BigQuery performs background re-clustering operations to maintain sorted blocks. You do not need to handle it manually.
A misunderstanding of how databases handle index maintenance leads beginners to believe clustering is fragile.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
You cannot change the partition column of an existing table. You must create a new table with the desired partition column and copy the data into it using a SELECT statement or an export/import operation.
Yes, clustering can be applied to tables without partitioning. The benefits are reduced because the entire table is a single partition, but BigQuery still sorts the data within the table and performs block-level pruning on the clustered columns.
BigQuery returns an error when you try to create the 4,001st partition. You must redesign your table, for example by switching to a coarser partition granularity (e.g., MONTH instead of DAY) or by using integer range partitioning with a wider interval.
No, denormalisation is a trade-off. It reduces query cost and time by eliminating JOINs, but it increases storage costs and makes updates more complex. Use it only for columns that are frequently queried together and rarely updated.
No, clustering is free. You pay only for the storage of the data and for the bytes scanned during queries. Clustering reduces the bytes scanned, so it can actually lower your costs.
No, a BigQuery table can have only one partition column. You must choose between integer range partitioning and time-unit partitioning based on the nature of your data and query patterns.
You've finished Schema Design for BigQuery: Partitioning, Clustering, and Optimized Storage. Continue through the PCDE study guide to build a complete picture of the exam.
Done with this chapter?