Rollup materialization on Snowflake

example_model.sql

-- models/example_model.sql


{{ config(
    materialized='rollup',
    rollups={
        "hourly": "date_trunc('hour', event_time)",
        "daily": "date_trunc('day', event_time)"
    },
    aggregates={
        "count_events": "count(*)",
        "count_distinct_users": "count(distinct user_id)"
    }
) }}

with gen as (
    select
        row_number() over (order by 1) as i
  from table(generator(rowcount => 1000))
)

select 
    dateadd('minute', i * 43, '2019-01-01 12:00:00'::timestamp) as event_time,
    uniform(1, 20, random()) as user_id
from gen

rollup.sql

-- macros/rollup.sql

{% macro create_rollup(base_view, suffix, rollup_expr, aggregates) %}

    {% set suffixed_identifier = base_view.identifier ~ "_" ~ suffix %}
    {% set suffixed_relation = base_view.incorporate(table_name=suffixed_identifier) %}
    {% set rollup_sql %}
        select
            {{ rollup_expr }} as period
            {% for agg_name, agg_expr in aggregates.items() %}
                , {{ agg_expr }} as {{ agg_name }}
            {% endfor %}
        from {{ base_view }}
        group by 1
        order by 1
    {% endset %}

    {% do run_query(create_table_as(False, suffixed_relation, rollup_sql)) %}

{% endmacro %}

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

    {% set rollups = config.get('rollups') %}
    {% set aggregates = config.get('aggregates') %}
    {%- set base_view = api.Relation.create(
        identifier=model['alias'],
        schema=schema,
        database=database,
        type='view'
    ) -%}


    --build the base view
    {% call statement('main') -%}
        {{ create_view_as(base_view, sql) }}
    {%- endcall %}

    -- build the rollup tables
    {% for rollup_name, rollup_expr in rollups.items() %}
        {% do create_rollup(
            base_view,
            rollup_name,
            rollup_expr,
            aggregates
        ) %}
    {% endfor %}

{% endmaterialization %}