The problem I’m having is that i have build a simple incremental model that calculates the count of total number of rows for each day (this would be form the source table), the unique key being the date, this is set up to run daily. I am looking to add a test that alerts me if the percentage change in the number of rows from today vs yesterday increases by a certain threshold.
I am basically trying to monitor if, on a daily basis, the number of rows shoots up or falls by too much or not for the entire table
I have build out two separate SQLs for this, one that loads the numbers in an incremental fashion and another that calculates the percentage difference from the incremental table for each day. I was hoping there would be another way to just write one SQL as incremental that would give me percentage changes also
I am using the code below:
{{
config(
materialized='incremental',
unique_key='date_run',
full_refresh=true
)
}}
with source_1 as (
select
current_date() as date_run,
count(distinct current_id) as current_ids
from {{ source('fan_table_sandbox', 'international_fan_data') }}
group by date_run
),
aggregated_data as (
select
*
from source_1
{% if is_incremental() %}
where date_run > (select max(date_run) from {{ this }})
{% endif %}
),
lag_values as (
select
*,
lag(current_ids) over (order by date_run asc) as previous_day_count
from aggregated_data
)
select * from lag_values