dbt-labs / dbt-labs/jaffle_shop_duckdb
Can payment_methods be defined once and referred multiple times?
- Dominant language
- Shell
- Stars
- 288
- Forks
- 235
- PR merge metrics
- No merged PRs in 30d
Description
The accepted values for `payment_method` field for `stg_customers` are defined in `models/staging/schema.yml'.
```yml
version: 2
models:
- name: stg_customers
columns:
- name: customer_id
tests:
- unique
- not_null
- name: stg_orders
columns:
- name: order_id
tests:
- unique
- not_null
- name: status
tests:
- accepted_values:
values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']
- name: stg_payments
columns:
- name: payment_id
tests:
- unique
- not_null
- name: payment_method
tests:
- accepted_values:
values: ['credit_card', 'coupon', 'bank_transfer', 'gift_card']
```
These values are used in `models/orders.sql`.
```sql
-- ...
order_payments as (
select
order_id,
{% for payment_method in payment_methods -%}
sum(case when payment_method = '{{ payment_method }}' then amount else 0 end)
as {{ payment_method }}_amount,
{% endfor -%}
sum(amount) as total_amount
from payments
group by order_id
),
```
Can `payment_methods` defined once in `models/staging/schema.yml', so that we could avoid data inconsistencies in the future. If it is possible, how can I do it?
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.