Teams should separate raw, staging, reporting, and mart layers, then design models to reduce repeated scanning of large tables. Incremental updates help, but they are not enough if downstream logic still forces expensive distinct aggregations or broad table reads. The stronger pattern is to refactor the data flow so each model does less work and reuses narrower intermediate datasets.
How dbt model structure affects performance as volumes grow
dbt becomes slower when models do too much work too early. The main performance pressure usually comes from repeated full-table scans, wide joins across large relations, and downstream transformations that keep reprocessing the same rows. A cleaner structure is to make each layer progressively narrower, so expensive logic is pushed later and only executed on smaller, better-shaped datasets.
The practical implication is that model design is not just a readability concern. It becomes a query-plan concern. If a downstream model repeatedly deduplicates, aggregates, or filters a very large source, incremental materialization alone will not save it. Teams get better results when they separate raw ingestion, staging cleanup, intermediate shaping, and reporting-ready outputs, then keep each model focused on one expensive operation at most.
- Raw models should preserve source fidelity and avoid unnecessary transformation.
- Staging models should standardize types, naming, and basic filtering.
- Intermediate models should do the heavy relational work once, then be reused.
- Mart models should stay thin and selective, with minimal re-scanning.
That pattern reduces the number of times the warehouse has to rediscover the same business logic. It also makes it easier for the optimiser to work with narrower relations, which usually matters more as data grows than micro-tuning a single SQL statement.
Where incremental models help, and where they stop helping
Incremental models are useful when the changed slice of data is genuinely small relative to the full dataset. They reduce compute by processing only new or updated records, but they do not automatically make the whole pipeline efficient. If the model still needs broad distinct checks, large joins, or windowed logic over an ever-expanding history table, the cost can remain high even when only part of the table is refreshed.
Teams should treat incrementality as one optimisation layer, not the architecture itself. The biggest win usually comes from moving expensive operations upstream into reusable intermediate models, then feeding downstream models with already-shaped data. In practice, that means avoiding repeated DISTINCT over large raw facts, avoiding “do everything” mart models, and being selective about which fields need to survive into each layer.
When volume rises, model boundaries also become a maintenance decision. A model that is easy to reason about but expensive to run can become a production bottleneck, while a slightly more layered design often gives better performance and clearer ownership. The goal is to reduce the amount of data each model must inspect, not just to refresh fewer rows.
Standards & Framework Alignment
This section maps relevant standards and security frameworks to the operational risks and controls described in this guidance.
CIS Controls v8 and NIST CSF 2.0 set the governance and control requirements practitioners need to meet.
| Framework | Control / Reference | Relevance |
|---|---|---|
| CIS Controls v8 | CIS 8 — Data Protection | Model layering limits unnecessary data exposure and repeated broad reads. |
| Recommendation — Reduce broad table access by narrowing reusable transformations and selecting only needed columns. | ||
| NIST CSF 2.0 | PR.PT — Protective Technology | Efficient pipeline design is a protective control for availability and performance. |
| Recommendation — Design data pipelines to minimise repeated processing and preserve system performance at scale. | ||
Practitioner Guidance
What to prioritise: Find the models that repeatedly scan large tables or repeat the same deduplication and aggregation logic. Those are usually the first refactor candidates because they create the most avoidable warehouse cost.
What to verify: Check whether your slowest models are still reading from raw or overly wide upstream relations when they could be consuming a narrower intermediate table instead. Also verify that incremental predicates actually reduce the scanned data, not just the written output.
Implementation sequence: Start by isolating raw and staging layers, then extract repeated business logic into shared intermediate models, and finally slim down mart models so they mostly select, join, and present rather than recompute.
Practitioner takeaway: The best dbt performance improvement is usually architectural, not cosmetic, so optimise the data flow first and the SQL second.
Related resources from NHI Mgmt Group
- How should IT and security teams structure page data when large identity datasets start slowing down admin workflows?
- How should security teams govern AI data access without slowing the business down?
- How should security teams move high-volume telemetry into a data warehouse without losing structure?
- How should teams secure sensitive data in analytics platforms without slowing down access?
