Dimensional Data Modeling | Set 01

2 Scenarios

25 Minutes

Intermediate

Locked scenarios

Scenarios to be attempted in sequence

Industry

retail-and-cpg

Skills

data-modelling

data-understanding

Tools

excel

sql

Learning Objectives

Design and normalize relational tables to eliminate redundancy and maintain data integrity

Optimize fact and dimension tables for efficient querying

Implement Slowly Changing Dimensions (SCDs) to properly track historical data changes

Choose the right schema (Star vs. Snowflake) to balance performance, storage, and scalability

Overview

GlobalMart, a fast-growing e-commerce startup, is expanding its operations and analytics capabilities. To support this growth, it relies on dimensional modeling for business intelligence. However, several challenges arise:

📌 Data Integrity & Normalization: Inefficient database design leads to null values, redundancy, and inconsistencies in customer and order data.

📌 Query Performance: Analysts struggle with slow queries when analyzing large volumes of sales and transaction data.

📌 Historical Tracking: Customer details, sales regions, and account balances change over time and require proper versioning.

📌 Schema Design: Choosing between Star and Snowflake schemas impacts query speed and storage efficiency.

Your Task: As a Data Engineer, your goal is to analyze, design, and optimize GlobalMart’s dimensional data model.

Prerequisites

Knowledge of concepts like entities, attributes, relationships, normalization (1NF, 2NF, 3NF)
Understanding of dimensional modeling – Fact and dimension tables, Star vs. Snowflake schema
Experience with data integrity and historical tracking – Primary & foreign keys, Slowly Changing Dimensions (SCDs)
Basic data warehousing concepts – Fact table granularity, partitioning, and aggregation