SUBMISSION: Capstone 1 — Add Enterprise Tests & Documentation to a Legacy dbt Project

🎯 Portfolio Capstone Deliverable: Harden an untested 40-model legacy dbt codebase into an enterprise-grade, certified production data platform with exhaustive test coverage and automated documentation.

---

🏢 Business Scenario & Executive Prompt

You inherit an established dbt repository with 40 models powering all corporate reporting. Tests: zero. Descriptions: zero. Last month, an engineer accidentally altered a join condition that doubled all reported Monthly Recurring Revenue (MRR) for 10 days before an external auditor caught it.

The VP of Data has assigned you to establish an Enterprise Data Quality & Documentation Suite that halts faulty PR builds and provides clear data dictionaries for business stakeholders.

Your deliverables must cover:

1. Generic Schema Tests: Uniqueness, not-null, referential integrity, and accepted values.
2. Custom Singular Business Logic Test: A financial assertion verifying zero dollar divergence between reported revenue and payment gateway records.
3. Shared Markdown Doc Blocks: Centrally maintained descriptions for core enterprise metrics.

---

📋 Project Context & Scope

Your project contains three primary dimensional mart models:

1. fct_orders: Grain is one row per order. Key metrics: order_id, customer_id, order_total_usd, order_status.
2. dim_customers: Grain is one row per customer. Key metrics: customer_id, customer_segment, lifetime_spend_usd.
3. stg_stripe__charges: Payment provider charges. Key metrics: charge_id, order_id, amount_paid_usd, charge_status.

---

🎯 Required Deliverables

Submit a .zip archive or GitHub repo link containing:

1. models/marts/core/schema.yml: Generic tests on all core mart models (unique, not_null, relationships, accepted_values).
2. tests/assert_revenue_reconciliation.sql: A singular SQL assertion test verifying zero financial divergence between orders and payments.
3. models/docs/metrics.md: Reusable markdown docs blocks for core business definitions (mrr, active_customer, gross_revenue).
4. models/marts/core/fct_orders.sql: Model incorporating doc block references via {{ doc('...') }}.

---

🛠️ Step-by-Step Implementation Guide

Step 1 — Generic Schema Tests (models/marts/core/schema.yml)

Configure generic tests for fct_orders and dim_customers:

• Uniqueness & Not-Null: Assert that order_id in fct_orders and customer_id in dim_customers are unique and non-null.
• Referential Integrity: Assert that every customer_id in fct_orders exists in dim_customers.
• Accepted Values: Assert that order_status in fct_orders only contains valid lifecycle statuses ('completed', 'pending', 'cancelled', 'refunded').

Step 2 — Author Singular Business Test (tests/assert_revenue_reconciliation.sql)

In dbt, a singular test is a SQL query stored under tests/.

• The test fails if the query returns any rows.
• Write a query that computes total revenue from fct_orders and compares it to total charges from stg_stripe__charges.
• If the difference exceeds $0.05, return a failure record.

Step 3 — Create Markdown Doc Blocks (models/docs/metrics.md)

Use {% docs <block_name> %} ... {% enddocs %} tags to define metrics that appear in multiple models or columns:

{% docs gross_revenue %}
Gross revenue is the total transaction volume billed to customers,
before deductions for refunds, chargebacks, and payment processing fees.
Calculated in USD.
{% enddocs %}

Step 4 — Link Doc Blocks into Model Schemas

In your schema.yml, reference your doc block using description: "{{ doc('gross_revenue') }}". This guarantees consistent business definitions across your entire documentation site!

Step 5 — Verify Your Quality Suite

Run the full verification suite:

dbt test                              # executes both generic schema tests and singular tests
dbt docs generate                     # compiles documentation catalog
dbt docs serve                        # verifies doc blocks render properly in the browser

---

💻 Starter Scaffold

1. models/marts/core/schema.yml

version: 2

models:
  - name: fct_orders
    description: "Central order transactions fact table."
    columns:
      - name: order_id
        description: "Primary key."
        tests:
          - unique
          - not_null
      - name: customer_id
        description: "Foreign key to customer."
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id
      - name: order_status
        tests:
          - accepted_values:
              values: ['completed', 'pending', 'cancelled', 'refunded']
      - name: order_total_usd
        description: "{{ doc('gross_revenue') }}"
        tests:
          - not_null

  - name: dim_customers
    description: "Customer dimensional table."
    columns:
      - name: customer_id
        tests:
          - unique
          - not_null

2. tests/assert_revenue_reconciliation.sql

-- Singular test: Fails if difference between orders revenue and stripe charges exceeds 5 cents.
WITH order_totals AS (
    SELECT
        ROUND(SUM(order_total_usd), 2) AS total_order_revenue
    FROM {{ ref('fct_orders') }}
    WHERE order_status = 'completed'
),

stripe_totals AS (
    SELECT
        ROUND(SUM(amount_paid_usd), 2) AS total_stripe_revenue
    FROM {{ ref('stg_stripe__charges') }}
    WHERE charge_status = 'succeeded'
),

reconciliation AS (
    SELECT
        o.total_order_revenue,
        s.total_stripe_revenue,
        ABS(o.total_order_revenue - s.total_stripe_revenue) AS discrepancy
    FROM order_totals o
    CROSS JOIN stripe_totals s
)

-- Return rows ONLY when discrepancy exceeds 5 cents (0.05)
SELECT *
FROM reconciliation
WHERE discrepancy > 0.05

3. models/docs/metrics.md

{% docs gross_revenue %}
Total billed transaction volume in USD across completed orders,
excluding taxes and shipping where applicable.
Single source of truth for corporate reporting.
{% enddocs %}

{% docs customer_lifetime_value %}
Cumulative revenue generated by an individual customer across all historical
orders since initial signup date.
{% enddocs %}

---

📊 100-Point Evaluation Rubric

Evaluation Dimension	Weight	Target Standard (Pass ≥ 80%)
1. Generic Test Rigor	25 pts	Complete coverage of unique, not_null, relationships, and accepted_values across core mart tables.
2. Singular Test Logic	25 pts	Singular SQL test accurately computes financial discrepancy and adheres to dbt convention (returns rows on failure).
3. Centralized Doc Blocks	25 pts	Clean {% docs %} blocks authored in markdown and linked into YAML schemas via {{ doc('...') }}.
4. CLI Execution & Coverage	25 pts	dbt test runs cleanly; documentation builds successfully without syntax or broken link errors.

---

📤 Submission Instructions & Guidelines

1. Deliverable Formats: Upload your completed .yml, .sql, and .md files in a .zip archive, or upload assert_revenue_reconciliation.sql and provide your GitHub PR URL in the Submission Notes.
2. Dialect Compatibility: Ensure SQL expressions execute cleanly on standard ANSI SQL, Snowflake, BigQuery, Databricks, or PostgreSQL.
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.
5. Confirmation: Click Submit Assignment to complete Capstone 1.