Known issues

Real operational issues found in this pipeline, past and present — what happened, and what to do about it.

1. stock_int full refresh resets created_at to today (fixed)

Which table: stock_int — running a full refresh on it used to reset every car's "date listed" (created_at) back to today, for the whole table at once, not just the car you were trying to fix. stock_stg is only related in the background: it's where a car's created_at first comes from, and it defaults to today if the dealer's own file doesn't supply one — that default is what made the bug possible, but stock_stg itself never lost any history.

The original problem: even before the full-refresh case, the same thing could happen on an ordinary, everyday update — whenever a dealer's feed re-uploaded a car that was already in the system, that car's "date listed" quietly reset to today, even if it had actually been listed months earlier. A car that had been on the lot for six months could suddenly look brand new, just because the dealer's feed ran again.

The fix: stock_int was changed to check, for each car, "have we already seen this one before?" If yes, it keeps the original listed date and only updates the "last updated" date. If it's genuinely new, it gets today's date as expected. That check initially only ran on normal updates — a follow-up fix, shipped the same day, made it also run correctly during a full refresh, which is what closed the table-wide reset case above.

Where the fix lives: src/stock/dbt/models/intermediate/stock/stock_int.sql, in the last_known_created_at CTE near the top of the file.

2. stock_int full refresh resets is_sold and deactivated_at (known, accepted)

When you rebuild stock_int from scratch (a full refresh), every car in the table gets marked "still for sale" — even cars that sold years ago.

Normally, the pipeline only looks at today's batch of cars a dealer sends in, and correctly marks anything missing as sold. A full refresh ignores that "today's batch" boundary and reprocesses the entire history of every car ever loaded — and the query has a blanket rule that says "mark as not sold," with no exception for old, already-sold cars.

The problem: a full refresh of stock_int resets every car's sold status back to active (is_sold = FALSE, deactivated_at = NULL) — for the entire table at once, regardless of whether the car actually sold.

How it happens

stock_int's own SELECT hardcodes is_sold = FALSE and deactivated_at = NULL for every row it produces.

On a normal incremental run this is safe. The run only touches the current batch:

snippet.sqlsql

WHERE s.batch_id = invocation_id

Presence in today's batch is itself the signal a car is active — forcing is_sold = FALSE there is correct.

A full refresh removes that filter entirely. It reprocesses every row stock_stg has ever held, not just today's batch.

stock_stg never deletes a row just because a vehicle disappeared from a feed — it only replaces rows matching the current file's IDs. So it accumulates years of listings, sold or not, and a full refresh sweeps up all of that history in one pass.

Example:

A car sells in March 2024. stock_int correctly marks it is_sold = TRUE.
The dealer's feed stops mentioning it — expected, it's sold.
A full refresh runs later (say, to backfill a new column).
That same car reprocesses through stock_int's SELECT, which hardcodes is_sold = FALSE for every row, full refresh or not.
The car is active again, with no dealer having relisted anything.

Effect and impact

This isn't theoretical — it happened during a real full refresh run to backfill a new column:

Not-sold row count jumped from roughly 84,000 to 119,472 — about 35,000 rows reactivated in one run.
Every one of those ~35,000 extra rows was a car that had genuinely sold at some point, some as far back as 2023.
Anything downstream reading stock_int's is_sold flag inherited the same false "active" signal — marts, exports, dealer-facing listings.
Recovery isn't immediate. The false "active" status only clears dealer by dealer, as each dealer's next feed reprocesses and normal batch-absence logic catches up. Depending on how often a dealer's feed runs, that's days to weeks.

Why it happens

created_at and is_sold/deactivated_at look like the same kind of problem, but they aren't symmetric:

created_at should never change once set. Preserving it across a full refresh is always correct.
is_sold genuinely needs to flip based on the current batch on a normal run — that's what lets a re-listed car correctly reactivate (see the is_sold feature for the Week 1/2/3 example).

A full refresh has no equivalent signal to work from. It isn't scoped to "cars in today's batch" — it's every row stock_stg has ever held, sold or not — with nothing in the query to distinguish "genuinely reactivated" from "simply reprocessed."

That's the harder problem created_at's fix didn't have to solve, and why this one remains open.