returns

source

What got sent back, why, and how much we refunded.

Definition

The operational returns ledger — one row per return request, linked to the order line it reverses. `return_status` tracks the lifecycle (requested → approved → received → refunded, or rejected); `refund_amount` is non-zero only once a return is `refunded`. Aggregated downstream into `mart_returns_daily` (de-identified by day × category × reason), which drops the customer linkage entirely.

14 of 14

Column

Description

Tests · PII

return_id

Primary key — BIGSERIAL on the source.

2 tests

order_id

FK to `orders.order_id`. The order this return reverses.

customer +3 PII1 test

order_item_id

FK to `order_items.order_item_id`. The specific line returned.

1 test

product_id

FK to `products.product_id`. Denormalised for category rollups.

1 test

customer_id

FK to `customers.customer_id`. The data-subject join key (table-level PII).

customer +18 PII1 test

return_reason

Why the item was returned.

1 test

return_status

Return lifecycle state. `refund_amount` is non-zero only when `refunded`.

1 test

quantity

Units returned on this line (1..ordered quantity). INT on the source.

refund_amount

Amount refunded in the order currency. NUMERIC(12,2); 0 until `refunded`.

requested_at

When the return was requested (TIMESTAMPTZ). Renamed to `requested_at` + derived `return_day` in staging.

resolved_at

When the return reached a terminal state (received/refunded/rejected). Nullable while in flight.

restocked

Whether the returned unit went back into sellable inventory.

created_at

Row insert timestamp in the source DB.

updated_at

Row update timestamp in the source DB. Cursor for incremental sync.