flat_relation.sql

--TODO: This query will change once we fix the names of the columns in the origin (init_id <-> final_id)
{{ config(alias='relation') }}
WITH cte_relation AS (
    SELECT
        vr.compound_id AS init_id,
        vr.relation_type_id,
        vr.final_id,
        r.evidence_accession,
        r.evidence_source_id
    FROM {{ ref('valid_relations') }} AS vr
    LEFT JOIN {{ ref('relation') }} AS r
        ON vr.compound_id = r.init_id AND vr.final_id = r.final_id AND vr.relation_type_id = r.relation_type_id
)

SELECT * FROM cte_relation;