Your assignment
Original fictional order desk. You know basic joins, aggregation and primary keys. A source sends changes; the output should have one current row per order. Write a contract with grain, identity, version ordering, required fields, status meanings, freshness owner and failure behavior. This is local self-practice, not a production migration.
A small source to inspect
Amounts are integer minor units in one fictional currency. Versions are authoritative positive integers per order. Deleted is a retained tombstone, not a missing source row. A correction can have the same old business day but arrive in a newer batch.
batch | order | version | business_day | amount | status
b1 | o1 | 1 | Jan10 | 10000 | completed
b1 | o2 | 1 | Jan11 | 8000 | completed
b2 | o1 | 2 | Jan10 | 9000 | completed
b2 | o2 | 2 | Jan11 | 0 | deleted
b2 | o4 | 1 | Jan05 | 6000 | completedAttempt before the diagnosis
State the grain of the change feed and current model. How do you distinguish an identical replay from a conflicting payload? Which records count in completed value? Why can a business-day watermark miss a correction? What evidence proves the source version is reliable?
Worked diagnosis
Feed identity is (order_id,version) with identical duplicates permitted by an explicit replay rule; two different payloads at the same identity are a contract failure. Target key is order_id, retaining the greatest received version. Completed count/value include only completed rows. Version2 for o1 replaces, rather than adds to, its previous value; the deletion tombstone for o2 must survive a stale replay. A configured unique key does not itself prove the feed is unique or resolve conflicting changes.
Your first quality checks
Check required keys, positive integer versions, nonnegative integer amounts, recognised statuses, valid dates and distinct payload count per key/version. Stop on conflicts instead of choosing whichever row happens to arrive last. In real systems add schema-version, source ownership, lineage and expected freshness contracts; freshness does not establish correctness.
SELECT order_id,version,COUNT(*) AS distinct_payloads
FROM (SELECT DISTINCT order_id,version,order_day,amount_minor,status
FROM source_changes)
GROUP BY order_id,version HAVING COUNT(*)>1;Reconciliation exercise
After b1 in the mini source, completed value is 18,000. Predict b2 by explaining each movement, then state final count/value. Do not use SUM(DISTINCT amount) as a key-based deduplication strategy; two unrelated orders may legitimately have equal amounts.
Reconciliation solution
Correction o1 removes 1,000; deletion o2 removes 8,000; late-arriving o4 adds 6,000. Final completed count is 2 and value 15,000. The current model retains o2 as a deleted row. Keep a movement ledger alongside row-level comparisons: a plausible aggregate can hide incorrect record attribution.
Review and explain
Score 0–2 each for grain, key/version semantics, status metric, failure policy and movement reconciliation. Explain one counterexample that would defeat a naive append-only model. No certificate or saved course progress is created.
Authoritative reference
dbt Labs, incremental models. Read the unique-key and filtering guidance. Our exercises use local SQLite; they do not claim a dbt deployment. Next: Cobalt 2.
Try the activity before reading its reference. Keep your sources, commands and assumptions with your work. Companion activities are unsaved self-practice and do not award a certificate or change lesson progress.