Data Modeling and Dimensional Design

ULC-002 leaves you able to query any schema. This course teaches you to design one — specifically, the kind of schema a business reports from, which is not the kind of schema a business runs on. You work on **PostgreSQL 16** on your own lab machine, with `ultishop` — the synthetic online-retail database from ULC-002 — as your source system. It has everything a real source system has: free-text statuses that arrive in sixteen spellings, eight orders dated 1970 and 2099, customers with no region, and no history at all. Over ten lessons you turn it into a warehouse: a landing area, a fiscal-aware calendar, a Type 2 customer dimension that remembers where somebody used to live, transaction, periodic-snapshot and accumulating-snapshot fact tables, junk and factless facts, foreign keys, indexes, monthly partitioning and a materialised aggregate that reconciles to the fact it came from. Two lessons put you in front of a problem rather than an exercise. A slowly-changing-dimension load finishes half way and leaves forty customers with two current rows; you find it from the data, repair it, and then add the constraint that makes it impossible. An order feed arrives before the customer feed; you diagnose two different causes in one symptom and fix only the one that is broken. A third has you review a model somebody else delivered — one that loads, returns numbers, and reports seven times the revenue it holds — against a written checklist, and decide whether to accept it. The working practice is the same as the modelling. Every schema change goes through a written change record with a rollback that has been executed. Every load is proved idempotent by running it twice. Every derived table is reconciled to its source, with its scope stated. Every command and every number in these lessons was executed on PostgreSQL 16.15 and the output you see is the output that was observed. The final project is an engagement: a subscription-box retailer with six feeds, a finance team that wants month-end positions and a marketing team that wants order-line detail attributed to the plan a customer was on at the time. You deliver the design, the model, the documentation and a handover, graded by automated checks against the model and a rubric against your reasoning.