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:

Schema Extraction: Decompose the 200 columns into clean facts (fct_shipments) and conformed dimensions (dim_carrier, dim_facility, dim_customer).
Role-Playing Dimensions: Accurately handle role-playing dimensions (origin facility vs destination facility).
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:

obt_star_refactor.sql: Target Star Schema DDL (fct_shipments, dim_carrier, dim_facility).
obt_backward_compatible_view.sql: SQL view preserving legacy column names so existing BI dashboards remain operational during migration.
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:

Dashboard performance: "the executive dashboard takes 45 seconds; users complain weekly."
Metric inconsistency: "marketing's customer_segment says 18,000 enterprise customers; sales says 21,000."
Build cost: "adding a new column to the OBT triggers a 2-hour rebuild that blocks other workflows."
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] ──→ [stg_*] ──→ [mart_orders_obt]          ← existing, untouched
                    ╲
                     ╲──→ [fct_order_items + dims]  ← new, additive

Validate the new star against the OBT: row counts match, revenue totals match per day/customer/product.

Phase 2 — Migrate one dashboard at a time

Pick the slowest dashboard. Rewrite it to query the star instead of the OBT. Compare results. Once consistent, deploy.

Repeat for the next-slowest dashboard. Etc.

This is the boring-but-safe approach. Engineering teams use the same pattern for any large migration (services, APIs, schemas).

Phase 3 — Deprecate the OBT (or keep as a view)

Once all dashboards query the star, three options:

(a) Delete the OBT — clean. Requires confidence all references are gone.
(b) Convert OBT to a view — CREATE VIEW mart_orders_obt AS SELECT ... FROM fct_order_items JOIN dim_customer .... Preserves the OBT interface for any straggler queries; metrics now flow from the star.
(c) Keep OBT as a derived materialization — mart_orders_obt rebuilt nightly from the star. Provides BI users the flat-table experience; the star is the truth.

Option (c) is most common in mature warehouses: star for modeling, OBT-style materialization for BI consumption.

Step 5 — Resolving metric inconsistency

This is half the value of the refactor. The OBT has, e.g., customer_segment denormalized into every order row. If the segment was computed differently at different times, the same customer has different segments on different orders. Pure garbage.

In the star:

dim_customer:
    customer_key, customer_id, segment, ..., effective_from, effective_to, is_current

Segment is computed once per customer per Type 2 version. Every fact joins to one consistent value. Marketing and sales can fight about how to compute segment, but they can't disagree about what it is once it's in the dim.

This consistency comes from:

One canonical computation of each dimension attribute.
Conformed dimensions: one dim_customer used by all facts.
Type 2 SCDs: history correctly attributed.

The OBT's chaos came from multiple computations of the same attribute living in the OBT's columns over time. The refactor removes that.

Step 6 — Performance gains

Expected improvements:

Star queries: 5-20× faster than OBT for typical dashboards (column pruning + smaller scan footprint).
Build cost: dimension changes touch one table; refresh in seconds instead of hours.
Schema evolution: adding a customer attribute is one ALTER TABLE on dim_customer, not a full OBT rebuild.

Caveat: BI tools that auto-detect schemas may need re-pointing. Looker explores need updating. Plan this in.

Step 7 — The pitch to management

"We have 4 specific pain points: slow dashboards, inconsistent metrics, slow schema changes, and high build cost. The fix is a 6-week incremental migration to a star schema, run in parallel to the existing OBT until we've validated it. Day 1 nothing breaks. By week 6, dashboards are 5-20× faster and metrics finally agree across teams. The team continues delivering throughout — no freeze required."

That's the conversation. No "OBT is bad, refactor" — concrete pains, concrete fix, concrete timeline.

Common follow-ups

Follow-up 1: "What's the risk of dual-writing to OBT and the star during the migration?"

Drift. If the OBT and star get out of sync, queries against either return different numbers. Mitigation: both derive from the same staging models. If staging changes, both update. Validation queries comparing OBT and star daily catch drift early.

Follow-up 2: "What if Looker explores hardcoded the OBT's column names everywhere?"

Map the OBT view (option (b)) to preserve the column names. Looker continues working. New explores point at the star. Old explores get migrated as a separate workstream.

Follow-up 3: "How do you prevent another OBT from accumulating later?"

Discipline. Document the star as the canonical model. Reject PRs that add denormalized columns to the fact when they belong in dimensions. Use dbt's tests to enforce dimension uniqueness and FK integrity. Code review.

📥 Download Starter Assets & Mock Database

Choose your preferred environment to get started immediately:

💾 Pre-Seeded SQLite Database (Recommended — Zero Setup):
⬇ Download case5_obt_to_star.sqlite
Pre-loaded with raw source tables, mock transactional records, and schema skeletons. Double-click to open in DB Browser for SQLite, DBeaver, or VS Code.

📄 ANSI / PostgreSQL SQL Starter Script:
⬇ Download case5_obt_to_star_starter.sql
Contains clean DDL statements, table definitions, and mock INSERT statements ready to paste into PostgreSQL, DuckDB, Snowflake, or BigQuery.

🛠️ How to Complete & Run This Case Study

You can complete this hands-on project using whichever tool matches your local setup:

Option 1: SQLite / DB Browser / DBeaver (Easiest — Zero Installation)
Download case5_obt_to_star.sqlite above. Open it in DB Browser for SQLite, DBeaver, or VS Code (with the free SQLite Viewer extension). All source ingestion tables and sample records are already populated. Write your target dimensional models and verification queries directly against the database with 0 server configuration.
Option 2: Local PostgreSQL or DuckDB
Download case5_obt_to_star_starter.sql. Execute the script in your local PostgreSQL database (via psql, DBeaver, or pgAdmin) or in DuckDB.
Option 3: Cloud Data Warehouse (Snowflake / BigQuery)
Copy and paste the DDL and mock seed data into a Snowflake worksheet or Google BigQuery dataset.

💻 Starter Scaffold

Use this template to begin your implementation:

-- ====================================================================
-- DELIVERABLE 1: obt_star_refactor.sql
-- ====================================================================

-- TARGET STAR SCHEMA DDL
-- 1. Fact Table: fct_shipments (Order/Shipment grain)
CREATE TABLE IF NOT EXISTS fct_shipments (
    shipment_key            BIGINT PRIMARY KEY,
    tracking_number         VARCHAR(100) NOT NULL,
    
    order_date_key          INTEGER NOT NULL,
    carrier_key             INTEGER NOT NULL,      -- FK -> dim_carrier
    origin_facility_key     INTEGER NOT NULL,      -- FK -> dim_facility
    dest_facility_key       INTEGER NOT NULL,      -- FK -> dim_facility
    customer_key            INTEGER NOT NULL,      -- FK -> dim_customer
    
    weight_kg               NUMERIC(10, 2) NOT NULL,
    shipping_charge_usd     NUMERIC(12, 2) NOT NULL,
    carrier_cost_usd        NUMERIC(12, 2) NOT NULL,
    net_margin_usd          NUMERIC(12, 2) NOT NULL,
    transit_days            NUMERIC(6, 2)
);

-- 2. Conformed Dimension: dim_carrier
CREATE TABLE IF NOT EXISTS dim_carrier (
    carrier_key             INTEGER PRIMARY KEY,
    carrier_code            VARCHAR(50) NOT NULL,
    carrier_name            VARCHAR(100) NOT NULL,
    service_level           VARCHAR(50) NOT NULL
);

-- 3. Conformed Dimension: dim_facility (Role-Playing for Origin & Destination)
CREATE TABLE IF NOT EXISTS dim_facility (
    facility_key            INTEGER PRIMARY KEY,
    facility_code           VARCHAR(50) NOT NULL,
    facility_name           VARCHAR(100) NOT NULL,
    city                    VARCHAR(100) NOT NULL,
    state_province          VARCHAR(100) NOT NULL,
    country                 VARCHAR(100) NOT NULL
);

-- ====================================================================
-- DELIVERABLE 2: obt_backward_compatible_view.sql
-- ====================================================================
-- Compatibility View: Emulates the old 200-column OBT during migration
-- so existing Looker/Tableau dashboards do not break during Phase 2.
CREATE OR REPLACE VIEW v_analytics_obt_legacy AS
SELECT 
    s.tracking_number,
    c.carrier_name,
    c.service_level,
    orig.facility_name AS origin_facility,
    dest.facility_name AS destination_facility,
    s.shipping_charge_usd,
    s.carrier_cost_usd,
    s.net_margin_usd
FROM fct_shipments s
JOIN dim_carrier c ON s.carrier_key = c.carrier_key
JOIN dim_facility orig ON s.origin_facility_key = orig.facility_key
JOIN dim_facility dest ON s.dest_facility_key = dest.facility_key;

📊 Evaluation Rubric (100 Points Total)

Evaluation Criterion	Weight	Target Standard (Pass ≥ 80%)
1. OBT Decomposition Quality	25 pts	Successfully identifies bloated dimensions mixed inside the OBT and extracts them into clean, conformed dimension tables (`dim_carrier`, `dim_facility`, `dim_customer`).
2. Role-Playing Dimension Handling	25 pts	Correctly recognizes that origin and destination facilities reference the same conformed `dim_facility` entity via role-playing foreign keys (`origin_facility_key`, `dest_facility_key`).
3. Zero-Downtime Migration Strategy	25 pts	Outlines a realistic 3-phase rollout plan (Shadow Build -> Backward-Compatible View -> Deprecation) that avoids breaking live BI reporting.
4. Metric Consistency & Scan Efficiency	25 pts	Proves that metric fan-out bugs are eradicated and demonstrates columnar storage scan reduction compared to querying the wide table.

📤 Submission Instructions & Guidelines

Deliverable Formats: Upload your completed .sql file or a .zip archive containing your SQL scripts and documentation (.md, .pdf, or ERD diagrams).
Dialect Compatibility: Ensure all DDL and queries execute cleanly on standard ANSI SQL, PostgreSQL, Snowflake, BigQuery, or Databricks.
Self-Review: Cross-check your solution against the 100-Point Evaluation Rubric above before submitting (passing threshold is 80/100 points).
Upload: Use the drag-and-drop file upload zone below. You can optionally detail architectural trade-offs in the submission notes box.
Confirmation: Click Submit Assignment. Your submission will be recorded, your lesson and module progress will be marked complete, and you can download your submitted work at any time.

Assignment Instructions