Eliminating Fan-out Double-counting in Multi-tenant Financial Views using BigQuery ROW_NUMBER()…

Eliminating Fan-out Double-counting in Multi-tenant Financial Views using BigQuery ROW_NUMBER() Window Functions

Author: Lalithesh Role: Backend Engineer Published: June 2026 Tags: BigQuery SQL Window Functions Backend Engineering Financial Data Data Quality

The Problem: Silent Revenue Inflation You Never See Coming

As a backend engineer building financial reporting pipelines, one of the most dangerous bugs I’ve encountered is fan-out double-counting — a silent aggregation error caused by JOIN operations that produce more rows than the intended grain of your query.

Unlike null pointer exceptions or schema mismatches, fan-out does not throw an error. Your query runs successfully. Your numbers look reasonable. But your revenue totals, transaction counts, or any other aggregated metric are quietly inflated — sometimes by 2x, 3x, or more — depending on how many duplicate rows exist on the join side.

This post walks through what fan-out is, how to detect it, and how to permanently fix it using BigQuery’s ROW_NUMBER() window function — a pattern I've applied in production financial reporting pipelines handling data across multiple tenants and brands.

What Is Fan-out?

Fan-out occurs when a JOIN between two tables results in more rows than the left table’s grain because the right-side table has multiple rows matching a single join key.

Minimal Example

sql

-- Table A: transactions (1 row per transaction)
-- Table B: config_table (should be 1 row per entity, but has duplicates)

SELECT
  a.entity_id,
  SUM(a.amount) AS total_amount   -- ⚠️ INFLATED due to fan-out
FROM transactions a
JOIN config_table b ON a.entity_id = b.entity_id
GROUP BY a.entity_id

If config_table has 3 rows for entity_id = 'E001', every transaction for E001 joins to all 3 rows — tripling the revenue for that entity without any warning.

Why It’s Especially Dangerous in Financial Systems

• Aggregates pass basic sanity checks — values are positive, non-null, and plausible
• It only surfaces when the right-side join table has duplicate keys
• Different entities may fan out at different multipliers, making cross-entity comparisons meaningless
• It can exist undetected in production for months until an external reconciliation catches the discrepancy

Step 1: Diagnose Fan-out in Your Join Table

Before writing any fix, confirm the root cause. Run this diagnostic against the right-side join table:

sql

-- Detect duplicate join keys in the dimension/config table
SELECT
  entity_id,
  COUNT(*) AS row_count
FROM config_table
GROUP BY entity_id
HAVING COUNT(*) > 1
ORDER BY row_count DESC

If this returns rows, you have a fan-out source. Note which entity_id values have the highest duplicate counts — those are the entities with the most inflated aggregates.

Step 2: Fix with ROW_NUMBER() Deduplication

The standard backend engineering fix is to assign a row rank within each duplicate group using ROW_NUMBER(), then filter to retain only one canonical row per join key before performing the JOIN.

sql

WITH deduped_config AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY entity_id
      ORDER BY updated_at DESC    -- Retain the most recently updated row
    ) AS rn
  FROM config_table
),

canonical_config AS (
  SELECT * EXCEPT(rn)
  FROM deduped_config
  WHERE rn = 1                    -- ✅ One row per entity_id
),

base_transactions AS (
  SELECT
    entity_id,
    period,
    amount
  FROM transactions
)

SELECT
  t.entity_id,
  t.period,
  SUM(t.amount) AS total_amount   -- ✅ Accurate, fan-out eliminated
FROM base_transactions t
JOIN canonical_config c ON t.entity_id = c.entity_id
GROUP BY t.entity_id, t.period

Key Design Decisions

**PARTITION BY entity_id** — defines deduplication granularity. Every row in the same partition competes for rank 1. Adjust this to match the actual join key of your query.

**ORDER BY updated_at DESC** — deterministic tie-breaking. This is critical. Without a deterministic ORDER BY, BigQuery may return different rows across query executions, producing non-reproducible results. Always tie-break on a reliable timestamp, version number, or surrogate key.

**SELECT * EXCEPT(rn)** — cleanly removes the helper column before the JOIN, keeping the downstream CTE schema clean.

**WHERE rn = 1** — the deduplication filter. Only the top-ranked row per partition survives into the join.

Step 3: Handle Sign Convention for Financial Amounts

A related issue in financial database pipelines is sign convention for credit/debit accounts. In standard double-entry accounting, revenue accounts carry credit balances (stored as negative values) in the general ledger. If consumed directly in reporting queries, revenue surfaces as a negative number in dashboards.

The fix is a CASE-based sign-flip applied before aggregation:

sql

SELECT
  entity_id,
  period,
  account_type,
  CASE
    WHEN account_type = 'REVENUE' THEN -1 * amount
    WHEN account_type = 'EXPENSE' THEN amount
    ELSE amount
  END AS normalized_amount
FROM ledger_entries

This pattern ensures all downstream aggregations work with economically correct signs regardless of how the source system stores account balances.

Step 4: Validate the Fix — Reconciliation Query

After applying the deduplication fix, validate that your corrected totals match the source system of record:

sql

-- Post-fix aggregation
SELECT
  entity_id,
  period,
  SUM(total_amount) AS corrected_revenue
FROM corrected_financial_view
GROUP BY entity_id, period

-- Compare against source system totals
-- Expected: delta = 0 for all entity_id + period combinations

A zero delta across all entities confirms the fan-out has been fully eliminated. Any residual delta indicates additional fan-out sources that need investigation.

Step 5: Applying the Pattern in View Definitions

In production, this deduplication logic should live inside the database view definition — not in application-layer queries or ORM filters. Centralizing it at the view layer ensures:

• Every downstream consumer of the view gets deduplicated data automatically
• Application-layer code does not need to replicate the fix
• Changes to the deduplication logic propagate to all consumers in a single deployment
• Query plans are optimized once at the view layer rather than re-executed per consumer

sql

-- Production view definition (BigQuery)
CREATE OR REPLACE VIEW `your_project.your_dataset.financial_summary_view` AS

WITH deduped_config AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY entity_id
      ORDER BY updated_at DESC
    ) AS rn
  FROM `your_project.your_dataset.config_table`
),

canonical_config AS (
  SELECT * EXCEPT(rn)
  FROM deduped_config
  WHERE rn = 1
)

SELECT
  t.entity_id,
  t.period,
  c.entity_name,
  c.region,
  SUM(
    CASE
      WHEN t.account_type = 'REVENUE' THEN -1 * t.amount
      ELSE t.amount
    END
  ) AS net_amount
FROM `your_project.your_dataset.transactions` t
JOIN canonical_config c ON t.entity_id = c.entity_id
GROUP BY t.entity_id, t.period, c.entity_name, c.region

Performance Considerations in BigQuery

ROW_NUMBER() is a window function and incurs a sort + partition operation. For large tables, keep these optimizations in mind:

Consideration

Recommendation

Partition pruning

Always filter on partition columns (e.g., WHERE date >= ...) before the window function CTE

Clustering

If the config/dimension table is clustered on entity_id, the PARTITION BY entity_id scan is cheaper

Materialization

For very large config tables with frequent joins, consider materializing the deduplicated CTE as a separate table or scheduled query result

Slot usage

Window functions consume more slots than simple scans — profile with BigQuery’s Query Plan Explanation for high-frequency pipelines

Common Mistakes to Avoid

1. Non-deterministic ORDER BY in ROW_NUMBER()

sql

-- ❌ Wrong: no ORDER BY means arbitrary row selection
ROW_NUMBER() OVER (PARTITION BY entity_id) AS rn

-- ✅ Correct: deterministic tie-break
ROW_NUMBER() OVER (PARTITION BY entity_id ORDER BY updated_at DESC) AS rn

2. Fixing fan-out in the SELECT clause instead of upstream

sql

-- ❌ Wrong: dividing by COUNT(*) to "undo" fan-out
SUM(amount) / COUNT(DISTINCT config_id) AS patched_amount

-- ✅ Correct: eliminate the duplicate rows before aggregation

3. Assuming DISTINCT fixes fan-out

sql

-- ❌ Wrong: DISTINCT on the outer query doesn't help if rows differ in any column
SELECT DISTINCT entity_id, SUM(amount) ...

-- ✅ Correct: deduplicate the join table specifically on the join key

Key Takeaways

Concept

Detail

Root cause

Right-side JOIN table has multiple rows per join key

Detection

GROUP BY join_key HAVING COUNT(*) > 1

Fix

ROW_NUMBER() OVER (PARTITION BY join_key ORDER BY ...) + WHERE rn = 1

Tie-breaking

Always deterministic — use timestamp, version, or surrogate key

Sign convention

Credit/Revenue accounts require -1 * negation for correct financial reporting

Validation

Reconcile post-fix aggregates against source system totals

Architecture

Centralize fix in the view definition, not the application layer

Conclusion

Fan-out double-counting is a silent, high-impact correctness bug that is particularly costly in financial reporting systems where accuracy is non-negotiable. As a backend engineer, the right instinct is to treat it as a data integrity issue at the database layer — not something to patch in application code.

The ROW_NUMBER() deduplication pattern is deterministic, performant at scale in BigQuery, and composable — it fits cleanly into CTE-based view definitions without affecting the readability of downstream query logic.

If you’re building financial data pipelines with multi-tenant or multi-entity JOIN patterns, make this deduplication step a standard part of your view design checklist.