Hi @samuelperezh ,

Pattern for Grace Period

The auto_cdc_from_snapshot SCD2 should be able to naturally take care of your scenario. Let's walk through an example:

1. Initial load with 2 products on 25/02/2026:

The target of the auto_cdc_from_snapshot SCD2 will be:

2. Assume that bar and baz disappear on the load of 26/02/2026:

The target of auto_cdc_from_snapshot will be updated to:

3. The bar reappears the next day, on 27/02/2026:

The target CDC table now reflects the reappearance:

The wrongly deleted data will appear as a duplicate which needs to be handled in the downstream:

• If the new __START_AT is earlier than previous __END_AT + 3 days, we keep the record. Otherwise, it will be ignored.
• For each identical row, we retrieve only the newest record.

CREATE MATERIALIZED VIEW target_auto_cdc_clean AS
WITH filtered AS (
  SELECT *,
    LEAD(__END_AT) OVER (PARTITION BY name, age ORDER BY __END_AT DESC NULLS FIRST) AS prev_end_at 
  FROM names_cdc
),
valid AS (
  SELECT *
  FROM filtered
  WHERE prev_end_at IS NULL 
     OR (
          prev_end_at IS NOT NULL 
          AND __START_AT <= prev_end_at + INTERVAL 3 DAYS 
        )
),
ranked AS (
  SELECT *, row_number() OVER (PARTITION BY name, age ORDER BY __START_AT DESC) AS rn 
  FROM valid
)
SELECT * FROM ranked WHERE rn = 1

This will return:

Then, to apply the grace logic:

SELECT *, 
    CASE 
        WHEN __END_AT > current_date() + INTERVAL 3 DAYS THEN TRUE
        ELSE FALSE 
    END as is_sold,
    CASE
        WHEN __END_AT > current_date() + INTERVAL 3 DAYS THEN __END_AT
        ELSE NULL END as sold_date
FROM target_auto_cdc_clean