This exercise builds Stage 4 and Stage 5 of the capstone pipeline: the Superset-style BI dashboard with row-level security and the governance masking layer. Building on the Gold mart from Lesson 33, you will create a virtual dataset, apply RLS filters scoped to two franchise roles, verify non-overlap and conservation properties, implement column masking for sensitive player contact data, and validate RBAC access control across four role tiers.
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 virtual dataset from the Gold mart, applies RLS for MI and GT franchise analysts, and verifies the three core RLS properties: single-team scoping, non-overlap, and conservation. Step 2 applies column masking to a player contact dataset for four roles and validates RBAC access control rules across staging, analytics, and mart-specific permission boundaries — completing the full governance layer for the capstone platform.
Step 1 — Virtual Dataset and RLS Validation
Build the Superset-style virtual dataset from the Gold mart joined with team information, apply RLS filters for MI analyst and GT analyst roles, and verify: each role sees only their team's bowler statistics, the MI and GT result sets share no overlapping bowler-season records, and the sum of MI rows, GT rows, and other-team rows equals the full Gold mart row count. Assert an admin role bypasses RLS and sees all records.
Analogy🏏Cricket
🏏 Think of it like cricket: The virtual dataset over the Gold mart is the shared franchise stats portal, and RLS is the credential that confines each analyst to their own squad. Just as an MI analyst and a GT analyst open the identical bowler-statistics dashboard but each sees only their own team's bowler-season rows — enforced by the access token they carry, not by separate dashboards — the RLS filter wraps the query with current_team set to the user's franchise. The three checks are the referee's reconciliation of a shared scorecard: single-team scoping (every MI row really is MI), non-overlap (no bowler-season record appears in both the MI and GT views), and conservation (MI plus GT plus all other teams' rows sum back to the full Gold mart count, so nothing silently disappeared). An admin, like the head statistician, bypasses the filter and sees every record. The payoff: one governed dataset serves every franchise with provably isolated, complete views.
python
# capstone_dashboard_rls.py — Step 1: Virtual dataset and RLS validation
import duckdb
import pandas as pd
import numpy as np
np.random.seed(45)
# Extend gold_df with team association (reusing from Lesson 33)
gold_with_team = gold_df.copy()
team_map = {"Hardik Pandya": "MI", "Jasprit Bumrah": "MI", "Mohammed Shami": "PBKS"}
gold_with_team["current_team"] = gold_with_team["bowler_name"].map(team_map).fillna("GT")
# Add some GT bowlers for RLS testing
extra_gt = pd.DataFrame({
"bowler_name": ["Rashid Khan", "Mohammed Shami"],
"season": [2024, 2024],
"deliveries": [80, 90],
"runs_conceded": [95, 110],
"wickets": [5, 4],
"economy_rate": [7.1, 7.3],
"current_team": ["GT", "GT"],
})
gold_with_team = pd.concat([gold_with_team, extra_gt], ignore_index=True)
con.register("gold_with_team", gold_with_team)
def run_with_rls(con, user_team: str = None) -> pd.DataFrame:
base = "SELECT bowler_name, season, deliveries, economy_rate, current_team FROM gold_with_team"
if user_team:
return con.execute(f"SELECT * FROM ({base}) v WHERE current_team = '{user_team}'").df()
return con.execute(base).df()
mi_view = run_with_rls(con, "MI")
gt_view = run_with_rls(con, "GT")
admin_view = run_with_rls(con, None)
# Property 1: single-team scoping
assert (mi_view["current_team"] == "MI").all()
assert (gt_view["current_team"] == "GT").all()
print(f"MI analyst sees {len(mi_view)} rows (all MI) ✓")
print(f"GT analyst sees {len(gt_view)} rows (all GT) ✓")
# Property 2: non-overlap
mi_keys = set(zip(mi_view["bowler_name"], mi_view["season"]))
gt_keys = set(zip(gt_view["bowler_name"], gt_view["season"]))
assert mi_keys.isdisjoint(gt_keys)
print("No overlap between MI and GT result sets ✓")
# Property 3: conservation
other_view = admin_view[~admin_view["current_team"].isin(["MI","GT"])]
total_check = len(mi_view) + len(gt_view) + len(other_view)
assert total_check == len(admin_view)
print(f"Conservation: MI({len(mi_view)})+GT({len(gt_view)})+other({len(other_view)})={total_check} == total({len(admin_view)}) ✓")
print("Step 1 ✓: Virtual dataset and RLS validation complete")Step 2 — Column Masking and RBAC Validation
Apply column masking to a player contact dataset for four roles (ADMIN, ANALYST, FINANCE, PUBLIC), verify each role's masked output matches the expected visibility pattern, and run RBAC access control checks across the staging, analytics, and mart-level permission boundaries established in Module 5. Finally run the full capstone integration test, asserting all correctness invariants from Lessons 32 through 34 pass simultaneously.
Analogy🏏Cricket
🏏 Think of it like cricket: This step locks down the sensitive stuff and proves the whole system holds together. Column masking is the tiered access to a player's private contact card: just as an ADMIN sees the real email and contract, FINANCE sees the contract but masked contact, and an ANALYST or PUBLIC user sees only initials and a hidden contract, each of the four roles gets a differently scoped view of the same rows. RBAC is the ground-access policy — just as a net bowler is allowed into the practice area but not the match-officials' room, DATA_ENGINEER_ROLE may write to staging and analytics while ANALYST_ROLE is refused staging entirely and BI_SERVICE_ROLE is confined to the published mart. The closing full integration test is the pre-tournament dress rehearsal: just as officials verify the scoreboard, the DRS, the access passes and the data feed all work together before the first ball, every invariant from Lessons 32 through 34 is asserted at once. The payoff: a governed, end-to-end-verified capstone platform.
python
# capstone_dashboard_rls.py — Step 2: Masking, RBAC, full integration test
import pandas as pd
# ── Column masking ─────────────────────────────────────────────────────────────
player_contacts = pd.DataFrame({
"player_name": ["Hardik Pandya", "Jasprit Bumrah"],
"email": ["[email protected]", "[email protected]"],
"contract_value":[15.5, 18.0],
})
def mask_email(v, role): return v if role in ("ADMIN",) else f"{v[0]}***@{v.split('@')[1]}"
def mask_contract(v, role): return v if role in ("ADMIN","FINANCE") else -1
for role in ["ADMIN","ANALYST","FINANCE","PUBLIC"]:
masked = player_contacts.copy()
masked["email"] = masked["email"].apply(lambda v: mask_email(v, role))
masked["contract"] = masked["contract_value"].apply(lambda v: mask_contract(v, role))
visible_email = role == "ADMIN"
visible_contract= role in ("ADMIN","FINANCE")
print(f" {role:<10}: email_visible={visible_email}, contract_visible={visible_contract}")
assert mask_email("[email protected]", "ADMIN") == "[email protected]"
assert mask_email("[email protected]", "ANALYST") == "h***@ipl.com"
assert mask_contract(15.5, "FINANCE") == 15.5
assert mask_contract(15.5, "PUBLIC") == -1
print("Column masking assertions passed ✓")
# ── RBAC validation ────────────────────────────────────────────────────────────
ROLE_PERMS = {
"DATA_ENGINEER_ROLE": {"schemas":["staging","analytics"], "ops":["SELECT","INSERT"]},
"ANALYST_ROLE": {"schemas":["analytics"], "ops":["SELECT"]},
"BI_SERVICE_ROLE": {"schemas":["analytics"], "ops":["SELECT"],
"tables":["mart_bowler_season_stats"]},
}
def check_access(role, schema, table, op):
p = ROLE_PERMS.get(role, {})
if schema not in p.get("schemas",[]): return False
if op not in p.get("ops",[]): return False
if "tables" in p and table not in p["tables"]: return False
return True
rbac_tests = [
("DATA_ENGINEER_ROLE","staging","raw_deliveries","INSERT", True),
("ANALYST_ROLE","analytics","fact_ipl_delivery","SELECT", True),
("ANALYST_ROLE","staging","raw_deliveries","SELECT", False),
("BI_SERVICE_ROLE","analytics","mart_bowler_season_stats","SELECT", True),
("BI_SERVICE_ROLE","analytics","fact_ipl_delivery","SELECT", False),
]
for role, schema, table, op, expected in rbac_tests:
assert check_access(role, schema, table, op) == expected
print("RBAC access control assertions passed ✓")
# ── Full Capstone Integration Test ────────────────────────────────────────────
print("\n" + "="*60)
print("Course 6 Capstone — Full Integration Test")
print("="*60)
final_assertions = [
("Stage 1: SCD Type 2 point-in-time correct", team_2023=="GT" and team_2024=="MI"),
("Stage 2: COPY INTO idempotent", r1["status"]=="LOADED" and r2["status"]=="SKIPPED"),
("Stage 2: Stream/Task MERGE idempotent", processed1==120 and processed2==0),
("Stage 2: Fact table row count correct", final_count==240),
("Stage 3: Schema tests passed", all_pass),
("Stage 3: Row conservation (fact=stg=int)", fact_count==len(stg_df)==len(int_df)),
("Stage 3: Ratio metric correctness verified", comparison["differs"].any()),
("Stage 4: RLS single-team scoping", (mi_view["current_team"]=="MI").all()),
("Stage 4: RLS non-overlap", mi_keys.isdisjoint(gt_keys)),
("Stage 4: RLS conservation", total_check==len(admin_view)),
("Stage 5: Column masking correct", mask_contract(15.5,"PUBLIC")==-1),
("Stage 5: RBAC access control correct", check_access("ANALYST_ROLE","staging","x","SELECT")==False),
]
all_ok = True
for name, ok in final_assertions:
print(f" {'✓' if ok else '✗'} {name}")
if not ok: all_ok = False
print()
if all_ok:
print(f"ALL {len(final_assertions)} ASSERTIONS PASSED — Capstone is submission-ready ✓")
else:
raise AssertionError("Some capstone assertions failed")
print("Step 2 ✓: Column masking, RBAC, and full integration test complete")