Styxis
Company
Knowledge

DataFusion keys after GROUP BY: validate joins at the aggregate grain

An original primary key may no longer be unique by itself after aggregation, allowing repeated contributions to disappear.

Re-establish the key after transformation

Do not inherit a transformed result's key contract directly from the original table's primary-key declaration. An item appearing in separate daily groups is not unique by item alone. A Truthound check must select the composite key representing the actual aggregate grain. [1, 2, 3]

When an inner join becomes a semi join

The public DataFusion report describes an aggregate grouped by a primary key plus another column being treated as unique on the key alone. Join elimination trusts that information and changes an inner join to a semi join, removing repeated contributions. A correct primary-key declaration on the source does not establish uniqueness in every transformed result. [1]

Document what each row represents

Record the actual aggregate grain and verify contribution multiplicity and totals in downstream joins. Distinguish legitimate per-date rows from erroneous duplicates. Deleting them merely because the item repeats would reproduce the optimizer's mistake in preprocessing. [1, 3]

Derive the total from contributions

For an illustrative fixture, an item valued at 10 matches two date groups and an item valued at 20 matches one. The correct total is 10+10+20=40. An observed total of 40 retains the per-date contributions. A total of 30 counts each item only once and loses its contribution from another date. Derive the reference from group-match multiplicity rather than copying the join output. [1, 3]

Two separate Truthound checks

Truthound Core's ColumnPairValidator can compare expected and observed totals aligned to the same aggregate scope. Derive the expected total independently from item values and per-date contribution counts, and provide non-null comparison values. MultiColumnUniqueValidator can additionally inspect the actual item–date grain, but uniqueness alone does not establish the total. Truthound does not correct the optimizer's functional-dependency metadata. A control without join elimination should also verify contribution counts on the same input. [1, 2, 3]

Application conditions

Derive the expected total independently from contribution counts in the same item_id and sales_date input, and link it to that join’s observed total.

Alternatives

Calculate repeated monetary contributions through a control query that avoids join elimination.

Limitations

  • Truthound’s composite-key check does not correct optimizer functional-dependency metadata.

Evidence

Event checked
Subject checked
Modified