A self-paced practical course for analysts, developers and data engineers building or modernising a data warehouse for the first time. Seven lessons cover gathering requirements, choosing an architecture, dimensional modelling with facts and dimensions, ingesting data, transformations and layering, data quality and testing, and performance, cost, governance and delivering BI. Each lesson ends with an exercise that builds a small warehouse step by step.
What you will learn
- Gather analytical requirements and define business processes
- Choose a warehouse architecture and platform
- Design star schemas with facts and dimensions
- Load data with full and incremental patterns
- Organise transformations into layers
- Test and monitor data quality
- Optimise performance and cost and deliver governed BI
Lessons
- Requirements and Business Processes
- Architecture and Platform Choices
- Dimensional Modelling
- Loading Data
- Transformations and Layering
- Data Quality and Testing
- Performance, Cost, Governance and BI
Self-paced course from Tech Skills Library. Educational content only; it is not an accredited qualification, licence or credential.
Lessons
Requirements and Business Processes
Architecture and Platform Choices
Architectural patterns Traditional warehouse: structured data modelled for reporting. Data lake plus warehouse: raw files stored cheaply with curated warehouse tables. Lakehouse: open table formats on object storage with warehouse-like features. ELT versus ETL Modern cloud warehouses often load raw data first and transform inside the warehouse (ELT), using its compute power. Platform criteria Separation […]
Dimensional Modelling
Dimensional models make data easy to understand and fast to query. Facts and dimensions Fact tables record measurements from business events at a declared grain, such as one row per order line. Dimension tables describe context: customer, product, date, store. Declaring the grain Decide exactly what one fact row represents before choosing columns. Mixed grains […]
Loading Data
Extraction patterns Full loads: copy entire tables; simple but costly for large tables. Incremental loads: load only new or changed rows using timestamps or change data capture. Tools Managed connectors, open-source ingestion tools or custom scripts. Staging Land raw data unchanged with load timestamps, so you can reprocess when logic changes. Idempotency Loads should produce […]
Transformations and Layering
Transformation steps Clean data types and formats, deduplicate, apply business rules, conform keys and build dimensions and facts. SQL-based transformation frameworks Frameworks let you write transformations as version-controlled SQL models with dependencies, documentation and tests. Naming and structure Use consistent naming for staging, intermediate and mart models. Documentation Document business definitions for every measure. Exercise […]
Data Quality and Testing
Tests Uniqueness and not-null checks on keys. Referential integrity between facts and dimensions. Accepted values for categories. Reconciliation of totals with source systems. Freshness checks. Monitoring Run tests on every load and alert on failures. Track data quality trends. Ownership Assign owners to datasets who respond to quality issues. Exercise Add at least eight tests […]
Performance, Cost, Governance and BI
Performance Partition or cluster large tables by commonly filtered columns, select only needed columns and pre-aggregate heavily used summaries. Cost Monitor query costs, set budgets, schedule loads efficiently and suspend idle compute. Governance Apply role-based access, mask sensitive columns, track lineage and keep a data catalogue. Delivering BI Build a semantic layer or certified datasets […]