A real change. A plausible result. An invalid training table.

THE FAILURE PATTERN

Root cause: fanout + future data

FROM orders
LEFT JOIN order_items
  ON order_items.order_id = orders.order_id
LEFT JOIN refunds
  ON refunds.order_id = orders.order_id
LEFT JOIN sessions
  ON sessions.customer_id = orders.customer_id
LEFT JOIN marketing_events
  ON marketing_events.customer_id = orders.customer_id
LEFT JOIN payments
  ON payments.customer_id = orders.customer_id

Problem: order_items/refunds can multiply each order, while sessions/events/payments add customer-level rows without point-in-time constraints.

Broken measurement: labels & metrics computed after fanout

AVG(target)        AS refund_rate,
SUM(total_amount)  AS gross_sales,
SUM(refund_amount) AS total_refund,
COUNT(order_id)    AS rows_seen

These aggregates now run over exploded rows, not unique orders. The result can look precise while measuring the wrong thing.

Rationale

The joined_orders step combines order-level data with multiple one-to-many sources. That can multiply one logical order into many rows before the query calculates labels and metrics. The same duplicated rows then flow into refund_rate, revenue totals, refund totals, counts, and retention-style outputs. Because sessions, marketing_events, and payments are also joined without as-of constraints, the training table can include future customer activity. The final output can therefore look like a valid training table while the refund label and business metrics are wrong.