offdataai Learn · Intermediate

Project: Brewly, end to end

Take one coffee-subscription business from its brief to an AI-ready data product: concepts, source model, star schema, history, snapshot, metrics, ontology and guardrails.

Start learning for $1/month

9 lessons · about 120 minutes · included in Explorer

End-to-end projectIntermediate9 lessons · about 120 minutes

Project: Brewly, end to end

Brewly roasts specialty coffee and sells it two ways: subscriptions (a bag every week or every month, on one of three plans) and one-off orders from its web shop. It has 40,000 customers and a small team that keeps asking questions nobody can answer quickly:

Every step continues your own work from the last: concepts, source model, star schema, history, snapshot, metrics, ontology and guardrails.

Outcome: You can carry a real business case from requirements to a governed, agent-ready data model and explain every decision.

1
Meet Brewly: name the business concepts
Before any table design, agree what the business is made of.
Build concepts and relationships · 12 min · instant checks
2
Model Brewly's source system
Fix the order table that can only hold one coffee, and connect every table.
Build it in the Model Builder · 14 min · instant checks
3
Design Brewly's star schema
Build the order-line fact and its dimensions on top of your source model.
Build it in the Model Builder · 16 min · instant checks
4
Add history and a second date
Keep revenue by city true when customers move, and measure shipping time.
Build it in the Model Builder · 12 min · instant checks
5
Model subscriptions as a monthly snapshot
MRR and churn aren't events. Capture the state of every subscription each month.
Build it in the Model Builder · 14 min · instant checks
6
Define Brewly's semantic layer
Turn your star into the governed metrics every dashboard and agent will share.
Define governed metrics · 14 min · instant checks
7
Map Brewly's ontology onto your model
Your step 1 concepts come back. Now they point at the analytics model.
Build concepts and relationships · 12 min · instant checks
8
Make Brewly's model safe for AI agents
Move personal data where the assistant can't reach it, without losing anything.
Build it in the Model Builder · 12 min · instant checks
9
Ship it: answer Brewly's questions
Put your model, metrics and ontology together and answer the team's real questions.
Knowledge check · 12 min · instant checks

What you will be able to do

✓Turn a business brief into named concepts
✓Map each concept to the source table that holds it
✓Connect concepts with meaningful verbs
✓Spot a table that breaks first normal form in a real schema
✓Resolve orders and products with an associative table
✓Connect a source model with its foreign keys and mark personal data
✓Declare the grain of Brewly's sales fact
✓Derive conformed dimensions from the source tables
✓Connect the fact to every dimension with surrogate keys
✓Decide which customer attributes need history
✓Turn dim_customer into a Type 2 dimension
✓Role-play dim_date for order and ship dates
✓Tell an event fact from a periodic snapshot
✓Design a monthly subscription snapshot for MRR and churn
✓Reuse conformed dimensions across two facts
✓Define event metrics on the order fact
✓Define semi-additive metrics on the snapshot
✓Limit each metric to the dimensions it can truly be sliced by
✓Move concepts from source tables to the analytics model
✓Add the synonyms the business actually uses
✓Connect concepts into the join paths an agent will walk
✓Find personal data that leaked into the analytics model
✓Split contact details into a restricted table
✓Keep the analytics model fully joinable without PII
✓Trace a business question to the metric and join path that answer it
✓Explain how history, semi-additivity and guardrails keep answers correct
✓Present the finished data product end to end

Free reading on these topics

How to Learn Data Modeling: A Roadmap From Beginner to Data Architect

A structured learning path for data modeling, from relational basics and SQL to Kimball, Data Vault, semantic layers and ontologies, with what to practise at each stage and how to know you are ready to move on.

8 min read

What Is Grain in Data Modeling? How to Declare It and Avoid Common Mistakes

Grain is the definition of what one row in a fact table represents. How to declare it, worked examples from e-commerce, SaaS, banking and healthcare, and the mistakes that break reports.

8 min read

How to Define Metrics in a Semantic Layer: Simple, Ratio and Derived Metrics

A practical guide to metric definitions in a semantic layer. Measures vs metrics, simple, ratio and derived metrics, additivity, allowed dimensions, time grains, and a complete YAML example.

8 min read

How to Build an Ontology for Your Data: Concepts, Definitions and Relationships

A step-by-step guide to building a business ontology for a data platform. Choosing concepts, writing definitions, naming relationships as verb phrases, and mapping them to tables and columns.

8 min read

The Context Layer: Giving AI Agents the Meaning Behind Your Data

A context layer serves AI agents the meaning, metadata, rules and documents a data question needs. What goes in one and how MCP delivers it.

6 min read

Included in Explorer

Every course and every exercise for $1 a month.

Explorer also includes the Model Builder, all 174 templates and SQL for every warehouse. Cancel anytime.

Start learning for $1/month

More courses

Foundations: concepts, terms and best practices

Beginner · 6 lessons

Relational modeling (3NF)

Beginner · 4 lessons

Dimensional modeling (Kimball)

Beginner · 8 lessons

Data Vault 2.0

Intermediate · 5 lessons

Build a semantic layer

Intermediate · 5 lessons

Build an ontology

Intermediate · 5 lessons

Context layer for AI agents

Advanced · 5 lessons