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
{% 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 %}
