Data Modeling and Schema Design for Analytics
Chapter 1: Foundations of Analytical Architecture
Operational vs Analytical Workloads
Row-Oriented vs Columnar Storage
The Role of Denormalization
Hands-on Practical: Analyzing Query Plans
Chapter 2: Dimensional Modeling Core
The Grain of Data
Fact Tables Fundamentals
Dimension Tables Structure
Star Schema Architecture
Snowflake Schema Variations
Hands-on Practical: Designing a Star Schema
Chapter 3: Advanced Dimension Handling
Surrogate Keys vs Natural Keys
Slowly Changing Dimensions Type 1
Slowly Changing Dimensions Type 2
Date and Time Dimensions
Role-Playing Dimensions
Hands-on Practical: Implementing SCD Type 2
Chapter 4: Complex Fact Table Patterns
Transactional Fact Tables
Periodic Snapshot Fact Tables
Accumulating Snapshot Fact Tables
Factless Fact Tables
Hands-on Practical: Building an Accumulating Snapshot
Chapter 5: Physical Design and Optimization
Partitioning Strategies
Clustering and Sorting Keys
Nested Data Structures
One Big Table OBT Methodology
Hands-on Practical: Optimization Tuning
Row-Oriented vs Columnar Storage
Was this section helpful?
- C-Store: A Column-Oriented DBMS, Michael Stonebraker, Daniel J. Abadi, Adam Batkin, Xuedong Chen, Mitch Cherniack, Miguel Ferreira, Edmond Lau, Amerson Lin, Samuel Madden, Elizabeth J. O'Neil, Patrick E. O'Neil, Alex Rasin, Nga Tran, Stanley B. Zdonik, 2005 Proceedings of the 31st International Conference on Very Large Data Bases (VLDB) (VLDB Endowment) DOI: 10.14778/1070617.1070670 - This seminal paper introduced the C-Store architecture, detailing the advantages of column-oriented storage for analytical workloads and serving as a blueprint for many modern analytical databases.
- Designing Data-Intensive Applications: The Big Ideas Behind Reliable, Scalable, and Maintainable Systems, Martin Kleppmann, 2017 (O'Reilly Media, Inc.) - A highly regarded book that explains the fundamental trade-offs in database storage and retrieval, including a thorough comparison of row-oriented and columnar layouts, compression, and their implications for OLTP and OLAP systems.
- Database System Concepts, Avi Silberschatz, Henry F. Korth, S. Sudarshan, 2019 (McGraw-Hill) - A comprehensive textbook covering foundational database concepts, including physical storage structures, file organization, indexing, and I/O efficiency, providing the theoretical context for understanding row-oriented and columnar storage. (7th Edition)
© 2025 ApX Machine LearningEngineered with