Materialization options reference

This is a lookup table, not a tutorial. For the when-to-use-what framework, see the materializations concepts page. For a worked example, see the incremental guide.

view

{{ config(
    materialized='view',
    tags=['staging'],
    docs={'node_color': '#3a6e96'}
) }}

table

{{ config(
    materialized='table',
    file_format='delta',
    liquid_clustered_by=['customer_id', 'event_date'],
    tblproperties={'delta.autoOptimize.optimizeWrite': 'true'},
    databricks_compute='heavy'
) }}

incremental

{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='order_id',
    on_schema_change='append_new_columns',
    incremental_predicates=["DBT_INTERNAL_DEST.updated_at >= current_date - interval 7 days"],
    liquid_clustered_by=['order_id'],
    merge_exclude_columns=['_dbt_copied_at']
) }}

Strategy compatibility

on_schema_change behavior

materialized_view

{{ config(
    materialized='materialized_view',
    refresh_schedule={'cron': '0 * * * *', 'time_zone': 'UTC'},
    tblproperties={'pipelines.autoOptimize.managed': 'true'}
) }}

streaming_table

{{ config(
    materialized='streaming_table',
    tblproperties={'pipelines.reset.allowed': 'false'}
) }}

select * from stream({{ ref('events_raw') }})

Source must be a streaming-compatible Delta table or a registered streaming source. stream() wraps the ref.

ephemeral

{{ config(materialized='ephemeral') }}

Not a real materialization. dbt inlines the model's SQL as a CTE into every downstream model's compiled SQL.

Has no storage, no alias, no schema. Cannot be tested directly; the tests fold into the downstream consumer's SQL.

Compute routing

Define named compute in profiles.yml:

outputs:
  prod:
    databricks_compute:
      default: { http_path: /sql/1.0/warehouses/serverless-small }
      heavy:   { http_path: /sql/1.0/warehouses/serverless-large }
      python:  { http_path: /api/2.1/clusters/all-purpose-cluster-id }

Pin a model to named compute:

{{ config(databricks_compute='heavy') }}

Serverless SQL warehouses are the right default for SQL models. All-purpose clusters are for Python models and interactive notebooks, not batch SQL.

Table properties (tblproperties)

Common ones:

See also

• Materializations concepts — the decision framework.
• Incremental models guide — worked example of merge.
• Model authoring standards — Causeway's defaults per layer.