Custom Materializations

Concepts covered: Materializations

materialized='table' names a macro. When a node's turn comes, dbt hands that macro the compiled SELECT as sql, the node as model and the relation it should produce as this, and the macro decides every statement that follows. The table materialization creates a temporary table from the SELECT, renames the existing table out of the way, renames the new one into place, drops the old one and reports what now exists. view sends one create or replace view. A snapshot is a materialization with the same contract and a longer body, and a project can supply its own.

statement('main') is the name dbt watches. The rows affected and the timing it prints for the node come from the statement with that name, and fetch_result=True on a statement is what run_query uses underneath to read rows back. A materialization may send other statements under other names; only main is reported as the model.

A materialization of 20 lines

A warehouse that stores tables as files on object storage partitions each table by a column named at creation. The built-in table macro never writes that property, and a post_hook arrives too late, because the table is already laid out. The wrapping around the SELECT has to change, which is the one job a hook cannot do. Here are the 20 lines of macros/partitioned_table.sql, grouped by what each does:

{{ config(
    materialized='partitioned_table',
    partition_by=['dt']
) }}

select
    cast(ordered_at as date) as dt,
    order_id,
    amount_cents
from {{ ref('stg_orders') }}

create table analytics.fct_orders_daily
with (partitioned_by = array['dt'])
as
select
    cast(ordered_at as date) as dt,
    order_id,
    amount_cents
from analytics.stg_orders

The model's compiled SELECT in target/compiled/ looks like any other model's; the create table ... with (...) wrapper appears only in target/run/, where dbt writes the statements it sent. config.require() fails the node when the materialization runs, with a compiler error naming the missing key. dbt parse and dbt compile never execute a materialization's body, so neither catches it. Notice what this version gives up: it drops the old table before creating the new one, so a reader querying between the 2 statements finds nothing. The built-in table avoids that with a temporary relation and a rename. Writing a materialization means paying again for every property the built-in already had.

default after the name makes the macro apply on any adapter. A second definition with adapter='name' wins on that adapter alone, which is how the built-ins specialize without the model knowing.

The built-in table uses 3 relations: make_intermediate_relation(target) for the new table, make_backup_relation(existing) for the old one, and the target itself for the name readers use. Create, rename, rename, drop: the gap in which this names nothing is a single rename. Copy that shape when a reader may be querying while you build, and the 20 lines above become 30.

When writing one is justified

Write a materialization when

• The DDL around the SELECT has to differ: a table property, a storage layout, an object that is neither a table nor a view
• Which statements run depends on what exists: a found relation takes one path, nothing found takes another
• The same wrapping belongs to many models and should live in 1 macro rather than in a hook on each

Reach for something else when

• The work happens after the table exists: a grant, an audit row. That is a post_hook
• A config already does it. persist_docs, set once in dbt_project.yml, writes model and column descriptions to the warehouse as comments; other needs have configs of their own, among them schema, alias and contract
• One model needs it. A model-specific pre_hook and post_hook cost 2 lines and no new contract

Materializations are also the layer with the least help when they break. A wrong return value leaves dbt's relation cache out of step with the warehouse, and the next run's drop or rename fails against an object of the wrong type. A missing run_hooks(post_hooks) silently drops every post_hook in the project for models of that type. Treat a custom materialization as adapter code: a short file, a test model in the project that uses it, and a review by someone who has read the built-in it replaces.

About this lesson

This section is part of the Jinja and Macros: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.