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)

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', 'customers') }}.

Step 2 — Construct Staging Models

Staging models have a 1:1 relationship with source tables. Their job is to rename columns to snake_case, cast data types, convert currencies (cents to dollars), and filter soft-deleted or test records.

• Staging models should be materialized as view (configure this in dbt_project.yml).
• Keep logic light: no joins, no heavy aggregations.

Step 3 — Build the Intermediate Joining Layer

Build models/intermediate/finance/int_orders_with_payments.sql.

• Intermediate models isolate complex joins (e.g., LEFT JOIN stg_stripe__charges ON stg_stripe__charges.order_id = stg_shopify__orders.order_id).
• This model becomes the reusable single source of truth for downstream reporting.

Step 4 — Materialize the Final Mart

Build models/marts/finance/fct_weekly_revenue.sql.

• Configure this model as materialized='table'.
• Join int_orders_with_payments to stg_shopify__customers using {{ ref() }}.
• Aggregate metrics: DATE_TRUNC('week', order_placed_at), country, segment, order counts, and total revenue.

Step 5 — Write Schema Tests and Documentation

Create models/marts/finance/schema.yml.

• Add descriptions to models and columns.
• Declare not_null tests on week, country, total_revenue_usd.
• Add a surrogate key test or composite uniqueness check.

Step 6 — Execute Reconciliation Validation

Write analysis/reconciliation.sql to execute a full diff:

WITH legacy AS (
    -- Legacy query output
    SELECT SUM(total_revenue_usd) AS legacy_revenue FROM legacy_output
),
dbt_mart AS (
    -- Refactored dbt model output
    SELECT SUM(total_revenue_usd) AS mart_revenue FROM {{ ref('fct_weekly_revenue') }}
)
SELECT
    legacy.legacy_revenue,
    dbt_mart.mart_revenue,
    (dbt_mart.mart_revenue - legacy.legacy_revenue) AS revenue_delta
FROM legacy, dbt_mart;

Acceptance criteria: revenue_delta must equal 0.00.

---

💻 Starter Scaffold & Architecture Templates

1. models/staging/shopify/_shopify__sources.yml

version: 2

sources:
  - name: shopify
    database: raw
    schema: shopify
    tables:
      - name: customers
        description: "Raw customer accounts from Shopify e-commerce."
      - name: orders
        description: "Raw order checkouts."

  - name: stripe
    database: raw
    schema: stripe
    tables:
      - name: charges
        description: "Payment gateway charges and status."

2. models/staging/shopify/stg_shopify__customers.sql

WITH source AS (
    SELECT * FROM {{ source('shopify', 'customers') }}
),

renamed AS (
    SELECT
        id::bigint AS customer_id,
        LOWER(TRIM(email)) AS email,
        country,
        segment_calculated AS customer_segment,
        created_at AS customer_created_at
    FROM source
    WHERE NOT COALESCE(is_test, FALSE)
)

SELECT * FROM renamed

3. models/staging/shopify/stg_shopify__orders.sql

WITH source AS (
    SELECT * FROM {{ source('shopify', 'orders') }}
),

renamed AS (
    SELECT
        id::bigint AS order_id,
        customer_id::bigint AS customer_id,
        total_price AS order_total_usd,
        status AS order_status,
        created_at AS order_placed_at
    FROM source
)

SELECT * FROM renamed

4. models/staging/stripe/stg_stripe__charges.sql

WITH source AS (
    SELECT * FROM {{ source('stripe', 'charges') }}
),

renamed AS (
    SELECT
        id AS charge_id,
        order_id::bigint AS order_id,
        amount::numeric / 100.0 AS amount_paid_usd,
        status AS charge_status,
        created_at AS charged_at
    FROM source
    WHERE status = 'succeeded'
)

SELECT * FROM renamed

5. models/intermediate/finance/int_orders_with_payments.sql

WITH orders AS (
    SELECT * FROM {{ ref('stg_shopify__orders') }}
),

payments AS (
    SELECT * FROM {{ ref('stg_stripe__charges') }}
),

joined AS (
    SELECT
        orders.order_id,
        orders.customer_id,
        orders.order_placed_at,
        orders.order_total_usd,
        payments.charge_id,
        COALESCE(payments.amount_paid_usd, 0.0) AS paid_amount_usd,
        payments.charged_at
    FROM orders
    LEFT JOIN payments ON payments.order_id = orders.order_id
)

SELECT * FROM joined

6. models/marts/finance/fct_weekly_revenue.sql

{{ config(materialized='table') }}

WITH orders_with_payments AS (
    SELECT * FROM {{ ref('int_orders_with_payments') }}
),

customers AS (
    SELECT * FROM {{ ref('stg_shopify__customers') }}
),

joined AS (
    SELECT
        o.order_id,
        o.order_placed_at,
        o.paid_amount_usd,
        c.country,
        c.customer_segment
    FROM orders_with_payments o
    JOIN customers c ON c.customer_id = o.customer_id
),

aggregated AS (
    SELECT
        DATE_TRUNC('week', order_placed_at)::date AS revenue_week,
        country,
        customer_segment,
        COUNT(DISTINCT order_id) AS orders_count,
        SUM(paid_amount_usd) AS total_revenue_usd,
        ROUND(SUM(paid_amount_usd) / NULLIF(COUNT(DISTINCT order_id), 0), 2) AS avg_order_value_usd
    FROM joined
    GROUP BY 1, 2, 3
)

SELECT * FROM aggregated

---

📊 100-Point Evaluation Rubric

Evaluation Dimension	Weight	Target Standard (Pass ≥ 80%)
1. DAG Layering & Separation of Concerns	25 pts	Clean architectural separation: Staging models isolate raw inputs 1:1, intermediate models handle joins, and marts handle dimensional rollups.
2. Correct Lineage & Macros	25 pts	Zero hardcoded raw table paths; all source models use {{ source() }} and downstream relations use {{ ref() }} with clean DAG visualization in dbt docs.
3. Financial Parity & Reconciliation	25 pts	reconciliation.sql produces exact revenue match against legacy query output; zero cent discrepancy and zero duplicated orders.
4. Testing, Documentation & Code Style	25 pts	Consistent SQL naming (stg_, int_, fct_), standardized CTE pattern (source, renamed, joined), and generic tests (not_null, unique) declared in schema YAML.

---

📤 Submission Instructions & Guidelines

1. Deliverable Formats:
   • Option A: Upload a single .zip archive containing your models/ and analysis/ directories along with your dbt_project.yml.
   • Option B: Upload your primary SQL mart file (fct_weekly_revenue.sql) and paste your GitHub PR or repository link in the Submission Notes.
2. Dialect Compatibility: Ensure your SQL and Jinja templates conform to standard ANSI SQL, Snowflake, BigQuery, Databricks, or PostgreSQL.
3. Pre-Submission Checklist:
   • Execute dbt run and verify all models build without syntax errors.
   • Execute dbt test and confirm all declared schema tests pass.
   • Run your reconciliation query to verify zero metric drift.
4. Submit Your Work:
   • Use the file dropzone below to upload your project file.
   • Include any commentary or trade-off decisions in the Submission Notes box.
   • Click Submit Assignment to record your submission and complete the milestone.