Joins & fan-out

Joining a parent table to a related table with multiple matching rows (a 1:N or N:M relationship) multiplies parent rows across each matching child. While standard relational behavior for detail queries, this quietly invalidates aggregates: summing an order’s total_amount after joining to its line items counts the order’s total once per item instead of once per order.

This is called a join fan-out, and it is one of the most common causes of silent data corruption in business intelligence. DataSquares automatically detects and rewrites queries to eliminate fan-out double-counting across parent and child measures, and refuses queries loudly with specific corrective guidance rather than producing incorrect numbers when a shape cannot be auto-corrected.

What a fan-out looks like

Consider an orders table (one row per order) and an order_items table (multiple rows per order):

orders (`id`, `total`)		order_items (`order_id`, `qty`)
1	$100	1	2
		1	3
2	$50	2	1

Joining these tables produces three rows in the result set:

Order 1 is duplicated because it contains two items.
Naive evaluation of SUM(orders.total) returns $250 ($100 + $100 + $50), instead of the true total of $150.
Naive evaluation of AVG(orders.total) or COUNT(orders.id) is similarly distorted.

How DataSquares corrects fan-out

When a chart or query combines measures and dimensions across 1:N relationships, the DataSquares query planner identifies the fan-out and computes each measure at its authentic grain, joining results back cleanly. This protection is universal across canvas dashboard cards, the visual query builder, SquareX measures, and exports.

The query engine dynamically chooses between two automated rewrite mechanisms:

1. Foreign-key pre-aggregation (`__fanout_<alias>`)

For composable aggregations (SUM, COUNT, MIN, MAX, AVG), the child table is collapsed into a Common Table Expression (CTE) grouped by its foreign key before joining back to the parent:

Child WHERE pushdown: Filters applying exclusively to child tables are pushed directly inside the pre-aggregation CTE.
Exact AVG decomposition: AVG(x) is decomposed into SUM(x) / NULLIF(COUNT(x), 0) within the CTE so parent weighting remains mathematically exact rather than averaging averages.
Join type preservation: An INNER JOIN against the child table joins against the pre-aggregation CTE with INNER JOIN, correctly preserving parent filtering for parents with active children.

2. Grain-keyed measure CTEs (`__mgrain_<alias>`)

Non-composable measures (COUNT DISTINCT, MEDIAN, STDEV, VAR) cannot be reconstructed from foreign-key partials. For these operations:

Each measure is evaluated in a dedicated CTE at the report’s exact grouping grain.
The join path is traversed independently for that measure and joined back to the primary grain using dialect-aware null-safe equality (IS NOT DISTINCT FROM, <=>, or null-matched equality).
Row-Level Security (RLS) policies and table transforms compose across all generated measure CTEs.

Multi-hop join paths

When queries traverse across multiple table hops (e.g. Customers → Orders → Order_Items), the planner walks the full relationship graph. Multi-hop parent and child measures are each calculated at their respective true grains without compounding duplication.

Cross-model Blends fan-out pushdown

When blending across multiple independent data models or distinct data sources in Blends (/blends):

The query planner pushes aggregate calculations down to each source model before the cross-source join occurs.
Both sides are pre-aggregated to the shared join keys in parallel, preventing cross-database row multiplication and protecting memory budgets.

When a chart is refused (Fail-loud design)

If a query contains ambiguous graph topology or incompatible dimensional slicing, DataSquares refuses the query with a descriptive error explaining the exact constraint and how to fix it:

This chart hits a join fan-out that would double-count a measure and can’t be auto-corrected yet (reason). What to do instead.

Refusal reasons & solutions

Refusal message	Root cause	How to resolve
the grouping spans two tables — A and B	Grouping by columns from both parent and child makes the output grain finer than the parent table, duplicating parent rows per child group	Group by columns from a single table, or separate the secondary grouping column into its own focused chart
the relationship between A and B is many-to-many	Both sides multiply across the relationship, creating ambiguity between entity totals and association totals	Explicitly model the bridge table as two `1:N` relationships in the Data Model, allowing the engine to pre-aggregate each side independently
“A” is reachable by more than one join path	Multiple ambiguous paths connect the tables, producing conflicting calculation paths	Remove or redirect redundant relationships in the Data Model to establish a single canonical path
“A” is not below the grouping table “B”	The measure’s table cannot be reached downward from the selected grouping dimensions	Adjust chart dimensions to group by a column at or above table A
*the “AGG” of “field” on “A” can’t be pre-aggregated*	The selected aggregate function cannot be recomposed across the foreign key on this table shape	Use standard composable aggregations (`SUM`, `COUNT`, `MIN`, `MAX`, `AVG`), or define a SquareX measure
*“field” on “A” is selected without an aggregation*	Unaggregated detail columns cannot survive pre-aggregation across a parent grain	Apply an aggregation to the column or add it to the chart’s group-by dimensions
a NULL-test filter on “A”, which is left-joined	Filtering `IS NULL` on a left-joined table asserts child absence that cannot be expressed inside child pre-aggregation	Apply the filter directly on the parent table or switch to an inner join
the grouping key is a computed expression	The planner cannot bind a raw SQL grouping expression to a single model table to establish grain	Define the expression as a Calculated Field on the table, then group by the calculated field

Verifying query results

To independently verify that a metric is free of fan-out distortion:

Open the SQL editor.
Run a standalone query aggregating the parent table without joins:
```
SELECT SUM(total) FROM orders;
```
Compare the standalone total with your dashboard card or visual query. When fan-out correction is active, the numbers match.

Data models & relationships — defining table relationships and cardinality settings.
Calculated fields — adding row-level expressions to tables.
SquareX measures — defining advanced metrics and filter context overrides.
Visual query builder — visual joins with live CTE preview.