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 calledcreate_streamspecifically for the Snowflake adapter.config.get('source_table')— Reads the source table name from the model'sconfig()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 Case | Custom Materialization Name |
|---|---|
| Create a Snowflake table with custom DDL | create_table |
| Track CDC changes on a table | create_stream |
| Schedule a recurring SQL job inside Snowflake | create_task |
| Insert audit or event rows with DML | insert_audit_row |
| Create a Snowflake Stored Procedure | create_procedure |
| Manage Snowflake Alerts / Notifications | create_alert |
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
| Materialization | Who Manages Refresh? | SQL Flexibility | Best For |
|---|---|---|---|
| View | Query-time (on-the-fly) | Full SQL | Staging models |
| Table | dbt run (scheduled) | Full SQL | Mart models |
| Incremental | dbt run (scheduled) | Full SQL | Large fact tables |
| Ephemeral | Compiled inline | Full SQL | Lightweight helper logic |
| Materialized View | Snowflake (automatic) | ❌ Limited (no JOINs) | Simple aggregates |
| Dynamic Table | Snowflake (automatic) | ✅ Full SQL | Real-time pipelines |
| Custom | You define it | You define it | Tables, Streams, Tasks, and DML operations |