Ref's in unit test not mocked

[Jay-diehl]

Hi,

I have the following unit test. In the macros I have overwritten the ref function with the DBT unit test version. However when I run the unit test it doesn't mock the data for the tables and it expects all the rows. I have verified all the columns & data types. Anybody has a good way to debug the issue?

-- depends_on: {{ ref('stg_visma__werknemers') }}
-- depends_on: {{ ref('int_visma__contract_data') }}
-- depends_on: {{ ref('int_visma__functie_rooster_historie') }}
-- depends_on: {{ ref('stg_portal__kostenplaats_visma_mapping') }}
-- depends_on: {{ ref('int_fourps__werknemers') }}
-- depends_on: {{ ref('int_easyjob__werknemers') }}

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

{{ config(tags=['test-test2', 'int-unit-tests'], enabled = true) }}

{% call dbt_unit_testing.test('int_werknemers', 'Check if the total overview is created correct.') %}

{% call dbt_unit_testing.mock_ref('stg_visma__werknemers', options = options)   %}
SELECT 1                  AS werknemer_id,
       'John'             AS voornaam,
       'Doe'              AS achternaam,
       'John Doe'         AS volledige_naam,
       '1990-01-01'::date AS geboortedatum,
       '2020-01-01'::date AS datum_in_dienst,
       'vast'             AS vast_of_inlener
UNION ALL
SELECT 2                  AS werknemer_id,
       'Jane'             AS voornaam,
       'Doe'              AS achternaam,
       'Jane Doe'         AS volledige_naam,
       '1991-01-01'::date AS geboortedatum,
       '2021-01-01'::date AS datum_in_dienst,
       'inlener'          AS vast_of_inlener
{% endcall %}

{% call dbt_unit_testing.mock_ref('int_visma__contract_data', options = options) %}
SELECT 1                     AS werknemer_id,
       0.7::double precision AS parttime_factor,
       'A1'                  AS kostenplaats,
       'Piet'                AS manager,
       '2020-01-01'::date    AS datum_in_dienst,
       '2021-12-31'::date    AS datum_uit_dienst,
       0                     AS contract_onderbreking
UNION ALL
SELECT 2                     AS werknemer_id,
       0.8::double precision AS parttime_factor,
       'ELEKTRO/ELEKTRO'     AS kostenplaats,
       'Piet'                AS manager,
       '2021-01-01'::date    AS datum_in_dienst,
        NULL::date           AS datum_uit_dienst,
        1                    AS contract_onderbreking
{% endcall %}

{% call dbt_unit_testing.mock_ref('int_visma__functie_rooster_historie', options = options) %}
SELECT 1                    AS werknemer_id,
       '2020-12-31'::date   AS datum,
        8::double precision AS geplande_uren,
       'Engineer'           AS functie
UNION ALL
SELECT 2                    AS werknemer_id,
       '2021-12-31'::date   AS datum,
        8::double precision AS geplande_uren,
       'Analyst'            AS functie
{% endcall %}

{% call dbt_unit_testing.mock_ref('stg_portal__kostenplaats_visma_mapping', options = options) %}
SELECT 'ELEKTRO/ELEKTRO' AS visma_kostenplaats,
       '2-10'            AS kostenplaats_id
{% endcall %}

{% call dbt_unit_testing.mock_ref('int_fourps__werknemers', options = options) %}
SELECT 'W1'                  AS werknemer_id,
       1                     AS parsed_werknemer_id,
       'John'                AS voornaam,
       'Doe'                 AS achternaam,
       'John Doe'            AS volledige_naam,
       'Engineer'            AS functie,
       'norm'                AS boekings_type,
       160::double precision AS norm_uren,
       '111'                 AS kostenplaats_id,
       '2020-01-01'::date    AS datum_indiensttreding,
       NULL::date            AS datum_uitdiensttreding,
       'vast'                AS vast_of_inlener
UNION ALL
SELECT '3'                   AS werknemer_id,
       3                     AS parsed_werknemer_id,
       'Mark'                AS voornaam,
       'Spencer'             AS achternaam,
       'Mark Spencer'        AS volledige_naam,
       'Analyst'             AS functie,
       'norm'                AS boekings_type,
       120::double precision AS norm_uren,
       '222'                 AS kostenplaats_id,
       '2022-01-01'::date    AS datum_indiensttreding,
       NULL::date            AS datum_uitdiensttreding,
       'inlener'             AS vast_of_inlener
{% endcall %}

{% call dbt_unit_testing.mock_ref('int_easyjob__werknemers', options = options) %}
SELECT '2020-01-01'::date AS datum_in_dienst,
       NULL::date         AS datum_uit_dienst,
       '99'               AS easyjob_adres_id,
       99                 AS easyjob_werknemer_id,
       99                 AS easyjob_tijdkaart_werknemer_id,
       99                 AS visma_werknemer_id,
       'Mark'             AS voornaam,
       'Spencer'          AS achternaam,
       '222'              AS kostenplaats_id,
       'Mark Spencer'     AS volledige_naam,
       1                  AS row
UNION ALL
SELECT '2020-01-01'::date AS datum_in_dienst,
        NULL::date         AS datum_uit_dienst,
        '56'               AS easyjob_adres_id,
       56                 AS easyjob_werknemer_id,
       56                 AS easyjob_tijdkaart_werknemer_id,
       NULL               AS visma_werknemer_id,
       'Piet'             AS voornaam,
       'Friet'            AS achternaam,
       '222'              AS kostenplaats_id,
       'Piet Friet'       AS volledige_naam,
       1                  AS row
{% endcall %}

{% call dbt_unit_testing.expect() %}
    SELECT
           1                  AS visma_werknemer_id,
           'W1'               AS fourps_werknemer_id,
           NULL               AS easyjob_adres_id,
           NULL::int          AS easyjob_werknemer_id,
           NULL::int          AS easyjob_tijdkaart_werknemer_id,
           '1990-01-01'::date AS geboortedatum,
           'Engineer'         AS functie_4ps,
           'Engineer'         AS functie_visma,
           'norm'             AS boekings_type,
           160                AS norm_uren,
           '2020-01-01'::date AS datum_in_dienst,
           '2021-12-31'::date AS datum_uit_dienst,
           'Piet'             AS manager,
           0.7                AS parttime_factor,
           'A1'               AS visma_kostenplaats,
           '111'              AS kostenplaats_werknemer_id,
           'vast'             AS vast_of_inlener,
           'Engineer'         AS functie,
           1                  AS werknemer_id,
           'John Doe'         AS volledige_naam,
           'John'             AS voornaam,
           'Doe'              AS achternaam,
           'Visma'            AS bronsysteem
    UNION ALL
    SELECT 2                  AS visma_werknemer_id,
           NULL               AS fourps_werknemer_id,
           NULL               AS easyjob_adres_id,
           NULL::int          AS easyjob_werknemer_id,
           NULL::int          AS easyjob_tijdkaart_werknemer_id,
           '1991-01-01'::date AS geboortedatum,
           NULL               AS functie_4ps,
           'Analyst'          AS functie_visma,
           NULL               AS boekings_type,
           NULL               AS norm_uren,
           '2021-01-01'::date AS datum_in_dienst,
           NULL::date         AS datum_uit_dienst,
           'Piet'             AS manager,
           0.8                AS parttime_factor,
           'ELEKTRO/ELEKTRO'  AS visma_kostenplaats,
           '2-10'             AS kostenplaats_werknemer_id,
           'inlener'          AS vast_of_inlener,
           'Analyst'          AS functie,
           2                  AS werknemer_id,
           'Jane Doe'         AS volledige_naam,
           'Jane'             AS voornaam,
           'Doe'              AS achternaam,
           'Visma'            AS bronsysteem
    UNION ALL
    -- Check if the full outer join exists for 4ps.
    SELECT NULL           AS visma_werknemer_id,
           '3'            AS fourps_werknemer_id,
           NULL           AS easyjob_adres_id,
           NULL::int      AS easyjob_werknemer_id,
           NULL::int      AS easyjob_tijdkaart_werknemer_id,
           NULL::date     AS geboortedatum,
           'Analyst'      AS functie_4ps,
           NULL           AS functie_visma,
           'norm'         AS boekings_type,
           120            AS norm_uren,
           NULL::date     AS datum_in_dienst,
           NULL::date     AS datum_uit_dienst,
           NULL           AS manager,
           NULL           AS parttime_factor,
           NULL           AS visma_kostenplaats,
           '222'          AS kostenplaats_werknemer_id,
           'inlener'      AS vast_of_inlener,
           'Analyst'      AS functie,
           3              AS werknemer_id,
           'Mark Spencer' AS volledige_naam,
           'Mark'         AS voornaam,
           'Spencer'      AS achternaam,
           '4PS'          AS bronsysteem
    UNION ALL
    -- Check if the full outer join exists for easyjob.
    SELECT
           NULL           AS visma_werknemer_id,
           NULL           AS fourps_werknemer_id,
           '56'           AS easyjob_adres_id,
           56             AS easyjob_werknemer_id,
           56             AS easyjob_tijdkaart_werknemer_id,
           NULL::date     AS geboortedatum,
           NULL           AS functie_4ps,
           NULL           AS functie_visma,
           NULL           AS boekings_type,
           NULL           AS norm_uren,
           NULL::date     AS datum_in_dienst,
           NULL::date     AS datum_uit_dienst,
           NULL           AS manager,
           NULL           AS parttime_factor,
           NULL           AS visma_kostenplaats,
           '222'          AS kostenplaats_werknemer_id,
           NULL           AS vast_of_inlener,
           'Geen functie' AS functie,
           56             AS werknemer_id,
           'Piet Friet'   AS volledige_naam,
           'Piet'         AS voornaam,
           'Friet'        AS achternaam,
           'EasyJob'      AS bronsysteem
    UNION ALL
    SELECT
        NULL           AS visma_werknemer_id,
        NULL           AS fourps_werknemer_id,
        '99'           AS easyjob_adres_id,
        99             AS easyjob_werknemer_id,
        99             AS easyjob_tijdkaart_werknemer_id,
        NULL::date     AS geboortedatum,
        NULL           AS functie_4ps,
        NULL           AS functie_visma,
        NULL           AS boekings_type,
        NULL           AS norm_uren,
        NULL::date     AS datum_in_dienst,
        NULL::date     AS datum_uit_dienst,
        NULL           AS manager,
        NULL           AS parttime_factor,
        NULL           AS visma_kostenplaats,
        '222'          AS kostenplaats_werknemer_id,
        NULL           AS vast_of_inlener,
        'Geen functie' AS functie,
        99             AS werknemer_id,
        'Mark Spencer' AS volledige_naam,
        'Mark'         AS voornaam,
        'Spencer'      AS achternaam,
        'EasyJob'      AS bronsysteem
{% endcall %}

{% endcall %}

[psousa50]

Hi @Jay-diehl,

could you please confirm that you added this to your project?

{% macro ref() %}
   {{ return(dbt_unit_testing.ref(*varargs, **kwargs)) }}
{% endmacro %}

{% macro source() %}
   {{ return(dbt_unit_testing.source(*varargs, **kwargs)) }}
{% endmacro %}

Thank you