I have two tables in snowflake database. One was generated manually (Table1) and other using DBT Model (Table2). Both these tables should have same number of records in them. I am trying to write a DBT Test that can make that comparison for me.
However, my test keeps returning compilation error:
"Compilation Error in test record_count_prontoform_prod_lptr_grower_prontoform_prod_lptr_grower__LPTR_GROWER_RAW_SUBMISSIONS__RAW_SUBMISSIONS (models\marts\schema_prontoform_prod_lptr_plant.yml) macro 'dbt_macro__test_record_count' takes no keyword argument 'model'"
I wrote following Test in the DBT Project:
SQL File Name: test_record_count.sql
-- count_match_test.sql
{% test record_count(source_name, source_table, model_name) %}
WITH
table1_count AS (
SELECT COUNT(*) AS count FROM {{ source(source_name, source_table) }}
),
table2_count AS (
SELECT COUNT(*) AS count FROM {{ ref(model_name) }}
)
SELECT
CASE WHEN t1.count = t2.count THEN 'pass'
ELSE RAISE_ERROR('Record count mismatch between ' || source_name || ' and ' || model) END AS test_result
FROM table1_count t1
JOIN table2_count t2 ON t1.count = t2.count
{% endtest %}
Schema.yml
- name: prontoform_prod_lptr_grower
description: 'Mapping ids to question labels'
data_tests:
- record_count:
source_name: "LPTR_GROWER_RAW_SUBMISSIONS"
source_table: "RAW_SUBMISSIONS"
model_name: "prontoform_prod_lptr_grower"
columns:
- name: submission_id
description: 'submission_id of form'
data_tests:
- not_null
- unique
- name: question_id
description: 'Unique question ID used for mapping'
data_tests:
- not_null
- name: question_label
description: 'The label of the question'
data_tests:
- not_null
Test Run:
(dbt_venv) PS C:\prontoforms_dbt\dbt-snowflake> dbt test --select prontoform_prod_lptr_grower 23:37:52 Running with dbt=1.8.6 23:37:52 Registered adapter: snowflake=1.8.3 23:37:53 Encountered an error: Compilation Error in test record_count_prontoform_prod_lptr_grower_prontoform_prod_lptr_grower__LPTR_GROWER_RAW_SUBMISSIONS__RAW_SUBMISSIONS (models\marts\schema_prontoform_prod_lptr_plant.yml) macro 'dbt_macro__test_record_count' takes no keyword argument 'model' (dbt_venv) PS C:\prontoforms_dbt\dbt-snowflake>
Custom tests are required to take in a model
parameter and can optionally take in a column_name
parameter. From the DBT documentation:
To define your own generic tests, simply create a test block called <test_name>. All generic tests should accept one or both of the standard arguments:
- model: The resource on which the test is defined, templated out to its relation name. (Note that the argument is always named model, even when the resource is a source, seed, or snapshot.)
- column_name: The column on which the test is defined. Not all generic tests operate on the column level, but if they do, they should accept column_name as an argument.
The model name is implicitly passed in based on your yaml configuration, and it's passed in as a ref object so you can simply refer to it in your test. Also, to fail the test, you want to return an non-empty result set.
{% test record_count(model, source_name, source_table) %}
WITH
table1_count AS (
SELECT COUNT(*) AS count FROM {{ source(source_name, source_table) }}
),
table2_count AS (
SELECT COUNT(*) AS count FROM {{ model }}
)
select 1 from table1_count, table2_count where table1_count.count != table2_count.count;
{% endtest %}
In your yaml:
- name: prontoform_prod_lptr_grower
description: 'Mapping ids to question labels'
data_tests:
- record_count:
source_name: "LPTR_GROWER_RAW_SUBMISSIONS"
source_table: "RAW_SUBMISSIONS"
When the test fails, it will show an error saying 1 record failed. If you want to see the count instead, change select 1
to select table2_count.count
.