Description:

This course on Data Modeling Basics With Use Case is designed to provide a comprehensive understanding of data modeling concepts and techniques, with a focus on practical use cases. Participants will learn how to create effective data models to support business requirements and decision-making processes. The course covers entity-relationship modeling, normalization, and data modeling best practices.

Data Modeling Course – Table of Contents

Module 1: Introduction to Data Modeling

1.1 What is Data Modeling?
1.2 Importance of Data Modeling
1.3 Benefits of Data Modeling
1.4 Data Modeling Best Practices

Module 2: Database & Data Warehousing Fundamentals

2.1 What is a Database?
2.2 What is a DBMS (Database Management System)?
2.3 Understanding Database Schemas
2.4 Tables, Columns, and Rows
2.5 Schema vs Database
2.6 Database Architecture & Hierarchy
2.7 What is a Data Warehouse?
2.8 OLTP vs OLAP Systems
2.9 Data Lake vs Data Warehouse vs Lakehouse

Module 3: Core Data Modeling Concepts

3.1 Primary Key & Foreign Key
3.2 Surrogate Key vs Natural Key
3.3 Composite Key

Module 4: Data Normalization & Denormalization

4.1 Introduction to Data Normalization
4.2 First Normal Form (1NF)
4.3 Second Normal Form (2NF)
4.4 Third Normal Form (3NF)
4.5 Data Denormalization
4.6 Normalization vs Denormalization

Module 5: Dimension Modeling Concepts

5.1 What is a Dimension Table?
5.2 Types of Dimensions
5.3 Conformed Dimensions
5.4 Degenerate Dimensions
5.5 Junk Dimensions
5.6 Role-Playing Dimensions
5.7 Calendar Date Dimension
5.8 Dimension Types – Quick Comparison

Slowly Changing Dimensions (SCDs)

5.9 Introduction to SCDs
5.10 Type 0 – Fixed Dimension
5.11 Type 1 – Overwrite
5.12 Type 2 – Add New Row
5.13 Type 3 – Add New Column
5.14 Type 4 – Separate History Table
5.15 Type 6 – Hybrid Approach (1+2+3)

Module 6: Fact Tables & Measures

6.1 What is a Fact Table?
6.2 Types of Fact Tables
6.3 Transactional Fact Tables
6.4 Periodic Snapshot Fact Tables
6.5 Accumulating Snapshot Fact Tables
6.6 Factless Fact Tables

Referential Integrity

Module 7: Advanced Data Modeling Techniques

7.1 Handling Early-Arriving Data
7.2 Handling Late-Arriving Data
7.3 Indexing Strategies for Fact & Dimension Tables

Module 8: Schema Modeling Techniques

8.1 Star Schema Modeling
8.2 Snowflake Schema Modeling
8.3 Star Schema vs Snowflake Schema

Module 9: Types of Data Models

9.1 Conceptual Data Model
9.2 Logical Data Model
9.3 Physical Data Model

Module 10: Entities, Attributes & Relationships

10.1 Entities and Attributes
10.2 Relationships in Data Modeling

Types of Relationships

10.3 One-to-One Relationship
10.4 One-to-Many Relationship
10.5 Many-to-One Relationship
10.6 Many-to-Many Relationship
Cardinality in Data Modeling

Module 11: Modern Data Architecture

11.1 What is Data Integrity?
11.2 ETL vs ELT
11.3 Medallion Architecture (Bronze, Silver, Gold)
11.4 Modern Cloud Data Warehouses (Databricks / Microsoft Fabric / Snowflake etc)

Module 12: Data Modeling Tools

12.1 Introduction to Data Modeling Tools
12.2 DBDesigner.io
12.3 Comparison with Other Tools (Erwin, Lucidchart, Draw.io)

Module 13: Business Understanding & Requirement Gathering

13.1 Understanding Business Processes
13.2 Translating Business Requirements into Data Requirements
13.3 Identifying Key Entities, Attributes & Relationships

Module 14: Real-World Use Case – Airline Domain

14.1 Airline Domain Use Case Overview
14.2 Understanding Input Datasets
14.3 Creating Data Models using DBDesigner.io

Module 15: Hands-on Data Modeling Implementation

Snowflake Schema Implementation

15.1 Conceptual Data Model
15.2 Logical Data Model
15.3 Physical Data Model

Star Schema Implementation

15.4 Conceptual Data Model
15.5 Logical Data Model
15.6 Physical Data Model

Module 16: Reporting & Analytics Layer

16.1 Gold Layer Aggregations
16.2 Power BI Integration
16.3 Creating Important DAX Measures
16.4 Deriving Business Insights
16.5 Why Star Schema is Preferred in BI Reporting
16.6 End-to-End Data Flow Architecture

Module 17: Advanced Modern Data Architectures-Additional

17.1 What is Data Vault Modeling
17.2 Hub, Link, Satellite Tables
17.3 Data Vault vs Star Schema
17.4 What is Data Mesh
17.5 Principles of Data Mesh
17.6 What is Data Fabric
17.7 Data Fabric Architecture

Module 18: Best Practices & Interview Preparation

18.1 Choosing the Right Data Model
18.2 Frequently Asked Interview Questions

Module 19: Bonus Resources

18.1 Reference Articles & Documentation
18.2 Youtube Videos
 

What you will learn:

• Understand Data Modeling Fundamentals
  Gain a solid foundation in data modeling concepts and principles
• Apply Data Modeling Techniques
  Learn how to create effective data models for various use cases
• Develop Practical Use Cases
  Explore real-world scenarios and apply data modeling concepts