Own the Data

This is the final SQL project. You will take one messy marketplace extract the whole way: audit the raw tables, model a clean schema, load it, answer hard questions, make one important query easier for the planner, and leave checks behind.

The browser cannot load files from your machine, so the raw staging tables are already loaded for you. In production, those rows might arrive from raw files or an ELT tool. Here, your live work starts from raw_* tables and uses normal SQL to create the modeled layer.

What you'll build

You will produce a project notebook with six sections:

• Raw extract audit: counts, dates, statuses, regions, channels, duplicate emails, and sample rows.
• Clean model: dimensions and facts with primary keys, foreign keys, status checks, and a clear customer de-duplication rule.
• Load SQL: INSERT INTO clean_table (...) SELECT ... FROM raw_* statements that transform raw rows into modeled tables.
• Analytics suite: monthly revenue, top products, top customers, a running revenue window, support rate, and refund/cancel impact.
• Performance proof: an index, an EXPLAIN (COSTS OFF) before or after, and a short explanation of the plan shape.
• Handoff package: data-quality checks, table comments or documentation notes, and review-ready assumptions.

Use these conventions unless you consciously document a different choice:

• A clean customer is identified by normalized email: lower(email).
• If the same email appears more than once, keep the smallest raw_customer_id as the clean customer_id.
• Completed revenue includes orders with status paid or shipped.
• Net item revenue is quantity * unit_price * (100 - discount_pct) / 100.0.
• Cancelled and refunded orders still matter for operations, but they do not count as completed revenue.

Workspace

Run the starter audit first. Then replace the TODO sections with your own schema, loads, reports, and checks.

Checkpoint 1: Audit The Raw Extract

Before modeling anything, prove you understand the raw inputs.

Answer these questions:

1. How many rows are in each raw table?
2. What order date range is covered?
3. How many distinct order statuses, shipping regions, and channels appear?
4. Which customer emails duplicate after normalization?
5. How many orders are in each status?

Acceptance criteria:

• Raw table counts are raw_customers 11, raw_products 10, raw_orders 48, raw_order_items 92, and raw_support_cases 16.
• Orders run from 2026-01-03 through 2026-04-16.
• The extract has 4 statuses, 4 shipping regions, and 4 channels.
• mira@example.com appears in 2 raw customer rows after lower(email).
• Status counts are cancelled 6, paid 24, refunded 6, and shipped 12.

Checkpoint 2: Model And Load The Clean Layer

Create a clean schema that a reviewer could understand without reading the raw extract:

• dim_customers(customer_id, email, full_name, signup_date, region, acquisition_channel)
• dim_products(product_id, sku, product_name, category, list_price, is_active)
• fact_orders(order_id, customer_id, ordered_on, status, channel, ship_region)
• fact_order_items(item_id, order_id, product_id, quantity, unit_price, discount_pct, net_amount)
• fact_support_cases(case_id, customer_id, order_id, opened_on, issue_type, priority, resolved_on, satisfaction_score)

Use primary keys, foreign keys, and checks where they make the model safer. Load each table with INSERT INTO ... SELECT from the raw tables. For customers, normalize email and collapse duplicates by keeping the smallest raw id as the clean customer_id.

Acceptance criteria:

• Clean table counts are dim_customers 10, dim_products 10, fact_orders 48, fact_order_items 92, and fact_support_cases 16.
• The duplicate raw email collapses to one clean mira@example.com customer.
• Every fact_orders.customer_id points at dim_customers.customer_id.
• Every fact_order_items.order_id points at fact_orders.order_id, and every fact_order_items.product_id points at dim_products.product_id.
• fact_order_items.net_amount uses the convention above and should be stored or computed consistently to two decimal places.

Checkpoint 3: Ship The Analytics Suite

Now answer questions that use the clean model, not the raw staging tables.

Required reports:

1. Monthly completed net revenue.
2. Top products by completed net revenue.
3. Top customers by completed net revenue.
4. Running completed revenue by month with a window function.
5. Support rate for completed orders.
6. Cancelled and refunded item value.

Acceptance criteria:

• Monthly completed net revenue is 3325.05 for January, 3087.35 for February, 2690.60 for March, and 1437.55 for April.
• Running completed revenue ends at 10540.55.
• The top product by completed net revenue is Noise-Canceling Headphones with 2547.00. The next two are Mechanical Keyboard with 1767.30 and Monitor Arm with 1265.00.
• The top customer is elle@example.com with 1704.65 completed net revenue. The next two are ben@example.com with 1512.70 and ava@example.com with 1481.30.
• Completed orders total 36. Completed orders with a support case total 14, so the support rate is 38.9%.
• Cancelled orders have 1801.00 item value; refunded orders have 1552.90 item value.

Checkpoint 4: Prove One Performance Improvement

Pick the report you would expect to run often. A good target is completed revenue by date because it filters by status, groups by date, and joins order items.

Add one index that matches your query shape. For example, a partial index on completed orders can support reports that repeatedly filter status IN ('paid', 'shipped') and read ordered_on:

• Create an index with a clear name such as idx_fact_orders_completed_date.
• Run EXPLAIN (COSTS OFF) on the completed monthly revenue query.
• Explain whether the plan uses your index. If the tiny browser dataset still prefers a sequential scan, say so honestly and verify that the index exists.

Acceptance criteria:

• pg_indexes shows exactly 1 index named idx_fact_orders_completed_date on fact_orders (your primary key's automatic index will also be listed — that one does not count against this criterion).
• Your handoff notes include the report query, the index definition, the plan shape, and whether the index actually appears in the plan.
• You do not claim a timing win from this small dataset. Use plan structure, not milliseconds.

Checkpoint 5: Quality, Security, And Handoff

Finish like the database person from 23.3: leave proof behind.

Write quality checks that return zero bad rows or one pass/fail summary row for:

• duplicate clean customer emails
• orphan item orders
• orphan item products
• invalid order statuses
• non-positive item quantities
• support cases pointing at missing clean customers

Then add a short handoff note in SQL comments or table comments that explains the grain of each fact table, the completed-revenue convention, and the duplicate-customer rule. If you want a security stretch, create a read-only role and grant it SELECT on the clean tables.

Acceptance criteria:

• The required quality checks all return 0 bad rows.
• If you use table comments, at least fact_orders and fact_order_items have non-empty comments.
• If you add a read-only role, it can read the clean tables but should not be used for writes.
• Your final notebook has stable ORDER BY clauses on every multi-row report.

Stretch Goals

Try these after the required checkpoints pass:

• Add a customer-month cohort report with retention-style flags.
• Rank products within category by completed revenue using RANK().
• Add a seven-day moving average of completed revenue by order date.
• Compare raw revenue to clean completed revenue and explain the cancelled/refunded gap.
• Add a small data_dictionary table that documents each clean column and its grain.

How To Get Unstuck

Use 10.1 and 10.2 when the model feels vague: write the relationship sentence first, then choose keys. Use 17.1 when a metric feels wrong: name the grain before writing the aggregate. Use 23.2 when you are unsure whether a query is safe to ship: turn the assumption into a zero-bad-rows assertion.

For performance, keep Section 16's discipline. EXPLAIN (COSTS OFF) tells you shape; it does not promise production timing. For handoff, keep 23.1's review order: grain, join keys, NULL story, then style.