Core Materializations

Core Materializations define the fundamental strategies for transforming and persisting data within a data warehouse using dbt. These materializations abstract the complex SQL DDL and DML required to manage different types of database objects such as tables, views, incremental tables, and materialized views.

The materialization system relies on a standardized lifecycle: setting up relations, executing pre-hooks, building the model, handling grants, persisting documentation, and performing final cleanup. This ensures consistent behavior across different database adapters while allowing for adapter-specific overrides where necessary.

Table Materialization

The table materialization creates a physical table in the database by executing a CREATE TABLE AS (CTAS) statement. It uses an atomic swap pattern to minimize downtime: data is built into an intermediate relation, then renamed to the target relation while the old relation is moved to a backup and eventually dropped.

Workflow Logic

The following diagram illustrates the lifecycle of a table materialization, emphasizing the use of intermediate and backup relations to ensure reliability.

Key Components

Sources: table.sql:3-16

Incremental Materialization

Incremental materializations allow dbt to only process new or modified records instead of rebuilding the entire table. This is achieved through strategies like append, merge, or delete+insert.

Data Flow and Schema Management

Incremental models handle schema changes dynamically based on the on_schema_change configuration. They compare the temporary relation (containing new data) with the existing relation to determine if columns need to be added or if types need expansion.

Incremental Configurations

Sources: incremental.sql:12-16, incremental.sql:41-50

Materialized View Materialization

The materialized_view materialization manages relations that persist results but are automatically updated by the database. Unlike standard tables, these often support configuration changes without a full rebuild.

Configuration Change Handling

When a materialized view exists, dbt checks for configuration changes. Depending on the on_configuration_change setting, it may alter the view, fail the run, or warn the user.

Seed Materialization

Seeds are used to load CSV files into the database as tables. The materialization handles the conversion of CSV data into SQL INSERT statements or CREATE TABLE commands.

Lifecycle of a Seed

1. Load Agate Table: dbt loads the CSV file into an internal Agate table structure.
2. Table Existence Check:
   • If it's a view, an error is raised.
   • If it's a table and full_refresh is true, it is reset.
   • If it doesn't exist, a new table is created.
3. Data Loading: Rows are loaded using load_csv_rows.
4. Post-Processing: Indexes are created and documentation is persisted.

Sources: seed.sql:1-68

Summary of Core Features

The core materializations share a common architecture designed for atomicity and metadata management. Every materialization follows a strict hook execution order: pre-hooks (outside and inside transaction), the main build statement, post-hooks (inside and outside transaction), and finally an adapter commit. This ensures that documentation, grants, and data are always in a consistent state upon completion.

Sources: table.sql:31-74, seed.sql:22-63, materialized_view.sql:110-128