Study guide
12.4 Data Warehouse Modeling: Normalization, Star Schemas, and Slowly Changing Dimensions
Google Cloud Certified Professional Data Engineer Exam
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Security, IAM, and Data Protection
1.1 Cloud IAM, Service Accounts, and Resource Hierarchy in GCP Data Systems 1.2 Data Encryption, Key Management (CMEK), and Cloud KMS 1.3 Sensitive Data Protection: Cloud DLP, De-identification, and Masking
2 Chapter 2: Reliability and Compliance Architecture
2.1 High Availability, Regional vs Multi-Regional Deployments, and Disaster Recovery 2.2 Data Residency, Sovereignty, and Regulatory Compliance (HIPAA, GDPR) 2.3 Modern Data Architecture Patterns: Data Mesh, Lakehouse, and Modern Data Stack
3 Chapter 3: Cross-Cloud and Federated Governance
3.1 BigLake Architecture: Unified Multicloud Storage and Fine-Grained Access Control 3.2 BigQuery Omni: Multicloud Analytics Across AWS S3, Azure ADLS, and Google Cloud 3.3 Analytics Hub and Federated Computational Governance
4 Chapter 4: Data Ingestion and Pipeline Planning
4.1 Data Pipeline Planning: Sources, Sinks, and Network Architecture (VPC, PSC, Interconnect) 4.2 Batch vs Streaming Processing Trade-Offs and Architecture Selection 4.3 Change Data Capture (CDC) with Datastream for Continuous Database Ingestion
5 Chapter 5: Streaming Ingestion with Cloud Pub/Sub
5.1 Cloud Pub/Sub Core Architecture: Topics, Subscriptions, and Message Lifecycle 5.2 Pub/Sub Message Ordering, Exactly-Once Delivery, and Deduplication 5.3 Pub/Sub BigQuery/Cloud Storage Direct Subscriptions and Dead-Letter Topics
6 Chapter 6: Pipeline Processing with Apache Beam and Cloud Dataflow
6.1 Apache Beam Programming Model: Pipelines, PCollections, and Core PTransforms 6.2 Cloud Dataflow Architecture: Autoscaling, Worker Allocation, and Dataflow Prime 6.3 Windowing Strategies in Streaming Dataflow: Fixed, Sliding, and Session Windows
7 Chapter 7: Advanced Dataflow: Robustness and Exactly-Once Semantics
7.1 Watermarks, Triggers, Accumulating vs Discarding Panes, and Late Data Handling 7.2 Side Inputs, Broadcast Joins, and Stateful Stream Processing 7.3 Pipeline Robustness: Dead-Letter Queues, Error Handling, and Pipeline Lifecycle Management
8 Chapter 8: Managed Data Processing: Cloud Dataproc and Cloud Data Fusion
8.1 Cloud Dataproc: Architecture, Ephemeral vs Persistent Clusters, and Spark Tuning 8.2 Dataproc Serverless and Autoscaling Policies for Batch Processing 8.3 Cloud Data Fusion: Visual ETL/ELT Pipelines, CDAP Plugins, and Data Lineage
9 Chapter 9: Storage Selection and Cloud Storage Architecture
9.1 GCP Storage Selection Decision Matrix: Relational, NoSQL, Object, and Analytical Stores 9.2 Cloud Storage Architecture: Storage Classes, Lifecycle Rules, and Object Versioning 9.3 Cloud Storage Security: Bucket Lock (WORM), Access Control, and Data Migration Tools
10 Chapter 10: Relational Cloud Databases: Cloud Spanner and Cloud SQL
10.1 Cloud Spanner Architecture: TrueTime, External Consistency, and Global Scalability 10.2 Cloud Spanner Schema Design: Primary Keys, Interleaved Tables, and Query Tuning 10.3 Cloud SQL: High Availability, Read Replicas, Maintenance, and Point-in-Time Recovery
11 Chapter 11: Scalable NoSQL Databases: Cloud Bigtable and Firestore
11.1 Cloud Bigtable Architecture: Storage Model, Tablet Splitting, and Cluster Management 11.2 Cloud Bigtable Row Key Design: Lexicographical Ordering, Salt Keys, and Anti-Patterns 11.3 Cloud Firestore: Document Model, Real-Time Sync, and ACID Collections
12 Chapter 12: Enterprise Data Warehousing with BigQuery
12.1 BigQuery Architecture: Capacitor Columnar Format, Jupiter Network, and Borg Slots 12.2 BigQuery Partitioning: Ingestion-Time, Date/Timestamp, and Integer Range Strategies 12.3 BigQuery Clustering: Cardinality, Multi-Column Ordering, and Performance Optimization 12.4 Data Warehouse Modeling: Normalization, Star Schemas, and Slowly Changing Dimensions
13 Chapter 13: Advanced Analytical Queries and Transformations in BigQuery
13.1 Advanced SQL in BigQuery: Window Analytic Functions, ARRAY/STRUCT, and PIVOT/UNPIVOT 13.2 Modern SQL Orchestration with Dataform: DAG Modeling, Testing, and Documentation 13.3 BI Engine In-Memory Acceleration and Enterprise Looker/LookML Semantic Modeling
14 Chapter 14: Machine Learning and Feature Engineering with BigQuery ML
14.1 BigQuery ML Architecture: In-Database Training, Model Topologies, and SQL Analytics 14.2 Feature Engineering: ML.TRANSFORM, Preprocessing Pipelines, and Vertex AI Feature Store 14.3 MLOps, Model Governance, Continuous Monitoring, and Deployment Lifecycles
15 Chapter 15: Operations, Orchestration, and Data Governance
15.1 Workflow Orchestration with Cloud Composer: Managed Airflow, DAG Design, and Dependencies 15.2 Unified Data Governance with Dataplex: Lakes, Zones, Data Quality, and Lineage 15.3 Pipeline Observability, FinOps, and Cost Optimization Across GCP Data Services
12.4 Data Warehouse Modeling: Normalization, Star Schemas, and Slowly Changing Dimensions
Key Takeaways Explain This Topic AI
Declare the fact table grain as a sentence before modeling anything else; an undeclared grain is what produces the join fan-out that silently multiplies header-level measures by the number of child rows.
BigQuery inverts row-store normalization economics: keep small independently-changing dimensions as star-schema tables joined on surrogate keys, and model intrinsic one-to-many relationships as ARRAY<STRUCT<...>> so the join disappears entirely.
A Type 2 slowly changing dimension requires the fact table to store the surrogate key of the dimension version in effect at event time; storing the natural key silently degrades the design back to Type 1.
BigQuery PRIMARY KEY and FOREIGN KEY constraints are NOT ENFORCED declarative hints for the optimizer, so uniqueness must be guaranteed by a Dataform assertion or QUALIFY ROW_NUMBER() deduplication in the pipeline.
Time travel and fail-safe are disaster-recovery windows of at most seven days, not a substitute for Type 2 history; they cannot answer point-in-time questions about attribute values from prior years.
Last updated: September 2026
12.4 Data Warehouse Modeling: Requirements, Normalization, Star Schemas, and Slowly Changing Dimensions
Exam Focus: Blueprint topic 3.2, "Planning for using a data warehouse," is tested as design judgment , not syntax. You must be able to translate a stated business requirement into a data model, decide how far to normalize for a columnar engine, choose between a star schema and BigQuery's nested and repeated fields, and handle dimension attributes that change over time without corrupting historical reporting.
Partitioning and clustering (sections 12.2 and 12.3) make a table fast to scan . Modeling decides what the table is . A badly modeled warehouse cannot be rescued by tuning: if the grain is wrong, every downstream number is wrong, and if history is overwritten, no query can reconstruct it.
1. From Business Requirements to a Model
Modeling starts with requirements, not tables. Four questions produce the model:
What business process are we measuring? Each process — order placement, shipment, claim adjudication, meter reading — becomes one fact table.
What is the grain? State it as a sentence: "one row per order line per shipment." Declaring the grain first is what prevents the fan-out errors that silently double revenue when a one-to-many join is added later.
What do users filter and group by? Those become dimensions: date, customer, product, store, channel.
What do users measure? Those become facts: quantity, amount, cost, duration. Additive measures (revenue) behave differently from non-additive ones (a margin percentage, an account balance) — store the additive components and derive ratios at query time or in a rollup, never store a pre-divided ratio you cannot re-aggregate.
- Fact table type Grain Example Aggregation caution
- Transaction One row per event Individual order line Fully additive across all dimensions
Periodic snapshot One row per entity per period Daily account balance Semi-additive — never sum balances across time
Accumulating snapshot One row per process instance, updated as it progresses Order lifecycle with order/pick/ship/deliver timestamps Rows are updated in place, so plan for MERGE
2. Deciding the Degree of Normalization
Normalization theory was built for row-stores where a repeated string wastes disk on every row and joins are cheap index lookups. BigQuery inverts both assumptions: columnar storage compresses repeated values extremely well, and large joins require a distributed shuffle. That shifts the correct answer toward denormalization — but not to zero normalization.
Model Shape Strength on BigQuery Weakness
Third normal form (3NF) Many narrow, highly-related tables Faithful to source systems; minimal update anomalies Every query pays multiple shuffle joins; poor for analytics
Star schema One central fact table, denormalized dimension tables joined on surrogate keys Broadcast joins against small dimensions are cheap; familiar to BI tools; dimensions update independently Still a join; very large dimensions can force a shuffle
Nested / repeated (STRUCT and ARRAY) Child rows stored inside the parent row Join eliminated entirely; Capacitor prunes unreferenced nested columns; preserves one-to-many without fan-out Updating a child requires rewriting the parent row; harder for BI tools that expect flat tables
Fully flat wide table One denormalized row per event with every attribute repeated Fastest single-table scans, no joins at all Dimension changes require rewriting the fact table; storage grows; attribute history is lost
The decision rule the exam rewards:
Dimensions that are small and change independently (a few million rows of customer or product attributes) stay as dimension tables in a star schema . BigQuery broadcasts a small dimension to every slot, so the join is nearly free, and a changed attribute is one small update rather than a petabyte rewrite.
Relationships that are intrinsically one-to-many and always queried with the parent (order line items, event parameters, page hits in a session) become nested and repeated fields . There is no join and no fan-out risk.
Attributes that are tiny, stable, and always needed (country code, channel) can simply be denormalized onto the fact table.
Do not flatten a large, frequently-updated dimension into a petabyte fact table. A single corrected customer address would trigger a full-table rewrite. -- Nested and repeated: one row per order, line items inside it. No join, no fan-out.
- CREATE TABLE `sales.orders` (
- order_id STRING NOT NULL,
- order_ts TIMESTAMP,
- customer_key INT64, -- surrogate key into the customer dimension
- line_items ARRAY<STRUCT<
- sku STRING,
- quantity INT64,
- unit_price NUMERIC,
- discount_pct NUMERIC>>
- )
- PARTITION BY DATE(order_ts)
- CLUSTER BY customer_key;
- - Correct aggregation: UNNEST expands children only for the referenced columns
- SELECT o.order_id, SUM(li.quantity * li.unit_price) AS order_total
- FROM `sales.orders` o, UNNEST(o.line_items) AS li
- GROUP BY o.order_id;
The fan-out trap, restated: if you model line items as a separate table and join it to an order-header table, SUM(order_header_amount) multiplies the header amount by the number of line items. With ARRAY<STRUCT<...>> the header columns exist once per order and cannot be double-counted. This is the same arithmetic failure that Looker's symmetric aggregates exist to work around (section 13.3) — nesting avoids it at the model layer instead of patching it at the BI layer.
3. Surrogate Keys and Dimension Design
Dimension tables should carry a surrogate key — a warehouse-generated integer or GENERATE_UUID() string — rather than reusing the source system's natural key.
Natural keys get reused, reformatted, or collide after a merger or acquisition; surrogate keys are stable forever.
A surrogate key is what makes Type 2 history (below) possible at all: the same customer can occupy several dimension rows, each with a different surrogate key, each valid for a different time span.
Keep the natural/source key as an attribute so lineage back to the operational system remains queryable.
BigQuery does not enforce primary or foreign keys. PRIMARY KEY (...) NOT ENFORCED and FOREIGN KEY (...) NOT ENFORCED constraints are declarative only: they inform the query optimizer (enabling join eliminations) but never reject a duplicate. Uniqueness must be enforced by the pipeline — a Dataform uniqueKey assertion, or QUALIFY ROW_NUMBER() OVER (PARTITION BY ...) = 1 during the load.
4. Slowly Changing Dimensions (SCD)
A customer moves from the Bronze to the Gold loyalty tier. What should last quarter's report show? The answer determines the SCD type, and exam scenarios always contain the deciding phrase.
- Type Behaviour The phrase that signals it
- Type 0 Attribute never changes after insert "original signup channel," "date of birth"
- Type 1 Overwrite in place; history is lost "correct the typo," "we only care about the current value"
Type 2 Insert a new row with new validity dates; prior row closed "reporting must reflect the tier as it was at the time of the sale "
Type 3 Add a previous_value column "we need the current and immediately prior territory, nothing older"
Type 4 Current row in the main dimension, history in a separate table "current lookups must stay fast but audit needs full history"
Type 2 is the one worth implementing correctly: -- Type 2 dimension: one row per customer per attribute-version
- CREATE TABLE `dw.dim_customer` (
- customer_key INT64 NOT NULL, -- surrogate key, unique per version
- customer_id STRING NOT NULL, -- natural key, repeats across versions
- loyalty_tier STRING,
- region STRING,
- valid_from TIMESTAMP NOT NULL,
- valid_to TIMESTAMP, -- NULL while current
- is_current BOOL NOT NULL
- )
- CLUSTER BY customer_id, is_current;
- - Close the outgoing version and open a new one
- MERGE `dw.dim_customer` AS d
- USING `staging.customer_changes` AS s
- ON d.customer_id = s.customer_id AND d.is_current
- WHEN MATCHED AND d.loyalty_tier != s.loyalty_tier THEN
- UPDATE SET valid_to = s.changed_at, is_current = FALSE
- WHEN NOT MATCHED THEN
- INSERT (customer_key, customer_id, loyalty_tier, region, valid_from, valid_to, is_current)
- VALUES (FARM_FINGERPRINT(CONCAT(s.customer_id, CAST(s.changed_at AS STRING))),
- s.customer_id, s.loyalty_tier, s.region, s.changed_at, NULL, TRUE);
Two rules follow, and both appear as distractors:
The fact table stores customer_key , not customer_id . Storing the natural key silently converts a Type 2 dimension back into Type 1, because every historical fact would join to whichever row happens to be current.
Current-state queries filter WHERE is_current ; point-in-time queries join on the validity window. Omitting either predicate multiplies every fact row by the number of dimension versions — a fan-out disguised as a correct join. -- Point-in-time: the tier as it stood when the order was placed
- SELECT f.order_id, d.loyalty_tier, f.revenue
- FROM `dw.fact_orders` f
- JOIN `dw.dim_customer` d
- ON f.customer_key = d.customer_key
- AND f.order_ts >= d.valid_from
- AND (d.valid_to IS NULL OR f.order_ts < d.valid_to);
A note on BigQuery's time travel: time travel (default 7 days, configurable 2-7) and fail-safe are disaster-recovery features for recovering a table you damaged. They are not a substitute for Type 2 history — they expire, they are table-wide rather than row-scoped, and they cannot answer "what tier was this customer in three years ago." Choosing time travel to satisfy a historical-reporting requirement is a reliable wrong answer.
5. Architecture to Support the Access Patterns
The final blueprint bullet — "defining architecture to support data access patterns" — is the layered warehouse:
Layer Contents Modeling posture
Raw / landing Source-faithful, append-only, partitioned by ingestion date No modeling; preserve exactly what arrived so it can be replayed
Staging / cleansed Typed, deduplicated, conformed units and time zones Light normalization; assertions run here
Curated / presentation Star schemas, Type 2 dimensions, nested facts Full modeling; this is what analysts and Looker query
Rollups / marts Pre-aggregated tables and materialized views per consumer Precalculated for dashboard latency (section 13.3)
Dataset placement carries the governance: separate datasets per layer, IAM granted per dataset, policy tags on sensitive columns, and — because a BigQuery join cannot cross regions — every dataset in the layer stack in the same location . A requirement for EU-resident data means the whole stack is EU, not just the curated layer.
Exam Trap: "Query the raw landing tables directly to avoid duplicating storage." BigQuery storage is inexpensive relative to repeated compute, and raw data has no conformed grain, no deduplication and no history. The curated layer exists precisely so that every consumer gets the same, correct numbers.