This exercise builds a production-grade dbt project for the IPL analytics platform using DuckDB as the warehouse backend. You will implement the complete dbt model hierarchy: staging models that clean and rename raw columns, intermediate models that join and enrich staging data, and Gold mart models that produce the bowler season statistics and match summary tables. The exercise adds schema.yml tests, simulates dbt Snapshots for the player dimension SCD Type 2, and implements a Semantic Layer-equivalent metric computation with the ratio additivity test from Module 1.
The exercise is structured in two steps. Step 1 builds the staging, intermediate, and mart model layers in DuckDB, applies a custom `phase_label` macro equivalent, verifies column-level data quality tests (not_null, unique, accepted_values, between), and confirms row conservation from staging through to the Gold mart. Step 2 simulates dbt Snapshots for the player dimension, runs the metric computation equivalent to the Semantic Layer economy_rate metric, and verifies that the ratio metric correctly uses separate numerator/denominator aggregation rather than averaging pre-computed ratios.
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 — Model Hierarchy and Data Quality Tests
Build the three-layer model hierarchy: staging (rename + type cast raw columns), intermediate (join with match and player dimensions, derive phase), and Gold mart (economy rate, wickets, boundaries per bowler per season). Apply data quality assertions equivalent to dbt schema.yml tests: not_null on primary keys, unique on delivery_id, accepted_values on phase, and between(0,6) on runs_scored. Assert row conservation from staging to Gold and verify the phase distribution matches expected proportions (6/20 powerplay, 9/20 middle, 5/20 death).
Analogy🏏Cricket
🏏 Think of it like cricket: The three-layer model hierarchy is the pipeline from raw telegraph feed to certified scorecard. Staging renames and type-casts the raw ball-by-ball columns like a scorer transcribing scribbled notes into a clean standard form; the intermediate layer derives phase (powerplay, middle, death) and boundary flags like an analyst enriching each delivery with context; the Gold mart aggregates economy, wickets and boundaries per bowler like the published season statistics. The dbt tests are the referee's checks before certification: not_null on keys (every delivery has an ID), unique on delivery_id (no ball logged twice), accepted_values on phase (only the three legal phases), and between(0,6) on runs (no impossible score off the bat). Just as the referee confirms the raw ball count equals the scorecard's total before signing off, row conservation asserts the staging count equals the Gold deliveries sum. The payoff: no wrong number reaches the public scoreboard because every layer is quality-gated.
python
# exercise_dbt_project.py — Step 1: Model hierarchy and data quality tests
import duckdb
import pandas as pd
import numpy as np
from datetime import date, datetime, timezone
np.random.seed(42)
con = duckdb.connect(":memory:")
# ── Raw source data ───────────────────────────────────────────────────────────
N = 1200 # 10 matches × 120 deliveries
raw_deliveries = pd.DataFrame({
"DeliveryID": range(1, N+1),
"MatchID": np.repeat(range(10001, 10011), 120),
"OverNumber": [(i%120)//6 + 1 for i in range(N)],
"BallNumber": [(i%120)%6 + 1 for i in range(N)],
"RunsOffBat": np.random.choice([0,1,2,4,6], N),
"Extras": np.random.choice([0,0,0,1], N, p=[0.88,0.05,0.05,0.02]),
"IsWicket": (np.random.random(N) < 0.05).astype(int),
"BowlerName": np.random.choice(["Bumrah","Shami","Hardik","Ashwin"], N),
"BatterName": np.random.choice(["Rohit","Kohli","Gill","Dhoni"], N),
"BattingTeam": np.random.choice(["MI","CSK","RCB","KKR"], N),
})
con.register("raw_deliveries", raw_deliveries)
# ── stg_ipl_deliveries: rename, type cast, no joins ───────────────────────────
stg_df = con.execute("""
SELECT
CAST(DeliveryID AS BIGINT) AS delivery_id,
CAST(MatchID AS INTEGER) AS match_id,
CAST(OverNumber AS INTEGER) AS over_number,
CAST(BallNumber AS INTEGER) AS ball_number,
CAST(RunsOffBat AS INTEGER) AS runs_off_bat,
CAST(Extras AS INTEGER) AS extras,
CAST(IsWicket AS BOOLEAN) AS is_wicket,
BowlerName AS bowler,
BatterName AS batter,
BattingTeam AS batting_team
FROM raw_deliveries
WHERE DeliveryID IS NOT NULL
""").df()
con.register("stg_ipl_deliveries", stg_df)
# ── int_delivery_enriched: add phase, is_boundary, total_runs ─────────────────
int_df = con.execute("""
SELECT *,
CASE
WHEN over_number <= 6 THEN 'powerplay'
WHEN over_number <= 15 THEN 'middle'
ELSE 'death'
END AS phase,
CAST(runs_off_bat >= 4 AS BOOLEAN) AS is_boundary,
runs_off_bat + extras AS total_runs
FROM stg_ipl_deliveries
""").df()
con.register("int_delivery_enriched", int_df)
# ── mart_bowler_season_stats: Gold mart ───────────────────────────────────────
mart_df = con.execute("""
SELECT
bowler,
COUNT(*) AS deliveries,
SUM(runs_off_bat) AS runs_conceded,
SUM(CAST(is_wicket AS INTEGER)) AS wickets,
SUM(CAST(is_boundary AS INTEGER)) AS boundaries,
ROUND(SUM(runs_off_bat)/NULLIF(COUNT(*)/6.0,0),2) AS economy_rate
FROM int_delivery_enriched
GROUP BY bowler
ORDER BY economy_rate
""").df()
con.register("mart_bowler_season_stats", mart_df)
# ── dbt schema.yml test equivalents ──────────────────────────────────────────
def run_dbt_tests(con, model_name: str, df: pd.DataFrame) -> dict:
results = {}
# not_null: delivery_id
results["not_null:delivery_id"] = df["delivery_id"].notna().all() if "delivery_id" in df.columns else None
# unique: delivery_id
results["unique:delivery_id"] = not df["delivery_id"].duplicated().any() if "delivery_id" in df.columns else None
# accepted_values: phase
if "phase" in df.columns:
results["accepted_values:phase"] = set(df["phase"].unique()).issubset({"powerplay","middle","death"})
# between: runs_off_bat in [0,6]
if "runs_off_bat" in df.columns:
results["between:runs_off_bat"] = df["runs_off_bat"].between(0, 6).all()
return results
test_results = run_dbt_tests(con, "int_delivery_enriched", int_df)
print("dbt test results:")
all_pass = True
for test, result in test_results.items():
icon = "✓" if result else "✗"
print(f" {icon} {test}: {result}")
if not result: all_pass = False
assert all_pass
# Row conservation: staging count == intermediate count == gold deliveries sum
assert len(stg_df) == len(int_df)
assert int(mart_df["deliveries"].sum()) == len(stg_df)
print(f"\nRow conservation: stg={len(stg_df)} = int={len(int_df)} = gold.sum={int(mart_df['deliveries'].sum())} ✓")
# Phase proportion test (6/20=30% powerplay, 9/20=45% middle, 5/20=25% death)
phase_counts = int_df["phase"].value_counts(normalize=True)
assert 0.28 <= phase_counts["powerplay"] <= 0.32, f"Powerplay %: {phase_counts['powerplay']:.2%}"
assert 0.43 <= phase_counts["middle"] <= 0.47, f"Middle %: {phase_counts['middle']:.2%}"
print(f"Phase proportions: {phase_counts.to_dict()} ✓")
print("Step 1 ✓: model hierarchy, tests, and row conservation complete")Step 2 — Snapshots and Semantic Layer Metrics
Simulate a dbt Snapshot on the player dimension: run initial load, apply a team change for one player, run the snapshot again, and verify the SCD Type 2 history shows two records with correct valid_from and valid_to dates. Then verify the Semantic Layer economy_rate metric correctly computes by aggregating the numerator and denominator separately at the bowler-season grain, and assert that averaging pre-computed per-match economy rates produces a different (incorrect) result than the correct ratio computation.
Analogy🏏Cricket
🏏 Think of it like cricket: A dbt Snapshot is the automated squad-register keeper. Just as it records Hardik Pandya's GT spell with an end date and opens a fresh MI record the moment he transfers — leaving a clean two-row history — the snapshot run detects the team change and closes the old version while inserting the new, so historical deliveries stay linked to the team he actually bowled for. The Semantic Layer economy_rate is the official formula everyone must use. Just as you compute a bowler's tournament economy by summing all runs conceded and dividing by total overs bowled — never by averaging his per-match economies, which wrongly weights a two-over cameo the same as a four-over spell — the correct metric aggregates numerator and denominator separately at the bowler-season grain. Asserting that the averaged-rates figure differs from the aggregated ratio proves the trap is real. The payoff: one canonical, ratio-correct statistic that every dashboard and ranking agrees on.
python
# exercise_dbt_project.py — Step 2: Snapshots and Semantic Layer metrics
import duckdb
import pandas as pd
import numpy as np
from datetime import datetime, timezone
# ── dbt Snapshot: SCD Type 2 for dim_player ───────────────────────────────────
con.execute("""
CREATE TABLE dim_player_src (
player_id VARCHAR, player_name VARCHAR, current_team VARCHAR,
updated_at TIMESTAMPTZ
);
INSERT INTO dim_player_src VALUES
('IPL-HP-2015', 'Hardik Pandya', 'GT', '2022-01-01 00:00:00+00'),
('IPL-BUJ-2008','Jasprit Bumrah','MI', '2016-01-01 00:00:00+00');
CREATE TABLE snap_dim_player (
player_id VARCHAR, player_name VARCHAR, current_team VARCHAR, updated_at TIMESTAMPTZ,
dbt_valid_from TIMESTAMPTZ, dbt_valid_to TIMESTAMPTZ, dbt_is_current BOOLEAN
);
""")
def snapshot_run(con, run_ts):
src = con.execute("SELECT * FROM dim_player_src").df()
snap = con.execute("SELECT * FROM snap_dim_player WHERE dbt_is_current").df()
for _, row in src.iterrows():
ex = snap[snap["player_id"]==row["player_id"]]
if len(ex)==0:
con.execute("INSERT INTO snap_dim_player VALUES (?,?,?,?,?,NULL,TRUE)",
[row["player_id"],row["player_name"],row["current_team"],
row["updated_at"], run_ts.isoformat()])
elif pd.Timestamp(ex.iloc[0]["updated_at"]) < pd.Timestamp(row["updated_at"]):
con.execute("UPDATE snap_dim_player SET dbt_valid_to=?,dbt_is_current=FALSE WHERE player_id=? AND dbt_is_current",
[run_ts.isoformat(), row["player_id"]])
con.execute("INSERT INTO snap_dim_player VALUES (?,?,?,?,?,NULL,TRUE)",
[row["player_id"],row["player_name"],row["current_team"],
row["updated_at"], run_ts.isoformat()])
snapshot_run(con, datetime(2024,1,1,tzinfo=timezone.utc))
con.execute("UPDATE dim_player_src SET current_team='MI', updated_at='2024-04-01 00:00:00+00' WHERE player_id='IPL-HP-2015'")
snapshot_run(con, datetime(2024,4,2,tzinfo=timezone.utc))
hist = con.execute("SELECT player_id, current_team, dbt_valid_from, dbt_valid_to, dbt_is_current FROM snap_dim_player WHERE player_id='IPL-HP-2015' ORDER BY dbt_valid_from").df()
assert len(hist)==2
assert hist.iloc[0]["current_team"]=="GT"
assert hist.iloc[1]["current_team"]=="MI"
print("dbt Snapshot SCD Type 2:")
print(hist.to_string(index=False))
# ── Semantic Layer: ratio metric correctness ──────────────────────────────────
# Generate per-match stats with intentionally unequal delivery counts
np.random.seed(42)
matches = []
for match_id in range(10001, 10011):
for bowler in ["Bumrah","Shami"]:
n = np.random.randint(18, 30) # unequal delivery counts per match
np.random.seed(match_id + ord(bowler[0]))
matches.append({
"bowler": bowler,
"match_id": match_id,
"deliveries": n,
"runs": int(np.random.randint(20, 50)),
})
df_m = pd.DataFrame(matches)
df_m["per_match_economy"] = (df_m["runs"] / (df_m["deliveries"] / 6)).round(2)
con.register("match_stats", df_m)
# CORRECT: ratio metric — aggregate numerator and denominator separately
correct = con.execute("""
SELECT bowler,
ROUND(SUM(runs) / NULLIF(SUM(deliveries)/6.0, 0), 2) AS correct_economy
FROM match_stats GROUP BY bowler ORDER BY bowler
""").df()
# INCORRECT: averaging pre-computed per-match economy rates
incorrect = con.execute("""
SELECT bowler, ROUND(AVG(per_match_economy), 2) AS wrong_economy
FROM match_stats GROUP BY bowler ORDER BY bowler
""").df()
result = correct.merge(incorrect, on="bowler")
result["differ"] = result["correct_economy"] != result["wrong_economy"]
print("\nRatio metric correctness (economy_rate):")
print(result.to_string(index=False))
assert result["differ"].any(), "Correct and incorrect methods should differ!"
print(f"\nRatio metric is correct (averages differ from aggregated ratio) ✓")
print("Step 2 ✓: dbt Snapshot and Semantic Layer metric correctness complete")