SUBMISSION: Case 5 — Refactoring One Big Table (OBT) to Star

# SUBMISSION: Case 5 — Refactoring One Big Table (OBT) to Star > 🎯 **Hands-on Portfolio Deliverable:** In this project, you will execute a high-stakes schema refactoring project: decomposing an inherited, 200-column monolithic One Big Table (OBT) into a clean, lightning-fast Kimball star schema without causing downtime or breaking hundreds of existing business dashboards. ## 🏢 Business Scenario & Executive Prompt You have just joined **FleetLogix**, an enterprise supply-chain logistics company. Two years ago, a former engineering contractor built a single 200-column table called `analytics_obt` to serve all company dashboards. Today, `analytics_obt` has grown to 85 million rows: - **Runaway Query Costs:** Simple queries scanning 3 columns still scan hundreds of megabytes because the wide table is bloated with semi-structured JSON and repeated text. - **Metric Inconsistency:** Analysts joining line items to `analytics_obt` frequently double-count shipment revenue because the grain of the wide table is ambiguous. - **Dashboard Timeouts:** Executive shipment tracking dashboards take over 90 seconds to load on Monday mornings. Your mission is to decommission the legacy OBT and architect a modern dimensional star schema: 1. **Schema Extraction:** Decompose the 200 columns into clean facts (`fct_shipments`) and conformed dimensions (`dim_carrier`, `dim_facility`, `dim_customer`). 2. **Role-Playing Dimensions:** Accurately handle role-playing dimensions (origin facility vs destination facility). 3. **Zero-Downtime Migration Plan:** Author a 3-phase rollout and compatibility view so business operations continue uninterrupted. --- ## 🎯 Required Deliverables You must upload a single `.sql` or `.zip` file containing: 1. **`obt_star_refactor.sql`**: Target Star Schema DDL (`fct_shipments`, `dim_carrier`, `dim_facility`). 2. **`obt_backward_compatible_view.sql`**: SQL view preserving legacy column names so existing BI dashboards remain operational during migration. 3. **Migration Plan Document**: A structured migration playbook explaining your phased rollout and metric reconciliation strategy. --- > *"You inherit a warehouse where the central table is `mart_orders_obt` — 200 columns, every dimension attribute denormalized into the fact. Dashboards are slow, metrics disagree between teams, and adding a new column triggers a 2-hour rebuild. Plan the refactor to a proper star schema."* This is the most realistic warehouse-refactor scenario. Pure greenfield is rare; you'll almost always inherit something that grew organically and now hurts. --- ## Step 1 — Diagnose the actual problems Before any refactor, document concrete pain points. "OBT is bad" isn't a justification — specific symptoms are: 1. **Dashboard performance**: "the executive dashboard takes 45 seconds; users complain weekly." 2. **Metric inconsistency**: "marketing's customer_segment says 18,000 enterprise customers; sales says 21,000." 3. **Build cost**: "adding a new column to the OBT triggers a 2-hour rebuild that blocks other workflows." 4. **Schema evolution friction**: "every dimension change requires backfilling the OBT, slowing down product changes." If you can't articulate concrete pains, the refactor isn't justified yet. ## Step 2 — Inventory the OBT's columns Group the 200 columns into: | Category | Example columns | Count | |---|---|---| | Core measures | revenue, quantity, cost | ~15 | | Order metadata | order_id, order_date, payment_method | ~10 | | Customer attributes | customer_name, country, segment, signup_date | ~30 | | Product attributes | product_name, category, brand, sku | ~25 | | Store attributes | store_name, region, store_type | ~15 | | Marketing attribution | first_touch_channel, last_touch_channel, utm_* | ~20 | | Geographic context | ship_country, bill_country, region_code | ~15 | | Flags / status | is_gift, is_express, is_first_order | ~20 | | Derived metrics | revenue_usd, margin_pct, customer_ltv_to_date | ~30 | | Audit | inserted_at, source_system, last_updated | ~10 | Most of these are denormalized attributes that belong in dimensions. The measures and FK columns are what stay on the fact. ## Step 3 — Plan the target star ``` dim_date │ │ dim_customer ─── fct_order_items ─── dim_product │ │ dim_store ──────┼────── dim_channel │ dim_order_flags ``` Fact table: ~20 columns (FKs + measures + degenerate dims like order_id). Dimensions: 6-7 tables, each with their attributes. ## Step 4 — Phase the migration (this is the hard part) A big-bang refactor is risky. Do it incrementally: ### Phase 1 — Build the star alongside the OBT (no breakage yet) Build `fct_order_items`, `dim_customer`, etc. as new tables that derive from the same source as the OBT. They run in parallel. Existing dashboards keep using the OBT; nothing breaks. ``` [Source] ──→ [s