int_zendesk__field_history_scd.sql

-- model needs to materialize as a table to avoid erroneous null values
{{ config( materialized='table') }} 

{%- set ticket_columns = adapter.get_columns_in_relation(ref('int_zendesk__field_history_pivot')) -%}

with change_data as (

    select *
    from {{ ref('int_zendesk__field_history_pivot') }}

), set_values as (

-- each row of the pivoted table includes field values if that field was updated on that day
-- we need to backfill to persist values that have been previously updated and are still valid 
    select 
        date_day as valid_from,
        ticket_id,
        ticket_day_id

        {% for col in ticket_columns if col.name|lower not in ['date_day','ending_day','ticket_id','ticket_day_id'] %} 

        ,{{ col.name }}
        ,sum(case when {{ col.name }} is null 
                then 0 
                else 1 
                    end) over (order by ticket_id, date_day rows unbounded preceding) as {{ col.name }}_field_partition
        {% endfor %}

    from change_data

), fill_values as (
    select
        valid_from, 
        ticket_id,
        ticket_day_id

        {% for col in ticket_columns if col.name|lower not in ['date_day','ending_day','ticket_id','ticket_day_id'] %} 

        ,first_value( {{ col.name }} ) over (partition by {{ col.name }}_field_partition, ticket_id order by valid_from asc rows between unbounded preceding and current row) as {{ col.name }}
        
        {% endfor %}
    from set_values
) 

select *
from fill_values