37 lines
870 B
MySQL
37 lines
870 B
MySQL
|
-- Macro: Generate a CTE that unions current and predecessor data for a given source
|
||
|
|
|
||
|
|
{% macro chain_lineage(source_ref, urn_col='urn', year_col='year') %}
|
||
|
|
|
||
|
|
with current_data as (
|
||
|
|
select
|
||
|
|
{{ urn_col }} as current_urn,
|
||
|
|
{{ urn_col }} as source_urn,
|
||
|
|
*
|
||
|
|
from {{ source_ref }}
|
||
|
|
),
|
||
|
|
|
||
|
|
predecessor_data as (
|
||
|
|
select
|
||
|
|
lin.current_urn,
|
||
|
|
src.{{ urn_col }} as source_urn,
|
||
|
|
src.*
|
||
|
|
from {{ source_ref }} src
|
||
|
|
inner join {{ ref('int_school_lineage') }} lin
|
||
|
|
on src.{{ urn_col }} = lin.predecessor_urn
|
||
|
|
where not exists (
|
||
|
|
select 1 from {{ source_ref }} curr
|
||
|
|
where curr.{{ urn_col }} = lin.current_urn
|
||
|
|
and curr.{{ year_col }} = src.{{ year_col }}
|
||
|
|
)
|
||
|
|
),
|
||
|
|
|
||
|
|
combined as (
|
||
|
|
select * from current_data
|
||
|
|
union all
|
||
|
|
select * from predecessor_data
|
||
|
|
)
|
||
|
|
|
||
|
|
select * from combined
|
||
|
|
|
||
|
|
{% endmacro %}
|