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

# 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 %} ... {% enddocs %}` tags to define metrics that appear in multiple models or columns: ```markdown {% 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: ```bash 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` ```yaml 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_