🖼️ See the drawing + lab 🏠 Course home 🌿 Branch on GitHub ✏️ View source

🖼️ आकृती आणि labThe drawing + lab पूर्ण पानावर उघडा ↗Open full page ↗

🏛️ धडा 18 — OLTP विरुद्ध OLAP: office आणि संग्रहालय

📍 तुम्ही इथे आहात: 18 पैकी धडा 18 — शेवटचा धडा! · मागील: lesson-17-app-code

भाग 3 — अधिक खोलात. धडे 01–12 नी रेकॉर्ड रूम बांधली आणि चालवली. धडे 13–18 हे interviews आणि incidents मध्ये विचारले जाणारे विषय आहेत — प्रत्येक त्याच school.db वर प्रत्यक्ष चालतो.

📦 या ब्रँचमध्ये काय आहे

संपूर्ण कोर्स, आणि db/demo.py मधले olap(): हजेरीचा मासिक report, चालू 200,000-ओळींच्या table वर आणि "nightly ETL" ने बनवलेल्या summary table वर चालवलेला, आणि grade_changes मध्ये प्रत्येक श्रेणी-बदल नोंदवणारा एक trigger (change data capture).

🧒 5 वर्षांच्या मुलाला समजावल्यासारखे

शाळेचे office दिवसभर छोट्या कामांत व्यस्त असते: कतरिनाची हजेरी लावा, रोहनची श्रेणी दुरुस्त करा, दियाचे नाव नोंदवा. हेच OLTP — online transaction processing.

वर्षातून एकदा मुख्याध्यापिका एक प्रचंड प्रश्न विचारतात: "शाळा सुरू झाल्यापासून प्रत्येक वर्षाची, महिन्यानुसार हजेरी". हे office ला विचारले, तर कारकून शोधत असताना सगळ्यांचे काम थांबते. म्हणून शाळा शेजारी एक संग्रहालय 🏛️ ठेवते, प्रचंड प्रश्नांसाठी बांधलेले, दर रात्री office च्या नोंदींतून भरलेले. हेच OLAP — online analytical processing, सहसा एक data warehouse.

दर रात्री सगळे copy न करता संग्रहालय ताजे ठेवण्यासाठी office एक बदलांची डायरी ठेवते: प्रत्येक वेळी श्रेणी बदलली की एक ओळ लिहिली जाते. संग्रहालय ती डायरी वाचते. हेच change data capture (CDC).

🗺️ आकृती

flowchart LR
    oltp["🗄️ live room (OLTP)<br/>many tiny reads/writes · row store"]
    olap["🏛️ warehouse (OLAP)<br/>few huge reads · columnar"]
    oltp -->|"🌙 nightly ETL"| olap
    oltp -. "CDC: every change (trigger / WAL)" .-> olap

🗺️ काढलेली आवृत्ती + एक lab: https://school-edh.pages.dev/database/lesson-diagrams.html#l18

❓ काय

OLTP — Postgres, MySQL, SQLite: rows एकत्र साठवलेल्या, point lookups साठी indexes, अनेक छोटे transactions.
OLAP — BigQuery, Redshift, Snowflake, ClickHouse, DuckDB: columnar storage (एका column च्या values एकत्र, compressed साठवलेल्या), अब्जावधी rows scan आणि aggregate करण्यासाठी बांधलेले.
ETL / ELT — चालू खोलीतून extract, transform, warehouse मध्ये load, ठरलेल्या वेळापत्रकानुसार. Summary tables (जसे attendance_by_month) हे सर्वात सोपे रूप.
CDC — प्रत्येक बदल घडताच पकडणे: change table मध्ये लिहिणारा trigger (हा demo), किंवा Debezium किंवा AWS DMS सारख्या tools ने WAL (धडा 15) वाचणे.
Star schema — warehouse चा आकार: एक मोठा fact table (हजेरीच्या ओळी), ज्याभोवती dimension tables (विद्यार्थिनी, वर्ग, तारीख).

🤔 का

चालू खोलीवरचे जड reports बाकी सगळ्यांना हळू करतात, आणि dashboards ना सतत refresh व्हायला आवडते. त्यांना त्यांच्यासाठीच बांधलेल्या store मध्ये हलवल्याने office जलद आणि reports स्वस्त राहतात.

🔧 कसे (या repo मध्ये)

db/demo.py मधले olap() report दोन्ही प्रकारे चालवून वेळ मोजते, उत्तरे जुळतात का तपासते, मग cdc_grades trigger बनवते आणि रोहनची maths ची श्रेणी बदलते.

🧪 करून पाहा

python3 db/demo.py olap
python3 - <<'EOF'
import sqlite3
c = sqlite3.connect("db/school.db")
c.execute("UPDATE grades SET grade = 'A+' WHERE student_id = 2 AND subject = 'science'")   # same value: A+ → A+
c.execute("UPDATE grades SET grade = 'B+' WHERE student_id = 1 AND subject = 'maths'"); c.commit()
print(c.execute("SELECT grade_id, old, new FROM grade_changes").fetchall())
EOF

✅ तपासा — तुम्हाला काय दिसायला हवे

report on the live 200,000-line table: ~29 ms · on the nightly summary (12 rows): ~0.08 ms (तुमचे आकडे वेगळे असतील), same answer: True, आणि बदलांच्या डायरीत (9, 'B', 'A'). Snippet मध्ये trigger दोन्ही updates नोंदवतो — तो प्रत्येक UPDATE OF grade वर चालतो, तीच value पुन्हा लिहिणाऱ्या update वरही.

🏁 तुम्ही आत्ताच काय सिद्ध केले

कोणते प्रश्न office मध्ये आणि कोणते संग्रहालयात हवेत हे तुम्ही सांगू शकता, आणि summary table आणि बदलांची डायरी वापरून report चालू खोलीतून बाहेर हलवू शकता.

⚠️ नेहमीच्या चुका

production database वर दर सेकंदाला refresh होणारे dashboards
warehouse मधल्या rows बदलणे — तो source मधून पुन्हा बांधला जातो, बदलला जात नाही
कोणीच monitor न करणारा nightly ETL (report गुपचूप मागच्या आठवड्याचेच दाखवतो)
काही हजार rows साठी "आपल्याला warehouse हवे" — एक indexed query पुरेशी असू शकते
CDC triggers प्रत्येक write वर काम वाढवतात हे विसरणे

🏭 प्रत्यक्ष वापरात हे का महत्त्वाचे: बहुतेक companies product साठी Postgres/MySQL आणि analytics साठी warehouse चालवतात, ETL किंवा CDC pipelines ने जोडलेले — data engineering इथूनच सुरू होते.

🎓 रेकॉर्ड रूम आता पूर्णपणे तुमची

चिकट चिठ्ठ्या → नोंदवह्या आणि keys → अभिलेखपाल → पेन्सिलमधली नोंदवही → एक तथ्य, एक जागा → कॅटलॉग → दोन कारकून → नूतनीकरण → आगीपासून सुरक्षित प्रत → अधिक खोल्या → on call → कुलूपबंद नोंदवही → rankings → डायरी → खोलीच्या प्रती → खोका घेऊन जाणारी मदतनीस → शेजारचे संग्रहालय. 🗄️🎓

git checkout main
python3 db/demo.py     # every lesson, 01–18, in one run

🏛️ Lesson 18 — OLTP vs OLAP: the office and the archive

📍 You are here: Lesson 18 of 18 — the final lesson! · Previous: lesson-17-app-code

Part 3 — going deeper. Lessons 01–12 built and ran the record room. Lessons 13–18 are the topics interviews and incidents ask about — each one runs for real on the same school.db.

📦 What's in this branch

The complete course, plus olap() in db/demo.py: a monthly attendance report run on the live 200,000-line table and on a summary table built by a "nightly ETL", and a trigger that records every grade change in grade_changes (change data capture).

🧒 Explain like I'm 5

The school office is busy all day with tiny jobs: mark Katrina present, fix Rohan's grade, enrol Diya. That is OLTP — online transaction processing.

Once a year the head teacher asks a huge question: "attendance by month, for every year since the school opened". If you ask the office, everyone stops working while the clerks dig. So the school keeps an archive 🏛️ next door, built for huge questions, filled every night from the office's records. That is OLAP — online analytical processing, usually a data warehouse.

To keep the archive fresh without copying everything every night, the office keeps a change diary: every time a grade changes, one line is written. The archive reads the diary. That is change data capture (CDC).

🗺️ Diagram

flowchart LR
    oltp["🗄️ live room (OLTP)<br/>many tiny reads/writes · row store"]
    olap["🏛️ warehouse (OLAP)<br/>few huge reads · columnar"]
    oltp -->|"🌙 nightly ETL"| olap
    oltp -. "CDC: every change (trigger / WAL)" .-> olap

🗺️ Drawn version + a lab: https://school-edh.pages.dev/database/lesson-diagrams.html#l18

❓ What

OLTP — Postgres, MySQL, SQLite: rows stored together, indexes for point lookups, many small transactions.
OLAP — BigQuery, Redshift, Snowflake, ClickHouse, DuckDB: columnar storage (a column's values stored together, compressed), built to scan and aggregate billions of rows.
ETL / ELT — extract from the live room, transform, load into the warehouse, on a schedule. Summary tables (like attendance_by_month) are the simplest form.
CDC — capture each change as it happens: a trigger writing to a change table (this demo), or reading the WAL (lesson 15) with tools like Debezium or AWS DMS.
Star schema — the warehouse shape: a big fact table (attendance lines) surrounded by dimension tables (pupil, class, date).

🤔 Why

Heavy reports on the live room slow everyone else down, and dashboards love to refresh. Moving them to a store built for them keeps the office fast and the reports cheap.

🔧 How (in this repo)

olap() in db/demo.py times the report both ways, checks the answers match, then creates the cdc_grades trigger and changes Rohan's maths grade.

🧪 Try it

python3 db/demo.py olap
python3 - <<'EOF'
import sqlite3
c = sqlite3.connect("db/school.db")
c.execute("UPDATE grades SET grade = 'A+' WHERE student_id = 2 AND subject = 'science'")   # same value: A+ → A+
c.execute("UPDATE grades SET grade = 'B+' WHERE student_id = 1 AND subject = 'maths'"); c.commit()
print(c.execute("SELECT grade_id, old, new FROM grade_changes").fetchall())
EOF

✅ Verify — what you should see

report on the live 200,000-line table: ~29 ms · on the nightly summary (12 rows): ~0.08 ms (your numbers differ), same answer: True, and (9, 'B', 'A') in the change diary. In the snippet, the trigger records both updates — it fires on every UPDATE OF grade, even one that writes the same value.

🏁 What you just proved

You can say which questions belong in the office and which in the archive, and move a report off the live room with a summary table and a change diary.

⚠️ Common mistakes

dashboards that refresh every second against the production database
editing rows in the warehouse — it is rebuilt from the source, not edited
a nightly ETL nobody monitors (the report silently shows last week)
"we need a warehouse" for a few thousand rows — one indexed query may be enough
forgetting that CDC triggers add work to every write

🏭 Why this matters in production: most companies run Postgres/MySQL for the product and a warehouse for analytics, joined by ETL or CDC pipelines — data engineering starts here.

🎓 The record room is yours — all of it

Sticky notes → registers and keys → the archivist → the pencil ledger → one fact, one place → the catalogue → two clerks → renovations → the fireproof copy → more rooms → on call → the locked register → rankings → the diary → copies of the room → the helper with a box → the archive next door. 🗄️🎓

git checkout main
python3 db/demo.py     # every lesson, 01–18, in one run

← Previousapp code Finished! Take the quiz →check what stuck

This page is the lesson's README from the lesson-18-oltp-olap branch, shown here so the whole School stays on one site. Code files open on GitHub at the same branch.