testing a single CTE in dbt

user_3517025 0
Viewed 550

can you test a single CTE as opposed to a model? For example, in:

# models/mymodel.sql

with my_cte as (
  {%- set tables = dbt_utils.get_relations_by_pattern('my_schema%', 'prefix%') -%}
  {%- set my_columns = ['my_col1', 'mycol2', 'mycol3',...,]
  {{ dbt_utils.union_relations(relations = tables, include=my_columns ) }}
),

clean_cols as (
  select 
    my_col1::numeric as mycol1,
    coalesce(mycol2, mycol3) as my_coalesced_col23,
    ....
  from my_cte
)

select * from clean_cols

I’d want to test that mycol2 and mycol3 are mutually exclusive (I have a custom generic test)

NOTE: I need those things to stay in the same file, otherwise it becomes very difficult to manage correctness with a long list of columns. Having the columns to include and select all in one place follows the best practice of rename and recast fields once and in one place.

dbt
1 Answers

In your current implementation, the answer is no. However, if you use the ephemeral materialization, it totally is possible! In this case you break your first CTE into it's own .sql model, then configure it like below. In this way you can test the unioner.sql model, but when you invoke dbt run, there will not be a unioner model, the logic instead gets injected into the my_cte definition in my_model instead.

-- models/unioner.sql
{{
  config(
    materialized = 'ephemeral',
    )
}}
{%- set tables = dbt_utils.get_relations_by_pattern('my_schema%', 'prefix%') -%}
{{ dbt_utils.union_relations(relations = tables ) }}

-- models/mymodel.sql

with my_cte as (
  SELECT * FROM {{ ref('unioner') }}
),

clean_cols as (
  select 
    my_col1,
    coalesce(mycol2, mycol3)
    ....
  from my_cte
)

select * from clean_cols
user_38426100
Related