drive-snowflake

Stock Pipeline

src/stock/dbt is the most complex dbt project in this repo. It isn't a straight-line medallion pipeline — stock_int fans in from stock_stg plus six separate enrichment models joined through one hub view, and a second, parallel branch (stock_change_history) reads raw data directly to build a change-data-capture event stream alongside it. This page maps the whole shape before the detail pages go model-by-model.

End-to-end: the fan-in, not a chain

Six models feed the hub view that stock_int joins against — colours, NVIC fallback, Salesforce dealer lookup, Redbook key resolution, warranty calculation, and DAP pricing:

The CDC branch is separate again — it reads STOCK_RAW directly, not stock_stg:

How often this runs

VEHICLE_STOCK_DBT_RUN_15MIN runs dbt run (not dbt build) against the whole STOCK_DBT project every 15 minutes, on warehouse STOCK_WH:

snippet.hclhcl
"VEHICLE_STOCK_DBT_RUN_15MIN" = {
  database_key     = "VEHICLE_STOCK_DB"
  schema_key       = "VEHICLE_STOCK_SCHEMA"
  dbt_project_name = "STOCK_DBT"
  schedule_minutes = var.stock_task_schedule_minutes  # 15, both environments
  schedule_type    = "minutes"
  started          = var.enable_stock_task
  warehouse_key    = "STOCK_WH"
}
  • ARGS = 'run', not build — the scheduled task never runs dbt tests. Every test in this project (schema tests and the extensive tests/unit/ suite) only executes when someone explicitly runs dbt test/dbt build — not as part of the normal 15-minute cadence.
  • Enabled state differs by environment, as checked in: staging's enable_stock_task defaults to false (Terraform comment: "Suspended to prevent errors during migration"), prod's defaults to true. No .tfvars override either — so staging's scheduled stock task is off by default, prod's is on.

Medallion architecture, with the caveat that matters

Raw → staging → intermediate → marts still describes the overall shape, but "intermediate" here isn't one layer, it's seven models converging into stock_int through stock_enrichments_view. All 4 marts are views, not materialized tables — computed on read from stock_int (or stock_change_history_enriched for the lifecycle mart), not their own physically stored copy.

At a glance

Core modelsstock_stg → 6 enrichment refs → stock_enrichments_view → stock_int → 3 marts
CDC branchSTOCK_RAW → stock_change_history → stock_change_history_enriched → stock_lifecycle_fact_mart
ScheduleVEHICLE_STOCK_DBT_RUN_15MIN, every 15 minutes, dbt run (no tests), STOCK_WH — off by default in staging, on in prod
Uniqueness invariantExactly one row per stock_unique_key (data_provider_dealer_id + data_provider_stock_id) in stock_stg/stock_int — not enforced by Snowflake, only by model logic
Test postureProject-wide severity: warn — same as Redbook; tests don't block the scheduled run since it's dbt run, not dbt build
Full re-ingestscripts/full-stock-sync/main-ingest.sh — pauses the Snowpipe and the 15-minute task, truncates, reloads from Manifold's MySQL, reseeds CDC, resumes

Start here

Esc
Esc Close