# Error: dbt Tests on partitioned table fail

**URL:** <https://discourse.getdbt.com/t/error-dbt-tests-on-partitioned-table-fail/14829>\
**Category:** Help\
**Tags:** jinja, testing, bigquery, dbt-core\
**Created:** [August 12, 2024, 4:30pm UTC](https://discourse.getdbt.com/t/error-dbt-tests-on-partitioned-table-fail/14829 "2024-08-12T16:30:32Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![madm4niac](https://avatars.discourse-cdn.com/v4/letter/m/c57346/32.png) [@madm4niac](https://discourse.getdbt.com/u/madm4niac)\
**Post date:** [August 12, 2024, 4:30pm UTC](https://discourse.getdbt.com/t/error-dbt-tests-on-partitioned-table-fail/14829/1 "2024-08-12T16:30:33Z")

</div>

## The problem I’m having

The dbt tests are failing on a partitioned table. When I check the table through BQ console it returns 0 but the results of the dbt tests results say that the test fail, unable to meet the criteria **!=0**

```auto
10 of 12 ERROR not_null_with_partition_raw_table __meta_use_case___ meta_inserted_at
Got 1 result, configured to fail if != 0

```

## The context of why I’m trying to do this

To check whether the fields have any nulls

## What I’ve already tried

I created a test script **not\_null\_with\_partition** under the macros folder and referenced it in the **schema.yml**

## Some example code or error messages

not\_null\_with\_partition.sql (under macros) -

```auto
{% set airflow_info = fromjson(var('airflow_info', '{}')) %}
{% test not_null_with_partition(model, column_name, partition_column) %}
with invalid_records as (
    select *
    from {{ model }}
    where {{ column_name }} is null
      and {{ partition_column }} >= get_max_meta_inserted_at( {{ model }}, airflow_info) 
)
select count(*)
from invalid_records
{% endtest %}

```

schema.yml (under models) -

```auto
version: 2

models:
  - name: raw_table
    description: something
    config:
      contract:
        enforced: true
    columns:
      - name: id
        data_type: STRING
        description: The unique identifier for each record - generate_uuid()
      - name: _meta_use_case
        data_type: STRING
        description: The use case of the data
        data_tests:
          - not_null_with_partition:
              partition_column: _meta_inserted_at 
      - name: _meta_source_file
        data_type: STRING
        description: The source file of the data
        data_tests:
          - not_null_with_partition:
              partition_column: _meta_inserted_at
      - name: _meta_run_id
        data_type: STRING
        description: Airflow job run ID
        data_tests:
          - not_null_with_partition:
              partition_column: _meta_inserted_at
      - name: _meta_inserted_at
        data_type: TIMESTAMP
        description: CURRENT_TIMESTAMP() - when the record was inserted into the table
        data_tests:
          - not_null_with_partition:
              partition_column: _meta_inserted_at

```

max\_inserted\_at (under macros) -

```auto
{% macro get_max_meta_inserted_at(table_ref, airflow_info) %}
    {% set query %}
        select max(_meta_inserted_at) 
        from {{ table_ref }}
        where _meta_inserted_at >= safe_cast('{{ airflow_info["ingested_at"] }}' as timestamp)
    {% endset %}
    
    {% set max_meta_inserted_at = dbt_utils.get_single_value(query) %}
    {{ return("'" ~ max_meta_inserted_at ~ "'") }}
{% endmacro %}

```

Checking \_meta\_use\_case for nulls in BQ console -

```auto
with invalid_records as (
    select *
    from `project.dataset.raw_table` 
    where _meta_use_case is null
      and _meta_inserted_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY) -- to be replaced with output from get_max_meta_inserted_at
)
select count(*)
from invalid_records

```

 ![image](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/2X/5/5368e2279a596c846ed1f83b8c1fb7da8ad2d465.png)

---

<div class="post-metadata">

**Author:** ![brunoszdl](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/brunoszdl/32/4113_2.png) [@brunoszdl](https://discourse.getdbt.com/u/brunoszdl)\
**Post date:** [August 12, 2024, 5:22pm UTC](https://discourse.getdbt.com/t/error-dbt-tests-on-partitioned-table-fail/14829/2 "2024-08-12T17:22:24Z")

</div>

Try using store\_failures ( [store\_failures | dbt Developer Hub](https://docs.getdbt.com/reference/resource-configs/store_failures) ) to be able to see the record dbt is ‘failing’. Maybe it helps the investigation