int_shopify_gql__customers_order_aggregates: can kind IN ('sale','capture') double the order-level totals?

Hi — I was reading through this package and want to check my understanding of the join grain in int_shopify_gql__customers_order_aggregates, because I may well be wrong about how Shopify transaction kinds behave in practice.

transaction_aggregates is built with group by 1, 2, 3, so it is one row per (order_id, source_relation, lower(kind)):

sql
transaction_aggregates as (
select
order_id,
source_relation,
lower(kind) as kind,
sum(currency_exchange_calculated_amount) as currency_exchange_calculated_amount
from transactions
group by 1, 2, 3
)

The join in aggregated matches on order_id and source_relation, and restricts kind to two values:

sql
left join transaction_aggregates
on orders.order_id = transaction_aggregates.order_id
and orders.source_relation = transaction_aggregates.source_relation
and transaction_aggregates.kind in ('sale','capture')

If an order can carry both a sale row and a capture row, that join yields two rows for that order rather than one. The refunds join immediately below it pins kind = 'refund' to a single value, so it does not have this property — the difference between the two adjacent joins is what made me look.

My question is whether both kinds landing on one order is reachable in practice. The CTE comment says "customers can pay via multiple payment gateways", so I wondered whether one gateway recording a sale alongside another recording a capture would produce it.

If it is reachable, the columns sourced from order_aggregates would be doubled for those orders, since order_aggregates is joined separately and is one row per order:

lifetime_total_tax / avg_tax_per_order
lifetime_total_discount / avg_discount_per_order
lifetime_total_shipping / avg_shipping_per_order
lifetime_total_shipping_with_discounts / avg_shipping_with_discounts_per_order
lifetime_total_shipping_tax / avg_shipping_tax_per_order
avg_quantity_per_order

I think lifetime_total_refunded is affected the same way. The refunds join is one row per order on its own, but it is keyed to orders rather than to transaction_aggregates — so if the sale/capture join yields two rows for an order, the single refund row is repeated across both and sum(refunds.currency_exchange_calculated_amount) doubles.

lifetime_count_orders uses count(distinct orders.order_id) so it looks protected, and sum(transaction_aggregates.currency_exchange_calculated_amount) looks correct to me either way, since each transaction row is still counted once — which I take to be the intent of the multi-gateway comment.

One thing I noticed that may be relevant: in integration_tests/seeds/shopify_gql_transaction_data.csv there are ten transactions across ten distinct order_ids, one transaction each — including a sale (60001, 60006, 60008) and a capture (60003, 60009), but never both on the same order. So the seed data covers both kinds individually while the multi-gateway case described in the CTE comment is not represented, and I think the integration tests would pass either way.

If it would help, adding a seed row that gives one order both a sale and a capture would make the behaviour visible in CI without needing anyone's production data.

A query that would settle it on real data:

sql
select count(*) as orders_with_both_kinds
from (
select order_id, source_relation
from transaction_aggregates
where kind in ('sale','capture')
group by 1, 2
having count(*) > 1
)

If that returns 0 across warehouses then there is no issue here and I am happy to be corrected. It might still be worth a short comment in the model, since the two adjacent joins treat kind differently and it is not obvious on a first read which behaviour is intended.

I have not run the package myself — this came from reading the SQL — so I have opened this as a question rather than a bug report.

Read the int_shopify_gql__customers_order_aggregates model and integration_tests/seeds/shopify_gql_transaction_data.csv, then run the integration tests with one order containing both sale and capture transactions. Done means establishing whether the join duplicates order-level and refund totals, and making the intended behavior visible in CI if the case is reachable.

Written by the indexing model from the issue text.