CODEWITHSUNDEEP
dbt
Joey Baruch
Joey Baruch

Reputation: 5249

testing a single CTE in dbt

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.

Upvotes: 2

Views: 1447

Answers (1)

Anders Swanson
Anders Swanson

Reputation: 3961

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

Upvotes: 2

Related Questions