Back to Catalog

Capstone Project: Data Warehouse Engineering

PremiumAdvancedCourse

Design and implement a data warehouse in a practical capstone project. Apply SQL, ETL, and BI skills to build, manage, and report from a real-world data warehouse using MySQL, PostgreSQL, shell scripting, and Cognos Analytics.

Language

  • English

Topic

  • Database

Skills You Will Learn

  • Business Intelligence (BI), Cognos Analytics, Data Warehousing, Enterprise Data Warehouse (EDW), ETL, PostgreSQL

Offered By

  • IBMSkillsNetwork

Estimated Effort

  • 10 Hours

Platform

  • SkillsNetwork

Last Update

  • December 16, 2025
About this Course
Design, develop, and analyze data in a real-world data warehousing solution using industry-standard tools, techniques, and workflows.

In this capstone course, part of the IBM Data Warehouse Engineer Professional Certificate, learners apply the full range of data warehousing skills acquired throughout the program. Assuming the role of a Junior Data Engineer, you are presented with a real-world use case that requires designing and implementing a complete data warehouse solution.

In this capstone project, you will:
  • Demonstrate proficiency in end-to-end data warehouse engineering practices through this capstone project.
  • Design a data platform using PostgreSQL as a staging and production data warehouse.
  • Implement a full data warehouse with ETL pipelines to extract source data from an OLTP database and apply transformations.
  • Query and generate reports from stored data.
  • Implement dimensional models, fact tables, materialized views, cubes, and rollups.
  • Create interactive dashboards using IBM Cognos Analytics or Google Looker to visualize business metrics.

The course begins with designing a data platform using MySQL as an OLTP system. You then build and implement a data warehouse from which reports will be generated. As part of the process, you also create ETL pipelines using shell scripts and SQL to extract transactional data from MySQL and load it into a PostgreSQL-based warehouse. Transformation of data into dimensional models, exporting as CSVs, and automation using Cron Jobs are also covered.

You then take on a reporting challenge, using IBM Cognos Analytics or Google Looker to design dashboards that communicate key business metrics. 

This course culminates in a reviewed project submission, allowing you to demonstrate your complete data warehouse engineering workflow.

Recommended Skills Prior to Taking this Course

The following skills are required to be successful in this program: 
  • We recommend completing all previous courses in the IBM Data Warehouse Engineer Professional Certificate before starting this capstone.
  • Through these courses, you will have developed working knowledge of the following required for this course: 
    • Data Engineering fundamentals
    • Relational Databases (RDBMS)
    • SQL
    • Linux commands and Shell Scripting
    • Relational Database Administration (DBA)
    • ETL and Data Pipelines
    • Data Warehouse Design and Implementation
    • IBM Cognos Analytics or Google Looker

Course Syllabus

Module 1: Data Platform Architecture and OLTP Database
  • Introduction to Capstone Project
  • Data Platform Architecture
  • Assignment Overview: OLTP Database
  • OLTP Database Requirements and Design
  • Lab: OLTP Database
  • Checklist: OLTP Database
  • Graded Quiz: OLTP Database

Module 2: Build a Data Warehouse

Data Warehouse Design and Setup
  • Assignment Overview: Data Warehouse Design & Setup
  • Hands-on Lab: Data Warehousing
  • Checklist: Data Warehousing
Data Warehouse Reporting
  • Assignment Overview: Data Warehouse Reporting
  • Hands-on Lab: Data Warehouse Reporting using PostgreSQL
  • Optional: Obtain IBM Cloud Feature Code and Activate Trial Account
  • Optional Lab Information
  • Optional: Hands-on Lab: Data Warehouse Reporting using DB2
  • Checklist: Data Warehouse Reporting
  • Graded Quiz: Build a Data Warehouse

Module 3: Data Analytics

Data Analytics Overview and Preparation
  • Assignment Overview
  • Optional: About this Optional Lesson with Looker Studio
  • Optional: Getting Started with Google Looker Studio
  • Optional: Hands-on Lab: Getting Started with Google Looker Studio
  • Optional: Creating Visualizations in Reports using Looker Studio
  • Optional: Hands-on Lab: Creating and Configuring Visualizations in Reports with Google Looker Studio
  • Optional: Hands-on Lab: Advanced Charts in Looker Studio
  • Optional: Summary and Highlights
Dashboard Creation
  • Final Assignment Overview
  • Final Assignment Part A: Dashboard Creation using IBM Cognos Analytics
  • Final Assignment Part B: Dashboard Creation using Google Looker Studio
  • Checklist: Dashboard Creation
  • Graded Quiz: Dashboard Creation

Module 4: ETL & Data Pipelines
  • Assignment Overview: ETL with MySQL, PostgreSQL, and Bash
  • Assignment Overview: ETL & Data Pipelines
  • Formatting Functions in PostgreSQL
  • Hands-on Lab: ETL with MySQL, PostgreSQL, and Bash
  • Checklist: ETL
  • Graded Quiz: ETL with MySQL, PostgreSQL, and Bash

Module 5: Final Submission and Peer Review
  • Submit your Work and Review your Peers
  • Submit your Work and Review your Peers
Course Wrap-Up
  • Congrats & Next Steps
  • Thanks from the Course Team