How to build Snowflake dynamic tables in a dev environment without auto-refresh?

Help
, ,
1

In our dbt setup, developers run test builds that clone / create models into their dev schemas. When a developer is testing a dynamic table, it’ll build that table into their dev schema which means it will auto-refresh based on the configured lag.

Is there a way to automatically suspend a dynamic table if it’s built in a dev environment, such as with a config parameter? One way I was thinking about this was using on-run-end to detect the dev environment + materialization type across all models, but that seems clunky / overkill to have to loop over every model. I’d also rather not have to include a config for every dynamic model.

2

you may have figured out a way to do this but for others landing here, we’ve been using this macro to set config for all of our dynamic tables that extends the normal config macro. forcing DOWNSTREAM allows our tables to create in dev and never refresh on their own as they won’t have any tables relying on them with a schedule.

{% macro dynamic_table_config(
  materialized = 'dynamic_table',
  warehouse = target.warehouse,
  target_lag = '6 hours',
  unique_key = ['_id']
) %}

{{
  config(
    materialized = materialized,
    target_lag = target_lag if target.name in ('prod', 'stg') else 'DOWNSTREAM',
    snowflake_warehouse = warehouse,
    unique_key = unique_key,
    **kwargs
  )
}}
{% endmacro %}