.webp)
.svg)
Program overview
Online
On-site
Hybrid
Data Modelling for Analytics: Star Schema and Data Vault
Design warehouse models that stay correct as the business changes. Three days covering dimensional modelling in depth, fact grain, slowly changing dimensions, bridge structures, and Data Vault 2.0 hubs, links and satellites. Participants build a dimensional mart and a raw vault against the same source system, then compare the two, so the choice between approaches is made on evidence rather than preference.
Duration:
3 Days
Level:
Intermediate
.avif)
1500+ users onboarded
Who will Benefit from this Training?
Training Objectives
Build a high-performing, job-ready tech team.
Personalise your team’s upskilling roadmap and design a befitting, hands-on training program with Uptut
Key training modules
Comprehensive, hands-on modules designed to take you from basics to advanced concepts
Download Curriculum
- Why Analytical Models Are Different
- Transactional versus analytical workloads and their opposing design pressures
- Why third normal form makes a poor reporting layer
- Query patterns, read amplification and column-oriented storage
- Layered warehouse architecture: staging, integration and delivery
- Hands-on: critique an existing reporting schema and identify why it is slow to change
- Dimensional Modelling Foundations
- Facts and dimensions and the questions each answers
- The four-step dimensional design process
- Building a bus matrix across business processes
- Conformed dimensions and enterprise consistency
- Hands-on: build a bus matrix for a multi-process business domain
- Fact Tables and Choosing Grain
- Declaring grain as one sentence, and testing every column against it
- Transaction, periodic snapshot and accumulating snapshot fact tables
- Factless fact tables for events and coverage
- Additive, semi-additive and non-additive measures
- Degenerate dimensions and handling transaction identifiers
- Hands-on: design three fact tables at different grains from one source process
- Designing Dimensions
- Dimension attributes as the source of report labels and filters
- Hierarchies, drill paths and flattening versus snowflaking
- Role-playing dimensions and multiple date relationships
- Junk dimensions for low-cardinality flags and indicators
- Date and time dimensions built properly, including fiscal calendars
- Hands-on: build a full date dimension and a role-playing implementation
- Slowly Changing Dimensions
- Types zero through three and their reporting consequences
- Type two implementation with effective dates, current flags and surrogate keys
- Types four, five and six for mixed history requirements
- Late arriving dimensions and late arriving facts
- Choosing a type per attribute rather than per table
- Hands-on: implement type two history and prove point-in-time reporting is correct
- Bridges, Hierarchies and Difficult Relationships
- Many-to-many relationships between facts and dimensions
- Bridge tables and allocation weighting factors
- Ragged and variable depth hierarchies
- Multi-valued attributes and group dimensions
- Handling nulls, unknowns and not-applicable members
- Hands-on: model a customer to account many-to-many with correct allocation
- Data Vault 2.0 Foundations
- The problem Data Vault solves: auditability, agility and source integration
- Hubs and the discipline of identifying true business keys
- Links for relationships, including same-as and hierarchical links
- Satellites for descriptive attributes and change tracking
- Hash keys, hash diffs and their role in parallel loading
- Hands-on: identify business keys and design the hub and link layer for your domain
- Building and Loading the Raw Vault
- Insert-only loading and why nothing is ever updated
- Load date, record source and required metadata columns
- Parallel and restartable load patterns across hubs, links and satellites
- Multi-source satellites and integrating overlapping systems
- Point-in-time and bridge tables for query performance
- Hands-on: load a raw vault from two source systems and query it as of a past date
- Business Vault and Information Marts
- Separating raw facts from business rules and derived logic
- Computed satellites, derived links and business vault structures
- Delivering star schemas as information marts from the vault
- Virtualising marts as views versus materialising them
- Managing the cost and complexity the extra layer introduces
- Hands-on: deliver a dimensional mart from the vault you built
- Choosing an Approach
- Kimball, Inmon, Data Vault and one big table compared on real criteria
- Medallion and lakehouse layering and how it maps to these approaches
- Team size, source volatility, audit requirements and skill availability as deciding factors
- Hybrid architectures and where they succeed or fail
- Semantic layers and metric definitions above the physical model
- Hands-on: write and defend an architecture decision record for your own organisation
- Physical Design, Testing and Delivery
- Clustering, partitioning, distribution and sort keys on cloud warehouses
- Incremental models, merge patterns and idempotent reloads
- Surrogate key generation strategies at scale
- Testing data models: uniqueness, referential, freshness and grain tests
- Documentation, lineage and change management for the model
- Cost and performance tuning for analytical workloads
- Hands-on: capstone presentation of your model, tests and physical design choices
Hands-on Experience with Tools
Training Delivery Format
Flexible, comprehensive training designed to fit your schedule and learning preferences
Opt-in Certifications
AWS, Scrum.org, DASA & more
.avif)
100% Live
on-site/online training
.avif)
Hands-on
Labs and capstone projects
.avif)
Lifetime Access
to training material and sessions
Skill-Gap Assessment
Analysing skill gap and assessing business requirements to craft a unique program
1
Personalisation
Customising curriculum and projects to prepare your team for challenges within your industry
2
Implementation
Supplementing training with consulting support to ensure implementation in real projects
3
Why this course
- Both approaches, honestly compared: Teams build the same domain as a star schema and as a Data Vault, then evaluate the trade-offs themselves.
- Grain discipline: Most broken warehouses trace back to an undefined fact grain. This course makes that decision explicit and testable.
- History that holds up to audit: Slowly changing dimensions and vault satellites are taught to the standard finance and regulatory reporting demands.
- Built for modern platforms: Physical design covers Snowflake, BigQuery, Databricks and Redshift alongside traditional warehouses.
Training objectives
- Explain why analytical models differ structurally from transactional ones
- Define and defend the grain of a fact table before designing it
- Design conformed dimensions and a bus matrix across multiple business processes
- Select and implement the right slowly changing dimension type per attribute
- Model many-to-many relationships using bridge and factless fact structures
- Build hubs, links and satellites following Data Vault 2.0 standards
- Load a raw vault using insert-only, parallel-safe patterns
- Deliver dimensional information marts from a business vault
- Choose between Kimball, Inmon, Data Vault and wide-table approaches for a given context
- Apply physical design and testing practices on a modern cloud warehouse
Who will benefit
- Data engineers and analytics engineers
- BI developers and warehouse designers
- Data architects and platform leads
- Senior analysts owning semantic and reporting layers
- Teams migrating a legacy warehouse to a cloud platform
.avif)
Lead the Digital Landscape with Cutting-Edge Tech and In-House " Techsperts "
Discover the power of digital transformation with train-to-deliver programs from Uptut's experts. Backed by 70,000+ professionals across the world's leading tech innovators.
.svg)
.svg)
.svg)
.svg)
.svg)
Recommended Training
Continue your learning journey with these hand-picked courses and bundles
Frequently Asked Questions
2. Will my team get any practical experience with this training?
.svg)
With our focus on experiential learning, we have made the training as hands-on as possible with assignments, quizzes and capstone projects, and a lab where trainees will learn by doing tasks live.
3. What is your mode of delivery - online or on-site?
We conduct both online and on-site training sessions. You can choose any according to the convenience of your team.
4. Will trainees get certified?
Yes, all trainees will get certificates issued by Uptut under the guidance of industry experts.
5. What do we do if we need further support after the training?
We have an incredible team of mentors that are available for consultations in case your team needs further assistance. Our experienced team of mentors is ready to guide your team and resolve their queries to utilize the training in the best possible way. Just book a consultation to get support.