Hi there, firstly thanks for the amazing job you're doing, I am huge fan of dbt !

❤️

Thanks for the kind words @hvassard.

I see what you are saying. It looks to me like the solution would be to move this further up in the logic.

Side note: check out store_failures_as if you haven't already! It allows storing failures either as a table or a view (or not at all via ephemeral).

More detail

The logic that is used to store the failures is located in a file like target/compiled/my_project/seeds/_seeds.yml/my_test_with_limit_and_store_failures.sql:

with meet_condition as(
  select *
  from "db"."feature_456"."customers"
),

validation_errors as (
  select *
  from meet_condition
  where
    -- never true, defaults to an empty result set. Exists to ensure any combo of the `or` clauses below succeeds
    1 = 2
    -- records with a value <= max_value are permitted. The `not` flips this to find records that don't meet the rule.
    or not ID <= 5
)

select *
from validation_errors

In my case, it does a "create table as" into this table:

• "db"."feature_456_dbt_test__audit"."my_test_with_limit_and_store_failures"

And then the actual test logic is in target/run/my_project/seeds/_seeds.yml/my_test_with_limit_and_store_failures.sql :

select
      count(*) as failures,
      count(*) != 0 as should_warn,
      count(*) != 0 as should_error
    from (
      
        select *
        from "db"."feature_456_dbt_test__audit"."my_test_with_limit_and_store_failures"
    
      limit 2
    ) dbt_internal_test

So you can see the first one does not have the limit clause applied, whereas the second one does.

Potential solution

So the solution might be to move this to here and here instead.

Transferred from dbt-core #9463 to #373

Reprex

Create these files:

seeds/customers.csv

id,first_name,last_name
1,Michael,P.
2,Shawn,M.
3,Kathleen,P.
4,Jimmy,C.
5,Katherine,R.
6,Sarah,R.

seeds/_seeds.yml

seeds:
  - name: customers
    columns:
      - name: id
        tests:
          - dbt_utils.accepted_range:
              name: my_test_with_limit_and_store_failures
              max_value: 1
              inclusive: true
              config:
                store_failures_as: table
                limit: 2

Run this command:

dbt build -s customers --full-refresh

Inspect the table and see that it has 5 results when according to the docs, it should only have 2:

When true, store_failures saves all records (up to limit) that failed the test. Failures are saved in a new table with the name of the test. By default, store_failures uses the schema {{ profile.schema }}_dbt_test__audit, but you can configure the schema suffix to a different value.