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 %}
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?