Stock operational guidelines

Day-to-day operational knowledge for the Stock pipeline: how a vehicle gets marked sold without a dealer ever saying so, what a full refresh actually resets, and how to fully re-ingest stock from Manifold when you need to start over. For specific bugs and known trade-offs found in this pipeline, see Known issues.

The is_sold feature: sold detection without a sold notification

Dealers don't tell this pipeline when a vehicle sells — they just stop including it in their next inventory file. stock_int treats that absence as the signal:

Stock is marked sold (is_sold = TRUE, deactivated_at = CURRENT_TIMESTAMP()) when it:

1. Already exists in stock_int.
2. Is missing from the dealer's current batch in stock_stg.
3. Belongs to a dealer who has actually submitted a new batch (a dealer who hasn't submitted anything recently doesn't have their entire inventory silently marked sold by default).

This runs as a single bulk UPDATE, executed as a stock_int pre-hook, covering every dealer with a pending batch in one pass rather than per-dealer — for performance, and so cross-dealer isolation is guaranteed by construction rather than by careful looping.

Reactivation is symmetric: if a vehicle marked sold reappears in a later batch, is_sold resets to FALSE. Real-world scenario this handles: a deal falls through, or a trade-in comes back onto the lot — Redbook's own worked example goes Week 1 (A, B, C all active) → Week 2 (only A submitted; B and C marked sold) → Week 3 (A and B submitted; B reactivated, C stays sold).

A complete inventory swap works the same way at scale — if a dealer replaces their entire feed with different vehicles (new shipment, system migration), every vehicle absent from the new batch gets marked sold in the same pass, no special-casing needed.

A second, independent trigger: the dealer itself goes inactive

Batch-absence isn't the only way stock gets marked sold. A separate mechanism — processing_call_mark_inactive_dealer_stock_as_sold(), also run as a stock_int pre-hook — checks each dealer's status directly against DEALER_ACTIVE_STATUS_VIEW (see Dealer status validation) and marks a dealer's entire existing stock as sold if that dealer is no longer ACTIVE in Salesforce, or isn't in Salesforce at all.

This covers a case batch-absence alone can't: a dealer who simply stops submitting files. If a dealer's subscription lapses and they never send another batch, there's no "missing from the new batch" event to trigger on — this second mechanism is what actually deactivates their stock. A standalone stored procedure version of the same logic (MARK_INACTIVE_DEALER_STOCK_AS_SOLD()) also exists, deployable independently of a stock_int build.

The CDC event log runs the same logic again, separately

stock_change_history doesn't read stock_int's is_sold column — it computes its own version of the same batch-absence rule directly against STOCK_RAW, to produce a is_sold = TRUE change event in the event log. This is a second, parallel implementation of the same idea (stock missing from a dealer's latest batch = sold), not a read of the first one's result — see Marts (fact & dimension) for how this feeds stock_lifecycle_fact_mart.

Full refresh: what actually resets, and what doesn't

A full refresh of stock_int runs CREATE OR REPLACE TABLE ... AS (...) directly — there's no temporary table. A model referencing its own table via {{ this }} inside that statement sees the table's contents from before the statement ran. This is what lets some columns survive a full refresh while others don't:

created_at is preserved across a full refresh, provided stock_int already exists (true in both staging and prod — the table has existed for years). It's only ever set to today's date the very first time stock_int is built. The model checks whether it's already seen a row via {{ this }}, guarded on whether the table exists, not on whether the run is incremental:

-- Correct: only treats "never seen before" as true on the table's first-ever build
{% if this and load_relation(this) is not none %}
  ...query {{ this }}...
{% else %}
  ...treat as if nothing has ever been seen before...
{% endif %}

A guard on is_incremental() instead of table existence would silently discard created_at history on every full refresh, even when the table already has the data — a real mistake this pattern exists to avoid.

deactivated_at/is_sold are not preserved. They reset to NULL/active on every full refresh of stock_int, regardless of why you ran it. That state comes from the pre-hook UPDATE described above, not from stock_int's own SELECT — the SELECT always produces is_sold = FALSE/deactivated_at = NULL for every row. A full refresh has nothing to preserve that state from; plan to let a normal incremental run follow shortly after to re-mark genuinely sold stock. This is a known, accepted trade-off — see Known issues for why it wasn't fixed the same way created_at was.

When to run a full refresh

Prefer --select <model> --full-refresh over a whole-project refresh whenever you know exactly which model changed — it's faster, cheaper, and (unless the model is stock_int itself) doesn't touch deactivated_at/is_sold at all.

Run --select stock_int --full-refresh when:

• You changed stock_int.sql's transformation logic and need every historical row recomputed now, not just rows that happen to get reprocessed naturally.
• You added a column that needs backfilling across existing rows.
• Upstream enrichment data changed retroactively (a Redbook key mapping was corrected, for example) and you want it reflected everywhere immediately.

dbt run --select stock_int --full-refresh --target staging

Whole-project refresh (make dbt-full-refresh-stock, or dbt run --full-refresh with no --select) rebuilds every model — confirm that's actually necessary, especially in production, since it costs more, takes longer, and resets deactivated_at/is_sold for the entire table.

Full re-ingest from Manifold: scripts/full-stock-sync

For a genuine start-over — re-seeding stock entirely from Manifold's own database rather than reprocessing what's already in Snowflake — scripts/full-stock-sync/main-ingest.sh automates the full release checklist end to end:

./main-ingest.sh [staging|prod] [--no-clean] [--disable-chunking] [--skip-dbt] [--yes]

What it does, in order:

1. Suspend ingestion services — pauses STOCK_BRONZE_PIPE (the normal production Snowpipe) and suspends VEHICLE_STOCK_DBT_RUN_15MIN, so neither races the reseed while it's in progress.
2. Wait for drain — polls SYSTEM$PIPE_STATUS() and TASK_HISTORY for up to 300 seconds until the pipe is fully paused with nothing pending and no task run still executing.
3. Truncate the stock tables for a clean re-seed (unless --no-clean) — STOCK_CHANGE_HISTORY, STOCK_COLOURS_REF, STOCK_IMAGE_INT, STOCK_INT, STOCK_NVIC_REF, STOCK_REDBOOK_ENRICHMENT_REF, STOCK_REJECTED_PER_BATCH, STOCK_WARRANTY_REF, STOCK_STG, STOCK_RAW.
4. Ingest: load-stock-csv.sh <env> pulls stock data and loads it, then runs dbt (unless --skip-dbt).
5. Initialize CDC tables: full-refresh stock_change_history and stock_historical_seed_events_stg.
6. Seed historical records: a one-time INSERT backfills stock_change_history from the seed table — a no-op if stock_change_history already has rows, so re-running this step safely does nothing on a table that's already seeded.
7. Validate: confirm the seeded row count in stock_change_history matches the seed table, and confirm the pipeline is capturing new change events correctly going forward.
8. Resume ingestion services — un-pauses the Snowpipe and un-suspends the 15-minute task.

./main-ingest.sh                        # staging, truncate + re-seed (default)
./main-ingest.sh prod                   # prod, truncate + re-seed
./main-ingest.sh prod --no-clean --yes  # prod, incremental (no truncate), no prompt

Treat this as a genuinely destructive operation on prod by default — the default behaviour truncates before re-seeding. Use --no-clean deliberately when you want an incremental top-up instead of a full wipe.

This bypasses Snowpipe entirely — it doesn't go through S3

Normal production ingestion lands stock via S3 and STOCK_BRONZE_PIPE's Snowpipe auto-ingest (see Stock Ingestion). load-stock-csv.sh does not use that path at all: it creates a temporary internal Snowflake stage per chunk, PUTs the CSV chunk directly to it, then runs COPY INTO STOCK_RAW ... FROM @stage. This is a genuinely separate loading mechanism, not a variant of the production one — which is exactly why step 1 above pauses the real Snowpipe first, so the two don't write to STOCK_RAW concurrently.

Where the data actually comes from

fetch-stock-data.sh doesn't call an API — it runs two mysql queries directly against Manifold's legacy MySQL database (the stock_v2 schema, on the drive-prod-db-manifold-mysql-sls2 RDS cluster), one for active stock (is_sold = 0) and one for sold stock (is_sold = 1), and converts the TSV output to CSV. "Manifold" here means this MySQL database specifically, not a service or API — it's the same source system referenced throughout this repo's other Manifold-sourced feeds (Redbook, external stock ID mapping).

Both the active and sold loads pass provider-specific dbt vars — sold-stock chunks run with bypass_active_dealer_check: true (see STOCK_RAW schema & file superseding — historical sold stock may belong to dealers that are no longer active), and both active and sold chunks run with skip_colour_api: true (skips the external colour-standardisation API call in stock_colours_ref, avoiding throttling across a bulk historical load).

See also

• Known issues — the created_at bug and the is_sold/deactivated_at trade-off mentioned above, in full
• Redbook key resolution hierarchy
• Marts (fact & dimension)