Fan-out resolution

The compiler resolves fan-out automatically. Normally, a one-to-many join can duplicate rows and inflate your aggregates. The compiler detects this risk from the relationship cardinality you already declared and rewrites the query so every entity counts exactly once. There is nothing to configure: every measure defined in AQL is fan-out-safe by default.

What fan-out does to your numbersCopy Anchor Link

Fan-out happens when a join duplicates rows on the "one" side of a one-to-many relationship. Aggregates then count those duplicates as if they were real data.

Take an ecommerce dataset where one users record relates to many orders records. Ask a natural question: how many users do we have, grouped by order status? A naive query joins the two models, then aggregates:

SELECT
  orders.status,
  COUNT(users.id) AS total_users
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY 1

With three users and five orders, the join produces one row per order:

Grouped by status, user 54 is counted twice under "cancelled", so that group reports 2 users instead of 1.

The join repeats each user once per order they placed. A user with five delivered orders adds five to the "delivered" count instead of one. On real data, the inflated numbers often look plausible, so the error is easy to miss.

For how joins are derived from relationships in general, see How joins are generated.

How the industry handles fan-out: symmetric aggregatesCopy Anchor Link

Most join-based BI tools run the fanned-out join anyway, then repair the aggregate with arithmetic. The technique is known as symmetric aggregates. It hashes each row's primary key into an enormous number and adds the measure value on top. It then sums the distinct results and subtracts the hashes back out:

SUM(DISTINCT hash(pk) * 1000000 + value) - SUM(DISTINCT hash(pk) * 1000000)

This works because duplicated rows of the same entity produce the same hash + value, so DISTINCT collapses them into one. Two entities that share a value stay separate because their hashes differ. After subtracting the hash sum, what remains is the true total.

This approach has several drawbacks:

• Every fanned-out aggregate computes a hash (typically MD5) per row, then runs big-decimal SUM DISTINCT arithmetic on the results.
• The generated SQL is filled with nested hash, cast, and coalesce calls, which makes it hard to read or debug.
• The hash and the measure value are packed into one DECIMAL(38,0), so measure values beyond roughly 14 digits overflow and fail the query.
• The results are only correct if the declared primary key is actually unique. If it isn't, the query still runs and silently returns wrong numbers.

How the compiler resolves fan-out insteadCopy Anchor Link

Instead of repairing the fan-out with arithmetic, the compiler restructures the query so the fan-out never happens. It relies on two pieces of information that already exist in your dataset:

1. Relationship cardinality. Every relationship in your dataset declares which side is "one" and which is "many". That tells the compiler exactly when a join path could duplicate rows.
2. The measure's aggregation structure. AQL measures declare what they aggregate, and the compiler reads that structure directly.

Using these, the compiler restructures the query: it aggregates the measure at its own model's grain, reduces the "many" side to distinct (join key, dimension) pairs, and joins the two results back together. For the users-by-order-status question above, the generated query becomes:

WITH user_status_pairs AS (
  SELECT user_id, status
  FROM orders
  GROUP BY 1, 2
)
SELECT
  s.status,
  COUNT(users.id) AS total_users
FROM users
LEFT JOIN user_status_pairs s ON users.id = s.user_id
GROUP BY 1

With the data from the example above, the distinct pairs step collapses user 54's two cancelled orders into one row:

Joining users to these pairs can match each user at most once per status, so the final result is correct:

Each user now counts once per status they actually have orders in, not once per order. The exact rewrite varies with the query shape, but the principle is the same: deduplicate before aggregating.

Since this approach doesn't rely on hash arithmetic, the drawbacks of symmetric aggregates don't apply:

• There is no per-row hashing, and no risk of decimal overflow.
• The generated SQL stays readable. If you want to verify a number, read the generated query and follow the rewrite step by step.
• There is nothing to configure per query. The compiler only needs the relationships you already declared.