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.

-- 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).

dim_date

Pre-populated standard calendar. Five years back, two years forward.

fct_payments (separate fact)

One row per payment event. Joins to dim_customer (current state at payment time) and a payment_method dimension.

Later: fct_user_events

Big table; partitioned by event_date, clustered by user_id. Build after the others to learn from real query patterns.

Step 4 — Phase the migration over weeks

Week 1: Source landed in raw

Fivetran / CDC connectors live. Raw data flowing. No transformation yet.

Week 2: Staging models built

Every OLTP table has a stg_* model. Light cleaning only. Documented.

Week 3-4: First mart — fct_order_items + 4 dimensions

Validate against the OLTP. Build the first dashboard on the new warehouse.

Week 5-6: Migrate dashboards off the OLTP

Replace dashboard queries one by one. Validate metric consistency. Confirm the new dashboards are faster.

Week 7-8: Add more facts

fct_payments, fct_user_events, marketing facts as needed.

Week 9-10: Decommission OLTP read replicas for analytics

Turn off the read replica that BI was hitting. Save infrastructure cost. Confirm no dashboards broke.

10 weeks for a small team. Faster if you have a dedicated analytics engineer.

Step 5 — The dimensional decisions to call out

Conformed dim_customer

Joined by orders, payments, and user_events. Avoids the "marketing's customer is different from sales' customer" mess that grows in OLTP-backed analytics.

Type 2 strategically

Don't Type 2 every dimension attribute on day one. Pick the ones that genuinely matter for analytics (segment, country) and start there. Add more later.

Snapshot vs query-time joins for FX, prices, costs

If the business is multi-currency or has time-varying costs, snapshot the relevant value to the fact. (See Case 1 — Multi-Currency Financial Fact & Grain.)

Junk dimension for flags

Combine 10+ boolean flags into a junk dim. Saves columns on the fact.

Step 6 — Common Series-A mistakes

Mistake 1: Building marts before staging

Tempting because marts are visible. Painful because the second-source you add doesn't have a staging foundation to slot into. Always do staging first.

Mistake 2: 1:1 copy of OLTP tables

mart_orders that's just orders + a few joins. No dimensional thinking. Has the same problems as querying OLTP directly. Take time to model.

Mistake 3: Trying to model the entire OLTP at once

Start with one fact (orders), validate it works, expand from there. A 10-fact warehouse built in 2 weeks is unfinished in all 10 places.

Mistake 4: No SCDs

Year 1: nobody asks for history; you skip Type 2. Year 2: dimensions have all overwritten; finance needs history; you can't answer.

The cheapest insurance is to Type 2 the critical dimensions (customer, product) from day one. Even if you don't query history early, the data is captured.

Mistake 5: No documentation

Marts without documentation become unusable after the first analytics engineer leaves. dbt's description: fields and YAML configs cost nothing and pay off enormously.

Step 7 — Talk through the pitch

You: "I'd build this in three layers — raw, staging, marts — in parallel to the existing OLTP analytics. Week 1 sets up data landing. Weeks 2-4 build staging and the first mart, fct_order_items plus 4 dimensions. Weeks 5-8 migrate dashboards over and add more facts. Weeks 9-10 decommission the OLTP read replica.

Key decisions early: build conformed dim_customer from day one (because marketing and sales analytics will both need it), Type 2 SCDs on the attributes that genuinely matter for history (country, segment), pre-populate dim_date once and reuse forever, line-item grain for the orders fact for maximum flexibility.

The biggest risk is metric drift during migration. We'll run validation queries comparing OLTP-derived dashboards to warehouse-derived ones daily, catching discrepancies before they hit users."

Common follow-ups

Follow-up 1: "What CDC method do you use to extract from Postgres?"

Logical replication (Postgres native), Debezium, or Fivetran's connector. Logical replication is cheapest if engineering is willing to set it up; Fivetran is plug-and-play but $$$.

Follow-up 2: "What warehouse do you pick? Snowflake, BigQuery, Redshift, or Databricks?"

Depends on the existing stack:

• If on AWS heavily: Redshift (or Snowflake on AWS).
• If on GCP: BigQuery (zero ops, easiest start).
• If heavy data science: Databricks.
• Default for a Series A: Snowflake or BigQuery. Both are excellent; Snowflake gives more flexibility, BigQuery is cheaper to operate at small scale.

Follow-up 3: "How do you handle schema changes in the source OLTP?"

Source changes (added column) → CDC picks them up → raw table updates → staging needs explicit handling. dbt's dbt-source-status can detect schema drift. Plan for it; expect it monthly.

---

📥 Download Starter Assets & Mock Database

Choose your preferred environment to get started immediately:

• 💾 Pre-Seeded SQLite Database (Recommended — Zero Setup):
  ⬇ Download case6_oltp_to_warehouse.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 case6_oltp_to_warehouse_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 case6_oltp_to_warehouse.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 case6_oltp_to_warehouse_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: warehouse_migration_architecture.sql
-- ====================================================================

-- --------------------------------------------------------------------
-- LAYER 1: RAW INGESTION (raw.*) - Append-Only Source Mirror
-- --------------------------------------------------------------------
CREATE SCHEMA IF NOT EXISTS raw;

CREATE TABLE IF NOT EXISTS raw.orders (
    _airbyte_raw_id         VARCHAR(64) PRIMARY KEY,
    _airbyte_extracted_at   TIMESTAMPTZ NOT NULL,
    _airbyte_data           JSONB NOT NULL
);

-- --------------------------------------------------------------------
-- LAYER 2: STAGING (stg.*) - Cleansed, Typed, Deduplicated Views
-- --------------------------------------------------------------------
CREATE SCHEMA IF NOT EXISTS staging;

CREATE OR REPLACE VIEW staging.stg_orders AS
WITH ranked_orders AS (
    SELECT 
        (_airbyte_data->>'id')::BIGINT AS order_id,
        (_airbyte_data->>'customer_id')::BIGINT AS customer_id,
        (_airbyte_data->>'order_status')::VARCHAR AS order_status,
        (_airbyte_data->>'order_timestamp')::TIMESTAMPTZ AS order_timestamp,
        (_airbyte_data->>'order_total_usd')::NUMERIC(12, 2) AS order_total_usd,
        _airbyte_extracted_at,
        ROW_NUMBER() OVER (
            PARTITION BY (_airbyte_data->>'id') 
            ORDER BY _airbyte_extracted_at DESC
        ) AS row_num
    FROM raw.orders
)
SELECT 
    order_id,
    customer_id,
    order_status,
    order_timestamp,
    order_total_usd
FROM ranked_orders
WHERE row_num = 1;

-- --------------------------------------------------------------------
-- LAYER 3: PRODUCTION MARTS (marts.*) - Dimensional Star Schema
-- --------------------------------------------------------------------
CREATE SCHEMA IF NOT EXISTS marts;

CREATE TABLE IF NOT EXISTS marts.fct_orders (
    order_key               BIGINT PRIMARY KEY,
    order_id                BIGINT NOT NULL,
    order_date_key          INTEGER NOT NULL,
    customer_key            INTEGER NOT NULL,
    order_status            VARCHAR(50) NOT NULL,
    order_total_usd         NUMERIC(12, 2) NOT NULL,
    inserted_at             TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS marts.dim_customer (
    customer_key            INTEGER PRIMARY KEY,
    customer_id             BIGINT NOT NULL,
    email                   VARCHAR(255) NOT NULL,
    country                 VARCHAR(100) NOT NULL,
    effective_from          TIMESTAMPTZ NOT NULL,
    effective_to            TIMESTAMPTZ,
    is_current              BOOLEAN NOT NULL DEFAULT TRUE
);

---

📊 Evaluation Rubric (100 Points Total)

Evaluation Criterion	Weight	Target Standard (Pass ≥ 80%)
1. Layered Architecture Separation	25 pts	Strict separation of concerns between Raw Ingestion (raw.*), Cleansed/Staging (staging.*), and Business Dimensional Marts (marts.*).
2. Change Data Capture & Deduplication	25 pts	Staging models demonstrate resilient deduplication logic (ROW_NUMBER() OVER PARTITION BY id ORDER BY extracted_at DESC) to absorb CDC re-deliveries.
3. Dimensional Mart Standards	25 pts	Mart models follow Kimball principles: surrogate keys, conformed dimensions, atomic fact grains, and pre-computed additive metrics.
4. Series-A Anti-Pattern Avoidance	25 pts	Migration playbook avoids the 5 major startup pitfalls (no 1:1 OLTP copies, no building marts before staging, explicit SCD strategy, documentation).

---

📤 Submission Instructions & Guidelines

1. Deliverable Formats: Upload your completed .sql file or a .zip archive containing your SQL scripts and documentation (.md, .pdf, or ERD diagrams).
2. Dialect Compatibility: Ensure all DDL and queries execute cleanly on standard ANSI SQL, PostgreSQL, Snowflake, BigQuery, or Databricks.
3. Self-Review: Cross-check your solution against the 100-Point Evaluation Rubric above before submitting (passing threshold is 80/100 points).
4. Upload: Use the drag-and-drop file upload zone below. You can optionally detail architectural trade-offs in the submission notes box.
5. 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.