Advanced Data Warehousing
Program Information
- 🎓 Delivery Method:
- On-site (available now, on demand)
- Remote (live, on demand)
- Online (self-paced, coming soon)
- ⏳ Duration:
- 3 days, 4 hours per day
- Custom options available for internal teams
- 🧩 Format:
- Interactive workshops
- Hands-on exercises
- 👨🎓 Maximum Participants:
- 12 per session (to ensure personalized interaction)
- 📋 Course Requirements:
- No prior technical background required.
- 📅 Scheduling:
- Flexible scheduling available on demand
- 📞 Contact to Schedule:
- 📧 training@vizacta.com
Course Description
This advanced data warehousing course is designed for data professionals who want to master dimensional modeling using the star schema approach. The course dives deep into fact and dimension table design, slowly changing dimensions, bridge tables, and advanced modeling patterns used in enterprise data warehouses. Learners will explore performance optimization, ETL strategies, and real-world modeling challenges across various business domains.
What you will learn
- Design and implement robust star schema models for analytics
- Differentiate and apply various types of fact and dimension tables
- Handle slowly changing dimensions (SCD Types 1, 2, 3, and hybrid)
- Model complex relationships using bridge tables and mini-dimensions
- Integrate conformed dimensions across multiple subject areas
- Build ETL pipelines that support scalable dimensional models
- Optimize star schemas for performance and BI tool compatibility
- Apply dimensional modeling techniques in a capstone project
Course Content
- Star schema architecture: facts, dimensions, keys
- Dimensional modeling goals: understandability, performance, extensibility
- Granularity and atomic data
- Case study: retail sales dimensional model
- Types of fact tables: transactional, snapshot, accumulating snapshot
- Additive, semi-additive, and non-additive facts
- Factless fact tables for event tracking and coverage
- Degenerate dimensions and grain declaration
- Role-playing, junk, and conformed dimensions
- Hierarchical structures and attribute roll-ups
- Surrogate vs. natural keys
- Attribute classification and change handling strategies
- SCD Types 1, 2, and 3 – definitions and use cases
- Hybrid strategies like Type 6 (combine 1+2+3)
- ETL implementation patterns for managing SCDs
- Performance implications of SCD design choices
- Bridge tables for many-to-many relationships
- Multi-valued dimensions and hierarchical bridges
- Mini-dimensions and outriggers
- Snowflaking: when it’s appropriate and when to avoid it
- Conformed facts and dimensions across data marts
- Value chain modeling and drill-across queries
- Surrogate key management strategies
- Design patterns for extensibility and change tracking
- ETL stages: staging, transformation, and loading
- Handling late-arriving facts and dimensions
- Surrogate key generation and maintenance
- Audit, balance, and control (ABC) techniques in ETL
- Aggregate and summary fact tables for faster querying
- BI tool-specific schema design (Power BI, Tableau, Looker)
- Partitioning, indexing, and columnar storage considerations
- Common star schema design pitfalls and anti-patterns