gold.decision_context is the foundation of the decision layer. It materializes one row
per (organization_id, variant_id, shop_id, snapshot_date), consolidating stock state,
margin, forecast, and dimension keys into a single shape that every decision-vector scorer
reads. Build it once, score it three ways.
| Concept | Value |
|---|---|
| Grain | one row per (organization_id, variant_id, shop_id, snapshot_date) |
| Primary key | ["organization_id", "variant_id", "shop_id", "snapshot_date"] |
| Write mode | MERGE on the PK (idempotent same-day reruns) |
| Clustering | (organization_id, snapshot_date, variant_id) |
| Retention | 90 days of daily snapshots per position (writer-enforced) |
| Writer | BuildDecisionContextTask (pipelines/layers/gold/tasks/build_decision_context/) |
Lineage — three gold inputs
The builder anchors ongold.stock_snapshot (one position = one stock row) and LEFT-joins
margin and forecast onto it, collapsing every input across size_taxonomy_id first.
stock_snapshotis the anchor: no positions → nothing to build (skip_task_if_empty).sales_kpissuppliesgross_margin_pct(LEFT JOIN on the position key).sales_forecastsis best-effort — filtered togranularity = 'daily', summed over the next-30-day window; a missing forecast leaves the column NULL.
Column reference
Audit columns (created_at, updated_at) are omitted. The aged_stock_flag is the only
non-position column that is NOT NULL.
Stock state
| Column | Type | Meaning | Source / transformation |
|---|---|---|---|
current_stock | DOUBLE | On-hand quantity at snapshot_date | stock_snapshot.current_stock_qty, SUM across sizes |
target_stock | DOUBLE | Target stock for the position | v1: always NULL (Phase-2 policy-table hook) |
days_of_cover | DOUBLE | Days of cover at current pace | stock_snapshot.days_of_supply, MIN across sizes |
lead_time_days | INT | Supplier lead time | v1: always NULL (lands with the supplier dimension) |
Sales & margin
| Column | Type | Meaning | Source / transformation |
|---|---|---|---|
gross_margin_pct | DOUBLE | Realised gross margin % | LEFT JOIN sales_kpis |
sell_through_7d / 30d / 90d | DOUBLE | Sell-through rates | v1: always NULL (per-position computation is Phase 2) |
aged_stock_flag | BOOLEAN | Aged-stock indicator (NOT NULL) | (snapshot_date − last_sale_date) ≥ aged_stock_threshold_days (default 90); false on parse failure |
Forecast
| Column | Type | Meaning | Source / transformation |
|---|---|---|---|
forecast_30d | DOUBLE | 30-day demand forecast | SUM sales_forecasts.quantity over (snapshot_date, +30] |
forecast_confidence | DOUBLE | Confidence of the 30-day forecast | AVG forecast_confidence over the same window |
Dimension keys (for rule-scope matching)
| Column | Type | Meaning | Source |
|---|---|---|---|
brand_id | STRING | Brand (denormalised) | passthrough from the anchor stock_snapshot row |
category_id | STRING | Category | v1: always NULL |
supplier_id | STRING | Supplier | v1: always NULL |
v1 always-NULL slots
Seven columns are typed and reserved but always NULL in v1 —target_stock,
lead_time_days, sell_through_7d/30d/90d, category_id, supplier_id. Their type and
nullability are stable so the writer can fill them later without a DDL migration.
The scoring layer treats NULL as “skip this signal for this position” by coalescing each
NULL input to a neutral default before the math runs (documented per-domain on the next page).
Validation
Rules are embedded on the schema and applied by the writer:- Errors (fail the task):
decision_context_not_empty,decision_context_required_fields(organization_id,snapshot_date,aged_stock_flagnon-NULL),decision_context_pk_unique. - Warnings: non-negativity checks on
current_stock,days_of_cover,forecast_30d.
DecisionContextHealthTask raises [freshness] (no recent snapshot) or
[row-count-drift] (today’s slice diverges from yesterday’s) breaches for on-call triage.
Source
- Schema:
pipelines/shared/schemas/gold/decision_context.py - Writer:
pipelines/layers/gold/tasks/build_decision_context/task.py - Repo doc:
docs/gold/decision-context.md