Warehouse-specific performance configurations allow dbt to optimize physical table layouts, query pruning, and compute sizing directly in model definitions. By tuning storage and execution properties for BigQuery, Snowflake, and Databricks, data teams can reduce query scan volumes, execution times, and warehouse computing costs.
BigQuery partition and cluster controls
In Google BigQuery, performance and cost depend directly on bytes scanned. Key model configurations include:
- Partitioning divides tables by day, month, or integer ranges. Setting partition_by with order_date ensures queries filtering on date prune unneeded partitions.
- Clustering sorts data within partitions by up to four columns (such as customer_id and status), accelerating filter and aggregation performance.
- Setting require_partition_filter forces all downstream queries to include a partition filter in their WHERE clause, preventing accidental full-table scans.
These settings protect large fact tables from costly full scans.
Snowflake clustering and compute sizing
For Snowflake environments, dbt provides controls over table micro-partitions and virtual warehouse compute:
- Defining cluster_by on multi-terabyte tables maintains micro-partition sorting for frequently filtered columns.
- Setting transient true eliminates Fail-safe storage and reduces Time Travel windows to one day, lowering storage overhead on intermediate and staging models.
- The snowflake_warehouse config overrides the default virtual warehouse for a specific model. You can run light staging views on an X-Small warehouse while routing a heavy monthly aggregation model to an X-Large warehouse.
This matches compute sizing directly to model complexity.
Databricks and incremental predicates
On Databricks Delta Lake, modern performance tuning focuses on clustering and merge optimization:
- The liquid_clustered_by config uses Delta Lake Liquid Clustering to automatically balance data layout without rigid partition hierarchies.
- File format settings guarantee Delta format is used for ACID transactions and data caching.
- The incremental_predicates config provides explicit partition pruning expressions during incremental merges across all warehouses. Adding date boundaries to incremental_predicates restricts the target table scan to recent partitions instead of scanning the full historical dataset during merges.
These configurations prevent incremental updates from degrading into slow full-table scans as data volumes grow.