06-reference

practical data modeling fan out dashboard double counting

2026-10-20·reference·source: Practical Data Modeling·by Joe Reis
data-modelingsql-joinsdata-qualityagent-output-integrity

Why this is in the vault

Sharp, concrete explanation of "fan-out" — joining tables at mismatched grain silently multiplies rows and inflates aggregates (counts, sums) without the query ever erroring — plus a durable checklist for catching it before it reaches a dashboard.

The core argument

Reis's example: a "Customer Health" dashboard joins Customers, Subscriptions, and Support Tickets on customer_id, then counts tickets and sums MRR per customer. A customer with 3 subscriptions and 5 tickets produces 15 joined rows — so ticket_count reads 15 instead of 5, subscription_count reads 15 instead of 3, and total MRR comes out 5x true. Nothing fails; the query runs and the number just looks wrong only if you already know the right answer. He names two variants: the "fan trap" (summing a parent value across fanned-out child rows) and the "chasm trap" (two one-to-many branches meeting on a shared key).

His core caution: COUNT(DISTINCT ...) is not a fix, it's camouflage — it hides the row explosion, doesn't help SUM (SUM(DISTINCT ...) breaks whenever two rows legitimately share a value), and the full cross-product still gets built underneath. The actual fix is to aggregate each branch to one row per entity before joining (pre-aggregate in CTEs, then LEFT JOIN with COALESCE for nulls), never after. His verification checklist: state each table's grain in one sentence, annotate every join with its grain effect, compare COUNT(*) to COUNT(DISTINCT key) before and after joining, and reconcile totals against source tables using IN filters (which can't multiply rows).

The piece is explicitly adapted from Chapters 8 (grain) and 9 (aggregation) of his book Mixed Model Arts — both already individually filed in this vault — repackaged as a standalone explainer with AI-assisted drafting disclosed in the post itself. No sponsor block; the only promotional link is to his own book.

Mapping against Ray Data Co

The concrete hit: RDCO's own /process-newsletter and /deep-research skills are literally named "fan-out" patterns — one sub-agent per article/question, dispatched and then merged back. Reis's mechanism (combining results at a mismatched grain silently inflates the aggregate, with no error thrown) is the same shape of failure that feedback_workflow_agent_output_integrity already tracks under "pointer returns" and "false verified stamps": a multi-stage pipeline can silently double-count or over-report (duplicate task creation into Notion, a backlog inflating ~2.9x per project_deep_research_backlog_inflation, a sub-agent re-processing an already-filed item) and the aggregate output looks plausible precisely because nothing crashed. Reis's fix generalizes cleanly too: aggregate/dedupe each branch to its correct grain (one result per source item) before merging fan-out outputs, and reconcile the merged count against the known input count — the same discipline this skill run itself is supposed to apply when checking source_url against existing vault files before filing. This isn't a stretch to SQL-specific technique; the "silent multiplication at merge time" failure mode is the load-bearing idea, and it transfers directly to agent fan-out/fan-in architecture.

Related