# Dynamically get snapshot strategy using macros

**URL:** <https://discourse.getdbt.com/t/dynamically-get-snapshot-strategy-using-macros/14062>\
**Category:** Help\
**Tags:** snapshots, macros\
**Created:** [July 4, 2024, 10:39pm UTC](https://discourse.getdbt.com/t/dynamically-get-snapshot-strategy-using-macros/14062 "2024-07-04T22:39:04Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![kneeyoh15](https://avatars.discourse-cdn.com/v4/letter/k/f05b48/32.png) [@kneeyoh15](https://discourse.getdbt.com/u/kneeyoh15)\
**Post date:** [July 4, 2024, 10:39pm UTC](https://discourse.getdbt.com/t/dynamically-get-snapshot-strategy-using-macros/14062/1 "2024-07-04T22:39:04Z")

</div>

## The problem I’m having

using a macro to return the strategy type in a snapshot.

## The context of why I’m trying to do this

I want to return the strategy based on a sql query using the macro below

## Some example code or error messages

```auto
{% snapshot snapshot__dbo_Job_Notes %}
{{
    config(
        target_schema = var('snapshot_schema'),
        target_database = var('snapshot_database'),
        tags = ['sedona', 'job','operations', 'org_IA'],
        unique_key = 'item_sk',
        strategy = get_snapshot_strategy('dbo', 'Job_Notes', 'IA'),
        updated_at = 'etl_timestamp',
        check_cols = ['Job_Notes_Id', 'Job_Id', 'Notes', 'Access_Level', 'UserCode', 'Entered_Date', 'Edit_UserCode', 'Edit_Date', 'Note_Type_Id'],
        post_hook = snapshot__post_hook(this)
    )
}}
select * from {{ ref('stg__dbo_Job_Notes') }}
{% endsnapshot %}

I want to return the strategy based on a sql query using the macro below

{% macro get_snapshot_strategy(schema, table, org_code) %}
declare @etl_method as varchar(30) = NULL 
declare @etl_mode as varchar(30) = NULL 
select 
  @etl_method = t.etl_method, 
  @etl_mode = t.etl_mode 
from 
   source_tables t 
  join organizations o on o.org_id = t.org_id 
  and o.org_code = {{org_code}} 
  and o.is_active = 1 
where 
  t.table_source = {{table}} 
  and t.schema_source = {{schema}} 
  and t.is_active = 1 case when @etl_method IS NULL 
  OR @etl_mode IS NULL then return '''' else 
select 
  case 
	when @etl_mode = 'Full' then '''check''' 
	else '''timestamp''' end 
  end as result
{% endmacro %}

```

## Error:

 ![image](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/2X/5/5d626006d2bd8de9610cb009f95b6227fb249f90.png)

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [July 4, 2024, 11:16pm UTC](https://discourse.getdbt.com/t/dynamically-get-snapshot-strategy-using-macros/14062/2 "2024-07-04T23:16:14Z")

</div>

It’s not possible to set configs of any kind based on the outcome of a SQL query. Because configs are set prior to actually running any sort of SQL query.  
[https://github.com/dbt-labs/docs.getdbt.com/discussions/1310](https://github.com/dbt-labs/docs.getdbt.com/discussions/1310)

Note: `@Jeremy Yeo` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1720134778522069?thread_ts=1720132767.645869&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![kneeyoh15](https://avatars.discourse-cdn.com/v4/letter/k/f05b48/32.png) [@kneeyoh15](https://discourse.getdbt.com/u/kneeyoh15)\
**Post date:** [July 8, 2024, 1:39pm UTC](https://discourse.getdbt.com/t/dynamically-get-snapshot-strategy-using-macros/14062/3 "2024-07-08T13:39:14Z")

</div>

@a_slack_user Thanks. what i am trying to do is call macro from the strategy field in snapshot. the macro i am calling is what has the SQL. is that possible?