Incremental updates and history

Match the update method to the row meaning

Bruin's update methods have different effects: create+replace rebuilds everything, append duplicates rows on a rerun, delete+insert replaces rows for an incremental key, truncate+insert keeps the table but rewrites all rows, and merge needs a truly unique primary key. time_interval limits a replacement to a time range. SCD2, short for slowly changing dimension type 2, keeps old versions of a customer row and needs a full-refresh first run.

For this data, ordered_at tells when the order happened and _loaded_at tells when the system received the row. Use _loaded_at to find new information, then replace the date groups identified by ordered_at. Mixing them up can miss late orders or rewrite the wrong dates.

Your task

Change weekly_category_revenue from create+replace to delete+insert keyed by iso_week, add time_granularity: date, and filter the query to the requested {{ start_datetime }}/{{ end_datetime }} window. Run one limited date range twice and record the row count and revenue total after each run. The complete 2023-2025 result is 871 rows and 733,684.59 in the currencies supplied by the source data. Compare the design with time_interval in docs/incremental-decision.md, include the exact iso_week metadata and date filter, and choose the strategy you would ship.

Then replace the empty query in pipeline/assets/core/dim_customer_history.sql with an SCD2 query over customer_snapshots, use scd2_by_time with incremental_key: snapshot_at, run that asset once with bruin run --full-refresh pipeline/assets/core/dim_customer_history.sql, and record _valid_from, _valid_until, _is_current, and the row count in docs/scd2-decision.md. Use customer_snapshots to identify five customers with overlapping validity windows and five with gaps before changing the stub. Do not full-refresh either reporting table.

Check your understanding

• What is the central two-timestamp rule?
• What does append do when the same range runs twice?
• What does an SCD2 table preserve?

Do it with your agent

Say next lesson, make the incremental and SCD2 changes, record the evidence, then say review my work.

Rubric

• Explains that ordered_at says when an order happened and _loaded_at says when the system received it.
• The same range run twice has identical row count and revenue total on both runs.
• Compares delete+insert with time_interval, names the chosen strategy, and includes incremental_key: iso_week, time_granularity: date, and a limited date filter.
• Uses scd2_by_time with incremental_key: snapshot_at, preserves customer history, and records why its first run needs --full-refresh.
• Identifies five overlapping and five gapped customer validity sequences and states the chosen SCD2 window invariant.