It is 9 PM on a Friday at a food delivery company. The analytics team needs one number for a leadership review Monday morning: total revenue per restaurant for the last 30 days. The query has been running for eleven minutes. The `orders` table has 40 columns. Customer name and address are repeated on every row. Restaurant name is repeated on every row. There is no separate `date` table, so every "last 30 days" query does a full table scan and a string comparison on a text column that stores dates as `"14-08-2026"` on some rows and `"2026-08-14"` on others, because three different services have been writing to this table for two years with no shared schema. Nobody designed this table. It grew, one `ALTER TABLE` at a time, under deadline pressure, with nobody asking what one row actually means. That is what a data model is - a decision about what one row means, made on purpose, before the table exists instead of after it breaks. * **Data modeling** is the process of deciding what entities and events exist in your data, what a single row in each table represents, and how tables relate to each other, before you write a single `CREATE TABLE` statement. * Data modeling exists because bad models compound. A table with an unclear grain produces wrong numbers in every query built on top of it, and by the time someone notices, ten dashboards already trust the wrong number. * You reach for deliberate data modeling any time you are designing a new table, redesigning a slow or confusing one, or handing data off to another team that will build on top of it. > 📌 **Remember:** A data model is not documentation you write after the fact. It is > the design decision itself. The `CREATE TABLE` statement is just the model written > down in SQL. ---
**OLTP** (Online Transaction Processing) systems run the app. A Zomato order being placed, a Zerodha trade being executed, a PhonePe payment being authorised - each of these is a single, small, fast transaction that reads and writes a few rows. OLTP databases like PostgreSQL are built to handle thousands of these small transactions per second while keeping every write correct, even when many happen at once. **OLAP** (Online Analytical Processing) systems answer questions about history. "What was our revenue by city last quarter?" touches millions of rows and does heavy aggregation, but it only needs to run once, and it does not need to complete in milliseconds. OLAP systems, most often a cloud data warehouse, are built to scan huge amounts of data efficiently. > 💡 **Tip:** OLTP and OLAP both need correctness - a warehouse that gives wrong > answers is useless too. The real difference is workload shape: OLTP is optimised > for high-concurrency transactions touching a handful of rows at a time, while OLAP > is optimised for large analytical scans and aggregations over historical data. > "OLAP" describes a workload pattern, not a specific product - a warehouse is just > the most common place that workload runs. OLTP (PostgreSQL, MySQL) OLAP (Warehouse) +----------------------+ +----------------------+ | app writes 1 order | ETL/ | analyst reads millions| | at a time, many/sec | -------> | of rows for one report| | normalized, no dupes | ELT | denormalized, some dup| +----------------------+ +----------------------+ > **Note:** ETL and ELT both mean "move data from OLTP into OLAP." The difference is > whether transformation happens before loading (ETL) or after loading, inside the > warehouse, using SQL (ELT). This module focuses on the target shape of the data, > not how it gets there - that is covered fully in the Airflow and dbt modules. > 💡 **Practice:** Look at your phone's food delivery app. List three pieces of > information you would expect in the OLTP database the moment you place an order. > Now list three questions a business analyst might ask about a month of orders. > Notice how different those two lists feel - that difference is OLTP vs OLAP. **Concept check:** Would you design the "orders" table the same way for the app backend and for a monthly revenue report? Why not? ---
A relational table represents a defined set of related facts about an entity, a relationship, or a business event. `customers` and `restaurants` model entities. `orders` models a business event - and later in this module, that same event will reappear as a fact table, which is exactly the same idea in an analytical schema. ### Tables, rows, columns, and keys * A **primary key** uniquely identifies one row in a table. No two rows share one, and it is never null. * A **foreign key** is a column in one table that points to the primary key of another table, and is how tables relate to each other. ```sql CREATE TABLE customers ( customer_id BIGINT PRIMARY KEY, full_name VARCHAR(100) NOT NULL, city VARCHAR(50) NOT NULL, signup_date DATE NOT NULL ); CREATE TABLE orders ( order_id BIGINT PRIMARY KEY, customer_id BIGINT REFERENCES customers(customer_id), restaurant_id BIGINT NOT NULL, order_total_rs NUMERIC(10, 2) NOT NULL, ordered_at TIMESTAMP NOT NULL ); ``` > **Note:** `REFERENCES customers(customer_id)` is a foreign key constraint. It stops > you from ever inserting an order for a customer that does not exist, enforced by > the database itself, without writing application code to check it. ### Surrogate keys vs natural keys A **natural key** already exists in the real world or the source system - a mobile number, a GSTIN, a source system's own ID column. A **surrogate key** is generated by the database or warehouse itself, with no meaning outside the system - usually an auto-incrementing integer. > 📌 **Engineering Decision:** Use a surrogate key for dimension tables whenever you > need stable warehouse identity, and especially when you need to track historical > versions with SCD Type 2 - a natural key alone cannot represent "which version of > this customer was true on this date," but a surrogate key can, because each version > gets its own. Always keep the natural key as a regular column too, for lookups back > to the source system. Fact tables are less absolute: a fact table generally still > gets its own warehouse-generated key for the row itself, but which keys it stores > for its relationships depends on the architecture - sometimes a business identifier > is enough, sometimes a full surrogate key per dimension reference is worth it. > Do not treat "always surrogate, everywhere" as a universal rule - decide it per > table based on whether identity needs to stay stable over time. ### Constraints - rules the database enforces for you * `NOT NULL` - this column can never be empty * `UNIQUE` - no two rows can share this value * `DEFAULT` - what value to use when none is provided * `CHECK` - a custom rule, like `CHECK (order_total_rs > 0)` > 🔴 **Common Mistake:** Skipping constraints because "the application already > validates this." Application code has bugs, gets bypassed by scripts, and changes > hands between teams. A constraint at the database level is enforced no matter what > writes to the table. The fix: put every rule you actually depend on as a constraint, > not just a comment or an application check. ### ACID, briefly **Atomicity, Consistency, Isolation, Durability** are why a transaction either fully happens or does not happen at all, and why a crash a millisecond after commit never loses that data. ```sql BEGIN; UPDATE wallets SET balance_rs = balance_rs - 500 WHERE wallet_id = 'w_rahul_9821'; UPDATE wallets SET balance_rs = balance_rs + 500 WHERE wallet_id = 'w_merchant_442'; COMMIT; -- both updates succeed together, or neither happens ``` > **Note:** ACID is what makes OLTP databases trustworthy for the small, frequent > writes covered above. Transaction isolation levels and concurrency control go > deeper than this module needs - that lives in the SQL and Databases foundations > content. ### Indexes - trading write speed for read speed An index is an additional data structure the database maintains alongside a table so it can locate rows without scanning every one. PostgreSQL commonly uses B-tree indexes, which keep values sorted for fast lookups and range scans, but other index types exist for different access patterns, like full-text search or spatial data. ```sql CREATE INDEX idx_orders_customer_id ON orders(customer_id); ``` > 🔴 **Common Mistake:** Adding an index to every column "just in case." Every index > speeds up reads on that column but slows down every `INSERT`, `UPDATE`, and `DELETE` > on the table, because the database has to update the index too. On a high-write > OLTP table, index only the columns you actually filter or join on frequently. **Concept check:** A table gets 50,000 inserts per second and is queried maybe twice a day. Should you index every column? What would you do instead? ---
**Normalization** reduces unnecessary redundancy and organizes column dependencies so each fact is maintained in the appropriate table. **Denormalization** deliberately duplicates data to avoid joins - both are correct design choices, in different places. * **1NF:** every column holds one atomic value, never a list. * **2NF:** every non-key column depends on the *whole* primary key, not part of it - matters only for composite (multi-column) primary keys. * **3NF:** every non-key column depends only on the primary key, not on another non-key column. ```sql -- VIOLATES 3NF: restaurant_city depends on restaurant_id, not order_id CREATE TABLE orders_bad ( order_id BIGINT PRIMARY KEY, restaurant_id BIGINT, restaurant_city VARCHAR(50) -- duplicated on every order from this restaurant ); -- CORRECT: restaurant_city lives once, in the restaurants table CREATE TABLE restaurants ( restaurant_id BIGINT PRIMARY KEY, restaurant_city VARCHAR(50) ); CREATE TABLE orders_good ( order_id BIGINT PRIMARY KEY, restaurant_id BIGINT REFERENCES restaurants(restaurant_id) ); ``` > 📌 **Engineering Decision:** Normalize your OLTP application database, almost > without exception - it keeps every fact maintained in one place, so a restaurant > changing its city is one `UPDATE`, not a hunt across every order row that > duplicated it. Denormalize your OLAP warehouse tables on purpose, because analysts > querying millions of rows would rather read one wide table than write six joins. > Normalize where data is written. Denormalize where data is read. **Concept check:** A `products` table stores `category_name` directly instead of a `category_id` foreign key. What breaks when a category gets renamed? ---
Before you design a single fact table, walk through the same repeatable sequence every time. This is the method, not just a definition - use it on every new business process you model. 1. **Identify the business process.** Example: payments. 2. **Declare the grain in one sentence.** "One row per successful payment transaction." 3. **Identify the dimensions.** Who (customer), what (payment method), where (merchant), when (date). 4. **Identify the facts.** What can actually be measured: amount, fee. 5. **Choose the fact table type.** Transaction, periodic snapshot, or accumulating snapshot - covered below. 6. **Decide history requirements per dimension.** Does this dimension need SCD Type 1 or Type 2? 7. **Define keys and relationships.** Surrogate keys, natural keys, foreign keys. 8. **Validate the model** by asking: can every row's meaning be stated in one sentence? Can any join multiply rows unexpectedly? Can the metrics this table needs to answer actually be calculated without double counting? > 📌 **Remember:** Grain is the single most important decision in a fact table, > because every other decision - which dimensions to join, which facts to store, how > to aggregate - only makes sense once the grain is fixed. Write the grain down as > "one row per ___" before creating a single column, and check every new column > against that sentence. ---
A **fact table** stores the measurements of a business process - a trade, an order, a payment. Its rows are numbers you can add up. A **dimension table** stores the context around those measurements - who, what, where, when. dim_date dim_customer +---------+ +------------+ | date_sk |<---+ +->| customer_sk| +---------+ | | +------------+ | | +-----------+ | fact_trades| +-----------+ | | dim_instrument | | dim_broker +------------+<-+ +->+------------+ A **star schema** puts one fact table at the center with dimension tables radiating outward. In a classic star schema, dimensions connect directly to the fact table rather than forming chains of dimension-to-dimension joins - that chained pattern is what a snowflake schema introduces instead, covered later at awareness level. ```sql CREATE TABLE fct_trades ( trade_sk BIGINT PRIMARY KEY, date_sk INT REFERENCES dim_date(date_sk), customer_sk BIGINT REFERENCES dim_customer(customer_sk), instrument_sk BIGINT REFERENCES dim_instrument(instrument_sk), trade_id VARCHAR(50), -- degenerate dimension, see below quantity INT NOT NULL, price_rs NUMERIC(10, 2) NOT NULL, trade_value_rs NUMERIC(14, 2) NOT NULL ); ``` The clean mental model to keep in your head: **a fact is what happened, a dimension is the context in which it happened.** | | Payment example | |:---|:---| | Fact | amount = Rs 500, fee = Rs 5 | | Dimensions | customer = Rahul, merchant = Swiggy, date = Aug 16, city = Delhi | ### Additive, semi-additive, and non-additive measures Not every number in a fact table can be summed the same way. * **Additive** measures can be summed across every dimension - `order_total_rs` can be summed by customer, by date, by city, all correctly. Most facts are additive. * **Semi-additive** measures can be summed across some dimensions but not others - an account balance can be summed across customers at one point in time, but summing a balance across dates produces a meaningless number. You average or take the latest value across time instead. * **Non-additive** measures cannot be summed at all - a percentage, a ratio, an average. You store the underlying numerator and denominator and recompute the ratio after aggregating those, never sum the ratio itself. > 🔴 **Common Mistake:** Summing a semi-additive or non-additive measure the same way > you would sum revenue. `SUM(account_balance_rs)` across a week of daily snapshots > produces a number with no real meaning - the fix is to know which of your three > categories a measure falls into before writing the aggregation. ### Degenerate and junk dimensions A **degenerate dimension** is an identifier stored directly on the fact table with no separate dimension table, because it has no descriptive attributes of its own - `trade_id` above is degenerate. A **junk dimension** bundles several low-cardinality flags - `payment_method`, `is_first_order`, `promo_applied` - into one small dimension table, instead of five separate skinny tables or five separate flag columns on the fact table. ### Fact table types | Fact table type | Grain | Example | |:---|:---|:---| | Transaction | One row per business event | One row per UPI payment | | Periodic snapshot | One row per entity per fixed time period | Daily closing wallet balance per customer | | Accumulating snapshot | One row per process, updated as it progresses | One row per order, updated at each fulfillment stage | ```sql -- accumulating snapshot: one row per order, updated as it moves through Swiggy's -- fulfillment pipeline - most columns start NULL and fill in over time CREATE TABLE fct_order_fulfillment ( order_sk BIGINT PRIMARY KEY, customer_sk BIGINT, restaurant_sk BIGINT, placed_date_sk INT NOT NULL, accepted_date_sk INT, picked_up_date_sk INT, delivered_date_sk INT, order_total_rs NUMERIC(10, 2) NOT NULL ); ``` > **Note:** An accumulating snapshot row is the one exception to "never `UPDATE` a > fact table" - it is designed to be revisited as the process advances. A transaction > fact table is written once and never touched again. > 💡 **Practice:** Sketch the grain statement and column list for a fact table > tracking Hotstar video streaming sessions. Is this a transaction fact, a periodic > snapshot, or an accumulating snapshot? Justify your answer in one sentence. ### Avoiding double counting - the most common modeling failure This is the single most valuable practical lesson in this module. Consider an `orders` table storing `order_total_rs` directly, joined to `order_items`: ```text orders order_items order_id | order_total_rs order_id | product | quantity 1 | 1000 1 | A | 2 1 | B | 1 ``` ```sql -- looks reasonable, silently wrong SELECT SUM(o.order_total_rs) FROM orders o JOIN order_items oi ON o.order_id = oi.order_id; -- returns 2000, not 1000 - order_total_rs got counted once per matching item row ``` The join multiplied one order row into two rows before the `SUM()` ran, because the fact (`order_total_rs`) lives at order grain but the join produced rows at item grain. This is exactly why declaring the grain first matters - it is not academic, it is how real revenue numbers get silently doubled in production. > 🔴 **Common Mistake:** Mixing grains inside one fact table - or joining a > header-level total to a line-level table - happens because it feels convenient at > the time. The fix: build a proper `fct_order_items` at line-item grain (with > `line_amount_rs` per line, not a repeated order total), and a separate > `fct_orders` at order grain if you need order-level totals. One fact table, one > grain, always. Before trusting any `SUM()`, ask what grain the rows being summed > are actually at. ### Bridge tables for many-to-many relationships A star schema assumes each fact row joins to exactly one row per dimension, but real businesses have many-to-many relationships - one product appears on many orders, one order contains many products. A **bridge table** resolves this. Note the lesson above still applies: if a business process genuinely happens at the line-item level, model a line-item fact table directly rather than reaching for a bridge table as the default. ```sql -- one row per order line, at the correct grain - not a bridge over order totals CREATE TABLE fct_order_items ( order_line_sk BIGINT PRIMARY KEY, order_sk BIGINT REFERENCES fct_orders(order_sk), product_sk BIGINT REFERENCES dim_product(product_sk), quantity INT NOT NULL, line_amount_rs NUMERIC(10, 2) NOT NULL ); -- grain: one row per order line item ``` ### Conformed dimensions A **conformed dimension** - most often `dim_date` or `dim_customer` - is shared across multiple fact tables, so "last 30 days" and "customer city" mean exactly the same thing whether you are querying orders, trades, or payments. **Concept check:** Why does a `dim_date` table exist at all, instead of just using a native `DATE` column and computing weekday or fiscal quarter with SQL functions every time? ---
It is 9 PM on a Friday at a food delivery company. The analytics team needs one number for a leadership review Monday mo...
OLTP (Online Transaction Processing) systems run the app. A Zomato order being placed, a Zerodha trade being executed, a...
A relational table represents a defined set of related facts about an entity, a relationship, or a business event. custo...
Normalization reduces unnecessary redundancy and organizes column dependencies so each fact is maintained in the appropr...
Before you design a single fact table, walk through the same repeatable sequence every time. This is the method, not jus...
A fact table stores the measurements of a business process - a trade, an order, a payment. Its rows are numbers you can ...
Dimension attributes are not permanent. A customer moves from Pune to Bengaluru. Slowly Changing Dimensions (SCDs) are t...
Snowflake schema normalizes dimension tables further, breaking out attributes like categoryname into their own linked ta...
A few physical modeling ideas are worth knowing at the level of "how does this affect my schema design," without going d...
Prerequisites: PostgreSQL running locally (or via docker-compose), psql or any SQL client, basic familiarity with CREATE...
Concept When to use it Normalized schema (3NF) OLTP application databases, anywhere data is written frequently Denormali...
Designing a fact table without writing down the grain first happens because it feels faster to just start adding columns...
Aligns directly with DevOps, Site Reliability (SRE), and Platform Engineering job descriptions.