Why can't my macro see the values inside of the columns it's given?

Help
,
1

Hi everyone! I am new with macro creation and I have an issue about it.

Problem

I am triyng to send column values to the macro, and inside it I have a few Ifs conditions. But the validation is not carrying out, as the column name has not value (only receive a string as x.name)

The context

My model has this column

{{ get_city('d.country') }} name

and inside the macro, for example, I have this condition:

{% macro get_city(coutry) -%}
...
{%- if country == 'text' -%}
   	        'ans'

If the condition is equal to:

{%- if country == 'd.coutry' -%}

It returns True

Extra Info

The engine is Big Query

Please excuse my redaction. Other kind of info that you need, let me know! Thks :raised_hands:

2

You should use Jinja to generate SQL, not to run transformations directly. You’ll have to work hard to go from a country name to a city name using Jinja, and that’s because it’s not a good practice.

How were you going to do this inside of your macro? If it was going to be a series of if statements, you can get your warehouse to do the work instead, e.g. by creating a seed file that contains the mappings and joining against that, or by creating a case statement.

If the case statement is going to be large and reused in multiple places, then you could leave that in a macro, but you should be thinking about macros as SQL generators where the work is ultimately still run against the warehouse.

3

Hi Joel!

Before all, Thanks a lot for your answer

I guees that it is correct if I share the total idea, and from here, maybe you could say me what is the best option in that case:

Context

First idea:

As I showed, my idea was share column value to the macro, and inside the macro return the final value depend on if the column value is equal to a string.

But it was not the “total” idea. Inside the macro there are a series of if statments (as you said) but a series of arrays too (variables).

Why do I have this?
Because in each if statement, I verified if the column value is in array (not equal to string). And this was the problem that I showed (not receive the column value)

Second idea:

Do not use macro, and have the same variables (the arrays inside a dict ) in the .sql. Avoiding the use of if statement, I implemented case when statements, but I can not looping on array and use this statement (case when) because the alias will be repeated.

ex.

		{%- for region, cities in coutries.items() %}
				{%- for city in cities %}
						case when column_value = '{{city}}' then '{{region}}' end as 'alias'
				{%- if not loop.last -%}, {%- endif-%}
				{%- endfor -%}
		{%- if not loop.last -%}, {%- endif-%}
    	{%- endfor -%}

Conclusion

I do not want to share a “wall text”, but I hope that my ideas are clear. Let me know if something does not understood

From you answer, do you say that it could be a good idea work this logic inside the warehouse? :thinking:

4

If you have a dictionary of cities and regions, then I would do that using an in clause, something like this:

case
  {%- for region, cities in coutries.items() %}
    when column_value in ('{{ cities | join("', '") }}') then '{{region}}' 
  {%- endfor -%}
end as alias

However you might also like to make a seed (a csv file with the mappings) and join against that:

select 
  model.*,
  region_lookup.region
from {{ ref('some_model') }} as model
left outer join {{ ref('region_lookup') }} as region_lookup on model.column_value = region_lookup.city

It seems to me that the bigger issue you’re going to have is that city names are not unique, and either of these approaches would cause issues when you have two cities with the same name.

1 Like