Trouble rendering actual query

[kjana-rv]

I created a unit test but I found that this line of code https://github.com/EqualExperts/dbt-unit-testing/blob/1733bdb798d927a6d7122d8e150bf44825e168f9/macros/sql_builders.sql#L21 does not seem to be rendering correctly when running: dbt test --select tag:unit-test

Any ideas why this might be happening?

Copying below the compiled result, test, and DBT model:

Compiled result:

select * FROM 
      (
          select count(1) as expectation_count from (
              select count(1) as count, `anonymous_id` from (
                  select 'a' as anonymous_id 
                ) as s 
                group by `anonymous_id`
            ) as exp
        ) as exp_count,
      (
          select count(1) as actual_count from (
              select count(1) as count, `anonymous_id` from ( 
                  with `stg_decision_provided` as (
                      select 'a' as anonymous_id
                    ),
                    `stg_address_entry_conversion` as (
                        select 'a' as anonymous_id
                    )
              select * from () as t 
        ) as s 
        group by `anonymous_id`
    ) as act) as act_count

Test:

{{
    config(
        tags=['unit-test','no-db-dependency']
    )
}}

{% set options = {"include_missing_columns": true} %}

{% call dbt_unit_testing.test('int_address_entry_conversion', 'test not working') %}

  {% call dbt_unit_testing.mock_ref ('stg_decision_provided') %}
    select 'a' as anonymous_id
  {% endcall %}

  {% call dbt_unit_testing.mock_ref ('stg_address_entry_conversion') %}
    select 'a' as anonymous_id
  {% endcall %}

  {% call dbt_unit_testing.expect() %}
     select 'a' as anonymous_id 
  {% endcall %}
{% endcall %}

DBT Model (int_address_entry_conversion):

{{
  config(
    materialized=('table' if target.name != 'dev' else 'view'),
    partition_by={
      "field": "conversion_timestamp",
      "data_type": "timestamp",
      "granularity": "day"
    },
    cluster_by = ['decision_requested_id']
  )
}}

with stg_decision_provided as (
    select
        anonymous_id,
        decision_requested_id,
        timestamp
    from {{ dbt_unit_testing.ref('stg_decision_provided' ) }} 
),
stg_address_entry_conversion as (
    select
        anonymous_id,
        event_id as conversion_event_id,
        timestamp as conversion_timestamp,
        event_name as conversion_event_name
    from {{ dbt_unit_testing.ref('stg_address_entry_conversion') }}
),
within_last_7_days as (
    select
        stg_decision_provided.anonymous_id, 
        stg_decision_provided.decision_requested_id,
        stg_address_entry_conversion.conversion_event_id,
        stg_address_entry_conversion.conversion_timestamp,
        concat(stg_address_entry_conversion.conversion_event_name, '_within_7_days') as conversion_event_name
    from stg_decision_provided
    left join stg_address_entry_conversion 
    on stg_decision_provided.anonymous_id=stg_address_entry_conversion.anonymous_id
    where stg_address_entry_conversion.conversion_timestamp
    between stg_decision_provided.timestamp and DATE_ADD(stg_decision_provided.timestamp, INTERVAL 7 DAY)
)

select * from within_last_7_days 

[psousa50]

Hi @kjana-rv, the dbt-init-testing framework had an issue with dbt version 1.3, it was fixed in release v0.2.2. Is this the release that you are using?

[kjana-rv]

Hi, @psousa50 great question! I'm currently at dbt version 1.0 and dbt-unit-testing v0.2.0.

[psousa50]

ok, so please upgrade to version v0.2.2 and try again. Thanks! 👍

[kjana-rv]

That worked! I was able to test a success and failure case. The only other thing I haven't figured out is how to make the feedback more readable. I tried adding the output_sort_field config option as per the README doc, but the feedback is still limited to displaying just that there was a row mismatch in the failure case. However, the code to get the row diffs can be found if I go to the execution details.

{% call dbt_unit_testing.test('int_address_entry_conversion', 
                              'my test',
                              {"output_sort_field": "anonymous_id"}) %}

Thank you so much for your help! I'm happy to open another ticket for this if it is unrelated.

[psousa50]

No worries, glad that I could help 🙂 I'm afraid I didn't quite understand your issue with the feedback, could you elaborate a bit more on that, please? Tx!

[kjana-rv]

Sorry for any confusion; I misread the docs on Test Feedback and thought you had to pass the output_sort_field as an option.

The issue I'm having is that the test feedback output seems to be incomplete; here's a screenshot:

I was expecting this result to show up in the log output right after Rows mismatch with the differences against the defined records in dbt_unit_testing.expect():

which I was able to get by copying and running the generated query in the logs:

I haven't dived deep enough, but It looks like this function is not printing the output: https://github.com/EqualExperts/dbt-unit-testing/blob/33d5cc1762da9bc73828741fac26204333ed80be/macros/tests.sql#L127

Hope this helps!

[psousa50]

Are you using dbt cloud? The print_table method is not supported on dbt could 😞

[kjana-rv]

Yes, I am using DBT cloud. Are you aware if there is any limitation to supporting a similar function in DBT cloud? Happy to help research the topic.

[psousa50]

I think we'll need to develop a similar function ourselves, but that's not an easy task when you think you have to do it in Jinja

[psousa50]

Hi @kjana-rv, we just released v.0.2.3 with support for dbt cloud. Please try it and let us know if it works for you. Thank you!

[kjana-rv]

@psousa50 it's been a while, but finally got to test the fix and it works! Thank you so much for the effort.