Relations Cache Optimizer
Relevant source files
The following files were used as context for generating this wiki page:
- dbt-snowflake/src/dbt/include/snowflake/macros/metadata/list_relations_without_caching.sql
- dbt-adapters/src/dbt/include/global_project/macros/materializations/models/incremental/incremental.sql
- dbt-bigquery/src/dbt/include/bigquery/macros/adapters.sql
- dbt-postgres/src/dbt/include/postgres/macros/adapters.sql
- dbt-spark/src/dbt/include/spark/macros/adapters.sql
- dbt-adapters/src/dbt/include/global_project/macros/adapters/metadata.sql
- dbt-adapters/CHANGELOG.md
The Relations Cache Optimizer is a systemic approach within dbt-adapters to manage and optimize how dbt interacts with database metadata. Its primary purpose is to reduce redundant metadata queries to the data warehouse by utilizing a local cache of relations (tables, views, etc.), while providing fallback mechanisms for bypass-caching when necessary.
This system is integrated into core materialization workflows, such as incremental and table materializations, ensuring that relation existence checks and schema validations are performed efficiently. By optimizing catalog generation and relation listing, it minimizes the overhead on the database's information schema.
Sources: dbt-adapters/src/dbt/include/global_project/macros/materializations/models/incremental/incremental.sql:3-10, dbt-adapters/src/dbt/include/global_project/macros/adapters/metadata.sql:75-80
Core Architecture and Data Flow
The architecture relies on a distinction between cached relation lookups and direct metadata fetches. The system typically attempts to load a relation from the local cache first before resorting to a live query.
Relation Lookup Logic
When a materialization starts, it uses load_cached_relation(this) to determine if the target relation already exists. If the relation is not in the cache, the adapter may invoke list_relations_without_caching to synchronize the local state with the remote database.
Sources: dbt-adapters/src/dbt/include/global_project/macros/materializations/models/incremental/incremental.sql:4-8, dbt-adapters/src/dbt/include/global_project/macros/adapters/metadata.sql:75-77
The diagram shows the logic flow for determining if a relation exists, prioritizing the local cache before performing a remote metadata query.
Implementation Details by Adapter
Different adapters implement optimized listing strategies to handle large schemas and varying database capabilities.
Snowflake Pagination Optimization
The Snowflake adapter implements a specialized paginated version of list_relations_without_caching. This prevents performance degradation in schemas with tens of thousands of objects by using a watermark-based loop.
- Max Iterations: Defaults to 10,000, configurable via
list_relations_page_limit. - Results per Page: Defaults to 10,000, configurable via
list_relations_per_page. - Watermark: Uses the last object name fetched to continue the next iteration.
Sources: dbt-snowflake/src/dbt/include/snowflake/macros/metadata/list_relations_without_caching.sql:3-18
Postgres and BigQuery Direct Fetching
In contrast to pagination, adapters like Postgres and BigQuery perform targeted queries against specific system tables (e.g., pg_tables, pg_views) when a cache miss occurs.
Sources: dbt-postgres/src/dbt/include/postgres/macros/adapters.sql:84-110, dbt-bigquery/src/dbt/include/bigquery/macros/adapters.sql:84-86
Key Functions and Macros
The following table summarizes the primary macros used to optimize relation metadata handling:
| Macro | Purpose | Implementation Style |
|---|---|---|
load_cached_relation | Retrieves relation metadata from dbt's internal cache. | Global / Internal |
list_relations_without_caching | Directly queries the database for all relations in a schema. | Adapter-specific (Snowflake, Spark, etc.) |
get_catalog_for_single_relation | Optimizes catalog generation by targeting one relation. | Feature / Capability |
snowflake__show_objects_sql | Generates SQL for paginated Snowflake object listing. | Snowflake-specific |
Sources: dbt-adapters/src/dbt/include/global_project/macros/adapters/metadata.sql:75-80, dbt-snowflake/src/dbt/include/snowflake/macros/metadata/list_relations_without_caching.sql:53-61, dbt-adapters/CHANGELOG.md:329-331
Incremental Materialization Integration
The Relations Cache Optimizer is critical during incremental runs to manage intermediate and backup relations.
This sequence demonstrates how materializations interact with the cache to handle lifecycle operations for temporary and backup relations.
Sources: dbt-adapters/src/dbt/include/global_project/macros/materializations/models/incremental/incremental.sql:3-23
Handling Type Mismatches and Swaps
In full-refresh modes, the system uses the cache to identify the backup_relation_type. If the existing_relation is identified as a view but the target is a table, the optimizer ensures the cache is updated correctly after the swap (rename) operation.
Sources: dbt-adapters/src/dbt/include/global_project/macros/materializations/models/incremental/incremental.sql:8-15, dbt-adapters/src/dbt/include/global_project/macros/materializations/models/table.sql:40-45
Metadata Lifecycle Management
- Discovery: Relations are discovered via initial catalog queries or
list_relations_without_caching. - Persistence: Relations are stored in the dbt cache to prevent redundant
DESCRIBEorSHOWcommands. - Invalidation: When
drop_relationorrename_relationis called, the adapter signals the cache to invalidate or update the corresponding entry.
Sources: dbt-adapters/src/dbt/include/global_project/macros/adapters/metadata.sql:75-95, dbt-postgres/src/dbt/include/postgres/macros/adapters.sql:84-88
Configuration Flags
Users can tune the behavior of the optimizer, particularly for paginated listing, using project flags:
| Flag | Default | Description |
|---|---|---|
list_relations_per_page | 10,000 | Number of results per SQL iteration (e.g., Snowflake). |
list_relations_page_limit | 10,000 | Maximum number of iterations before throwing a warning. |
Sources: dbt-snowflake/src/dbt/include/snowflake/macros/metadata/list_relations_without_caching.sql:10-15
The Relations Cache Optimizer serves as the bridge between dbt's model logic and the physical state of the database. By providing a structured way to bypass the cache when needed and paginating large metadata requests, it ensures that dbt remains performant even as project scale increases.