Reuse query to create multiple models

[juliana.campagnolo]

I have a base query that I would like to use in more than one model, making small changes. It is possible?
In the following query, I only need to change the #logo.

I need to create the filial_XX and filial_YY tables. The base query is the same, the only thing that would change is where ds_logomarca = XX or ds_logomarca = YY

select  
	f.cd_filial,
	f.nm_fantasia as nm_fantasia,
	f.ds_cidade as ds_cidade,
	f.sg_estado as sg_estado,
	f.ds_filial_regiao_micro as ds_filial_regiao_micro,
	f.cd_nr_cep as nr_cep,
	f.nr_cnpj as nr_cnpj
from {{ ref('filial') }} f
where f.cd_filial_tipo in(0, 1, 7)  
	and f.dt_encerramento is null  
	and f.cd_filial <> 901
	and f.cd_companhia = 1
	and ds_logomarca = #logo
order by f.cd_filial

[a_slack_user]

Looks like maybe you’re overpy zealous about creating models for just this. This looks like it could just be a simple CTE where it needs to be used. If it is being used a lot, then maybe the filtering part should be turned into a macro. If you really want it as a model though, I don’t see the harm in making it a view with the column instead of trying to filter on that value. Again, you can always filter in the specific place you’re using this. It doesn’t make sense to me to try to create multiple models for each case. If there’s more potential values in the future, it would probably become pretty confusing have a model just for every possible value.

_{Note: @Jack Nguyen originally posted this reply in Slack. It might not have transferred perfectly.}

[a_slack_user]

Hi, thanks for the reply!
I used this query just as an example, but we have a situation where we need to create files (via Redshift unload) that will be made available outside the organization, therefore, they need to be separated for security.
The objective of reusing the query base is to facilitate future maintenance, since this base would feed several models, and if any adjustments were necessary, this would be done in a single location.

_{Note: @juliana.campagnolo originally posted this reply in Slack. It might not have transferred perfectly.}

[brunoszdl]

Hi @juliana.campagnolo, you can try creating a macro like

macros.sql

{% macro filial_logo_sql(logomarca) %}
    select  
		f.cd_filial,
		f.nm_fantasia as nm_fantasia,
		f.ds_cidade as ds_cidade,
		f.sg_estado as sg_estado,
		f.ds_filial_regiao_micro as ds_filial_regiao_micro,
		f.cd_nr_cep as nr_cep,
		f.nr_cnpj as nr_cnpj
	from {{ ref('filial') }} f
	where f.cd_filial_tipo in(0, 1, 7)  
		and f.dt_encerramento is null  
		and f.cd_filial <> 901
		and f.cd_companhia = 1
		and ds_logomarca = '{{logomarca}}'
	order by f.cd_filial
{% endmacro %}

And then in your models call the macro like

filial_XX.sql

{{config(...)}}

{{filial_logo_sql('XX')}}

filial_XX.sql

{{config(...)}}

{{filial_logo_sql('YY')}}