source-to-target tests [i.e. make sure all ids in source model are found in target model]

[shayms8]

I want to define a source-to-target test between 2 models to make sure all unique_ids found in source are also found in target.

a test/macro for example:

{% test source_to_target(source_model, target_model, unique_column_name) %}
    WITH source_model AS
    (
        SELECT {{ unique_column_name }} as unique_id
        FROM {{ self_model }}
    ),
    target_model AS
    (
        SELECT {{ unique_column_name }} as unique_id
        FROM {{ target_model }}
    )

    -- exists in source and not in target
    SELECT s.unique_id AS s_unique_id, t.unique_id as t_unique_id
    FROM source_model s LEFT JOIN target_model t ON s.unique_id = t.unique_id
    WHERE t.unique_id IS NULL
{% endtest %}

for example, i have the source model: users_raw and the target model users.
I want to run: source_to_target(source_model='users_raw', target_model='users', unique_column_name='user_id') when the users model finish running to to make sure users_raw and users has the exact same user_ids.

Is this possible?

[a_slack_user]

It sounds like you’re describing the built-in relationships test: https://docs.getdbt.com/docs/build/tests#generic-tests

_{Note: @Owen originally posted this reply in Slack. It might not have transferred perfectly.}