# Running backfills in incremental models, “obsolete records” may persist

**URL:** <https://discourse.getdbt.com/t/running-backfills-in-incremental-models-obsolete-records-may-persist/7661>\
**Category:** Help\
**Tags:** incremental, snowflake\
**Created:** [April 4, 2023, 11:24am UTC](https://discourse.getdbt.com/t/running-backfills-in-incremental-models-obsolete-records-may-persist/7661 "2023-04-04T11:24:35Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![miandreu](https://avatars.discourse-cdn.com/v4/letter/m/f14d63/32.png) [@miandreu](https://discourse.getdbt.com/u/miandreu)\
**Post date:** [April 4, 2023, 11:24am UTC](https://discourse.getdbt.com/t/running-backfills-in-incremental-models-obsolete-records-may-persist/7661/1 "2023-04-04T11:24:36Z")

</div>

## The problem I’m having

We’ve seen in the “incremental models” where the date field’s degree of detail drops to lower levels like minutes or seconds, a data problem may occur where incorrect data persists in production on future reloads/backfills from the same period of time. To make it simpler to describe what I am observing, I will do so using the following example at the day level.  
We have the current model:

```auto
{{config(
  materialized='incremental',
  incremental_strategy='delete+insert',
  unique_key='date_day',
)}}

select date('2023-01-01') as date_day
UNION 
select date('2023-01-02') as date_day
UNION 
select date('2023-01-03') as date_day
UNION 
select date('2023-01-04') as date_day
UNION 
select date('2023-01-05') as date_day
UNION 
select date('2023-01-06') as date_day

```

I executed it the first time, and the table was created. If I execute it more times, what the model is doing is:

```auto
CREATE TEMP_TABLE AS CTE;

DELETE FROM FINAL_TABLE WHERE date_day in (SELECT date_day from TEMP_TABLE);

INSERT INTO FINAL_TABLE SELECT * FROM TEMP_TABLE;

```

Let’s imagine that we are doing a backfill of the data and this `data_day = 2023-01-03` we don’t have data. Then after executing the following code:

```auto
{{config(
  materialized='incremental',
  incremental_strategy='delete+insert',
  unique_key='date_day'
)}}

select date('2023-01-01') as date_day
UNION
select date('2023-01-02') as date_day
UNION
select date('2023-01-04') as date_day
UNION
select date('2023-01-05') as date_day
UNION
select date('2023-01-06') as date_day

```

We will find that `2023-01-03` persist in the final table.  
The problem comes from the `DELETE` command that is not built as a range of dates.

```auto
DELETE FROM FINAL_TABLE
WHERE date_day >= (SELECT min(date_date) FROM TEMP_TABLE)
  and date_day <= (SELECT max(date_date) FROM TEMP_TABLE)

```

## The context of why I’m trying to do this

We have some cases where date\_key is hours or minutes and this is a case that can happen with some data

## What I’ve already tried

I look for a solution of incremental materialization and delete\_strategy equal delete+insert, but I did not find anything.The only solution was to aggregate a pre\_hook, but it means that we need to know before hand the `min` an `max` date that is going to be load in the final table.

```auto
{{config(
  materialized='incremental',
  incremental_strategy='delete+insert',
  unique_key='date_day',
  pre_hook="
  {% if is_incremental() %}
    delete from {{ this }} where date_day between date('2023-01-02') and date('2023-01-06')
  {% endif %}"
)}}

```

The same happens with incremental\_strategy=‘merge’

## Some example code or error messages

NA

---

<div class="post-metadata">

**Author:** ![joellabes](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/joellabes/32/3980_2.png) [@joellabes](https://discourse.getdbt.com/u/joellabes)\
**Post date:** [April 5, 2023, 12:59am UTC](https://discourse.getdbt.com/t/running-backfills-in-incremental-models-obsolete-records-may-persist/7661/2 "2023-04-05T00:59:29Z")

</div>

Hi @miandreu, it is correct for dbt to not process records that do not exist in the query’s results.

The way incremental models work is that after the initial build, they only delete+insert/update records present in the later queries. (Check out [Incremental models in-depth | dbt Developer Hub](https://docs.getdbt.com/guides/best-practices/materializations/4-incremental-models) for a deeper dive)

Keep in mind that from dbt’s perspective, unique\_keys have no relationship to one another (eg they are often md5 hashes), which is why the delete statement is not a range. You control the behaviour of your unique keys in your model, so I would recommend explicitly casting your keys to a date or truncating them to the day level to ensure that no hour/minute data sneaks in.

If you need to re-process the table after some source data has been deleted, you could run `dbt run --select some_model --full-refresh` which will force the table to be dropped and rebuilt from scratch.