# Creating Dynamic Queries with Jinja and model.yml files

**URL:** <https://discourse.getdbt.com/t/creating-dynamic-queries-with-jinja-and-model-yml-files/4936>\
**Category:** Show and Tell\
**Tags:** jinja, yaml\
**Created:** [September 20, 2022, 6:01pm UTC](https://discourse.getdbt.com/t/creating-dynamic-queries-with-jinja-and-model-yml-files/4936 "2022-09-20T18:01:50Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![iamwilling](https://avatars.discourse-cdn.com/v4/letter/i/ecd19e/32.png) [@iamwilling](https://discourse.getdbt.com/u/iamwilling)\
**Post date:** [September 20, 2022, 6:01pm UTC](https://discourse.getdbt.com/t/creating-dynamic-queries-with-jinja-and-model-yml-files/4936/1 "2022-09-20T18:01:50Z")

</div>

### Problem Context:

We have various fields that we do/don’t want to surface to end users in Looker for any given transformation. However, the fields are necessary for transformation logic. In a gross sense, our query relationships goes `staging` → `transformation` → `surface only selected fields to visualization tools ('Clean')`. However, manually maintaining our `Clean` queries is growing into a bit of a pain, and we’ve run into some frustrating situations doing so. A key part of this is that we don’t want to actually maintain the SQL in the `Clean` queries.  
We also have limited resources to implement this from a BigQuery or Infra side of things, so we had to do this only with what dbt has to offer.

### Solution

We have added a property to every column entry, that we can read via Jinja. This greatly simplifies things, as we already document each column, both as a matter of good practice, and to centralize documentation in dbt–all of our descriptions are written to BQ, and Looker now has the capability to import the descriptions into LookML when creating view files. Part of the design of this is to operate on an allow-list basis–the column must have the property, and the property must = `true`. At some point, a column must be defined as being allowed/not allowed, and we decided that dbt is right place for us, given our constraints.

I had avoided using the `tags` hierarchy, as the .yml files were getting pretty big.

An example of the `model.yml`:

```auto
version: 2

models:
  - name: upstream_query
    description: "blablabla"
    columns:
    - name: field_1
      description: "blablabla"
      is_allowed: true
    - name: field_2
      description: "blablabla"
      is_allowed: false
    - name: field_3
      description: "blablabla"
      is_allowed: true

```

The macro:

```auto
{% macro get_allowed_columns_macro(tablename) -%}
	{% if execute %}
		{%- set column_list = [] -%}
		{%- for node in graph.nodes.values() -%}
			{%- if node.name == tablename -%}
				{%- for column, properties in node.columns.items() -%}
					{%- do column_list.append(column) if properties.get('is_allowed') == true -%}
				{%- endfor -%}
			{%- endif -%}
		{%- endfor %}
		{{ return(column_list) }}
	{% endif %}
{%- endmacro %}

```

An example query:

```auto
/* Define the table you are referring to */
{% set tableref = 'upstream_query' -%}
/* Create a [list] of columns that are flagged as field_name == true */
{% set allowed_columns = get_allowed_columns_macro(tableref) -%}

SELECT
{%- for columns in allowed_columns -%}
{%- if not loop.first %}
, {{ columns }}
{%- else %}
{{ columns }}
{%- endif -%}
{%- endfor %}

FROM {{ ref(tableref) }}

```

Which will compile to:

```auto
SELECT
field_1
, field_3

FROM `database.dataset.upstream_query`

```

---

<div class="post-metadata">

**Author:** ![antoine.falieres](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/antoine.falieres/32/2737_2.png) [@antoine.falieres](https://discourse.getdbt.com/u/antoine.falieres)\
**Post date:** [July 18, 2023, 12:46pm UTC](https://discourse.getdbt.com/t/creating-dynamic-queries-with-jinja-and-model-yml-files/4936/2 "2023-07-18T12:46:09Z")

</div>

Hey ! is it possible to do the other way around ?  
I mean using the columns in the model.sql then allowing them in the model.yml ?