5. Pivot Payments with a Jinja Loop

Difficulty: Medium · Topic: Jinja, vars & packages

Turn {{ ref('stg_payments') }} into one row per order with one column per payment method. The methods are credit_card, coupon, bank_transfer and gift_card.

Return order_id, then credit_card_amount, coupon_amount, bank_transfer_amount, gift_card_amount and total_amount. Only payments with status = 'success' count; a method an order did not use is 0, never null. Every order that has any payment row appears, even if none succeeded.

Write the four method columns with a Jinja {% for %} loop over a list, so adding a method later is a one-word change.

Your model is compiled like dbt does and run in DuckDB against 3 test cases, including hidden edge cases.

Sample inputs

{{ ref('stg_payments') }}

payment_id	order_id	method	status	amount
1	100	credit_card	success	30
2	100	coupon	success	5
3	101	bank_transfer	success	12.5
4	101	credit_card	fail	12.5
5	102	gift_card	success	20

Expected output

order_id	credit_card_amount	coupon_amount	bank_transfer_amount	gift_card_amount	total_amount
100	30	5	0	0	35
101	0	0	12.5	0	12.5
102	0	0	0	20	20

Hints

Hint 1

{% set payment_methods = ['credit_card', 'coupon', 'bank_transfer', 'gift_card'] %} at the top, then loop over it inside the SELECT.

Hint 2

Each column is sum(case when method = '{{ m }}' and status = 'success' then amount else 0 end) as {{ m }}_amount. Without else 0 a missing method sums to null.

Hint 3

Watch the commas: {{ ',' if not loop.last }} after each column, or put the loop before total_amount and end every column with a comma.

More dbt problems

• Daily Revenue for a Reporting Window · Easy
• Smaller Builds in Development · Medium
• Surrogate Key for Order Lines · Medium

All dbt problems