SUBMISSION: Milestone Project 1 — Refactor a Monolithic SQL Query into a Clean dbt Project DAG

# SUBMISSION: Milestone Project 1 — Refactor a Monolithic SQL Query into a Clean dbt Project DAG > 🎯 **Hands-on Milestone Deliverable:** In this milestone project, you will act as the Analytics Engineer refactoring a company's most critical revenue reporting pipeline. You will untangle a 200-line monolithic legacy query into a maintainable, multi-layer dbt DAG and submit your complete dbt model files. --- ## 🏢 Business Scenario & Executive Prompt You have joined **Apex Global**, a high-volume B2B e-commerce merchant. On your first week, the VP of Finance shows you the SQL query powering the executive board deck: a 200-line monolithic script containing 8 nested CTEs, joins across 4 disparate physical schemas, and three conflicting definitions of customer revenue. Whenever a column name changes upstream in PostgreSQL, the entire query breaks, causing hours of panic before board meetings. Furthermore, the query takes 18 minutes to run and cannot be reused by other departments. Your task is to refactor this query into an industry-standard **dbt project architecture**: 1. Isolate raw source tables into clean staging models (`stg_`). 2. Encapsulate multi-source joins into reusable intermediate models (`int_`). 3. Materialize user-facing dimensional marts (`fct_` and `dim_`) with full DAG lineage. 4. Author a financial reconciliation query proving zero dollar loss down to the cent. --- ## 📋 Upstream Source Schemas & Legacy Monolith ### Upstream Raw Sources Apex Global ingests raw data from Shopify, Stripe, Google Ads, and Facebook Ads: 1. **`raw.shopify.customers`**: `id` (bigint), `email` (varchar), `country` (varchar), `segment_calculated` (varchar), `is_test` (boolean), `created_at` (timestamp). 2. **`raw.shopify.orders`**: `id` (bigint), `customer_id` (bigint), `total_price` (numeric), `status` (varchar), `created_at` (timestamp). 3. **`raw.stripe.charges`**: `id` (varchar), `order_id` (bigint), `amount` (bigint in cents), `status` (varchar), `created_at` (timestamp). 4. **`raw.google_ads.clicks`** & **`raw.facebook_ads.clicks`**: `click_id` (varchar), `user_id` (bigint), `click_time` (timestamp). ### The Monolithic Legacy Query (To Be Refactored) ```sql WITH raw_customers AS ( SELECT id, email, country, segment_calculated AS segment, created_at FROM raw.shopify.customers WHERE NOT COALESCE(is_test, FALSE) ), raw_orders AS ( SELECT id, customer_id, total_price, created_at FROM raw.shopify.orders WHERE created_at >= '2024-01-01' ), raw_payments AS ( SELECT id, order_id, amount / 100.0 AS amount_usd, status, created_at FROM raw.stripe.charges WHERE status = 'succeeded' ), marketing_clicks AS ( SELECT click_id, user_id, click_time, 'google' AS channel FROM raw.google_ads.clicks UNION ALL SELECT click_id, user_id, click_time, 'facebook' AS channel FROM raw.facebook_ads.clicks ), orders_joined_payments AS ( SELECT o.id AS order_id, o.customer_id, o.created_at AS order_placed_at, o.total_price, p.id AS charge_id, COALESCE(p.amount_usd, 0.0) AS paid_amount FROM raw_orders o LEFT JOIN raw_payments p ON p.order_id = o.id ), final_metrics AS ( SELECT DATE_TRUNC('week', o.order_placed_at)::date AS week, c.country, c.segment, COUNT(DISTINCT o.order_id) AS orders_count, SUM(o.paid_amount) AS total_revenue_usd, SUM(o.paid_amount) / NULLIF(COUNT(DISTINCT o.order_id), 0) AS avg_order_value_usd FROM orders_joined_payments o JOIN raw_customers c ON c.id = o.customer_id GROUP BY 1, 2, 3 ) SELECT * FROM final_metrics ORDER BY week DESC, total_revenue_usd DESC; ``` --- ## 🎯 Required Deliverables Your refactored dbt project must be organized into clear architectural layers. Submit a `.zip` archive or link to your GitHub repository containing: 1. **`models/staging/shopify/_shopify__sources.yml`**: Declaring sources with freshness SLAs. 2. **`models/staging/shopify/stg_shopify__customers.sql`**: Staging customer model filtering test users. 3. **`models/staging/shopify/stg_shopify__orders.sql`**: Staging orders model. 4. **`models/staging/stripe/stg_stripe__charges.sql`**: Staging payments model converting cents to dollars. 5. **`models/intermediate/finance/int_orders_with_payments.sql`**: Reusable intermediate join between orders and payments. 6. **`models/marts/finance/fct_weekly_revenue.sql`**: Final dimensional revenue mart table. 7. **`models/marts/finance/schema.yml`**: Schema tests (`unique`, `not_null`) on `fct_weekly_revenue`. 8. **`analysis/reconciliation.sql`**: Automated test query reconciling revenue between the legacy query and your new mart. --- ## 🛠️ Step-by-Step Implementation Guide ### Step 1 — Declare Sources with Freshness Create `models/staging/shopify/_shopify__sources.yml` and declare the source tables. Never query raw tables with raw database prefixes like `raw.shopify.customers`; always use `{{ source('shopify', 'custome