dbt Source Fan-Out in BigQuery: The DAG Anti-Pattern Costing You

What Is dbt Source Fan-Out and Why Does It Matter?

dbt source fan-out occurs when a single raw source table is referenced directly by multiple downstream dbt models using {{ source() }}, instead of being funnelled through a single dedicated staging model. The result is duplicated BigQuery scan costs, a broken dependency graph that lineage tooling cannot trace, and test coverage that exists on paper but fires in the wrong place. If you have a dbt project with more than a handful of contributors and more than a few months of history, the chances are this anti-pattern is already embedded in your DAG — and quietly inflating your bill.

This is one of those problems that starts small and feels harmless. A developer needs a column from a raw payments table. The staging model for that table exists, but it doesn't expose the column they need — so they write {{ source('stripe', 'charges') }} directly in their intermediate model and move on. Six months later, seven models are referencing the same raw source, each independently scanning the full table on every run. Nobody has documented which model is the canonical owner of that source. The freshness check is defined once but the data flows through different paths to different consumers. Your DAG looks like a delta, not a pipeline.

How dbt source fan-out breaks your BigQuery bill

BigQuery gives you two options for how you are charged for query execution: on-demand pricing or the capacity-based pricing model. On-demand pricing costs $6.25 per TB scanned, while Standard Edition capacity pricing starts at $0.04 per slot-hour. Under on-demand — which is the default for most pre-Series B companies — every model that references a raw source table directly triggers an independent full scan of that table.

Here is what that looks like in practice. Suppose you have a raw_stripe_charges table that is 50 GB and partitioned by event date, but none of the fan-out models apply a partition filter (a related mistake we cover in detail in our post on dbt models querying partitioned BigQuery tables without partition filters). Each model that calls {{ source('stripe', 'charges') }} independently scans all 50 GB. With seven fan-out models, that is 350 GB of scan on every dbt run. At $6.25 per TB, that is $2.19 per run — not alarming in isolation. But run that pipeline hourly and you are looking at over $50 per day, or roughly $1,500 per month, for a single raw table that should have been read once.

Each source node should be referenced by a single model that performs basic operations — renaming, recasting, and other light transformations — to maintain consistency throughout the project. The role of this staging model is to mirror the raw data but align it with project conventions. When that contract is violated, you pay for every violation in compute.

The cost impact compounds when the fan-out pattern spreads across multiple sources. A pattern we see repeatedly in our work with early-stage fintech and payments companies is that the fan-out originates at one high-cardinality table — typically a transactions or events table — then gets replicated as a template behaviour across the rest of the project. By the time we do an initial audit, it is common to find four to six sources each with three or more direct consumers, producing a total scan multiplier of three to five times what the pipeline actually requires.

---

📺 Watch: More Drama in the Modern Data Stack? dbt just dropped DBT CHARTS 🔥🔥

---

How source fan-out destroys your dbt lineage and test coverage

The cost problem is visible. The lineage and testing problem is invisible until something breaks in production.

The ref() function interpolates the schema into your model file to allow you to change your deployment schema via configuration, and uses references between models to automatically build the dependency graph. The source() function anchors to a raw table outside that graph. When multiple models call {{ source() }} on the same table, dbt sees multiple entry points into the DAG from the same data asset — which means:

• Lineage tools show parallel paths with no canonical owner. Tools like dbt Docs, Datafold, and Monte Carlo build their lineage views from the compiled DAG. A source with seven children looks like seven independent pipelines, not one pipeline with seven consumers. Impact analysis when the source schema changes becomes guesswork.

• Freshness checks only fire at source level, not at consumption level. If raw_stripe_charges is stale, dbt will flag the source — but it has no way to know which of the seven downstream models are critically affected and which are tolerance-insensitive. You get a single alert for a problem that may have seven distinct business impacts.

• Tests defined on the staging model don't protect fan-out consumers. If you have a not_null test on stg_stripe_charges.charge_id and a fan-out model reads raw_stripe_charges.id directly, that test never runs for that consumer. The fan-out model silently receives nulls that the staging model would have caught and rejected. If your dbt project has multiple source nodes pointing to the exact same location in your data warehouse, you will have an inaccurate view of your lineage.

In one engagement with a Series A payments company, we found an intermediate model computing settlement totals that was calling {{ source('processor', 'transactions') }} directly, bypassing the staging model entirely. The staging model applied currency normalisation and excluded voided transactions. The intermediate model did neither. The result was settlement figures that were systematically wrong in multi-currency corridors and slightly inflated by voids — both invisible until a manual reconciliation surfaced the discrepancy weeks later.

If you are looking to systematically fix this class of problem in your dbt project, explore how Fintel Analytics approaches analytics engineering delivery — we work with growth-stage companies globally to audit, refactor, and harden dbt projects that have scaled faster than their governance.

How to detect source fan-out in your dbt project right now

Detection is straightforward. You need to find every {{ source() }} call in your project and map how many distinct models reference each source node. There are three practical approaches:

1. Use dbt's built-in project evaluator

The dbt_project_evaluator package includes a rule called fct_source_fanout that surfaces exactly this pattern. It shows each instance where a source is the direct parent of multiple resources in the DAG. Install the package, run dbt build --select package:dbt_project_evaluator, and query the fct_source_fanout table in your warehouse. The output gives you source name, database, schema, and a count of direct model children. Any source with more than one direct child is a fan-out candidate.

2. Query your compiled manifest directly

After running dbt compile, the manifest.json in your target/ directory contains the full DAG. Parse the nodes and sources sections to build a map of parent → child relationships. Filter for nodes where the parent is a source node and the child count is greater than one. This approach works without installing additional packages and is easy to automate in a CI check.

3. Grep your SQL files as a fast first pass

If you want a quick read before instrumenting anything properly, run a recursive grep across your models/ directory:

grep -rn "source(" models/ | grep -v "staging/" | awk -F'source(' '{print $2}' | sort | uniq -c | sort -rn

This is imprecise — it will miss edge cases and doesn't give you model-level attribution — but it will immediately show you which sources are being called outside your staging layer, and how often.

Once you have your fan-out map, prioritise remediation by scan volume. Unused reservations, highly concurrent workloads, and complex query structures are the primary culprits behind escalating BigQuery slot cost — but in fan-out scenarios, it is raw duplicate scanning that drives the immediate savings opportunity. Start with the largest tables being fanned out into the most models.

How to fix dbt source fan-out without breaking your pipeline

The fix follows a consistent pattern, but the sequencing matters. Done wrong, you will break downstream models during the refactor. Here is the approach we use with clients:

Step 1: Identify the canonical staging model

For each fanned-out source, there should already be one staging model that is the designated owner. If it doesn't exist, you need to create it before you do anything else. The staging model should: select from {{ source() }} once, rename columns to your project's naming conventions, cast data types, apply basic filters (e.g. exclude soft-deleted records), and define all not_null and unique tests. Since raw data is normally loaded by third parties, the structure of it can change over time — tables and columns may be added, removed, or renamed. When this happens, it is easier to update models if raw data is only referenced in one place.

Step 2: Add missing columns to the staging model

The most common reason developers bypass staging is that the staging model doesn't expose the column they need. Before migrating fan-out consumers, update the staging model to expose everything the downstream models require. This is also a good moment to document each column in your schema.yml — description, data type, tests.

Step 3: Migrate fan-out models from source() to ref()

ref() is used to reference other dbt models (SQL files inside the dbt project), and dbt uses ref() to automatically manage model dependencies and execution order. Replace each {{ source('stripe', 'charges') }} call in your fan-out models with {{ ref('stg_stripe_charges') }}. Update column references to match the staging model's naming conventions. This is where most breakage occurs — take each model one at a time, run dbt compile after each change, and validate the compiled SQL before promoting.

Step 4: Run both versions in parallel before cutting over

For high-stakes models — anything that feeds financial reporting, executive dashboards, or production APIs — run the refactored model in parallel with the original for one or two pipeline cycles. Compare outputs row by row or aggregation by aggregation before switching the downstream consumer to the new model. This is the same discipline we apply when migrating calculations out of spreadsheets and into dbt: you don't cut the old version until the new version has proven parity. Migrating calculations from spreadsheets into dbt models eliminates a class of recurring manual errors — the same principle applies when migrating from raw source references into tested, governed staging models.

Step 5: Enforce the pattern going forward

Refactoring without enforcement means the fan-out returns within six months. Add fct_source_fanout from dbt_project_evaluator as a CI gate — fail the pipeline if any new model introduces a direct source reference that bypasses the staging layer. Document the rule in your project's CONTRIBUTING.md. Make it a code review checklist item. The operational cost of re-auditing this every six months is higher than the cost of building the gate once.

For context on related materialisation decisions that compound the cost impact of fan-out, see our post on dbt intermediate models in BigQuery: the materialisation mistake costing you — fan-out models materialised as tables are particularly expensive because they embed both the duplicate scan cost and the storage overhead into your bill permanently.

When is source fan-out actually acceptable?

Almost never — but there are two narrow cases worth acknowledging.

First, in very early-stage projects (pre-production, exploration phase, single developer), strict staging layer discipline may be premature. If you are running ad-hoc analysis against a single dataset with no downstream consumers, the fan-out pattern is irrelevant. The problem emerges when exploration code gets promoted into production without refactoring.

Second, some organisations use a deliberate "raw vault" pattern where source tables are accessed by multiple models by design, with the duplication managed through slot reservations rather than query reduction. Reserved slots bill continuously for the full committed capacity whether or not a query is running, which turns idle capacity into a fixed, ongoing cost — so this approach is only viable if your reservation is already sized for the workload and your slot utilisation justifies the commitment. Purchase capacity commitments only after slot utilisation metrics show a workload consistently needs dedicated capacity. For most pre-Series B companies on on-demand pricing, this calculus doesn't work in your favour.

The honest answer is: source fan-out is almost always a historical accident, not an intentional architectural choice. It accumulates under deadline pressure, in teams without strong code review culture, and in projects that started as individual exploration notebooks and grew into production pipelines. Recognising that is the first step to fixing it systematically.

Frequently Asked Questions

Q: What is dbt source fan-out in BigQuery?

A: dbt source fan-out occurs when multiple dbt models reference the same raw source table directly using {{ source() }} instead of routing through a single dedicated staging model. In BigQuery, this causes each model to independently scan the full source table, multiplying query costs and fragmenting your DAG lineage.

Q: How do I detect source fan-out in my dbt project?

A: The fastest method is to run the dbt_project_evaluator package and query the fct_source_fanout table, which shows every source with more than one direct model child. You can also parse manifest.json after dbt compile, or run a grep across your models/ directory for source() calls outside your staging/ folder.

Q: How much does dbt source fan-out cost in BigQuery?

A: The cost depends on table size and fan-out degree. A 50 GB table referenced by seven models incurs 350 GB of scan per run at $6.25 per TB on-demand pricing. Run hourly, that is over $1,500 per month from a single fanned-out source — purely from duplicate scanning that a single staging model would eliminate.

Q: What is the difference between source() and ref() in dbt?

A: source() is used to reference raw or external tables not created by dbt — these live in your warehouse and are defined in a sources.yml file — while ref() is used to reference other dbt models (SQL files inside the dbt project). Raw data should be accessed via source() in one place only: the staging model. All other models should use ref() to consume the staged output.

Q: How do I prevent source fan-out from recurring in my dbt project?

A: Add the dbt_project_evaluator package as a CI gate and configure fct_source_fanout as a failing test in your pipeline. Complement this with a written project convention — documented in your CONTRIBUTING.md — that staging models are the exclusive owners of source() references, and make it a mandatory code review checklist item.

Source fan-out is one of those problems that feels low-priority until you see the BigQuery bill or trace a data quality incident back to a model that silently bypassed every test your team had written. At Fintel Analytics, we audit and refactor dbt projects for growth-stage companies across fintech, payments, and e-commerce — and source fan-out appears in the majority of projects we inherit. If your team has a dbt project that has grown faster than its governance, that is a solvable problem, and the cost savings from a single week of structured refactoring typically exceed the engagement cost.