Custom dbt test is failing

Help
, ,
1

The problem I’m having

I want to create a macro where it checks if the column exists in a model by querying the information_schema.columns table in snowflake. if it returns TRUE then i want the test to pass as it means the column exists. if it returns FALSE then I want the test to fail as it means the column doesnt exist.

this is my macro:

– macros/column_exists.sql
{% macro test_column_exists(model, column_name) %}
{% set table = adapter.get_relation(
database=model.database,
schema=model.schema,
identifier=model.identifier
) %}
{% set table_name = table.identifier %}
{% set database = table.database %}
{% set schema = table.schema %}

with columns as (
select column_name
from {{ database }}.information_schema.columns
where table_name = ‘{{ table_name }}’
and table_schema = ‘{{ schema }}’
)
select
case when count(*) = 1 THEN TRUE
ELSE FALSE
end as “EXISTS?”
from columns
where column_name = ‘{{ column_name }}’
{% endmacro %}

this is my schema.yml file:

models:

  • name: export
    description: ‘MONTHLY2 export’
    columns:
    • name: COL
      description: “”
      tests:
      • column_exists:
        column_name: ‘COL’

this is the compiled code:

with columns as (
select column_name
from CLIENT_EXPORTS.information_schema.columns
where table_name = ‘BT_EXPORT’
and table_schema = ‘BT’
)
select
case when count(*) = 1 THEN TRUE
ELSE FALSE
end as “EXISTS?”
from columns
where column_name = ‘COL’

The context of why I’m trying to do this

I want to test my model for columns that must exist because if there is a schema change then it must be flagged.

Some example code or error messages

Got 1 result, configured to fail if != 0

What I’ve already tried

since the error message is always “configured to fail if !=0”, I have tried to make the output 0 when the column exists. But this still fails.

case when count(*) = 1 then 0 else 1 end as failures

2

Hey Darren, you could use the expect_column_to_exist test from dbt_expectations

Let me know if there is a reason you can’t use it