5 Ways to Optimise dbt Models to Reduce Costs and Improve Performance

Having worked with dbt on both Snowflake and Google Cloud Platform (BigQuery), I've found that many performance issues aren't caused by the warehouse itself, they're caused by how models are structured.

Here are five optimisations that consistently deliver faster pipelines and lower costs.

1. Materialise models intentionally, not everything needs to be a table

One of the most common mistakes is materialising every model as a table.

Each materialisation has trade-offs.

• Views reduce storage costs but increase query execution time.

• Tables improve query performance but consume storage and require refreshes.

• Incremental models dramatically reduce compute costs for large datasets.

• Ephemeral models eliminate unnecessary objects while simplifying transformations.

Instead of applying one materialisation everywhere, choose the one that matches how the model is consumed.

For example, dimension tables referenced hundreds of times each day often benefit from being materialised as tables, while lightweight transformation layers can remain ephemeral or views.

A simple materialisation review can reduce pipeline execution time significantly without changing business logic.

2. Reduce unnecessary joins and CTE complexity

As dbt projects grow, it's common to see models with ten or more Common Table Expressions (CTEs), repeated joins and transformations duplicated across multiple models.

While dbt encourages modularity, excessive abstraction can create unnecessary work for the query optimiser.

Some questions I regularly ask are:

• Can this transformation happen earlier?

• Is this join actually required?

• Are we calculating the same metric multiple times?

• Can intermediate models be consolidated?

Reducing complexity not only speeds up execution but also makes models easier to debug and maintain.

3. Use incremental models wherever full refreshes aren't necessary

One of the quickest ways to reduce warehouse costs is to stop rebuilding historical data unnecessarily. If only yesterday's data has changed, there's rarely a reason to process several years of history.

Benefits include:

• Faster pipeline execution

• Lower compute costs

• Reduced warehouse utilisation

• Better scalability as data volumes grow

This becomes increasingly valuable once datasets reach hundreds of millions of rows.

4. Test data quality early to avoid expensive downstream failures

Performance isn't just about speed.A failed dashboard, broken machine learning feature or inaccurate KPI often costs more than a slow query.

Adding dbt tests for:

• uniqueness

• null values

• accepted values

• relationships

• freshness

helps catch issues before they propagate through the platform.

I've found that investing in automated testing dramatically reduces production incidents and increases confidence in analytical outputs.

Reliable pipelines are often more valuable than marginal performance improvements.

5. Monitor query performance, not just pipeline success

Many teams celebrate when a dbt job finishes successfully.

I care just as much about how efficiently it finished.

Both Snowflake and BigQuery expose detailed execution statistics including:

• bytes scanned

• query execution time

• warehouse utilisation

• partition pruning effectiveness

• expensive joins

• long-running transformations

Reviewing these metrics regularly helps identify models that gradually become more expensive as data grows.

Small improvements applied consistently across dozens of models often produce substantial savings over time.

Final Thoughts

Optimising dbt isn't about making SQL clever.

It's about designing models that remain reliable, maintainable and cost-effective as your organisation grows.

The biggest wins usually come from thoughtful architecture rather than complex optimisation tricks:

• choose the right materialisations

• minimise unnecessary transformations

• process only new data where possible

• automate data quality checks

• continuously monitor warehouse performance

These practices have helped me build data platforms that are faster to develop, cheaper to operate and more reliable for the teams that depend on them every day.

As AI increasingly becomes part of the analytics engineering workflow, these principles become even more important. Faster, cleaner and well-tested models provide better context for AI agents, reduce hallucinations caused by poor data quality and keep infrastructure costs under control as AI-driven workloads continue to grow.