snowflake-cloud-data-platformdbt

DBT Test Response: Compilation Error macro 'dbt_macro__test' takes no keyword argument 'model'


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>


Solution

  • 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.