# A dynamic schema test for nested & repeated BigQuery fields

**URL:** <https://discourse.getdbt.com/t/a-dynamic-schema-test-for-nested-repeated-bigquery-fields/743>\
**Category:** Show and Tell\
**Tags:** testing, bigquery\
**Created:** [December 9, 2019, 6:12pm UTC](https://discourse.getdbt.com/t/a-dynamic-schema-test-for-nested-repeated-bigquery-fields/743 "2019-12-09T18:12:38Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![joshtemple](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/joshtemple/32/62_2.png) [@joshtemple](https://discourse.getdbt.com/u/joshtemple)\
**Post date:** [December 9, 2019, 6:12pm UTC](https://discourse.getdbt.com/t/a-dynamic-schema-test-for-nested-repeated-bigquery-fields/743/1 "2019-12-09T18:12:38Z")

</div>

Nested and repeated records (`STRUCT` and `ARRAY` of `STRUCT` types) in BigQuery are really powerful for performance and organizing, but can be frustrating to test in dbt because they require some extra SQL (namely, the use of `UNNEST`) to expose correctly.

I spent some time a few months ago working on a macro to dynamically unnest a column and run a not null test based on a `.` delimited specifier. I learned a lot about Jinja, especially its recursive capabilities. Here’s how I did it.

## Example case

Imagine we have a nested order/order items table called `orders` that looks like this:

| order\_id | items.item\_id | items.quantity | promo\_codes | address.city | address.state |
| --- | --- | --- | --- | --- | --- |
| 6301418492 | 13939 | 1 | | New York | NY |
| | 32482 | 4 | | | |
| | 59221 | 1 | | | |
| 4929138818 | 13193 | 3 | [HOLIDAY2019, EMP40] | Baltimore | MD |

In this table, `items` is an `ARRAY` of `STRUCT` (nested and repeated), `promo_codes` is an ARRAY of `STRING` (repeated but not nested), and `address` is a `STRUCT` (nested but not repeated).

Our goal is to write a generalizable schema test called `not_null_nr` that finds null records in any kind of nested/repeated data at any depth of nesting.

We should be able to write our `schema.yml` file like this:

```yaml
columns:
  - name: items.item_id
    tests:
      - not_null_nr
  - name: promo_codes
    tests:
      - not_null_nr
  - name: address.city
    tests:
      - not_null_nr

```

## Writing the tests manually

Let’s start by imaging how we would write these tests manually in SQL. If we want to test that `order_id` is not null, we can write a simple test like this:

```sql
select order_id
from orders
where order_id is null

```

If we try to write the same test on a nested, repeated field like `items.item_id`…

```sql
select items.item_id
from orders
where items.item_id is null

```

…we get an error from BigQuery:

```plaintext
Cannot access field item_id on a value with type ARRAY

```

Turns out, if we want to test `item_id`, we have to `UNNEST` and join our `items` field to our `orders` table. Now our test might look like this:

```sql
select order_id, item.item_id
from orders
left join unnest(items) item
where item.item_id is null

```

What about our `address.city` field, which is nested but not repeated? In that case the test is simpler because we don’t need to `UNNEST`.

```sql
select order_id, address.city
from orders
where address.city is null

```

But wait, there’s more! It’s also possible that we have a field that is repeated but not made up of `STRUCT` types, like an `ARRAY` of `INT64`.

Take a look at our `promo_codes` column, which falls into this category. We want to make sure none of those promo codes are null. We write a test in that situation like this:

```auto
select order_id, code
from orders
left join unnest(orders.promo_codes) code
where code is not null

```

In this test we still need to `UNNEST`, but we don’t need a `.` selector because the items in the array are not `STRUCT` type.

## Generalizing the schema test

So to generalize this schema test properly, it needs to cover 3 cases\*:

1. The field is **nested** but **not repeated** , e.g. `{'item_id': 100, 'qty': 10}`
2. The field is **nested and repeated** , e.g. `[{'item_id': 100, 'qty': 1}, {'item_id': 200, 'qty': 2}]`
3. The field is **not nested** but is **repeated** , e.g. `[1, 2, 3, 4]`

\*_There is one more case, neither nested nor repeated, but I’m leaving it up to the user to run regular schema tests on those columns instead._

The macro should accept the name of the model and the name of the field to be tested. We should be able to specify any depth of nesting in the field name, e.g. `level_1.level_2.level_3.level_4` and the macro should perform the appropriate `UNNEST`. This suggests some kind of recursion, which we’ll get into in a minute.

## Constructing the macro

I start off by defining the macro itself and the parameters it should accept. It has to start with `test_` so dbt recognizes it as a custom schema test.

```jinja
{% macro test_not_null_nr(model, column_name) -%}

```

We’re going to be getting into some nested loops, and Jinja doesn’t have the ability to track variables from outer loops within the inner loop (see the section [here](https://jinja.palletsprojects.com/en/2.10.x/templates/#assignments) on Scoping Behavior).

As a workaround, I create a `namespace` called `vars` with two variables, `level` and `column_list`, which will allow us to track some variables globally.

```jinja
{%- set vars = namespace(level=1, column_list=[]) -%}

```

Next, I split the `column_name` on `.` so we can see the requested levels of nesting and assign it to a new variable called `hierarchy`.

```jinja
{%- set hierarchy = column_name.split('.') -%}

```

I pull all the database fields from the model and get the one that corresponds to our top level `hierarchy` field so we have a rich representation of the column and its children. For example, dbt has a flag to tell us if columns are repeated or not based on the information it gets from the BigQuery.

The `selectattr` filter here gets the item in the list `columns` that has attribute `name` equal to the first element in our `hierarchy` list.

```jinja
{%- set columns = adapter.get_columns_in_table(model.schema, model.table) -%}
{%- set root = columns | selectattr('name', 'equalto', hierarchy[0]) | list -%}

```

Now that we’ve set up our variables, I inject the basic SQL to construct the test, counting the number of rows with a `FROM` that corresponds to our first level (the model itself).

```sql
select count(*)
from {{ model }} level_{{ vars.level }}

```

Next, I’m going to recurse through the children of our `root` object until we arrive at the final level of `hierarchy`. Jinja2 has a [recursive loop definition](https://jinja.palletsprojects.com/en/2.10.x/templates/#list-of-control-structures).

```jinja
{% for child in root recursive %}

```

The loop is responsible for generating the correct `UNNEST` statements to unroll the table to a state where we can access the final field in `hierarchy`. Within the loop, I do the following:

1. Add the `name` of the child to the `column_list`, which we’ll use for creating strings like `level_1.level_2.level_3`
2. Whenever we reach a child that is a repeated field (as indicated by the `mode` attribute on the child), we increase `vars.level`, we need to `UNNEST` and join the field so we can access it.
3. If the child has a `fields` attribute, we need to go deeper, so I call the `loop` variable to recurse on the field that matches the name in the corresponding level of `hierarchy`.

```jinja
{% for child in root recursive %}
  {%- set vars.column_list = vars.column_list + [child.name] -%}
  {%- if child.mode == 'REPEATED' -%}
    {%- set vars.level = vars.level + 1 %}
    left join unnest(level_{{ vars.level - 1 }}.{{ vars.column_list | join('.') | string }}) as level_{{ vars.level }}
    {%- set vars.column_list = [] -%}
  {%- endif -%}

  {%- if child.fields -%}
    {{ loop(child.fields | selectattr('name', 'equalto', hierarchy[loop.depth]) | list) }}
  {%- endif -%}

{% endfor %}

```

Finally, I add a `WHERE` clause to perform the actual test. At this point, `vars.level` corresponds to the deepest level (the loop exited at) and we can build the field selector from our `column_list`.

```jinja
where level_{{ vars.level }}
{%- if vars.column_list -%}
.{{ vars.column_list | join('.') | string }}
{%- endif %}
is null

```

## The completed macro

As far as I can tell (I’ve tested this on a bunch of sample combinations), this works. Of course, adapting this for a unique test or other type of custom schema test will require more effort, but the bones should be there.

If you end up identifying any issues or improving on this in any way, let me know!

```jinja
{% macro test_not_null_nested(model, column_name) -%}

{# Workaround to avoid Jinja loop scope issues #}
{%- set vars = namespace(level=1, column_list=[]) -%}

{%- set hierarchy = column_name.split('.') -%}
{%- set columns = adapter.get_columns_in_table(model.schema, model.table) -%}
{%- set root = columns | selectattr('name', 'equalto', hierarchy[0]) | list -%}
select count(*)
from {{ model }} level_{{ vars.level }}

{% for child in root recursive %}
{%- set vars.column_list = vars.column_list + [child.name] -%}
{%- if child.mode == 'REPEATED' -%}
{%- set vars.level = vars.level + 1 %}
left join unnest(level_{{ vars.level - 1 }}.{{ vars.column_list | join('.') | string }}) as level_{{ vars.level }}
{%- set vars.column_list = [] -%}
{%- endif -%}

{%- if child.fields -%}
{{ loop(child.fields | selectattr('name', 'equalto', hierarchy[loop.depth]) | list) }}
{%- endif -%}

{% endfor %}

where level_{{ vars.level }}
{%- if vars.column_list -%}
.{{ vars.column_list | join('.') | string }}
{%- endif %}
is null

{%- endmacro %}

```

---

<div class="post-metadata">

**Author:** ![dain](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/dain/32/666_2.png) [@dain](https://discourse.getdbt.com/u/dain)\
**Post date:** [March 9, 2021, 2:53pm UTC](https://discourse.getdbt.com/t/a-dynamic-schema-test-for-nested-repeated-bigquery-fields/743/2 "2021-03-09T14:53:32Z")

</div>

Thanks a lot for this!

I got some deprecation notices, so had to change this line:

```
{%- set columns = adapter.get_columns_in_table(model.schema, model.table) -%}

```

To these instead:

```
{%- set relation = api.Relation.create(schema=model.schema, identifier=model.table) -%}
{%- set columns = adapter.get_columns_in_relation(relation) -%}
```

---

<div class="post-metadata">

**Author:** ![doug.beatty](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/doug.beatty/32/1940_2.png) [@doug.beatty](https://discourse.getdbt.com/u/doug.beatty)\
**Post date:** [May 12, 2023, 3:47pm UTC](https://discourse.getdbt.com/t/a-dynamic-schema-test-for-nested-repeated-bigquery-fields/743/3 "2023-05-12T15:47:03Z")

</div>

Supporting this logic for arbitrary tests got raised as a feature request here:

> <https://github.com/dbt-labs/dbt-core/issues/7613>
>
> \### Is this your first time submitting a feature request?
> 
> \- \[X\] I have read the… \[expectations for open source contributors\](https://docs.getdbt.com/docs/contributing/oss-expectations)
> \- \[X\] I have searched the existing issues, and I could not find an existing issue for this feature
> \- \[X\] I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion
> 
> \### Describe the feature
> 
> \# Context
> When adding column metadata, for example, to a model YML file, it is already possible to reference a nested column in order to add a description. E.g.
> \`\`\`yaml
> columns:
> \- name: foo.bar
> description: baz
> \`\`\`
> 
> Presumably, DBT is doing something clever and traversing dot notation. This works today and if configured as such and supported by the adapter, this description will be written back to the database. Notably, this works regardless of whether foo is a struct or an array (using BQ terminology here but the same concepts exist in other DBs). I assume this works regardless of depth too but I've not explicitly verified this. Generic tests will even work if foo is a struct (again, presumably with arbitrary depth).
> 
> However, generic tests won't work in the case of a repeated/array field - and this is not surprising - different SQL would be needed to unnest repeated fields. E.g. this will cause an error during the not\_null test execution.
> \`\`\`columns:
> - name: foo.bar # foo is an array
> tests:
> - not\_null
> \`\`\`
> 
> \[Someone from the community came up with an implementation for a nested not null test a while ago\](https://discourse.getdbt.com/t/a-dynamic-schema-test-for-nested-repeated-bigquery-fields/743). It makes for some good inspiration but it has fallen short of what it should have been! The nested data logic should not be limited to not\_null - it should be a generic wrapper test that calls out to some other generic test. This can be achieved by having the test wrap do nothing more than setting the \`model\` variable to be a sub-query. DBT actually does the exact same thing when you provide \`config/where\`. This way, the nested data test can be a totally generic wrapper, constructing a single column of data and then calling the relevant test. The only limitation is not all columns can be passed to the downstream test which the downstream test would ideally like to have in the case of \`store\_failures = True\` but this is a small (and reasonable limitation).
> 
> I have an alternative implementation to this test that works in a generic way and we are using it for several downstream generic tests. The only limitation is that the test has to have some logic like the below which explicitly calls the relevant test:
> \`\`\`jinja
> {% if test == "accepted\_values" %}
> {{ test\_accepted\_values(model, "column", kwargs.get("values"), kwargs.get("quote", True))}}
> {% elif test == "not\_null" -%}
> {{ test\_not\_null(model, "column") }}
> {% elif test == "relationships" %}
> {{ test\_relationships(model, "column", kwargs.get("to"), kwargs.get("field")) }}
> {% elif test == "unique" %}
> {{ test\_unique(model, "column") }}
> {%- endif %}
> \`\`\`
> 
> As far as I can tell, within a jinja-only context, this can't be gotten around for two reasons:
> 1. Jinja doesn't allow you to pass/expand/unpack kwargs - e.g. some\_test(\*\*kwargs)
> 2. My \`test\` variable cannot be used to dynamically determine the macro to call - jinja's not quite object-oriented enough for that.
> 
> \# The Feature Request
> DBT core should do the SQL wrapper before it calls an existing generic test. It should do something similar to what my jinja is doing, but far easier in python, and basically parse the \`foo.bar.baz\` column name, ask the adapter for the column metadata and then traverse it and build the subquery before calling the test. It's pretty straight forward stuff - just a case of adding \`CROSS JOIN UNNEST(\<x\>) AS \<y\>\` whenever it encounters a repeated field. There's slightly more to it than that but the community post does a great job of explaining the logic and I'm happy to provide our improvement too.
> 
> \### Describe alternatives you've considered
> 
> Doing it myself via a custom generic test that is a clever wrapper - but as described in main part of the post, has some unavoidable limitations - as well as being a missed opportunity to benefit the rest of the community.
> 
> \### Who will this benefit?
> 
> Everyone that wants to test nested data. Huge impact!
> 
> \### Are you interested in contributing this feature?
> 
> Not familiar with dbt-core - hopefully someone can take the logic and just implement it in the right place, having thought through any implications.
> 
> \### Anything else?
> 
> \_No response\_