Correctness traps: fan-out and double counting
Understand the join hazards that silently corrupt metrics, and how the layer's design defends against them.
A semantic layer is only valuable if its numbers are right. The most dangerous bugs in analytics aren't crashes — they're plausible-but-wrong numbers that nobody catches. The classic culprit is fan-out.
What fan-out is
When you join a table to another it has a one-to-many relationship with, rows multiply. Join orders to order_items and each order appears once per line item. Now SUM(orders.some_value) counts that order two or three times. The number looks reasonable and is completely wrong.
-- WRONG: fan-out inflates the order count by line-item count
SELECT COUNT(orders.order_id)
FROM orders JOIN order_items USING(order_id); -- ~15,000, not ~8,700!
-- RIGHT: DISTINCT collapses the duplicates
SELECT COUNT(DISTINCT orders.order_id)
FROM orders JOIN order_items USING(order_id); -- ~8,700 ✓How the layer defends against it
- Count metrics use `COUNT(DISTINCT …)`.
orders_countandnew_customersare defined with DISTINCT, so even when a dimension forces a fan-out join, they stay correct. - Additive measures live at the right grain.
revenueis based onorder_items(the line grain), so summing there is naturally correct — no fan-out to undo. - The join resolver adds only necessary joins. It never joins a table a metric doesn't need, so it can't introduce fan-out gratuitously.
Getting DISTINCT right is exactly the kind of detail every hand-written query gets wrong eventually. Encode it once in the metric definition and every question inherits the correct behavior — forever.
The deeper lesson: a metric isn't just an aggregate, it's an aggregate at a specific grain. Part of governing a metric is guaranteeing it's computed at the grain where its math is valid.