Remove the order-line and refund join fan-out
An order with three lines and two refunds becomes six joined rows. The daily mart counts each captured amount several times although the ledger is correct.
- Focused work estimate
- 2h 30m + prerequisites
- Priority in the scenario
- High
- Engineering practice
- Join cardinality · Aggregation · Reconciliation
Estimated field mix
- Data engineering80%
- Database engineering20%
Field percentages are editorial estimates of the ticket's engineering focus. They total 100%; they are not measured time, proficiency scores, or ownership evidence.
Review it, then add it to your workspace.
The board opens an editable draft; nothing is saved until you confirm it. Sign-in and workspace permissions apply, and Demo boards remain ephemeral.
Project context
A fictional marketplace reports daily sales to merchants. Refund joins inflate revenue, merchants close books in different time zones, and backfills compete with nightly loads. All source orders and merchants are synthetic.
Setup prerequisites
- SQL joins and aggregates
- Batch pipelines
- Metric definitions
Preceding work
Complete these dependencies, or supply their agreed outputs before taking this ticket.
Acceptance criteria
- Aggregate sources at declared grains before joining.
- Count each capture and posted refund exactly once.
- Keep orders without refunds and flag orphan refunds.
Implementation constraints
- DISTINCT on monetary values is not a fix; equal legitimate amounts remain distinct.
Verification to include
- Reconcile a three-line, two-refund order to its source total.
- Include equal-value captures and an orphan refund to expose accidental deduplication.
Deliverables
- Corrected SQL and join-fan-out fixture
Rollout and recovery
Compare a synthetic month and publish a new metric version with its correction delta.
Value of the work
For the engineer: Practice metric contracts, historical dimensions, incremental processing, and reproducible warehouse releases.
For the team: Inspect whether an engineer can reconcile business totals and explain metric changes in a reviewable data product.
Evidence boundaries
Outcome Evidence: Tests, patches, and runbooks are requested deliverables. They become Outcome Evidence only through a qualified Mission and immutable Evidence IDs.
Ownership Evidence: Independent adaptation must be observed under a declared verification policy and cite immutable Evidence IDs. Completing a planning ticket establishes no Ownership Evidence.