Hi,
I have two Redshift models model1 and model2 that reuse the same CTE except for one filter, so
Model1.sql
With example_cte as (select * from mydata where column1 = 1 and filter2 and filter3 qualify row_number() over (partition by col1, col2 order by col1) = 1),
select * from table2 left join example_cte on example_cte.foreign_key = table2.id
Model2.sql
With example_cte as (select * from mydata where column1 = 2 and filter2 and filter3 qualify row_number() over (partition by col1, col2 order by col1) = 1),
select * from table2 left join example_cte on example_cte.foreign_key = table2.id
I now created an ephemeral model for the CTE in order to avoid duplicating logic but can’t figure out a way to set the filter on column1 dynamically inside the model files of model1 and model2.
Is there a way to do that?
Thank you!