StudyCerts
StudyCertsProfessional Cloud Data Engineer
professional-cloud-data-engineer
Advanced
Gauge your current knowledge
Advanced
Gauge your current knowledge
Deciding the degree of data normalization
...
professional-cloud-data-engineer
Gauge your current knowledge
Gauge your current knowledge
The choice between normalized and denormalized data depends heavily on whether your system handles transactions (OLTP) or analytics (OLAP). In Google Cloud, Cloud Spanner is built for OLTP workloads, like processing sales or updating inventory, where data must be consistent and accurate for every write. Its architecture supports normalized schemas, where data is split into separate, well-organized tables to prevent errors and duplication. This works well for quick, single-record lookups and operations. On the other hand, BigQuery is designed for OLAP, handling massive analytical queries that read huge amounts of data to find trends. Here, denormalization—combining data into fewer, wider tables—is often better because it speeds up these large-scale reads by avoiding slow join operations between many separate tables.
Two common data warehouse designs illustrate this trade-off. A star schema uses a central fact table (like sales numbers) linked to simpler dimension tables (like customer or product info) and is a form of denormalization. A snowflake schema normalizes those dimensions further, breaking them into more tables. While BigQuery can work with both, its internal storage is optimized for a different approach: using nested and repeated fields (like arrays of structs) to keep related data together in one table without traditional joins. Your decision should be based on your workload's pattern: choose normalization for systems with many writes and a need for strict data integrity (like Spanner), and choose denormalization for systems dominated by complex read queries (like BigQuery).
Your schema design directly impacts performance, storage use, and cost in Google Cloud. In a columnar data warehouse like BigQuery, denormalized, flat tables are generally more efficient. They reduce the need for joins, which are computationally expensive operations. This leads to faster query completion (lower latency) and uses fewer compute resources. Compute power in BigQuery is measured in slots, and inefficient schemas that require many joins will consume more slots, increasing your bill. Therefore, transforming a traditional star or snowflake schema into a single, wide table can save both time and money.
Storage costs are also a factor. While normalized schemas in traditional databases aim to reduce redundancy, BigQuery's columnar storage can make denormalized tables surprisingly space-efficient. More importantly, the performance gains from avoiding joins often outweigh any extra storage used. To manage costs proactively, use tools like the Google Cloud Pricing Calculator for estimates and Cloud Monitoring to track actual slot usage. Active Assist can also provide optimization recommendations. Schema design is an iterative process; test changes with subsets of data, use clustering to organize data within tables, and refine your design based on real query patterns to control costs and ensure predictable performance.
BigQuery offers a powerful middle-ground between full normalization and full denormalization: semi-normalized designs using nested and repeated fields. This is done with the STRUCT and ARRAY data types. Instead of having a separate ORDERS table linked to a CUSTOMERS table, you can store an array of order structs directly inside each customer record. This preserves the logical relationship between customers and their orders but keeps all the data physically together in one table.
This design is highly efficient because it aligns with BigQuery's internal Dremel processing model and Capacitor storage format. Since related data is colocated, queries can read it quickly without performing slow, resource-intensive join operations across different tables. This leads to better performance and lower computational costs. This approach is most beneficial when your analytical queries frequently need to filter or aggregate data based on these nested relationships, like summing all orders for a particular customer. For access patterns that only need the nested data independently, a traditional separate table might still be suitable. You can further optimize performance by using BigQuery's clustering feature on these nested tables to organize the data within partitions.