Singular test - How to skip testing all models inside a JOIN ?

Help
,
1

Hi there

I have two models and I need to test whether all lines from one model were rolled up to a second model.

Testing is very easy as I only need to create a singular test and write a LEFT OUTER JOIN between these 2 models, but when I run “dbt test --select s4_venda_mensal”, dbt run the singular test which fails because lines from “s4_venda_mensal” (model one) were not moved to “s4_ocupacao” (model two).

In my case, the singular test is only relevant to test “s4_ocupacao” (model two), but dbt understands that all models referred in singular test should be tested when “dbt test” is invoked for individual model.

Question: how to skip testing a given singular test from a given model?

That’s my singular test script:

SELECT
    vnd.nr_competencia,
    vnd.pk_contrato,
    vnd.vl_venda
FROM {{ ref('s4_venda_mensal') }} AS vnd
LEFT OUTER JOIN {{ ref('s4_ocupacao') }} AS ocu
    ON  ocu.nr_competencia = vnd.nr_competencia
    AND ocu.pk_contrato    = vnd.pk_contrato
WHERE vnd.nr_competencia IN (SELECT nr_competencia FROM var_periodo)
  AND ocu.pk_contrato IS NULL

As said, this script is only relevant to test model name s4_ocupacao. When I run “dbt test --select sr_venda_mensal” it would be nice if the singular test would be skipped.

Any thoughts ?

2

You might be looking for --indirect-selection=cautious
Details here: https://docs.getdbt.com/reference/node-selection/test-selection-examples?indirect-selection-mode=eager#indirect-selection

Note: @Owen originally posted this reply in Slack. It might not have transferred perfectly.

3

Interesting documentation. Thanks for sharing.

Well, it only works when adding to selectors. Is there another way to make it valid for all selectors?

It would be nice to set on config section of singular test, but it think it’s not possible.

Maybe, concatenating enrironment variables to get schema name with table name (constant string). It will bypass ref function, but it’s an ugly solution.