SUBMISSION: Case 6 — Full OLTP-to-Warehouse Migration Architecture

# SUBMISSION: Case 6 — Full OLTP-to-Warehouse Migration Architecture > 🎯 **Capstone Portfolio Project:** In this culminating project, you will architect the complete end-to-end data platform migration for a fast-scaling startup. You will design the layered cloud warehouse architecture (Raw $ ightarrow$ Staging $ ightarrow$ Marts), configure Change Data Capture (CDC) deduplication, build the dimensional marts, and write the executive migration playbook. ## 🏢 Business Scenario & Executive Prompt You have been hired as the Founding Data Engineer at **FinTrack**, a Series A fintech startup. Until now, all company business reporting has run directly against a read-replica of their production PostgreSQL transactional database. Last week, an analyst ran a quarterly churn analysis query that locked the replica, cascaded to the primary database, and blocked user deposits for 40 minutes. The CTO and founders have approved an immediate budget for a dedicated analytics data warehouse (Snowflake / BigQuery / Databricks). Your assignment is to deliver the complete architectural migration blueprint: 1. **Three-Layer Architecture:** Design the schema separation for Raw Ingestion (`raw.*`), Cleansed/Staging (`staging.*`), and Production Dimensional Marts (`marts.*`). 2. **CDC Ingestion & Deduplication:** Write the staging transformation views that handle streaming Change Data Capture (CDC) JSON records and deduplicate re-delivered rows. 3. **Production Dimensional Marts:** Deliver the core analytics star schema (`fct_orders`, `dim_customer`, `dim_date`) that will power all company reporting. 4. **Startup Migration Playbook:** Outline the phased 8-week migration schedule and strategy to decommission the PostgreSQL read-replica safely. --- ## 🎯 Required Deliverables You must upload a single `.sql` or `.zip` file containing: 1. **`warehouse_migration_architecture.sql`**: Production DDL establishing the complete 3-layer architecture (`raw`, `staging`, `marts`). 2. **`migration_playbook.md`**: An executive engineering playbook detailing the CDC strategy, cutover timeline, and mitigation for the 5 classic Series-A mistakes. 3. **`executive_kpi_queries.sql`**: 3 production queries powering the executive dashboard directly from the new dimensional marts. --- > *"You're hired by a Series A startup. Their analytics is currently running on read replicas of the production Postgres OLTP. Dashboards are slowing the app, metrics are inconsistent, and the data team wants a proper warehouse. Given their OLTP schema, plan the warehouse model."* This is the most common real-world greenfield warehouse case: a startup outgrowing its OLTP analytics. The interviewer wants to hear pragmatic step-by-step migration thinking, not a textbook plan. --- ## Step 1 — Profile the OLTP Before designing anything, inventory the OLTP: 1. **Which tables are loaded by the app?** (Get this from the engineering team or schema introspection.) 2. **What's the row count per table?** (Big tables = candidates for partitioning later; small tables = candidates for dimensions.) 3. **Which tables drive the most dashboard queries?** (Look at slow query logs, BI tool usage.) 4. **What are the natural foreign-key relationships?** (Helps map to star schema later.) A typical Series A startup OLTP might be: | Table | Approx rows | Notes | |---|---|---| | users | 200K | Source of dim_user | | products | 5K | Source of dim_product | | categories | 50 | Sub-dim of product | | orders | 1.5M | Becomes fct_orders | | order_items | 4.2M | Becomes fct_order_items | | payments | 1.5M | Could become fct_payments | | addresses | 300K | Becomes dim_address (snapshot per order) | | user_events | 50M | Becomes fct_user_events | Already we can sketch a starting star. ## Step 2 — Plan the staging layer first Set up Fivetran / Airbyte / native CDC to land OLTP tables into a `raw` schema in the warehouse, one table per OLTP source table, untransformed. ``` raw.users raw.products raw.orders raw.order_items raw.payments raw.user_events ``` Then `stg_*` models that lightly clean: - Rename camelCase → snake_case. - Cast types (string IDs to integers where appropriate). - Filter test/internal records. - Standardize timestamp columns to UTC. ```sql -- stg_orders SELECT id AS order_id, user_id AS customer_id, -- rename to canonical 'customer' across warehouse created_at AS order_placed_at, -- standardize naming status, total AS order_total FROM raw.orders WHERE NOT is_test_order; ``` This is the unglamorous step. Get it right and the rest is easy. ## Step 3 — Plan the mart layer Start with the most important facts: ### fct_order_items (line-item grain) The most analytically valuable fact. Build first. ### dim_customer Type 2 on country, segment, signup_channel. Type 1 on email, name. ### dim_product Type 2 on category. Type 1 on price (since current_price is a reference; orders carry their own price snapshot). ###