9. Data Test: Refunds Larger Than the Order
Difficulty: Easy · Topic: Data tests
A singular data test in dbt is a SELECT in tests/ that returns the rows that break a rule; the test passes when it returns nothing.
Write assert_refunds_within_order_amount: an order can be refunded in several parts, but the total refunded must never exceed the order's amount. Return one row per violating order with order_id, order_amount and refunded_amount (the total refunded).
Refunding exactly the order amount is allowed.
Your model is compiled like dbt does and run in DuckDB against 3 test cases, including hidden edge cases.
Sample inputs
{{ ref('stg_orders') }}
| order_id | customer_id | ordered_on | status | amount |
|---|---|---|---|---|
| 1 | 10 | 2024-01-01 | completed | 50 |
| 2 | 11 | 2024-01-02 | completed | 20 |
| 3 | 12 | 2024-01-03 | completed | 30 |
{{ ref('stg_refunds') }}
| refund_id | order_id | refunded_at | amount |
|---|---|---|---|
| 900 | 1 | 2024-01-05 | 30 |
| 901 | 1 | 2024-01-06 | 25 |
| 902 | 2 | 2024-01-07 | 5 |
| 903 | 3 | 2024-01-08 | 30 |
Expected output
| order_id | order_amount | refunded_amount |
|---|---|---|
| 1 | 50 | 55 |
Hints
Hint 1
Sum refunds per order first, then compare that total with the order amount.Hint 2
Comparing each refund with the order amount misses orders refunded in several small parts.Hint 3
Only orders that have refunds can fail, so an inner join between orders and the refund totals is fine here.More dbt problems
- A Reusable not_negative Test · Medium
- Orphaned Rows: a relationships Test · Medium