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
Nested Data Structures
Was this section helpful?
- Nested and repeated columns (ARRAYs and STRUCTs), Google Cloud, 2024 (Google Cloud) - Official guide on using arrays and structs in BigQuery, including querying and best practices for performance optimization.
- Semi-structured Data: Introduction, Snowflake Inc., 2018 (Snowflake Inc.) - Official documentation covering semi-structured data types like ARRAY and OBJECT, and the LATERAL FLATTEN function in Snowflake.
- Database System Concepts, Abraham Silberschatz, Henry F. Korth, S. Sudarshan, 2019 (McGraw-Hill Education) - A widely respected textbook providing a comprehensive academic foundation on database normalization, including First Normal Form (1NF), essential for understanding the traditional context.
- Apache Parquet, Apache Software Foundation, 2024 - The official documentation for Apache Parquet, a widely used columnar storage format that supports complex nested data structures and is foundational to many modern data warehouses.
© 2025 ApX Machine LearningEngineered with