Incremental model partitioned with daily granularity but rebuild it weekly

Help
,
1

I would like to create an incremental model partitioned with daily granularity but rebuild it weekly.
This way, in each rebuild, I would like to calculate and append all days from the last build until now (1 week)

It would be something like this example:
date
03-04-2023 (appended in build 03-04-2023)
04-04-2023 (appended in build 03-04-2023)
05-04-2023 (appended in build 03-04-2023)
06-04-2023 (appended in build 03-04-2023)
07-04-2023 (appended in build 03-04-2023)
08-04-2023 (appended in build 03-04-2023)
09-04-2023 (appended in build 03-04-2023)
10-04-2023 (appended in build 10-04-2023)
11-04-2023 (appended in build 10-04-2023)
12-04-2023 (appended in build 10-04-2023)
13-04-2023 (appended in build 10-04-2023)
14-04-2023 (appended in build 10-04-2023)
15-04-2023 (appended in build 10-04-2023)
16-04-2023 (appended in build 10-04-2023)

How would a config like this look like?
Thanks!

2

When you build incremental models (and any models!), you should build them in an idempotent way, which doesn’t care how many times a model is run. From there, you can use tags or other selection syntax to run the model on your selected cadence.

The way you do this is by using the is_incremental() macro in your model to add a subset of your query that identifies new records since your last run. It would look something like this:

select 
  col_a,
  col_b,   
  col_c,
  event_timestamp
from {{ ref('upstream_model') }}

{% if is_incremental() %}
  where event_timestamp >= (select max(event_timestamp) from {{ this }})
{% endif %}

When your model runs, it will find all records in upstream_model whose event_timestamp is newer than the most recent data in the incremental model, and return them for insertion.

If you run it twice in a row, the second run won’t pick up any new records; if you only run it once a fortnight, all records from the past two weeks will be picked up.

You might also want to read Incremental models in-depth | dbt Developer Hub for more info on how incremental models work

3

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.