Capstone — Production Data Warehouse Portfolio — Data Warehouse & Analytics Engineering

This is the final submission for Course 6: Data Warehouse & Analytics Engineering. You have built a production-patterned analytics engineering platform integrating every module: data warehouse fundamentals with star schema and SCD Type 2 (Module 1); Snowflake cloud DW with COPY INTO, Streams and Tasks, and Time Travel (Module 2); analytics engineering with dbt models, snapshots, and CI/CD (Module 3); BI and visualisation with Superset, Metabase, and semantic modelling (Module 4); and data governance with quality dimensions, masking, GDPR, and RBAC (Module 5).

The submission requires the full integration test from Lesson 34 to pass with all twelve assertions verified. Before submitting, ensure you have completed all three exercise lessons in order, run the full integration test and confirmed all assertions pass, reviewed the 24-item submission checklist below, and optionally extended the pipeline with the bonus challenges from each exercise lesson. A submission where all assertions pass represents mastery of production analytics engineering at the level expected of a Senior Analytics Engineer or Data Warehouse Architect.

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.

Submission Checklist

python

# Course 6 Capstone Submission Checklist
# Verify each item before submitting

# [REQUIRED] Module 1 — DW Fundamentals
# 1.  Fact table grain is explicitly declared and consistently maintained
# 2.  dim_player implements SCD Type 2 with correct valid_from/valid_to boundaries
# 3.  Point-in-time SCD Type 2 lookup correctly resolves historical team membership
# 4.  Fact table stores version-specific surrogate keys, not natural keys

# [REQUIRED] Module 2 — Snowflake Cloud DW
# 5.  COPY INTO simulation with load history prevents duplicate file loads
# 6.  Stream/Task incremental MERGE processes zero rows when no new data exists
# 7.  Final fact table row count equals sum of all batches with zero duplication

# [REQUIRED] Module 3 — Analytics Engineering with dbt
# 8.  Staging and intermediate models preserve row count (no fanout from joins)
# 9.  Schema tests (not_null, unique, accepted_values, relationship) all pass
# 10. economy_rate metric uses aggregated ratio computation, not averaged pre-computed rates
# 11. Ratio correctness test demonstrates the averaging error with genuinely unequal test data
# 12. dbt manifest lineage resolution correctly identifies all transitive dependencies

# [REQUIRED] Module 4 — BI & Visualisation
# 13. Virtual dataset built from Gold mart with appropriate grain for dashboard charts
# 14. RLS scoping verified for at least two franchise roles (single-team scoping)
# 15. RLS non-overlap verified (no shared records between different franchise views)
# 16. RLS conservation verified (sum of all role views equals unfiltered total)

# [REQUIRED] Module 5 — Governance
# 17. Column masking verified across at least three role tiers (ADMIN, ANALYST/PUBLIC, FINANCE)
# 18. RBAC access control tests include both positive and negative access cases
# 19. DAMA quality dimensions measured and documented for at least one dataset
# 20. GDPR-style pseudonymisation preserves non-PII analytics attributes

# [REQUIRED] Integration
# 21. Full integration test asserts all 12 correctness invariants simultaneously
# 22. All assertions pass without modification or special-casing

# [RECOMMENDED] Code Quality
# 23. All pipeline functions have docstrings explaining their governance or correctness purpose
# 24. State management between exercise lessons is explicit (single script or persisted state)

print("Checklist reviewed. Submit project files to the SkillVeris capstone portal.")