How this track works
Ten sessions, one marketplace. Bazaar is a multi-merchant ecommerce platform: 60 shoppers, 12 merchants, 60 products, 90 days of orders, carts and payment attempts. Over this track you will design its transactional schema from scratch (b1-b4), work out why analysts cannot query it (b5), build the dimensions and facts they can query (b6-b7), answer four real business questions with the result (b8), publish the model so an AI agent can query it accurately (b9), and extend the whole thing to a new subject area in the capstone (b10).
Everything is real code you keep. The sql/ folder in this repo holds the DDL, the transform, the marts and the agent views; python/ holds the generator, the build script, the validator, the analysis and the agent scripts; semantic/ holds the schema card, the contract and the golden question set. Each page tells you which files it ships.
Conceptual, logical, physical 6 min live
"Data model" means three different artifacts, and conflating them is why modeling conversations go badly. The conceptual model is the things the business has and how they relate - no columns, no types, readable by a merchant. The logical model adds attributes, keys and cardinality - readable by an analyst. The physical model is DDL for one specific engine - types, constraints, indexes. You go down that ladder once per subject area, and every session in b1-b4 is one rung.
LiveWhy the conceptual model is not optional3 min▶
The temptation is to skip straight to tables, because tables feel like progress. What you get is a schema shaped like whatever export you happened to receive - a "sales" table that is really orders and payments jammed together, a "customer" column that is sometimes an email and sometimes a name. Those choices are cheap to make and expensive to unmake, because every query, dashboard and pipeline built on top of them encodes the mistake.
- Entities are nouns the business says out loud: shopper, merchant, product, cart, order, payment. If you have to explain a table's name to a merchant, it is probably not an entity.
- Relationships carry the verbs: a shopper places orders, a cart holds products, an order receives payment attempts. The verb tells you the cardinality.
- Ubiquitous language: use the business's word, not the engineer's. Bazaar's people say "merchant", so nothing in the schema says "vendor", "seller" or "partner". One concept, one word, everywhere - this is the naming discipline session a5 makes a standard.
The table that was two entities. A marketplace stored orders and payments in one transactions table because the payment provider's export had them that way. Every revenue number was then off by however many customers had retried a failed card - and nobody could find the bug, because the table's name made the double-count look like the point of the table. Two entities, two grains, two tables. The export was not a model.
Self-studyEntity, attribute, or relationship?3 min read▶
Three questions settle almost every case:
- Does it have its own identity and its own lifecycle? A cart is created, changed and abandoned independently of any order. Entity.
- Does it only describe something else? A shipping city describes a shopper. Attribute. (Until the business starts managing cities as things in their own right - then it becomes an entity, and that is a real modeling event, not a mistake.)
- Does it exist only because two things met? "This product, in this cart, in this quantity" has no meaning without both. Relationship - and if it carries its own attributes (quantity, added time), it becomes an associative table, which session b3 covers.
Bazaar's ambiguous case is price. A product has a list price (attribute of product), and an order line has the price actually charged (attribute of the line, captured at time of sale). Those are two different facts that happen to share a word. Storing only one of them is the single most common way to lose the ability to answer "what did we charge for this last March".
Bazaar's eight entities 6 min live
Here is the conceptual model you will spend b2-b4 turning into a real schema. Eight entities, six relationships. Read the verbs on the connectors - each one is a cardinality decision you will make explicit in session b3.
| Entity | One row is | Why it is separate |
|---|---|---|
| users | one registered shopper | identity that outlives any order |
| merchants | one selling merchant | the thing "which merchant dropped" is about |
| products | one sellable product, owned by one merchant | exists before and after any sale |
| carts | one cart | demand that did not become revenue still needs somewhere to live |
| cart_items | one product in one cart | resolves the many-to-many, and carries quantity |
| orders | one order header: who, when, where to | the thing a customer refers to |
| order_items | one product line on one order | the true grain of "what was sold" |
| transactions | one payment attempt | an order can be declined, retried, then refunded - three rows |
LiveThe two entities everyone leaves out3 min▶
Most first drafts of this model have six tables, not eight. The two that get dropped are the two that matter most later:
- carts. Teams treat the cart as UI state and throw it away at checkout. Then somebody asks "did sales drop because fewer people wanted to buy, or because checkout broke?" and there is no way to tell. Session b8 answers exactly that question, and it is only answerable because carts are a table.
- transactions. Teams put a
paidflag on the order instead. Then the decline, the retry and the refund all collapse into one boolean, the payments team cannot see their own failure rate, and finance cannot reconcile. Session b8's day-level diagnosis is impossible without this table.
Bazaar, in your browser 3 min live
Real SQLite compiled to WebAssembly, seeded with Bazaar's source data before every run. Nothing you type can break the next example, and nothing you run leaves your tab. The data is deterministic (seed 42), so every number printed on these pages is a number you can reproduce.
LiveYour first look at the source3 min▶
Press ▶ Run. The first run downloads the engine once (~660 KB, then cached).
SELECT 'users' AS entity, COUNT(*) AS rows FROM users UNION ALL SELECT 'merchants', COUNT(*) FROM merchants UNION ALL SELECT 'products', COUNT(*) FROM products UNION ALL SELECT 'carts', COUNT(*) FROM carts UNION ALL SELECT 'cart_items', COUNT(*) FROM cart_items UNION ALL SELECT 'orders', COUNT(*) FROM orders UNION ALL SELECT 'order_items', COUNT(*) FROM order_items UNION ALL SELECT 'transactions', COUNT(*) FROM transactions;
COUNT(*) on the line table is not the number of orders. 1,060 transactions against 985 orders tells you some orders needed more than one payment attempt.
Rebuild the flat export, and see the problem ★ 12 min · everyone builds
The export everyone in the business works from is a join of five of these tables, flattened to one row per order line. Build it, then count how many times the same fact is stored. This is the mess session b2 takes apart.
Join orders to their lines, then to products, merchants and users - the flattening every "just give me a CSV" request produces.
Look at one product across many rows. Its name, category and price are stored again on every single line.
Count the redundancy: how many stored copies of each product name exist, versus how many products there are.
SELECT o.order_id, o.order_ts, u.email, u.city,
p.product_name, p.category, oi.unit_price, oi.quantity,
m.merchant_name
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN users u ON u.user_id = o.user_id
JOIN products p ON p.product_id = oi.product_id
JOIN merchants m ON m.merchant_id = oi.merchant_id
ORDER BY o.order_id
LIMIT 12;
SELECT COUNT(*) AS stored_copies,
COUNT(DISTINCT p.product_name) AS distinct_values,
COUNT(*) - COUNT(DISTINCT p.product_name) AS wasted_repeats
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id;
The export is not the problem. Storing it is. Flattening data for a report is fine and normal - that is what session b6 and b7 do on purpose, for analytics. The failure mode is when the flat shape becomes the system of record: then a price correction has to find every copy, and the copies it misses become a support ticket six weeks later. Session b2 makes that failure happen in front of you, with real SQL.
Your turn: interrogate the grain ★ 10 min · build your own
Grain is the single most important idea in this course, and the fastest way to internalise it is to count the same thing three ways and get three answers. Each editor starts fresh from Bazaar's source tables.
LiveQ1 · How many orders? Three answers, one correct3 min▶
SELECT (SELECT COUNT(*) FROM orders) AS orders_table,
(SELECT COUNT(*) FROM order_items) AS order_lines,
(SELECT COUNT(*) FROM transactions) AS payment_attempts,
(SELECT COUNT(DISTINCT order_id) FROM order_items) AS orders_with_lines;
All four numbers are correct answers to different questions. When a dashboard says "orders" and nobody wrote down which one it means, this is where the argument comes from - and session a3 is the leader-track session about exactly this.
LiveQ2 · Find an order that needed more than one payment attempt4 min▶
SELECT t.order_id, COUNT(*) AS attempts,
GROUP_CONCAT(t.status) AS statuses,
GROUP_CONCAT(COALESCE(t.decline_reason, '-')) AS reasons
FROM transactions t
GROUP BY t.order_id
HAVING COUNT(*) > 1
ORDER BY attempts DESC
LIMIT 8;
Now imagine this table did not exist and the order simply had status = 'paid'. Every one of these stories - declined, retried, succeeded - would be invisible, and so would the incident that session b8 investigates.
Self-studyQ3 · Which entity is missing from this schema?3 min▶
A thought exercise, no SQL. Bazaar can currently answer "what was ordered" and "what was paid". It cannot answer "what was returned". Nothing in the eight tables records a return: what came back, why, whether it was resellable, or which refund it triggered.
That is not an oversight - it is the capstone. Session b10 hands you returns and refunds as a new subject area and asks you to take it down the whole ladder: entities, keys, DDL, dimensions, facts, mart, agent view. Bring one question about returns you would want answered, and design toward it.
Try it yourself - this week ◐ 20-30 min total
- Bookmark this page. The editors are a real database any time you want to test an idea.
- Take a flat export you actually work with - a CSV somebody emails you, a spreadsheet tab - and list its entities. Just the nouns, no columns. Most exports have three to five hiding in them.
- For each entity you found, write the one-sentence grain: "one row is one ___". If you need the word "and", you found two entities.
- Find one fact in that export that is stored more than once. Count the copies. That number is the size of your next data-quality incident.
- Name one question your current schema cannot answer because an entity is missing. Bring it to b2.
Sources covered
This track teaches the working core of the standard modeling literature, run on a live engine instead of slides. The full source map, including what is deliberately out of scope, is in materials/official-course-map.md. This page covers:
Three questions before you go 🎯 ◐ 90 seconds
1 · What does a conceptual model contain that a logical model does not?
The conceptual model is deliberately the smallest of the three: the things the business has and how they relate, readable by someone who has never seen SQL. Attributes and keys arrive in the logical model; types, constraints and indexes in the physical one.
2 · Bazaar has 985 orders and 1,434 order lines. A dashboard says "1,434 orders in the last 90 days". What went wrong?
One row of order_items is one product on one order, so counting it counts lines. Orders are COUNT(DISTINCT order_id). This is the single most common analytics error, and it is a grain error, not a SQL error.
3 · Why does Bazaar store payment attempts in their own table rather than a paid flag on the order?
The attempt is its own event at its own grain. Collapsing it into a boolean destroys the decline rate, the retry behaviour and the refund timing - which is exactly the information session b8 needs to explain a one-day revenue collapse.