does anybody have a query that gets any failed test results from the last dbt execution by querying the elementary package's own models? I'd like to run it as a separate step before generating the report with

edr

This is a databricks spark sql query that fetches all the tests that have fired warnings over the last x days. could be modified pretty easily to fetch what you're looking for

with per_test_summary as ( 

    select 
        etr.table_name,
        etr.column_name,
        etr.test_name,
        case job_id 
            when <insert here> then '<job name here>'
            when <insert here> then '<job name here>'
        else 'unknown' end as job_name,
        sum(case when etr.status = 'warn' then 1 else 0 end) as num_warnings,
        count(1) as num_runs
    from database.elementary_schema.elementary_test_results etr
    inner join (
        select distinct invocation_id, job_id
        from database.elementary_schema.dbt_invocations
        where job_id in (
              <insert job IDs>
            )
            and invocation_id is not null 
            -- Only invocations from last x complete days 
            and run_started_at >= date(from_utc_timestamp(current_timestamp, 'America/Los_Angeles')) - ({{ days_back }})
            and run_started_at <= date(from_utc_timestamp(current_timestamp, 'America/Los_Angeles')) 
    ) i on etr.invocation_id = i.invocation_id
    group by 1,2,3,4
)

select 
    *,
    num_warnings / num_runs as pct_of_time_warns
from per_test_summary
where num_warnings / num_runs >= {{ min pct time warning fired }}
order by 7 desc