Project Brief — Full Analytics Engineering Platform — Data Warehouse & Analytics Engineering

The capstone project builds a complete production-patterned analytics engineering platform for the IPL data warehouse, integrating every module of Course 6. You will design a star schema with SCD Type 2 dimensions, implement a Snowflake-style data pipeline with COPY INTO and incremental MERGE patterns, build a three-layer dbt model hierarchy with tests and a Semantic Layer metric, create a Superset-style dashboard with row-level security, and apply data governance controls including masking and access control validation. Every concept from data warehouse fundamentals through governance appears in one coherent, end-to-end analytics platform.

The pipeline follows the standard analytics engineering pattern: a star schema with fact_ipl_delivery and four SCD-aware dimension tables, loaded incrementally with idempotent upsert logic, transformed through staging and intermediate dbt-equivalent layers into a Gold mart, exposed through a virtual dataset with role-based row-level security, and protected by column-level masking for sensitive player data. The pipeline runs idempotently end-to-end — executing it twice for the same logical date produces identical fact and mart row counts, identical SCD Type 2 dimension history, and identical RLS-filtered query results for every role.

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.

Project Specification

Architecture

The pipeline has five stages. Stage 1 — Schema Design: a star schema with `fact_ipl_delivery` (delivery grain) and SCD Type 2 `dim_player`, plus `dim_match`, `dim_venue`, `dim_team`. Stage 2 — Snowflake Load: COPY INTO-style batch loading with load history idempotency, followed by Stream/Task-style incremental MERGE. Stage 3 — dbt Models: staging, intermediate, and Gold mart layers with schema tests and a ratio-correct economy_rate metric. Stage 4 — BI Layer: a virtual dataset with RLS scoping by franchise. Stage 5 — Governance: column masking and RBAC validation.

Analogy🏏Cricket

🏏 Think of it like cricket: The capstone's five-stage pipeline is the full assembly line that turns a raw match into the polished figures on a broadcast, each stage handing a cleaner product to the next. Stage one designs the star schema — laying out the official register: a ball-by-ball fact table at delivery grain ringed by SCD-aware dimensions for players, teams, and venues, the way a scoring system is designed before a ball is bowled. The later stages load raw deliveries, transform them through tested dbt models, snapshot the slowly-changing dimensions, and finally surface governed metrics to a dashboard — each step building strictly on the last, never skipping ahead. Just as a broadcast's data operation flows from the scorer's raw entry, through validation, through the stats desk's roll-ups, to the graphic on air — and a break at any stage corrupts everything downstream — the pipeline's stages form an ordered chain where the grain declared at the start governs the correctness of every metric computed at the end. Design the register wrong and no amount of polish downstream can rescue the numbers.

Deliverables

The complete submission must include: a star schema with grain-correct fact and SCD Type 2 dimension tables; a Snowflake-style pipeline with COPY INTO idempotency and incremental MERGE; a three-layer dbt model hierarchy with passing schema tests; a ratio-correct economy_rate metric verified against the averaging-error test; a virtual BI dataset with RLS scoping for at least two franchise roles with non-overlap and conservation assertions; column masking verified across three role tiers; and a full integration test asserting all invariants simultaneously.

Analogy🏏Cricket

🏏 Think of it like cricket: The capstone's insistence that the whole pipeline be idempotent — safe to re-run without changing the result — is the same rule that lets a match's official result be re-confirmed a hundred times and always read identically. Idempotency means running the load twice loads no delivery twice, running the snapshot twice opens no duplicate player stint, and re-running the metrics recomputes the same economy rate rather than doubling it. It is achieved through MERGE-based upserts and file-tracked loads that recognise what they have already processed, the way an official result-confirmation system checks whether a match is already ratified before recording it again and simply no-ops if so. Just as a scoring system that double-counted an over every time an operator refreshed the page would make every total untrustworthy, a pipeline that is not idempotent produces different numbers on every re-run — and since real pipelines fail and get retried constantly, idempotency is not a nicety but the property that makes the whole platform's figures reproducible and therefore believable.