Lesson · ⏱ 7 min read
🏗️ Data modelling: designing tables for analysis
EnglishHinglish
🏠 Real-life example
A supermarket bill lists what happened: items, quantities, amounts. That's a 'fact'. Separately, the store keeps details about products (brand, category) and stores (city, size). Those are 'dimensions'. Keeping the bills slim and the details in their own tables makes every report faster and cleaner.
💡 In simple words
Analytics databases (data warehouses like BigQuery or Snowflake) are designed differently from app databases. The classic design is a star schema: a central fact table of events (sales, clicks, trips) with numeric measures, surrounded by dimension tables that describe the who, what, where and when. Window functions such as running totals, ranks and 'previous value' let you analyse sequences without messy self-joins. A good model makes common questions easy and wrong answers hard.
🪜 Step by step
- Decide the grain: what does one row mean? One order, one order line, or one customer per day?
- Create a fact table for events and numbers (order_id, date_id, product_id, quantity, amount).
- Create dimension tables for descriptions (product name and category, customer city, calendar dates).
- Connect them with keys in a star shape: facts in the middle, dimensions around.
- Document each table and column, and add tests (unique keys, no NULLs where not allowed).
🔑 Key terms
- Fact table
- Events with numbers, e.g. each sale and its amount.
- Dimension table
- Descriptive details, e.g. product name or store city.
- Window function
- A calculation across related rows, such as a rank or running total.
👀 See it in action
SELECT store_id, sale_date, amount,
SUM(amount) OVER (PARTITION BY store_id ORDER BY sale_date) AS running_total
FROM fact_sales;A running total per store, with no self-join needed.
⚠️ Common mistakes to avoid
- Mixing grains in one table, like daily totals and individual orders together. Sums become wrong.
- Overwriting history: if a customer moves city and you overwrite it, old sales get the new city. Keep history where it matters.
✅ Quick check
In a star schema, where does the 'product category' column belong?
- Repeated in every table
- The fact table
- A product dimension table
Show answer
C. A product dimension table: Category describes the product, so it lives in the product dimension, which the fact table links to.
🙋 Questions learners ask
Normalised tables or a star schema?
Apps use normalised tables to avoid duplicate data while writing. Analytics uses star schemas because they make reading and reporting simple and fast.
Why does data modelling matter for AI?
Clean, well defined tables make features and training data trustworthy. Many 'model problems' are really data definition problems.
📌 Remember
Facts = what happened (numbers). Dimensions = descriptions.
📖 Words from this lesson (AI dictionary)
Learn this as part of a full course
Track your progress, take the quick checks and follow the lessons in the right order. This lesson is part of:
Continue in the course