Capstone brief — the CricketVerse data platform

This capstone module brings together every concept from Course 1 — SQL foundations, advanced SQL, database design, PostgreSQL internals, and NoSQL polyglot persistence — into a single end-to-end project: building the data layer for CricketVerse, a fictional national cricket analytics platform. Across the capstone exercises you will design a normalised schema, optimise its physical layout with indexes and partitioning, write the analytical queries that power the product's dashboards, and architect the polyglot persistence layer that handles the platform's real-time and historical workloads. The capstone simulates the actual scope of work a data engineer delivers when standing up a new analytical platform.

A capstone differs from the module exercises you have completed so far in one important way: the module exercises were scaffolded — each step told you exactly what to build and provided the solution code. The capstone exercises specify requirements and acceptance criteria, but leave the design and implementation decisions to you. This mirrors real engineering work, where the requirements are clear but the solution is yours to design. Reference solutions are provided in each exercise lesson, but the learning value comes from attempting the design before reading them.

By completing this capstone, you will have a portfolio-quality artifact: a complete data platform design with a normalised PostgreSQL schema, a documented index and partitioning strategy, a set of production-grade analytical queries, and a polyglot architecture decision record. This is exactly the kind of work product that demonstrates data engineering competence to employers and clients — more compelling than a certificate alone, because it shows applied skill across the full lifecycle of a data platform.

Analogy🏏Cricket

🏏 Think of it like cricket: A SELECT query is precisely how a selection committee picks a playing XI. FROM is the full list of centrally contracted players — the raw pool. WHERE is the fitness and eligibility screen: injured or unavailable players are removed before anyone debates merit, and the fewer names that survive this screen, the faster the meeting goes — exactly why a good WHERE clause matters more than anything downstream. ORDER BY is ranking the survivors by recent form, then by experience as the tiebreaker. LIMIT 11 takes the top of that ranked list and stops. The committee never ranks the entire national player pool and then discards thousands of names — and neither should your query force the database to sort millions of rows it will immediately throw away. The order of operations is the whole game: filter first, sort what remains, take only what you need.

The CricketVerse Platform Requirements

Business Context

CricketVerse is a national cricket analytics platform serving three user types: fans (who want live scores, player profiles, and leaderboards), analysts (who want deep statistical queries across decades of data), and broadcasters (who need real-time data feeds during live matches). The platform tracks every ball bowled in every match across all formats (Test, ODI, T20) and all levels (international, IPL, domestic), going back 20 years. At peak — during a major tournament like the IPL or a World Cup — the platform ingests delivery events from up to 10 simultaneous live matches at a combined rate of approximately 100 deliveries per second, while serving hundreds of thousands of concurrent dashboard users.

Analogy🏏Cricket

🏏 Think of it like cricket: The CricketVerse brief is the ground you are being asked to build, and its shape dictates everything downstream. Just as a stadium must serve three very different crowds at once — casual fans wanting the live score, analysts poring over decades of statistics, and broadcasters needing an instant feed for the commentary box — the platform serves fans, analysts and broadcasters, and each audience pulls the design a different way. Just as the game itself nests neatly — countries field teams, teams field players, players appear in matches, matches split into innings, innings into individual deliveries — the data has a natural hierarchy a normalised schema must mirror exactly. Just as a World Cup night strains a ground far beyond a quiet club fixture, peak load means ingesting around 100 deliveries per second from up to ten simultaneous live matches while hundreds of thousands of dashboard users watch — and twenty years of every-ball history swells the delivery table to hundreds of millions of rows. The payoff: knowing who you serve and how the game is structured is what turns a vague 'analytics platform' into concrete schema, indexing and scaling decisions.

The platform's data has natural structure: countries contain teams, teams field players, players appear in matches, matches are hosted at venues and belong to tournaments and seasons, and each match consists of innings, which consist of deliveries. This hierarchy must be modelled correctly in a normalised relational schema while remaining performant for the analytical queries that drive the product. The delivery-level data is the largest and fastest-growing dataset — 20 years of every-ball data across all matches amounts to hundreds of millions of delivery records.

Functional Requirements

The platform must support these analytical capabilities: career batting and bowling statistics per player across formats; head-to-head records between any two players (how a specific batter performs against a specific bowler); venue analysis (average first-innings score, win rates batting first vs second); season leaderboards updated in real time during live matches; partnership analysis (runs scored by pairs of batters); and form analysis (a player's rolling average over their last N innings using window functions). Each capability maps to specific SQL techniques from the course.

Analogy🏏Cricket

🏏 Think of it like cricket: Before a Test match starts, the umpires and both captains agree on a rulebook of what must happen during play — DRS reviews must be available to each side, the over-rate must be tracked ball by ball, and a follow-on decision must trigger automatically once the first-innings deficit crosses 200 runs. Just as that rulebook spells out exactly which actions the umpires must perform and when, a functional requirement spells out exactly what the system must do — ingest every delivery event, let a fan search a bowler's economy rate, trigger a follow-on alert. Just as the captains don't debate how fast the scoreboard operator types, only that the scoreboard is updated after every ball, functional requirements describe observable behavior, not internal implementation. Just as leaving out "must recalculate Duckworth-Lewis targets when rain interrupts play" would leave the umpires improvising mid-match, an incomplete functional requirement leaves developers guessing what the system should do under real conditions. The insight: a functional requirement is the rulebook's "what," written precisely enough that anyone building the system knows exactly which behavior to implement, not just the goal it should achieve.

The platform must also meet non-functional requirements: dashboard leaderboard reads must return in under 50 milliseconds even during peak load; the delivery event ingestion must sustain 100+ writes per second without dropping events; career statistics queries must complete in under 1 second; and the historical data (older than 2 years) must be archivable to cheaper storage without affecting query performance on recent data. These non-functional requirements drive the physical design decisions: indexing, partitioning, caching, and polyglot persistence.

Analogy🏏Cricket

🏏 Think of it like cricket: Functional versus non-functional requirements is the difference between what shots the team must play and the conditions under which they must play them. 'Show career statistics and head-to-head records' is functional — the shot itself; any competent schema can play it eventually. 'Leaderboard reads under 50 milliseconds during peak load' is non-functional — that same shot executed in the final over of a packed World Cup night: the constraint isn't what, but how fast, how reliably, at what scale. Non-functional requirements are what actually drive architecture, the way conditions drive selection: a docile pitch lets you pick anyone, but a 100-events-per-second ingestion rate, like a vicious turning track, ruthlessly eliminates most options and forces the specialist choices — Redis for the sub-50ms reads, partitioning for the archive scans. Read the NFRs first; they pick half your team before skill even enters the conversation.

Capstone Structure

The capstone is delivered across three exercise lessons and one final project. Exercise 1 (Schema Design) asks you to design the complete normalised PostgreSQL schema with appropriate constraints, then add the physical design (indexes and partitioning) that meets the non-functional requirements. Exercise 2 (Analytical Queries) asks you to write the production queries that power each functional requirement, applying joins, aggregations, window functions, and CTEs. Exercise 3 (Polyglot Architecture) asks you to design the multi-database architecture that handles the real-time and historical workloads, with an explicit decision record. The final project integrates all three into a complete, documented platform deliverable.

Analogy🏏Cricket

🏏 Think of it like cricket: The capstone is structured like a tour with three warm-up fixtures building to one Test match. Just as the first fixture drills the fundamentals of batting order and field placements, Exercise 1 (Schema Design) has you lay out the normalised PostgreSQL schema with its constraints, then add the physical design — indexes and partitioning — that meets the non-functional requirements. Just as the second fixture is about scoring runs against real bowling, Exercise 2 (Analytical Queries) has you write the production queries that power each feature, wielding joins, aggregations, window functions and CTEs. Just as the third fixture tests adapting to conditions and picking the right specialists, Exercise 3 (Polyglot Architecture) has you design the multi-database setup for the real-time and historical workloads, with an explicit decision record justifying each choice. Just as the Test match brings every skill together over five days, the final project integrates all three into one complete, documented platform. The payoff: each exercise rehearses one discipline in isolation so the capstone can assemble them into a coherent, defensible whole.

The requirements summary above is your reference card for the entire capstone. Keep it open as you work through the three exercises — each entity, relationship, functional requirement, and non-functional requirement maps to specific work you will do. The entities and relationships drive Exercise 1's schema design. The functional requirements drive Exercise 2's analytical queries. The non-functional requirements drive both the physical design in Exercise 1 and the polyglot architecture in Exercise 3.

Notice how the non-functional requirements map directly to course concepts: NFR1 (sub-50ms leaderboard) points to the Redis sorted set pattern from Module 5; NFR2 (100+ writes/sec ingestion) points to partitioning from Module 3 or Cassandra from Module 5; NFR3 (sub-1s career stats) points to materialised views from Module 3 and indexing from Module 4; NFR4 (archive old data) points to range partitioning with DETACH PARTITION from Module 3. This explicit mapping from requirement to technique is the core skill the capstone develops.

How to Approach the Capstone

Attempt each exercise's design before reading its reference solution. The reference solutions show one correct approach, but there are always multiple valid designs — your design may differ from the reference and still be correct. The value of the capstone comes from grappling with the design decisions yourself: which columns belong in the partition key, whether to embed or reference, where a materialised view pays off, when to reach for Redis versus a PostgreSQL index. Reading the solution without attempting the design first reduces the capstone to passive reading rather than active skill-building.

Document your decisions as you make them. For each design choice, write a one-sentence justification: 'Partitioned deliveries by match_date because NFR4 requires archiving data older than 2 years, and DETACH PARTITION makes this instant.' This documentation habit is what separates senior engineers from juniors — the ability to explain not just what was built, but why. The final project explicitly requires a decision record, so building it incrementally as you work through the exercises makes the final assembly straightforward.

Test your work against the acceptance criteria. After each exercise, verify: is the schema in 3NF (no transitive or partial dependencies)? Does every foreign key have an index? Does each query return correct results on the test dataset? Does the architecture decision record explicitly state the trade-offs? Testing against explicit acceptance criteria — rather than assuming the work is correct because it runs without error — is the engineering discipline that catches design flaws before they reach production.

Analogy🏏Cricket

🏏 Think of it like cricket: The capstone working method is how professionals prepare for a big series. Attempting your own design before reading the reference solution is shadow batting before watching the great players' footage: struggle first, and the comparison teaches; peek first, and you merely memorise someone else's footwork. Documenting each decision — 'partitioned deliveries by match_date because NFR3 demands archive scans stay fast' — is the captain's habit of stating a plan aloud before the over, so when it fails you know which assumption broke, and when it works you can repeat it deliberately. And testing against the acceptance criteria after each exercise is the fitness beep test: objective, pass or fail, no vibes — is the schema in 3NF, does every foreign key have its index, does the leaderboard actually return in 50ms? A design that 'feels solid' but fails the criteria is a batter who looks elegant in the nets and averages twelve.