Analytical Truth: Grain, Cardinality and the Join That Quietly Duplicates Your KPI
Why technically valid SQL can still produce analytically false results.
The core problem
A table has an analytical grain: what one row represents. Joining two tables without respecting their grains can multiply rows even when the SQL is syntactically correct. Summing a measure after that multiplication can create a precise-looking false answer.
Cardinality is part of the model
One-to-one, one-to-many and many-to-many relationships have different consequences. If a task-level fact is joined to multiple evidence records and then task hours are summed, task hours may be duplicated unless the evidence is aggregated, filtered or otherwise modeled at a compatible grain.
Controls and interpretation
The lab changes join structure and exposes row multiplication so the user can see how a KPI changes without any underlying work changing. The lesson is that grain and join contracts belong upstream of visualization.
Assumptions and limits
The example is intentionally small. Real analytical truth also requires temporal semantics, slowly changing dimensions, null policy, metric definitions, lineage and source-system reconciliation.
Validation checks
Row counts should reconcile to the declared grain. A measure that is invariant to descriptive enrichment should remain invariant after a valid join. Primary-key uniqueness and expected join cardinality are deterministic properties and should be tested rather than entrusted to visual inspection.