Triggers

Overview

Triggers are a special type of materialization that let you react to events in your data. When you define a Trigger you provide a SQL select statement and, a python function or Jinja macro to be executed when your trigger fires. When a Trigger is materialized, your select statement is used to define a view. Whenever any rows fall into that view, your function/macro will be called.

Triggers are the primary mechanism for building event based pipelines in Balto. You can even use Triggers with the Balto orchestration metadata views to create complex cross-project workflows.

Usage

Syntax

To create a trigger add a new sql model with a materialization type of trigger and handler set to the FQN of the stored procedure that will be called when your trigger fires.

{{
    config(
        materialized="trigger",
        handler="your_handler"
    )
}}

select order_id
from {{ source('jaffle_shop', 'orders') }} as source
left join {{ ref('stg_orders') }} as target on source.order_id = target.order_id
where target.order_id is null

Handlers must accept a single string argument, which is a reference to the stream which holds any new rows for your handler to process. The stream can be referenced as follows:

CREATE OR REPLACE PROCEDURE dbt.test_handler(source_stream STRING)
    RETURNS VARCHAR NULL
    LANGUAGE SQL
    EXECUTE AS OWNER
AS $$
BEGIN
    insert into some_table
    select count(*)
    from identifier(:source_stream);
END;
$$;

Limitations

Triggers are implemented in Snowflake using a stream on top of a view created from your Trigger's select clause. This means that the same general limitations for streams on views in Snowflake apply to your Trigger.

Examples

Push application event metrics

The following example creates a Trigger which will push a DataDog metric with the count of large payments tagged by payment_method.

{{
    config(
        materialized="trigger",
        handler="metric_handler"
    )
}}

select
    'jaffle_shop.payments.large_payments' as metric_name,
    'count' as metric_type,
    1 as metric_value,
    object_construct('payment_method', payment_method) as metric_tags
from {{ ref('stg_payments') }}
where amount > 1000