The Mental Model
The capstone combines every course idea into one trusted analytics layer. You will design the models, tests, metric specs, semantic objects, and lineage map.
This is not about memorizing commands. It is about showing you can reason from raw records to trusted business answers.
Dataset reference: ecommerce tables and grain assumptions.
Deliver evidence for a trusted revenue model
Use three orders: completed order 101 for 100 with a refund of 20, completed order 102 for 50 without a refund, and cancelled order 103 for 90. Under the teaching metric contract, net revenue is 130.
| Deliverable | Evidence to provide |
|---|---|
| Models | Staging, refund aggregation, order fact, and customer dimension with stated grain |
| Tests | Key uniqueness, non-null keys, relationships, and the 130 revenue fixture |
| Operations | Freshness objective, owner, retry behavior, and backfill procedure |
| Consumers | One metric contract used by a dashboard and an API |
Introduce a duplicated refund, a late correction, and a missing customer in separate runs. Show which check catches each defect and how you recover without changing unrelated dates. Keep the source snapshot, model revision, test results, and reconciliation together.
Acceptance check: another engineer can reproduce 130, explain every exclusion, and restore the expected result after each injected data-quality failure.
Interactive Check
Question: What should be the final proof that your analytics layer is trustworthy?
Reveal the answer
You should be able to explain the grain, tests, metric definitions, owners, freshness expectations, and lineage from source to consumer.
Practice: Trusted Analytics Layer Design
Create a complete design worksheet for ecommerce analytics.
Use the guided lab below to record your result, assumptions, and the check that would catch an incorrect result.