This course covers the design, development, and maintenance of a SQL data warehouse using open-source technologies, with PostgreSQL as the reference database. Participants learn to translate business requirements into dimensional models, integrate data from multiple sources, develop SQL transformations, and prepare reliable datasets for reporting and analysis.
The course covers staging structures, fact and dimension tables, historical data management, incremental loading, data quality, query optimization, and operational maintenance.
Duration 5 Days – 35 hrs.
Objectives
- Explain data warehouse architecture and its role in business intelligence.
- Translate business reporting requirements into a dimensional data model.
- Create staging, dimension, and fact tables using SQL.
- Develop extraction, transformation, and loading processes.
- Implement incremental loads and manage historical dimension changes.
- Apply data quality checks and source-to-target reconciliation.
- Write analytical SQL queries for business reporting.
- Optimize warehouse queries and storage structures.
- Apply access controls, load monitoring, and maintenance practices.
Target Audience
- SQL developers and database developers.
- Data engineers and ETL developers.
- Business intelligence developers.
- Database administrators supporting analytical systems.
- Data analysts with SQL experience.
- Software developers transitioning into data warehousing.
Prerequisites
- Understand relational database concepts, including tables, primary keys, foreign keys, and relationships.
- Be able to write SQL queries using joins, filtering, grouping, and aggregate functions.
- Have basic experience creating tables and modifying data.
- Familiarity with business reporting and spreadsheet or CSV data is helpful.
- Previous data warehouse experience is not required.
Course Outline
Day 1: Data Warehouse Foundations and Requirements
Module 1: Introduction to Open Source Data Warehousing
- Purpose and characteristics of a data warehouse.
- Transactional systems versus analytical systems.
- Data warehouses, data marts, and data lakes.
- Open-source warehouse components and PostgreSQL’s role.
- Source, staging, warehouse, and reporting layers.
- ETL and ELT processing approaches.
Module 2: Requirements and Development Environment
- Identifying business processes, stakeholders, and reporting needs.
- Defining measures, key performance indicators, and data grain.
- Identifying source systems and profiling source data.
- Establishing naming conventions and documentation standards.
- Preparing PostgreSQL databases, schemas, and SQL development tools.
- Defining source-to-target mappings.
Day 2: Dimensional Modeling and Schema Development
Module 3: Designing Dimensional Models
- Star schemas and snowflake schemas.
- Fact tables, dimension tables, and table grain.
- Natural keys and surrogate keys.
- Conformed dimensions and date dimensions.
- Additive, semi-additive, and non-additive measures.
- Transaction and snapshot fact tables.
Module 4: Implementing Warehouse Structures
- Creating staging, dimension, and fact tables.
- Selecting data types and defining constraints.
- Establishing relationships and referential integrity.
- Implementing slowly changing dimensions: Type 1 and Type 2.
- Handling unknown members and late-arriving data.
- Documenting the warehouse schema and data dictionary.
Day 3: Data Integration and Loading
Module 5: Extracting and Transforming Source Data
- Loading relational extracts and CSV files into staging tables.
- Applying bulk-loading techniques.
- Standardizing text, dates, numeric values, and codes.
- Handling nulls, duplicates, and invalid records.
- Using joins, common table expressions, and SQL functions for transformations.
- Applying business rules and mapping source values.
Module 6: Building Reliable Load Processes
- Initial full loads and incremental loading.
- Watermarks and change detection fundamentals.
- Loading dimensions before related fact tables.
- Resolving surrogate keys and preserving dimension history.
- Handling source updates and deletions.
- Using transactions, load logs, and restartable processing.
- Preventing duplicate results when rerunning loads.
Day 4: Data Quality and Analytical SQL
Module 7: Data Quality and Reconciliation
- Defining completeness, validity, consistency, and uniqueness rules.
- Validating relationships between facts and dimensions.
- Reconciling record counts and business totals.
- Checking historical validity periods and duplicate business keys.
- Managing rejected records and correction workflows.
- Capturing data lineage and load audit information.
Module 8: Developing Reporting Datasets
- Querying star schemas with appropriate joins.
- Calculating business measures at the correct grain.
- Using window functions for ranking and running totals.
- Developing period comparisons and trend queries.
- Creating reporting views and summary tables.
- Using materialized views and planning refreshes.
- Preventing double counting in analytical queries.
Day 5: Performance and Warehouse Operations
Module 9: Optimizing Warehouse Performance
- Reading query execution plans.
- Selecting indexes for analytical workloads.
- Applying table partitioning and partition pruning.
- Maintaining database statistics.
- Optimizing joins, filters, and aggregations.
- Balancing query performance with loading requirements.
Module 10: Operating and Maintaining the Data Warehouse
- Applying roles, permissions, and least-privilege access.
- Scheduling load processes and managing dependencies.
- Monitoring load failures, data freshness, and warehouse growth.
- Planning backup, recovery, and retention.
- Managing SQL scripts and schema changes through version control.
- Documenting deployment, maintenance, and recovery procedures.
