AI Path Finder
Free consultation

Lesson · Hinglish · ⏱ 7 min padhne mein

🏗️ Data modelling: analysis ke liye tables design karna

EnglishHinglish

🏠 Asli zindagi ki misaal

Supermarket ka bill batata hai kya hua: items, quantity, amount. Yeh 'fact' hai. Alag se store products (brand, category) aur stores (city, size) ki details rakhta hai. Yeh 'dimensions' hain. Bills ko halka rakhna aur details ko unki apni tables mein rakhna har report ko fast aur saaf banata hai.

💡 Aasaan shabdon mein

Analytics databases (BigQuery ya Snowflake jaise data warehouses) app databases se alag design hote hain. Classic design star schema hai: beech mein events (sales, clicks, trips) ki fact table, numeric measures ke saath, aur uske chaaron taraf dimension tables jo kaun, kya, kahan aur kab describe karti hain. Window functions, jaise running totals, ranks aur 'pichhli value', bina messy self-joins ke sequences analyse karne dete hain. Achha model common sawaalon ko aasaan aur galat jawaabon ko mushkil bana deta hai.

🪜 Step by step

  1. Grain tay karo: ek row ka matlab kya hai? Ek order, ek order line, ya har din ek customer?
  2. Events aur numbers ke liye fact table banao (order_id, date_id, product_id, quantity, amount).
  3. Descriptions ke liye dimension tables banao (product naam aur category, customer city, calendar dates).
  4. Inhe keys se star shape mein jodo: beech mein facts, chaaron taraf dimensions.
  5. Har table aur column document karo, aur tests lagao (unique keys, jahan allowed nahi wahan NULL nahi).

🔑 Zaroori shabd

Fact table
Numbers wale events, jaise har sale aur uska amount.
Dimension table
Describe karne wali details, jaise product ka naam ya store ki city.
Window function
Related rows pe calculation, jaise rank ya running total.

👀 Example dekho

sql
SELECT store_id, sale_date, amount,
       SUM(amount) OVER (PARTITION BY store_id ORDER BY sale_date) AS running_total
FROM fact_sales;

Har store ka running total, bina self-join ke.

⚠️ Aam galtiyaan jinse bachna hai

  • Ek table mein alag grains mix karna, jaise daily totals aur individual orders saath. Sums galat ho jaate hain.
  • History overwrite karna: customer ki city badli aur aapne overwrite kar di, to purani sales bhi nayi city dikhayengi. Jahan zaroori ho history rakho.

✅ Jaldi se check karo

Star schema mein 'product category' column kahan hona chahiye?

  1. Har table mein repeat
  2. Fact table mein
  3. Product dimension table mein
Jawaab dekho

C. Product dimension table mein: Category product ko describe karti hai, isliye woh product dimension mein rehti hai, jisse fact table judti hai.

🙋 Seekhne wale yeh poochhte hain

Normalised tables ya star schema?

Apps likhte waqt duplicate data se bachne ke liye normalised tables use karti hain. Analytics star schema use karta hai kyunki padhna aur reporting aasaan aur tez hoti hai.

AI ke liye data modelling kyun zaroori hai?

Saaf, achhi tarah defined tables features aur training data ko bharosemand banati hain. Kai 'model problems' asal mein data definition problems hote hain.

📌 Yaad rakho

Facts = kya hua (numbers). Dimensions = descriptions.

📖 Is lesson ke shabd (AI dictionary)

Poora course, aapke roadmap ke hisaab se

Progress tracking, quick checks aur step-by-step order ke saath. Yeh lesson in courses ka hissa hai:

Data Engineer (AI focus)

Course mein seekho