Project: customer history with MERGE
Aster Supply customer region history
The reporting lead needs type 2 history for a customer move. Customer 101 moves to West at May 10, 2025; customer 102 is unchanged. Deliver preserved history and a current-customer view with one current row per key.
Aster Supply needs customer-region history for reports that use the region valid when an event happened. A move should close the old current record and open a new one. An unchanged incoming row must not create a false historical change.
Build a dimension with validity boundaries and a current flag, stage the genuinely changed keys, then close and open their records. The steps use a fixed effective timestamp so adjacent intervals meet without overlap. The final view exposes the current region while the history table retains the previous one. These separate writes demonstrate the state transitions in a finite training session. A production implementation must also make the sequence atomic or recoverable, because a failure after closing the old row must not leave the customer without a current record.
CREATE TABLE main.silver.customer_dim USING DELTA AS SELECT id,region,TIMESTAMP '2025-05-01' AS valid_from,CAST(NULL AS TIMESTAMP) AS valid_to,true AS is_current FROM main.bronze.customers;
The initial rows are current until an actual change closes them.