Generic row count test in dbt

posted on 2021-11-01

If you have a model in dbt and want to make sure it has at least N number of rows, here is how to write a generic test you can add to your schema.yml.

In the macros folder, defaults to ./macros, create a file called test_row_count.sql with the following code:

{% test row_count(model, column_name, above) %}
    select row_count
    from (select count({{ column_name }}) as row_count from {{ model }})
    where row_count <= {{ above }}
{% endtest %}

The code is written for Snowflake SQL and will count the number of rows for a given model and if the row count is not above a given number, it will result in a selected row and an error.

To use the test, simply add the following to your schema.yml:

version: 2

models:
- name: your_model_name
    columns:
    - name: any_column_you_want
        tests:
        - row_count:
            above: 300

And that is it!