E2E tests in dbt: can unit test framework do it?

[IliaOzhmegov]

With a new updated version of dbt-core, it became possible to test the logic for transformation steps. However, it does not allow to do E2E tests in dbt, or does it?

I have a pretty standart case:

a few sources, some intermediate tables and some fact (red table), so I want to mock data for sources or at least staging, and then just make sure that the very end is correct (red table). Is there a way to do it in dbt? Is it possible to to with unit test framework?

[b-per]

The unit testing framework is about unit testing individual models, not pipeline. So, what you describe is not possible out of the box.

What you could do is:

• add unit tests for all models (incl int_1-1 etc...)
• or use your own testing mechanism to achieve what you want to test
  • it is possible to achieve what you want to do without the unit testing approach, but leveraging logic based on var() / env_var(). Based on those you could point to different sources, and you could also have a final test that compares the fct_12 model generated with a seed that you would have created before

[b-per]

What I'd do in the models with var() env_var() would be something like

In stg_1 for example

select
...
from {{ ref('my_end_to_end_input_seed') if var('dbt_end_to_end', false) else source('mysource','mytable') }}

And I would add a seed with a test to check that it is the same as fct_12, like following

seeds:
  - name: my_end_to_end_input_seed
    tests:
      - dbt_utils.equality:
          name: equality_fct_12_end_to_end
          compare_model: ref('fct_12')

And finally, the pipeline could be tested with

dbt build --select my_end_to_end_input_seed+ --vars '{"dbt_end_to_end": true}'

(I have not tested this whole code so their might be a couple of things to tweak, I share it to explain the approach)

[IliaOzhmegov]

Let me maybe share some results of implementation. I am sure somebody also will find it useful.

1. Added the var into dbt_project.yml:
   snippet:

vars:
  'dbt_date:time_zone': 'Europe/Berlin'
  'dbt_test_e2e': false

2. change the sources for stg to something like this:

{{ config(
    schema='mart'
) }}

WITH source AS (
  {% if var('dbt_test_e2e', false) %}
    -- Use seed data for E2E tests
    SELECT
      PAYLOAD_TYPE, 
      PARSE_JSON(MESSAGE::VARCHAR) AS MESSAGE
    FROM {{ ref('input_order_events') }}
  {% else %}
    -- Use the actual source data in production
    SELECT * FROM {{ source('rds_kafka', 'order_messages')}}
  {% endif %}
)

SELECT 
  trxid AS ID,
  ts     AS EVENT_CREATED,

  VALUE_1 AS AMOUNT,
  -- some other stuff
  KAFKA_M AS PAYLOAD
FROM source

3. then I have created a set of some cases with some output as json or csv:

└── tests
    ├── business_cases
    │   ├── 1.delivery_payment
    │   │   ├── input_stg_1.json
    │   │   ├── input_stg_2.csv
    │   │   └── output_fct_12.csv
    │   └── 2.delivery_return
    │   │   ├── input_stg_1.json
    │   │   ├── input_stg_2.csv
    │   │   └── output_fct_12.csv
    └── fixtures

4. Define seeds/schema.yml:

seeds:
  - name: output_fct_12
    data_tests: # dbt considers it as data_test, BUT it's an E2E end
      - dbt_utils.equality:
          name: e2e_test_fct_12
          compare_model: ref('fct_12')
          tags: ['e2e']

5. the I have to create a script that takes the data in tests/business_cases and assembles single CSV for corresponding input and output:

seeds
├── input_stg_1.csv
├── input_stg_1.csv
├── output_fct_12.csv
└── schema.yml

6. And only after running these commands I can do that E2E testing:

python3 scripts/assemble_data_for_e2e_tests.py
dbt build --vars '{dbt_test_e2e: true}'  --target ci

7. Also, I had to change the way I do tests on real data because otherwise it runs that data_test defined in seeds/schema.yml:

dbt build --exclude tag:e2e  --target ci

What is not cool is that, it's really

1. Hard to keep the grip over test cases for E2E tests that's why I had to keep them a bit separated
2. Running the python script before running the actual dbt tests
3. Adding tags to exclude the triggering the tests in seeds/schema.yml
4. Those "E2E" tests interfere, they are not isolated

[b-per]

Good that you got it working!

What about doing the following to have the test automatically deactivated when not running e2e?
In that case you won't need --exclude tag:e2e

seeds:
  - name: output_fct_12
    data_tests: # dbt considers it as data_test, BUT it's an E2E end
      - dbt_utils.equality:
          name: e2e_test_fct_12
          compare_model: ref('fct_12')
          tags: ['e2e']
          enabled: "{{ var('dbt_test_e2e', false) }}"

[IliaOzhmegov]

That's a good one! I didn't know that we can add conditions in the test!

[IliaOzhmegov]

I think my biggest question right now: is there a way to keep those business cases isolated?