Open Source Data Warehousing: Developing a SQL Data Warehouse

Inquire now

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.

 

Inquire now

Best selling courses

Duration: 5 days – 35 hrs   Overview The “SOC Network and Threat Detection and Analysis” training course is designed to equip Security Operations Center (SOC) analysts and IT security professionals with the skills and knowledge required to detect, analyze, and respond to network threats effectively. This comprehensive course covers essential topics such as threat...

Duration 1 day – 7 hrs   Overview   This 1-day training builds upon basic warehouse operations knowledge and introduces key logistics concepts involved in the movement and coordination of goods—especially wet and dry food items—within and outside the warehouse. Participants will explore transport logistics, inbound and outbound coordination, documentation practices, and cold chain considerations,...

Duration 2 days – 14 hrs   Overview   This hands-on course provides an introduction to Splunk, a powerful platform for searching, monitoring, and analyzing machine-generated data. The training focuses on how developers and QA professionals can leverage Splunk to gain insights from logs and metrics, improve application observability, detect anomalies, and support test validation....

Duration 3 days – 21 hrs   Overview.   This course is designed for fresh graduates aspiring to build a career in Data Science. It introduces the fundamentals of data science, focusing on data analysis, visualization, and basic machine learning concepts using Python. The course provides hands-on practice with real-world datasets, equipping participants with the...

Duration 5 days – 35 hrs   Overview   The Microsoft Certified: Azure Administrator Associate (AZ-104) Training Course is designed for IT professionals who want to develop expertise in managing Microsoft Azure environments. This course provides hands-on training on deploying, managing, and monitoring Azure services, including virtual machines, networking, storage, identity, and security.   Objectives...

ARTIFICIAL INTELLIGENCE / MACHINE LEARNING / DEEP LEARNING

Capstone Workshop: Build Your Own GenAI Toolkit TECH AI

GenAI Toolkit Training is a practical, hands-on capstone workshop designed to help participants bring their generative AI knowledge together into a personalized toolkit of AI tools, prompts, workflows, and practical solutions. Rather than simply learning individual AI platforms, participants will learn how to select and combine the right tools to address real-world technical and professional...

In this course, you will gain practical foundation level training that enables immediate and effective participation in big data and other analytics projects. Learn ways of storing data that allow for efficient processing and analysis, and gain the skills you need to store, manage, process, and analyze massive amounts of unstructured data to create an appropriate data lake.

Advanced Laravel Engineering Scalable Architecture, Performance, Testing, Queues & DevOps equips developers with the skills to design scalable Laravel applications, optimize performance, implement automated testing, manage queues, and integrate DevOps practices for reliable, production-ready deployments.   Duration 5 Days – 35 hrs   Overview This advanced-level bootcamp is designed for teams building large-scale or mission-critical...

// BG EARTH WHEN NOT PLAYING

We use cookies on our website to personalize your experience by storing your preferences and recognizing repeat visits. By clicking “Accept”, you agree to the use of all cookies. You can also select “Cookie Settings” to adjust your preferences and provide more specific consent. Cookie Policy