Refunds Accounting

-- models/refunds_accounting.sql
{{ config(materialized='view') }}

select
DATE_FORMAT(coalesce(r.created_on,oic.created_on),'%Y-%m') as created_month,
sum(cost) as cost,
sum(vendor_cost) as vendor_cost
from
returns rt
left join
refunds r
ON r.order_item_id = rt.order_item_id
and
{% condition refund_date %} r.created_on {% endcondition %}
left join
order_item_credits oic
ON oic.order_item_id = rt.order_item_id
and
{% condition refund_date %} oic.created_on {% endcondition %}
where
r.id is not null OR oic.id is not null
group by 1

# models/schema.yml
version: 2

models:
  - name: refunds_accounting
    columns:
      - name: cost
      - name: created_month
      - name: vendor_cost

Assumptions

• materialized='view' is the default; no persist hint found.
• schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

select
DATE_FORMAT(coalesce(r.created_on,oic.created_on),'%Y-%m') as created_month,
sum(cost) as cost,
sum(vendor_cost) as vendor_cost
from
returns rt
left join
refunds r
ON r.order_item_id = rt.order_item_id
and
{% condition refund_date %} r.created_on {% endcondition %}
left join
order_item_credits oic
ON oic.order_item_id = rt.order_item_id
and
{% condition refund_date %} oic.created_on {% endcondition %}
where
r.id is not null OR oic.id is not null
group by 1

view: refunds_accounting {
  derived_table: {
    sql: select
DATE_FORMAT(coalesce(r.created_on,oic.created_on),'%Y-%m') as created_month,
sum(cost) as cost,
sum(vendor_cost) as vendor_cost
from
returns rt
left join
refunds r
ON r.order_item_id = rt.order_item_id
and
{% condition refund_date %} r.created_on {% endcondition %}
left join
order_item_credits oic
ON oic.order_item_id = rt.order_item_id
and
{% condition refund_date %} oic.created_on {% endcondition %}
where
r.id is not null OR oic.id is not null
group by 1 ;;
  }

  dimension: cost {
    type: number
    sql: ${TABLE}.cost ;;
  }

  dimension: created_month {
    sql: ${TABLE}.created_month ;;
  }

  dimension: vendor_cost {
    type: number
    sql: ${TABLE}.vendor_cost ;;
  }

  measure: total_cost {
    type: sum
    sql: ${TABLE}.cost ;;
  }

  measure: total_vendor_cost {
    type: sum
    sql: ${TABLE}.vendor_cost ;;
  }
}

- view: refunds_accounting
  derived_table:
    sql: select
DATE_FORMAT(coalesce(r.created_on,oic.created_on),'%Y-%m') as created_month,
sum(cost) as cost,
sum(vendor_cost) as vendor_cost
from
returns rt
left join
refunds r
ON r.order_item_id = rt.order_item_id
and
{% condition refund_date %} r.created_on {% endcondition %}
left join
order_item_credits oic
ON oic.order_item_id = rt.order_item_id
and
{% condition refund_date %} oic.created_on {% endcondition %}
where
r.id is not null OR oic.id is not null
group by 1
  fields:
  - dimension: cost
    type: number
    sql: ${TABLE}.cost
  - dimension: created_month
    sql: ${TABLE}.created_month
  - dimension: vendor_cost
    type: number
    sql: ${TABLE}.vendor_cost
  - measure: total_cost
    type: sum
    sql: ${TABLE}.cost
  - measure: total_vendor_cost
    type: sum
    sql: ${TABLE}.vendor_cost