STOCK_RAW schema & file superseding

STOCK_RAW is an audit trail, not a working table — every file a dealer ever submits stays here permanently. Everything downstream reads a filtered, deduplicated view of it, never the raw table's full history.

Audit trail vs. latest state

This split runs through the whole pipeline:

If a dealer uploads the same vehicle twice in one day, STOCK_RAW ends up with 2 rows (both eventually complete); stock_stg/stock_int end up with exactly 1. Audit compliance needs the full trail; operations needs current state only — this is why the split exists, not an accident of incremental logic.

Two pre-hooks run before any row is processed

stock_stg runs two stored procedures as pre_hooks, in this order, before its own SELECT runs:

{{
config(
    pre_hook=[
        "CALL VALIDATE_DEALER_STATUS()",
        "CALL VEHICLE_STOCK_DB.VEHICLE_STOCK_SCHEMA.MARK_SUPERSEDED_FILES_AS_SKIPPED()"
    ]
)
}}

1. Dealer validation — sets dealer_status on every pending STOCK_RAW row (ACTIVE, INACTIVE, ACCOUNT_INACTIVE, NO_SUBSCRIPTION, or NOT_FOUND — see Dealer status validation) and flips non-active dealers' rows to processing_status = 'skipped'.

2. File superseding — among the rows that survived dealer validation, keeps only the latest file per dealer as pending and marks older files from the same dealer as skipped.

Only after both pre-hooks run does stock_stg's own query see the pending rows — by then, that's just the latest file, from active dealers, with everything else already excluded.

Why file superseding exists

A dealer's feed system can upload more than one file in quick succession — a partial retry, an accidental double-submit, a system quirk on their end. If two of those files are both still pending the next time the 15-minute dbt task runs, without superseding both would get fully processed as independent inventory, each one overwriting the other's rows in stock_stg on whichever ordering the dedup happened to land on. Superseding removes that race entirely: it decides which file wins before stock_stg ever sees either one.

How the procedure decides

MARK_SUPERSEDED_FILES_AS_SKIPPED() only ever looks at pending rows, and it groups them by data_provider_dealer_id alone — it has no notion of "vehicle" at this point, only "dealer" and "file":

The decision is per file, not per vehicle — if a dealer's older file is superseded, every record in it is marked skipped, including any vehicle that never appears in the newer file at all. There's no merge between an old file and a new one; the older file simply doesn't get processed.

One implementation detail worth knowing if you're reading the procedure's SQL directly: the temp table it builds counts COUNT(*) per dealer (a row count, not a distinct-file count), so a single file with several vehicle rows can look like "multiple files" by that count alone. It doesn't cause incorrect skips — the actual exclusion check is metadata_uuid NOT IN (the latest file's own metadata_uuid), and every row in a single file shares one metadata_uuid, so they're all protected regardless of the row count. Confusing to read, correct in practice.

Scenario: multiple files, same dealer

This is the case superseding exists for, verified against real Snowflake data by src/stock/dbt/tests/fixtures/test-mark-superseded.sh. Dealer A's feed uploads three files five minutes apart, all landing before the dbt task next runs:

Only uuid-3 — the file with the latest ingested_at — reaches stock_stg. The other two files' 8 records stay in STOCK_RAW permanently, visible for audit, but never processed.

Scenario: mixed, evaluated per dealer independently

Superseding runs per data_provider_dealer_id — one dealer having multiple pending files has no effect on any other dealer's files, even within the same task run. The same fixture test proves this with three dealers processed in one pass: E and F each upload twice, G uploads once:

Verified result: 8 records skipped (4 from each of E and F's older file), 11 complete (4 + 4 + 3), 19 total — Dealer G's single file is untouched precisely because it never has a competing pending file to lose against. This mixed case is also what a normal day looks like in miniature: most dealers behave like G, and superseding only ever activates for the ones that happen to double-upload inside one task window.

This is a different "superseding" from the sold-detection one. A separate fixture scenario (scenario_5_superseding_with_sold in test-mark-dealer-stock-as-sold.sh) also uses the word "superseding," but each of its files runs through its own complete dbt cycle before the next one lands — none of them are ever pending at the same time, so MARK_SUPERSEDED_FILES_AS_SKIPPED never fires. What that test actually exercises is a vehicle disappearing from one completed batch to the next, which the sold-detection logic on Stock operational guidelines handles — a different mechanism from the file-level skip described on this page, despite the shared name.

Feature flag: bypassing the active-dealer check

bypass_active_dealer_check (default false, passed via --vars) exists for a specific, narrow case: importing historical or legacy stock data for dealers that aren't currently active in Salesforce.

# Import legacy stock, bypassing the active-dealer check
dbt run --select stock_stg stock_colours_ref stock_int \
  --vars '{"bypass_active_dealer_check": true, "skip_colour_api": true}'

This doesn't change what VALIDATE_DEALER_STATUS() records — a dealer's real status is still computed and stored — it only changes whether stock_stg filters on it. Don't use this for a normal run; it's specifically for backfills.

See also

• Dealer status validation
• Rejection & validation tracking