dbt-labs / dbt-labs/jaffle_shop_duckdb

Can payment_methods be defined once and referred multiple times?

Open
#52 0 comments 0 reactions 0 assignees View on GitHub
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.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.