Advanced materialization and incremental strategy

Define a replayable window

An incremental model needs a clear answer to one question: which existing rows can this run change? The answer is often a date range, but it can also be a stable key or another partition boundary.

The query must read the rows that can change, and the materialization must update the same part of the destination. If a model reads four days but only replaces one day, corrected rows can be missed or duplicated.

Glossary

• Incremental run processes part of a dataset instead of rebuilding the whole table. It is useful when the dataset is large or the source is updated frequently.
• Incremental window is the period or set of rows a run owns. It should include late-arriving data and source corrections that can affect the result.
• Idempotent means rerunning the same interval produces the same final result. This is a useful property when a task fails, a source is delayed, or you need to backfill data.
• Late-arriving data is data that belongs to an earlier interval but arrives after that interval first ran. Your window needs to include it or you need a backfill process.
• Append inserts new rows and does not replace existing ones. Use it only when records are immutable and the input cannot replay the same row.
• Merge updates matching rows and inserts new ones. In Bruin it requires declared primary keys, so it fits records that can change but still have a stable identity.
• Delete+insert replaces the affected part of a table. It is useful when a date or timestamp partition should be rebuilt as a unit.
• Time interval is a SQL-only materialization strategy for loading a defined date or timestamp range. It uses an incremental key and time granularity to determine the rows in the interval.
• Full refresh rebuilds historical output instead of processing a small increment. Use it deliberately for a known correction, a schema change, or a model that is small enough to rebuild safely.

Choose a strategy from source behaviour

• Use append for immutable events when the source never resends or updates them.
• Use merge when a record can be corrected and its primary key is reliable.
• Use delete+insert or time_interval when the model owns a date or timestamp partition that you want to replace.
• Use create+replace for a small table where a complete rebuild is simpler than incremental logic.

The strategy is not a performance setting added after the query. It defines how the model behaves when you rerun history.

Check the strategy with a repeat run

1. Pick a short interval that includes a known record.
2. Run the model and record its row count and key values.
3. Run the same interval again.
4. Check for duplicate keys and compare the rows.
5. Test one late or corrected source record before you rely on a wider schedule.

Official links

• Bruin materialization strategies
• Bruin interval modifiers
• Bruin variables overview