Data Warehouse ETL Testing & Data Quality Management A-Z

Name: Data Warehouse ETL Testing & Data Quality Management A-Z
Rating: 4.4 (8737 reviews)

ETL Testing and Data Quality Management for beginners with practical exercises and certificate of completion.

Created byLorenz D.

Last updated 4/2019

English

Arabic [Auto],German [Auto],

What you'll learn

Understand the basics of ETL/ELT testing
The importance of Data Quality within the Data Lifecycle
Learn the different types of ETL/ELT testing and Data Quality dimensions
Design a set of frequently used ETL/ELT and Data Quality regression tests
A comparison of Data Quality tools (Informatica, Talend, Oracle, SAS)
Perform database tests using queries and build database views to automate ETL/ELT testing and Data Quality monitoring
Data Quality Management and monitoring
Setting up a database connection to your visualization tool
Build Data Quality dashboards and reports

Course content

4 sections • 20 lectures • 1h 52m total length

Introduction5:11
Explore data quality and testing in a practical ETL and ELT context, building database views and dashboards to monitor validity, completeness, and data lineage with PostgreSQL and QlikSense.
What is ETL/ELT Testing and Data Quality Management?4:48
Explore how ETL and ELT testing ensures data quality and governance across a data warehouse, staging area, and data marts, testing transformations, lineage, and schema integrity from source to target.
Data Categories and Classifications7:38
Explore data categories such as transactional, master, reference, and metadata, and see how they shape data quality, governance, and one version of the truth across systems.
Data Quality Rules and Dimensions6:32
Data Requirements and Test Design - Exercise4:22
Build data quality rules and database views to monitor completeness, uniqueness, validity, consistency, and integrity across staging, data warehouse, and data mart, and create a QlikSense dashboard.
Quiz

What are Database Views?4:25
Learn how database views are stored queries that fetch data from tables without storing. Build data quality views for profiling, completeness, validity, uniqueness, and lineage in PostgreSQL or Oracle.
View Data Quality Completeness - Exercise6:31
Identify null records across tables for dataset completeness, focusing on order id, order date, and sales. Create a data quality completeness view to display null counts by column and table.
View Data Quality Uniqueness - Exercise6:53
Validate data quality uniqueness by comparing total to distinct records, identify duplicates such as orders and product names, and create a data quality uniqueness view to summarize totals and differences.
View Data Quality Validity - Exercise9:35
Assess data quality validity in staging orders by validating quantity, sales, discount, unit price, shipping cost, and order vs ship dates; create a validity view for master data management.
View Data Quality Consistency - Exercise7:23
Analyze data quality consistency by inspecting distinct values in staging orders, and build a data quality consistency view for order priority, ship mode, and product category and subcategory.
View Data Quality Integrity - Exercise10:37
Examine data lineage by comparing staging tables to the data mart with an except-like check in PostgreSQL, verify order id and order date alignment, and ensure data quality integrity.
View Data Profiling - Exercise6:34
Explore data profiling to gain insight into your data and metadata before applying data quality rules. Query PostgreSQL and Oracle metadata views like pg_stats and information_schema.columns to reveal structure.
Quiz

Create a Database Connection4:02
Connect to a PostgreSQL database to load data quality views into a dashboard using QlikSense or any visualization tool, then edit and refine the data quality dataset.
Data Quality Completeness Dashboard - Exercise6:29
Create a data quality completeness dashboard to track null records by table and attribute, using a detailed analysis table and summary pie charts, with filtering and data reloads.
Data Quality Uniqueness Dashboard - Exercise2:24
Build a data quality uniqueness sheet with tables and constraints, track distinct and duplicate counts, switch to a detailed table view, and summarize duplicates per table with a pdf export.
Data Quality Validity Dashboard - Exercise4:19
Develop a data validity dashboard to track valid and invalid records against business rules, using a detailed analysis table and a summary chart to flag invalid order quantities.
Data Quality Consistency Dashboard - Exercise4:43
Build a data quality consistency sheet to compare distinct values and their counts against reference values, using table and distribution visualizations to detect inconsistencies.
Data Quality Integrity Dashboard - Exercise4:02
Build a data integrity sheet with a detailed analysis table and a summary chart to detect integrity mismatches between staging tables and the data mart, such as customer name discrepancies.
Data Profiling & Summary Dashboard - Exercise5:24
Create a summary sheet to view data quality measures, including completeness, uniqueness, validity, consistency, and integrity. Build a data profiling dashboard showing total records per table.
Quiz

Requirements

Some basic knowledge of SQL
Some basic knowledge of visualization tools is recommended, but not required
Database setup (PostgreSQL, Oracle, SQL Server etc.) is recommended
Visualization setup (Qlik, Spotfire, Microstrategy, Tableau, PowerBI etc.) is recommended

Description

Learn the essentials of ETL Data Warehouse Testing and Data Quality Management through this step-by-step tutorial. This course takes you through the basics of ETL testing, frequently used Data Quality queries, reporting and monitoring. In this tutorial we will learn how to build database views for Data Quality monitoring and build Data Quality visualizations and reports!

..Learn to build data quality dashboards from scratch!

..Learn some of the most common mistakes made when performing ETL/ELT tests..

..Forget about manual ad-hoc ETL testing, learn more about automated ETL and data quality reports

The course contains training materials, where you can practice, apply your knowledge and build an app from scratch. The training materials are provided in an Excel file that you can download to your computer. Each module also ends with a short quiz and there is a final quiz at the end of the course.

After completion of this course, you will receive a certificate of completion.

Good luck and hope you enjoy the course.

Pre-requisites:

Basic knowledge of SQL
Some experience with Visualization tools would be helpful, but not required
Basic setup of database (PostgreSQL, Oracle) and visualization tool (Qliksense) is recommended

Course content:

The course consists of the following modules:

Introduction
What is ETL/ELT Testing and Data Quality Management?
Build database views for Data Quality Monitoring
Build dashboards for Reporting
Exercises
Final Quiz

Who should follow this course?

Students that want to learn the basics of ETL/ELT testing and Data Quality Management
Business Analysts and Data Analysts that would like to learn more about ETL/ELT testing, frequently used queries and practical examples
Software Engineers that would like to build an automated solution for ETL/ELT testing using database views/dashboards
Data Stewards and Managers considering to apply data quality standards within their organization

Who this course is for:

Students that want to learn the basics of ETL/ELT testing and Data Quality Management
Business Analysts and Data Analysts that would like to learn more about ETL/ELT testing, frequently used queries and practical examples
Software Engineers that would like to build an automated solution for ETL/ELT testing using database views/dashboards
Data Stewards and Managers considering to apply data quality standards within their organization

Data Warehouse ETL Testing & Data Quality Management A-Z

What you'll learn

Explore related topics

Course content

Introduction5 lectures • 29min

Data Quality and ETL Testing - Building Database Views7 lectures • 52min

Data Quality and ETL Testing - Dashboards and Monitoring7 lectures • 31min

Conclusion - Final Note1 lecture • 1min

Requirements

Description

Who this course is for: