INFO20003 Chap.11 Data Warehousing, Distribution and NoSQL
Data Warehousing, Distribution and NoSQL
The final architecture chapter contrasts current operational processing with historical analytical workloads. Star schemas place measurable events at a declared fact-table grain and surround them with descriptive dimensions. Distributed database design then introduces fragmentation, placement and replication, while CAP clarifies the choice between immediate consistency and universal availability during a partition.
NoSQL families are evaluated by access pattern and data shape rather than treated as one replacement for relations. Operational systems support short current-state transactions with strong integrity and frequent updates. Analytical systems integrate history for scanning, grouping and comparison, often through an extraction, transformation and loading pipeline that introduces a known freshness delay.
Dimensional design begins with a business process and a precise fact-table grain. Measures must have one value at that grain, while dimensions supply descriptive axes such as customer, product, place and date. Additive measures can be summed across every relevant dimension; semi-additive snapshots cannot be summed safely across time; ratios and percentages are normally recomputed from their underlying components.
Degenerate identifiers and slowly changing dimensions preserve useful transaction or historical context when their policy is stated. Distribution adds placement decisions. Horizontal fragmentation divides rows, vertical fragmentation divides attributes while retaining a reconstruction key, and replication keeps copies at several sites. Each can improve locality or resilience but creates coordination, routing and recovery work.
Synchronous replicas can strengthen visibility guarantees at latency and availability cost; asynchronous replicas can serve stale values and risk losing recent acknowledged work under some failure models. CAP concerns the forced choice during a network partition: reject or delay some requests to preserve one consistent value, or continue serving and reconcile divergent state later.
It does not mean a system permanently ignores a third property in failure-free operation. Data-model selection follows access patterns and transaction boundaries. Key-value stores excel at direct key access, document stores keep related aggregates together, wide-column systems support large sparse keyed datasets, and graph databases optimise relationship traversal.
Relational systems remain strong where joins, declarative constraints and multi-record transactions dominate. Polyglot persistence can combine strengths, but duplicated facts, event ordering, schema evolution and cross-system observability become application responsibilities.
A defensible architecture answer therefore declares grain or aggregate boundaries, expected reads and writes, consistency visible to clients, partition and recovery behaviour, and the operational cost introduced by each claimed benefit.
What this chapter covers
- 01
Operational workloads
- 02
Warehouse architecture
- 03
Fact table grain
- 04
Dimensions and measures
- 05
Fragmentation
- 06
Replication
- 07
CAP trade-offs
- 08
NoSQL families
Worked example · free
Design a ride analysis star
- 1Declare one fact row as one completed ride.
- 1Create date, station, bicycle type and member category dimensions.
- 1Use start and end station as role-playing keys to one station design.
- 1Store ride_count equal to one and duration_minutes as measures.
- 1Keep current bicycle location outside the completed-ride grain.
Key terms
- Fact table
- A dimensional table recording measurable events or snapshots at a declared grain.
- Dimension
- A descriptive table used to filter, group and label facts.
- Additive measure
- A numerical fact that can be summed safely across the relevant dimensions.
- Distributed database
- One logical database whose data are physically placed across connected locations.
- Replication
- The storage of copies of data at more than one site.
- CAP trade-off
- The partition-time choice between immediate consistency and serving every request.
Data Warehousing, Distribution and NoSQL FAQ
What must accompany a polyglot persistence recommendation?
Name the access pattern or consistency boundary each store improves, then account for duplicated facts, event ordering, schema evolution, monitoring and recovery across systems. Using several data models is justified only when the workload benefit exceeds the coordination and operational cost transferred into applications and platform processes.
Why must a fact-table grain be declared first?
The grain states what one row means and therefore which dimensions and measures are valid. Mixing transaction lines, daily summaries or current snapshots makes aggregation ambiguous and can double-count results.
Does CAP mean a system permanently chooses only two properties?
No. The forced choice arises during a network partition. A partition-tolerant design then preserves consistency by rejecting or delaying some work, or favours availability by answering from reachable replicas with possible divergence.
When is a NoSQL model appropriate?
Choose it when the access pattern and data shape benefit from key-value, document, column-family or graph structures and the required consistency model is acceptable. Large data volume alone does not invalidate the relational model.
Exam move
Design analytical schemas by writing the business process and grain before drawing a star. Classify each measure as additive, semi-additive or non-additive and test a sum across time. For distributed systems, describe the exact partition scenario and state what a client observes under a consistency-first or availability-first response.
Build a comparison grid for relational, key-value, document, column-family and graph models using query pattern, integrity, transaction scope and duplication. Finish architecture answers with the cost introduced by the chosen benefit, such as ETL latency, replica coordination or application-managed consistency.
Working through Data Warehousing, Distribution and NoSQL in INFO20003? Sia is AskSia’s AI Computer Science tutor — ask any INFO20003 Data Warehousing, Distribution and NoSQL question and get a clear, step-by-step explanation grounded in how INFO20003 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.