Make repeat runs safe

Choose based on how the source changes

An incremental model updates part of a table instead of rebuilding all of its history. Choose the update method based on how the source data changes:

• Append adds newly arrived rows. Use it when earlier rows never change and the source does not send the same event twice.
• Merge updates existing rows and adds new rows. Use it when a primary key lets the pipeline match a source row to a destination row. It does not remove destination rows that disappear from the source.
• Replacing a time interval rebuilds one complete date range. Use it when you can safely rerun all data for selected dates.
• A full rebuild is often simplest for small tables.

This lesson practices the time-interval approach. The daily revenue table has complete calendar dates, so it can replace the requested date range. Running the same dates again produces the same final rows instead of adding duplicates.

Change how the table updates

The settings below tell Bruin to replace the selected dates using order_date. The query then returns exactly those dates.

In commerce/assets/analytics/customer_daily_revenue.sql, replace the materialization block with:

materialization:
  type: table
  strategy: time_interval
  incremental_key: order_date
  time_granularity: date

Then add this filter after the join and before GROUP BY:

WHERE f.order_date BETWEEN CAST('{{ start_date }}' AS DATE)
  AND CAST('{{ end_date }}' AS DATE)

Both dates are included. A run with January 2 as both the start and end date processes January 2. Bruin deletes rows with an inclusive BETWEEN start_date AND end_date, so the model must select those same dates. If the query excludes the end date, Bruin can delete that date without putting it back.

Create the table once

time_interval deletes the requested dates from the destination table before inserting the new result. A new local database does not have that table yet, so its first time-interval run will fail.

Create the table with one full refresh:

bruin run commerce/assets/analytics/customer_daily_revenue.sql \
  --start-date 2026-01-01 \
  --end-date 2026-01-03 \
  --full-refresh

Run this full refresh once for a new database. If an earlier lesson already created the table with create+replace, the command simply rebuilds it with the final settings. Normal runs after this should not use --full-refresh.

The commands in this lesson run only the reporting asset. If source data has changed, run bruin run commerce/pipeline.yml first so the raw, staging, and fact models are current.

Ask your coding agent

Inspect the rendered SQL

Render one day without changing data:

bruin render commerce/assets/analytics/customer_daily_revenue.sql \
  --start-date 2026-01-02 \
  --end-date 2026-01-02

Find the WHERE clause in the output. It should contain the two requested dates rather than the Jinja variables.

Run the same interval twice

bruin run commerce/assets/analytics/customer_daily_revenue.sql \
  --start-date 2026-01-02 \
  --end-date 2026-01-02

bruin run commerce/assets/analytics/customer_daily_revenue.sql \
  --start-date 2026-01-02 \
  --end-date 2026-01-02

bruin query --connection duckdb-default \
  --description "check customer daily revenue rows after repeated interval runs" \
  --query "SELECT order_date, COUNT(*) AS rows FROM analytics.customer_daily_revenue GROUP BY order_date ORDER BY order_date;"

January 2 should still contain two rows after the second run. The update rule removes those dates before inserting the new result.

Know when to use a full refresh

A full refresh creates the destination table when it does not exist. After that first run, use it only for a deliberate historical correction, a column or type change that cannot be applied to selected dates, or a small table where rebuilding everything is simpler than rerunning selected dates.

Before a production full refresh, estimate the data volume, the reports and tables affected, the run time, and how you would recover. The command is simple; the production decision is not.

Checkpoint

You should be able to explain why this model uses a time interval instead of append, why a new table needs one full refresh, and why both date boundaries are included. You should also have evidence that a repeated January 2 run does not create duplicates.