# Illustrative incremental response

This is a hand-authored teaching example, **not an actual model transcript** or
model benchmark. No model, dbt project, paid service or warehouse was run.

## Plausible suggestion — filter on order event time

> Use an upsert with a three-day lookback on order time; merge handles late data.

```sql
WITH source_rows AS (
    SELECT oi.order_id, oi.product_id, o.customer_id, o.order_ts,
           oi.quantity * oi.unit_price_cents AS line_revenue_cents,
           max(o._loaded_at, oi._loaded_at) AS source_loaded_at
    FROM stg_order_items oi
    JOIN stg_orders o ON o.order_id = oi.order_id
)
SELECT order_id, product_id, customer_id, order_ts,
       line_revenue_cents, source_loaded_at
FROM source_rows
WHERE (SELECT max(order_ts) FROM fact_sales) IS NULL
   OR order_ts >= (SELECT datetime(max(order_ts), '-3 days') FROM fact_sales);
```

This candidate passes an empty target but fails the later run. With a January 10
maximum event time, its January 7 cutoff excludes old January 1 orders even when
the header or line arrived or changed January 11. Its target stays at two rows,
2900 cents; the bounded load-time contract expects five rows, 19400 cents.

Changing the predicate to strictly newer load times is also insufficient:
`source_loaded_at > max(source_loaded_at)` misses the two January 7 cutoff ties.
Only reading the header's load time misses the corrected line 201/10.

The corrected selection uses the later load marker from **both** inputs, an empty
maximum branch, and an inclusive three-day overlap. This is a bounded policy:
202/10 loaded January 6 remains excluded until the separate explicit backfill.
An upsert cannot recover a row the SELECT never emits.

Save the SQL block alone in `candidate.sql`, then run:

```sh
python3 incremental-selection.py --candidate-file candidate.sql
```

The candidate exits 1 because later cases fail. The fixed harness applies a
SQLite UPSERT by `(order_id, product_id)` to evaluate the resulting state. It
**does not validate a dbt adapter, its generated MERGE, warehouse behavior,
concurrent writers or production performance**. Hard deletes need another path.