This exercise applies all four Module 1 concepts by designing and implementing a complete star schema data warehouse for the SkillVeris e-learning SaaS platform. The platform generates three types of measurable events: lesson completions (a learner completes a lesson), quiz attempts (a learner submits a quiz with a score), and XP awards (a learner earns experience points). You will design the grain, fact tables, dimension tables, and aggregate tables for these events, implement SCD Type 2 for the learner dimension, and validate the schema with additivity and idempotency assertions.
The exercise is structured in two steps. Step 1 builds the dimension tables with surrogate keys and SCD Type 2 for `dim_learner`, creates the three fact tables at their declared grains, and populates them with generated event data. Step 2 builds the aggregate tables at two grain levels (weekly and monthly), validates additivity of the XP and completion count measures, and runs a point-in-time query against the SCD Type 2 `dim_learner` to verify that historical fact records are correctly associated with the learner's attributes at the time of the event.
Analogy🏏Cricket
🏏 Think of it like cricket: OLTP is the IPL's live ticketing counter — it handles thousands of simultaneous seat reservations, each requiring a precise single-seat record update with immediate confirmation. Speed per transaction and data consistency under concurrent updates are everything. OLAP is the IPL's season statistics department — it runs complex analytical queries across every ball bowled in every match of every season to produce the published rankings, economy rates, and historical comparisons. No one books a seat through the statistics department, and no broadcaster calls the ticketing counter for Bumrah's career economy rate. The two workloads demand completely different systems. Just as the ticketing counter is built for speed and correctness on one seat at a time and would buckle if asked to tally a decade of attendance mid-sale, an OLTP row-store excels at single-record writes but chokes on full-table aggregation; and just as the statistics department pores over millions of past deliveries but would be hopeless at booking a live seat under contention, the OLAP columnar engine sweeps billions of rows yet is the wrong tool for a fast single-row update. The physical design of each — row-oriented for the counter, columnar for the stats desk — is what makes it superb at its own job and unfit for the other's.
Step 1 — Dimensions, Facts and SCD Type 2
Design and create the dimension tables (`dim_learner` with SCD Type 2 for subscription tier, `dim_course`, `dim_lesson`, `dim_date`), create the three fact tables at their declared grains, populate with generated event data, and apply an SCD Type 2 update to simulate a learner upgrading from Free to Pro. Verify the SCD Type 2 history shows the correct version for the learner's tier at different dates and that fact rows associate with the correct dimension version through the surrogate key.
Analogy🏏Cricket
🏏 Think of it like cricket: Building dim_learner with SCD Type 2 is like keeping a franchise squad register you never overwrite. Just as Hardik Pandya gets a fresh registration number when he moves from GT to MI — his old number closed with an end date, a new number opened — a learner upgrading from Free to Pro gets a brand-new surrogate learner_key while the old row's valid_to is stamped and is_current flipped to false. Just as every entry in the ball-by-ball log points at the exact registration number the bowler held that day, each fact_lesson_completion row stores the surrogate key current when the lesson finished, so a completion earned under 'Free' stays 'Free' forever. Verifying the SCD Type 2 history — one closed version, one open version — is exactly like auditing the squad register to confirm both spells are recorded with clean, non-overlapping date boundaries. The payoff: point-in-time-correct analytics where no past event is ever silently reattributed to a learner's current tier.
python
# exercise_dw_schema.py — Step 1: Dimensions, facts and SCD Type 2
import duckdb
import pandas as pd
import numpy as np
from datetime import date, datetime, timezone, timedelta
np.random.seed(42)
con = duckdb.connect(":memory:")
# ── Dimension: dim_learner (SCD Type 2 for subscription_tier) ─────────────────
con.execute("""
CREATE TABLE dim_learner (
learner_key INTEGER PRIMARY KEY, -- surrogate (one per version)
learner_id VARCHAR(20) NOT NULL, -- natural key
learner_name VARCHAR(80) NOT NULL,
country VARCHAR(40) NOT NULL,
signup_date DATE NOT NULL,
subscription_tier VARCHAR(10) NOT NULL, -- 'free' | 'pro' | 'team'
-- SCD Type 2 columns
valid_from DATE NOT NULL,
valid_to DATE,
is_current BOOLEAN NOT NULL DEFAULT TRUE
);
""")
# Initial learners: all start on Free tier
learners_initial = [
(1001,'L001','Arun Sharma', 'India','2024-01-15','free','2024-01-15',None,True),
(1002,'L002','Priya Nair', 'India','2024-02-01','free','2024-02-01',None,True),
(1003,'L003','Carlos Ruiz', 'Mexico','2024-03-10','pro','2024-03-10',None,True),
]
for row in learners_initial:
con.execute("INSERT INTO dim_learner VALUES (?,?,?,?,?,?,?,?,?)", row)
# SCD Type 2: Arun upgrades to Pro on 2024-04-01
con.execute("UPDATE dim_learner SET valid_to='2024-04-01', is_current=FALSE WHERE learner_key=1001")
con.execute("INSERT INTO dim_learner VALUES (1004,'L001','Arun Sharma','India','2024-01-15','pro','2024-04-01',NULL,TRUE)")
# ── Dimensions: dim_course, dim_lesson, dim_date ──────────────────────────────
con.execute("""
CREATE TABLE dim_course (
course_key INTEGER PRIMARY KEY,
course_id VARCHAR(20) NOT NULL,
course_name VARCHAR(100) NOT NULL,
difficulty VARCHAR(10) NOT NULL, -- 'beginner'|'intermediate'|'advanced'
topic_area VARCHAR(40) NOT NULL,
total_lessons INTEGER NOT NULL,
total_xp INTEGER NOT NULL
);
INSERT INTO dim_course VALUES
(2001,'C001','Python for DE', 'beginner', 'Data Engineering', 35, 1800),
(2002,'C002','Cloud DE', 'advanced', 'Cloud Computing', 35, 2400),
(2003,'C003','DW Analytics Eng', 'advanced', 'Data Warehouse', 35, 2300);
""")
con.execute("""
CREATE TABLE dim_lesson (
lesson_key INTEGER PRIMARY KEY,
lesson_id VARCHAR(20) NOT NULL,
course_key INTEGER NOT NULL REFERENCES dim_course(course_key),
lesson_title VARCHAR(120) NOT NULL,
lesson_type VARCHAR(10) NOT NULL, -- 'reading'|'exercise'|'project'
xp_reward INTEGER NOT NULL,
est_minutes INTEGER NOT NULL
);
INSERT INTO dim_lesson VALUES
(3001,'C001-L01',2001,'OLTP vs OLAP', 'reading', 75, 25),
(3002,'C001-L02',2001,'Star Schema', 'reading', 75, 30),
(3003,'C002-L01',2002,'Cloud Computing Models','reading', 75, 25),
(3004,'C003-L01',2003,'Data Vault 2.0', 'reading', 75, 30);
""")
# ── Fact tables ───────────────────────────────────────────────────────────────
# GRAIN: one row = one lesson completion by one learner
con.execute("""
CREATE TABLE fact_lesson_completion (
completion_key BIGINT PRIMARY KEY,
learner_key INTEGER NOT NULL, -- surrogate (version-specific)
lesson_key INTEGER NOT NULL,
course_key INTEGER NOT NULL,
completed_date DATE NOT NULL,
-- Measures
xp_earned INTEGER NOT NULL,
time_spent_min INTEGER NOT NULL,
completion_flag INTEGER NOT NULL DEFAULT 1 -- additive count
);
""")
# Generate 200 lesson completions
learner_keys = [1001, 1002, 1003, 1004] # 1001 is Arun pre-upgrade
lesson_keys = [3001, 3002, 3003, 3004]
course_map = {3001:2001, 3002:2001, 3003:2002, 3004:2003}
base_date = date(2024, 1, 15)
completions = []
for i in range(200):
lk = int(np.random.choice(learner_keys))
lsk = int(np.random.choice(lesson_keys))
ck = course_map[lsk]
dt = base_date + timedelta(days=int(np.random.randint(0, 120)))
completions.append({
"completion_key": i+1,
"learner_key": lk,
"lesson_key": lsk,
"course_key": ck,
"completed_date": dt.isoformat(),
"xp_earned": int(np.random.choice([75, 100, 175])),
"time_spent_min": int(np.random.randint(10, 60)),
"completion_flag": 1,
})
df_completions = pd.DataFrame(completions)
con.register("completions_data", df_completions)
con.execute("INSERT INTO fact_lesson_completion SELECT * FROM completions_data")
# Verify SCD Type 2 history
history = con.execute(
"SELECT learner_key, learner_id, subscription_tier, valid_from, valid_to, is_current "
"FROM dim_learner WHERE learner_id='L001' ORDER BY valid_from"
).df()
print("SCD Type 2 history for L001 (Arun Sharma):")
print(history.to_string(index=False))
# Point-in-time: Arun's tier for completions before and after upgrade
pre = con.execute("SELECT learner_key, subscription_tier, valid_from, valid_to FROM dim_learner WHERE learner_id='L001' AND valid_from='2024-01-15'").df()
post = con.execute("SELECT learner_key, subscription_tier, valid_from, valid_to FROM dim_learner WHERE learner_id='L001' AND is_current=TRUE").df()
assert pre.iloc[0]['subscription_tier'] == 'free'
assert post.iloc[0]['subscription_tier'] == 'pro'
print("\nSCD Type 2: pre-upgrade=free, post-upgrade=pro ✓")
print(f"Total lesson completions: {len(df_completions)} ✓")
print("Step 1 ✓: Dimensions, facts and SCD Type 2 complete")Step 2 — Aggregate Tables and Additivity Validation
Build weekly and monthly aggregate tables from the lesson completion fact, validate additivity of XP and completion count measures (aggregate totals must equal the sum across all delivery-grain rows), and run a query joining the monthly aggregate with the learner SCD Type 2 dimension using the correct point-in-time surrogate key join to produce a monthly completion report by subscription tier. Assert total XP conservation and confirm that the non-additive completion rate is computed correctly from the additive completion and total_lesson counts.
Analogy🏏Cricket
🏏 Think of it like cricket: Aggregate tables are the over-by-over and full-match scorecard summaries built from the ball-by-ball log. Just as a scorer proves a scorecard is trustworthy by confirming every delivery's runs sum to the same total as every over's runs and every innings' runs, this step validates additivity by asserting the delivery-grain XP total equals the weekly aggregate total equals the monthly aggregate total — if any disagree, a row was double-counted or dropped. Just as the monthly report joins each learner's completions to the tier they held that month via the version-specific surrogate key — never the natural key — a cricket analyst joins each innings to the captaincy role the player held on that match date, not their current role. And just as you never average two batsmen's strike rates to get a team strike rate but recompute from total runs and total balls, the completion rate here is recomputed from additive counts, never summed from pre-computed rates. The payoff: fast dashboards whose numbers still reconcile exactly to the raw events.
python
# exercise_dw_schema.py — Step 2: Aggregate tables and additivity validation
import duckdb
import pandas as pd
# ── Weekly aggregate table ────────────────────────────────────────────────────
con.execute("""
CREATE TABLE agg_weekly_completion AS
SELECT
DATE_TRUNC('week', CAST(completed_date AS DATE)) AS week_start,
course_key,
SUM(xp_earned) AS total_xp, -- additive: SUM safely
SUM(completion_flag) AS completions, -- additive: SUM safely
SUM(time_spent_min) AS total_minutes,
AVG(time_spent_min) AS avg_minutes -- semi-additive: store for reporting
FROM fact_lesson_completion
GROUP BY DATE_TRUNC('week', CAST(completed_date AS DATE)), course_key
""")
# ── Monthly aggregate table ───────────────────────────────────────────────────
con.execute("""
CREATE TABLE agg_monthly_completion AS
SELECT
DATE_TRUNC('month', CAST(completed_date AS DATE)) AS month_start,
learner_key,
course_key,
SUM(xp_earned) AS total_xp,
SUM(completion_flag) AS completions,
SUM(time_spent_min) AS total_minutes
FROM fact_lesson_completion
GROUP BY DATE_TRUNC('month', CAST(completed_date AS DATE)), learner_key, course_key
""")
# ── Additivity validation: aggregate totals must equal fact totals ─────────────
fact_xp = con.execute("SELECT SUM(xp_earned) FROM fact_lesson_completion").fetchone()[0]
fact_cnt = con.execute("SELECT SUM(completion_flag) FROM fact_lesson_completion").fetchone()[0]
weekly_xp = con.execute("SELECT SUM(total_xp) FROM agg_weekly_completion").fetchone()[0]
monthly_xp = con.execute("SELECT SUM(total_xp) FROM agg_monthly_completion").fetchone()[0]
weekly_cnt = con.execute("SELECT SUM(completions) FROM agg_weekly_completion").fetchone()[0]
assert fact_xp == weekly_xp == monthly_xp, "XP additivity failed"
assert fact_cnt == weekly_cnt, "Completion count additivity failed"
print(f" XP additivity: fact={fact_xp} = weekly={weekly_xp} = monthly={monthly_xp} ✓")
print(f" Count additivity: fact={fact_cnt} = weekly={weekly_cnt} ✓")
# ── Monthly report by subscription tier (correct SCD Type 2 join) ─────────────
monthly_by_tier = con.execute("""
SELECT
a.month_start,
l.subscription_tier,
SUM(a.completions) AS completions,
SUM(a.total_xp) AS total_xp,
ROUND(AVG(a.completions), 1) AS avg_completions_per_learner
FROM agg_monthly_completion a
JOIN dim_learner l ON a.learner_key = l.learner_key -- surrogate key join
GROUP BY a.month_start, l.subscription_tier
ORDER BY a.month_start, l.subscription_tier
""").df()
print("\nMonthly completions by subscription tier:")
print(monthly_by_tier.to_string(index=False))
# ── Non-additive: course completion RATE requires total_lessons ────────────────
# Never sum pre-computed rates — compute from additive counts
course_completion = con.execute("""
SELECT
c.course_name,
SUM(a.completions) AS total_completions,
c.total_lessons,
ROUND(100.0 * SUM(a.completions) /
NULLIF(COUNT(DISTINCT a.learner_key) * c.total_lessons, 0), 1) AS completion_pct
FROM agg_monthly_completion a
JOIN dim_course c ON a.course_key = c.course_key
GROUP BY c.course_name, c.total_lessons
ORDER BY completion_pct DESC
""").df()
print("\nCourse completion rate (non-additive, recomputed from counts):")
print(course_completion.to_string(index=False))
print("\nStep 2 ✓: Aggregate tables and additivity validation complete")