Generate Snapshots Dynamically via macros

Help
, , ,
1

Is there a way to create Snapshots Dynamically via macros instead of a model for each table ?

We have landing schema of 400 tables, we need a schema with SCD2 tracking so we are using dbt snapshots for this RAW schema. Instead of creating 400 models for this RAW schema, exploring is there a way to dynamically generate snapshots using macros and having the list of tables in a sql or yml file.

I’ve tried with a few macros scripts that can generate snapshots dynamically, while there are no errors in this, this does not give me the desired result. Do we can do this in dbt, if so what am I missing here ? Appreciate your thoughts.

Some example code or error messages

-- macros/generate_snapshot_sql.sql
{% macro generate_snapshot_sql(raw_tables) %}
{% for table_name in raw_tables %}
    {{ config(
        materialized='snapshot',
        unique_key='_FIVETRAN_ID',
        strategy='timestamp',
        updated_at='_FIVETRAN_SYNCED',
        target_schema=generate_schema_name('RAW'),
        alias=table_name ~ '_SNAPSHOT'
    ) }}

    select * from {{ ref(table_name) }}
{% endfor %}
{% endmacro %}


snapshots/my_snapshots.sql
{% set raw_tables = ['WFD_TABLE'] %}
{{ generate_snapshot_sql(raw_tables) }}
2

hey Yesh - good question! Macros are fantastic for running functions within a given file, but aren’t designed for manipulating the file system itself (e.g. creating many new snapshot or model files all at once).

I’ve seen some teams use python scripts for programmatic generation of files like this. The workflow would be something like:

  1. Create python script that outputs a file per snapshot with the base syntax applied
  2. Commit changes to the relevant git repo
  3. Pull and/or approve changes. From there, dbt can run the relevant logic within each model or snapshot when you trigger a run

I haven’t used this package personally, but have come across it in other discussions previously. Might be worth taking a look at for ideas: dbt-generator/dbt_generator at main · tuanchris/dbt-generator · GitHub

1 Like