This exercise builds Stage 3 of the capstone pipeline: the dbt-style model hierarchy with tests and Semantic Layer metrics. Building on the star schema and fact table from Lesson 32, you will implement staging and intermediate transformation layers, build the Gold mart with bowler season statistics, apply dbt schema test equivalents, and implement the ratio-correct economy_rate metric verified against the averaging error. The exercise also simulates dbt documentation generation by producing a model metadata manifest.
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.
The exercise covers two steps. Step 1 builds the staging and intermediate transformation layers from the fact table created in Lesson 32, joins with the SCD Type 2 player dimension using the version-specific surrogate key, and runs schema tests for not_null, unique, and accepted_values. Step 2 builds the Gold mart with the economy_rate metric, runs the averaging-error correctness test, and generates a simulated dbt manifest documenting the model lineage.
Step 1 — Staging, Intermediate Layers and Schema Tests
Build the staging model that joins `fact_ipl_delivery` with `dim_player` using the surrogate `bowler_key` to enrich with `player_name` and `bowling_style`, build the intermediate model that joins with `dim_match` and `dim_venue` for full context, and apply schema tests: not_null on `delivery_key`, unique on `delivery_key`, accepted_values on `phase`, and a relationship test verifying every `bowler_key` exists in `dim_player`. Assert all tests pass and that row count is preserved across both transformation layers.
Analogy🏏Cricket
🏏 Think of it like cricket: The staging and intermediate layers are the scorers who turn raw deliveries into a fully annotated scorecard. Just as a scorer links each ball to the bowler's name and bowling style by looking him up in the squad register, the staging model joins fact_ipl_delivery to dim_player on the version-specific surrogate bowler_key — so the enrichment reflects the team the bowler was on that day, not today. The intermediate layer then adds match and venue context, like noting the fixture and ground for every ball. The schema tests are the referee's certification checks: not_null and unique on delivery_key (every ball logged exactly once), accepted_values on phase, and a relationship test confirming every bowler_key genuinely exists in the squad register — the cricket equivalent of rejecting a delivery credited to an unregistered bowler. Asserting the row count is preserved across both layers confirms no join accidentally duplicated a ball. The payoff: an enriched, fully validated delivery record ready for aggregation.
python
# capstone_dbt_models.py — Step 1: Staging, intermediate, schema tests
import duckdb
import pandas as pd
# Reuses con, fact_ipl_delivery, dim_player, dim_match, dim_venue from Lesson 32
# ── Staging model: join fact with player dimension ────────────────────────────
stg_sql = """
SELECT
f.delivery_key, f.match_key, f.venue_key, f.bowler_key, f.batting_team_key,
f.over_number, f.phase, f.runs_scored, f.is_wicket,
p.player_name AS bowler_name, p.bowling_style, p.current_team
FROM fact_ipl_delivery f
LEFT JOIN dim_player p ON f.bowler_key = p.player_key
"""
stg_df = con.execute(stg_sql).df()
con.register("stg_delivery_enriched", stg_df)
# ── Intermediate model: join with match and venue ─────────────────────────────
int_sql = """
SELECT
s.*, m.season, m.match_date, v.venue_name, v.city
FROM stg_delivery_enriched s
LEFT JOIN dim_match m ON s.match_key = m.match_key
LEFT JOIN dim_venue v ON s.venue_key = v.venue_key
"""
int_df = con.execute(int_sql).df()
con.register("int_delivery_full", int_df)
# ── Schema tests ────────────────────────────────────────────────────────────────
def test_not_null(df, col): return df[col].notna().all()
def test_unique(df, col): return not df[col].duplicated().any()
def test_accepted_values(df, col, values): return set(df[col].unique()).issubset(values)
def test_relationship(child_df, child_col, parent_df, parent_col):
return set(child_df[child_col]).issubset(set(parent_df[parent_col]))
tests = {
"not_null:delivery_key": test_not_null(int_df, "delivery_key"),
"unique:delivery_key": test_unique(int_df, "delivery_key"),
"accepted_values:phase": test_accepted_values(int_df, "phase", {"powerplay","middle","death"}),
"relationship:bowler_key": test_relationship(int_df, "bowler_key",
con.execute("SELECT * FROM dim_player").df(), "player_key"),
"not_null:bowler_name": test_not_null(int_df, "bowler_name"),
"not_null:venue_name": test_not_null(int_df, "venue_name"),
}
print("dbt schema test results:")
all_pass = True
for name, result in tests.items():
print(f" {'✓' if result else '✗'} {name}")
if not result: all_pass = False
assert all_pass
# Row conservation across layers
fact_count = con.execute("SELECT COUNT(*) FROM fact_ipl_delivery").fetchone()[0]
assert len(stg_df) == fact_count
assert len(int_df) == fact_count
print(f"\nRow conservation: fact={fact_count} = stg={len(stg_df)} = int={len(int_df)} ✓")
print("Step 1 ✓: Staging, intermediate, and schema tests complete")Step 2 — Gold Mart, Semantic Layer Metric and Manifest
Build the Gold mart with bowler season statistics, compute the economy_rate metric using the ratio-correct pattern (aggregated numerator and denominator), verify the averaging error by comparing against a naive average-of-per-match-rates computation, and assert the two methods produce different results. Generate a simulated dbt manifest documenting the model dependency graph from staging through to the Gold mart, and verify the manifest correctly identifies all upstream dependencies for the Gold mart model.
Analogy🏏Cricket
🏏 Think of it like cricket: The Gold mart is the published season leaderboard, and this step builds it correctly. Just as a bowler's tournament economy must be computed by summing all runs conceded and dividing by total overs — never by averaging his per-match economies, which would weight a short cameo spell the same as a full quota — the economy_rate metric aggregates numerator and denominator separately, and the step proves the trap by asserting the naive average-of-per-match-rates gives a different, wrong answer. The dbt manifest is the lineage chart every statistics department keeps: just as you can trace a published season figure back through the enriched scorecard to the raw ball-by-ball log, get_full_lineage recursively resolves the Gold mart's dependencies all the way down to fact_ipl_delivery and dim_player. Confirming those upstream nodes appear is like auditing that every number on the leaderboard is traceable to certified source data. The payoff: a correct, fully documented statistic with provable provenance.
python
# capstone_dbt_models.py — Step 2: Gold mart, Semantic Layer, manifest
import pandas as pd
import numpy as np
np.random.seed(44)
# ── Gold mart: bowler season statistics ───────────────────────────────────────
gold_sql = """
SELECT
bowler_name,
season,
COUNT(*) AS deliveries,
SUM(runs_scored) AS runs_conceded,
SUM(CAST(is_wicket AS INTEGER)) AS wickets,
ROUND(SUM(runs_scored)/NULLIF(COUNT(*)/6.0,0),2) AS economy_rate
FROM int_delivery_full
GROUP BY bowler_name, season
ORDER BY economy_rate
"""
gold_df = con.execute(gold_sql).df()
print("Gold mart — bowler season stats:")
print(gold_df.to_string(index=False))
# Delivery conservation
assert int(gold_df["deliveries"].sum()) == len(int_df)
print(f"\nGold conservation: {int(gold_df['deliveries'].sum())} == {len(int_df)} ✓")
# ── Semantic Layer ratio-correctness test ─────────────────────────────────────
# Generate per-match stats with unequal delivery counts to test correctness
matches_data = []
for mid in [10001, 10002]:
for bowler in ["Hardik Pandya"]:
n = np.random.randint(15, 30)
runs = np.random.randint(15, 40)
matches_data.append({"bowler": bowler, "match_id": mid, "deliveries": n, "runs": runs})
mdf = pd.DataFrame(matches_data)
mdf["per_match_economy"] = (mdf["runs"]/(mdf["deliveries"]/6)).round(2)
con.register("match_econ", mdf)
correct_economy = con.execute(
"SELECT bowler, ROUND(SUM(runs)/NULLIF(SUM(deliveries)/6.0,0),2) AS correct FROM match_econ GROUP BY bowler"
).df()
incorrect_economy = con.execute(
"SELECT bowler, ROUND(AVG(per_match_economy),2) AS incorrect FROM match_econ GROUP BY bowler"
).df()
comparison = correct_economy.merge(incorrect_economy, on="bowler")
comparison["differs"] = comparison["correct"] != comparison["incorrect"]
print("\nSemantic Layer ratio correctness test:")
print(comparison.to_string(index=False))
assert comparison["differs"].any(), "Test data must produce different correct/incorrect results"
print("Ratio metric correctness verified ✓")
# ── dbt manifest simulation ────────────────────────────────────────────────────
MANIFEST = {
"nodes": {
"model.ipl_dw.stg_delivery_enriched": {
"depends_on": {"nodes": ["model.ipl_dw.fact_ipl_delivery", "model.ipl_dw.dim_player"]},
},
"model.ipl_dw.int_delivery_full": {
"depends_on": {"nodes": ["model.ipl_dw.stg_delivery_enriched",
"model.ipl_dw.dim_match", "model.ipl_dw.dim_venue"]},
},
"model.ipl_dw.mart_bowler_season_stats": {
"depends_on": {"nodes": ["model.ipl_dw.int_delivery_full"]},
},
}
}
def get_full_lineage(manifest: dict, model: str) -> set:
"""Recursively resolve all upstream dependencies."""
direct = set(manifest["nodes"].get(model, {}).get("depends_on", {}).get("nodes", []))
all_deps = set(direct)
for dep in direct:
all_deps |= get_full_lineage(manifest, dep)
return all_deps
lineage = get_full_lineage(MANIFEST, "model.ipl_dw.mart_bowler_season_stats")
print(f"\nGold mart full lineage: {sorted(lineage)}")
assert "model.ipl_dw.fact_ipl_delivery" in lineage
assert "model.ipl_dw.dim_player" in lineage
print("dbt manifest lineage resolution verified ✓")
print("Step 2 ✓: Gold mart, Semantic Layer, and manifest complete")