Skip to main content

Custom Materializations in dbt + Snowflake

Custom Materializations​

What is a Custom Materialization?​

A Custom Materialization is when you write your own Jinja macro to tell dbt exactly what DDL/DML to execute when building a model. Instead of dbt running CREATE VIEW or CREATE TABLE, it runs your custom SQL.

This is the most advanced dbt feature and is used when none of the built-in materializations fit your use case.


Where to Create a Custom Materialization​

Custom materializations live in your project's macros/ directory:

my_dbt_project/
├── macros/
│ └── materializations/
│ └── create_table.sql ← Your custom materialization
│ └── create_stream.sql ← Your custom materialization
│ └── create_task.sql
├── models/
│ └── streams/
│ └── stream_raw_orders.sql ← Model using your custom materialization

Example 1: Custom Materialization to Create a Snowflake Table​

Use a custom table materialization when the table needs DDL that the built-in table materialization does not provide.

macros/materializations/create_table.sql​

{% materialization create_table, adapter='snowflake' %}

{%- set target_relation = this -%}
{%- set table_comment = config.get('table_comment', 'Managed by dbt custom materialization') -%}

{% call statement('main') %}

CREATE OR REPLACE TABLE {{ target_relation }}
COMMENT = '{{ table_comment }}'
AS
{{ sql }};

{% endcall %}

{{ return({'relations': [target_relation]}) }}

{% endmaterialization %}

Use it in a Model​

models/marts/customer_summary.sql​
{{ config(
materialized='create_table',
table_comment='Customer order summary managed by dbt'
) }}

SELECT
CUSTOMER_ID,
COUNT(*) AS TOTAL_ORDERS,
SUM(AMOUNT) AS TOTAL_AMOUNT
FROM {{ ref('stg_orders') }}
GROUP BY CUSTOMER_ID

Run It​

dbt run --select customer_summary

This creates or replaces CUSTOMER_SUMMARY as a Snowflake table using the SQL returned by the model.


Example 2: Custom Materialization to Create a Snowflake Stream​

A Snowflake Stream tracks changes (inserts, updates, deletes) on a table. dbt has no built-in support for Streams, so you must create a custom materialization.

Step 1: Define the Custom Materialization Macro​

macros/materializations/create_stream.sql​
{% materialization create_stream, adapter='snowflake' %}

{%- set target_relation = this -%}
{%- set source_table = config.get('source_table') -%}
{%- set append_only = config.get('append_only', false) -%}

{% call statement('main') %}

CREATE OR REPLACE STREAM {{ target_relation }}
ON TABLE {{ source_table }}
{% if append_only %}
APPEND_ONLY = TRUE
{% endif %}
SHOW_INITIAL_ROWS = TRUE
COMMENT = 'Managed by dbt custom materialization';

{% endcall %}

{{ return({'relations': [target_relation]}) }}

{% endmaterialization %}

How it works:

  • {% materialization create_stream, adapter='snowflake' %} — Registers a new materialization type called create_stream specifically for the Snowflake adapter.
  • config.get('source_table') — Reads the source table name from the model's config() block.
  • {{ return({'relations': [target_relation]}) }} — Tells dbt what database object was created so it can track it.

Step 2: Use it in a Model​

models/streams/stream_raw_orders.sql​
{{ config(
materialized='create_stream',
source_table='beems_dbt_project_demo.raw.raw_orders',
append_only=true
) }}

-- This SELECT is not used by the materialization,
-- but dbt requires every model to have a SELECT statement.
SELECT 1

Step 3: Run It​

dbt run --select stream_raw_orders

Result in Snowflake:

CREATE OR REPLACE STREAM STREAM_RAW_ORDERS
ON TABLE beems_dbt_project_demo.raw.raw_orders
APPEND_ONLY = TRUE
SHOW_INITIAL_ROWS = TRUE
COMMENT = 'Managed by dbt custom materialization';

You now have a Snowflake Stream managed entirely through dbt! 🎉


Example 3: Custom Materialization for a Snowflake Task​

A Snowflake Task is a scheduled SQL job inside Snowflake. Again, dbt has no built-in support, so you create a custom materialization.

macros/materializations/create_task.sql​

{% materialization create_task, adapter='snowflake' %}

{%- set target_relation = this -%}
{%- set warehouse = config.get('warehouse', 'COMPUTE_WH') -%}
{%- set schedule = config.get('schedule', '60 MINUTE') -%}
{%- set sql_body = config.get('sql_body') -%}

{% call statement('main') %}

CREATE OR REPLACE TASK {{ target_relation }}
WAREHOUSE = {{ warehouse }}
SCHEDULE = '{{ schedule }}'
COMMENT = 'Managed by dbt custom materialization'
AS
{{ sql_body }};

-- Tasks are created in SUSPENDED state by default
ALTER TASK {{ target_relation }} RESUME;

{% endcall %}

{{ return({'relations': [target_relation]}) }}

{% endmaterialization %}

Use it in a Model​

models/tasks/task_refresh_daily_summary.sql​
{{ config(
materialized='create_task',
warehouse='COMPUTE_WH',
schedule='1440 MINUTE',
sql_body="INSERT INTO beems_dbt_project_demo.dev.daily_order_summary SELECT CURRENT_DATE(), COUNT(*), SUM(amount) FROM beems_dbt_project_demo.raw.raw_orders"
) }}

SELECT 1

Result in Snowflake: A scheduled task that runs every 24 hours (1440 minutes) and inserts a daily summary row.


Example 4: Custom Materialization for DML with INSERT​

Custom materializations can execute DML instead of creating a relation from a model SELECT. This example inserts a daily audit row into an existing Snowflake table.

macros/materializations/insert_audit_row.sql​

{% materialization insert_audit_row, adapter='snowflake' %}

{%- set target_relation = this -%}
{%- set audit_table = config.get('audit_table') -%}
{%- set source_table = config.get('source_table') -%}

{% call statement('main') %}

INSERT INTO {{ audit_table }} (
MODEL_NAME,
ROW_COUNT,
INSERTED_AT
)
SELECT
'{{ target_relation.identifier }}',
COUNT(*),
CURRENT_TIMESTAMP()
FROM {{ source_table }};

{% endcall %}

{{ return({'relations': []}) }}

{% endmaterialization %}

Use it in a Model​

models/audit/log_orders_load.sql​
{{ config(
materialized='insert_audit_row',
audit_table='BEEMS_DBT_PROJECT_DEMO.AUDIT.DBT_MODEL_AUDIT',
source_table='BEEMS_DBT_PROJECT_DEMO.RAW.RAW_ORDERS'
) }}

SELECT 1

Run It​

dbt run --select log_orders_load

Each run executes the INSERT INTO statement and adds one audit row. Because the materialization returns an empty relation list, dbt does not treat log_orders_load as a table or view that it created.


When to Use Custom Materializations​

Use CaseCustom Materialization Name
Create a Snowflake table with custom DDLcreate_table
Track CDC changes on a tablecreate_stream
Schedule a recurring SQL job inside Snowflakecreate_task
Insert audit or event rows with DMLinsert_audit_row
Create a Snowflake Stored Procedurecreate_procedure
Manage Snowflake Alerts / Notificationscreate_alert
Important

Custom materializations are advanced. Only use them when no built-in materialization fits your needs. For most pipelines, table, incremental, and dynamic_table will cover 95% of your requirements.


Summary​

MaterializationWho Manages Refresh?SQL FlexibilityBest For
ViewQuery-time (on-the-fly)Full SQLStaging models
Tabledbt run (scheduled)Full SQLMart models
Incrementaldbt run (scheduled)Full SQLLarge fact tables
EphemeralCompiled inlineFull SQLLightweight helper logic
Materialized ViewSnowflake (automatic)❌ Limited (no JOINs)Simple aggregates
Dynamic TableSnowflake (automatic)✅ Full SQLReal-time pipelines
CustomYou define itYou define itTables, Streams, Tasks, and DML operations