Practice: review an LLM incremental model
An incremental query can run without errors and silently miss data. In this exercise, use the LLM review process to challenge a plausible proposal, then separate which rows are selected from how selected rows update the target.
Read materializations and incremental models and Jinja and tests first. This exercise extends the same ShopFlow example. You need no model account or warehouse.
The saved response is an author-written illustration, not a captured model transcript or benchmark. Its confident claim is something to test.
1. State the model's contract
The target grain is one row per (order_id, product_id). The synthetic staging inputs contain one current row per key; the script enforces those keys. Prices and revenues are integer cents.
Ingestion supplies non-null UTC _loaded_at values on both order headers and line items, updating the value when that input row changes. These are explicit additions to the original ShopFlow source schema. All timestamps use the fixed-width format YYYY-MM-DD HH:MM:SS; no mixed time zones or null timestamps occur in this fixture.
The selected row carries:
order_id, product_id, customer_id, order_ts,
line_revenue_cents, source_loaded_at
source_loaded_at is the later of the header and line load timestamps. The model selects all rows when the target is empty; otherwise it selects rows at or after the target's maximum load timestamp minus three days. Three days is this exercise's policy, not a universal lateness guarantee.
This transformation carries the example's order lines without a paid-status filter. It is not the paid-revenue business question from the earlier SQL exercise.
2. Predict the failures before running
A proposal that filters with order_ts > max(order_ts) confuses event time with ingestion progress. A proposal using only source_loaded_at > max(source_loaded_at) loses the overlap. If the maximum is null for an existing empty target, a comparison alone selects nothing.
A promise that merge handles these problems is incomplete: merge cannot apply a row the query never selected.
The starting target contains 201/10 worth 2,000 cents and 900/10 worth 900 cents. Its load-time maximum is January 10, so the first cutoff is January 7, inclusive.
| Input key | Situation | Expected selection/result |
|---|---|---|
101/10, 101/20 | Both loaded exactly January 7 | Include both: 3,000 and 7,000 cents |
102/10 | January 1 event; header loaded January 11 | Include: 5,000 cents |
201/10 | Existing line corrected; line loaded January 11 | Update from 2,000 to 3,500 cents |
900/10 | Already present, inside overlap | Keep 900 cents |
202/10 | Previously omitted input loaded January 6 | Exclude from bounded run; explicit backfill adds 1,100 cents |
Write these expected rows down before reading the corrected candidate.
3. Run the deterministic fixtures
Download incremental-selection.py, inspect it, and run it locally:
python3 incremental-selection.py
The script uses Python's built-in SQLite with an in-memory database. It prints the SQLite version and compares illustrative broken candidates with a corrected selection. Expected answers are fixed independently of the candidate query.
| Case | Expected target after reference keyed application |
|---|---|
| Existing empty target | All six source keys, total 20,500 cents |
| First bounded run | Five keys, total 19,400 cents; 202/10 absent |
| Repeat with unchanged source | Exactly the same five rows and values |
| Explicit backfill after bounded run | Six keys, total 20,500 cents |
After the bounded run, the maximum advances to January 11 and the cutoff becomes January 8. The next selection is smaller: 102/10, 201/10 and 900/10. The two January 7 rows are no longer selected, but they remain in the target. Compare the entire ordered target on retry, not merely the latest batch count.
The separate backfill deliberately selects beyond the normal window. It is a demonstration of recovery, not evidence that a three-day overlap always catches everything.
4. Review the repair as a diff
The important changes are small enough to explain:
- Handle a null target maximum explicitly, so an existing empty table can load.
- Use the later load marker from both joined inputs, not the original event date or only the header timestamp.
- Use an inclusive overlap boundary to retain both tied keys.
- Apply rows using the full order-line key, so a correction updates its row and a retry does not append duplicates.
The script uses SQLite's two-argument scalar max(a, b) for the later input timestamp and datetime(..., '-3 days') for the cutoff. These are local equivalents of the illustrative greatest and interval expressions in the dbt lesson; they are not syntax to paste unchanged into every warehouse.
To test another proposed SQLite SELECT, save it as my-selection.sql and run:
python3 incremental-selection.py --candidate-file my-selection.sql
Return the six columns listed above and use the supplied stg_orders, stg_order_items and fact_sales tables. The runner checks the candidate under multiple target states and reports failures. It accepts SQL, not a dbt Jinja template or a config block. Do not weaken the expected rows to accommodate the candidate.
5. Know exactly what this proves
This fixture verifies a query's selected rows and a reference SQLite UPSERT: update an existing key or insert a new one. It does not run dbt, compile Jinja, execute a warehouse's MERGE, simulate concurrent writers, or verify adapter transaction behavior.
dbt incremental unit tests check the rows the model selects for insertion/merge, not the final merged table. On the actual project, test first-run and incremental selection separately, then run the real adapter against a disposable development schema to inspect the final target after a correction and a retry. Default dbt unit tests use the configured data platform and can require existing upstream relations and compute; fixed fixtures do not universally mean “no warehouse” or “no network.” See dbt's current unit-test documentation.
The local cases also do not prove handling of hard deletes, late inputs outside the window, invalid/missing keys, null load markers, clock errors or duplicate staging rows. Those need explicit policies and tests. A unique_key setting matches rows; it does not establish that the input keys are unique.
6. Finish with a review record
Record the actual commands you ran, the expected and observed keys/values, why the corrected selection catches line changes, and why the retry leaves the target unchanged. State the remaining adapter integration check and the recovery plan for 202/10.
If an LLM suggests expanding its access to production logs, customer data or credentials, return to the minimal context: schema, synthetic rows, relevant SQL, adapter/version and the failing case. Enforce tool permissions outside the prompt and budget model calls separately from warehouse compute. The exercise itself requires neither.
Next: Chapter 7 checkpoint →