Capstone: add returns to the pipeline

The request

Finance now wants recognized revenue after partial returns. A return should reduce revenue on the date the return happened, not on the original order date.

Extend the same project instead of starting over. Your finished model should answer:

What was each customer's net recognized revenue on each activity date?

One row represents one customer on one activity date. The two columns customer_id + activity_date identify each row together.

For this capstone, assume every return references an earlier paid order and has the same source currency as that order. A return linked to an unknown or non-paid order needs a visible exception or a failing check. Real finance reporting also needs an approved policy for return eligibility and revenue recognition.

Plan with your coding agent

Add the return source

Create commerce/assets/raw/returns.csv:

return_id,order_id,return_ts,return_amount
R001,1001,2026-01-02 11:00:00,10.00
R002,1006,2026-01-04 09:30:00,20.00

The final source state should also keep order 1005 as paid, following the earlier source-correction exercise.

Build the extension

Add these assets:

1. raw.returns, a DuckDB seed for the return file.
2. staging.stg_returns, which standardizes IDs, timestamps, dates, and amount.
3. analytics.fct_returns, which joins returns to orders to find the customer.
4. analytics.customer_daily_net_revenue, which combines positive paid-order events and negative return events.

Use the patterns from the course, but write the SQL yourself. The finished table flow should be:

raw.orders -> staging.stg_orders -> analytics.fct_orders ------┐
                                                               ├-> analytics.customer_daily_net_revenue
raw.returns -> staging.stg_returns -> analytics.fct_returns ---┘

raw.customers -> staging.stg_customers -> analytics.dim_customers
                                                               └-> analytics.customer_daily_net_revenue

Required behaviour

The net revenue reporting table must:

• Use one row per customer per activity date.
• Keep paid order revenue positive and return amounts negative.
• Include recognized_revenue, returned_amount, and net_revenue as separate columns.
• Treat recognized_revenue as this course's paid-order metric, not an accounting revenue-recognition calculation.
• Join each return to exactly one order and each customer to exactly one dimension row.
• Use time_interval to replace the requested activity_date range on each run.
• Filter events with both start_date and end_date included, matching the dates Bruin deletes.
• Reject null keys, duplicate return IDs, negative return amounts, and duplicate customer-date rows.
• Document the return timing rule, model owner, business owner, and source-currency limitation.

If a return references an unknown order, fail a check or route it to a visible exception model. Do not silently drop it.

Build with your coding agent

Prove the result

Validate, render, and run the complete interval:

bruin validate commerce/pipeline.yml
bruin render commerce/assets/analytics/customer_daily_net_revenue.sql \
  --start-date 2026-01-01 \
  --end-date 2026-01-04
bruin run commerce/pipeline.yml \
  --start-date 2026-01-01 \
  --end-date 2026-01-04 \
  --full-refresh

Query the reporting table:

bruin query --connection duckdb-default \
  --description "inspect capstone customer daily net revenue rows" \
  --query "SELECT customer_id, activity_date, recognized_revenue, returned_amount, net_revenue FROM analytics.customer_daily_net_revenue ORDER BY activity_date, customer_id;"

Your seven customer-date rows should match these net values:

The total net revenue should be 291.50.

Run January 2 through January 4 twice without --full-refresh:

bruin run commerce/pipeline.yml \
  --start-date 2026-01-02 \
  --end-date 2026-01-04

bruin run commerce/pipeline.yml \
  --start-date 2026-01-02 \
  --end-date 2026-01-04

Row counts and totals must remain unchanged.

Capture local review evidence

Update the Bruin header in analytics.customer_daily_net_revenue with the return timing rule, model owner, business owner, and source-currency limitation. Then inspect the local diff and confirm the checked output and repeat-run results match this lesson.

Use this rubric:

A score of 10 out of 10 means the capstone meets the course requirements. Fix any missing row definition, join check, data check, or repeat-run evidence before treating the model as ready for shared use.

Optional extensions

The core course ends with the SQL project. If your project also uses Python, continue with Python assets and Python materialization. If your team uses coding agents, add Bruin MCP after the project has tests and a review process.