elementary doesnt insert row into elementary_test_results when execute dbt unit-tests

[megetron3]

i am using dbt 1.8 with unitest new feature and the latest elementary package. executing data tests generates the elementary_test_results table correctly. executing against unittest test will not insert row to the table. here is example test:

To Reproduce Use a simple dbt unit test:

unit_tests:
  - name: test_dora
    model: my_model
  ...

Expected behavior expecting the elementary_test_results to be populated with a row on run-end hook

[haritamar]

Hi @megetron3 ! Actually, the elementary dbt package doesn't support unit tests right now.

I'm not 100% sure if unit tests results have the same structure and can be added easily, it may require some research. If you're interested in contributing this I can provide some directions on where to look.

Thanks, Itamar

[SkinnyPigeon]

We'd love this to be implemented as well. Our analytics teams have started using unit tests so it would be nice for them to be able to track performance.

I've made this fork of one of dbt's jaffle shop repos. It's self-contained with duckdb and seed so just need to run the commands in the README to get the complied code.

For what it's worth, the unit tests are executed before the model is run. The code that is run is vanilla SQL, however, the tests mock all of the data that is being used. For instance, in this test case:

  - name: test_days_since_ordered
    description: Test my datediff caluclation for days_since_ordered
    model: stg_orders
    overrides:
      macros:
        dbt.current_timestamp: "DATE '2024-01-15'"
    given:
      - input: ref('raw_orders')
        rows:
          - {order_date: 2024-01-01}
    expect:
      rows:
        - {order_date: 2024-01-01, days_since_ordered: 14}

The code is compiled to the following:

with  __dbt__cte__raw_orders as (

-- Fixture for raw_orders
select cast(null as INTEGER) as id, cast(null as INTEGER) as user_id, 
    
    cast('2024-01-01' as DATE)
 as order_date, cast(null as character varying(256)) as status
), source as (
    select * from __dbt__cte__raw_orders

),

staged as (

    select 
        id as order_id,
        user_id as customer_id,
        order_date,
        datediff('day', cast(order_date as DATE), cast(DATE '2024-01-15' as DATE)) as days_since_ordered,
        status like '%pending%' as is_status_pending,
        case 
            when status like '%shipped%' then 'shipped'
            when status like '%return%' then 'returned'
            when status like '%pending%' then 'placed'
            else status
        end as status
    from source

)

select * from staged

From a user's standpoint, these don't appear to be being executed in any novel way so it is possible that your existing hooks would work.

On a manifest/graph level, they do have their own section (we have had to update processes we use to incorporate them). For instance, rather than appears in the nodes key or models key they have their own key unit_tests. The above example produces the following:

"unit_test.jaffle_shop.stg_orders.test_days_since_ordered": {
            "model": "stg_orders",
            "given": [
                {
                    "input": "ref('raw_orders')",
                    "rows": [
                        {
                            "order_date": "2024-01-01"
                        }
                    ],
                    "format": "dict",
                    "fixture": null
                }
            ],
            "expect": {
                "rows": [
                    {
                        "order_date": "2024-01-01",
                        "days_since_ordered": 14
                    }
                ],
                "format": "dict",
                "fixture": null
            },
            "name": "test_days_since_ordered",
            "resource_type": "unit_test",
            "package_name": "jaffle_shop",
            "path": "staging/unit_tests.yml",
            "original_file_path": "models/staging/unit_tests.yml",
            "unique_id": "unit_test.jaffle_shop.stg_orders.test_days_since_ordered",
            "fqn": [
                "jaffle_shop",
                "staging",
                "stg_orders",
                "test_days_since_ordered"
            ],
            "description": "Test my datediff caluclation for days_since_ordered",
            "overrides": {
                "macros": {
                    "dbt.current_timestamp": "DATE '2024-01-15'"
                },
                "vars": {},
                "env_vars": {}
            },
            "depends_on": {
                "macros": [],
                "nodes": [
                    "model.jaffle_shop.stg_orders"
                ]
            },
            "config": {
                "tags": [],
                "meta": {}
            },
            "checksum": "bf7a7bc696eab2c1ae3e365e926afaf3a148781e06ce39f0f538f2a71ecd9d1d",
            "schema": "main",
            "created_at": 1736938031.233261,
            "versions": null,
            "version": null
        },

Hope this helps, or at least gives you an idea of the scope of the work that might be required. I am not sure I will be able to carve out the time to actually prepare a PR but we think this would be a real nice-to-have feature